Обсуждение: [BUGS] BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATETABLE" command

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

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

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

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

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