Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

База данных и целостность данных (целостность ссылок)

Posted Апрель 25th, 2011 by BrokenEye
in

Приветствую всех :)

Прошу прощения за простыню, но описать вопрос короче не получается.
Раньше мне не приходилось задумываться над этой проблемой, да и, плюс ко всему, имеется слабое понимание работы внутренних механизмов всевозможных СУБД, поэтому пишу с целью получить конкретные рекомендации, либо определить направление, в котором стоит изучать данную тему.

Если кратко, то вопрос касается того, как избежать ссылок на несуществующие записи в БД, при одновременной работе нескольких пользователей.

Теперь сама суть вопроса:
Возможна ли такая ситуация, что между первым и вторым этапом работы скрипта, группа, к которой привязывается добавляемый аккаунт, будет удалена? Соответственно мы добавим аккаунт с привязкой к уже не существующей группе.

Хочется не оценивать вероятность возникновения подобной ситуации, а понять возможна ли такая ситуация в принципе и если возможна, то какими средствами подобных ситуаций можно избежать?

Если она все таки возможна, то я вижу следующие способы ее избежать:

  1. Разработать сервисный скрипт, который будет отыскивать "потерянные" аккаунты и выполнять с ними какие-либо действия. Т.е. мы будем иметь возможность проводить профилактику.
  2. Использовать блокировки таблиц. Вариант, как я понимаю, реальный, но со своими минусами.

Так вот существуют ли какие-либо более изящные решения данной проблемы?

Мне рекомендовали смотреть в сторону транзакций, но и тут у меня есть вопросы :)

К примеру такая функция

CREATE OR REPLACE FUNCTION test() RETURNS bigint AS
$BODY$
begin
 
	INSERT INTO
	TABLE
	(name) VALUES ('123');
 
	raise exception '_fail_';
 
	INSERT INTO
	TABLE
	(name) VALUES ('456');
 
	exception
		when raise_exception then
			RETURN 0;
 
 
	RETURN 1;
end;
 
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test() OWNER TO owner;

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

Но что произойдет в этом случае?
Процесс добавления нового аккаунта можно реализовать при помощи следующей функции:

CREATE OR REPLACE FUNCTION add_account(p_group_id bigint, p_login character varying, p_passwd character varying) RETURNS bigint AS
 
$BODY$
declare
	result bigint;
 
begin
 
 
		-- Проверяем существует ли группа
	perform * FROM groups WHERE id = p_group_id;
 
	IF found IS false then
		result = -1;
		raise exception 'group_not_found';
	end IF;
 
	-- ->ВОПРОС<-
	-- Возможно ли технически, что группа, проверку на существование которой мы прошли выше, будет 	удалена и произойдет добавления аккаунта привязанного к уже несуществующей группе?
 
	INSERT INTO accounts
	(group_id, login, passwd)
	VALUES(p_group_id, p_login, p_passwd)
	returning id INTO result;
 
	RETURN result;
 
	exception
		when raise_exception then
			RETURN result;
 
end;
 
$BODY$
 
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION add_accoun(bigint, character varying, character varying) OWNER TO owner;

Надеюсь прольется хоть сколько света на мой вопрос :)

‹ INTEGER - NUMERIC - VARCHAR триггеры и триггерные функции ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Нашел тут некоторую

On Апрель 25th, 2011 BrokenEye says:

Нашел тут некоторую информацию, но учитывая мой уровень английского, я пока точно не понял, то ли это, что мне нужно. Однако подозреваю, что копаю в нужном направлении :)
http://www.postgresql.org/docs/8.1/static/transaction-iso.html

Внешний ключ в таблице

On Апрель 26th, 2011 Alarm says:

Внешний ключ в таблице пользователей решит Вашу проблему

Скорее всего ранее вы

On Апрель 26th, 2011 admin says:

Скорее всего ранее вы работали с MySQL в котором нет ни внешних ключей ни транзакций.
При добавлении новой записи используйте транзакцию, что гарантирует, что никто в момент добавление ничего нового не добавит.
Чтобы не оставалось записей ссылающихся на другие, используйте внешний ключ, как вам правильно сказал Alarm:
http://postgresql.ru.net/manual/ddl-constraints.html#DDL-CONSTRAINTS-FK

"При добавлении новой записи

On Апрель 29th, 2011 BrokenEye says:

"При добавлении новой записи используйте транзакцию, что гарантирует, что никто в момент добавление ничего нового не добавит." - а можно чуть подробнее об этом? :)
Или Вы имели в виду использование транзакций вкупе с внешним ключом?

Просто полагаясь на то, что я успел понять о транзакциях, могу сказать, что использование только транзакций (без внешнего ключа) не спасет от ситуации удаления группы перед добавлением аккаунта к ней привязываемого.

Хотя и с внешним ключом нет гарантий.

В общем я в сомнениях. Буду признателен, если немного раскроете эту мысль :)

Про транзакции, на русском с

On Апрель 29th, 2011 admin says:

Про транзакции, на русском с хорошим примером:
http://postgresql.ru.net/manual/tutorial-transactions.html
Вам не нужно городить исключения по той причине, что при использовании внешнего ключа и транзакции ситуации, когда чего-то не существует не может быть в принципе. Т.е. если вы добавляется с использованием транзакции, то в этот же момент удалить никто ничего не сможет. Если удаление произойдёт сразу после добавления, сработает внешний ключ и либо не даст удалить, либо удалит только что сделанную вами запись, чтобы обеспечить целостность данных.

С транзакциями более-менее

On Апрель 30th, 2011 BrokenEye says:

С транзакциями более-менее понятно, но меня интересовала возможность отлова ошибки внешнего ключа. Решил этот вопрос соответсвующими PHP функциями :)

Спасибо еще раз за участие. Узнал для себя много новых вещей :)

Спасибо за ответы,

On Апрель 29th, 2011 BrokenEye says:

Спасибо за ответы, ребят :)

Однако накопилось еще несколько вопросов. Буду рад, если рассудите.
Как и в прошлый раз - много текста, но надеюсь это не отпугнет :)

Если использовать внешний ключ, то функция добавления аккаунта может выглядеть так:
CREATE OR REPLACE FUNCTION add_account(p_group_id bigint, p_login character varying, p_passwd character varying) RETURNS bigint AS
 
$BODY$
declare
	result bigint;
 
begin
	INSERT INTO accounts
	(group_id, login, passwd)
	VALUES(p_group_id, p_login, p_passwd)
	returning id INTO result;
 
	RETURN result;
 
	exception
		when FOREIGN_KEY_VIOLATION then
			RETURN -1;
 
end;
 
$BODY$
 
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION add_accoun(bigint, character varying, character varying) OWNER TO owner;

Наличие обработчика исключений позволит приложению, вызывающему эту функцию, правильно отреагировать и, к примеру, сообщить пользователю о том, что он пытается добавить аккаунт с привязкой к несуществующей группе.
Правда я тут вычитал, что использования блока exception значительно увеличивает накладные расходы. Вычитал об этом вот тут: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

По сему вопросы:

  1. Есть ли альтернативный способ решения без использования exception? В основном хотелось бы иметь возможность выделить ошибку FOREIGN_KEY_VIOLATION без получения сообщений об ошибке на стандартный вывод. Все это ради того, чтобы уведомить об этом пользователя,
  2. В ситуации, если мы имеем более одного внешнего ключа, то возможно ли как-то понять по какому из них произошла ошибка? Может быть куда-то записывается наименование поля?
Впрочем есть у меня альтернативный вариант, но хотелось бы рассмотреть его отдельно от первой функции и вопроса с ней связанного.
Приведу его здесь, пожалуй, только за тем, чтобы услышать о его плюсах, минусах и нюансах по сравнению с первым, если у вас будет желание высказаться по этому поводу :)
По сути отличие этого варианта от приведенного в самом начале этой темы лишь в добавлении FOR UPDATE. Как я понимаю, это блокирует запись с группой, к которой мы хотим привязать добавляемый аккаунт.
CREATE OR REPLACE FUNCTION add_account(p_group_id bigint, p_login character varying, p_passwd character varying) RETURNS bigint AS
 
$BODY$
declare
	result bigint;
 
begin
 
 
		-- Проверяем существует ли группа
	perform * FROM groups WHERE id = p_group_id FOR UPDATE;
 
	IF found IS false then
		RETURN -1;
	end IF;
 
	INSERT INTO accounts
	(group_id, login, passwd)
	VALUES(p_group_id, p_login, p_passwd)
	returning id INTO result;
 
	RETURN result;
 
end;
 
$BODY$
 
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION add_accoun(bigint, character varying, character varying) OWNER TO owner;

это блокирует запись с

On Май 3rd, 2011 Alarm says:

это блокирует запись с группой
-очевидно что если будет несколько одновременных запросов то возникнет блокировка

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023