--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<br><br>I have a table whose column size needs to be increased:<br=
><br>=A0\d dim_product<br>=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=A0=A0=A0 =A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 Table "report.dim_product"<br>=A0=A0=A0=
=A0=A0=A0=A0 Column=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Ty=
pe=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=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0 Mo=
difiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A 0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <br>
----------------------+--------------------------+---------- ---------------=
------------------------------------------------------------ -<br>=A0product=
_id | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null=
default nextval('dim_product_id_seq'::regclass)<br>
=A0application_id=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0 | not null<br>=A0source_product_cd=A0=A0=A0 | integer=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null<br>=A0produc=
t_type=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(20)=A0=A0=A0 | not null<=
br>=A0product_name=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(100)=A0=A0 |=
not null<br>
=A0vendor_offer_cd=A0=A0=A0=A0=A0 | character varying(30)=A0=A0=A0 | <br>=
=A0service_name=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(20)=A0=A0=A0 | =
<br>=A0category=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(40)=
=A0=A0=A0 | <br>=A0svc_line_cd=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varyi=
ng(40)=A0=A0=A0 | <br>=A0established=A0=A0=A0=A0=A0=A0=A0=A0=A0 | timestamp=
with time zone | not null<br>
=A0modified=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | timestamp with time zone =
| not null<br>Indexes:<br>=A0=A0=A0 "dim_product_pkey" PRIMARY KE=
Y, btree (product_id)<br>=A0=A0=A0 "idx_dim_product_modified" btr=
ee (modified)<br>=A0=A0=A0 "idx_dim_product_source_product_cd" bt=
ree (source_product_cd)<br>
Triggers:<br>=A0=A0=A0 t_dim_product_timestamp_b_iu BEFORE INSERT OR UPDATE=
ON dim_product FOR EACH ROW EXECUTE PROCEDURE public.update_timestamps()<b=
r><br><br>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:<br>
<br>alter table dim_product alter column service_name type varchar(55);<br>=
<br>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.<br>
<br>Thanks in advance,<br>Mike<br><br><br><br>

--001485f7d82c37f94b04898e53c1--

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:
>
> 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.

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

--
=C3=81lvaro Herrera <alvherre [at] commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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 =A0=A0
create function update_timestamp() returns trigger as $$begin=A0=A0new.ts =
=3D now();=A0=A0return 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------+-----------------------------=
--=A0=A0 =A01 | 2010-06-21 14:33:32.14576-04=A0=A0 =A02 | 2010-06-21 14:33:=
34.545739-04=A0=A0 =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------+-----------------------------=
--=A0=A0 =A01 | 2010-06-21 14:33:32.14576-04=A0=A0 =A02 | 2010-06-21 14:33:=
34.545739-04=A0=A0 =A03 | 2010-06-21 14:33:36.097878-04(3 rows)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- On Mon, 6/21/10, Mike Broers <mbroers [at] gmail.com> wrote:
From: Mike Broers <mbroers [at] gmail.com>
Subject: [ADMIN] alter column resize triggers question
To: pgsql-admin [at] 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=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=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0 Ta=
ble "report.dim_product"
=A0=A0=A0=A0=A0=A0=A0 Column=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0 Type=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=A0=A0=A0=A0=A0=A0=A0 =A0=A0=A0=A0=A0=
=A0=A0 Modifiers=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=A0=A0=A0=A0=A0
=0A----------------------+--------------------------+------- ---------------=
------------------------------------------------------------ ----
=A0product_id | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
| not null default nextval('dim_product_id_seq'::regclass)
=0A=A0application_id=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 | not null
=A0source_product_cd=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 | not null
=A0product_type=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(20)=A0=A0=A0 | =
not null
=A0product_name=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(100)=A0=A0 | no=
t null
=0A=A0vendor_offer_cd=A0=A0=A0=A0=A0 | character varying(30)=A0=A0=A0 |
=A0service_name=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(20)=A0=A0=A0 |=

=A0category=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(40)=A0=
=A0=A0 |
=A0svc_line_cd=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(40)=A0=A0=A0 =
|
=A0established=A0=A0=A0=A0=A0=A0=A0=A0=A0 | timestamp with time zone | not =
null
=0A=A0modified=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | timestamp with time zo=
ne | not null
Indexes:
=A0=A0=A0 "dim_product_pkey" PRIMARY KEY, btree (product_id)
=A0=A0=A0 "idx_dim_product_modified" btree (modified)
=A0=A0=A0 "idx_dim_product_source_product_cd" btree (source_product_cd)
=0ATriggers:
=A0=A0=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



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

<table cellspacing=3D"0" cellpadding=3D"0" border=3D"0" ><tr><td valign=3D"=
top" style=3D"font: inherit;">Mike,<div><br></div><div>Doesn't look like it=
, at least on 8.4.  Give the script below a try for yourself.</div><di=
v><br></div><div>Another approach would be to create a new table with the s=
chema you need, insert the rows from your existing table into it, rename th=
e two tables appropriately, then recreate the indexes and trigger on the ne=
w table.  That way you won't have to worry about the trigger firing at=
all.</div><div><br></div><div>Bob Lunney</div><div><br></div><div>=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div><div><br></div><div>create table =
blah (blah int, ts timestamptz);             =
                     =
;                     &nb=
sp;             </div><div><br></div><di=
v>create function
update_timestamp() returns trigger as $$</div><div>begin</div><div> &=
nbsp;new.ts =3D now();</div><div>  return new;</div><div>end;</di=
v><div>$$ language plpgsql;</div><div><br></div><div><div>create trigger bl=
ah_t</div><div>before insert or update on blah for each row </div><div=
>execute procedure update_timestamp();</div></div><div><br></div><div><div>=
insert into blah values (1);</div><div>insert into blah values (2);</div><d=
iv>insert into blah values (3);</div><div>select * from blah;</div></div><d=
iv><br></div><div><div> blah |           &nbs=
p;  ts</div><div>------+-------------------------------</div><div>&nbs=
p;   1 | 2010-06-21 14:33:32.14576-04</div><div>   &nbs=
p;2 | 2010-06-21 14:33:34.545739-04</div><div>    3 | 2010-0=
6-21 14:33:36.097878-04</div><div>(3 rows)</div></div><div><br></div><div><=
div>alter table blah alter column blah type bigint;</div><div>select *
from blah;</div></div><div><br></div><div> blah |      =
;        ts</div><div>------+--------------------------=
-----</div><div>    1 | 2010-06-21 14:33:32.14576-04</div><d=
iv>    2 | 2010-06-21 14:33:34.545739-04</div><div> &nb=
sp;  3 | 2010-06-21 14:33:36.097878-04</div><div>(3 rows)</div><div><b=
r></div><div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div><div><br></=
div><div>--- On <b>Mon, 6/21/10, Mike Broers <i><mbroers [at] gmail.com></=
i></b> wrote:</div><div><blockquote style=3D"border-left: 2px solid rgb(16,=
16, 255); margin-left: 5px; padding-left: 5px;"><br>From: Mike Broers <=
mbroers [at] gmail.com><br>Subject: [ADMIN] alter column resize triggers ques=
tion<br>To: pgsql-admin [at] postgresql.org<br>Date: Monday, June 21, 2010, 2:18=
PM<br><br><div id=3D"yiv767064262">Pg v8.3.8<br><br>I have a table whose c=
olumn size needs to be increased:<br><br> \d
dim_product<br>          =
;            &n=
bsp;            =
;            &n=
bsp;  Table "report.dim_product"<br>     &nbs=
p;  Column        |  &nbs=
p;        Type    &n=
bsp;      |      &nb=
sp;            =
            &nb=
sp;     
Modifiers           =
;            &n=
bsp;            =
;   <br>=0A----------------------+--------------------------+----=
------------------------------------------------------------ ---------------=
-------<br> product_id | integer      &n=
bsp;           | not null=
default nextval('dim_product_id_seq'::regclass)<br>=0A application_id=
       | integer     =
;             |=
not null<br> source_product_cd    | integer  =
;            &n=
bsp;   | not null<br> product_type    &n=
bsp;    | character varying(20)    | not null=
<br> product_name         | ch=
aracter varying(100)   | not null<br>=0A vendor_offer_cd&nbs=
p;     | character varying(30)    | <br>=
 service_name         | charac=
ter varying(20)    | <br> category   &nb=
sp;         | character varying(40)=
    | <br> svc_line_cd     &nb=
sp;    | character varying(40)    | <br> =
;established          | timest=
amp with time zone | not null<br>=0A modified    &=
nbsp;        | timestamp with time zone =
| not null<br>Indexes:<br>    "dim_product_pkey" PRIMARY KEY=
, btree (product_id)<br>    "idx_dim_product_modified" btree=
(modified)<br>    "idx_dim_product_source_product_cd" btree=
(source_product_cd)<br>=0ATriggers:<br>    t_dim_product_ti=
mestamp_b_iu BEFORE INSERT OR UPDATE ON dim_product FOR EACH ROW EXECUTE PR=
OCEDURE public.update_timestamps()<br><br><br>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:<br>=0A<br>alter table dim_product alter colu=
mn service_name type varchar(55);<br><br>But i am worried about the trigger=
s because I believe that the alter table statement will rewrite the table a=
nd I dont want those triggers firing.  Does anyone know if I need to d=
isable these triggers prior to the alter table statement, or if there are a=
ny 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.<br>=
=0A<br>Thanks in advance,<br>Mike<br><br><br><br>=0A</div></blockquote></di=
v></td></tr></table><br>=0A=0A
--0-174959576-1277145598=:94877--