From: Stuart McGraw <smcg2297@frii.com>
To: pgsql-sql@postgresql.org
Subject: Re: changing multiple pk's in one update
Date: Mon, 13 Apr 2009 20:27:00 -0600
Message-ID: <49E3F474.8090505@frii.com> (raw)
In-Reply-To: <d18085b50904131409g10d43d6cs35dd14ede13bc02@mail.gmail.com>
References: <49DC1034.1060705@frii.com>
<dcc563d10904101120y49f00977nc74132e982518444@mail.gmail.com>
<49E37441.6030301@frii.com>
<d18085b50904131409g10d43d6cs35dd14ede13bc02@mail.gmail.com>
Glenn Maynard wrote:
> (JMdict?
yup. ;-)
> 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. The 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?
As Richard Broersma pointed out, the fk's are all
"cascade update".
>> 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 have done a few other simple database apps in the
past and always did things that way. The current app
also started that way but I tried, experimentally, using
composite keys, and was amazed how much simpler things
became. For example, to copy an entry one duplicates
the entry's rows (in all the related tables) and need
only change the entry id number in the duplicated rows.
When each child table has a independent serial id, this
becomes more complicated and requires looping in the
copy function (AFAICT). I've noted that a number of
other things I was doing in the database api and in
Postgresql functions similarly became simpler.
> 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.
I try to avoid a lot of problems like that by doing very
little actual updating (in the database sense). Most
changes are made to a new copy of an entry. When an
update is actually done (such as to delete the original
copy of an entry), it is done in a very short time interval
with serializable isolation.
> 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.
Since the data in the database is of primary importance,
and the app(s) secondary, I really would like as much
constraint checking in the database as possible.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: smcg2297@frii.com
Subject: Re: changing multiple pk's in one update
In-Reply-To: <49E3F474.8090505@frii.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