From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | pabloa98 <pabloa98(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Lewis <mlewis(at)entrata(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Rob Sargent <robjsargent(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Could postgres12 support millions of sequences? (like 10 million) |
Date: | 2020-03-21 00:55:40 |
Message-ID: | dfeaaa15-81c3-7a13-6209-b4db3db83c64@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/20/20 2:13 PM, pabloa98 wrote:
>
>
> On Fri, Mar 20, 2020 at 10:26 AM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 3/20/20 9:59 AM, Adrian Klaver wrote:
> > On 3/19/20 10:31 PM, pabloa98 wrote:
> >> I see.
> >>
> >> Any suggestion? It should behave like a sequence in the sense that
> >> concurrent transitions will get different numbers from this
> >> alternative sequence like solution.
> >>
> >> In our case, we will need to do a call nextval('some_seq') (or
> >> similar) from different processes no more than twice every minute.
> >>
> >>
> >> It would be nice to have a sequence data type. Correct me if I am
> >> wrong, but It seems to me that a sequence data type would cost the
> >> same or less than the current sequence implementation.
> >>
> >> The schema would be more clear too. We could have a table like:
> >>
> >> CREATE TABLE pair(
> >> group INT NOT NULL,
> >> element INT NOT NULL,
> >> seq SEQUENCE INCREMENT 1
> >> START 1
> >> CACHE 1
> >> MINVALUE 1
> >> MAXVALUE 99999999
> >> NOT NULL,
> >> CONSTRAINT PRIMARY KEY (group, element)
> >> );
> >>
> >> And then:
> >>
> >> INSERT INTO event(group, element, code)
> >> VALUES (
> >> 1,
> >> 1,
> >> ( SELECT seq.nextval('code_1_1_seq') FROM pair p WHERE
> >> p.group=1 and p.code=1 )
> >> );
> >>
> >> Or perhaps storing all the sequences in the same table as rows will
> >> have the same behavior.
> >
> > If code is just something to show the sequence of insertion for
> group,
> > element combinations then maybe something like below:
> >
> > CREATE TABLE event(
> > group_id INT NOT NULL, --changed as group is reserved word
> > element INT NOT NULL,
> > insert_ts timestamptz NOT NULL DEFAULT clock_timestamp(),
> > PRIMARY KEY(group_id, element, insert_ts)
> > );
> >
> >
> > insert into event(group_id, element) VALUES
> > (1, 1),
> > (1, 1),
> > (1, 1),
> > (2, 1),
> > (1, 1),
> > (1, 3),
> > (1, 1),
> > (1, 3),
> > (2, 1),
> > (2, 1);
> >
> >
> > select * from event ;
> > group_id | element | insert_ts
> > ----------+---------+--------------------------------
> > 1 | 1 | 03/20/2020 09:51:12.675926 PDT
> > 1 | 1 | 03/20/2020 09:51:12.675985 PDT
> > 1 | 1 | 03/20/2020 09:51:12.675991 PDT
> > 2 | 1 | 03/20/2020 09:51:12.675996 PDT
> > 1 | 1 | 03/20/2020 09:51:12.676 PDT
> > 1 | 3 | 03/20/2020 09:51:12.676004 PDT
> > 1 | 1 | 03/20/2020 09:51:12.676008 PDT
> > 1 | 3 | 03/20/2020 09:51:12.676012 PDT
> > 2 | 1 | 03/20/2020 09:51:12.676016 PDT
> > 2 | 1 | 03/20/2020 09:51:12.67602 PDT
> > (10 rows)
> >
> >
> > select group_id, element, row_number() OVER (partition by (group_id,
> > element) order by (group_id, element)) AS code from event;
> > group_id | element | code
> > ----------+---------+------
> > 1 | 1 | 1
> > 1 | 1 | 2
> > 1 | 1 | 3
> > 1 | 1 | 4
> > 1 | 1 | 5
> > 1 | 3 | 1
> > 1 | 3 | 2
> > 2 | 1 | 1
> > 2 | 1 | 2
> > 2 | 1 | 3
> > (10 rows)
>
> Oops the above actually returned:
>
> select group_id, element, row_number() OVER (partition by (group_id,
> element) order by (group_id, element)) AS code, insert_ts from event;
> group_id | element | code | insert_ts
> ----------+---------+------+--------------------------------
> 1 | 1 | 1 | 03/20/2020 09:51:12.675926 PDT
> 1 | 1 | 2 | 03/20/2020 09:51:12.675985 PDT
> 1 | 1 | 3 | 03/20/2020 09:51:12.675991 PDT
> 1 | 1 | 4 | 03/20/2020 09:51:12.676008 PDT
> 1 | 1 | 5 | 03/20/2020 09:51:12.676 PDT
> 1 | 3 | 1 | 03/20/2020 09:51:12.676004 PDT
> 1 | 3 | 2 | 03/20/2020 09:51:12.676012 PDT
> 2 | 1 | 1 | 03/20/2020 09:51:12.67602 PDT
> 2 | 1 | 2 | 03/20/2020 09:51:12.676016 PDT
> 2 | 1 | 3 | 03/20/2020 09:51:12.675996 PDT
> (10 rows)
>
>
> Needs to be:
>
> select group_id, element, row_number() OVER (partition by (group_id,
> element) order by (group_id, element, insert_ts)) AS code, insert_ts
> from event;
> group_id | element | code | insert_ts
> ----------+---------+------+--------------------------------
> 1 | 1 | 1 | 03/20/2020 09:51:12.675926 PDT
> 1 | 1 | 2 | 03/20/2020 09:51:12.675985 PDT
> 1 | 1 | 3 | 03/20/2020 09:51:12.675991 PDT
> 1 | 1 | 4 | 03/20/2020 09:51:12.676 PDT
> 1 | 1 | 5 | 03/20/2020 09:51:12.676008 PDT
> 1 | 3 | 1 | 03/20/2020 09:51:12.676004 PDT
> 1 | 3 | 2 | 03/20/2020 09:51:12.676012 PDT
> 2 | 1 | 1 | 03/20/2020 09:51:12.675996 PDT
> 2 | 1 | 2 | 03/20/2020 09:51:12.676016 PDT
> 2 | 1 | 3 | 03/20/2020 09:51:12.67602 PDT
> (10 rows)
>
>
>
> Clever. :D
> I will use it on other things.
>
> The problem for this specific case is that if someone deletes a row,
> several codes will change. For this problem, codes should not be changed.
A new requirement.
To get a better response I would suggest posting a detailed model of
what you are after and why?
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | pabloa98 | 2020-03-21 02:53:43 | Re: Could postgres12 support millions of sequences? (like 10 million) |
Previous Message | Adrian Klaver | 2020-03-21 00:53:11 | Re: Could postgres12 support millions of sequences? (like 10 million) |