Get the fields that are updated

[gauravsaini23]

This is for the 'UPDATE' events. Is there a way to get a list of fields that are updated rather than having all the values?
There is a discussion here on #188 but it is not very clear if it is possible from wal2json or Postgres itself does not provide any option. Please clarify.

[eulerto]

No. That sentence was referring to columns that is stored in a TOAST table.

[gauravsaini23]

@eulerto - Makes sense. So, is there a way to get the fields that are actually updated in Postgres? Just wanted to understand the problem area if it's Postgres or wal2json?

[eulerto]

wal2json does not provide an option to provide only columns that were updated. You have to compare/modify the JSON on client-side. What is your use case?

[gauravsaini23]

How can I compare/modify the JSON when the object is already updated at this point?

My use case is more like CDC. We are listening to Postgres updates using wal2json and based on them will be updating another application with the fields that are updated.

[ledbruno]

I have similar question: is there a way to receive previous state on the event? Something similar to debezium
https://debezium.io/documentation/reference/configuration/event-flattening.html

[tbussmann]

For my understanding, the values available in the oldkeys structure depend on the REPLICA IDENTITY-Parameter of the affected table. If you set this to FULL, you should be able to retrieve the full old values in update and delete and can calculate the differences with something like

SELECT data,  
	after::jsonb, 
	before::jsonb,
	(after - before)::jsonb as diff
FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL, 'pretty-print', '1', 'add-msg-prefixes', 'wal2json')
	CROSS JOIN LATERAL hstore(ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'columnnames')), 
		ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'columnvalues'))) as after
	CROSS JOIN LATERAL hstore(ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'oldkeys'->'keynames')), 
		ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'oldkeys'->'keyvalues'))) as before 
;

(Note: this is just a PoC, it only looks to the first change. This can certainly be simplified with more JSON magic. I used a little hstore tick, as there isn't a easy jsonb - jsonb operator defined. Format 2 is likely better suited for this usecase. Here, you would look to the columns and identity arrays in the dataJSON)

[iloveshare]

For my understanding, the values available in the oldkeys structure depend on the REPLICA IDENTITY-Parameter of the affected table. If you set this to FULL, you should be able to retrieve the full old values in update and delete and can calculate the differences with something like

SELECT data,  
	after::jsonb, 
	before::jsonb,
	(after - before)::jsonb as diff
FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL, 'pretty-print', '1', 'add-msg-prefixes', 'wal2json')
	CROSS JOIN LATERAL hstore(ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'columnnames')), 
		ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'columnvalues'))) as after
	CROSS JOIN LATERAL hstore(ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'oldkeys'->'keynames')), 
		ARRAY(SELECT jsonb_array_elements_text(data::jsonb->'change'->0->'oldkeys'->'keyvalues'))) as before 
;

(Note: this is just a PoC, it only looks to the first change. This can certainly be simplified with more JSON magic. I used a little hstore tick, as there isn't a easy jsonb - jsonb operator defined. Format 2 is likely better suited for this usecase. Here, you would look to the columns and identity arrays in the dataJSON)

Oooooops! This really works! I solved the problem by alter the table REPLICA IDENTITY by:
ALTER TABLE users REPLICA IDENTITY FULL;

Then I get the full old keys:

[gauravsaini23]

@tbussmann - This works like a charm. It is providing the fields in DIFF that are NULL or changed. Thanks for it.