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 [EMAIL PROTECTED]
To report any other bug, fill out the form below and e-mail it to [EMAIL PROTECTED] If you not only found the problem but solved it and generated a patch then e-mail it to [EMAIL PROTECTED] 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 : [EMAIL PROTECTED] 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. :) ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
