From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Error from trigger
Date: Thu, 8 Dec 2005 00:43:18 +0100
User-Agent: KMail/1.8.1
References: <200512080006.44606.leif@solumslekt.org>
	<4507.1133997785@sss.pgh.pa.us>
In-Reply-To: <4507.1133997785@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200512080043.18481.leif@solumslekt.org>

On Thursday 08 December 2005 00:23, Tom Lane wrote:
>Is there a reason you don't just mark the FK reference as ON DELETE
>CASCADE, rather than using a handwritten trigger?

I could have done that, of course. I'm still a little shaky on "best=20
practice" with these things. Besides, I haven't found out yet how to=20
alter the table to make the reference cascading. And I wanted to=20
experiment with simple triggers.

>Offhand this looks like you might have dropped and recreated the
>event_citations table?  If so it's just the known problem that
>plpgsql caches plans and doesn't throw them away when the referenced
>objects change.

Right on target. Thank you. A few days ago, as I have already related on=20
this list, I did the following to fix a bad design with inherited=20
tables:

pgslekt=3D> create table event_cits (
pgslekt(> event_fk integer references events (event_id),
pgslekt(> source_fk integer references sources (source_id),
pgslekt(> PRIMARY KEY (event_fk, source_fk)
pgslekt(> );
NOTICE: =A0CREATE TABLE / PRIMARY KEY will create implicit index=20
"event_cits_pkey" for table "event_cits"
CREATE TABLE
pgslekt=3D> insert into event_cits (select event_fk, source_fk from=20
event_citations);
INSERT 0 29139
pgslekt=3D> drop table event_citations cascade;
NOTICE: =A0drop cascades to rule _RETURN on view event_notes
NOTICE: =A0drop cascades to view event_notes
DROP TABLE
pgslekt=3D> drop table citations;
DROP TABLE
pgslekt=3D> alter table event_cits rename to event_citations;
ALTER TABLE
pgslekt=3D> \i views_and_functions.sql

I've run a VACUUM FULL ANALYZE, but that doesn't seem to cure the=20
situation. Is there a way to fix it, short of a full dump, drop, and =20
reload?
=2D-=20
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE