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@xxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Friday, February 29, 2008 8:47 AM
Subject: 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
