Gavin Hamill <gdh at laterooms.com> writes:
> Hi again,
>
> After our recent discussion about how running ALTER TABLE manually on each node was akin to breaking the Prime Directive, we've today tried a simple script consisting of a few simple ALTERs thusly:
>
> nik at cayenne:~$ sudo /usr/lib/postgresql/8.1/bin/slonik </root/nikslon.txt
> <stdin>:9: PGRES_FATAL_ERROR select "_replication".ddlScript(2, 'ALTER TABLE "Hotel" ADD COLUMN "TotalRooms2" int4;
>
> ALTER TABLE "NoAvailability" ADD COLUMN "Notes" text;
> ALTER TABLE "NoAvailability" ALTER COLUMN "Notes" SET STORAGE EXTENDED;
>
> ALTER TABLE "MyRewards" ADD COLUMN "Date" date;
> ALTER TABLE "MyRewards" ALTER COLUMN "Date" SET STORAGE PLAIN;
> ALTER TABLE "MyRewards" ALTER COLUMN "Date" SET NOT NULL;
> ALTER TABLE "MyRewards" ALTER COLUMN "Date" SET DEFAULT (now())::date;
>
> ALTER TABLE "Room" ADD COLUMN "HideRackRate" bool;
> ALTER TABLE "Room" ALTER COLUMN "HideRackRate" SET STORAGE PLAIN;

I think others have successfully characterized all the issues having
to do with deadlocks...

Your deadlocking problem is hiding *another* problem, namely that in <
1.2, you can't submit that script to EXECUTE SCRIPT and expect success
:-(.

The trouble in 1.1.5 and earlier is that the entire script is
submitted as a single query.  The planner generates plans based on the
state of things AT THE BEGINNING, before the first query runs.

Unfortunately, that means that the queries that alter things created
in (or after) the first query will all fail, because the planner knows
they aren't legitimate things to alter.

The fix is pretty easy:  Submit two scripts.

One consists of all the "ADD COLUMN" DDL statements; the other of the
other statements.

By the way, I think the "MyRewards" alterations probably break...  If
memory isn't disserving me, you can't SET NOT NULL until you have
updated all existing tuples to have values...

Anyway, split it into 2 scripts, that'll work out better...
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)