On Fri, Aug 26, 2005 at 11:25:37AM -0700, Ian Burrell wrote:
> 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 = ''N'';
> UPDATE ua_users SET is_spider = ''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 = ''N'';
> UPDATE ua_users SET is_internal = ''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. 
> 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.

This is expected behaviour. You can only update columns on the master.
Changes to the table schemas however, need to go through EXECUTE SCRIPT.

Try sending all of the ALTER TABLE statements through slonik, and
running the UPDATE statements directly to the master database.


Tim

> 
>  - Ian
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general

-- 
Tim Goodaire    416-673-4126    tgoodair at ca.afilias.info
Database Administrator, Afilias Canada Corp.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://gborg.postgresql.org/pipermail/slony1-general/attachments/20050826/01ff09d2/attachment.bin