Skip site navigation (1) Skip section navigation (2)

BUG #6601: Inconsistent behavior of ALTER TABLE ADD COLUMN

From: jon(dot)plotky(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6601: Inconsistent behavior of ALTER TABLE ADD COLUMN
Date: 2012-04-18 19:00:06
Message-ID: E1SKa6o-00080w-9l@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6601
Logged by:          jon
Email address:      jon(dot)plotky(at)gmail(dot)com
PostgreSQL version: 8.4.7
Operating system:   RedHat Enterprise Linux 6.2 (Linux version 2.6.32)
Description:        

Not sure if this is a bug or not.  Would like to know what (if any) behavior
is guaranteed about updates to the pg_attribute catalog table.

Issue: After adding one new column to each of two different tables, querying
pg_attribute shows the new column in one table but not the other.  This is a
problem for ActiveRecord, which tries to  maintain its table model based on
querying pg_attribute.

Facts:
- Behavior is repeatable on different servers/databases
- After a commit the pg_attribute table shows the new column in both tables
- Postgresql log shows difference after the two ALTER TABLE statements (see
below), with a "forked new backend" message always following the ALTER TABLE
that does not update pg_attribute. Don't know if this has anything to do
with anything, but the log messages are always the same
- No difference after adding "nop" ALTER TABLE to cause a "table rewrite" as
mentioned at http://www.postgresql.org/docs/8.4/static/sql-altertable.html


Postgresql log of ALTER TABLE that immediately updates pg_attribute:
2012-04-18 00:06:27.449 EDT|postgres|mcp_production|DEBUG: 
StartTransactionCommand
2012-04-18 00:06:27.449 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"users" ADD COLUMN "fo_mapping_id" integer
2012-04-18 00:06:27.450 EDT|postgres|mcp_production|LOG:  statement: ALTER
TABLE "users" ADD COLUMN "fo_mapping_id" integer
2012-04-18 00:06:27.450 EDT|postgres|mcp_production|DEBUG:  ProcessUtility
2012-04-18 00:06:27.450 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"users" ADD COLUMN "fo_mapping_id" integer
2012-04-18 00:06:27.450 EDT|postgres|mcp_production|DEBUG: 
CommitTransactionCommand
2012-04-18 00:06:27.450 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"users" ADD COLUMN "fo_mapping_id" integer
2012-04-18 00:06:27.452 EDT|postgres|mcp_production|DEBUG: 
StartTransactionCommand
2012-04-18 00:06:27.452 EDT|postgres|mcp_production|STATEMENT:  SELECT id
FROM greetings WHERE user_id=284 limit 1


Postgresql log of ALTER TABLE that does not update pg_attribute:
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|DEBUG: 
StartTransactionCommand
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"organizations" ADD COLUMN "default_foid" integer
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|LOG:  statement: ALTER
TABLE "organizations" ADD COLUMN "default_foid" integer
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|DEBUG:  ProcessUtility
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"organizations" ADD COLUMN "default_foid" integer
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|DEBUG: 
CommitTransactionCommand
2012-04-18 00:06:27.558 EDT|postgres|mcp_production|STATEMENT:  ALTER TABLE
"organizations" ADD COLUMN "default_foid" integer
2012-04-18 00:06:27.580 EDT|||DEBUG:  forked new backend, pid=4626 socket=8
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:  postgres
child[4626]: starting with (
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:      postgres
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:      -v196608
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:      -y
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:     
mcp_production
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:  )
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:  InitPostgres
2012-04-18 00:06:27.580 EDT|postgres|mcp_production|DEBUG:  my backend id is
49
2012-04-18 00:06:27.581 EDT|postgres|mcp_production|DEBUG: 
StartTransaction
2012-04-18 00:06:27.581 EDT|postgres|mcp_production|DEBUG:  name: unnamed;
blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1,
children:
2012-04-18 00:06:27.582 EDT|postgres|mcp_production|DEBUG: 
CommitTransaction
2012-04-18 00:06:27.582 EDT|postgres|mcp_production|DEBUG:  name: unnamed;
blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1,
children:









Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-04-18 23:44:55
Subject: Re: BUG #6601: Inconsistent behavior of ALTER TABLE ADD COLUMN
Previous:From: Robert HaasDate: 2012-04-18 15:45:28
Subject: Re: BUG #6204: Using plperl functions generate crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group