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
