Re: Locking for function creation

Mark Morgan Lloyd wrote:
> Richard Huxton wrote:
>> Mark Morgan Lloyd wrote:
>>> If there's a risk that multiple clients will try to execute a 'create or
>>> replace function' simultaneously, what's the recommended practice for
>>> putting it in a transaction and/or locking it? If a lock's incolved what
>>> should this be applied to- the table that the function is most likely to
>>> be involved with, an arbitrary table, or a dummy table specifically
>>> reserved for this purpose?
>> What problem are you trying to prevent here? Do you want a particular
>> version of the function to be available for a certain amount of time?
>
> I don't anticipate that the function will change, but it's (re)defined by a
> script triggered periodically on a client system. I'm pretty sure that I've seen
> a problem whilst I was doing maintenance when two clients tried to redefine it
> simultaneouly (i.e. on one of them the redefinition failed rather than waiting),

Was it "tuple concurrently updated"? You can reproduce this fairly
simply by issuing BEGIN...CREATE OR REPLACE FUNCTION f... in two
different psql sessions and delaying COMMIT appropriately. AFAIK it's
harmless, but does abort your transaction.

> in the interim I've set up a transaction with a lock on the table that is most
> likely to be involved noting that by default the lock type is the most
> restrictive.

You probably want a userlock (see contrib/), or as low-impact a lock as
you can get away with. Perhaps lock your dummy table (row contains
function schema/name?). You'll still want to code your application in
such a way that it copes with errors though - the lock attempt can
always time out (in theory anyway).

--
Richard Huxton
Archonet Ltd