Thread: Alter Columns with Triggers

Alter Columns with Triggers

From
Adrian Parker
Date:
Hello. I'm quite new to Postgres. I've just been assigned the task of
changing 300+ Double Precision columns, spread out over 30+ tables, to
be of type Numeric(100, 3). Many of the columns have one or more
triggers/functions on them.

The alterations will run from a python program acting as a database
migrator, and access to the database will be disabled while the
migrator runs. I can progmatically query for all columns of type
double precision, and run Alter statements against them, but how do I
deal with Triggers/Functions? It seems I need to remove both the
trigger and function, and re-add them after the table is altered. Some
functions refer to others though.

None of the columns to be changed are primary or foreign keys.

Ideally I'd like a query that allows me to alter all the column types
which I need to alter despite the triggers/functions in place, but I
suppose this is not possible.

Is there a quick and easy fix? Currently I'm copying/pasting the
drop/create statements from pgadmin (its very slow and error prone)
and putting them in my python source.


Adrian

Re: Alter Columns with Triggers

From
Craig Ringer
Date:
On 12/07/2011 9:23 AM, Adrian Parker wrote:
> Is there a quick and easy fix? Currently I'm copying/pasting the
> drop/create statements from pgadmin (its very slow and error prone)
> and putting them in my python source.
>
I don't know about "quick and easy", but you can get the trigger
function sources from pg_catalog  using your Python program. The
argument types and function names are also there, which gives you enough
information to programmatically generate CREATE and DROP statements for
the functions, as well as ALTER TABLE functions to add and remove the
triggers.

As for dependencies: doesn't PL/PgSQL only check the existence of
functions called, etc, once a procedure is actually executed? I can't
remember for sure, but it might not prove to be an issue.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088     Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

Re: Alter Columns with Triggers

From
Sim Zacks
Date:
I don't recall any problems with functions or triggers, in my experience
the problems are with the views.

Any views that are on the table must be recreated.


In any case, I wrote a function a while back that can be fairly easily
modified to do what you want.

My function is for modifying a view that has other views referencing it.
It copies the definition of the views to a variable then does drop
cascade and recreates all the dependent views.


See http://archives.postgresql.org/pgsql-general/2005-02/msg01044.php


If triggers really are an issue, this query will give you the names and
finctions of all triggers on any table you want.

You can then copy the trigger and function definition, erase them and
recreate them.


select b.relname,a.tgname,c.proname from pg_trigger a join pg_class b on
b.oid=tgrelid

join pg_proc c on c.oid=tgfoid


Sim


On 07/12/2011 04:23 AM, Adrian Parker wrote:

> Hello. I'm quite new to Postgres. I've just been assigned the task of
> changing 300+ Double Precision columns, spread out over 30+ tables, to
> be of type Numeric(100, 3). Many of the columns have one or more
> triggers/functions on them.
>
> The alterations will run from a python program acting as a database
> migrator, and access to the database will be disabled while the
> migrator runs. I can progmatically query for all columns of type
> double precision, and run Alter statements against them, but how do I
> deal with Triggers/Functions? It seems I need to remove both the
> trigger and function, and re-add them after the table is altered. Some
> functions refer to others though.
>
> None of the columns to be changed are primary or foreign keys.
>
> Ideally I'd like a query that allows me to alter all the column types
> which I need to alter despite the triggers/functions in place, but I
> suppose this is not possible.
>
> Is there a quick and easy fix? Currently I'm copying/pasting the
> drop/create statements from pgadmin (its very slow and error prone)
> and putting them in my python source.
>
>
> Adrian
>