From: | Euler Taveira <euler(at)timbira(dot)com(dot)br> |
---|---|
To: | hillel(dot)eilat(at)attunity(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATE TABLE" command |
Date: | 2017-08-07 13:03:20 |
Message-ID: | CAHE3wgiZArC5kPNt3CVRGYA7H5O_WnvjosbFnTxrp+aYCTxtxg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
2017-08-07 9:20 GMT-03:00 <hillel(dot)eilat(at)attunity(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-08-07 13:19:26 | Re: Error of insert Foreign table with postgres_fdw |
Previous Message | hillel.eilat | 2017-08-07 12:20:30 | BUG #14771: "Logical decoding" does not cover the impact of "TRUNCATE TABLE" command |