Skip site navigation (1) Skip section navigation (2)

Re: Bug or feature

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Kyle <kyle(at)actarg(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Bug or feature
Date: 2000-11-22 19:16:35
Message-ID: Pine.BSF.4.21.0011221113490.1845-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
What is it actually giving you as an error
message in the failing case? Someone pointed
out a problem in deferred constraints recently
and I think this may be related. 

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Mon, 20 Nov 2000, Kyle wrote:

> Here's an interesting test of referential integrity.  I'm not sure if
> this is working the way it should or if it is a bug.
> 
> I'm trying to update the primary key in records that are linked together
> from the two different tables.  My initial assumption was that because
> of the cascade, I could update the primary key only in the gl_hdr table
> and it would cascade to the gl_items table.  I have two separate updates
> of gl_items shown below.  One updates the key in gl_items explicitly,
> the other tries to wait and allow the cascade to do it.  Only the first
> one works (try commenting one in/out at a time).
> 
> Unless I update the glid explicitly in gl_items, I get an RI violation
> when it tries to update the gl_hdr record.
> 
> 
> --Test RI in the general ledger
> 
> drop table gl_hdr;
> drop table gl_items;
> 
> create table gl_hdr (
>     glid        int4,
>     hstat       varchar(1),
>     constraint gl_hdr_pk_glid primary key (glid)
> );
> 
> create table gl_items (
>     glid        int4,
>     inum        int4,
>     istat       varchar(1),
>     primary key (glid, inum),
> 
>     constraint gl_items_fk_glid
>         foreign key (glid) references gl_hdr
>             on update cascade
>             deferrable initially deferred
> );
> 
> insert into gl_hdr (glid,hstat) values (1,'w');
> insert into gl_items (glid,inum,istat) values (1,1,'w');
> insert into gl_items (glid,inum,istat) values (1,2,'w');
> 
> select * from gl_hdr h, gl_items i where h.glid = i.glid;
> 
> begin;
> 
> --This one works:
> --  update gl_items     set glid = 1000, istat = 'c' where glid = 1;
> 
> --This one doesn't:
>     update gl_items     set istat = 'c' where glid = 1;
> 
> 
> 
>     update gl_hdr       set glid = 1000, hstat = 'c' where glid = 1;
> end;
> 
> select * from gl_hdr h, gl_items i where h.glid = i.glid;
> 
> 
> 


In response to

pgsql-sql by date

Next:From: Ramesh H RDate: 2000-11-23 02:50:57
Subject: How to know table structure from a java program
Previous:From: Roberto MelloDate: 2000-11-22 15:46:30
Subject: Re: Persistent Connects (pg_pconnect)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group