-- Test functions used

-- create n sequences.
CREATE FUNCTION create_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE 'CREATE SEQUENCE test' || CAST(n AS TEXT);
      n := n - 1;
    END LOOP;
    RETURN 0;
END
$$;

-- call nextval() on n sequences.
CREATE OR REPLACE FUNCTION nextval_seq(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE $$SELECT nextval('test$$ || CAST(n AS TEXT) || $$')$$;
        n := n - 1;
    END LOOP;
    RETURN 0;
END
$_$;


SELECT create_seq(30000); -- create 30000 sequences for testing
SELECT nextval_seq(30000); -- call nextval() on each of these new sequences

DROP TABLE IF EXISTS seq_test;

-- create a table to test which uses a sequence which will be half way up the linked list.
CREATE TABLE seq_test (id INT NOT NULL DEFAULT nextval('test15000'), value int not null);


-- Test insert speed
INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);

/* Unpatched.
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 556.273 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 552.356 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 671.185 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 579.625 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 731.435 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 617.625 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 544.717 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 528.094 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
*/

/* Patched
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 483.862 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 502.937 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 458.579 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 526.599 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 392.625 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 457.124 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 437.965 ms
postgres=# INSERT INTO seq_test (value) SELECT value FROM generate_series(1,100000) s(value);
INSERT 0 100000
Time: 598.410 ms
*/

-- Test 

DISCARD SEQUENCES;

CREATE OR REPLACE FUNCTION currval_loop(n integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
BEGIN
    WHILE n > 0 LOOP
        EXECUTE $$SELECT currval('test1');$$;
      n := n - 1;
    END LOOP;
    RETURN 0;
END
$_$;

/* patched
postgres=# select nextval('test1');
 nextval
---------
      63
(1 row)


Time: 6.932 ms
postgres=# select currval_loop(100000); -- in list mode with 1 sequence in the cache.
 currval_loop
--------------
            0
(1 row)


Time: 1856.452 ms
postgres=# select nextval_seq(32); -- fill sequence cache to 1 less than the switchover point
 nextval_seq
-------------
           0
(1 row)


Time: 3.404 ms
postgres=# select currval_loop(100000); -- test how the list cache performs with 32 sequences
 currval_loop
--------------
            0
(1 row)


Time: 1841.840 ms
postgres=# select nextval_seq(33); -- one more to switch to the hash table
NOTICE:  moved sequences into hash table
CONTEXT:  SQL statement "SELECT nextval('test33')"
PL/pgSQL function nextval_seq(integer) line 4 at EXECUTE statement
 nextval_seq
-------------
           0
(1 row)


Time: 21.401 ms
postgres=# select currval_loop(100000); -- check how the loop on currval performs with the hashtable.
 currval_loop
--------------
            0
(1 row)


Time: 1861.558 ms

postgres=# select nextval_seq(16000); -- fill the hashtable up to 16000 sequences.
 nextval_seq
-------------
           0
(1 row)


Time: 18646.860 ms

postgres=# select currval_loop(100000); -- check how a loop over currval performs with 16000 in the hash table.
 currval_loop
--------------
            0
(1 row)


Time: 1963.711 ms

*/

/* unpatched
postgres=# select nextval('test1'); -- put 1 sequence in the cache.
 nextval
---------
      62
(1 row)


Time: 1.458 ms
postgres=# select currval_loop(100000); -- test performance with 1 sequence.
 currval_loop
--------------
            0
(1 row)


Time: 1844.110 ms
postgres=# select nextval_seq(32); -- put 32 sequences in the list.
 nextval_seq
-------------
           0
(1 row)


Time: 57.925 ms
postgres=# select currval_loop(100000); -- performance of 32 in sequence list.
 currval_loop
--------------
            0
(1 row)


Time: 1802.433 ms
postgres=# select nextval_seq(16000); -- fill the list with 16000 sequences
 nextval_seq
-------------
           0
(1 row)


Time: 20680.676 ms
postgres=# select currval_loop(100000); -- check performance of currval loop with 16000 sequences in the cache
 currval_loop
--------------
            0
(1 row)


Time: 10329.220 ms  (patched version did this in 1963.711 ms)
postgres=#

*/