Re: plpgsql; execute query inside exists

On Mon, Oct 17, 2011 at 8:44 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
> On 17 October 2011 15:20, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> A better way to do this is to query information_schema:
>>
>> PERFORM 1 FROM information_schema.tables where schema_name = x and
>> table_name = y;
>>
>> IF FOUND THEN
>>  CREATE TABLE ...
>> END IF;
>>
>> (there is a race condition in the above code -- do you see it? if
>> concurrent access to this function is an issue, you have to LOCK an
>> object before running the PERFORM or perhaps use an advisory lock).
>
> Is there? You'd think that with transactional DDL and the code running
> in a single transaction (namely inside a stored function) it would be
> concurrency-safe.

Transactional DDL does not protect you from race conditions any more
than MVCC protects you from race conditions in regular DML. What
transactional DDL does is roll back the changes in the event of an
error so you don't have half written schema changes in your database.
MVCC gives a rigorous definition of visibility rules and transactions
guarantee only a complete unit of work getting committed to the
database. You still have to code defensively against multi-user
access however. The good news is that multi user coding is about an
order of magnitude easier in sql (especially postgres variant) than in
any other development platform that I'm aware of.

The race condition here is basically the same problem that affects
'upsert' patterns:

test record(s) if found update if not found insert;

The problem comes that in between the test and the insert case someone
else can also test and get the insert in before you do. You have two
general strategies to get around this: locking and retry. I greatly
advise going the locking route unless your concurrency requirements
are very high. It's much simpler, and since you're not invoking a
subtransaction, faster in the uncontested case.

merlin