CODEWITHSUNDEEP
sqlpostgresql
Peter Bugla
Peter Bugla

Reputation: 48

PostgreSQL Sequence: How to always increment when called (no matter what the result of a select statement is)?

(I use PostgreSQL 12.4.1)

I need to iterate through a table grabbing each record in there by its id each time returning 1 record (or none, if there's nothing left, of course) and make it available in a view. (That might sound strange, but that's what is needed.)

I have a way to handle that with a sequence, which I would expect to work, but it has the issue that it stops incrementing the sequence when it encounters a gap in the data table (so PostgreSQL doesn't increment the sequence if the select came up with an empty result set).

A small example to reproduce the issue (grabbed from PostreSQL Admin 4 Create Scripts):

CREATE SEQUENCE public.next_job_id
    INCREMENT 1
    START 30000
    MINVALUE 1
    MAXVALUE 1000000
    CACHE 1;

 

ALTER SEQUENCE public.next_job_id
    OWNER TO postgres;

 

CREATE TABLE public.test_table
(
    processing_job_id integer NOT NULL,
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT test_table_pkey PRIMARY KEY (processing_job_id)
)

 

TABLESPACE pg_default;

 

ALTER TABLE public.test_table
    OWNER to postgres;

 

CREATE OR REPLACE VIEW public.test_metrics_view
 AS
 SELECT t.processing_job_id, t.name, nextval('next_job_id') AS sequence_id
   FROM test_table t, next_job_id n
  WHERE t.processing_job_id = n.last_value;

 

ALTER TABLE public.test_metrics_view
    OWNER TO postgres;

Then add some data entries in test_table, let's say

processing_job_id  name
30000  Alpha
30001  Bravo
30003  Delta
30004  Echo

Important: Leave a gap in the processing_job_id column, otherwise it works fine as it is.

To answer some obvious questions: No, filling gaps in test_table is not an option. Yes, the view should contain only "the next record" (always only 1 record or none at all).

Problem: When the SELECT statement in the view (see code above) is executed (e.g. by calling SELECT * FROM public.test_metrics_view) for the gap (30002) the sequence is not incremented (I assume the empty result set somehow causes PostgreSQL to consider it not to be necessary).

What I need instead is either: If it encounters a gap, it should ... A (optimal): increment the sequence until it gets a result (=skip ahead to the next existing id) B (still okay): increment the sequence (so it might return 1 empty result set for each gap, but continue processing the table)

We tried a couple of things that were not successful so far. Any help appreciated.

Upvotes: 1

Views: 1021

Answers (1)

user14277246
user14277246

Reputation:

Use a simple function (don't know if with is parsable with views) to first increment the sequence explicitly, then perform then perform the select.

--drop function test_metrics_func()
create or replace function test_metrics_func() returns
table(
    processing_job_id integer,
    name text,
    seq_id bigint
     )
as $$
/*
explicitly increment the seq, and keep the update value cached
*/
with seq_inc as (select nextval('next_job_id') as seq)
--use the cached seq, instead of seq here
SELECT t.processing_job_id, t.name, s.seq AS sequence_id
   FROM test_table t, seq_inc s
  WHERE t.processing_job_id = s.seq
$$ language sql;

Note: Since seq-inc is explicitly called first, so the init value should be set to 29999, not 30000 in your case.

Testing

--resetting the seq to 29999 (important), or alpha will be ignored
select setval('public.next_job_id', 29999, true);
--calls
select * from test_metrics_func() ---> 30000    "alpha" 30000 
select * from test_metrics_func() ---> 30001    "bravo" 30001
select * from test_metrics_func() ---> nothing
select * from test_metrics_func() ---> 30003    "belta" 30003
select * from test_metrics_func() ---> 30004    "echo"  30004
select * from test_metrics_func() ---> nothing

NOTE: am not sure if the func(nextval) is thread-safe or not, and how does pg is going to deal with it.

Upvotes: 1

Related Questions