Обсуждение: Locking for function creation
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]
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
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]
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
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]
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
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]