Odd postgres sequence behavior
I have a table a Postgres 9.04 database with a table in it called Versions:
CREATE TABLE tracking."Versions" ( "ObjectId" UUID NOT NULL, "From" BIGINT NOT NULL, "To" BIGINT, "DataTypeId" INTEGER NOT NULL REFERENCES tracking."DataTypes" ( "DataTypeId" ), CONSTRAINT "Versions_pkey" PRIMARY KEY ("ObjectId", "DataTypeId") );
There is also a sequence defined in the database that is used by the From & To columns:
CREATE SEQUENCE tracking."dbVersion" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;
The Versions table is actually keeping track of changes made to other tables. Without going into the details:
- When a row is created in one of these other tables, a row is added to the Versions table and the From column is supposed to be set to the next value of the sequence.
- If an existing row in one of those tables is updated, the From value of the corresponding row in the Versions table has to be set to the next value of the sequence.
- When a row in one of these other tables is deleted, the To column has to be set to the next value of the sequence.
Rather than setting the Default value of the From column to "nextval('tracking."dbVersion'), I implemented a stored function that returns the result of calling this function:
CREATE OR REPLACE FUNCTION tracking."NextVersion"() RETURNS BIGINT AS $$ SELECT nextval('tracking."dbVersion"'::regclass); $$ LANGUAGE Sql;
All my code for inserting rows into the tables is implemented in C# using Entity Framework 4. All of the C# code is working fine. The weird thing is that when I look at the data in the Versions table, the values in the From column are all even. When I look at the sequence's properties in PgAdmin, it's odd. But the next time a row is inserted, the value stored is even.
What am I doing wrong? How does Postgres always use all of the values when you put that nextval call in the default property of a column?
Well, time for me to feel sheepish.
I looked over my C# code for inserting rows into the Versions table & I found that I was actually calling the NextVersion stored procedure twice. That explains why the sequence was always even when it was written to the From field. I've removed the second call & problem solved.