From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "Kevin Le Gouguec *EXTERN*" <kevin(dot)le-gouguec(at)insa-lyon(dot)fr>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Triggers handling |
Date: | 2014-10-08 10:06:33 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B17D6113F@ntex2010a.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Linehan | 2014-10-08 16:59:47 | Re: Triggers handling |
Previous Message | Kevin Le Gouguec | 2014-10-08 09:18:35 | [pg_restore] Triggers handling |