Re: Locking for function creation

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

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

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