LINUX.ORG.RU

Проблема с упорядочиванием записей в PostgreSQL

 


0

2
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;

блокировки перед апдейтом а не перед вызовом функции триггера по сути в результате получается

 При удалении t2 с id = 1 update t2 set position = position - 1 where position > 0;
 При удалении t2 с id = 2 update t2 set position = position - 1 where position > 1;
 При удалении t2 с id = 3 update t2 set position = position - 1 where position > 2;
отсюда и 1,2

anonymous ()

Изменение таблицы в триггере по той-же самой таблице считается крайне некрасивым решением.

anonymous ()
Ответ на: комментарий от anonymous

Предложите красивое решение. Пока сделал statement-level trigger который проходится по всем записям что думаю ещё более некрасиво но по крайней мере работает и записей не очень много да и delete не часто делается:

create function t2_after_delete() returns trigger as $$
declare
	rec record;
	pos bigint;
begin
	pos := 0;
	for rec in select id from t2 order by position asc loop
		EXECUTE format('update t2 set position = %L where id = %L', pos, rec.id);
		pos := pos + 1;
	end loop;
	
	return null;
end;
$$ language plpgsql;
create trigger t2_after_delete after delete on t2 for each statement execute procedure t2_after_delete();
iluha16 ()
Ответ на: комментарий от anonymous

Прочитав по ссылке мне кажется это совсем другое либо я что то не так понял. t2 это упрощённая таблица для демонстрации проблемы.

Таблица хранит в каждой записи условия которые должны тестироваться в определённом порядке устанавливаемым админом. Т.е. например

position 0: если x > (t2.min_x = 2) -> класс #1
position 1: если x > (t2.min_x = 1) -> класс #2

При такой последовательности при x = 3 будет присвоен класс #1. Если переставить местами будет класс #2. Поэтому порядок важен.

iluha16 ()

Отлично. Это будет 162-й пример моей коллекции говнокода в разделе: «почему триггеры по сути есть костыль, позволяющий криво спроектированной БД пусть медленно и с глюками, но работать».

r_asian ★☆☆ ()
Ответ на: комментарий от r_asian

Ну так посоветуй правильное решения. В этой теме одна критика и ни одного совета по поводу как сделать правильно и красиво.

iluha16 ()
Ответ на: комментарий от anonymous

Если тебе нужно в админке упорядоченный набор условий, то зачем заморачиваться над конкретными значениями position? Удалили ну и хрен с ним, поменяли местами, свапни у строк postition, вставили новый и +1 от текущего не уникальный — сделай update +1 на все position больше текущего. Зачем заморачиваться над инвариантом про all position < N решительно не понятно.

anonymous ()
Ответ на: комментарий от iluha16

При такой последовательности при x = 3 будет присвоен класс #1. Если переставить местами будет класс #2. Поэтому порядок важен.

Так порядок от дырок в нумерации не меняется. 5 > 2 не зависимо от того, есть в таблице 4 или его удалили.

anonymous ()
Ответ на: комментарий от iluha16

Может быть сделать триггер «FOR EACH STATEMENT»?

Это, мне кажется, логичным. В конце концов, тебе же нужно пересчитать один столбец, после изменения таблицы. Правильно это делать после окончания всей операции изменения таблицы, а не после изменения каждого столбца, которые, постгрес, скорее всего параллелит (см explain и пр).

PS: Модифицировать всю таблицу из триггера на каскадной операции это, прям как стрелять в ногу из пулемета.

soomrack ★★★ ()
Ответ на: комментарий от iluha16

А если хочешь красиво, то делай последовательность:

CREATE SEQUENCE seq_mytable_pos START 0;

а потом пихай в триггер FOR EACH STATEMENT код:

SELECT setval(seq_mytable_pos, 0);
UPDATE mytable SET pos = nextval(seq_mytable_pos);
soomrack ★★★ ()
Последнее исправление: soomrack (всего исправлений: 1)
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.