Re: [GENERAL] Sequence skipping values
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.RegardsJCRMichael Fuhr [EMAIL PROTECTED] 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 ruleis responsible because of what I mentioned in a previous message,viz., new.topic_id is
being
rewritten as an _expression_ instead ofa constant. If the insert doesn't provide a value for topic_idthen it takes its value from its default _expression_, which is acall to nextval. When the rule is rewritten, new.topic_id isn'treplaced 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 tointend. The last insert, for example, causes the following updatestatement 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, causingnextval to be evaluated each time; that's why it's incrementing bythe number of rows in the table. Also notice that lastx isn't beingassigned because id never matches the sequence's next value -- doyou see that problem in your case as well?You might be able to use a rule that uses currval instead of referringto 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 thenwe 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 youinsert 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 additionalfailure modes. This method is also inefficient because currval isvolatile so each row in the table will have to be checked, whichwill make the insert/update slow as the table grows. Additionally,the
update creates a dead tuple for every insert so you shouldvacuum the table often if it's updated often.A trigger would probably be better for this. If your databasedoesn't have a trigger-capable language like PL/pgSQL and yourwebhosting admins won't create it for you, then consider changingservices.-- Michael Fuhr
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
Hi Michael,I'm running PostgreSQL 7.4.5 on a shared wehosting, which means among other things that I have limited control over the database settings and capabilities, For instance, I cannot install languages and therefore I cannot write triggers.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. Thank you very much for your helpJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote: here are the structures of the table involved:I co uldn't duplicate the problem in 8.1.2 or 8.0.6 with the codeyou posted. I created the given tables and rules (plus guesses forthe administrators and status_list tables), then inserted severalrecords into topics, then inserted a few records into releases,then inserted a few more records into topics. The topics_id_seqsequence incremented by one each time with no gaps; that probablymeans my test didn't match exactly what you're doing.Does the topics table have any rules or triggers that you didn'tshow? Could you post a minimal but complete test case, i.e, allSQL statements that somebody could load into an empty database toreproduce the problem? If not then it might be useful to see theEXPLAIN ANALYZE output of a series of statements that exhibit theunexpected behavior. What version of PostgreSQL are you running?-- Michael Fuhr Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: [GENERAL] Sequence skipping values
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 see, 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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Sequence skipping values
Hello,I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on!If someone could give me a clue that wold be greatly appreciated thanksJC Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail.
Re: [GENERAL] Sequence skipping values
You likely roll back some transactions which insert. The sequence values don't get rolled back, once allocated, it's gone, even if you won't keep it. For concurrency/performance reasons sequence values are not transactional. Cheers, Csaba. On Fri, 2006-02-10 at 18:44, Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JC __ Brings words and photos together (easily) with PhotoMail - it's free and works with Yahoo! Mail. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence skipping values
On Fri, 2006-02-10 at 11:44, Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks Have you got some kind of trigger or rule on this table that could be causing such behaviour? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Sequence skipping values
Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JC Any rules, triggers, etc. involved? How are the inserts done (insert one record into the table)? What relationships does the table have to any other tables? Anything else accessing that sequence? There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 09:57:31AM -0800, Steve Crawford wrote: There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence. Also, deleting records won't modify the sequence (unless a trigger does so; the point is that the delete itself doesn't). If you delete the latest records in the table, the sequence will continue from where it was after the last insert (more properly, after the last call to nextval), not from the new highest value in the table. Other possibilities, though less likely, are that the sequence has non-default CACHE or INCREMENT values. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence skipping values
Hi,Thanks four your answers. Let me give more details here.The table with the id not incrementing by 1 as I expected is named topics.I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated.Each of those three tables contain a column that refer to topics.id as a foreign key.Those three columns contain id automatically generated by sequences and I have not observed any problemThanksJCSteve Crawford [EMAIL PROTECTED] wrote: Jean-Christophe Roux wrote: Hello, I have a table with and id field (primary key) which default value is the result of a sequence (increment explicitly set to 1). To my surprise, the real increment on insert is the total number of rows of the table. For instance, with 41 rows and a sequence last_value of 1141, the next insert row will have a value of 1182. It is not a big problem but I don't like skipping and wasting values in a sequence. Also, I'd like to understand what's going on! If someone could give me a clue that wold be greatly appreciated thanks JCAny rules, triggers, etc. involved? How are the inserts done (insert one record into the table)? What relationships does the table have to any other tables? Anything else accessing that sequence?There is no guarantee that a sequence will be contiguous. For example, begin...insert into...rollback will not reset the sequence as other transactions could have incremented the sequence.Cheers,Steve Yahoo! Mail Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
Jean-Christophe Roux wrote: Hi, Thanks four your answers. Let me give more details here. The table with the id not incrementing by 1 as I expected is named topics. I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated. Each of those three tables contain a column that refer to topics.id as a foreign key. Those three columns contain id automatically generated by sequences and I have not observed any problem Start psql and on each of those tables run \d tablename. Also run select * from sequencename for any sequences involved. Post results here - I suspect some clues will be contained therein. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote:
The table with the id not incrementing by 1 as I expected is named topics.
I have three other tables that contain rules that on insert into those
tables, some fields of the table Topic should be updated.
Each of those three tables contain a column that refer to topics.id as a
foreign key.
Those three columns contain id automatically generated by sequences and I
have not observed any problem
The word rules attracts attention; questions about sequences being
incremented multiple times due to rules appear in the lists regularly.
The problem is that where you might think the rule uses a value it's
really using an expression, so each time you use the value in the
rule you're evaluating the expression again. Example:
CREATE TABLE foo (id serial);
CREATE TABLE bar (id1 integer, id2 integer, id3 integer);
CREATE RULE foorule AS ON INSERT TO foo
DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);
INSERT INTO foo DEFAULT VALUES;
SELECT * FROM foo;
id
1
(1 row)
SELECT * FROM bar;
id1 | id2 | id3
-+-+-
2 | 3 | 4
(1 row)
When the rule rewrote the query it didn't use
INSERT INTO bar VALUES (1, 1, 1)
but rather
INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'),
nextval('foo_id_seq'))
because NEW.id evaluates to a nextval expression, not to the result
of that expression.
If you post the table definitions as Steve requested we'll be able
to see whether the above is indeed what's happening.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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
Re: [GENERAL] Sequence skipping values
Hi here are the structures of the table involved:CREATE TABLE topics( topic_id int8 NOT NULL DEFAULT nextval('topics_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, topic text NOT NULL, administrator_id int8 NOT NULL, status_id int8 DEFAULT 0, last_change timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, links int8 DEFAULT 0, releases int8 DEFAULT 0, last_administrator_id int8, CONSTRAINT topics_pk PRIMARY KEY (topic_id), CONSTRAINT topics_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT topics_status_fk FOREIGN KEY (status_id) REFERENCES status_list (status_id)
MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) select * from topics_id_seq"topics_id";1224;1;9223372036854775807;0;1;23;f;tit is this topics_id_seq that does not increment by one unit at a time, but rather by the number of rows of the table topics.To make things shorter, I am posting only one of the three tables. The other two tables are similar and have the same kind of rulesCREATE TABLE releases( topic_id int8 NOT NULL, release_id int8 NOT NULL DEFAULT nextval('releases_id_seq'::text), datetime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone, body text NOT NULL, administrator_id int8 NOT NULL, CONSTRAINT releases_pk PRIMARY KEY (release_id), CONSTRAINT releases_fk_administrator_id FOREIGN KEY (administrator_id) REFERENCES administrators (administrator_id) MATCH
SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT releases_fk_topic_id FOREIGN KEY (topic_id) REFERENCES topics (topic_id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT) CREATE OR REPLACE RULE releases_increment_topics AS ON INSERT TO releases DO UPDATE topics SET releases = topics.releases + 1 WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_administrator_id AS ON INSERT TO releases DO UPDATE topics SET last_administrator_id = new.administrator_id WHERE topics.topic_id = new.topic_id;CREATE OR REPLACE RULE releases_last_change AS ON INSERT TO releases DO UPDATE topics SET last_change = now() WHERE topics.topic_id = new.topic_id;Thanks again for your time and explanations; it is qu
ite
useful.RegardsJCRMichael Fuhr [EMAIL PROTECTED] wrote: On Fri, Feb 10, 2006 at 03:27:23PM -0800, Jean-Christophe Roux wrote: The table with the id not incrementing by 1 as I expected is named topics. I have three other tables that contain rules that on insert into those tables, some fields of the table Topic should be updated. Each of those three tables contain a column that refer to topics.id as a foreign key. Those three columns contain id automatically generated by sequences and I have not observed any problemThe word "rules" attracts attention; questions about sequences beingincremented multiple times due to rules appear in the lists regularly.The problem is that where you might think the rule uses a value it'sreally using an expr
ession,
so each time you use the "value" in therule you're evaluating the _expression_ again. Example:CREATE TABLE foo (id serial);CREATE TABLE bar (id1 integer, id2 integer, id3 integer);CREATE RULE foorule AS ON INSERT TO foo DO ALSO INSERT INTO bar VALUES (NEW.id, NEW.id, NEW.id);INSERT INTO foo DEFAULT VALUES;SELECT * FROM foo; id 1(1 row)SELECT * FROM bar; id1 | id2 | id3 -+-+- 2 | 3 | 4(1 row)When the rule rewrote the query it didn't use INSERT INTO bar VALUES (1, 1, 1)but rather INSERT INTO bar VALUES (nextval('foo_id_seq'), nextval('foo_id_seq'), nextval('foo_id_seq'))because NEW.id evaluates to a nextval _expression_, not to the resultof that _expression_.If you post the table definitions as Steve requested we'll be ableto see whether the above is indeed what's happening.--
Michael
Fuhr
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
Re: [GENERAL] Sequence skipping values
On Fri, Feb 10, 2006 at 08:10:36PM -0800, Jean-Christophe Roux wrote: here are the structures of the table involved: I couldn't duplicate the problem in 8.1.2 or 8.0.6 with the code you posted. I created the given tables and rules (plus guesses for the administrators and status_list tables), then inserted several records into topics, then inserted a few records into releases, then inserted a few more records into topics. The topics_id_seq sequence incremented by one each time with no gaps; that probably means my test didn't match exactly what you're doing. Does the topics table have any rules or triggers that you didn't show? Could you post a minimal but complete test case, i.e, all SQL statements that somebody could load into an empty database to reproduce the problem? If not then it might be useful to see the EXPLAIN ANALYZE output of a series of statements that exhibit the unexpected behavior. What version of PostgreSQL are you running? -- Michael Fuhr ---(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
