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

Re: rule question

From: Tim Rupp <caphrim007(at)gmail(dot)com>
To: Klint Gore <kgore4(at)une(dot)edu(dot)au>
Cc: Devi <devi(at)visolve(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: rule question
Date: 2008-02-29 10:08:39
Message-ID: 47C7D9A7.1030802@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Klint Gore wrote:
> [see below or the top posting police will arrive on my doorstep :)]
> 
> Devi wrote:
>> Hi,
>>
>> CREATE RULE dosen't require any lock.  It is carried out in the parser 
>> level.  But there will be ACCESS SHARE lock over the tables which are 
>> being queried & are acquired automatically.
>>
>> Thanks
>> DEVI.G
>> ----- Original Message ----- From: "Tim Rupp" <caphrim007(at)gmail(dot)com>
>> To: <pgsql-general(at)postgresql(dot)org>
>> Sent: Friday, February 29, 2008 8:47 AM
>> Subject: [GENERAL] rule question
>>
>>
>>> Hey list,
>>>
>>> Does CREATE RULE require an exclusive lock on the table it's making a 
>>> rule for? For instance, if an insert is being done on the table, and 
>>> you do 'create rule', it will wait for said insert to finish?
>>>
>>> Thanks,
>>> -Tim
> Seems to me like needs an exclusive lock.  I setup 2 sessions. first one 
> idle in transaction after an insert and then issued the create rule in 
> the other.  the 2nd one sat there.
> 
> pg_locks in the 1st one said
> # select * from pg_locks where relation = 20404;
> locktype | database | relation | page | tuple | virtualxid | 
> transactionid | classid | objid | objsubid | virtualtransaction | pid  
> |        mode         | granted
> ----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---- 
> 
> -----
> relation |    16770 |    20404 |      |       |            
> |               |      |       |          | 1/921              |  632 | 
> RowExclusiveLock    | t
> relation |    16770 |    20404 |      |       |            
> |               |      |       |          | 2/771              | 3812 | 
> AccessExclusiveLock | f
> (2 rows)
> 
> (ignore the formatting the important bit is pid, mode, granted) 3812 is 
> the pid of my create rule according to pg_backend_pid() and 632 is my 
> insert transaction.
> 
> Execution of the rule follows what you were saying.
> 
> klint.
> 

Thanks for the info guys, I'll use it to observe my own setup here.

One other question. If the lock needed is exclusive, and more inserts 
come in after it is requested, will Postgres schedule the rule to be 
created before those new inserts are allowed to happen? Or can the rule 
request sit there and wait indefinitely for it's exclusive lock.

Thanks!
-Tim

In response to

Responses

pgsql-general by date

Next:From: Oleg BartunovDate: 2008-02-29 10:11:17
Subject: Re: Text Search zero padding
Previous:From: Richard HuxtonDate: 2008-02-29 09:55:27
Subject: Re: Text Search zero padding

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