New default ignored by pre-exising insert rulesets. - Mailing list pgsql-bugs

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@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch then
e-mail it to pgsql-patches@postgresql.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@lucentstudios.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. :)