Re: [BUGS] BUG #14771: "Logical decoding" does not cover the impactof "TRUNCATE TABLE" command - Mailing list pgsql-bugs

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