Re: [HACKERS] Change lock requirements for adding a trigger
Added to TODO: * Reduce locking requirements for creating a trigger http://archives.postgresql.org/pgsql-hackers/2008-06/msg00635.php --- Simon Riggs wrote: On Wed, 2008-06-04 at 16:33 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. Looking at this area of locking, I've noticed that the locks held by CREATE TRIGGER are more of a problem than might be apparent. * Locks held by CREATE TRIGGER are an issue for trigger-based replication systems, where triggers are frequently added and removed to various tables. * ALTER TABLE .. ADD FOREIGN KEY holds an AccessExclusiveveLock on *both* referencing and referenced tables. It does this because we must add triggers to both tables. So reducing the lock strength required by CREATE TRIGGER would also allow a reduction in lock strength for adding FKs. So useful steps will be to * refactor pg_class code so that CREATE TRIGGER uses an identical approach to CREATE INDEX * reduce lock strength for CREATE TRIGGER and ALTER TABLE ... ADD FOREIGN KEY so that it takes a ShareLock during ATAddForeignKeyConstraint() * look at how we can reduce lock strength for other ALTER TABLE subcommands. Not sure how yet. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 16:33 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. Looking at this area of locking, I've noticed that the locks held by CREATE TRIGGER are more of a problem than might be apparent. * Locks held by CREATE TRIGGER are an issue for trigger-based replication systems, where triggers are frequently added and removed to various tables. * ALTER TABLE .. ADD FOREIGN KEY holds an AccessExclusiveveLock on *both* referencing and referenced tables. It does this because we must add triggers to both tables. So reducing the lock strength required by CREATE TRIGGER would also allow a reduction in lock strength for adding FKs. So useful steps will be to * refactor pg_class code so that CREATE TRIGGER uses an identical approach to CREATE INDEX * reduce lock strength for CREATE TRIGGER and ALTER TABLE ... ADD FOREIGN KEY so that it takes a ShareLock during ATAddForeignKeyConstraint() * look at how we can reduce lock strength for other ALTER TABLE subcommands. Not sure how yet. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On Jun 3, 2008, at 5:04 PM, Simon Riggs wrote: On Tue, 2008-06-03 at 16:48 -0500, Decibel! wrote: On May 30, 2008, at 9:51 AM, Simon Riggs wrote: On Thu, 2008-05-29 at 19:18 -0500, Decibel! wrote: Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. This is constrained by locking. There are a subset of DDL commands that might be able to be performed with just an ExclusiveLock or ShareLock rather than an AccessExclusiveLock. Nobody has studied which sub-statements this might apply to, but its do-able since CREATE INDEX already does this. Is there a good way to determine this other than depending on knowledge of the source code? The source doesn't know yet. So just analysis and thinking. The mechanism to hold less than an AccessExclusiveLock it doesn't exist yet, but it never will unless we have a list of the things that might be performed correctly with a lower level of lock. Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Change lock requirements for adding a trigger
Decibel! [EMAIL PROTECTED] writes: Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? pg_class.reltriggers. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 10:59 -0400, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: Ok, I'll take a stab at such a list. Can anyone think of any reasons why CREATE TRIGGER couldn't get by with ShareLock? pg_class.reltriggers. ISTM that we do this in many ways on pg_class, if we believe the docs. We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Seems we should have one consistent way of adding associated objects. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. My point was just that you can't arbitrarily decide that some operation needs only a given strength of lock if you are not up to speed on these sorts of details. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On Wed, 2008-06-04 at 16:33 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: We have * relhasindex (bool) set by CREATE INDEX but not unset by DROP INDEX * relhasrules (bool) * reltriggers (int2) set by CREATE and DROP, since its an integer Right. If CREATE INDEX can take a Share lock and can update pg_class, why would it not be theoretically possible for CREATE TRIGGER? It's (probably) theoretically possible, if we replace reltriggers with a bool that acts more like relhasindex, ie it's a hint to go look in pg_triggers. My point was just that you can't arbitrarily decide that some operation needs only a given strength of lock if you are not up to speed on these sorts of details. Understood. Wouldn't have looked there without your hint. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On May 30, 2008, at 9:51 AM, Simon Riggs wrote: On Thu, 2008-05-29 at 19:18 -0500, Decibel! wrote: Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. This is constrained by locking. There are a subset of DDL commands that might be able to be performed with just an ExclusiveLock or ShareLock rather than an AccessExclusiveLock. Nobody has studied which sub-statements this might apply to, but its do-able since CREATE INDEX already does this. Is there a good way to determine this other than depending on knowledge of the source code? -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Change lock requirements for adding a trigger
On Tue, 2008-06-03 at 16:48 -0500, Decibel! wrote: On May 30, 2008, at 9:51 AM, Simon Riggs wrote: On Thu, 2008-05-29 at 19:18 -0500, Decibel! wrote: Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. This is constrained by locking. There are a subset of DDL commands that might be able to be performed with just an ExclusiveLock or ShareLock rather than an AccessExclusiveLock. Nobody has studied which sub-statements this might apply to, but its do-able since CREATE INDEX already does this. Is there a good way to determine this other than depending on knowledge of the source code? The source doesn't know yet. So just analysis and thinking. The mechanism to hold less than an AccessExclusiveLock it doesn't exist yet, but it never will unless we have a list of the things that might be performed correctly with a lower level of lock. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Change lock requirements for adding a trigger
On Thu, 2008-05-29 at 19:18 -0500, Decibel! wrote: Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. This is constrained by locking. There are a subset of DDL commands that might be able to be performed with just an ExclusiveLock or ShareLock rather than an AccessExclusiveLock. Nobody has studied which sub-statements this might apply to, but its do-able since CREATE INDEX already does this. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Change lock requirements for adding a trigger
Is there a reason that we can't add a trigger to a table while a select is running? This is a serious pain when trying to setup londiste or slony. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
