From: | Kevin Le Gouguec <kevin(dot)le-gouguec(at)insa-lyon(dot)fr> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | [pg_restore] Triggers handling |
Date: | 2014-10-08 09:18:35 |
Message-ID: | 2127914125.9520629.1412759915016.JavaMail.zimbra@insa-lyon.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(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
--
Kévin Le Gouguec
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2014-10-08 10:06:33 | Re: Triggers handling |
Previous Message | Merlin Moncure | 2014-10-01 15:48:59 | Re: Rename entries with an increment |