Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 16E496326C1 for ; Mon, 21 Jun 2010 15:40:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 75961-05 for ; Mon, 21 Jun 2010 18:40:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39706.mail.mud.yahoo.com (web39706.mail.mud.yahoo.com [209.191.106.52]) by mail.postgresql.org (Postfix) with SMTP id 3DA2463261F for ; Mon, 21 Jun 2010 15:40:01 -0300 (ADT) Received: (qmail 94940 invoked by uid 60001); 21 Jun 2010 18:39:59 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1277145599; bh=EcSTbevD/UqM77+TApkeMuCLtP+zqMmisjNNta0zwmE=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=2jigBAhbYWki5uGwy8XRv28+1CVUnASlTOH6Zn8ZqVUW26PZlpte+TlulyEPNF1BzrPpMWZ1wWIMnGdgDgByZ0KlQi3U7fhBF1xoPAORZ6Gi1V3ZsGqXkrXukFvJLR2ZWucmrgTolnN07xsv6Q38fSXj9Ni+6A6l9RCNvdYIV88= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=wAFeGgrkKc9/U5UJ7+EePriAAO6dKdo7Af7Vx/RXyE9FFWF3eu0t8dxGpLajhIPy2+dw1vB0GWsiPsreloiqv4k+hOd5PrKT/afyJGYa7oXVaF+rE2hp/uqBV8oIEquRUNEeGjiF4Vqq3S8gObJM/sKThG5aTtlRe/tO9+fgDCI=; Message-ID: <162237.94877.qm@web39706.mail.mud.yahoo.com> X-YMail-OSG: jc45Sp4VM1n_rhZXKIH5njKPZqFPcDG4kEU.tesLaT18sgR cFPwAbyBtB1RutsRzgdkwVvJ6DFjLgz7zoKEOMdxM0m4GwTcAI7XXlXbzGn2 JlonGlwx97AoIYcinh6NsMQb9x3N5onQ8IwXvM7hBtHbs8QiXT4v3S1lDKwY M4zvSE1s_hnl6ChJWB1gjLReAUSfg1jkkKi4qHNsYlmaisZhXGfCUROBLSaH foE8fPDsTpnUHnB7w_R91aom1MmXl2Al4krdlC.j8HxekulxAIWAQgnd6f.f KgvQWMJSuIouVtsSvMCtfLGzaYevd0TZrPd9I9ah9ektokF003xSEwKnByWB 4Z3vWftYJPt4pN0HyR0iuhFyRhUU- Received: from [64.126.29.122] by web39706.mail.mud.yahoo.com via HTTP; Mon, 21 Jun 2010 11:39:58 PDT X-Mailer: YahooMailClassic/11.1.4 YahooMailWebService/0.8.103.269680 Date: Mon, 21 Jun 2010 11:39:58 -0700 (PDT) From: Bob Lunney Subject: Re: alter column resize triggers question To: pgsql-admin@postgresql.org, Mike Broers In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-174959576-1277145598=:94877" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201006/135 X-Sequence-Number: 34913 --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 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=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=20 =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 |=20 =A0service_name=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(20)=A0=A0=A0 |= =20 =A0category=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(40)=A0= =A0=A0 |=20 =A0svc_line_cd=A0=A0=A0=A0=A0=A0=A0=A0=A0 | character varying(40)=A0=A0=A0 = |=20 =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
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 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.

Bob Lunney

=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

create table = blah (blah int, ts timestamptz);             =                      = ;                     &nb= sp;             

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

create trigger bl= ah_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 |           &nbs= p;  ts
------+-------------------------------
&nbs= p;   1 | 2010-06-21 14:33:32.14576-04
   &nbs= p;2 | 2010-06-21 14:33:34.545739-04
    3 | 2010-0= 6-21 14:33:36.097878-04
(3 rows)

<= div>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
 &nb= sp;  3 | 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@gmail.com> wrote:

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

Pg v8.3.8

I have a table whose c= olumn size needs to be increased:

 \d dim_product
          = ;            &n= bsp;            = ;            &n= bsp;  Table "report.dim_product"
     &nbs= p;  Column        |  &nbs= p;        Type    &n= bsp;      |      &nb= sp;            =             &nb= sp;      Modifiers           = ;            &n= bsp;            = ;  
=0A----------------------+--------------------------+----= ---------------------------------------------------------------------------= -------
 product_id | integer      &n= bsp;           | not null= default nextval('dim_product_id_seq'::regclass)
=0A application_id=        | integer     = ;             |= not null
 source_product_cd    | integer  = ;            &n= bsp;   | not null
 product_type    &n= bsp;    | character varying(20)    | not null=
 product_name         | ch= aracter varying(100)   | not null
=0A vendor_offer_cd&nbs= p;     | character varying(30)    |
=  service_name         | charac= ter varying(20)    |
 category   &nb= sp;         | character varying(40)=     |
 svc_line_cd     &nb= sp;    | character varying(40)    |
 = ;established          | timest= amp with time zone | not null
=0A modified    &= nbsp;        | 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)
=0ATriggers:
    t_dim_product_ti= mestamp_b_iu BEFORE INSERT OR UPDATE ON dim_product FOR EACH ROW EXECUTE PR= OCEDURE 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 colu= mn service_name type varchar(55);

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.
= =0A
Thanks in advance,
Mike



=0A

=0A=0A --0-174959576-1277145598=:94877--