Skip site navigation (1) Skip section navigation (2)

New default ignored by pre-exising insert rulesets.

From: Arguile <arguile(at)lucentstudios(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: New default ignored by pre-exising insert rulesets.
Date: 2001-10-24 15:38:38
Message-ID: Pine.LNX.4.30.0110241136500.6031-100000@nghost.vosn.net (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
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. :)


Responses

pgsql-hackers by date

Next:From: Antonio Sergio de Mello e SouzaDate: 2001-10-24 15:51:53
Subject: Index on wide column
Previous:From: Hiroshi InoueDate: 2001-10-24 15:23:47
Subject: Re: [GENERAL] Using an SMP machine to make multiple indices on the same

pgsql-bugs by date

Next:From: Peter BretonDate: 2001-10-24 16:15:06
Subject: Possible bug in Postgres String comparison routines
Previous:From: Nicola LarosaDate: 2001-10-24 13:38:57
Subject: Decimal digits in timeofday()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group