From kyle@actarg.com Mon Nov 20 19:12:00 2000 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAKJC0s35655 for ; Mon, 20 Nov 2000 14:12:00 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id MAA25372 for ; Mon, 20 Nov 2000 12:10:03 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id MAA08265 for ; Mon, 20 Nov 2000 12:10:05 -0700 Message-ID: <3A19770D.AFAAECEF@actarg.com> Date: Mon, 20 Nov 2000 12:10:05 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Bug or feature Content-Type: multipart/mixed; boundary="------------A81608BD76075F196BED61DD" X-Archive-Number: 200011/162 X-Sequence-Number: 193 This is a multi-part message in MIME format. --------------A81608BD76075F196BED61DD Content-Type: multipart/alternative; boundary="------------87DAA7BFC3D30F5D9D927176" --------------87DAA7BFC3D30F5D9D927176 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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; --------------87DAA7BFC3D30F5D9D927176 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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;

--------------87DAA7BFC3D30F5D9D927176-- --------------A81608BD76075F196BED61DD Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" 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 --------------A81608BD76075F196BED61DD-- From sszabo@megazone23.bigpanda.com Wed Nov 22 19:18:48 2000 Received: from megazone23.bigpanda.com (138.210.6.64.reflexcom.com [64.6.210.138]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAMJIms93223 for ; Wed, 22 Nov 2000 14:18:48 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@localhost) by megazone23.bigpanda.com (8.11.1/8.11.0) with ESMTP id eAMJGZW01944; Wed, 22 Nov 2000 11:16:35 -0800 (PST) Date: Wed, 22 Nov 2000 11:16:35 -0800 (PST) From: Stephan Szabo To: Kyle cc: pgsql-sql@postgresql.org Subject: Re: Bug or feature In-Reply-To: <3A19770D.AFAAECEF@actarg.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200011/187 X-Sequence-Number: 218 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; > > > From tgl@sss.pgh.pa.us Tue Feb 10 08:29:25 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 87FE4D1D1A4 for ; Tue, 10 Feb 2004 08:29:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49684-07 for ; Tue, 10 Feb 2004 04:29:10 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 49634D1C9BD for ; Tue, 10 Feb 2004 04:29:08 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1A8T9Yl010797; Tue, 10 Feb 2004 03:29:09 -0500 (EST) To: "Herbert R. Ambos" Cc: pgsql-sql@postgresql.org Subject: Re: Bug or Feature? In-reply-to: <001701c3f00e$cec10330$78fba8c0@herbert> References: <001701c3f00e$cec10330$78fba8c0@herbert> Comments: In-reply-to "Herbert R. Ambos" message dated "Tue, 10 Feb 2004 11:48:03 -0800" Date: Tue, 10 Feb 2004 03:29:09 -0500 Message-ID: <10796.1076401749@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/96 X-Sequence-Number: 16787 "Herbert R. Ambos" 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 From hrambos@transco.ph Tue Feb 10 03:46:14 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F3E8CD1D27C for ; Tue, 10 Feb 2004 03:46:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 58068-07 for ; Mon, 9 Feb 2004 23:46:16 -0400 (AST) Received: from clusternode1.transco.local (unknown [203.177.60.220]) by svr1.postgresql.org (Postfix) with ESMTP id AF401D1B47D for ; Mon, 9 Feb 2004 23:46:08 -0400 (AST) Received: from herbert ([192.168.251.120]) by clusternode1.transco.local with Microsoft SMTPSVC(5.0.2195.5329); Tue, 10 Feb 2004 11:47:46 +0800 From: "Herbert R. Ambos" To: Subject: Bug or Feature? Date: Tue, 10 Feb 2004 11:48:03 -0800 Message-ID: <001701c3f00e$cec10330$78fba8c0@herbert> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0018_01C3EFCB.C0B31FF0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4024 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-OriginalArrivalTime: 10 Feb 2004 03:47:47.0536 (UTC) FILETIME=[A5F47D00:01C3EF88] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=3.3 tagged_above=0.0 required=5.0 tests=BAYES_10, DATE_IN_FUTURE_12_24, HTML_70_80, HTML_MESSAGE, RCVD_IN_DSBL X-Spam-Level: *** X-Archive-Number: 200402/94 X-Sequence-Number: 16785 This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C3EFCB.C0B31FF0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hey guys, =20 I found this interesting=85 =20 =20 test=3D# create table t ( c char); CREATE TABLE =20 test =3D# \d t Table "public.t" Column | Type | Modifiers --------+--------------+----------- c | character(1) | =20 test =3D# alter table t drop column c; ALTER TABLE =20 test =3D# \d t Table "public.t" Column | Type | Modifiers --------+------+----------- =20 test =3D# select * from t; =20 -- (0 rows) =20 test =3D# create table t2 (); CREATE TABLE =20 test =3D# select * from t2; =20 -- (0 rows) =20 =20 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=85 we can have other workarounds for that. Is this allowed in SQL? =20 =20 Thanks, =20 =20 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 =20 ------=_NextPart_000_0018_01C3EFCB.C0B31FF0 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Hey guys,

I found this interesting=85

test=3D# create table t ( c char);

CREATE TABLE

test =3D# \d t

Table "public.t"

Column | Type | Modifiers

--------+--------------+-----------

c | character(1) |

test =3D# alter table t drop column c;

ALTER TABLE

test =3D# \d t

Table "public.t"

Column | Type | Modifiers

--------+------+-----------

test =3D# select * from t;

(0 rows)

test =3D# create table t2 ();

CREATE TABLE

test =3D# select * from t2;

(0 rows)

I mean, we have a table with nothing on it. Of cou= rse this maybe useful if like to check if an object exists in pg_class for some reas= ons. But nah=85 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

------=_NextPart_000_0018_01C3EFCB.C0B31FF0--