pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert
2+ messages / 2 participants
[nested] [flat]

* PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert
@ 2023-04-05 09:09 Pranav Hegde <pranavh4@gmail.com>
  2023-04-05 14:24 ` Re: PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Pranav Hegde @ 2023-04-05 09:09 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Currently in the PgOutput messages for Insert statements, there is no way
to differentiate if the column was explicitly set to null in the insert
statement, or whether that column was omitted in the insert statement and
thus set to null (or default value)

For example, consider the below table:




*CREATE TABLE test_table (    id int primary key,    text_col_1 text
default 'default_text_1',    text_col_2 text default 'default_text_2');*

Now if we insert a row using:

*INSERT INTO test_table (id, text_col_1) VALUES (1, null);*
In the above query I am explicitly setting *text_col_1* to null, and I am
omitting *text_col_2* which will be populated with the default value.
The PgOutput replication message classifies both *text_col_1* and
*text_col_2* values as NULL. (Refer Logical Replication Message Format
<https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html;
)
Thus we are not able to identify which column was explicitly set to null
and which column was set to the default value.
This causes issues when we are parsing the message, especially in libraries
such as Debezium, where it returns the default value for the column, even
though in the actual SQL query we set that column to be explicitly null.
Thus if there was a way to differentiate between the two cases in the
replication message itself, then the appropriate action can be taken in
downstream pipelines (set to null or set to default value)

Was wondering if there is a way to overcome this issue in the current
pgoutput plugin or the code needs to be changed to account for this case.
Any help would be appreciated

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert
  2023-04-05 09:09 PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert Pranav Hegde <pranavh4@gmail.com>
@ 2023-04-05 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2023-04-05 14:24 UTC (permalink / raw)
  To: Pranav Hegde <pranavh4@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Pranav Hegde <pranavh4@gmail.com> writes:
> Currently in the PgOutput messages for Insert statements, there is no way
> to differentiate if the column was explicitly set to null in the insert
> statement, or whether that column was omitted in the insert statement and
> thus set to null (or default value)

Nope.  Replication works at the data level, it has no idea how that
data was formed originally.

> Thus we are not able to identify which column was explicitly set to null
> and which column was set to the default value.
> This causes issues when we are parsing the message, especially in libraries
> such as Debezium, where it returns the default value for the column, even
> though in the actual SQL query we set that column to be explicitly null.

If you have code that changes the data supplied, that code is wrong.
We are not likely to change the replication protocol to support it.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2023-04-05 14:24 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-05 09:09 PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert Pranav Hegde <pranavh4@gmail.com>
2023-04-05 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox