create table t1 (
id bigserial primary key,
name text not null
);
create table t2 (
id bigserial primary key,
name text not null,
t1_id bigint not null references t1(id) on delete cascade,
position bigint not null,
constraint t2_position_unique unique (position) deferrable
);
create function t2_before_insert() returns trigger as $$
declare
nextval bigint;
begin
lock table t2 in access exclusive mode;
select coalesce(max(position) + 1, 0) into nextval from t2;
new.position = nextval;
return new;
end;
$$ language plpgsql;
create trigger t2_before_insert before insert on t2 for each row execute procedure t2_before_insert();
create function t2_after_delete() returns trigger as $$
begin
lock table t2 in access exclusive mode;
set constraints t2_position_unique deferred;
update t2 set position = position - 1 where position > old.position;
return new;
end;
$$ language plpgsql;
create trigger t2_after_delete after delete on t2 for each row execute procedure t2_after_delete();
insert into t1 (name) values ('one');
insert into t1 (name) values ('two');
insert into t2 (name, t1_id) values ('one_one', 1);
insert into t2 (name, t1_id) values ('one_two', 1);
insert into t2 (name, t1_id) values ('one_three', 1);
insert into t2 (name, t1_id) values ('two_one', 2);
insert into t2 (name, t1_id) values ('two_two', 2);
# select * from t1;
id | name
----+------
1 | one
2 | two
(2 rows)
# select * from t2;
id | name | t1_id | position
----+-----------+-------+----------
1 | one_one | 1 | 0
2 | one_two | 1 | 1
3 | one_three | 1 | 2
4 | two_one | 2 | 3
5 | two_two | 2 | 4
(5 rows)
# delete from t1 where id = 1;
DELETE 1
# select * from t2;
id | name | t1_id | position
----+---------+-------+----------
4 | two_one | 2 | 1
5 | two_two | 2 | 2
(2 rows)
Вопрос: почему после DELETE position 1, 2 а не 0, 1. При удалении t2 с id = 1 в триггере должно выполниться update t2 set position = position - 1 where position > 0;
