Generating incremental numbers based on a different column

Question

I have got a composite primary key in a table in PostgreSQL (I am using pgAdmin4)

Let's call the the two primary keys productno and version.

version represents the version of productno.

So if I create a new dataset, then it needs to be checked if a dataset with this productno already exists.

• If productno doesn't exist yet, then version should be (version) 1
• If productno exists once, then version should be 2
• If productno exists twice, then version should be 3
  ... and so on

So that we get something like:

  productno | version 
 -----|----------- 
    1 |         1 
    1 |         2 
    1 |         3 
    2 |         1 
    2 |         2 

I found a quite similar problem: auto increment on composite primary key

But I can't use this solution because PostgreSQL syntax is obviously a bit different - so tried a lot around with functions and triggers but couldn't figure out the right way to do it.

Answer 1

You can keep the version numbers in a separate table (one for each "base PK" value). That is way more efficient than doing a max() + 1 on every insert and has the additional benefit that it's safe for concurrent transactions.

So first we need a table that keeps track of the version numbers:

create table version_counter 
(
  product_no integer primary key, 
  version_nr integer not null
);

Then we create a function that increments the version for a given product_no and returns that new version number:

create function next_version(p_product_no int) 
  returns integer
as
$$
   insert into version_counter (product_no, version_nr) 
   values (p_product_no, 1)
   on conflict (product_no) 
   do update 
      set version_nr = version_counter.version_nr + 1
   returning version_nr;
$$
language sql
volatile;

The trick here is the the insert on conflict which increments an existing value or inserts a new row if the passed product_no does not yet exists.

For the product table:

create table product
(
  product_no integer not null, 
  version_nr integer not null,
  created_at timestamp default clock_timestamp(),
  primary key (product_no, version_nr)
);

then create a trigger:

create function increment_version()
  returns trigger
as
$$
begin
  new.version_nr := next_version(new.product_no);
  return new;
end;
$$
language plpgsql;

create trigger base_table_insert_trigger
  before insert on product
  for each row
  execute procedure increment_version();

This is safe for concurrent transactions because the row in version_counter will be locked for that product_no until the transaction inserting the row into the product table is committed - which will commit the change to the version_counter table as well (and free the lock on that row).

If two concurrent transactions insert the same value for product_no, one of them will wait until the other finishes.

If two concurrent transactions insert different values for product_no, they can work without having to wait for the other.

If we then insert these rows:

insert into product (product_no) values (1);
insert into product (product_no) values (2);
insert into product (product_no) values (3);
insert into product (product_no) values (1);
insert into product (product_no) values (3);
insert into product (product_no) values (2);

The product table looks like this:

select *
from product
order by product_no, version_nr;

product_no | version_nr | created_at             
-----------+------------+------------------------
         1 |          1 | 2019-08-23 10:50:57.880
         1 |          2 | 2019-08-23 10:50:57.947
         2 |          1 | 2019-08-23 10:50:57.899
         2 |          2 | 2019-08-23 10:50:57.989
         3 |          1 | 2019-08-23 10:50:57.926
         3 |          2 | 2019-08-23 10:50:57.966

Online example: https://rextester.com/CULK95702