Re: Locking for function creation

Richard Huxton wrote:

> 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.

I /think/ so, but it was buried deep in custom scripting and (usual story) I was
under pressure to get something else done at the time :-)

> > 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).

Thanks, noted. I'm not expecting this to be a regular occurence since in general
the only time multiple sessions will be running will be during maintenance.

If I might ask a related question- assuming that a client has grabbed a
restrictive lock during a transaction that e.g. is create/replacing functions,
what happens to other sessions that attempt to run a select or update- will they
fail (i.e. an implicit NOWAIT) or will they wait until the lock is released?
Does this vary depending on whether a select/update is within an explicit
transaction?

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]