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

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 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@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'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