Обсуждение: 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
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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: > 269.21.1/1302 - Release Date: 2/27/2008 4:34 PM > >
[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.