Wait for the sequence to get last_value

Question

I have a query that gives all sequences together with the nextval:

SELECT c.oid::regclass, setval(c.oid, nextval(c.oid), false) 
FROM pg_class c 
WHERE c.relkind = 'S'

But it throws an error on the production database:

ERROR:  cannot access temporary tables of other sessions

I've also created a function with last_value (to avoid setting the sequence value) like in this post Get max id of all sequences in PostgreSQL

That doesn't help.

Is there a way to wait for all sequences to get finished without locking all tables?

Thats my function

CREATE TYPE tp_sequencedetails AS (sequence_name text, last_value bigint);

CREATE OR REPLACE FUNCTION getsequenceswithdetails()
  RETURNS SETOF tp_sequencedetails AS
  $BODY$
    DECLARE
      returnrec tp_sequencedetails;
      sequence_name text;
    BEGIN

      FOR sequence_name IN (SELECT c.oid::regclass FROM pg_class c WHERE c.relkind = 'S')
      LOOP
        FOR returnrec IN EXECUTE 'SELECT ''' || sequence_name || ''', last_value FROM ' || sequence_name
        LOOP
          RETURN NEXT returnrec;
        END LOOP;
      END LOOP;

    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

What are you trying to accomplish by doing this? What's the underlying problem you're trying to solve? You shouldn't need to do this. — Craig Ringer, Dec 02 '14 at 08:46
What do you mean by "Is there a way to wait for all sequences to get finished without locking all tables?" ? A sequence doesn't lock at all, you can always get the current value for this sequence. — Frank Heikens, Dec 02 '14 at 08:53
@FrankHeikens it seems to lock the sequences while the transaction that used this sequence is not finished yet. I've not debuged it yet. — iRaS, Dec 02 '14 at 09:00
@CraigRinger I'm copying the database, without the data but sequence values. — iRaS, Dec 02 '14 at 09:01

score 2 · Accepted Answer · answered Dec 02 '14 at 09:47

ERROR: cannot access temporary tables of other sessions

In another session you're creating a temporary sequence. You are now trying to get or to set a value for this sequence, but it's not visible in your current session. A temporary table and sequence is only visible for the session that creates this object.

Solution: Keep the temporary sequences out of your query.

SELECT  c.oid::regclass, setval(c.oid, nextval(c.oid), false) 
FROM pg_class c
    JOIN pg_namespace ON pg_namespace.oid = relnamespace
WHERE c.relkind = 'S'
AND nspname NOT ILIKE 'pg_temp%';

Wait for the sequence to get last_value

1 Answers1