| Ian Burrell 2005-08-26, 1:24 pm |
| Earlier this week, we got an error applying an EXECUTE SCRIPT on the
slave commands with an UPDATE command in the script. The script
looked like:
ALTER TABLE ua_users ADD COLUMN is_spider VARCHAR(1);
UPDATE ua_users SET is_spider =3D ''N'';
UPDATE ua_users SET is_spider =3D ''Y'' WHERE login_id LIKE ''webtest_'';
ALTER TABLE ua_users ALTER is_spider SET DEFAULT ''N'';
ALTER TABLE ua_users ADD CONSTRAINT ck_is_spider CHECK (is_spider IN
(''Y'', ''N''));
ALTER TABLE ua_users ALTER is_spider SET NOT NULL;
ALTER TABLE ua_users ADD COLUMN is_internal VARCHAR(1);
UPDATE ua_users SET is_internal =3D ''N'';
UPDATE ua_users SET is_internal =3D ''Y'' WHERE email_address LIKE
''%rentrak.com'';
ALTER TABLE ua_users ALTER is_internal SET DEFAULT ''N'';
ALTER TABLE ua_users ADD CONSTRAINT ck_is_internal CHECK (is_internal
IN (''Y'', ''N''));
ALTER TABLE ua_users ALTER is_internal SET NOT NULL;
Basically, add a new column, populate it with the UPDATE, and then add
constraints. It failed on both slaves with:
PGRES_FATAL_ERROR ERROR: could not find trigger 946964630.
That trigger is part of a foreign key between ua_users and
ua_subscriptions. It is the trigger on the ua_subscriptions table.=20
Both tables are replicated in the same set so I would expect that the
EXECUTE SCRIPT command would restore the foreign key triggers on both
tables. We have had a similar errors when doing UPDATEs in EXECUTE
SCRIPT for similar scripts.
I fixed the problem by modifying the DDL_SCRIPT event to just add the
tables, ran the UPDATE normally, and then made the constraints in a
separate execute script.
- Ian
|