Re: Alter Columns with Triggers

Поиск
Список
Период
Сортировка
От Sim Zacks
Тема Re: Alter Columns with Triggers
Дата
Msg-id 4E1D284E.3060901@compulab.co.il
обсуждение исходный текст
Ответ на Alter Columns with Triggers  (Adrian Parker <adrian.l.parker@gmail.com>)
Список pgsql-general
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
>


В списке pgsql-general по дате отправления:

Предыдущее
От: Tony Wang
Дата:
Сообщение: Re: Weird problem that enormous locks
Следующее
От: Oleg Bartunov
Дата:
Сообщение: Re: ts_rank seems very slow (140 ranked documents / second on my machine)