New default ignored by pre-exising insert rulesets.

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch then
e-mail it to pgsql-patches(at)postgresql(dot)org instead. Please use the command
"diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mailing this form.

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : arguile
Your email address : arguile(at)lucentstudios(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel P3 Xeon

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.19smp

PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3

Compiler used (example: gcc 2.95.2) : gcc 2.95.3

Please enter a FULL description of your problem:
------------------------------------------------

SYNOPSIS:
If a table field is altered to add a default, the default value is
bypassed by pre-existing rules.

DETAILS:
Let's say we have an employee (overused yes, but bear with me) table and
any changes to it are logged in a seperate table. The changes are logged
via a bunch of RULEs.

CREATE TABLE foo (id int);
CREATE TABLE log (id int, date timestamp);

CREATE RULE foo_insert AS
ON INSERT TO foo DO
INSERT INTO log (id) VALUES (new.id);

We insert a value and the rule is doing it's job.

INSERT INTO foo (id) VALUES (1);

Tracking changes is all well and good but when they occured would be
useful so a a timestamp field is added and is given the default of now().

ALTER TABLE log ALTER date SET DEFAULT now();

We then insert another record into the main table,

INSERT INTO foo (id) VALUES (2);

and are suprised to find out there's no timestamp in the date field. Just
to test we insert a value directly into the log table, then another into
our main table.

INSERT INTO log (id) VALUES (3);
INSERT INTO foo (id) VALUES (4);

At this point we'd expect the log to contain:

id | date
----+------------------------
1 |
2 | 0000-00-00 00:00:00-00
3 | 0000-00-00 00:00:00-00
4 | 0000-00-00 00:00:00-00

Instead the INSERT in the RULE seem to somehow bypass the default value
and we get this:

id | date
----+------------------------
1 |
2 |
3 | 0000-00-00 00:00:00-00
4 |

It didn't happen quite like that but you get the drift. As a side note, if
you add a NOT NULL contraint to the date (I know it's a SQL reserved word
but this is an example ;) field _that_ will be honoured and the system
will complain. It just seems to like ignoring defaults set after the fact.

Thanks for your time.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

-- This doesn't work

DROP TABLE foo; DROP TABLE log;
CREATE TABLE foo (id int);
CREATE TABLE log (id int, date timestamp);
CREATE RULE foo_insert AS
ON INSERT TO foo DO
INSERT INTO log (id) VALUES (new.id);
INSERT INTO foo (id) VALUES (1);
ALTER TABLE log ALTER date SET DEFAULT now(); -- alter after rule
INSERT INTO foo (id) VALUES (2);
INSERT INTO log (id) VALUES (3);
INSERT INTO foo (id) VALUES (4);
SELECT * FROM log;

-- This does work

DROP TABLE foo; DROP TABLE log;
CREATE TABLE foo (id int);
CREATE TABLE log (id int, date timestamp);
ALTER TABLE log ALTER date SET DEFAULT now(); -- alter before rule
CREATE RULE foo_insert AS
ON INSERT TO foo DO
INSERT INTO log (id) VALUES (new.id);
INSERT INTO foo (id) VALUES (1);
INSERT INTO foo (id) VALUES (2);
INSERT INTO log (id) VALUES (3);
INSERT INTO foo (id) VALUES (4);
SELECT * FROM log;

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I find 'em not fix 'em. :)