Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id ED5CA633740 for ; Wed, 10 Feb 2010 12:10:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 86192-03 for ; Wed, 10 Feb 2010 16:10:43 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f198.google.com (mail-qy0-f198.google.com [209.85.221.198]) by mail.postgresql.org (Postfix) with ESMTP id 27BD2632621 for ; Wed, 10 Feb 2010 12:10:43 -0400 (AST) Received: by qyk36 with SMTP id 36so136253qyk.5 for ; Wed, 10 Feb 2010 08:10:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=zCMCoA7sl7PuE1A0JUnhotHGyhiBB7O8ntbRuBOEm4A=; b=bop+J35aBUUQJR2HDajWmmb8TOnV/QsUe9zooHaXPYhSoDBusJetgHx2LOlfQw4vxz 7eGlxmm9oUAelflZMnVBheWOPvXUr1975VK9qVFzYkYhBhfA7gDTqdV98Kr2g5d8kRFl 3xnsplg49afLfpxz+v0tb2STXdoBtJdsWSNCM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=xx4pQuPpfLag02xbs4gmYL3MqZfZWRBag1V9+vlKlBAj9L9NUnB1T17kZOFwMGEvTS aeOA4bUEKxi1wGG6b1j+XRXMLvga/Rs0QzAVVRpmTVKYxR5ak4jCAh+iob6yPlK6bTnE 8s3hbDXNRy3IkQdUvpM0OASjzfVXOFqMvQXVw= MIME-Version: 1.0 Received: by 10.229.230.147 with SMTP id jm19mr210903qcb.9.1265818242174; Wed, 10 Feb 2010 08:10:42 -0800 (PST) In-Reply-To: <4ec1cf761002100803r2fe7b7f2v5d4417e7ded380f7@mail.gmail.com> References: <99024f5f1002071922j5b14c350mb1612313690f16ec@mail.gmail.com> <87k4umg0c3.fsf@hi-media-techno.com> <1265794218.3714.12.camel@coyote> <971254BD-6919-41BB-8D26-160C14D5BFE2@gmail.com> <1265815807.6369.19.camel@coyote> <4ec1cf761002100803r2fe7b7f2v5d4417e7ded380f7@mail.gmail.com> From: Josh Kupershmidt Date: Wed, 10 Feb 2010 11:10:22 -0500 Message-ID: <4ec1cf761002100810w520663d5m9f76e4e357967e70@mail.gmail.com> Subject: Re: Disabling triggers with psql (gforge 7.4 to 8.2 migration) To: =?ISO-8859-1?Q?I=F1igo_Martinez_Lasala?= Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016364ec83cacd136047f414662 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.443 tagged_above=-10 required=5 tests=AWL=0.155, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/81 X-Sequence-Number: 33684 --0016364ec83cacd136047f414662 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable [Forgot to CC list, again] Found the doc. page on pg_trigger I was looking for. You were right, pg_trigger changed between 8.2 and 8.3: http://www.postgresql.org/docs/8.2/static/catalog-pg-trigger.html http://www.postgresql.org/docs/8.3/static/catalog-pg-trigger.html Josh On Wed, Feb 10, 2010 at 11:03 AM, Josh Kupershmidt wrot= e: > On Wed, Feb 10, 2010 at 10:30 AM, I=F1igo Martinez Lasala < > imartinez@vectorsf.com> wrote: > >> Hmmmm... >> >> What about >> UPDATE pg_trigger SET tgenabled =3D false; >> >> Restore data, and after done: >> UPDATE pg_trigger SET tgenabled =3D true; >> >> I'm going to test it. >> >> > Perhaps this was still a boolean column in 8.2, but at least in 8.3 and > above tgenabled is a "char" field. The only explanation I found of its > values is here: > http://archives.postgresql.org/pgsql-hackers/2007-01/msg01302.php > > Anyway, I suspect your idea will work, but if you're going to the trouble > of disabling+reenabling triggers for performance reasons, you might still= be > better off with pg_restore, since it can handle creation of constraints a= nd > indexes at the appropriate time as well, which can really help. > > Josh > --0016364ec83cacd136047f414662 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable [Forgot to CC list, again]

Found the doc. page on pg_trigger I was l= ooking for. You were right, pg_trigger changed between 8.2 and 8.3:
h= ttp://www.postgresql.org/docs/8.2/static/catalog-pg-trigger.html
http://www.postgresql.org/docs/8.3/static/catalog-pg-trigger.html
Josh

On Wed, Feb 10, 2010 at 11:03 A= M, Josh Kupershmidt <schmiddy@gmail.com> wrote:
On Wed, Feb 10, 2010 at 10:30 AM, I=F1igo Martinez Lasala <imartinez= @vectorsf.com> wrote:
=20 =20
Hmmmm...

What about
UPDATE pg_trigger SET tgenabled =3D false;

Restore data, and after done:
UPDATE pg_trigger SET tgenabled =3D true;

I'm going to test it.


P= erhaps this was still a boolean column in 8.2, but at least in 8.3 and abov= e tgenabled is a "char" field. The only explanation I found of it= s values is here:
http://archives.postgresql.org/pgsql-hackers/2007-01/m= sg01302.php

Anyway, I suspect your idea will work, but if you= 9;re going to the trouble of disabling+reenabling triggers for performance = reasons, you might still be better off with pg_restore, since it can handle= creation of constraints and indexes at the appropriate time as well, which= can really help.=A0

Josh

--0016364ec83cacd136047f414662--