pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Cc: wieck@sapserv.debis.de
Subject: Deferral of primary key constraint
Date: Fri, 02 Jun 2000 09:34:42 -0600
Message-ID: <3937D411.7B47A641@actarg.com> (raw)

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.

Attachments:

kyle.vcf (text/x-vcard, 290B) download 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

view thread (2+ messages)  latest in thread

Message-ID: <3937D411.7B47A641@actarg.com>
Permalink:  ../3937D411.7B47A641@actarg.com/
Also on:    postgresql.org/message-id/3937D411.7B47A641@actarg.com

 · 

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-sql@postgresql.org
  Cc: kyle@actarg.com, wieck@sapserv.debis.de
  Subject: Re: Deferral of primary key constraint
  In-Reply-To: <3937D411.7B47A641@actarg.com>
  References: <3937D411.7B47A641@actarg.com>

* 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