Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE label (
  label text PRIMARY KEY
);

CREATE TABLE version (
  id           serial PRIMARY KEY
, label        text NOT NULL REFERENCES label
, version      integer NOT NULL DEFAULT 1
, created_at   timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
, UNIQUE(label, version)
);

CREATE TABLE

CREATE TABLE

BEGIN;

INSERT INTO label (label)
VALUES ('label1')
ON     CONFLICT (label) DO UPDATE
SET    label = NULL WHERE false   -- never executed, but still locks the row
RETURNING *;                      -- optional

INSERT INTO version (label, version)
SELECT 'label1', coalesce(max(v.version), 0) + 1
FROM   version v
WHERE  v.label = 'label1'
RETURNING *;

COMMIT;

BEGIN

label

label1

INSERT 0 1

id
label
version
created_at

1
label1
1
2023-02-02 03:06:31.726873+00

INSERT 0 1

COMMIT

CREATE FUNCTION f_new_label (_label text, OUT label text, OUT version int)
  RETURNS record
  LANGUAGE sql STRICT AS
$func$
INSERT INTO label (label)
VALUES (_label)
ON     CONFLICT (label) DO UPDATE
SET    label = NULL WHERE false;  -- never executed, but still locks the row

INSERT INTO version AS v (label, version)
SELECT _label, coalesce(max(v1.version), 0) + 1
FROM   version v1
WHERE  v1.label = _label
RETURNING v.label, v.version;
$func$;

SELECT * FROM f_new_label('foo');
SELECT * FROM f_new_label('foo');
SELECT * FROM f_new_label('foo');

CREATE FUNCTION

label
version

foo
1

SELECT 1

label
version

foo
2

SELECT 1

label
version

foo
3

SELECT 1

INSERT INTO label (label) VALUES ('bar');  -- orphaned label

SELECT * FROM f_new_label('bar');
SELECT * FROM f_new_label('bar');
SELECT * FROM f_new_label('bar');

INSERT 0 1

label
version

bar
1

SELECT 1

label
version

bar
2

SELECT 1

label
version

bar
3

SELECT 1

TABLE label;
TABLE version;

label

label1

foo

bar

SELECT 3

id
label
version
created_at

1
label1
1
2023-02-02 03:06:31.726873+00

2
foo
1
2023-02-02 03:06:31.730516+00

3
foo
2
2023-02-02 03:06:31.730516+00

4
foo
3
2023-02-02 03:06:31.730516+00

5
bar
1
2023-02-02 03:06:31.734522+00

6
bar
2
2023-02-02 03:06:31.734522+00

7
bar
3
2023-02-02 03:06:31.734522+00

SELECT 7

id	label	version	created_at
1	label1	1	2023-02-02 03:06:31.726873+00
2	foo	1	2023-02-02 03:06:31.730516+00
3	foo	2	2023-02-02 03:06:31.730516+00
4	foo	3	2023-02-02 03:06:31.730516+00
5	bar	1	2023-02-02 03:06:31.734522+00
6	bar	2	2023-02-02 03:06:31.734522+00
7	bar	3	2023-02-02 03:06:31.734522+00