Re: Default values, inserts, and rules...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Sean Chittenden <sean(at)chittenden(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: Default values, inserts, and rules...
Date: 2002-08-21 13:59:30
Message-ID: 19917.1029938370@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

Sean Chittenden <sean(at)chittenden(dot)org> writes:
> There are two bits going on in this example:
> 1) To get the current value from a sequence, I have to use
> CURRVAL(seq_name) in the actual rule. Not that big of a deal, but
> kind of a PITA that I can't just use NEW.s.
> 2) When the rule does the insert into the t_log table, NEW.c1 doesn't
> contain the default value of 42. How can I do this? I don't want
> to force the application to specify the default values for
> everything.

This is a known bug in 7.2: default values that should be inserted into
unspecified columns of an INSERT aren't inserted till after the rule
rewriter, thus the rule text doesn't see 'em. This is fixed in CVS for
7.3, but I don't believe we have made a back-patch for 7.2 branch.
I believe this bug accounts for both your gripes.

You have to be quite careful with rules that manipulate a default
nextval() though, because rule expansion is basically a textual
macro-expansion operation, and it's very easy to end up with multiple
copies of the default-value expression --- thus leading to multiple
nextval() calls at runtime, which is assuredly not what you want.

On the whole I'd advise using a trigger, not a rule, for this purpose.
The trigger will see the final state of the row (including computed
defaults) and can reliably insert the correct data into the log table.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jean-Luc Lachance 2002-08-21 14:12:33 Re: inconsistend performance
Previous Message Masaru Sugawara 2002-08-21 12:25:25 Re: Default values, inserts, and rules...

Browse pgsql-general by date

  From Date Subject
Next Message C F 2002-08-21 16:45:31 Limiting User Resources
Previous Message Masaru Sugawara 2002-08-21 12:25:25 Re: Default values, inserts, and rules...