Re: rule question

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