pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug or feature
4+ messages / 4 participants
[nested] [flat]
* Bug or feature
@ 2000-11-20 19:10 Kyle <kyle@actarg.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kyle @ 2000-11-20 19:10 UTC (permalink / raw)
To: pgsql-sql
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;
Attachments:
kyle.vcf (text/x-vcard, 184B) download
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;;;;;
version:2.1
email;internet:kyle@actarg.com
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
* Re: Bug or feature
@ 2000-11-22 19:16 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Kyle <kyle@actarg.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2000-11-22 19:16 UTC (permalink / raw)
To: Kyle <kyle@actarg.com>; +Cc: 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@bigpanda.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;
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug or Feature?
@ 2004-02-10 08:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Herbert R. Ambos <hrambos@transco.ph>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-02-10 08:29 UTC (permalink / raw)
To: Herbert R. Ambos <hrambos@transco.ph>; +Cc: pgsql-sql
"Herbert R. Ambos" <hrambos@transco.ph> writes:
> [ drops only column of a table ]
> Is this allowed in SQL?
The SQL spec forbids that. We deliberately decided to ignore the spec
restriction, because it creates too many unpleasant boundary cases for
tools that want to manipulate table definitions. "DROP COLUMN foo"
shouldn't depend on whether there are other columns besides foo.
In short, yes, it's a feature.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Bug or Feature?
@ 2004-02-10 19:48 Herbert R. Ambos <hrambos@transco.ph>
0 siblings, 1 reply; 4+ messages in thread
From: Herbert R. Ambos @ 2004-02-10 19:48 UTC (permalink / raw)
To: pgsql-sql
Hey guys,
I found this interesting…
test=# create table t ( c char);
CREATE TABLE
test =# \d t
Table "public.t"
Column | Type | Modifiers
--------+--------------+-----------
c | character(1) |
test =# alter table t drop column c;
ALTER TABLE
test =# \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
test =# select * from t;
--
(0 rows)
test =# create table t2 ();
CREATE TABLE
test =# select * from t2;
--
(0 rows)
I mean, we have a table with nothing on it. Of course this maybe useful
if like to check if an object exists in pg_class for some reasons. But
nah… we can have other workarounds for that. Is this allowed in SQL?
Thanks,
herbert
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.542 / Virus Database: 336 - Release Date: 11/18/2003
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-10 19:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-20 19:10 Bug or feature Kyle <kyle@actarg.com>
2000-11-22 19:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2004-02-10 19:48 Bug or Feature? Herbert R. Ambos <hrambos@transco.ph>
2004-02-10 08:29 ` Re: Bug or Feature? Tom Lane <tgl@sss.pgh.pa.us>
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