Re: BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATE TABLE" command

Euler Taveira euler(at)timbira(dot)com(dot)br
Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Andres Freund <andres(at)anarazel(dot)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 a truncate 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 the source 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 flavor of 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/or intervention 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(at)anarazel(dot)de]
Sent: Monday, August 07, 2017 11:35 PM
To: Hillel Eilat <Hillel(dot)Eilat(at)attunity(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)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(dot)eilat(at)attunity(dot)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