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;

', -1);  - ERROR:  deadlock detected
DETAIL:  Process 6282 waits for AccessExclusiveLock on relation 32311939 of database 32311222; blocked by process 6277.
Process 6277 waits for AccessShareLock on relation 32311734 of database 32311222; blocked by process 6282.
CONTEXT:  SQL statement "lock table "public"."Language" in access exclusive mode"
PL/pgSQL function "altertablerestore" line 47 at execute statement
SQL statement "SELECT  "_replication".alterTableRestore( $1 )"
PL/pgSQL function "ddlscript_int" line 47 at perform
SQL statement "SELECT  "_replication".ddlScript_int( $1 ,  $2 ,  $3 )"
PL/pgSQL function "ddlscript" line 31 at perform

I sent this to set 2 because all machines subscribe to that (it's only 8 tables - although every machine has the full schema - it won't matter if tables not in use are altered...)

We can run this script again and again, and after a few seconds get a deadlock on a different table each time. The really annoying thing here is the deadlocked table isn't even one we want to ALTER. Is there anything we can do here except for 'try again at 4am when things are quiet'?

Cheers,
Gavin.