Deferred PK are not recognized

[quentinbricard]

Hi

I have an issue with a table which has a primary key defined as DEFERRABLE INITIALLY DEFERRED.

Here is postgres setup:

wal_level = logical
max_wal_senders = 3
max_replication_slots = 3

The PK is ignored and any data processed as an UPDATE or DELETE operation is not written by wal2json plugin.
Looking at the code, I found:

/* Make sure rd_replidindex is set */
RelationGetIndexList(relation);
...
if (!OidIsValid(relation->rd_replidindex) && relation->rd_rel->relreplident != REPLICA_IDENTITY_FULL)

RelationGetIndexList does not seem to set the right PK.

I created a small test case to show this behaviour (I updated the given sample).

$ cat /tmp/deferredPK.sql 
CREATE TABLE table_with_deferred_pk (a SERIAL, b VARCHAR(30), c TIMESTAMP NOT NULL, PRIMARY KEY(a, c) DEFERRABLE INITIALLY DEFERRED);

BEGIN;
INSERT INTO table_with_deferred_pk (b, c) VALUES('Backup and Restore', now());
INSERT INTO table_with_deferred_pk (b, c) VALUES('Tuning', now());
INSERT INTO table_with_deferred_pk (b, c) VALUES('Replication', now());
UPDATE table_with_deferred_pk SET b = 'Tuning - update' where b = 'Tuning';
DELETE FROM table_with_deferred_pk WHERE a < 3;
COMMIT;

$ psql -At -f /tmp/deferredPK.sql  postgres
CREATE TABLE
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
UPDATE 1
DELETE 2
COMMIT

We can see that only INSERT operations are written by wal2json

$ pg_recvlogical -d postgres --slot test_slot --start -o pretty-print=1 -f -
{
	"change": [
	]
}
WARNING:  table "table_with_deferred_pk" without primary key or replica identity is nothing
WARNING:  table "table_with_deferred_pk" without primary key or replica identity is nothing
WARNING:  table "table_with_deferred_pk" without primary key or replica identity is nothing
{
	"change": [
		{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_deferred_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [1, "Backup and Restore", "2018-04-19 07:19:02.867699"]
		}
		,{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_deferred_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [2, "Tuning", "2018-04-19 07:19:02.867699"]
		}
		,{
			"kind": "insert",
			"schema": "public",
			"table": "table_with_deferred_pk",
			"columnnames": ["a", "b", "c"],
			"columntypes": ["integer", "character varying(30)", "timestamp without time zone"],
			"columnvalues": [3, "Replication", "2018-04-19 07:19:02.867699"]
		}
	]
}

Note:

• Changing the PK to DEFERRABLE INITIALLY IMMEDIATE produces the same behaviour

[quentinbricard]

Any update on this?

My postgres version is 10.1

[eulerto]

@quentinbricard don't use deferred PK because it is not available to logical decoding. It means the PK won't be available for plugins (not just wal2json). It is like that since first version of logical decoding.
Since it is not documented I doubt it is an oversight. I'll dig into postgres source code later to confirm this limitation.

[jmealo]

@eulerto: Did you get a chance to confirm this? Maybe we can just document the limitation and close it.

[eulerto]

I concluded that it is an oversight and submitted a patch to -hackers ML.