Re: pg_restore --disable-triggers does not stop triggers

Hi!

8.0.1 and 8.1 beta.

Triggers are still fired although option --disable-triggers is applied
to pg_restore. The fired triggers abort pg_restore because of the
foreign keys violations.

The following restore script used to be working but it suddently
doesn't. I don't remember I ever changed this script since it had
worked.

#Backup command:
#PGCLIENTENCODING=UNICODE pg_dump -Fc db1 > db1
#

#Restore commands:
pg_restore -l db1 >list
createdb -E UNICODE db1
pg_restore -F c -L list -v -d db1 -s db1 >log-schema 2>&1
pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 >log-data
2>&1

Any idea will be much appreciated.

Regards,

CN

--
http://www.fastmail.fm - Email service worth paying for. Try it for free

am 06.10.2005, um 22:33:52 +0800 mailte CN folgendes:
> Hi!
>
> 8.0.1 and 8.1 beta.
>
> Triggers are still fired although option --disable-triggers is applied
> to pg_restore. The fired triggers abort pg_restore because of the
> foreign keys violations.
>
> The following restore script used to be working but it suddently
> doesn't. I don't remember I ever changed this script since it had
> worked.
>
> #Backup command:
> #PGCLIENTENCODING=UNICODE pg_dump -Fc db1 > db1
> #
>
> #Restore commands:
> pg_restore -l db1 >list
> createdb -E UNICODE db1
> pg_restore -F c -L list -v -d db1 -s db1 >log-schema 2>&1
> pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 >log-data

You are DB-Superuser?

Presently, the commands emitted for --disable-triggers must
be done as superuser. So, you should also specify a supe-
ruser name with -S, or preferably run pg_restore as a Post-
greSQL superuser.

Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Thank you for the reply!

> > Triggers are still fired although option --disable-triggers is applied
> > to pg_restore. The fired triggers abort pg_restore because of the
> > foreign keys violations.
[snip]
> > pg_restore -l db1 >list
> > createdb -E UNICODE db1
> > pg_restore -F c -L list -v -d db1 -s db1 >log-schema 2>&1
> > pg_restore -F c -L list -v -d db1 -a --disable-triggers db1 >log-data
>
> You are DB-Superuser?
>
> Presently, the commands emitted for --disable-triggers must
> be done as superuser. So, you should also specify a supe-
> ruser name with -S, or preferably run pg_restore as a Post-
> greSQL superuser.

I am using PostgreSQL superuser doing this.

I notice that PostgreSQL does disable triggers but it seems to not
disable CHECK constraint:

CREATE TABLE table1 (
CHECK(VerifyFunc(c2,c3)),
c1 VARCHAR(20),
c2 VARCHAR(20),
c3 "char" NOT NULL
)WITHOUT OIDS;

pg_restore: disabling triggers
pg_restore: restoring data for table "table2"
pg_restore: enabling triggers
pg_restore: disabling triggers
pg_restore: restoring data for table "table1"
pg_restore: ERROR: <Exception raised by VerifyFunc()>
CONTEXT: COPY table1, line 1: "100000 q1 X"
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error

--
http://www.fastmail.fm - IMAP accessible web-mail

"CN" <cnliou9(at)fastmail(dot)fm> writes:
> I notice that PostgreSQL does disable triggers but it seems to not
> disable CHECK constraint:

Why should it?

(Hint: a check constraint that looks at anything but the row being
checked is broken by definition.)

regards, tom lane

Tom,

Thank you very much for the enlightenment again!

> > I notice that PostgreSQL does disable triggers but it seems to not
> > disable CHECK constraint:
>
> Why should it?
>
> (Hint: a check constraint that looks at anything but the row being
> checked is broken by definition.)

Maybe my case is rare in the real world, but this is my problem:

I use CHECK(MyFunc(column_1,column_2)) because foreign key constraint is
insufficient to do the complicate check for that table. Such design
works well for production run but problem happens during database
restore. It happens because MyFunc() raises exception as the data of
this table with CHECK constraint is being restored before the data of
the table(s) referenced by MyFunc().

Is it a wise request for one more option to be added to pg_restore to
disable CHECK constraint?

Best Regards,

CN

--
http://www.fastmail.fm - Accessible with your email software
or over the web