Thread: ALTER TABLE .. SET SCHEMA lock strength
While reviewing the SQL/MED patch, I happened to notice that ExecAlterObjectSchemaStmt calls AlterTableNamespace with a lock mode argument of AccessExclusiveLock. Does anyone see a reason why ShareUpdateExclusiveLock would be insufficient? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Sat, 2011-01-01 at 11:06 -0500, Robert Haas wrote: > While reviewing the SQL/MED patch, I happened to notice that > ExecAlterObjectSchemaStmt calls AlterTableNamespace with a lock mode > argument of AccessExclusiveLock. Does anyone see a reason why > ShareUpdateExclusiveLock would be insufficient? It seemed unsafe to me to do that while an object was being accessed, since it effectively changes the search_path, which is dangerous. Seems like a good change, if it really is viable. -- Simon Riggs http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Sat, 2011-01-01 at 11:06 -0500, Robert Haas wrote:
>> While reviewing the SQL/MED patch, I happened to notice that
>> ExecAlterObjectSchemaStmt calls AlterTableNamespace with a lock mode
>> argument of AccessExclusiveLock. Does anyone see a reason why
>> ShareUpdateExclusiveLock would be insufficient?
> It seemed unsafe to me to do that while an object was being accessed,
> since it effectively changes the search_path, which is dangerous.
ALTER RENAME and ALTER SET SCHEMA are both in the nature of changing the
object's identity. Consider the fairly typical use-case where you are
renaming an "old" instance out of the way and renaming another one into
the same schema/name. Do you really want that to be a low-lock
operation? I find it really hard to envision a use case where it'd be
smart to allow some concurrent operations to continue using the the old
instance while others start using the new one.
regards, tom lane
On lör, 2011-01-01 at 13:17 -0500, Tom Lane wrote: > ALTER RENAME and ALTER SET SCHEMA are both in the nature of changing the > object's identity. Consider the fairly typical use-case where you are > renaming an "old" instance out of the way and renaming another one into > the same schema/name. Do you really want that to be a low-lock > operation? I find it really hard to envision a use case where it'd be > smart to allow some concurrent operations to continue using the the old > instance while others start using the new one. At least in Unix land, that's a handy property. And we're frequently cursing those other operating systems where it doesn't work that way.
On Sat, Jan 1, 2011 at 4:24 PM, Peter Eisentraut <peter_e@gmx.net> wrote: > On lör, 2011-01-01 at 13:17 -0500, Tom Lane wrote: >> ALTER RENAME and ALTER SET SCHEMA are both in the nature of changing the >> object's identity. Consider the fairly typical use-case where you are >> renaming an "old" instance out of the way and renaming another one into >> the same schema/name. Do you really want that to be a low-lock >> operation? I find it really hard to envision a use case where it'd be >> smart to allow some concurrent operations to continue using the the old >> instance while others start using the new one. > > At least in Unix land, that's a handy property. And we're frequently > cursing those other operating systems where it doesn't work that way. Yeah, exactly. If someone is renaming an old instance out of the way and sticking a new one in its place, the LAST thing you want to do is lock out queries unnecessarily. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company