alter column resize triggers question

--001485f7d82c37f94b04898e53c1
Content-Type: text/plain; charset=ISO-8859-1

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

--001485f7d82c37f94b04898e53c1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Pg v8.3.8

I have a table whose column size needs to be increased: >
=A0\d dim_product
                =
                         =A0=
         Table "report.dim_product"
  =A0=
     Column      =A0 |           Ty=
pe           |            =A0=
                          Mo=
difiers                =A0=A 0    =
                

----------------------+--------------------------+---------- ---------------=
------------------------------------------------------------ -
=A0product=
_id | integer                =A0 | not null=
default nextval('dim_product_id_seq'::regclass)

=A0application_id       | integer          =
      =A0 | not null
=A0source_product_cd  =A0 | integer=
                =A0 | not null
=A0produc=
t_type         | character varying(20)  =A0 | not null<=
br>=A0product_name         | character varying(100)   |=
not null

=A0vendor_offer_cd    =A0 | character varying(30)  =A0 |
=
=A0service_name         | character varying(20)  =A0 | =

=A0category             | character varying(40)=
  =A0 |
=A0svc_line_cd        =A0 | character varyi=
ng(40)  =A0 |
=A0established        =A0 | timestamp=
with time zone | not null

=A0modified             | timestamp with time zone =
| not null
Indexes:
  =A0 "dim_product_pkey" PRIMARY KE=
Y, btree (product_id)
  =A0 "idx_dim_product_modified" btr=
ee (modified)
  =A0 "idx_dim_product_source_product_cd" bt=
ree (source_product_cd)

Triggers:
  =A0 t_dim_product_timestamp_b_iu BEFORE INSERT OR UPDATE=
ON dim_product FOR EACH ROW EXECUTE PROCEDURE public.update_timestamps() r>

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 ta=
ble statement will rewrite the table and I dont want those triggers firing.=
=A0 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 s=
hould review before attempting this action?=A0 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





--001485f7d82c37f94b04898e53c1--

Re: alter column resize triggers question

Excerpts from Mike Broers's message of lun jun 21 14:18:01 -0400 2010:

> I need to change service_name column to varchar(55), my plan was to bac=
kup
> the table with pg_dump, then run the below alter statement:
>=20
> alter table dim_product alter column service_name type varchar(55);
>=20
> But i am worried about the triggers because I believe that the alter ta=
ble
> statement will rewrite the table and I dont want those triggers firing.

The triggers won't fire. It'll do you no harm to try it out in a toy
table, though.

--=20
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: alter column resize triggers question

--0-174959576-1277145598=:94877
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Mike,
Doesn't look like it, at least on 8.4. =A0Give the script below a try for y=
ourself.
Another approach would be to create a new table with the schema you need, i=
nsert the rows from your existing table into it, rename the two tables appr=
opriately, then recreate the indexes and trigger on the new table. =A0That =
way you won't have to worry about the trigger firing at all.
Bob Lunney
==============
create table blah (blah int, ts timestamptz); =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0   
create function update_timestamp() returns trigger as $$begin  new.ts =
=3D now();  return new;end;$$ language plpgsql;
create trigger blah_tbefore insert or update on blah for each row=A0execute=
procedure update_timestamp();
insert into blah values (1);insert into blah values (2);insert into blah va=
lues (3);select * from blah;
=A0blah | =A0 =A0 =A0 =A0 =A0 =A0 =A0ts------+-----------------------------=
--   =A01 | 2010-06-21 14:33:32.14576-04   =A02 | 2010-06-21 14:33:=
34.545739-04   =A03 | 2010-06-21 14:33:36.097878-04(3 rows)
alter table blah alter column blah type bigint;select * from blah;
=A0blah | =A0 =A0 =A0 =A0 =A0 =A0 =A0ts------+-----------------------------=
--   =A01 | 2010-06-21 14:33:32.14576-04   =A02 | 2010-06-21 14:33:=
34.545739-04   =A03 | 2010-06-21 14:33:36.097878-04(3 rows)
===============3D
--- On Mon, 6/21/10, Mike Broers wrote:
From: Mike Broers
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:

=A0\d dim_product
                         =A0=
                          Ta=
ble "report.dim_product"
      =A0 Column      =A0 |        =
   Type           |          =
                         =A0=
   Modifiers                =A0   =
                   
=0A----------------------+--------------------------+------- ---------------=
------------------------------------------------------------ ----
=A0product_id | integer                =A0 =
| not null default nextval('dim_product_id_seq'::regclass)
 application_id       | integer        =A0=
         | not null
=A0source_product_cd  =A0 | integer            =
    =A0 | not null
=A0product_type         | character varying(20)  =A0 | =
not null
=A0product_name         | character varying(100)   | no=
t null
 vendor_offer_cd    =A0 | character varying(30)  =A0 |=20
=A0service_name         | character varying(20)  =A0 |=
=20
=A0category             | character varying(40)=A0=
   |=20
=A0svc_line_cd        =A0 | character varying(40)  =A0 =
|=20
=A0established        =A0 | timestamp with time zone | not =
null
 modified             | timestamp with time zo=
ne | not null
Indexes:
  =A0 "dim_product_pkey" PRIMARY KEY, btree (product_id)
  =A0 "idx_dim_product_modified" btree (modified)
  =A0 "idx_dim_product_source_product_cd" btree (source_product_cd)
=0ATriggers:
  =A0 t_dim_product_timestamp_b_iu BEFORE INSERT OR UPDATE ON dim_produ=
ct 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:
=0A
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.=A0 =
Does anyone know if I need to disable these triggers prior to the alter tab=
le statement, or if there are any other dependencies or precautions I shoul=
d review before attempting this action?=A0 I have also seen there is a work=
around with running updates to the pg_attribute table but frankly that make=
s me a little nervous.
=0A
Thanks in advance,
Mike




--0-174959576-1277145598=:94877
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable