I've been working successfully with postgreSQL 7.4 for a while now, and we're now finally picking up a recent version (8.3.5). Unfortunately my existing migration code is failing due to some changes with 8.3. my $sql =<<EOF; ALTER TABLE instances ADD COLUMN udp_icpside_address_override inet DEFAULT NULL; ALTER TABLE instances ADD COLUMN udp_setside_address_override inet DEFAULT NULL; UPDATE instances SET udp_icpside_address_override = $udp_icpside_address_override; UPDATE instances SET udp_setside_address_override = $udp_setside_address_override; ALTER TABLE instances DROP COLUMN public_ip; ALTER TABLE instances DROP COLUMN public_ip_override; ALTER TABLE instances DROP COLUMN lan_mode; EOF $dbh->do($sql); This Perl is querying the db's existing state and conditionally migrating based on options in existing columns that I am getting rid of. The public_ip, public_ip_override and lan_mode columns are dropped in favour of the new udp_icpside_address_override and udp_setside_address_override columns, whose contents are determined conditionally but default to NULL. This worked in 7.4 but fails now with cannot ALTER TABLE "instances" because it has pending trigger events Under 8.3 what is the proper way to migrate a table like this? I know that I can now change the type of the existing column, but in this case I'm trading 3 columns for 2, and conditionally migrating their contents. I would appreciate a better understanding of this underlying system trigger (which is not mine) and why it is blocking me here, and how to do this using postgres best practices under 8.3. Thanks, Mike -- Michael P. Soulier <michael_soulier@xxxxxxxxx>, 613-592-2122 x2522 "Any intelligent fool can make things bigger and more complex... It takes a touch of genius - and a lot of courage to move in the opposite direction." --Albert Einstein
Attachment:
signature.asc
Description: OpenPGP digital signature
