Thread: [BUGS] BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATETABLE" command
[BUGS] BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATETABLE" command
From
hillel.eilat@attunity.com
Date:
The following bug has been logged on the website: Bug reference: 14771 Logged by: Hillel Eilat Email address: hillel.eilat@attunity.com PostgreSQL version: 9.4.4 Operating system: Windows 7 Description: I work on a mission of synchronizing data between PostgreSQL database and a "foreign" target database of choice. A simple SQL based "Logical Decoding" framework is used for capturing "INSERT,UPDATE,DELETE" operations which are applied on the PostgreSQL side. These are applied onto the target DB thereafter - thus - both sides remain intact with respect to their data contents. Yet - this synchronization pattern does not cover the case of "TRUNCATE TABLE" command. From "Logical Decoding" perspective - "TRUNCATE TABLE" is logically equivalent to "DELETE FROM TABLE". Both affect the contents of the PostgreSQL database identically. However - "DELETE FROM TABLE" will be reflected at the target database properly - as expected - while "TRUNCATE" will NOT!!. "Logical Decoding" aims to cover ALL changes made onto data in a coherent fashion. "TRUNCATE" stands for a case where change in data contents at PostgreSQL are not handled by "Logical Decoding", hence synchronization is not achieved. Did I miss something? Is it the only case? How can one cope with this deficiency? Regards Hillel. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impactof "TRUNCATE TABLE" command
From
Euler Taveira
Date:
2017-08-07 9:20 GMT-03:00 <hillel.eilat@attunity.com>: > PostgreSQL version: 9.4.4 > Operating system: Windows 7 > Description: > I advise you to use a recent version (10?) because logical decoding has been improved in the last years. > I work on a mission of synchronizing data between PostgreSQL database and a > "foreign" target database of choice. > This is not a bug. > From "Logical Decoding" perspective - "TRUNCATE TABLE" is logically > equivalent to "DELETE FROM TABLE". > Both affect the contents of the PostgreSQL database identically. > However - "DELETE FROM TABLE" will be reflected at the target database > properly - as expected - while "TRUNCATE" will NOT!!. > TRUNCATE is a DDL command. DDL commands are not supported (yet) by logical decoding. > "TRUNCATE" stands for a case where change in data contents at PostgreSQL are > not handled by "Logical Decoding", hence synchronization is not achieved. > Did I miss something? > Is it the only case? > How can one cope with this deficiency? > You have at least 4 options: (i) block TRUNCATE statement (via REVOKE); (ii) write a plugin using ProcessUtility_hook to block TRUNCATE statement; (iii) write a plugin using ProcessUtility_hook to convert it into DELETE FROM table; (iv) modify postgres to support TRUNCATE (logical) replication. -- Euler Taveira Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impactof "TRUNCATE TABLE" command
From
Michael Paquier
Date:
On Mon, Aug 7, 2017 at 3:03 PM, Euler Taveira <euler@timbira.com.br> wrote: > (i) block TRUNCATE statement (via REVOKE); > (ii) write a plugin using ProcessUtility_hook to block TRUNCATE statement; > (iii) write a plugin using ProcessUtility_hook to convert it into > DELETE FROM table; You need to be careful with some caveats, for example this extension I wrote some time ago changes TRUNCATE to DELETE queries on-the-fly with the utility hook: https://github.com/michaelpq/pg_plugins/tree/master/pg_trunc2del However you'd need to do more for: - TRUNCATE CASCADE - triggers firing on DELETE and TRUNCATE Still the idea is funny. -- Michael -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impactof "TRUNCATE TABLE" command
From
Andres Freund
Date:
Hi, On 2017-08-07 12:20:30 +0000, hillel.eilat@attunity.com wrote: > From "Logical Decoding" perspective - "TRUNCATE TABLE" is logically > equivalent to "DELETE FROM TABLE". It's absolutely not. A DELETE FROM TABLE has to include all the deleted rows (think of concurrency and servers that don't match), whereas a truncate doesn't include that. So changing TRUNCATE wouldn't be appropriate. > Both affect the contents of the PostgreSQL database identically. > However - "DELETE FROM TABLE" will be reflected at the target database > properly - as expected - while "TRUNCATE" will NOT!!. > "Logical Decoding" aims to cover ALL changes made onto data in a coherent > fashion. > "TRUNCATE" stands for a case where change in data contents at PostgreSQL are > not handled by "Logical Decoding", hence synchronization is not achieved. > Did I miss something? > Is it the only case? Others remarked on this. > How can one cope with this deficiency? The best solution imo is to have a TRUNCATE trigger that leads to the truncation being logged. Either by having a 'ddl commands' table, or by using wal messages. Greetings, Andres Freund -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impactof "TRUNCATE TABLE" command
From
Hillel Eilat
Date:
Euler Taveira euler@timbira.com.br Michael Paquier <michael.paquier@gmail.com> Andres Freund <andres@anarazel.de> Re: BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATE TABLE" command Thanks. TRUNCATE == DELETE FROM TABLE? Andres Freund states: >>It's absolutely not. >>A DELETE FROM TABLE has to include all the deleted rows (think of concurrency and servers that don't match), whereas atruncate doesn't include that. >>So changing TRUNCATE wouldn't be appropriate. From application perspective, this argument does not count. A replication task is committed to achieve full synchronization between both sides. TRUNCATE does not comply with this commitment. Though - technically - Andres' statement may be correct. But it is just a matter of how the captured data is logged, transmitted and processed by "Logical Decoding". Externally - a REPLICATION process is indifferent to these aspects. It expects to maintain a fully synchronized couple. >> The best solution IMO is to have a TRUNCATE trigger that leads to the truncation being logged. Unfortunately - I have minimal (actually - 0) degree of flexibility in making any modifications and/or intervention in thesource PG database. Euler Taveira states: >> This is not a bug. As explained above - one would consider it as a "conceptual bug" at least. >> TRUNCATE is a DDL command. DDL commands are not supported (yet) by logical decoding. After issuing TRUNCATE command, table's contents are changed, much like in DELETE. Consequently - TRUNCATE does have a flavorof a DML. The internal DDL aspects are not related to that point. Other DDL-s of interest ([CREATE | DROP | ALTER] TABLE) are tracked / captured in my application via a "ddl_command_end /Event Trigger". This trigger does not fire when TRUNCATE command is issued. Why that? It is considered as a DDL - isn't it? Michael Paquier wrote: >> Change TRUNCATE to DELETE queries on-the-fly with the utility hook As mentioned above - unfortunately - I have minimal (actually - 0) degree of flexibility in making any modifications and/orintervention in the backend PG database / server. Currently - I use plain "test_decoding" plugin - with no backend programming whatsoever. Hillel. -----Original Message----- From: Andres Freund [mailto:andres@anarazel.de] Sent: Monday, August 07, 2017 11:35 PM To: Hillel Eilat <Hillel.Eilat@attunity.com> Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATE TABLE" command Hi, On 2017-08-07 12:20:30 +0000, hillel.eilat@attunity.com wrote: > From "Logical Decoding" perspective - "TRUNCATE TABLE" is logically > equivalent to "DELETE FROM TABLE". It's absolutely not. A DELETE FROM TABLE has to include all the deleted rows (think of concurrency and servers that don'tmatch), whereas a truncate doesn't include that. So changing TRUNCATE wouldn't be appropriate. > Both affect the contents of the PostgreSQL database identically. > However - "DELETE FROM TABLE" will be reflected at the target database > properly - as expected - while "TRUNCATE" will NOT!!. > "Logical Decoding" aims to cover ALL changes made onto data in a > coherent fashion. > "TRUNCATE" stands for a case where change in data contents at > PostgreSQL are not handled by "Logical Decoding", hence synchronization is not achieved. > Did I miss something? > Is it the only case? Others remarked on this. > How can one cope with this deficiency? The best solution imo is to have a TRUNCATE trigger that leads to the truncation being logged. Either by having a 'ddl commands'table, or by using wal messages. Greetings, Andres Freund -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs