On Fri, Mar 20, 2020 at 10:26 AM Adrian Klaver <adrian.kla...@aklaver.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.

Reply via email to