Search Postgresql Archives

how do you get there from here?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux