SEO

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?

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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]

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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]

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Mark Morgan Lloyd wrote:
>
> 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?

They will wait without an explicit NOWAIT. This applies whether the
transaction is explicit or implicit.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Richard Huxton wrote:
>
> Mark Morgan Lloyd wrote:
> >
> > 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?
>
> They will wait without an explicit NOWAIT. This applies whether the
> transaction is explicit or implicit.

Just what I was hoping. Many thanks for your help :-)

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/