Postgres Pro
Downloads
Home   >   mailing lists

Re: alter column resize triggers question - Mailing list pgsql-admin

From Bob Lunney
Subject Re: alter column resize triggers question
Date
Msg-id 162237.94877.qm@web39706.mail.mud.yahoo.com
Whole thread Raw
In response to alter column resize triggers question  (Mike Broers <mbroers@gmail.com>)
List pgsql-admin
Tree view
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 by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: alter column resize triggers question
Next
From: "Prasad DVN"
Date:
Subject: Re: nmsbdmgr doesnt start