Обсуждение: BUG #14099: Altering temporary sequence in session has no effect

The following bug has been logged on the website:

Bug reference:      14099
Logged by:          Imraan Parker
Email address:      imraan@techie.com
PostgreSQL version: 9.5.2
Operating system:   Linux
Description:

Altering a temporary sequence using ALTER SEQUENCE within a session has no
effect. I am trying to set the starting value of the sequence. All this
happens within a PL/PGSQL function as seen below.

CREATE OR REPLACE FUNCTION _test_seq() RETURNS integer AS
$BODY$
BEGIN
   BEGIN
      CREATE TEMP SEQUENCE tt_seq;
      EXCEPTION WHEN OTHERS THEN
         ALTER SEQUENCE tt_seq START 1;
         --PERFORM setval('tt_seq', 1);
   END;
   PERFORM nextval('tt_seq');
   PERFORM nextval('tt_seq');
   PERFORM nextval('tt_seq');
   RETURN currval('tt_seq');
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

SELECT * FROM _test_seq();

Running the function increments the sequence but never sets it back to 1. If
you uncomment the setval(), then it works.

The ALTER SEQUENCE works on 9.1 and 9.3. I do not have a 9.4 instance to
test on so I cannot be sure if the problem is there too.

imraan@techie.com writes:
> Altering a temporary sequence using ALTER SEQUENCE within a session has no
> effect.

It would if you were using the right ALTER SEQUENCE subcommand.
Per the manual:

  The optional clause START WITH start changes the recorded start value of
  the sequence. This has no effect on the current sequence value; it
  simply sets the value that future ALTER SEQUENCE RESTART commands will
  use.

  The optional clause RESTART [ WITH restart ] changes the current value
  of the sequence. This is equivalent to calling the setval function with
  is_called = false: the specified value will be returned by the next call
  of nextval. Writing RESTART with no restart value is equivalent to
  supplying the start value that was recorded by CREATE SEQUENCE or last
  set by ALTER SEQUENCE START WITH.

> The ALTER SEQUENCE works on 9.1 and 9.3.

A quick test suggests that this works the same at least as far back as
9.1.

            regards, tom lane