DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=t/zHYnwfyKFnVXRTDDWIR8WcVbXbzZpcMgvjjNw7tfKSl978iNyT6DjeLWPENe8KD4
	X0utmmKnXQfOeCROFEThvEUfdQsPrceeRoEBOFenY5cw6O7A7Kn1fEQvnBz+EAAe3jV3
	/9Jv3EBoas5keiYmerNERE7sDqR2S+7pJr4Uo=
MIME-Version: 1.0
In-Reply-To: <49E37441.6030301@frii.com>
References: <49DC1034.1060705@frii.com>
	<dcc563d10904101120y49f00977nc74132e982518444@mail.gmail.com>
	<49E37441.6030301@frii.com>
Date: Mon, 13 Apr 2009 17:09:49 -0400
Message-ID: <d18085b50904131409g10d43d6cs35dd14ede13bc02@mail.gmail.com>
Subject: Re: changing multiple pk's in one update
From: Glenn Maynard <glennfmaynard@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

(JMdict?  I was playing with importing that into a DB a while back,
but the attributes in that XML are such a pain--and then my email died
while I was trying to get those changed, and I never picked it up
again.)

On Mon, Apr 13, 2009 at 1:20 PM, Stuart McGraw <smcg2297@frii.com> wrote:
> 1 to the number of sentences in the entry) and the sentence text. =C2=A0T=
he pk is
> of course the entry id and the sense number.
> There are other tables that have fk's to the senses.

Your PK is a composite of (entry, order)?  Won't your foreign keys
elsewhere all break when you shift the order around?

> I guess I could add an "order"[1] column and use the sense number as a
> surrogate partial key to avoid the need for key renumbering,
> but all the api's (and the normal human way of thinking) are based
> on "sense number 1 of entry x", "sense number 2 of entry y", so
> one would need to maintain "order" as a gapless sequence (or add a new
> mapping layer to map from/to a arbitrary monotonic sequence
> to a 1,2,3,... sequence) -- the gain doesn't seem that big.

Why not do this in the straightforward way: three separate fields: a
regular, sequential PK; an FK to the entry; and an order number.  Add
an index on (entry_key, order_number).  It's a little more expensive
since you have a new column and index (the PK), but in a table with a
lot of plain text that's probably insignificant.  Now you can use the
plain PK for your FK's.

I'd anticipate other problems with changing your PKs.  If you're
referring to sense (100,3), and a concurrent user adds a sense, you
may suddenly find yourself referring to a different sense due to them
being reordered out from under you.  You'd have similar problems if
your sense is deleted entirely: instead of the row simply ceasing to
exist (and resulting in predictable, checkable errors), you may end up
silently referring to another sense.

Maybe I'm misunderstanding what you're doing, though.

You'd have to have no UNIQUE constraint on the (entry, order)
composite index, though, or you'll have the same problem when you
reorder them.

--=20
Glenn Maynard