Re: 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. :)

Arguile <arguile(at)lucentstudios(dot)com> writes:
> If a table field is altered to add a default, the default value is
> bypassed by pre-existing rules.

Yeah, this problem has been known for awhile (to me at least). The
difficulty is that default values are added to INSERTs by the parser,
which is before rule creation and expansion. So the saved info about
the rule already has all the defaults it's gonna get. What's worse,
it won't track changes in existing defaults (though I'm not sure we
support altering defaults, anyway). If I do

regression=# create table foo (f1 int default 1, f2 int default 2);
CREATE
regression=# create view v1 as select * from foo;
CREATE
regression=# create rule v1i as on insert to v1 do instead
regression-# insert into foo values(new.f1);
CREATE
regression=# select pg_get_ruledef('v1i');
pg_get_ruledef

--------------------------------------------------------------------------------------------
CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD INSERT INTO foo (f1, f2) VALUES (new.f1, 2);
(1 row)

then I can see that the defaults have crept into what's stored for the
rule.

I believe the best fix for this is to move default-insertion out of the
parser and do it during planning, instead --- probably at the same
place that manipulates the insert's targetlist to match the column
ordering of the table. A possible objection is that default expressions
wouldn't be subject to rule manipulation, but we don't have any feature
that requires that anyway.

Comments anyone?

regards, tom lane

I said:
> Arguile <arguile(at)lucentstudios(dot)com> writes:
>> If a table field is altered to add a default, the default value is
>> bypassed by pre-existing rules.

> I believe the best fix for this is to move default-insertion out of the
> parser and do it during planning, instead

I have committed fixes for this into the 7.2 sources.

regards, tom lane