Re: how to make functions multi-user safe? - Mailing list pgsql-general

From Jakub Ouhrabka
Subject Re: how to make functions multi-user safe?
Date
Msg-id Pine.LNX.4.44.0212051411230.24253-100000@server
Whole thread Raw
In response to how to make functions multi-user safe?  (Robert Treat <xzilla@users.sourceforge.net>)
List pgsql-general
hi,

> Due to the limitation that SetQuerySnapshot() is not called from within plpgsql functions, we
> are having trouble making a function safe for a multiuser environment.
>
> Our goal: To have a multiuser-safe function that checks for the existence of a table.  If the table
> exists, it should return the name of the table.  If it does not exist, it will call another
> function that creates the table and then it should return the name of the table after it's been created.
> ...

i'd suggest encapsulating the whole function in LOCK lock_table IN
EXCLUSIVE MODE; to avoid concurrent access to the function and using
UPDATE pg_class SET relname=relname WHERE relname=quote_ident(''foo'') for
checking for the table existence (if there are any affected rows...). i
think that for update and delete the snapshot is taken when executing the
statement not before the function start as in the case of select.

hth,

kuba




pgsql-general by date:

Previous
From: Adam Witney
Date:
Subject: Re: DbVisualizer and PG 7.3?
Next
From: Csaba Nagy
Date:
Subject: Re: DbVisualizer and PG 7.3?