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?

Answers


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.

Tony


Need Your Help

control stdin and stdout of a ruby program in python

python ruby python-2.7 python-3.x six-python

First I should notice: I'm a python programmer with no knowledge about ruby!

How to display trending clips for the entire week, instead of the day?

ruby-on-rails ruby-on-rails-4

So, I have an application that tracks each clips view count and by doing so we're able to display the trending clips for the day. However, since the user-base is still relatively small we'd like to