On Mon, Oct 17, 2011 at 8:44 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote: > On 17 October 2011 15:20, Merlin Moncure <mmoncure@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
