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

Re: rule question

From: Klint Gore <kgore4(at)une(dot)edu(dot)au>
To: Devi <devi(at)visolve(dot)com>
Cc: Tim Rupp <caphrim007(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: rule question
Date: 2008-02-29 06:00:59
Message-ID: 47C79F9B.6040000@une.edu.au (view raw or flat)
Thread:
Lists: pgsql-general
[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


In response to

Responses

pgsql-general by date

Next:From: Greg SmithDate: 2008-02-29 07:08:13
Subject: Re: WAL Log Size
Previous:From: Tom LaneDate: 2008-02-29 05:47:22
Subject: Re: "Consider compacting this relation..." ???

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