Re: Sequence skipping values - Mailing list pgsql-general

Micheal,

Thanks a lot for you very clear explanation. To solve the problem, I have created another table so that the table does not use a rule to update itself. It works fine now.
Regards
JCR

Michael Fuhr <mike@fuhr.org> wrote:

On Sat, Feb 11, 2006 at 06:06:21AM -0800, Jean-Christophe Roux wrote:
> Yes you are right, I did not show one rule on table topics:
> CREATE OR REPLACE RULE topics_last_administrator_id AS
> ON INSERT TO topics DO UPDATE topics SET last_administrator_id = new.administrator_id
> WHERE topics.topic_id = new.topic_id;
>
> I am going to try to replicate the problem, using a local 8.1.2 database.

No need; the version shouldn't matter in this case. The above rule
is responsible because of what I mentioned in a previous message,
viz., new.topic_id is being rewritten as an expression instead of
a constant. If the insert doesn't provide a value for topic_id
then it takes its value from its default expression, which is a
call to nextval. When the rule is rewritten, new.topic_id isn't
replaced with that value but rather with the nextval expression.

Here's another example:

CREATE TABLE foo (id serial PRIMARY KEY, x integer, lastx integer);

CREATE RULE foo AS ON INSERT TO foo
DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = NEW.id;

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
3 | 2 |
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 |
3 | 2 |
6 | 3 |
(3 rows)

As you can s ee, this example doesn't do what the rule appears to
intend. The last insert, for example, causes the following update
statement to be run:

UPDATE foo SET lastx = 3 WHERE id = nextval('foo_id_seq')

Since nextval is volatile each row in the table is checked, causing
nextval to be evaluated each time; that's why it's incrementing by
the number of rows in the table. Also notice that lastx isn't being
assigned because id never matches the sequence's next value -- do
you see that problem in your case as well?

You might be able to use a rule that uses currval instead of referring
to the id column (but see below for a warning):

CREATE RULE foo AS ON INSERT TO foo
DO ALSO UPDATE foo SET lastx = NEW.x WHERE id = currval('foo_id_seq');

If we drop and recreate the foo table and add the above rule then
we get this:

INSERT INTO foo (x) VALUES (1);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
(1 row)

INSERT INTO foo (x) VALUES (2);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
(2 rows)

INSERT INTO foo (x) VALUES (3);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
(3 rows)

Unfortunately the update will fail to set lastx correctly if you
insert multiple rows with INSERT ... SELECT:

INSERT INTO foo (x) SELECT n FROM generate_series(4, 6) AS g(n);
SELECT * FROM foo;
id | x | lastx
----+---+-------
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 |
5 | 5 |
6 | 6 | 4
(6 rows)

I haven't considered other cases you so you might find additional
failure modes. This method is also inefficient because currval is
volatile so each row in the table will have to be checked, which
will make the insert/update slow as the table grows. Additionally,the update creates a dead tuple for every insert so you should
vacuum the table often if it's updated often.

A trigger would probably be better for this. If your database
doesn't have a trigger-capable language like PL/pgSQL and your
webhosting admins won't create it for you, then consider changing
services.

--
Michael Fuhr

---

Yahoo! Mail
Use Photomail to share photos without annoying attachments.