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@gmail.com>
> To: <pgsql-general@postgresql.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@une.edu.au