Postgres Pro
Downloads
Home   >   mailing lists

Thread: [HACKERS] Logical decoding truncate

[HACKERS] Logical decoding truncate

From
"Friedrich, Steffen"
Date:

Hi,

 

I am writing a logical decoding output plugin decoding WAL to SQL which is finally applied to target database.

 

Is it possible to decode a TRUNCATE statement and the tables involved? 

Assuming the SQL statement "TRUNCATE x, y;",  I am interested in decoding the operation TRUNCATE and the corresponding tables x and y so that I can reconstruct the SQL statement/transaction.

Is that possible?

If so, can you please provide an example or point me into the right direction?

 

I am currently looking at the structures provided to the commit callback e.g. ReorderBufferTXN but so far I have not been able to find the information I am looking for. What I found out is that the output plugin's begin and commit callbacks are called with has_catalog_changes=1. The change callback is not called for a TRUNCATE.

 

Thank you,

Steffen

WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: Paderborn
Registergericht Paderborn HRB 3507
Geschäftsführer: Dr. Jürgen Wunram (Vorsitzender), Christopher A. Chapman, Olaf Heyden, Dr. Ulrich Näher, Rainer Pfeil
Vorsitzender des Aufsichtsrats: Dr. Alexander Dibelius
Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193

Diese E-Mail enthält vertrauliche Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

This e-mail may contain confidential information.
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

Re: [HACKERS] Logical decoding truncate

From
Euler Taveira
Date:
2017-05-11 4:23 GMT-03:00 Friedrich, Steffen <Steffen.Friedrich@dieboldnixdorf.com>:

I am writing a logical decoding output plugin decoding WAL to SQL which is finally applied to target database.

 

Is it possible to decode a TRUNCATE statement and the tables involved? 


Yes, use event triggers. You can decode whatever DDL command you want.
 

Assuming the SQL statement "TRUNCATE x, y;",  I am interested in decoding the operation TRUNCATE and the corresponding tables x and y so that I can reconstruct the SQL statement/transaction.

Is that possible?

If so, can you please provide an example or point me into the right direction?

 

Take a look at BDR code (bdr_queue_ddl_commands and bdr_queue_dropped_objects) [1]. It implements DDL replication too.
--
   Euler Taveira                                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento