Re: Triggers handling
От | Albe Laurenz |
---|---|
Тема | Re: Triggers handling |
Дата | |
Msg-id | A737B7A37273E048B164557ADEF4A58B17D6113F@ntex2010a.host.magwien.gv.at обсуждение исходный текст |
Ответ на | [pg_restore] Triggers handling (Kevin Le Gouguec <kevin.le-gouguec@insa-lyon.fr>) |
Ответы |
Re: Triggers handling
(Paul Linehan <linehanp@tcd.ie>)
|
Список | 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
В списке pgsql-novice по дате отправления: