On Tue, 2004-08-17 at 12:22, Pierre-FrÃdÃric Caillaud wrote: > Suppose your table is like : > > key1 key2 > 1 1 > 1 2 > 2 1 > > To get the next value to insert for key1=1 you can do this : > > SELECT key2 FROM ... WHERE key1=1 ORDER BY key2 DESC LIMIT 1 > > Of course a UNIQUE INDEX on key1, key2 helps. > > You won't be protected from two transactions adding the same value at the > same time, though. The unique index will catch them and one of them will > fail (constraint violation etc). Just retry the transaction until it > works... or, be a warrior and lock the table... but if you do that, please > do it in a function/trigger so that it's not kept locked for long !
Actually, it should be transactionally safe to put this in a trigger and use select ... for update ... which should lock the key1/key2 combo you're operating on for the short period of the trigger running, and block other triggers from running at the same time on those data. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
