Re: [BUGS] Double sequence increase on single insert with RULE on

#1

I understand that RULES are like macros.
Strangest thing here is that INSERT to test1 will touch only one
sequence: test1_id_seq.
And it increments test1_id_seq twice during insert with RULE.
Then all sequence procedures like lastval() and currval() will return
number (as stated in report),
which is biger than actualy one inserted into the database.
When after insert:
BEGIN; INSERT INTO test1(some_text) VALUES ('test1'); SELECT lastval()
as id; END;
you make a select on test1 and test_log1 tables you see such a view:
testdb=# select * from test1;
id | some_text
----+-----------
2 | test1
(1 row)
testdb=# select * from test_log1;
qid | when_happened
-----+----------------------------
3 | 2005-11-16 10:27:33.100913
(1 row)

Sarunas

Tomas Zerolo wrote:

Quote:

On Tue, Nov 15, 2005 at 10:51:10PM -0700, Michael Fuhr wrote:

On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote:

AFAIK, serials are not guaranteed to produce sequential values; tehy
will produce unique values. That means that they can (and sometimes
will) jump.

In this particular case, however, the behavior is due to the rule
on test1:

CREATE RULE test1_on_insert AS ON INSERT TO test1
DO INSERT INTO test_log1 (qid) VALUES (new.id);

[...]

Oops, I didn't see that. Your eyes are sharper than mine ;-)

thanks
-- tomas

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#2

Ok

Then I found the solution in this partical case:
CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO
test_log1 (qid) VALUES ( (SELECT lastval()) );
With this rule all inserts are working as expected

Thank you for you advise

Sarunas

Michael Fuhr wrote:

Quote:

On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:

I understand that RULES are like macros.
Strangest thing here is that INSERT to test1 will touch only one
sequence: test1_id_seq.
And it increments test1_id_seq twice during insert with RULE.

Yes, that's a well-known effect of rewriting a query that includes
a call to nextval(). NEW.id in the rule doesn't refer to the value
that's inserted, but rather to the expression that's evaluated to
get that value. Since you didn't provide a value for id it gets
the default: nextval('test1_id_seq'). That expression is used in
both inserts, so the sequence gets incremented twice. See the
archives for numerous past discussions of this behavior.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

#3

Is that safe or could lastval return the value of a previous insert if
an insert fails? Though I suppose if the insert fails then the rule
shouldn't get fired...

On Thu, Nov 17, 2005 at 10:11:29AM +0200, Sarunas Krisciukaitis wrote:

Quote:

Ok

Then I found the solution in this partical case:
CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO
test_log1 (qid) VALUES ( (SELECT lastval()) );
With this rule all inserts are working as expected

Thank you for you advise

Sarunas

Michael Fuhr wrote:

On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:

I understand that RULES are like macros.
Strangest thing here is that INSERT to test1 will touch only one
sequence: test1_id_seq.
And it increments test1_id_seq twice during insert with RULE.

Yes, that's a well-known effect of rewriting a query that includes
a call to nextval(). NEW.id in the rule doesn't refer to the value
that's inserted, but rather to the expression that's evaluated to
get that value. Since you didn't provide a value for id it gets
the default: nextval('test1_id_seq'). That expression is used in
both inserts, so the sequence gets incremented twice. See the
archives for numerous past discussions of this behavior.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] Double sequence increase on single insert with RULE on

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] Double sequence increase on single insert with RULE on - 11-16-2005 , 07:03 AM

Re: [BUGS] Double sequence increase on single insert with RULE on - 11-18-2005 , 08:10 AM

Re: [BUGS] Double sequence increase on single insert with RULE on - 11-18-2005 , 01:40 PM