Thread: alter column appears to work, but doesn't?

I just updated a table to have a larger column size as follows.

alter table attributes_log alter column attribute_name type varchar(48);

The size was previously 24.

iddb=> \d attributes
                                               Table "iddb.attributes"
     Column     |         Type          |                                  Modifiers
----------------+-----------------------+-----------------------------------------------------------------------------
 attribute_id   | uuid                  | not null default (encode(pgcrypto.gen_random_bytes(16), 'hex'::text))::uuid
 attribute_name | character varying(48) | not null
 management     | character varying(24) | not null default 'by_value'::character varying

iddb=> insert into attributes ( attribute_name ) values ( 'abcdefghijklmnopqrstuvwxyz' );
ERROR:  value too long for type character varying(24)

I'm using PostgreSQL 9.0.4

I tried to replicate this with a new database and a simple table, but
could not.

I had to drop (and then recreate) three rules and a view on this table
before altering the column.

This is a production database, so I need to treat it gently.

--
Ron Peterson
Network & Systems Administrator
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Ron Peterson <rpeterso@mtholyoke.edu> writes:
> I just updated a table to have a larger column size as follows.

> alter table attributes_log alter column attribute_name type varchar(48);

How come this refers to "attributes_log" while your failing command is
an insert into "attributes"?

            regards, tom lane

2011-09-05_15:03:00-0400 Tom Lane <tgl@sss.pgh.pa.us>:
> Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > I just updated a table to have a larger column size as follows.
>
> > alter table attributes_log alter column attribute_name type varchar(48);
>
> How come this refers to "attributes_log" while your failing command is
> an insert into "attributes"?

That was a typo, sorry.  Did do the same thing on original table.  I did
the same thing to attributes_log because I have rules that log data
there from my original table on insert/update/delete.

I just dropped my logging rules, stopped the database and restarted it,
put my rules back in place, and now it works.  Not sure why.  Cached
query plan?

--
Ron Peterson
Network & Systems Administrator
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

Ron Peterson <rpeterso@mtholyoke.edu> writes:
> I just dropped my logging rules, stopped the database and restarted it,
> put my rules back in place, and now it works.  Not sure why.  Cached
> query plan?

Maybe.  We'd need a reproducible test case to do more than speculate
though.

            regards, tom lane

2011-09-05_16:14:00-0400 Tom Lane <tgl@sss.pgh.pa.us>:
> Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > I just dropped my logging rules, stopped the database and restarted it,
> > put my rules back in place, and now it works.  Not sure why.  Cached
> > query plan?
>
> Maybe.  We'd need a reproducible test case to do more than speculate
> though.

Hi Tom,

I was able to reproduce this.  DDL below.  Probably more DDL than
necessary, but not sure what is or isn't relevant.

postgres=# drop rule attribute_insert_rule on attributes;
postgres=# drop rule attribute_update_rule on attributes;
postgres=# drop rule attribute_delete_rule on attributes;
postgres=# alter table attributes_log alter column attribute_name type varchar(50);
...then recreate rules below
postgres=# insert into attributes values ( repeat( 'x', 49 ) );
ERROR:  value too long for type character varying(48)


CREATE TABLE attributes (
  attribute_name
    VARCHAR(48)
    UNIQUE
    NOT NULL
);

-- Attribute names can be inserted or deleted, but not changed.
CREATE OR REPLACE FUNCTION attribute_name_freeze_tf()
RETURNS TRIGGER
AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    IF NEW.attribute_name = OLD.attribute_name THEN
      RETURN NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER attribute_name_freeze_t
BEFORE UPDATE ON attributes
FOR EACH ROW EXECUTE PROCEDURE attribute_name_freeze_tf();

CREATE TABLE attributes_log (
  attribute_name
    VARCHAR(48),
  action
    CHAR(6)
    NOT NULL
    CHECK( action IN ('insert', 'delete','update') ),
  changed
    TIMESTAMP WITH TIME ZONE
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP
);

CREATE RULE attribute_insert_rule AS
ON INSERT TO attributes
DO
(
  INSERT INTO attributes_log (
    attribute_name,
    action )
  VALUES (
    new.attribute_name,
    'insert' );
);

CREATE RULE attribute_update_rule AS
ON UPDATE TO attributes
DO
(
  INSERT INTO attributes_log (
    attribute_name,
    action )
  VALUES (
    new.attribute_name,
    'update' );
);

CREATE RULE attribute_delete_rule AS
ON DELETE TO attributes
DO
(
  INSERT INTO attributes_log (
    attribute_name,
    action )
  VALUES (
    old.attribute_name,
    'delete' );
);
------------------------------------------------------------------------


--
Ron Peterson
Network & Systems Administrator
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

On Monday, September 05, 2011 1:48:58 pm Ron Peterson wrote:
> 2011-09-05_16:14:00-0400 Tom Lane <tgl@sss.pgh.pa.us>:
> > Ron Peterson <rpeterso@mtholyoke.edu> writes:
> > > I just dropped my logging rules, stopped the database and restarted it,
> > > put my rules back in place, and now it works.  Not sure why.  Cached
> > > query plan?
> >
> > Maybe.  We'd need a reproducible test case to do more than speculate
> > though.
>
> Hi Tom,
>
> I was able to reproduce this.  DDL below.  Probably more DDL than
> necessary, but not sure what is or isn't relevant.
>
> postgres=# drop rule attribute_insert_rule on attributes;
> postgres=# drop rule attribute_update_rule on attributes;
> postgres=# drop rule attribute_delete_rule on attributes;
> postgres=# alter table attributes_log alter column attribute_name type
> varchar(50); ...then recreate rules below
> postgres=# insert into attributes values ( repeat( 'x', 49 ) );
> ERROR:  value too long for type character varying(48)
>
>

I am not seeing where you change the varchar length in the table attributes.
That is where the error is coming from.

--
Adrian Klaver
adrian.klaver@gmail.com

Phghght.  Sorry, no, that didn't do it, I was typing too fast and
skipped updating the attributes table.  That was definitely not the case
w/ my original database.  Wasn't working.  The table definition reported
the update I made.  Insert did not work.  Dropping rules, restarting
database, and recreating rules got it working.  Dunno.

-Ron-