Re: alter column resize triggers question

Поиск
Список
Период
Сортировка
От Bob Lunney
Тема Re: alter column resize triggers question
Дата
Msg-id 162237.94877.qm@web39706.mail.mud.yahoo.com
обсуждение исходный текст
Ответ на alter column resize triggers question  (Mike Broers <mbroers@gmail.com>)
Список pgsql-admin
Дерево обсуждения
Mike,

Doesn't look like it, at least on 8.4.  Give the script below a try for yourself.

Another approach would be to create a new table with the schema you need, insert the rows from your existing table into it, rename the two tables appropriately, then recreate the indexes and trigger on the new table.  That way you won't have to worry about the trigger firing at all.

Bob Lunney

==============

create table blah (blah int, ts timestamptz);                                                                     

create function update_timestamp() returns trigger as $$
begin
  new.ts = now();
  return new;
end;
$$ language plpgsql;

create trigger blah_t
before insert or update on blah for each row 
execute procedure update_timestamp();

insert into blah values (1);
insert into blah values (2);
insert into blah values (3);
select * from blah;

 blah |              ts
------+-------------------------------
    1 | 2010-06-21 14:33:32.14576-04
    2 | 2010-06-21 14:33:34.545739-04
    3 | 2010-06-21 14:33:36.097878-04
(3 rows)

alter table blah alter column blah type bigint;
select * from blah;

 blah |              ts
------+-------------------------------
    1 | 2010-06-21 14:33:32.14576-04
    2 | 2010-06-21 14:33:34.545739-04
    3 | 2010-06-21 14:33:36.097878-04
(3 rows)

===============

--- On Mon, 6/21/10, Mike Broers <mbroers@gmail.com> wrote:

From: Mike Broers <mbroers@gmail.com>
Subject: [ADMIN] alter column resize triggers question
To: pgsql-admin@postgresql.org
Date: Monday, June 21, 2010, 2:18 PM

Pg v8.3.8

I have a table whose column size needs to be increased:

 \d dim_product
                                                  Table "report.dim_product"
        Column        |           Type           |                                      Modifiers                                      
----------------------+--------------------------+--------------------------------------------------------------------------------------
 product_id | integer                  | not null default nextval('dim_product_id_seq'::regclass)
 application_id       | integer                  | not null
 source_product_cd    | integer                  | not null
 product_type         | character varying(20)    | not null
 product_name         | character varying(100)   | not null
 vendor_offer_cd      | character varying(30)    |
 service_name         | character varying(20)    |
 category             | character varying(40)    |
 svc_line_cd          | character varying(40)    |
 established          | timestamp with time zone | not null
 modified             | timestamp with time zone | not null
Indexes:
    "dim_product_pkey" PRIMARY KEY, btree (product_id)
    "idx_dim_product_modified" btree (modified)
    "idx_dim_product_source_product_cd" btree (source_product_cd)
Triggers:
    t_dim_product_timestamp_b_iu BEFORE INSERT OR UPDATE ON dim_product FOR EACH ROW EXECUTE PROCEDURE public.update_timestamps()


I need to change service_name column to varchar(55), my plan was to backup the table with pg_dump, then run the below alter statement:

alter table dim_product alter column service_name type varchar(55);

But i am worried about the triggers because I believe that the alter table statement will rewrite the table and I dont want those triggers firing.  Does anyone know if I need to disable these triggers prior to the alter table statement, or if there are any other dependencies or precautions I should review before attempting this action?  I have also seen there is a workaround with running updates to the pg_attribute table but frankly that makes me a little nervous.

Thanks in advance,
Mike




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

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: alter column resize triggers question
Следующее
От: "Prasad DVN"
Дата:
Сообщение: Re: nmsbdmgr doesnt start