Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Get the fields that are updated #196

Closed
gauravsaini23 opened this issue Mar 17, 2021 · 8 comments
Closed

Get the fields that are updated #196

gauravsaini23 opened this issue Mar 17, 2021 · 8 comments
Labels

Comments

@gauravsaini23
Copy link

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
Copy link
Owner

eulerto commented Mar 24, 2021

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

@gauravsaini23
Copy link
Author

@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
Copy link
Owner

eulerto commented Mar 25, 2021

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
Copy link
Author

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
Copy link

ledbruno commented Apr 6, 2021

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

image

@tbussmann
Copy link

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
Copy link

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:
image
image

@gauravsaini23
Copy link
Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

5 participants