From kyle@actarg.com Fri Jun 2 16:34:54 2000 Received: from chi.actarg.com ([209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA96291 for ; Fri, 2 Jun 2000 12:34:54 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id JAA22199; Fri, 2 Jun 2000 09:34:42 -0600 Message-ID: <3937D411.7B47A641@actarg.com> Date: Fri, 02 Jun 2000 09:34:42 -0600 From: Kyle Bateman 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 CC: wieck@sapserv.debis.de Subject: Deferral of primary key constraint Content-Type: multipart/mixed; boundary="------------75ECB46E667ADBB044DB27AD" X-Archive-Number: 200006/20 This is a multi-part message in MIME format. --------------75ECB46E667ADBB044DB27AD Content-Type: multipart/alternative; boundary="------------C8A3F73D4A6C9B820AF217A6" --------------C8A3F73D4A6C9B820AF217A6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Is it possible to defer the check on a primary key constraint (or a check constraint, for that matter). Here is an example that shows why it would be nice to be able to do so. We have a real-life scenario that is similar (but much more complex). drop table btab; create table btab ( base varchar, pos int4, cmt varchar, primary key (base, pos) ); insert into btab (base,pos,cmt) values ('aa',1,'The'); insert into btab (base,pos,cmt) values ('aa',2,'quick'); insert into btab (base,pos,cmt) values ('aa',3,'grey'); insert into btab (base,pos,cmt) values ('aa',4,'fox'); insert into btab (base,pos,cmt) values ('bb',3,'dog'); insert into btab (base,pos,cmt) values ('bb',2,'brown'); insert into btab (base,pos,cmt) values ('bb',1,'The'); select * from btab order by base,pos; begin; delete from btab where base = 'aa' and pos = 2; update btab set pos = pos - 1 where pos > 2 and base = 'aa'; commit; select * from btab order by base,pos; begin; update btab set pos = pos + 1 where pos >= 2 and base = 'bb'; insert into btab (base,pos,cmt) values ('bb',2,'slow'); commit; select * from btab order by base,pos; The last transaction fails (on my box, anyway) because of the primary key index. We would like to be able to do inserts/deletes to a list of records and still ensure that they are in contiguous sequential order so we want to renumber higher records if a new record is inserted in the middle. The sequence is part of the primary key and we want to ensure uniqueness. Some renumbering will work (by chance) if the records happen to get adjusted in the right order. But if one of the updates tries to rename to an already existing record, it fails. How hard is it to take the deferral mechanism you have for foreign key references and apply it to the primary key too? It would also be handy to be able to defer a check constraint. --------------C8A3F73D4A6C9B820AF217A6 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Is it possible to defer the check on a primary key constraint (or a check constraint, for that matter).  Here is an example that shows why it would be nice to be able to do so.  We have a real-life scenario that is similar (but much more complex).

drop table btab;
create table btab (
    base        varchar,
    pos         int4,
    cmt         varchar,

   primary key (base, pos)
);

insert into btab (base,pos,cmt) values ('aa',1,'The');
insert into btab (base,pos,cmt) values ('aa',2,'quick');
insert into btab (base,pos,cmt) values ('aa',3,'grey');
insert into btab (base,pos,cmt) values ('aa',4,'fox');

insert into btab (base,pos,cmt) values ('bb',3,'dog');
insert into btab (base,pos,cmt) values ('bb',2,'brown');
insert into btab (base,pos,cmt) values ('bb',1,'The');

select * from btab order by base,pos;

begin;
delete from btab where base = 'aa' and pos = 2;
update btab set pos = pos - 1 where pos > 2 and base = 'aa';
commit;

select * from btab order by base,pos;

begin;
update btab set pos = pos + 1 where pos >= 2 and base = 'bb';
insert into btab (base,pos,cmt) values ('bb',2,'slow');
commit;

select * from btab order by base,pos;

The last transaction fails (on my box, anyway) because of the primary key index.

We would like to be able to do inserts/deletes to a list of records and still ensure that they are in contiguous sequential order so we want to renumber higher records if a new record is inserted in the middle.  The sequence is part of the primary key and we want to ensure uniqueness.  Some renumbering will work (by chance) if the records happen to get adjusted in the right order.  But if one of the updates tries to rename to an already existing record, it fails.

How hard is it to take the deferral mechanism you have for foreign key references and apply it to the primary key too?  It would also be handy to be able to defer a check constraint.
  --------------C8A3F73D4A6C9B820AF217A6-- --------------75ECB46E667ADBB044DB27AD Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard --------------75ECB46E667ADBB044DB27AD-- From JanWieck@t-online.de Fri Jun 2 20:45:21 2000 Received: from mailout00.sul.t-online.com (mailout00.sul.t-online.com [194.25.134.16]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA82985 for ; Fri, 2 Jun 2000 16:45:21 -0400 (EDT) (envelope-from JanWieck@t-online.de) Received: from fwd06.sul.t-online.de by mailout00.sul.t-online.com with smtp id 12xyJj-0007Pf-02; Fri, 2 Jun 2000 22:45:19 +0200 Received: from hot.jw.home (340000654369-0001@[62.224.102.188]) by fwd06.sul.t-online.de with esmtp id 12xyJV-0deLEOC; Fri, 2 Jun 2000 22:45:05 +0200 Received: (from wieck@localhost) by hot.jw.home (8.8.5/8.8.5) id VAA07464; Fri, 2 Jun 2000 21:14:56 +0200 From: JanWieck@t-online.de (Jan Wieck) Message-Id: <200006021914.VAA07464@hot.jw.home> Subject: Re: Deferral of primary key constraint In-Reply-To: <3937D411.7B47A641@actarg.com> from Kyle Bateman at "Jun 2, 2000 09:34:42 am" To: Kyle Bateman Date: Fri, 2 Jun 2000 21:14:56 +0200 (MEST) CC: pgsql-sql@postgresql.org Reply-To: Jan Wieck X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Sender: 340000654369-0001@t-dialin.net X-Archive-Number: 200006/22 Kyle Bateman wrote: > Is it possible to defer the check on a primary key constraint (or a > check constraint, for that matter). Here is an example that shows why > it would be nice to be able to do so. We have a real-life scenario that > is similar (but much more complex). Not yet. Only the trigger based foreign key checks are deferrable in 7.0. We discovered the need for deferrability of UNIQUE (what is what you want from the PK things) and other checks. Not sure up to now how we could do it, but a known problem. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #