Postgres sequence's last_value field does not work as expected

Question

I have a table in postgres whose primary key is assinged using a sequence (let's call it 'a_seq'). The sequence is for incrementing the value and inserting the current value as primary key of record being inserted.

Code i use for sequence:

CREATE SEQUENCE public.a_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.AssembleTable_RowId_seq OWNER TO postgres;

I am trying to copy a file from a disk and insert the information about copied file to table. There are files with same name in the disk so i'm retrieving the "last_value" from the sequence with this query:

SELECT last_value FROM a_seq;

and rename file "_" then insert it do database so the file name and the primary key (id) of that file is coherent like:

id | fileName                         
1  | 1_asd.txt

But when i insert the record, the id is always 1 value greater than the "last_value" a get from the query so table looks like this:

id | fileName                         
2  | 1_asd.txt

And i've tried to execute the select query above multiple times to check if its increment the value but it doesn't.
Any idea how to get the value which will be assinged to the record before the insertion?

NOTE: I use MATLAB and this is the code is use for insertion:

colnames = {'DataType'         , ...
            'FilePath'         , ...
            'FileName'         , ...
            'FileVersion'      , ...
            'CRC32Q'           , ...
            'InsertionDateTime', ...
            'DataSource'       };

data = {FileLine{5}  ,... % DataType
        tempPath     ,... % FilePath
        FileLine{1}  ,... % FileName
        FileLine{2}  ,... % FileVersion
        FileLine{3}  ,... % CRC32Q
        FileLine{4}  ,... % InsertionDateTime
        FileLine{6}  ,... % DataSource};

data_table = cell2table(data, 'VariableNames', colnames);
datainsert(conn , 'CopiedFiles' , colnames , data_table);

Marrin

show the insert statement please – Opiumism

To get the next sequence number use the function nextval('a_seq') – Hatband

I dont see any last_value in your MATLAB code - please post the code in regard to last_value – Opiumism

@VaoTsun The query SELECT last_value FROM a_seq; is being execute before insertion to rename copied file. – Potential

@a_horse_with_no_name But it increments the sequence value before insertion so there is still 1 value difference between file name and record id. – Potential

What I believe happens for you is: when you select last_value - you get last used sequence value and when you insert row, the default value for id is nextval, which rolls value by one above – Opiumism

Answer 1

updated

What I believe happens for you is: when you select last_value - you get last used sequence value and when you insert row, the default value for id is nextval, which rolls value by one above...

previous I believe you have an extra nextval somewhere in middle step. If you do it in one statement, it works as you expect, eg:

t=# create table so12(s int default nextval('s'), t text);
CREATE TABLE
t=# insert into so12(t) select last_value||'_abc.txt' from s;
INSERT 0 1
t=# select * from so12;
 s |     t
---+-----------
 1 | 1_abc.txt
(1 row)

update2 as Nick Barnes noticed, further (then initial1) iterations will give wrong results, su you need to use heis proposed CASE logic

Opiumism

The solution i find via your answer was retrieving the last_value increment it in MATLAB and rename file. And after i insert the record both file name and id is same. Thanks! – Potential

@NickBarnes yes - a good point. I did not think to try further iterations. thank you – Opiumism

Answer 2

This is a quirk in the way Postgres implements sequences; as inherently non-transactional objects in a transactional database, they behave a bit strangely.

The first time you call nextvalue() on a sequence, it will not affect the number you see in a_seq.last_value. However, it will flip the a_seq.is_called flag:

test=# create sequence a_seq;
test=# select last_value, is_called from a_seq;
 last_value | is_called
------------+-----------
          1 | f

test=# select nextval('a_seq');
 nextval
---------
       1

test=# select last_value, is_called from a_seq;
 last_value | is_called
------------+-----------
          1 | t

So if you need the next value in the sequence, you'd want something like

SELECT
  last_value + CASE WHEN is_called THEN 1 ELSE 0 END
FROM a_seq

Note that this is horribly broken if two processes are doing this concurrently, as there's no guarantee you'll actually receive this value from your next nextval() call. In that case, if you really need the filename to match the id, you'd need to either generate it with a trigger, or UPDATE it once you know what the id is.

But in my experience, it's best to avoid any dependencies between your data and your keys. If all you need is a unique filename, I'd just create an independent filename_seq.

Madancy

"The first time you call nextvalue() within a session [...]" Is it really per session, not per sequence? Running your example multiple times (in different sessions) returns the true last value – Imminence

@JGH: Oops, you're right. Think I go this confused with currval() (which doesn't work until you've called nextval() in your session). Thanks! – Madancy

Answer 3

When INSERT statement is executed without a value for id - Postgres automatically takes it from sequence using next_val. List of columns in the variable colnames does not have an id, so PG takes next value from the sequence. To solve the problem you may add id to colnames.

Callup

But the reason i created a sequence is not to deal with the unique id for every file. – Potential

Answer 4

To avoid any dependencies between your data and your keys, Please try:

CREATE SEQUENCE your_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE your_sequence
OWNER TO postgres;

Yolande

Answer 5

looking at what you posted, to select the last_value of x_seq you must call it like a table ie:

select last_value from xscheme.x_seq;

Leonhard