alter column resize triggers question

From: Mike Broers <mbroers(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: alter column resize triggers question
Date: 2010-06-21 18:18:01
Message-ID: AANLkTikypY0RTsC0bNzx4c2tzeogj2bgthZggmSiOapd@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2010-06-21 18:28:33 Re: alter column resize triggers question
Previous Message Cassiano, Marco 2010-06-21 16:33:27 Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ?