rule question

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

[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

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

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

On Fri, Feb 29, 2008 at 4:08 AM, Tim Rupp <caphrim007@gmail.com> wrote:

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.

PostgreSQL will process the transactions in order.