5

I need to read the current value of a sequence in a function. However, for the first time in each session I try to use currval(), I get following error:

currval of sequence "foo_seq" is not yet defined in this session

Hint for those who might find this question by googling for this error: you need to initialize the sequence for each session, either by nextval() or setval().

I could use something like lastval() or even setval('your_table_id_seq', (SELECT MAX(id) FROM your_table)); instead, but this seems seems either prone to gaps or slower than simple currval(). My aim is to avoid gaps and inconsistencies (I know some of the values will be added manually), so using nextval() before logic handling them is not ideal for my purpose. I would need this to initialize the sequence for the session anyway, but I would prefer to do something like this:

--start of the function here
IF is_not_initialized THEN
  SELECT setval('foo_seq', (SELECT MAX(id) FROM bar_table)) INTO _current;
ELSE
  SELECT currval('foo_seq') INTO _current;
END IF; 
--some magic with the _current variable and nextvalue() on the right position

The point is that I have no idea how might "is_not_initialized" look like and whether is it possible at all. Is there any function or other trick to do it?

EDIT: Actually, my plan is to let each group of customers choose between proper sequence, no sequence at all, and the strange "something like a sequence" I'm asking for now. Even if the customer wanted such a strange sequence, it would be used only for the columns where it is needed - usually because there are some analog data and we need to store their keys (usually almost gapless sequence) into the DB for backward compatibility.

Anyway, you are right that this is hardly proper solution and that no sequence might be better than such a messy workaround in those situations, so I'll think (and discuss with customers) again whether it is really needed.

Community
  • 1
  • 1
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
  • 2
    "*My aim is to avoid gaps*" - that's impossible. Sequence *will* have gaps, there is **no** way to avoid them. If you have a business requirement to avoid gaps, then you cannot use a sequence. But in general sequences are used to populate artificial primary keys and as such the actual value has not meaning whatsoever. Therefore gaps are not a problem. If you are relying on a gapless sequence values, your understanding of sequences is wrong. –  Jun 12 '14 at 11:09
  • 1
    Also *"I need to read the current value of a sequence"* - there is no such thing (in general). `currval()` returns *the value most recently obtained with `nextval()`* by definition (and `setval()` is more like a data-definition statement: it resets the sequence). I agree, some of these function-names are misleading. What are you trying to achieve with this? – pozs Jun 12 '14 at 11:39

2 Answers2

4

My aim is to avoid gaps and inconsistencies

You cannot use sequences if you want to avoid gaps. Nor can you reasonably use sequences if you want to assign some values manually.

The approach you are taking is unsound. It will not work. Forget about it, it isn't going to do what you think it's going to do.

I just wrote a sample implementation of a trivial gapless sequence generator for someone a few days ago, and there's a more complete one in this question.

You need to understand that unlike true sequences, gapless sequences are transactional. A consequence is that only one running transaction can have an uncommitted ID. If 100 concurrent transactions try to get IDs, only one of them will actually get the ID. The others will have to wait until that one commits or rolls back. So they're terrible for concurrency, especially if combined with long running transactions. They can also cause deadlocks if you use multiple different gapless sequences and different transactions might access them in different orders.

So think carefully whether you really need this.

Read: PostgreSQL gapless sequences

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
4

Craig, a_horse and pozs have provided information which can help you understand principles of using sequences. Apart from the question how are you going to use it, here is a function which returns current value of a sequence if it has been initialized or null otherwise.

If a sequence seq has not been initialized yet, currval(seq) raises exception with sqlstate 55000.

create or replace function current_seq_value(seq regclass)
returns integer language plpgsql 
as $$
begin
    begin
        return (select currval(seq));
    exception
        when sqlstate '55000' then return null;
    end;
end $$;

select current_seq_value('my_table_id_seq')
klin
  • 112,967
  • 15
  • 204
  • 232
  • That's pretty neat. With a bit more tweaking, it seems like you could produce a curr_or_next_seq_value function, that returns the currval if we have one, and if not, automatically calls nextval. That would be really handy in some situations. – Joe Strout Oct 19 '15 at 18:58