CODEWITHSUNDEEP
postgresqlsequence
Joe Strout
Joe Strout

Reputation: 2761

Why does pgsql claim the currval of sequence undefined even after calling nextval?

I'm working with a PostgresQL database, in which a trigger function logs changes to a history table. I'm trying to add a column which keeps a logical "commit ID" to group master and detail records together. I've created a (non-temporary) sequence, and before I start the batch of updates, I bump this. All my SQL is logged to a log file, so you can clearly see this happening:

2015-04-16 10:43:37 SQLSelect: SELECT nextval('commit_id_seq')
2015-04-16 10:43:37 commit_id_seq: 8

...but then I attempt the UPDATE, my trigger function attempts to use currval, and it fails:

2015-04-16 10:43:37 ERROR: ERROR:  currval of sequence "commit_id_seq" is not yet defined in this session
CONTEXT:  SQL statement "INSERT INTO history (table_name, record_id, sec_user_id, created, action, notes, status, before, after, commit_id)
        SELECT TG_TABLE_NAME, rec.id, (SELECT oid FROM pg_roles WHERE rolname = CURRENT_USER), now(), SUBSTR(TG_OP,1,1), note, stat, hstore(old), hstore(new), currval('commit_id_seq')"
PL/pgSQL function log_to_history() line 18 at SQL statement
 [3]

So my question is basically: WTF?

Upvotes: 2

Views: 2699

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22972

One of two reasons:

  1. Search_path differences, so you're actually talking about two different sequences.
  2. Different sessions. The "current value" is only defined for the session you call nextval() in.

You can add process-id to the logfile format to check if they are different sessions.

Upvotes: 3

Related Questions