[pg_restore] Triggers handling

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

Responses

Browse pgsql-novice by date

  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