BUG #6601: Inconsistent behavior of ALTER TABLE ADD COLUMN - Mailing list pgsql-bugs

The following bug has been logged on the website:

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

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:=20
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:=20
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:=20
StartTransactionCommand
2012-04-18 00:06:27.452 EDT|postgres|mcp_production|STATEMENT:  SELECT id
FROM greetings WHERE user_id=3D284 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:=20
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:=20
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=3D4626 socket=
=3D8
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:=20=20=20=20=20
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:=20
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:=20
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: