ALTER TABLE .. SET SCHEMA lock strength

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