Re: Moving from MySQL to PGSQL....some questions (multilevel

On 2004.03.03 12:43 scott.marlowe wrote:

>
> Next, what you need is a before trigger that will take any row being
> inserted, select the max(id2) where id1 = whatwereinserting adds one
> to it
> and makes that the new id2.
>
> Look up before triggers. plpgsql is a good language to do this in.
> Note
> that on large tables it WILL BE SLOW.

To make it fast, you'd want to keep the max(id2) value on the table
keyed by id1. Your trigger would update the max(id2) value as well
as alter the row being inserted. To keep from having problems with
concurrent inserts, you'd need to perform all inserts inside
serialized transactions. The only problem I see is that there's
a note in the documentation that says that postgresql's serialization
dosen't always work. Anybody know if it would work in this case?

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein