pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rigmor Ukuhe <rigmor.ukuhe@finestmedia.ee>
To: pgsql-admin@postgresql.org
Subject: Migrating from 7.2 to 8
Date: Sat, 26 Mar 2005 14:08:15 +0200
Message-ID: <424550AF.1040807@finestmedia.ee> (raw)

Hi,

I have problem with migrating our system to PG 8.

problem is with rules and triggers. Basically i want to insert row to 
table 'a1', its primary key has default value 
nextval('"REO_ID_seq"'::text).
I have a rule that must insert row to another table 'a2' using 
currval('"REO_ID_seq"'::text). And i have trigger that should enforce 
referential integrity between two tables.

CREATE CONSTRAINT TRIGGER "fk_a1_id" AFTER INSERT OR UPDATE ON "a1" FROM 
"a2" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
   "RI_FKey_check_ins" ('fk_a1_id', 'a1', 'a2', 'UNSPECIFIED', 'id', 'id');
CREATE RULE "rule_a1_INSERT" AS ON INSERT TO a1 DO INSERT INTO a2 
("name", "id") VALUES ('REO_HOUSE'::"varchar", 
int4(currval('"REO_ID_seq"'::text)));

These rule and trigger create statements are taken from dump script. (if 
i load this dump back to 7.2 then everything is working).

In PG 7.2 it worked nicely, but in PG8 i must create trigger with 
DEFERRABLE INITIALLY DEFERRED to get  code to work, otherwise it 
complains about missing key in table a2.

Can anybody point out if i am missing something? Now when i got it to 
work in pg 8 i ask myself, why this code is even working on pg 7.2?

-- 
Rigmor Ukuhe




Message-ID: <424550AF.1040807@finestmedia.ee>
Permalink:  ../424550AF.1040807@finestmedia.ee/
Also on:    postgresql.org/message-id/424550AF.1040807@finestmedia.ee

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: rigmor.ukuhe@finestmedia.ee
  Subject: Re: Migrating from 7.2 to 8
  In-Reply-To: <424550AF.1040807@finestmedia.ee>
  References: <424550AF.1040807@finestmedia.ee>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox