PostgreSQL
The world's most advanced open source database
Top posters
Number of posts in the past month
Top teams
Number of posts in the past month
Feeds
Planet
  • Policy for being listed on Planet PostgreSQL.
  • Add your blog to Planet PostgreSQL.
  • List of all subscribed blogs.
  • Manage your registration.
Contact
  • Get in touch with the Planet PostgreSQL administrators at planet at postgresql.org.

Long story short, using PostgreSQL 11 and RUM index you can do both TOP-N query and COUNT(*) query for non-selective FTS queries without fetching all the matching results from heap (and that is certainly much faster). If you’re interested in details, then please read the detailed description below.

At November 1st 2017, Tome Lane committed a patch enabling bitmap scans to behave like index-only scan when possible. In particular, since PostgreSQL 11 COUNT(*) queries can be evaluated using bitmap scans without accessing heap, when corresponding bit in visibility map is set.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
commit 7c70996ebf0949b142a99c9445061c3c83ce62b3
Author: Tom Lane <a href="&#109;&#097;&#105;&#108;&#116;&#111;:&#116;&#103;&#108;&#064;&#115;&#115;&#115;&#046;&#112;&#103;&#104;&#046;&#112;&#097;&#046;&#117;&#115;">&#116;&#103;&#108;&#064;&#115;&#115;&#115;&#046;&#112;&#103;&#104;&#046;&#112;&#097;&#046;&#117;&#115;</a>
Date:   Wed Nov 1 17:38:12 2017 -0400</p>

<pre><code>Allow bitmap scans to operate as index-only scans when possible.

If we don't have to return any columns from heap tuples, and there's
no need to recheck qual conditions, and the heap page is all-visible,
then we can skip fetching the heap page altogether.

Skip prefetching pages too, when possible, on the assumption that the
recheck flag will remain the same from one page to the next.  While that
assumption is hardly bulletproof, it seems like a good bet most of the
time, and better than prefetching pages we don't need.

This commit installs the executor infrastructure, but doesn't change
any planner cost estimates, thus possibly causing bitmap scans to
not be chosen in cases where this change renders them the best choice.
I (tgl) am not entirely convinced that we need to account for this
behavior in the planner, because I think typically the bitmap scan would
get chosen anyway if it's the best bet.  In any case the submitted patch
took way too many shortcuts, resulting in too many clearly-bad 
[...]

Working with databases, concurrency control is the concept that ensures that database transactions are performed concurrently without violating data integrity.

There is a lot of theory and different approaches around this concept and how to accomplish it, but we will briefly refer to the way that PostgreSQL and MySQL (when using InnoDB) handle it, and a common problem that can arise in highly concurrent systems: deadlocks.

These engines implement concurrency control by using a method called MVCC (Multiversion Concurrency Control). In this method, when an item is being updated, the changes will not overwrite the original data, but instead a new version of the item (with the changes) will be created. Thus we will have several versions of the item stored.

One of the main advantages of this model is that locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

But, if several versions of the same item are stored, which version of it will a transaction see? To answer that question we need to review the concept of transaction isolation. Transactions specify an isolation level, that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions.This degree is directly related with the locking generated by a transaction, and so, as it can be specified at transaction level, it can determine the impact that a running transaction can have over other running transactions.

This is a very interesting and long topic, although we will not go into too much details in this blog. We’d recommend the PostgreSQL and MySQL official documentation for further reading on this topic.

So, why are we going into the above topics when dealing with deadlocks? Because sql commands will automatically acquire locks to ensure the MVCC behaviour, and the lock type acquired depends on the transaction isolation defined.

There are several types of locks (again another long and inter

[...]
Posted by Payal Singh in OmniTI on 2018-05-20 at 23:39
For new blog posts (as well as these old ones) visit my website

Parsing logs is easy. You can create a Perl script to do all the hard work for you. But what if the files you want to parse vary greatly in content and format? Then you in turn need to do hard work on your script before it can work for you. Sounds fair. But again, what if you don't even have a fixed set of files, and you can never be sure which type of file you'll need to parse. In this case its probably an overkill to maintain a script to be able to parse all these files. It might just be faster to create a piped command on the go, and just change the order of variables as and when required.

While relying on single line commands to parse your files is not the best idea when you need the same information from the same kind of files over time, this can be much less of an headache if you just want a quick report on a particular attribute value in a log file. As a CLI noob, I was blown away by the flexibility and range of these tools, especially when used with pipes. Think of these individual tools as Planeteers having their own powers, but when a task requires greater force, they combine their powers to summon Captain Planet, or in our case... a log parser!

Grep piped with Awk is probably the easiest, and most commonly used command you can use for selective extraction. For instance, the following file is produced from an extremely useful tool called system_monitoring. This file has the form:















Lets say we only was the wkB/s information for disk 'sdb' with the time, so we can feed this to any graph generator and get a plot of the write blocks every 5 seconds for our server. Making such an input file can be accomplished in the following two commands:

Creating headers:
echo "time"$'\t'"wKBps" > writes.tsv - This just creates a new file 'writes.tsv' and echos the headers seperated by tabs. 

Parsing content:
cat iostat.log | grep sdb | awk {'print $2"\t"$11'} >> writes.tsv - The first part of this piped command just outputs the logfile. Th
[...]
pg_hexedit recently gained the ability to annotate the space taken up by each individual column/attribute within each individual tuple. This works with tables, and with B-Tree indexes.

I had to come up with a way of passing the pg_hexedit frontend utility the relevant pg_attribute metadata to make this work. This metadata describes the "shape" of individual tuples in a relation (backend code uses a closely related structure called a "tuple descriptor"). My approach works seamlessly in simple cases, but can still be used when manually running the pg_hexedit command line tool.

pg_attribute system catalog table with column annotations/tags

This new capability could be applied to optimizing the data layout of a table that is expected to eventually have a massive number of rows. Carefully choosing the order and type of each column can reduce the total on-disk footprint of a table by an appreciable amount, especially when the final table ends up with several 1 byte columns that get packed together.

I am aware of several PostgreSQL users that found it worthwhile to have a highly optimized tuple layout, going so far as to use their own custom dataypes. Alignment-aware micro-optimization of a Postgres client application's schema won't help much in most cases, but it can help noticeably with things like fact tables, or tables that contain machine-generated event data. Developing a sense of proportion around storage overhead should now be more intuitive.

For those of you out there working with PostgreSQL in a professional way, migrating from Oracle to PostgreSQL might be one of the most beloved tasks available. One of the first things most people will notice, however, is that those data types available in Oracle might not be quite the same in PostgreSQL. This blog will try to shed some light and show, how things work.

Data types in Oracle and PostgreSQL

While there are many similarities between Oracle and PostgreSQL there are a couple of differences, which are quite noteworthy. The first thing many people might notice is: PostgreSQL has many more data types than Oracle. As of version 10.0 an empty PostgreSQL database will expose the staggering number of 92 data types. Of course not all of them are useful and many of them are purely internal used for internal purposes.

Still: At the end of the day there are just more data types, which are can be used by applications, which of course

Let us take a look and see, which types can be matched. The following table contains a lof of potential options:

 

Oracle type Possible PostgreSQL types
CHAR char, varchar, text
NCHAR char, varchar, text
VARCHAR char, varchar, text
VARCHAR2 char, varchar, text, json
NVARCHAR2 char, varchar, text
CLOB char, varchar, text, json
LONG char, varchar, text
RAW uuid, bytea
BLOB bytea
BFILE bytea (read-only)
LONG RAW bytea
NUMBER numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0 numeric, float4, float8, int2, int4, int8,
boolean, char, varchar, text
FLOAT numeric, float4, float8, char, varchar, text
BINARY_FLOAT numeric, float4, float8, char, varchar, text
BINARY_DOUBLE numeric, float4, float8, char, varchar, text
DATE date, timestamp, timestamptz, char, varchar, text
TIMESTAMP date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH date, timestamp, timestamptz, char, varchar, text
LOCAL TIME ZONE
INTERVAL YEAR TO MONTH interval, char, varc
[...]
PostgreSQL allows to set time limit for each SQL statement. For example,

SET statement_timeout to '4s';

will set the upper limit of execution time for each subsequent query to 4 seconds. So,

SELECT pg_sleep(5);

will cancel  4 seconds after.

ERROR:  canceling statement due to statement timeout

Simple and pretty easy to understand.

But if it is applied  to extended queries, things are not so simple any more. Each extended query is divided into multiple phases:

  1. Parse: parse SQL statement
  2.  Bind: bind prameters to the parsed SQL statement
  3.  Execute: run the query
  4.  Sync: ask PostgreSQL to return the query results
 So when the statement timeout fires? The answer is 4, not 3. Until sync message is recieved, the statement timeout will not be checked in extended queries.

Ok. Consider next example. We assume that statement timeout has been set to 4 seconds.
  1. Parse: SELECT pg_sleep(2) (Query A)
  2.  Bind: bind prameters to the parsed SQL statement A
  3.  Execute: run the query A
  4. Parse: SELECT pg_sleep(3) (Query B)
  5. Bind B
  6.  Execute B
  7.  Sync
This time, the statment timeout will be fired even if each query A and B is finished  within 4 seconds. This is not very intuitive behavior I think. More over, if duration log is enabled, the time for each query A and B will be 2 seconds and 3 seconds of course. So users will be confused because despite the fact that each query definitely finishes within 4 seconds, the statement timer is fired.

From PostgreSQL 11, this behavior will be changed. The statement timeout will be checked at the time when Execute message is issued (3 and 6 above). So the statement timer will not be fired in the example.

In summary, statement timeout in PostgreSQL 11 will show more intuitive behavior than previous releases.
Posted by Bruce Momjian in EnterpriseDB on 2018-05-18 at 00:15

I have completed the draft version of the Postgres 11 release notes. Consisting of 167 items, this release makes big advances in partitioning, parallelism, and server-side transaction control via procedures. One of the more unexpected yet useful features is "Allow 'quit' and 'exit' to exit psql when used in an empty buffer".

The release notes will be continually updated until the final release, which is expected in September or October of this year.

Posted by Robert Haas in EnterpriseDB on 2018-05-17 at 17:03
Built-in sharding is something that many people have wanted to see in PostgreSQL for a long time. It would be a gross exaggeration to say that PostgreSQL 11 (due to be released this fall) is capable of real sharding, but it seems pretty clear that the momentum is building. The capabilities already added are independently useful, but I believe that some time in the next few years we're going to reach a tipping point. Indeed, I think in a certain sense we already have. Just a few years ago, there was serious debate about whether PostgreSQL would ever have built-in sharding. Today, the question is about exactly which features are still needed.
Read more »
Posted by Pavel Stehule on 2018-05-17 at 09:12
I did one indicative benchmark of popular interpret languages, and I was surprised how modern PHP is fast now. This test is pretty simple and stupid, syntactical, unrealistic, and I know it. It say nothing about speed any interpret in practical usage. But can be interesting to see, how surprisingly some interprets are near to C, and what engines can be used for intensive numeric calculations.
void main()
{
long int s = 0;
int i;

for (i in 1; i < 10000000; i++)
s := i;

printf("%ld\n", s);
}
optimized C 3ms
LuaJIT 20ms
unoptimized C 30ms
Lua 100ms
PHP 200ms
JavaScript V8 engine 500ms
Perl 600ms
JavaScrip Mozilla 900ms
Python2 1200ms
Python3 1700ms
PostgreSQL SQL 1700ms
PLpgSQL 2200ms

I repeat, this test has very small value for life - Only C language from this list is designed for heavy numeric operations. All other languages has designed for different purposes and in their typical domain the typical bottleneck will be elsewhere than in simple numeric calculation. But can be interesting, how modern computers are fast - for example PLpgSQL is designed as SQL glue (I know, so it is absolutely without any optimization - I hope so nobody use PLpgSQL for heavy numeric calculations, and still), and it is able do 10M operations in 2 sec.
Posted by Laurenz Albe in Cybertec on 2018-05-16 at 07:57
caged elephant
© Laurenz Albe 2018

 

In a recent wrestling match with the Linux “out-of-memory killer” for a Cybertec customer I got acquainted with Linux control groups (“cgroups”), and I want to give you a short introduction how they can be used with PostgreSQL and discuss their usefulness.

Warning: This was done on my RedHat Fedora 27 system running Linux 4.16.5 with cgroups v1 managed by systemd version 234. Both cgroups and systemd‘s handling of them seem to be undergoing changes, so your mileage may vary considerably. Still, it should be a useful starting point if you want to explore cgroups.

What are Linux cgroups?

From the cgroups manual page:

Control cgroups, usually referred to as cgroups, are a Linux kernel feature which allow processes to be organized into hierarchical groups whose usage of various types of resources can then be limited and monitored.

cgroups are managed with special commands that start with “cg”, but can also be managed through a special cgroups file system and systemd.

Now a running PostgreSQL cluster is a group of processes, so that’s a perfect fit.

There are several subsystems defined (also called “controllers” in cgroups terminology). Of these, the following are interesting for PostgreSQL:

  • memory: useful for limiting the total memory usage
  • blkio: useful for limiting the I/O throughput
  • cpu: useful to define upper and lower limits to the CPU time available for the processes
  • cpuset: useful for binding the processes to a subset of the available CPU cores

Configuring cgroups

During system startup, cgroups are created as defined in the /etc/cgconfig.conf configuration file.

Let’s create a cgroup to build a cage for a PostgreSQL cluster:

group db_cage {
    # user and group "postgres" can manage these cgroups
    perm {
        task {
            uid = postgres;
            gid = postgres;
            fperm = 774;
        }
        admin {
            uid = postgres;
            gid = postgres;
            dperm = 775;
            fperm = 774;
        }
    }

    # limit memory to 1 GB and di
[...]

Common Table Expressions (CTEs) are a powerful construct within SQL. In day to day conversation, you may hear CTEs referred to as WITH clauses. You can think of CTEs as similar to a view that is materialized only while that query is running and does not exist outside of that query. CTEs can be very useful building blocks for allowing your large SQL queries to be more readable. But, they can also be used recursively allowing you to create some very complex queries without having to drop down to a procedural language like plpgsql or plv8.

Recursive CTEs allow themselves to be called until some condition is met. Let’s jump right in and explore a recursive CTE—a basic one, and using PostgreSQL of course—and then let’s dissect the recursive CTE a bit further to see what we can use it for:

WITH RECURSIVE tens (n) AS (
    SELECT 10
  UNION ALL
    SELECT n+10 FROM tens WHERE n+10<= 100
)
SELECT n FROM tens;

When the above is run we’ll get the following result:

  n
-----
  10
  20
  30
  40
  50
  60
  70
  80
  90
 100
(10 rows)

With the above we could also easily do this with a generate_series. But stick with us and you’ll see the more complex things we can do that aren’t possible with generate_series. First let’s take a closer look at how it works.

The first part you’ll notice is WITH RECURSIVE. This tells Postgres the CTE can recursively call itself. The next portion you’ll notice is it takes some parameters into it. In this case (n), it can also take more than one should you need.

Moving further into the CTE, we have the first query that is executed, SELECT 10, which generates the first value. The second portion is where all the fun begins. The UNION ALL specifies that we’re going to return all the records that are produced from the loop. Then SELECT n+10 FROM tens WHERE n+10<= 100 will keep calling the tens CTE that is created until the condition is met.

So those are the basics, but the interesting question is: when would you use a recursive CTE? When you have a tree or hierarchical structure to your data, re

[...]
Posted by pgCMH - Columbus, OH on 2018-05-15 at 04:00

The May meeting will be held at 18:00 EST on Tues, the 22nd. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.

BRING YOUR LAPTOP

What

Due to technical issues last month, CoverMyMeds’ very own Andy will be re-presenting on PostgreSQL data restores this month. We’ll continue to use our VMs and pgBackRest to dive into the topic as well as the concepts and commands underneath. Please bring your laptop as this will be hands-on.

Where

CoverMyMeds has graciously agreed to validate your parking if you use their garage so please park there:

You can safely ignore any sign saying to not park in the garage as long as it’s after 17:30 when you arrive.

Park in any space that is not marked ‘24 hour reserved’.

Once parked, take the elevator/stairs to the 3rd floor to reach the Miranova lobby. Once in the lobby, the elevator bank is in the back (West side) of the building. Take a left and walk down the hall until you see the elevator bank on your right. Grab an elevator up to the 11th floor. (If the elevator won’t let you pick the 11th floor, contact Doug or CJ (info below)). Once you exit the elevator, look to your left and right; one side will have visible cubicles, the other won’t. Head to the side without cubicles. You’re now in the community space:

Community space as seen from the stage

The kitchen is to your right (grab yourself a drink) and the meeting will be held to your left. Walk down the room towards the stage.

If you have any issues or questions with parking or the elevators, feel free to text/call Doug at +1.614.316.5079 or CJ at +1.740.407.7043

Posted by Paul Ramsey in PostGIS on 2018-05-14 at 16:00

I presented my “PostGIS for Managers” talk for the last time (at least in this form) today at FOSS4G North America. The reason it’s for the last time is that the central conceipt it’s built around, that a core decision is between a proprietary and an open source database, isn’t really operative anymore. The real decisions are now being driven by other considerations, like cloud platforms, and the services available there. So, it’s not really PostgreSQL vs Oracle anymore.

I also presented my “SQL Festival” talk, for the first time! New material is always a little challenging: will it work, is it the right level for the audience? It seemed to be well received, a mix of generic SQL tidbits, and some specific interesting queries you can do with PostGIS.

In the previous part I dared to play with a not implemented feature fantasising how would it work. Well HA in first place is a matter of design and only then implementation. It does not excuse bad implementation, neither it makes naive designing look smart. Yet after you cover all possible scenarios and found an adequate best rule for most cases, sometimes a very primitive small change can ruin the stronghold. Below I want to sandbox.

What Happens When pgpool Should Failover, But Can’t?

When health check fails for the master, the failover_command fired to degenerate all or promote next slave to primary. Sounds solid. What if it fails itself, eg ssh connection fails (e.g. because other - bad admin remove key from ~/.ssh/authorized_keys). What we have?

As soon as health_check_timeout (default 20) is out (also affected by retry delay, max retires and so on) the node turns dead, so:

t=# select nid,port,st from dblink('host=localhost port=5433','show pool_nodes') as t (nid int,hostname text,port int,st text,lb_weight float,role text,cnt int,cur_node text,del int);
 nid | port |  st
-----+------+------
   0 | 5400 | down
   1 | 5401 | up
   2 | 5402 | up
(3 rows)

So no retries left and the failover failed. The first option obviously is doing failover manually. But if failover failed because of some stupid error, master is back on rails, and the only problem you have is pgpool thinking the master is offline - you would probably want to leave things as they used to be before the accident instead - right? Of course just moving master back online is not enough. Pgpool already “degenerated” the primary. Just adding it as a new node will not help either. The worst thing is that, after the event, pgpool will not try to check whether the old master is pg_is_in_recovery() or not, thus will never accept it as Primary. According to bug track you have to “Discard pgpool_status file and do not restore previous status” with pgpool -D command.

After discarding the status, we reconnect to avoid seeing server closed the connection un

[...]
Posted by Don Seiler on 2018-05-11 at 05:00

The Incident

While cleaning up some obsolete user accounts, one of my DROP USER commands failed with:

ERROR:  2BP01: role "joe" cannot be dropped because some objects depend on it

Querying the usual catalog views, I couldn’t find any objects owned by this user. That’s when I noticed the line just after the ERROR:

DETAIL:  privileges for schema public

The \dn+ command confirmed that user joe had explicit USAGE privileges on the public schema:

                           List of schemas
   Name   |  Owner   |  Access privileges   |      Description
----------+----------+----------------------+------------------------
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres        +|
          |          | joe=U/postgres       |

I revoked this and was then able to drop that user.

> REVOKE USAGE ON SCHEMA public FROM joe;
REVOKE
> DROP USER joe;
DROP ROLE

Replicating The Problem

It’s simple enough to replicate the problem, and it isn’t limited to the special public schema (although that does bring up another question for later).

Setup

We already have the public schema, let’s create another one for testing and then create our three lab rat users:

-- Create a test schema, we'll use this in addition to public
> CREATE SCHEMA appstuff;
CREATE SCHEMA

-- Create our three users
> CREATE USER moe;
CREATE ROLE
> CREATE USER larry;
CREATE ROLE
> CREATE USER curly;
CREATE ROLE

Now let’s grant USAGE on the two schemas to two of the users:

> GRANT USAGE ON SCHEMA public TO moe;
GRANT
> GRANT USAGE ON SCHEMA appstuff TO larry;
GRANT

-- Show the access privileges on the schemas
> \dn+
                           List of schemas
   Name   |  Owner   |  Access privileges   |      Description
----------+----------+----------------------+------------------------
 appstuff | postgres | postgres=UC/postgres+|
          |          | larry=U/postgres     |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres       
[...]

The slides from my talk on PostgreSQL Replication at PerconaLive 2018 are available.

Posted by gabrielle roth on 2018-05-10 at 15:59

When: 6-8pm Thursday May 17, 2018
Where: iovation
Who: Dylan Hornstein
What: Learning SQL

During this PDXPUG meetup, we will talk about one person’s journey to learn SQL. From joining iovation’s Reporting Team without any experience with SQL or relational databases, to using SQL every day for adhoc data analysis and bulk data dumps, Dylan Hornstein will talk about his experience getting familiar with SQL and learning to navigate a relational database, as well as some challenges and tips he’s found along the way. The presentation is geared towards those starting out in data roles and we will likely expand into a wider conversation around using SQL and understanding data.

Dylan has been a Data Analyst at iovation for three years now. Having spent six months as a Client Manager before moving to his Data Analyst role, Dylan has experience working directly with iovation’s clients as well as working behind the scenes with the data. In his current role, he is responsible for providing reports, adhoc analysis and bulk data dumps for clients and internal teams. While Dylan had prior work experience as a Data Analyst, his move to iovation’s Reporting Team came with a steep learning curve, as he was new to working with SQL and relational databases.


If you have a job posting or event you would like me to announce at the meeting, please send it along. The deadline for inclusion is 5pm the day before the meeting.

Our meeting will be held at iovation, on the 3rd floor of the US Bancorp Tower at 111 SW 5th (5th & Oak). It’s right on the Green & Yellow Max lines. Underground bike parking is available in the parking garage; outdoors all around the block in the usual spots. No bikes in the office, sorry! For access to the 3rd floor of the plaza, please either take the lobby stairs to the third floor or take the plaza elevator (near Subway and Rabbit’s Cafe) to the third floor. There will be signs directing you to the meeting room. All attendess must check in at the iovation front desk.

See you there!

Preface

There are several ways of building PostgreSQL under Windows. Official manual stands using Visual Studio is the simplest one, wiki describes how to use mingw and mingw-w64.

As for me, using new MSYS2 building platform for Windows is a bless. With its help not only PostgreSQL sources are built smoothly, but even extensions are not a problem anymore. And if you were playing with extensions under Windows, you know what I’m talking about.

At its core MSYS2 is an independent rewrite of MSYS, based on modern Cygwin (POSIX compatibility layer) and MinGW-w64 with the aim of better interoperability with native Windows software. It provides a bash shell, Autotools, revision control systems and the like for building native Windows applications using MinGW-w64 toolchains.

Installation

It is pretty straightforward. On the official page you have the step-by-step guide, but in case of troubles, you may check detailed install guide.

After installation and upgrading run in cmd console

C:\msys64\msys2_shell.cmd -mingw64

or simply just click on the Start menu “MSYS2 MinGW 64-bit” shortcut:
MSYS2 MinGW 64-bit shortcut

Then update packages:

pacman -Syu

Install only needed packages:

pacman --needed -S git mingw-w64-x86_64-gcc base-devel

Let’s build it!

Get the PostgreSQL sources. Here you have two options:
1. Download them from the official site.
2. Use git repository:

git clone git://git.postgresql.org/git/postgresql.git

Enter to the source folder:

cd postgresql

And run configure followed by make and make install:

./configure --host=x86_64-w64-mingw32 --prefix=/c/pgsql/ && make && make install

Here we tell our target machine will be 64-bit and we want our binaries to be copied to C:\pgsql\.

Conclusion

As you may see building PostgreSQL with MSYS2 for Windows is simple enough. But the main profit of such toolchain is building extensions for PostgreSQL. And there it shines in all its glory. Stay tuned.

The post Building PostgreSQL with MSYS2 and MinGW under Windows appeared first on Cybertec.

The following commit adds a new feature which is part of Postgres 11, and matters a lot for a couple of tools:

commit: e79350fef2917522571add750e3e21af293b50fe
author: Stephen Frost <sfrost@snowman.net>
date: Fri, 6 Apr 2018 14:47:10 -0400
Remove explicit superuser checks in favor of ACLs

This removes the explicit superuser checks in the various file-access
functions in the backend, specifically pg_ls_dir(), pg_read_file(),
pg_read_binary_file(), and pg_stat_file().  Instead, EXECUTE is REVOKE'd
from public for these, meaning that only a superuser is able to run them
by default, but access to them can be GRANT'd to other roles.

Reviewed-By: Michael Paquier
Discussion: https://postgr.es/m/20171231191939.GR2416%40tamriel.snowman.net

This is rather a simple thing: a set of in-core functions like using a hardcoded superuser check to make sure that they do not run with unprivileged user rights. For the last couple of releases, an effort has been made to remove those hardcoded checks so as one can GRANT execution access to a couple or more functions so as actions which would need a full superuser (a user who theoritically can do anything on the cluster and administers it), are delegated to extra users with rights limited to those actions.

This commit, while making lookups to the data directory easier, is actually very useful for pg_rewind as it removes the need of having a database superuser in order to perform the rewind operation when the source server is up and running.

In order to get to this state, one can create a dedicated user and then grant execution to a subset of functions, which can be done as follows:

CREATE USER rewind_user LOGIN;
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user;

Once run, then this

[...]

Introduction

PostgreSQL natively supplies a rich diversity of data types supporting many practical use cases. This article introduces the special implementation of serial data types typically used for creation of synthetic primary keys.

Unique Keys

A foundational precept of database design theory is that each tuple (i.e., row) of a relation (i.e., table) must be uniquely identified from other tuples. The attributes, or columns, that together distinctly identify one tuple from all the others are called a "key". Some purists maintain that any modeled object or concept inherently possesses an attribute or set of attributes that can serve as a key and that it is important to identify this set of key attributes and utilize them for the unique selection of tuples.

But as a practical matter, identifying a sufficiently large set of attributes assurring uniqueness for a modeled object may be impractical, and so for real-world implementations, developers often turn to synthetic keys as a surrogate. That is, rather than relying on some combination of actual attributes, a value internal to the database, typically incremented integer values, and otherwise having no physical meaning is defined as a key. In additional to the simplicity of a single column key, the fact that there is no real-world dependency means that external factors can never force a need to change the value, such as for instance, might be the case if a person's name where used as a key ... and then the person married or entered a federal government witness protection program and changed their name. Even some values commonly thought by laypersons to be unique and immutable, such as the U.S. social security number, are neither: a person can obtain a new SSN, and SSN's sometimes are re-used.

Declaring a Serial Data Type

PostgreSQL provides a special datatype declaration to satisfy this need for synthetic key

[...]
Posted by Federico Campoli on 2018-05-09 at 00:00

In the previous post we introduced the PostgreSQL’s dependency system.

At first sight the implementation can look like a maze where the succession of relationships are not clear.

This post will try to give a practical example to show how pg_depend can act like an Ariadne’s thread in order to resolve the dependencies.

The scenario presented is very simple but can be used as a starting point for more complex requirements.

Posted by Craig Kerstiens in CitusData on 2018-05-08 at 14:36

Hi. I work as a data architect in San Francisco and I’m auditing Dr. Jones class to stay up to date on the latest technologies and she mentioned you might be able to help me before I get too deep into the design of a new system.

I would be happy to help. Can you give me an overview of where you’re at?

Well my default was just to use Postgres. I had a few questions on what schema designs might make most sense.

Well I’m working with more interesting data architectures. Really getting excited about what’s possible with neomodern data architectures, they make it so my app devs can build any feature their hearts desire.

I thought your expertise used to be relational databases?

It was, but neomodern data architectures are better. Neomodern data architectures allow it so app devs can build any feature they like without having to think about data models. Really, it’s the future of databases.

Hmmm, my app is a pretty straightforward web app that allows salespeople to send campaigns and track metrics on the campaigns. My app should be fine in a Postgres database right?

That might work for you, but really, if you have to define a data model up front, then you’re limiting yourself.

How do you mean limiting? Can’t I just add new tables as I need them? Or add columns to existing tables?

Well you could, but meta document stores can take care of that for you

Meta document stores?

Yeah, you have a custom document for each data model, but then have a document store that auto parses each document and can tell you the structure without having to read the document itself

Oh nice, so I can have an index of all my data.

Exactly, and because you have a bunch of smaller document stores you can distribute them around the world and read the data from the nearest one

So reading from the nearest copy of data would be faster?

Well it’s supposed to be, but to make sure the data is correct you have to read from at least 3 nodes to get consensus

Consensus?

Yeah concensus. Since the underlying storage is all in blockchain, there has to be an agre

[...]
The PostgresConf team wanted to provide some information on the performance of PostgresConf US 2018 and events over the past year, as well as potentially answer some pending questions. Ultimately our goals are about people, which is why our motto is, "People, Postgres, Data." With each event we hold, each talk we accept, and how we train our volunteers, we make sure people (the benefit for and to), postgres, and data are considered and included. If there is no benefit or consideration to the growth of people, it is not an option.

With that in mind, please read on to see how our focus on people, Postgres, and data had an impact on the community over the last year.

Since PostgresConf US 2017 we have had events in:
  • Philadelphia 
  • Ohio (in combination with Ohio Linux Fest) 
  • South Africa 
  • Seattle 
  • Austin 
  • Jersey City (PostgresConf US 2018) 
  • Nepal 
All of these events are non-profit and volunteer organized.





PostgresConf US 2018


Logistics


  • Days: 5, 2 for training, 3 for Breakout sessions and summits
  • Official Attendance #: 601
  • Content: Over 207 sessions submitted
  • Sessions: Over 108 sessions provided 


Partner Support (Sponsors): 28


We had a record level of support from partners this year and due to this support we are going to be forced to find a new venue for next year. Our Jersey city location no longer has the capacity to hold us. This will increase costs but initial indications are that our partners understand this and are willing to support us financially to help continue the success of our efforts and keep costs reasonable for attendees.

Diversity


This year we were able to work with Women Who Code NYC. They provided many volunteers and we provided them with the ability to experience some of the best Postgres based content available, at no charge. We expect great things from this budding relationship in the future.


Professional Growth


We held a Career and Talent Fair. A dozen companies were present to connect with potential employees.

We also held a surprisingly well attended speed mentoring
[...]
Posted by Dimitri Fontaine in CitusData on 2018-05-07 at 08:46

Continuing our series of PostgreSQL Data Types today we’re going to introduce the PostgreSQL Point type.

In order to put the Point datatype in a context where it makes sense, we’re going to download a complete geolocation data set and normalize it, thus making good use of both the normalization good practice and those other PostgreSQL data types we’ve been learning about in the previous articles of this series.

Buckle-up, this is a long article with a lot of SQL inside.

To be OR not to be...
© Laurenz Albe 2018

 

PostgreSQL query tuning is our daily bread at Cybertec, and once you have done some of that, you’ll start bristling whenever you see an OR in a query, because they are usually the cause for bad query performance.

Of course there is a reason why there is an OR in SQL, and if you cannot avoid it, you have to use it. But you should be aware of the performance implications.

In this article I’ll explore “good” and “bad” ORs and what you can do to avoid the latter.

A little sample schema

We’ll use this simple setup for demonstration:

CREATE TABLE a(id integer NOT NULL, a_val text NOT NULL);

INSERT INTO a
   SELECT i, md5(i::text)
   FROM generate_series(1, 100000) i;

CREATE TABLE b(id integer NOT NULL, b_val text NOT NULL);

INSERT INTO b
   SELECT i, md5(i::text)
   FROM generate_series(1, 100000) i;

ALTER TABLE a ADD PRIMARY KEY (id);
ALTER TABLE b ADD PRIMARY KEY (id);
ALTER TABLE b ADD FOREIGN KEY (id) REFERENCES a;

VACUUM (ANALYZE) a;
VACUUM (ANALYZE) b;

Suppose that we want to run queries with equality and LIKE conditions on the text columns, so we need some indexes:

CREATE INDEX a_val_idx ON a(a_val text_pattern_ops);
CREATE INDEX b_val_idx ON b(b_val text_pattern_ops);

Have a look at the documentation if you don’t understand text_pattern_ops.

The “good” OR

An OR is fine in most parts of an SQL query: if it is not used to filter out rows from your query result, it will have no negative effect on query performance.

So if your OR appears in a CASE expression in the SELECT list, don’t worry.

Unfortunately you usually find the OR where it hurts: in the WHERE clause.

The “bad” OR

Now for an example of an OR in a WHERE clause that is still pretty nice:

EXPLAIN (COSTS off)
SELECT id FROM a
WHERE id = 42
   OR a_val = 'value 42';

                        QUERY PLAN                         
-----------------------------------------------------------
 Bitmap Heap Scan on a
   Recheck Cond: ((id = 42) OR (a_val = 'value 42'::text))
   ->  BitmapOr
         ->  Bitmap Index Scan on a_pkey
[...]
Posted by Shaun M. Thomas in 2ndQuadrant on 2018-05-04 at 17:00

With the addition of logical replication in Postgres 10, it’s natural to ask "what’s next"? Though not directly supported yet, would it be possible to subscribe two Postgres 10 nodes to each other? What kind of future would that be, and what kind of scenarios would be ideal for such an arrangement?

As it turns out, we already have a kind of answer thanks to the latency inherent to the speed of light: locality. If we can provide a local database node for every physical app location, we also reduce latency by multiple orders of magnitude.

Let’s explore the niche BDR was designed to fill.

What is Postgres-BDR?

Postgres-BDR is simply short for Postgres Bi-Directional Replication. Believe it or not, that’s all it needs to be. The implications of the name itself are numerous once fully explored, and we’ll be doing plenty of that.

So what does it do?

  • Logical replication
  • Multi-Master
  • Basic conflict resolution (last update wins)
  • Distributed locking
  • Global sequences
  • High latency replay (imagine a node leaves and comes back later)

How can two systems interact?

The key to having Multi-Master and associated functionality is logical replication. Once we can attach to the logical replication stream, we just need a piece of software to communicate between all participating nodes to consume those streams. This is necessary to prevent nodes from re-transmitting rows received from another system and resolve basic conflicts.

Why Geographical Distribution?

Specifically:

  • Local database instances
  • Inter-node communication is high latency
  • Eventual consistency is the only consistency (for now)
  • That’s a Good Thing!(tm)

We don’t want latency between our application and the database, so it’s better to operate locally. This moves the latency into the back-end between the database nodes, and that means the dreaded "eventual consistency" model. It’s a natural consequence, but we can use it to our advantage.

Consider This

We have an application stack that operates in four locations: Sydney, Dubai, Dallas, and Tokyo.

Applications in several countries

We tried to get a data center in Antarctica, but t

[...]
Posted by Luca Ferrari on 2018-05-04 at 00:00

plperl does not allow direct sub invocation, so the only way is to execute a query.

plperl: invoking other subroutines

The official plperl documentation shows you a way to use a subref to invoke code shared across different plperl functions via the special global hash %_SHARED. While this is a good approach, it only works for code attached to the hash, that is a kind of closure (e.g., a dispatch table), and requires each time an initialization of the %_SHARED hash since plperl interpreters does not share nothing across sections.

The other way, always working, is to execute a query to perform the SELECT that will invoke the function. As an example:

CREATE OR REPLACE FUNCTION plperl_trampoline( fun_name text ) RETURNS TEXT AS $PERL$ my ( $fun_name ) = @_; return undef if ( ! $fun_name ); elog( DEBUG, "Calling [$fun_name]" ); my $result_set = spi_exec_query( "SELECT $fun_name() AS result;" ); return $result_set->{ rows }[ 0 ]->{ result }; $PERL$ LANGUAGE plperl; 

so that you can simply do:

> select plperl_trampoline( 'now' ); plperl_trampoline ------------------------------ 2018-05-04 13:09:17.11772+02 

The problem of this solution should be clear: it can work only for a...

This is the second part of the blog “A Guide to Pgpool for PostgreSQL”. The first part covering load balancing, session pooling, in memory cache and installation can be found here.

Many users look towards pgpool specifically for High Availability features, and it has plenty to offer. There are few quite a lot of instructions for pgpool HA on the web (e.g. longer one and shorter one), so it would not make any sense to repeat them. Neither do we want to provide yet another blind set of configuration values. Instead I suggest to play against the rules and try doing it the wrong way, so we’ll see some interesting behaviour. One of the top expected feature (at least it’s on the top of the page) is the ability to recognise the usability of a “dead” ex master and re-use it with pg_rewind. It could save hours of bringing back the new standby with big data (as we skip rsync or pg_basebackup, which effectively copies ALL files over from the new master). Strictly speaking, pg_rewind is meant for planned failover (during upgrade or migrating to new hardware). But we’ve seen when it’s greatly helps with not planned but yet graceful shutdown and automated failover - for e.g., ClusterControl makes use of it when performing automatic failover of replication slaves. Let’s assume we have the case: we need (any) master to be accessible as much as possible. If for some reason (network failure, max connections exceeded or any other “failure” that forbids new sessions to start) we no longer can use a master for RW operations, we have a failover cluster configured, with slaves that can accept connections. We can then promote one of the slaves and fail over to it.

First let’s assume we have three nodes:

  • 10.1.10.124:5400 with /pg/10/m (pgpool spins here as well)
  • 10.1.10.147:5401 with /pg/10/m2
  • 10.1.10.124:5402 with /pg/10/s2

Those are effectively the same nodes as in part one, but the failover node is moved to a different host and $PGDATA. I did it to make sure I did not typo or forget some extra quote in remote ssh command. Also the

[...]

Recently I did some PostgreSQL consulting in the Berlin area (Germany) when I stumbled over an interesting request: How can data be shared across function calls in PostgreSQL? I recalled some one of the other features of PostgreSQL (15+ years old or so) to solve the issue. Here is how it works.

Stored procedures in PostgreSQL

As many of you might know PostgreSQL allows you to write stored procedures in many different languages. Two of the more popular ones are Perl and Python, which have been around for quite some time. The cool thing is: Both languages offer a way to share variables across function calls. In Perl you can make use of the $_SHARED variable, which is always there.

Here is an example:

CREATE OR REPLACE FUNCTION set_var(int)
RETURNS int AS $$
   $_SHARED{'some_name'} = $_[0];
   return $_[0];
$$ LANGUAGE plperl;

What the code does is to assign a value to some_name and returns the assigned value. Some other function can then make use of this data, which is stored inside your database connection. Here is an example:

CREATE OR REPLACE FUNCTION increment_var()
RETURNS int AS $$
   $_SHARED{'some_name'} += 1;
   return $_SHARED{'some_name'};
$$ LANGUAGE plperl;

This function will simply increment the value and return it. As you can see the code is pretty simple and easy to write.

Assigning shared variables

The following listing shows, how the code can be used. The first call will assign a value to the function while the second one will simply increment that value:

test=# SELECT set_var(5);
 set_var
---------
 5
(1 row)

test=# SELECT increment_var(), increment_var();
 increment_var | increment_var
---------------+---------------
             6 | 7
(1 row)

It is especially noteworthy here that the second column will already see the changes made by the first column, which is exactly what we want here.

Shared variables and transactions

When working with shared variables in PL/Perl or PL/Python you have to keep in mind that those changes will not be transactional as all the rest in PostgreSQL is. Even if you

[...]
Posted by pgCMH - Columbus, OH on 2018-05-03 at 04:00

Hey everyone! Just a quick note to let you all know that both CJ and myself are manning the PostgreSQL community booth at this year’s PyCon next week in Cleveland, OH.

The Python and PostgreSQL communities have a long history together, and we’re super excited to be able to represent PostgreSQL at the largest annual gathering of Pythonistas. If you can make the drive, come on up to Cleveland, say hi, and learn some Python!

https://us.pycon.org/2018/#!