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

2011-09-05_16:14:00-0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Ron Peterson <rpeterso(at)mtholyoke(dot)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