Skip site navigation (1) Skip section navigation (2)

Re: Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Daniel Farina <daniel(at)heroku(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Nikhil Sontakke <nikkhils(at)gmail(dot)com>, Nikhil Sontakke <nikhil(dot)sontakke(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Date: 2012-01-14 10:37:28
Message-ID: CAP7Qgm=B7_wcBtjVnmuBKR-CCxZuwEfSoi9WshHfC5qJkmmnGA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sat, Jan 14, 2012 at 2:25 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> On Fri, Jan 13, 2012 at 2:05 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Nov 29, 2011 at 10:10 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>
>>> I have plans to try to improve this, but it's one of those things that
>>> I care about more than the people who write the checks do, so it
>>> hasn't quite gotten to the top of the priority list yet.
>>
>> All right... I have a patch that I think fixes this, at least so far
>> as relations are concerned.  I rewrote
>> RangeVarGetAndCheckCreationNamespace() extensively, did surgery on its
>> callers, and then modified CREATE OR REPLACE VIEW and ALTER <relkind>
>> .. SET SCHEMA to make use of it rather than doing things as they did
>> before.
>>
>> So this patch prevents (1) concurrently dropping a schema in which a
>> new relation is being created, (2) concurrently dropping a schema into
>> which an existing relation is being moved, and (3) using CREATE OR
>> REPLACE VIEW to attempt to obtain a lock on a relation you don't own
>> (the command would eventually fail, of course, but if there were, say,
>> an outstanding AccessShareLock on the relation, you'd queue up for the
>> lock and thus prevent any further locks from being granted, despite
>> your lack of any rights on the target).
>
> The patch looks ok, though I wonder if we could have a way to release
> the lock on namespace much before the end of transaction. Since the
> lock is held all the time, now the possibility of dead lock is bigger.
> Say,
>
> -- tx1
> BEGIN;
> SELECT * FROM s.x;
> DROP SCHEMA t;
>
> -- tx2
> BEGIN;
> SELECT * FROM t.y;
> DROP SCHEMA s;

Although I wrote
> I know it's a limited situation, though. Maybe the right way would be
> to check the namespace at the end of the transaction if any object is
> created, rather than locking it.

actually what's surprising to me is that even SELECT holds lock on
namespace to the end of transaction. The ideal way is that we lock
only on CREATE or other DDL.

Thanks,
-- 
Hitoshi Harada

In response to

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2012-01-14 11:09:28
Subject: Re: Command Triggers
Previous:From: Hitoshi HaradaDate: 2012-01-14 10:25:01
Subject: Re: Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group