Thread: [pg_restore] Triggers handling

Hi all,

On 9.1, the doc[1] says that --disable-triggers is only relevant when processing data-only dumps. What about when it's
*not*data-only? 

Say I have a table T (id, column1, column2, ...) and another table T_integrity (id, hash), where "hash" corresponds to
md5(correspondingrow in T). T_integrity is updated by a trigger watching T for insertions/updates/deletions. 
Now I dump (pg_dump -Fc) the base, i.e. a set of tables like T, each with its corresponding T_integrity. My question
is,if the dump is not data-only, is there a formal definition somewhere of pg_restore's behaviour regarding triggers? 

Initially, I thought pg_restore would recreate the tables, register the triggers, and then fill the tables as a series
ofINSERT INTO statements, so the restored T_integrity would end up with duplicated rows (one row from the dump, another
fromthe trigger). As it happened, that wasn't the case. Empirically, pg_restore seems to copy/paste the tables'
contents,and THEN enable triggers (meaning updating T after pg_restore causes an update in T_integrity). 

That's great, since that fits my intended use case (check the hashes after restoring, which would be tautological
and/orconfusing if pg_restore executed triggers). However, I'd like to see it written somewhere that this behaviour is
intendedrather than coincidental, i.e. : 

1) I can expect pg_restore to not execute triggers on regular (not data-only) dumps, without specifying
--without-triggers;
2) This behaviour is consistent with future PostgreSQL versions.


Thanks in advance to anyone who can point the relevant part of the documentation!


[1] http://www.postgresql.org/docs/9.1/static/app-pgrestore.html


--
Kévin Le Gouguec

Kevin Le Gouguec wrote:
> On 9.1, the doc[1] says that --disable-triggers is only relevant when processing data-only dumps. What
> about when it's *not* data-only?
> 
> Say I have a table T (id, column1, column2, ...) and another table T_integrity (id, hash), where
> "hash" corresponds to md5(corresponding row in T). T_integrity is updated by a trigger watching T for
> insertions/updates/deletions.
> Now I dump (pg_dump -Fc) the base, i.e. a set of tables like T, each with its corresponding
> T_integrity. My question is, if the dump is not data-only, is there a formal definition somewhere of
> pg_restore's behaviour regarding triggers?
> 
> Initially, I thought pg_restore would recreate the tables, register the triggers, and then fill the
> tables as a series of INSERT INTO statements, so the restored T_integrity would end up with duplicated
> rows (one row from the dump, another from the trigger). As it happened, that wasn't the case.
> Empirically, pg_restore seems to copy/paste the tables' contents, and THEN enable triggers (meaning
> updating T after pg_restore causes an update in T_integrity).
> 
> That's great, since that fits my intended use case (check the hashes after restoring, which would be
> tautological and/or confusing if pg_restore executed triggers). However, I'd like to see it written
> somewhere that this behaviour is intended rather than coincidental, i.e. :
> 
> 1) I can expect pg_restore to not execute triggers on regular (not data-only) dumps, without
> specifying --without-triggers;
> 2) This behaviour is consistent with future PostgreSQL versions.
> 
> 
> Thanks in advance to anyone who can point the relevant part of the documentation!
> 
> 
> [1] http://www.postgresql.org/docs/9.1/static/app-pgrestore.html

I cannot find an explicit mention in the 9.1 documentation, but pg_dump dumps a database
in the following order:
- CREATE TABLE statements
- COPY statements with the data
- Indexes, constraints, triggers, rules

This is essential for good performance, but also to ensure that the COPY statements
work without error (foreign key references could make them fail).
So I think you can safely rely on that.

There is an mention of that in the pg_restore documentation from version 9.4 on:

--section=sectionname

    Only restore the named section. The section name can be pre-data, data, or post-data.
    This option can be specified more than once to select multiple sections. The default
    is to restore all sections.

    The data section contains actual table data as well as large-object definitions.
    Post-data items consist of definitions of indexes, triggers, rules and constraints
    other than validated check constraints. Pre-data items consist of all other data
    definition items.

Yours,
Laurenz Albe

unsubscribe