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.

HighGo Software Inc. (Canada) is pleased to announce the first GitHub community release of MongoDB Logical Decoding plugin v1.0.6 (wal2mongo), which can be used to replicate PostgreSQL database changes to an output format that can be directly fed into the mongo client tool to achieve logical replication between PostgreSQla and MongoDB. Wal2mongo plugin is useful for a case where PostgreSQL is used as the main raw data source to collect data from outside world but MongoDB is used internally for data analytics purposes. Manual data migration between PostgreSQL and MongoDB poses a lot of potential problem and having a logical decoding plugin like wal2mongo can help reduce the data migration complexity between the two databases.

Wal2mongo plugin produces outputs that can be understood by MongoDB without further parsing. For this reason, it can be used with the pg_recvlogical front end tool to achieve a very simple logical replication between the two databases. Simply feed the outputs of pg_recvlogical to the mongo client tool like this.

pg_recvlogical -S slot -f - --start | mongo

Both source and binary releases (for CentOS6, 7) of wal2mongo plugin can be found here

Feature Highlight

  • Rich support in data type translation between PostgreSQL and MongoDB
  • Support complex data type translation such as arrays, XML, JSON, timestamp …etc
  • Support database WAL changes caused by INSERT, UPDATE, and DELETE clauses.
  • Support 2 output modes: regular output mode and transaction output mode (for multi-document transaction control)
  • Support MongoDB version 3.6, 4.0 and above
  • Support multi-publisher translation mode
  • Support change stream identification using a combination of cluster name, database name and replication slot name such * that when multiple PostgreSQL servers using the same decoding plugin, the destination MongoDB server is able to distinguish the source of the change stream
  • simple, lightweight and feature-rich.
  • Can be paired with HighGo replication application to ac
[...]
Posted by Bruce Momjian in EnterpriseDB on 2020-05-29 at 14:00

There is still significant confusion about characters sets, encodings, and collations. This is because in the real, non-digital world, we usually treat languages, their characters, and ordering as unified, but in the digital world, they are treated separately, and their distinction can be hard to visualize.

These two posted queries illustrate collation in a very creative way. The first query, SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "C", outputs characters in their "C" binary order, with ASCII as the first 128 characters, successive groups of languages following, and ending with pictographic languages.

The second query, SELECT chr(i) FROM generate_series(1, 50000) i ORDER BY chr(i) COLLATE "en_US.utf8", outputs the same 50,000 characters in "United States English" UTF8 order. The output starts with pictographic languages, not ASCII. The Latin alphabet appears, but not until line 19068. What is interesting is that there are 118 symbols grouped together that look like 'a', 'a' with diacritics, or have 'a' as part of their symbol. Then 'b' appears with a group of 38 symbols that look like or use 'b', and so on through the Latin alphabet. (If you highlight a character and paste it into a search box, Google will tell you about that Unicode symbol.)

Continue Reading »

Generating simple data sets

Before we get started I want to introduce my favorite set-returning functions which can help you to generate sample data:


test=# SELECT * FROM generate_series(1, 10) AS x;
x
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

All we do here is simply to generate a list from 1 to 10 and print it on the screen. Let us play around with windowing a bit now: There are two cases we got to keep in mind. If the OVER-clause is empty it means that the entire data set is used. If we use ORDER BY it is only the data set up to the point in the sorted list. The following listing contains an example:


test=# SELECT *,
array_agg(x) OVER (),
array_agg(x) OVER (ORDER BY x)
FROM generate_series(1, 10) AS x;
x | array_agg | array_agg
----+------------------------+------------------------
1 | {1,2,3,4,5,6,7,8,9,10} | {1}
2 | {1,2,3,4,5,6,7,8,9,10} | {1,2}
3 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3}
4 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4}
5 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5}
6 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6}
7 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7}
8 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8}
9 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8,9}
10 | {1,2,3,4,5,6,7,8,9,10} | {1,2,3,4,5,6,7,8,9,10}
(10 rows)

As you can see the last column keeps accumulating more values.

PostgreSQL: ROWS BETWEEN … PRECEDING …. AND … FOLLOWING

Often it is necessary to limit the set of data used by the aggregation function. ROWS BETWEEN … PRECEDING … AND … FOLLOWING allows you to do exactly that. The following example shows how this works:


test=# SELECT *, array_agg(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM generate_series(1, 10) AS x;
x | array_agg
----+-----------
1 | {1,2}
2 | {1,2,3}
3 | {2,3,4}
4 | {3,4,5}
5 | {4,5,6}
6 | {5,6,7}
7 | {6,7,8}
8 | {7,8,9}
9 | {8,9,10}
10 | {9,10}
(10 rows)

What you see is that the data fed to array_agg is seriously restricted. But, the restriction we are using here is a static one. The constants are hardwired. In some cases you might need

[...]
Posted by Luca Ferrari on 2020-05-28 at 00:00

Understanding the relationship between LSN and WAL file names.

WAL, LSN and File Names

PostgreSQL stores changes that is going to apply to data into the Write Ahead Logs (WALs), that usually are 16 MB each in size, even if you can configure your cluster (starting from version 11) to different sizes.
PostgreSQL knows at which part of the 16 MB file (named segment) it is by an offset that is tied to the Log Sequence Number (LSN). Let’s see those in action.
First of all, let’s get some information about the current status:

testdb=> SELECT pg_current_wal_lsn(),
          pg_walfile_name( pg_current_wal_lsn() );;
-[ RECORD 1 ]------|-------------------------
pg_current_wal_lsn | C/CE7BAD70
pg_walfile_name    | 000000010000000C000000CE

The server is currently using the WAL file named 000000010000000C000000CE. It is possible to see the relationship between the LSN, currently C/CE7BAD70 and the WAL file name as follows. The LSN is made up by three pieces: X/YYZZZZZZ where:

  • X represents the middle part of the WAL file name, one or two symbols;
  • YY represents the final part of the WAL file name;
  • ZZZZZZ are six symbols that represents the offset within the file name.
    Therefore, given the LSN C/CE7BAD70 we can assume that the middle part of the WAL file name will be C and the last part will be CE, both zero padded to 8 symbols, so respectively 0000000C and 000000CE. Concatenated togehter, they provide us with a file name that ends with 0000000C000000CE. The initial part of the filename is still missing, and that is the timeline the server is running on, in this case 1, zero padded as the other parts, so 00000001 that provides us the final name 000000010000000C000000CE.
    To summarize, the following is the correspondance between the single parts:
LSN  ->              C  /     CE      7BAD70
WAL  -> 00000001 0000000C 000000CE



Please consider that the above example is just to show you the concept, but it is better to use the function pg_walfile_n

[...]
Posted by Bruce Momjian in EnterpriseDB on 2020-05-27 at 19:30

You might have seen that there is a pg_lsn data type:

test=> \dTS pg_lsn
              List of data types
   Schema   |  Name  |       Description
------------+--------+-------------------------
 pg_catalog | pg_lsn | PostgreSQL LSN datatype

Continue Reading »

Constraints are used to ensure that data in the database reflects the assumptions of the data model.

  • Do foreign keys match up to corresponding keys? (REFERENCES)
  • Are mandatory columns filled in? (NOT NULL)
  • Are unique values columns in fact unique? (UNIQUE)
  • Do other data quality rules pass? (CHECK)

Why enforce data quality rules in the database, and not at the application tier? Because if you qualify all your data at the application tier, you have to duplicate all that logic whenever you add another application that writes to the database.

If you are lucky enough to write a system successful enough to be in production for more than a couple years, the odds of multiple write applications proliferating rapidly converge to 100%. Enforcing core data quality rules in the database is a nice way to ground your system with solid assumptions about data integrity.

Our interactive learning portal has a whole section on the use of non-spatial constraints, if you're interested. There's also a video.

What about spatial, though? Thanks to the magic of PostgreSQL run-time extensions, the geometry type is just another type and all the machinery of constraint checking works just as well for geometry as any other type.

Here are a few examples of simple and complex constraints that spatial database users like to enforce on the geometry data.

Posted by Álvaro Hernández in OnGres on 2020-05-26 at 10:50

Heisenberg’s uncertainty principle

In quantum mechanics, the uncertainty principle […] asserts a fundamental limit to the precision with which the values for certain pairs of physical quantities of a particle, such as position, x, and momentum, p, can be predicted from initial conditions.

Uncertainty principle, Wikipedia

In layman’s terms, what Heisenberg’s principle states is that by measuring a property of a system, you may be altering that system itself: your observation becomes a distorted version of the reality.

In most cases, this distortion is negligible and we can simply ignore it. If we use a thermometer to measure someone’s temperature, some heat will be transferred from the person to the termometer, effectively lowering the person’s temperature. But it should not be noticeable, and well below the error margin of the thermometer.

But what happens when the measurement may not just affect, but rather completely ruin the measurement?

Where the potential lie is

You are probably resorting a lot to use Postgres’ EXPLAIN ANALYZE command when you want to optimize a query’s performance. You probably look at the query nodes, see which ones have the highest execution time and then try to optimize them. The costlier the node is, the biggest return of investment you get if you can optimize it. Obviously, a query optimization may change the query plan altogether, but you get the point: you want to know where most of the query execution time is going.

Now grab your favorite Postgres and run the following commands:

create table i1 as select i from generate_series(1,1000*1000) as i;
create table i2 as select i from generate_series(1,1000) as i;
analyze;
explain analyze select sum(i1.i * i2.i) from i1 inner join i2 using (i);

Note that the first analyze command is not related at all with the explain analyze command that follows it.


Run query. Note the time reported by explain analyze. Now run the query and note the execution time without explain analyze. You can

[...]

The functionality of using table partitions to speed up queries and make tables more manageable as data amounts grow has been available in Postgres for a long time already, with nicer declarative support available from v10 – so in general it’s a known technique for developers. But what is not so uniformly clear is the way how low-level partition management is done…as Postgres leaves it to users but no real standard tools or even concepts have emerged.

So it happened again that a customer approached me with a plan to use partitions for an action logging use case…but could not find a nice tool to his liking after a session of googling and was looking for some advice. Sure I know some tools…but my actual advice seemed quite unconventional and surprising for him: Why all the craziness with tools for this simple task? Why not throw together and use something simple yourself?

The problem with tools

Of course tools in a broader sense are necessary and great, we wouldn’t be here today without our stone axe and especially software developers thrive on skilful use of editors, programming languages, etc. But the thing with tools is that you should really know when you actually need to use one! And when it comes to simple, isolated tasks I’d argue that it’s better to delay picking one until you absolutely need one and understand the “problem space” on a sufficiently good level. As adopting some deeply integrated software tools also bring risks to the table – like the added additional complexity. When something then goes sour you’re usually up for some serious sweating / swearing as you quickly need to gain a deep understanding of the tool’s internals (as it was mostly set up some X months or years ago and who remembers all this stuff) – a tough thing to do when the production database is unable to process user queries and your manager is breathing down your neck.

Also most tools or products in the PostgreSQL realm are in the end just GitHub repos with code, but without the associated business model and sup

[...]

Event triggers are a very powerful mechanism to react to data structure changes in PostgreSQL.

Inspecting Command Tags and Events in event triggers

While preparing an example for a course of mine about event triggers, I thought I’ve never proposed a catch-all event trigger debugging use case. So, here it is.
Event Triggers are a powerful mechanism that PostgreSQL provides to react to database schema changes, like table or column addition and deletion, object creation, and so on. The official documentatio already presents a couple of example about dropping objects or rewriting tables, so my little example is about more common commands. I create the following function:

CREATE OR REPLACE FUNCTION
f_event_trigger_demo()
RETURNS EVENT_TRIGGER
AS
$code$
DECLARE
event_tuple record;
BEGIN
   RAISE INFO 'Event trigger function called ';
   FOR event_tuple IN SELECT *
                      FROM pg_event_trigger_ddl_commands()  LOOP
                      RAISE INFO 'TAG [%] COMMAND [%]', event_tuple.command_tag, event_tuple.object_type;
   END LOOP;
END
$code$
LANGUAGE plpgsql;

It is quite simple to understand what it does: every time the function is triggered, it asks or the tuples out of the special function pg_event_trigger_ddl_commands(), that provides one tuple for every single command executed. Why multiple tuples? Because you could execute one command that explodes into different sub-commands.
Than, simply, the function does print the command tag and the object type.
Usually command tags are uppercase, while object types are lowercase.
The trigger can be created as follows:

testdb=# create event trigger tr_demo on ddl_command_end execute function f_event_trigger_demo();

It is now simple enough to test the trigger:

testdb=# create table foo();
INFO:  Event trigger function called 
INFO:  TAG [CREATE TABLE] COMMAND [table]
CREATE TABLE

testdb=# alter table foo add column i int default 0;
INFO:  Event trigger function called 
INFO:  TAG [ALTER TABLE] COMMAND [table]
ALTER TABLE
[...]
Posted by Luca Ferrari on 2020-05-26 at 00:00

It’s time to test the new PostgreSQL 13 release!

PostgreSQL 13 beta 1 on FreeBSD via pgenv

Five days ago PostgreSQL 13 beta 1 has been released!
It’s time to test the new awesome version of our beloved database. Installing from source is quite trivial, but why not using pgenv to such aim?
Installing on my FreeBSD machine with pgenv is as simple as:

luca@miguel ~ % pgenv build 13beta1
...
PostgreSQL, contrib, and documentation installation complete.
pgenv configuration written to file /home/luca/git/pgenv/.pgenv.13beta1.conf
PostgreSQL 13beta1 built


Are you ready to test it?
Activate it and enjoy:

luca@miguel ~ % pgenv use 13beta1
...
server started
PostgreSQL 13beta1 started
Logging to /home/luca/git/pgenv/pgsql/data/server.log


luca@miguel ~ % psql -U postgres -c "SELECT version();" template1
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 13beta1 on x86_64-unknown-freebsd12.1, compiled by gcc (FreeBSD Ports Collection) 9.2.0, 64-bit
(1 row)

Enjoy!

Introduction

People who have worked with databases for a number of years will inevitably have encountered situations where some abandoned session caused table locks never to be released and basically ground a service to a halt. The most frequent culprit is an idle transaction on an open psql session behind a screen session on someone’s development server. This occurred so frequently that in 2016, Vik Fearing, Stéphane Schildknecht, and Robert Haas introduced idle_in_transaction_session_timeout into Postgres’ list of parameters in the release of version 9.6. However, an uncommitted idle transaction isn’t the only way to stick a wedge in the gears of a database system. Another one that we’ll discuss today involves prepared transactions, which have the potential to do the same thing.

What’s a Prepared Transaction?

Prepared transactions are Postgres’ implementation of two-phase commit. The aim of this feature is to synchronize the commit of several transactions as though they were one transaction. A possible use case for this is a system where there are several databases (think several intentories across geographic regions – something of that sort), and all need to be updated at the same time. The typical set of prepared transactions might look something like this:

  1. Issue BEGIN on any relevant database/node
  2. Do your CRUD
  3. Issue PREPARE TRANSACTION 'any_custom_transaction_name' to disassociate the transaction from your pid and store it in the database
  4. Once all databases/nodes have successfully finished the PREPARE TRANSACTION step, and you’re ready to commit, issue COMMIT PREPARED 'your_transaction_name' to commit the changes to disk.

If at any point there’s an error and the transactions need to be rolled back, a ROLLBACK PREPARED 'your_transaction_name' will roll back your transaction (this will need to be run on all relevant databases).

Where’s the Danger?

In the third step above, I hinted at the idea that a prepared transaction would be disassociated with your Postgres

[...]

Postgres already documents the ability to backup the database cluster using file system snapshots. Unfortunately, database clusters that use tablespaces often cannot use this method if the storage system doesn't support simultaneous snapshots across file systems.

However, simultaneous snapshots across file systems might not be a hard requirement for Postgres snapshot backups. It might be possible for snapshots to be non-simultaneous as long as the write-ahead log that spans the time frame between snapshots is included in the backup, and checkpoints do not happen during that time frame.

Internally, starting Postgres from a snapshot backup replays write-ahead log records to make a single file system snapshot consistent. Potentially, it could do the same for non-simultaneous snapshots of multiple file systems. However, documenting this, giving users a reliable list of steps to perform, and making sure it always works is probably too complex to justify.

Posted by Andreas 'ads' Scherbaum on 2020-05-25 at 14:00
PostgreSQL Person of the Week Interview with Paul Ramsey: I’m a middle-aged guy from the blessed west coast of Canada, where the sky and the mountains meet the sea. Raising a family takes up most of my time, but I’ve managed to carve out personal time for fitness recently, and exploring the area on my bike. I grew up in the interior of British Columbia, but have lived in Victoria, on Vancouver Island, for most of my adult life.

pgBackRest is a well-known powerful backup and restore tool. Old backups and archives are removed by the expire command based upon the defined retention policy.

Since the latest version published last month, new features regarding retention have been committed. We’ll here first overview those changes and then make a tour of the retention policy options that should be available in the next release.


New features

Time-based retention

Add time-based retention for full backups:

The --repo-retention-full-type option allows retention of full backups based 
on a time period, specified in days.

The new option will default to 'count' and therefore will not affect current 
installations. Setting repo-retention-full-type to 'time' will allow the user 
to use a time period, in days, to indicate full backup retention. Using this 
method, a full backup can be expired only if the time the backup completed is 
older than the number of days set with repo-retention-full (calculated from 
the moment the 'expire' command is run) and at least one full backup meets the 
retention period. If archive retention has not been configured, then the default 
settings will expire archives that are prior to the oldest retained full backup. 

For example, if there are three full backups ending in times that are 25 days 
old (F1), 20 days old (F2) and 10 days old (F3), then if the full retention 
period is 15 days, then only F1 will be expired; F2 will be retained because 
F1 is not at least 15 days old.

This possibility opens a lot of opportunities. However, it’s not so obvious that at least one full backup over the time-period defined will be kept.


Expire a specific backup set

Add --set option to the expire command:

The specified backup set (i.e. the backup label provided and all of its 
dependent backups, if any) will be expired regardless of backup retention 
rules except that at least one full backup must remain in the repository.

The description is pretty simple, it will now be possible to expi

[...]

Currently, it is impossible to move tables, indexes, and entire tablespaces from one cluster to another — that is because each table and index file is bound to the cluster's infrastructure because of:

  • Table and index definitions
  • pg_xact (commit/abort/in-progress transaction status records)
  • pg_multixact (used for multi-session row locking)

FYI, you can easily move tablespaces to new directories as long as it remains in the same cluster, and move tables and indexes between tablespaces.

So, how could it be made possible? Freeze can remove references to pg_xact and pg_multixact, assuming there are no active transactions during the freeze operation. Table and index definitions can be more complex, but it certainly seems possible. This requires more research.

If you want to see (and analyze) only a window of data over some continuous data stream in PostgreSQL, one way is to use a specialized tool like the PipelineDB extension. But if you can't do that, e.g. because you are stuck with AWS RDS or for some other reason, streaming data tables, or continuous views, can be implemented with pretty much PostgreSQL alone.

The basic idea is to have a table that allows for fast INSERT operations, is aggressively VACUUMed, and has some key that can be used to prune outdated entries. This table is fed with the events from the data stream and regularly pruned. Voilà: a streaming data table.

We have done some testing with two approaches on an UNLOGGED table, prune on every INSERT, and pruning at reqular intervals. UNLOGGED is not a problem here, since a view on a data stream can be considered pretty much as ephemeral.

The timed variant is about 5x - 8x faster on INSERTs. And if you balance the timing and the pruning interval right, the window size is almost as stable.

The examples are implemented in Python3 with psycopg2. Putting an index on the table can help or hurt performance, INSERT might get slower but pruning with DELETE faster, depending on the size and structure of the data. Feel free to experiment. In our case, a vanilla BRIN index did just fine.

Instead of using an external scheduler for pruning, like the Python daemon thread in the stream_timed_cleanup.py example, other scheduling mechanisms can be of course used, e.g. pg_cron, or a scheduled Lambda on AWS, or similar.

Feel free to experiment and improve...
failover logical replication slots postgresql

PostgreSQLMissing Piece: Failover of the Logical Replication Slot

Logical decoding capability has existed in PostgreSQL for the last several versions, and a lot more functionalities are added over time.  However, one of the very crucial functionalities is still missing in PostgreSQL, which prevents logical replication to be part of critical production environments where downstream expects the logical changes to be streamed over a database connection reliably – even after a high-availability failover.

The root of the problem is that the Logical Replication Slot which exists on the primary is not available in a physical standby.  Meanwhile, almost all PostgreSQL high-availability solutions depend on physical standbys. So if there is a failover, new primary (the old physical standby) won’t be aware of any slots which were existing with old primary. This leads to a completely broken logical replication (not continuous) downstream.  We can create a fresh slot on the new primary and set up a fresh replication, but if the original logical replication was lagging, this leads to data loss.

Logical Replication Slot

 

Designs and Discussions for Solving the Problem

This topic is not new. The historical PostgreSQL community discussion thread is available here. Craig Ringer blogged about it in 2016 following his efforts to provide a patch that should solve the problems. The idea was to write slot-related changes to the WAL logs so that the standby can recreate and maintain slots using the information passed through WAL logs.

Even though this approach of failover slot makes life easy for most of the scenarios, there were some notable objections because a lot more problems around it also needed to be addressed. Ideally, there should be a way to mark whether a slot is a “failover slot” so that the corresponding information will be written to WAL. But every standby which receives the WALs including the cascaded replicas will create and maintain slots. This will result in unnecessary overhead and WAL retention, maintaining old visio

[...]
This article gives a step by step guide to utilizing Machine Learning capabilities with 2UDA. In this article, we will use examples of Animals to predict whether they are Mammals, Birds, Fish or Insects. Software versions We are going to use 2UDA version 11.6-1 to implement the Machine Learning model. This version 11.6-1 combines: PostgreSQL […]

This year, I was so excited about doing a workshop about optimizing Python & Django apps with Postgres superpowers for the PyCon 2020 conference.

Working with other developers on performance is something I always find amazing. So props to the Python people at Microsoft who encouraged my team to create a workshop on Postgres for PyCon 2020. Thank you to Nina Zakharenko, Dan Taylor, & Crystal Kelch.

Alas, we had to change our plans and find other ways to share the PostgreSQL workshop content that we had prepared. So I created a video on the topic of database performance for Django developers, to help teach you the PostgreSQL tips and tricks that have served me well in optimizing my Django apps. These tips are what I call “Postgres superpowers.”

Being myself a developer, and having worked with Django for years, I know that there are a few traps to avoid in order to have fast apps. In this video, I go through a few performance traps to avoid—as well as tools you can use to identify pain points.

Where to find this PyCon video on Django performance

You can find this video on Optimizing Python & Django performance in three different places:

On the Microsoft Board for PyCon2020, you will find a ton of useful videos created by my teammates on the Python team here at Microsoft. I found Nina’s talk on debugging especially interesting! And my Postgres teammate Sai Srirampur also created an (awesome) short demo on Building HTAP applications with Python and Postgres—in Azure.

But before you go watch my PyCon video, let me tell you a few things that I think you will find useful when watching.

Tools to help optimize Python

Django and Postgres have tools for you to use in order to see your SQL queries executed. In my experience, looking into executed SQL queries is the best way to make sure you don’t let your O

[...]
CREATE TABLE one_column_table (field INTEGER);

The above SQL creates ‘one_column_table’ with only a single column. But does the table actually have only one column?

Let’s query the pg_attribute catalog to find out how many columns our one_column_table has.

SELECT attname, attnum, atttypid::regtype from pg_attribute WHERE attrelid = 'one_column_table'::regclass;
 attname  | attnum | atttypid
----------+--------+----------
 tableoid |     -6 | oid
 cmax     |     -5 | cid
 xmax     |     -4 | xid
 cmin     |     -3 | cid
 xmin     |     -2 | xid
 ctid     |     -1 | tid
 field    |      1 | integer
(7 rows)

What?? As per the ‘pg_attribute’ catalog, the table we just created has seven columns.

Somehow PostgreSQL has added six extra columns to our table and all of these implicit columns have -ve ‘attnum’ value.

This post is about these implicit system columns, what data these fields hold, and what we can do with that data.
So let us look at the system columns one by one.

ctid

TID is an acronym for a tuple identifier (row identifier), which is a pair of values (block number, tuple index within the block). Since the data files in PostgreSQL are logically divided into fixed-size blocks (8k by default), the CTID column identifies the physical location of the two within its table.

So what we can do with this CTID column?

Knowing the exact row location of the tuple within the table’s data file can be handy for fixing the data corruption in some cases. but it has another very interesting use cases. This CTID column could be used to identify the unique ROW within the table if it has duplicate data.

For example

Suppose we accidentally inserted the duplicate data in our example ‘one_column_table’ table.

INSERT INTO one_column_table SELECT generate_series(1,5);
--insert the same data 2nd time
INSERT INTO one_column_table SELECT generate_series(1,5);
SELECT * FROM one_column_table;
 field 
-------
     1
     2
     3
     4
     5
     1
     2
     3
     4
     5
(10 rows)

So l

[...]

This email thread is illustrative of why it is unwise to place the Postgres data directory (PGDATA) at the top of a mount point. Instead, create a subdirectory under the mount point and put PGDATA there. This has the advantage of avoiding possible data corruption if mounting fails, and allows more efficient use of pg_upgrade.

MQTT has become a de-facto standard for the transport of messages between IoT devices. As a result, a plethora of libraries and MQTT message brokers have become available. Can we use this to transport messages originating from PostgreSQL?

Aa message broker we use Eclipse Mosquitto which is dead simple to set up if you don't have to change the default settings. Such a default installation is neither secure nor highly available, but for our demo it will do just fine. The event generators are written in Python3 with Eclipse paho mqtt for Python.

There are at least two ways to generate events from a PostgreSQL database, pg_recvlogical and NOTIFY / LISTEN. Both have their advantages and shortcomings.

pg_recvlogical:

  • Configured on server and database level
  • Generates comprehensive information about everything that happens in the database
  • No additional programming neccessary
  • Needs plugins to decode messages, e.g. into JSON
  • Filtering has to be done later, e.g. by the decoder plugin
NOTIFY / LISTEN:
  • Configured on DDL and table level
  • Generates exactly the information and format you program into the triggers
  • Filtering can be done before sending the message
  • Needs trigger programming
  • The message size is limited to 8000 bytes
Examples for both approaches can be found here. The NOTIFY / LISTEN example lacks a proper decoder but this makes be a good excercise to start with. The pg_recvlogical example needs the wal2json plugin, which can be found here and the proper setup, which is also explained in the Readme. Please note, that the slot used in the example is mqtt_slot, not test_slot:


pg_recvlogical -d postgres --slot mqtt_slot --create-slot -P wal2json

Otherwise, setup.sql should generate all objects to run both examples.

Recently we have covered “count” quite extensively on this blog. We discussed optimizing count(*) and also talked about “max(id) – min(id)” which is of course a bad idea to count data in any relational database (not just in PostgreSQL). Today I want to focus your attention on a different kind of problem and its solution: Suppose you want to grant a user access to a certain piece of data only X times. How can one implement that safely?

SQL: Trying the trivial approach

One might argue that this is easy. Here is some pseudo code:

BEGIN;
SELECT count(*)
FROM   log
WHERE  customer_id = 10
       AND tdate = '2020-04-09'

if count >= limit
   ROLLBACK
else
   INSERT INTO log
   “do some work”
   COMMIT;

Looks easy, doesn’t it? Well, not really.

What happens if two people run this concurrently. Both sessions will receive the same result for count(*) and therefore one session will come to the wrong conclusion. Both transactions will proceed and do the work even if only one is allowed to do it. That is clearly a problem.

To solve the problem one could do …

… to eliminate concurrency all together. But, this is clearly a problem because it would block pg_dump or some analytics jobs or even VACUUM. Obviously not an attractive option. Alternatively we could try a SERIALIZABLE transaction but that might lead to many transactions failing due to concurrency issue.

One more problem is that count(*) might be really slow if there are already many entries. In short this approach has many problems:

  • It is buggy
  • It is slow
  • It does not scale
  • Runtime is unpredictable

But how can we do better?

Introducing a “count” table

To solve the problem we can introduce a “count table” but let us start right at the beginning. Let me create a table structure first:

CREATE EXTENSION btree_gist;

CREATE TABLE t_customer
(
    id                    serial PRIMARY KEY,
    customer_email        text NOT NULL UNIQUE,
    login_whatever_else   text
);

CREATE TABLE t_customer_limit
(
    i
[...]
Posted by Bruce Momjian in EnterpriseDB on 2020-05-19 at 03:45

I am planning to virtually attend and present at the Percona Live Online conference tomorrow, May 19. It starts at 10am, Eastern USA time, and spans 24 hours, so it covers every time zone. I am speaking at noon, Eastern USA time.

Attendance is free, so you might want to check it out. I saw some interesting topics on the program. I am also curious to experience a 24-hour virtual conference, though I am unlikely to remain awake that long.

pgBackRest is a well-known powerful backup and restore tool. The 2.26 version has been released on Apr 20, 2020. New features have been developed since then.

Today, let’s have a look at: add backup/expire running status to the info command.

This is implemented by checking for a backup lock on the host where info is running so there are a few limitations:

* It is not currently possible to know which command is running: backup, expire, or stanza-*. 
The stanza commands are very unlikely to be running so it's pretty safe to guess backup/expire. 
Command information may be added to the lock file to improve the accuracy of the reported command.

* If the info command is run on a host that is not participating in the backup, e.g. a standby, then there will be no backup lock. 
This seems like a minor limitation since running info on the repo or primary host is preferred.

Explanation

The info command will try to acquire a lockTypeBackup lock. This type of lock is originally acquired by backup, expire, or stanza-* commands.

The result will be displayed in two ways. First, the status of the text output will receive the backup/expire running extra message. Then, the JSON output will have a specific lock part in the status report. There, we’ll know if the backup lock is held or not. This will allow easier future improvements.

Let’s now see some examples.


Local backup

The first example scenario is a really simple one: local backup.

Before any backup, we typically get this information:

$ pgbackrest --stanza=my_stanza info
stanza: my_stanza
    status: error (no valid backups)
    cipher: none
    ...

$ pgbackrest --stanza=my_stanza --output=json info
[{
        "archive":...,
        "backup":[],
        "cipher":"none",
        "db":...,
        "name":"my_stanza",
        "status":
        {
                "code":2,
                "lock":
                {
                        "backup":{"held":false}
                },
                "message":"no valid backups"
        }
[...]
Posted by Bruce Momjian in EnterpriseDB on 2020-05-18 at 14:15

When we talk about database roles, most people immediately think of login roles, which allow people to log in. However, another user management feature is the ability to create non-login roles, formerly called groups. Non-login roles can also be assigned permissions, e.g., via GRANT, and can have login roles as members. Non-login roles can be even be members of other non-login roles.

What is the value of using non-login roles? They allow a group of people to be assigned as members of a non-login role, and that role can be used to abstract permission assignment. For example, if you have shop foremen, you can configure the login roles of all foremen to be members of a non-login foreman role. As people are added and removed from that staff position, they can be added/removed from the non-login role without the need to change permissions for the foreman role.

A further advantage of non-login roles, as explained in a recent email, is that Postgres can start to suffer performance problems if more than a few dozen roles are granted permission on an object. A much simpler and more manageable solution is to add users to a non-login role and assign object permissions to that non-login role.

Posted by Andreas 'ads' Scherbaum on 2020-05-18 at 14:00
PostgreSQL Person of the Week Interview with Dimitri Fontaine: Hi! My name is Dimitri Fontaine and I’m French, currently living in the greater Paris area. When I’m not sitting in front of a computer for work, I like to be with my kids. We play board games, have fun chats, watch movies together… well or at least try to find a single movie that we would each enjoy watching… I also play the guitar.
Posted by Pavel Stehule on 2020-05-17 at 17:54
I wrote two new features to pspg.

First feature is a option --skip-columns-like. Now, it can be used only for browsing csv or tsv documents. When this option is used, then specified columns (specified by substring of name) are not displayed. It can be useful for psql PostGIS data. More time some GEO data has not sense to display. If you have wide table with lot of columns, then can be nasty to write list of columns every time. Example

export PSPG="--skip-columns-like='geom$'

Then if you use psql, then you can write query like SELECT * FROM roads, and all columns like roads_geom will be removed from presented table. You have to switch output format to csv by \pset format csv (it is available from Postgres 12).

Second new feature is good for wide columns too. If you column is wide, and column name not, then you can see only white space and you have to scroll to left or to right to see column name. With last commit, the column name is almost all time visible - when it is possible.