Search Postgresql Archives

PgOutput Replication Message Format - Differentiate between explicit NULL and Omitted Columns during Insert

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux