Обсуждение: how do you get there from here?
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@mitel.com>, 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
Вложения
"Michael P. Soulier" <michael_soulier@mitel.com> writes:
> This worked in 7.4 but fails now with
> cannot ALTER TABLE "instances" because it has pending trigger events
I think the problem is you're issuing all those commands in one
transaction. If you COMMIT the updates then the subsequent ALTERs
should go through.
regards, tom lane
Michael P. Soulier wrote: > 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; Ok, using the default to set the value on the new column seems to avoid this... my $sql =<<EOF; ALTER TABLE instances DROP COLUMN public_ip; ALTER TABLE instances DROP COLUMN public_ip_override; ALTER TABLE instances DROP COLUMN lan_mode; ALTER TABLE instances ADD COLUMN udp_icpside_address_override inet DEFAULT $udp_icpside_address_override; ALTER TABLE instances ADD COLUMN udp_setside_address_override inet DEFAULT $udp_setside_address_override; ALTER TABLE instances ALTER COLUMN udp_icpside_address_override SET DEFAULT NULL; ALTER TABLE instances ALTER COLUMN udp_setside_address_override SET DEFAULT NULL; EOF $dbh->do($sql); But I don't understand why this was required. What's wrong with adding a column and copying data into it in a transaction? Mike
Вложения
"Michael P. Soulier" <michael_soulier@mitel.com> writes:
> But I don't understand why this was required. What's wrong with adding a
> column and copying data into it in a transaction?
Nothing. The problem apparently is that you've got deferred AFTER
triggers on that table, so the UPDATE commands have left unprocessed
trigger events behind, and the system can't be sure that those events
would still be sensible to fire after doing further ALTERs on the table.
*Why* you've got such triggers is not apparent from what you've told us.
regards, tom lane
Tom Lane wrote:
> Nothing. The problem apparently is that you've got deferred AFTER
> triggers on that table, so the UPDATE commands have left unprocessed
> trigger events behind, and the system can't be sure that those events
> would still be sensible to fire after doing further ALTERs on the table.
>
> *Why* you've got such triggers is not apparent from what you've told us.
I've not explicitely created any triggers. The table has constraints,
and if that results in triggers created by the system, then that would
be why. All I know is that it worked in 7.4 without issue.
Here is the table in question, most of the code initially generated by
the Django framework...
tugdb=# \d instances
Table "public.instances"
Column | Type |
Modifier
s
------------------------------+------------------------+-------------------------------
-------------------------
id | integer | not null
default nextval('inst
ances_id_seq'::regclass)
tugid | character varying(128) | not null
status | integer | not null
lan_ip | inet | not null
wan_ip | inet |
transcoding | boolean | not null
local_streaming | boolean | not null
nlogs2keep | integer | not null
log_verbosity | integer | not null
detailed_jitter_log | boolean | not null
srtp_starting_port | integer | not null
srtp_ending_port | integer | not null
rtp_framesize | character varying(2) | not null
tftp_blocksize | character varying(4) | not null
default_minet_icp | integer |
default_sip_icp | integer |
node_id | integer | not null
daisy_ip | inet |
restrict_minet | boolean | not null
relax_icp_rtp_checks | boolean | not null
udp_icpside_address | inet |
udp_setside_address | inet |
disable_srtp | boolean | not null
default false
udp_icpside_address_override | inet |
udp_setside_address_override | inet |
time_format | integer | not null default 12
setside_codec | character varying(16) | not null
default 'g.729'::char
acter varying
Indexes:
"dashboard_tuginstance_pkey" PRIMARY KEY, btree (id)
"dashboard_tuginstance_tugid_key" UNIQUE, btree (tugid)
"dashboard_tuginstance_node_id" btree (node_id)
Check constraints:
"dashboard_tuginstance_default_minet_icp_check" CHECK
(default_minet_icp >= 0)
"dashboard_tuginstance_default_sip_icp_check" CHECK (default_sip_icp
>= 0)
"dashboard_tuginstance_nlogs2keep_check" CHECK (nlogs2keep >= 0)
"dashboard_tuginstance_srtp_ending_port_check" CHECK
(srtp_ending_port >= 0)
"dashboard_tuginstance_srtp_starting_port_check" CHECK
(srtp_starting_port >= 0)
"dashboard_tuginstance_status_check" CHECK (status >= 0)
Foreign-key constraints:
"dashboard_tuginstance_node_id_fkey" FOREIGN KEY (node_id)
REFERENCES nodes(id) DEFERRABLE INITIALLY DEFERRED
Mike
--
Michael P. Soulier <michael_soulier@mitel.com>, 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
Вложения
"Michael P. Soulier" <michael_soulier@mitel.com> writes:
> Tom Lane wrote:
>> *Why* you've got such triggers is not apparent from what you've told us.
> I've not explicitely created any triggers. The table has constraints,
> and if that results in triggers created by the system, then that would
> be why.
Yup...
> Foreign-key constraints:
> "dashboard_tuginstance_node_id_fkey" FOREIGN KEY (node_id)
> REFERENCES nodes(id) DEFERRABLE INITIALLY DEFERRED
This is what's creating the limitation.
regards, tom lane
Tom Lane wrote: >> Foreign-key constraints: >> "dashboard_tuginstance_node_id_fkey" FOREIGN KEY (node_id) >> REFERENCES nodes(id) DEFERRABLE INITIALLY DEFERRED > > This is what's creating the limitation. So, I'm working around it by creating the new columns with the default value that I want populated, and then resetting the default. This works, but it doesn't feel like a best practice. Can someone recommend a better way to say, consolidate 3 columns down to 2 and conditionally move contents around to the new columns, inside of a transaction involving ALTER TABLE calls? I'm just wondering if there is a better solution than what I've done. Mike -- Michael P. Soulier <michael_soulier@mitel.com>, 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
Вложения
Michael P. Soulier wrote: > So, I'm working around it by creating the new columns with the default > value that I want populated, and then resetting the default. This works, > but it doesn't feel like a best practice. > > Can someone recommend a better way to say, consolidate 3 columns down to > 2 and conditionally move contents around to the new columns, inside of a > transaction involving ALTER TABLE calls? > > I'm just wondering if there is a better solution than what I've done. In fact, I just ran into this again and I don't know, off the top of my head, how to solve this one completely. Postgres7 code: BEGIN; ALTER TABLE clients ADD COLUMN typelabel varchar(128); UPDATE clients SET typelabel = settype; UPDATE clients SET settype = 'Unknown'; ALTER TABLE clients ALTER COLUMN typelabel SET DEFAULT 'Unknown'; COMMIT; Working Postgres8 code: BEGIN; ALTER TABLE clients ADD COLUMN typelabel varchar(128) DEFAULT 'Unknown'; COMMIT; But this way I lose the copy of the settype column data over to the typelabel column. Can someone suggest a better way that doesn't hit this "pending trigger" issue while in a transaction? Thanks, Mike -- Michael P. Soulier <michael_soulier@mitel.com>, 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
Вложения
On May 13, 2009, at 8:48 PM, Michael P. Soulier wrote: > Can someone suggest a better way that doesn't hit this "pending > trigger" > issue while in a transaction? IIRC you had an initially deferred foreign key constraint? Do you absolutely need that to be initially deferred, or deferrable even? Because that's what's causing your problem, not the foreign key constraint itself. If that FK does need to be initially deferred, as a workaround you can temporarily disable the foreign key while doing your updates. Easiest is to drop the constraint, do your updates and recreate the constraint, at which point the database will verify that the related records match the constraint. Of course this opens a risk where a record gets inserted that doesn't match your FK constraint which will cause recreation to error out and your transaction to rollback. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a0d321a10092082955122!
Alban Hertroys wrote: > IIRC you had an initially deferred foreign key constraint? Do you > absolutely need that to be initially deferred, or deferrable even? > Because that's what's causing your problem, not the foreign key > constraint itself. Ok. That's from the automagickally-generated SQL from Django. I suppose they felt it was easier to set up the FK initially deferred. > If that FK does need to be initially deferred, as a workaround you can > temporarily disable the foreign key while doing your updates. Easiest is > to drop the constraint, do your updates and recreate the constraint, at > which point the database will verify that the related records match the > constraint. Of course this opens a risk where a record gets inserted > that doesn't match your FK constraint which will cause recreation to > error out and your transaction to rollback. Understood. I'll see what I can do. This migration is fully automated so it has to, via code, detect the existing constraints, remove them and reapply them during migration, if I go that route. I can't manually migrate a few thousand installs in the field. I'll follow-up if I find a good solution. Thanks, Mike -- Michael P. Soulier <michael_soulier@mitel.com>, 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