Re: rule question

[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.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au