Thread: Default values, inserts, and rules...
I'm working on creating a DB schema that has full auditing/logging and
have run across a sticking point that's 'causing me a ton of grief.
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.
CREATE TABLE t (
s SERIAL NOT NULL,
c1 INT DEFAULT 42 NOT NULL,
c2 CHAR(1) NOT NULL);
CREATE TABLE t_log (
s INT NOT NULL,
c1 INT NOT NULL,
c2 CHAR(1) NOT NULL);
CREATE RULE t_ins AS ON INSERT TO t DO
INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),NEW.c1,NEW.c2);
INSERT INTO t (c2) VALUES ('a');
ERROR: ExecAppend: Fail to add null value in not null attribute c1
I think this was brought up a while ago... Tom, in response to your
question, moving this into the parser I think would solve the above
behaviors that I'd classify as incorrect. Am I wrong or missing some
way of getting the desired behavior?
http://archives.postgresql.org/pgsql-bugs/2001-10/msg00145.php
-sc
--
Sean Chittenden
sean@ruby-lang.org
seanc@FreeBSD.org
Attachment
On Tue, 20 Aug 2002 22:37:46 -0700
Sean Chittenden <sean@chittenden.org> wrote:
> CREATE RULE t_ins AS ON INSERT TO t DO
> INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),NEW.c1,NEW.c2);
> INSERT INTO t (c2) VALUES ('a');
> ERROR: ExecAppend: Fail to add null value in not null attribute c1
>
> I think this was brought up a while ago... Tom, in response to your
> question, moving this into the parser I think would solve the above
> behaviors that I'd classify as incorrect. Am I wrong or missing some
> way of getting the desired behavior?
>
If NEW.c1 doesn't contain the value, to use COALESCE() can avoid
raising that error. And the default value: 42 is correctly inserted into t_log.
CREATE RULE t_ins AS ON INSERT TO t DO
INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),
COALESCE(NEW.c1, 42),
NEW.c2);
Regards,
Masaru Sugawara
Sean Chittenden <sean@chittenden.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
> > 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. Hrm, I think I'm just going to start developing against CVS then because it also has the added perk of schemas which are perfect for hiding log tables. ;~) That said, if things are working correctly in CVS, would you still recommend a trigger over a rule? I'd think that a macro/rule would be faster than a trigger, but I don't have any real basis for my statement. In my examples I'm using CURRVAL() and not NEXTVAL() so I wouldn't worry about that being a problem. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> ... That said, if things are working correctly in CVS, would you still
> recommend a trigger over a rule?
Yes I would. I don't think you've thought carefully enough about the
implications of the statement that rules are macros... the perennial
problem with macros is multiple evaluations of an argument, and if the
argument has side-effects (like nextval()) you *will* get bit.
regards, tom lane
> > ... That said, if things are working correctly in CVS, would you still
> > recommend a trigger over a rule?
>
> Yes I would. I don't think you've thought carefully enough about
> the implications of the statement that rules are macros... the
> perennial problem with macros is multiple evaluations of an
> argument, and if the argument has side-effects (like nextval()) you
> *will* get bit.
::nods:: I understand the HUGE pitfall of using NEXTVAL() or the like
in a rule: it makes complete sense. But given that the NEW tuple is
being correctly populated with both the sequence number default
values, I'd think a rule is an ideal way of copying the contents of
the insert + some logging/transaction goo into a logging table.
Let me phrase my question better: if the rule contains nothing more
than an insert statement into a duplicate logging table, is it faster
and more efficient to use a rule than a trigger? For pretty much
everything else I'm using triggers, but for logging purposes, rules
seem ideal. Triggers struck me as being heavier weight than rules in
terms of parsing and the context switch to execute some pl code in the
triger... err... hold the phone... wait a sec, I see what you were
getting at. This behavior seems broken. :~) Example:
CREATE TABLE t (pk SERIAL NOT NULL, c1 CHAR(1) NOT NULL, PRIMARY KEY(pk));
CREATE TABLE t_log (pk INT NOT NULL, c1 CHAR(1) NOT NULL);
CREATE RULE t_ins AS ON INSERT TO t DO INSERT INTO t_log (pk,c1) VALUES (NEW.pk,NEW.c1);
INSERT INTO t (c1) VALUES ('a');
SELECT * FROM t;
pk | c1
----+----
1 | a
(1 row)
SELECT * FROM t_log;
pk | c1
----+----
2 | a
(1 row)
What I get from this is that NEW.pk is doing a NEXTVAL() instead of
reading the value that the tuple was populated with from the sequence.
I can't think of an instance where this'd be the desired behavior...
kinda breaks the data consistency that I had expected.
The good news is though that the default values work like an absolute
CHARM and I can continue to use CURRVAL() in my rules... still, this
behavior seems a tad broken. There a good reason for this or could
you give me a filename to look into so I can toss together a patch.
Seems like something is going out of its way to get a new value from
the pk sequence when it shouldn't... thoughts? -sc
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> What I get from this is that NEW.pk is doing a NEXTVAL() instead of
> reading the value that the tuple was populated with from the sequence.
You are missing the point completely. Rules do not operate on
individual tuples --- a rule is a textual transformation on the whole
query before it ever gets executed at all. What gets substituted into
the INSERT command in your rule is not any specific tuple's value,
but the NEXTVAL()-containing expression.
I'm too tired to explain this clearly, but perhaps if you go back and
reread the docs about rules with the above hint in mind, it will make
sense.
My rule of thumb is that if you think you can do it with either a
trigger or a rule, you should be using a trigger. Triggers are way
easier to understand, even though the notational baggage is heavier.
regards, tom lane
> Rules do not operate on individual tuples --- a rule is a textual > transformation on the whole query before it ever gets executed at > all. What gets substituted into the INSERT command in your rule is > not any specific tuple's value, but the NEXTVAL()-containing > expression. That makes loads of sense. I thought it did its transformations further down in the process and on individual tuples as opposed to a wholistic rewrite of the query. Using rules would've worked had I not tried to be clever with tracing transaction histories through tags. :-/ Guess it's no biggie though... rules just are so elegant compared to triggers. :~) -sc PS I converted everything to use schemas tonight and I can't applaud the efforts enough: they really clean up the database and make things much usable. The output of psql is also much easier on the eyes for large and complex schemas. -- Sean Chittenden
Hi all, I'm getting During a vacuum Cannot insert a duplicate key into unique index pg_statistic_relid_att_index Can I fix this index?
"Andrew Bartley" <abartley@evolvosystems.com> writes:
> During a vacuum
> Cannot insert a duplicate key into unique index pg_statistic_relid_att_index
> Can I fix this index?
Try REINDEX.
What PG version are you running?
regards, tom lane