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);

Answer 1

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.

Answer 2

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