[#1010960] ALTER TABLE ... ALTER COLUMN TYPE

Date:
2010-12-13 08:40 Priority:
3

State:
Open

Submitted by:
Uwe Bartels (uwebartels) Assigned to:
Nobody (None)

Category:
Interface Improvements (example) Group:
Next Release (example)

Summary:
ALTER TABLE ... ALTER COLUMN TYPE

Detailed description
Hi, i tried to improve pg_reorg by locking column definitions in pg_attribute. select * from pg_attribute where attrelid = <oid of reorganized table> and attnum>0 for update; so far it works well. in session 1 i run pg_reorg on a table. in session 2 i run a function which locks column definitions as shown above + simply sleeping for hours. in session 3 i tried to run an alter table pgbench_accounts alter column filler type char(85); the statement in session 3 waited as long as i did not stop the function started in session 2. So this would definetly prevent the alter table statement run during pg_reorg. The less WARNINGS you have - the better right? If you need that ACCESS EXCLUSIVE lock on the table you could check if there is any sql waiting for a lock on this table and then request to the user to cancel that statement. Then on the other hand - when i stopped the function in session 2 while pg_reorg was still running - I got an error message: pgbench=> alter table pgbench_accounts alter column filler type char(85); ERROR: cannot alter table "pgbench_accounts" because column "log_16392"."row" uses its rowtype So this brings up the question if I ever can change a column type during usage of the column type "log_16392"."row". It migth be that i've overseen something. but the people will make easier use of it the less possibilities exist to destroy you data. anyway this tool loks great and very professional. thanks. Uwe

Followup

Message
Date: 2011-01-05 09:07 Sender: Takahiro Itagaki > but here we are working on objects in the > system catalog and why not using locks on them? Queries that locks system catalog rows also locks the target relations. So, I think relation-level locking is enough. In addition, pg_reorg uses multiple transactions to build a new relation. So, we cannot always prevent system catalog changes from DDLs executed concurrently. Am I missing something? BTW, if we move the reorganizing feature into the core, there might be another solution like CREATE INDEX CONCURRENTLY, but pg_reorg is still an external module.
Date: 2011-01-05 08:57 Sender: Uwe Bartels Hi Takahiro, the locking mechanism for system tables are the same like those for user tables. normally i don't lock explicitely rows or tables in the system catalog, but here we are working on objects in the system catalog and why not using locks on them? but as i wrote in my previous post, by using this column type of the table "log_16392"."row", you already lock these columns implicitly. best... Uwe
Date: 2011-01-05 02:54 Sender: Takahiro Itagaki Do you lock rows in system catalogs directly with SQL? If so, I think it's a very rare use-cases...

No Changes Have Been Made to This Item