ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / проверка на уникальность
25 сообщений из 25, страница 1 из 1
проверка на уникальность
    #35596143
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
Здравствуйте,

Есть 3 таблицы каждая для типа пользователей :Т1 для админов, Т2 для заказчиков, Т3 для владельцев

В каждой таблице есть unique поле login не Primary key
login varchar(32) not null unique

Как написать функцию которая бы проверяла логин на уникальность?
...
Рейтинг: 0 / 0
15.10.2008, 15:45
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35596228
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
я написал примено так

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create or replace function sp_create_user (p_login varchar)
returns void
as $$
declare
	v_login varchar;
begin
	v_login := p_login;
	if v_login = admins.login or v_login = customers.login or v_login = owners.login then 
		return;
		exception 
		where unique_violation then
		raise notice ' caught unique violation % ', v_login;
		end;
 	end if;	
end;
$$language plpgsql;
выдаёт такую ошибку

ERROR: syntax error at or near "exception"
LINE 10: exception
^
...
Рейтинг: 0 / 0
15.10.2008, 16:09
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35596288
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
извините чуть ошибся
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create or replace function sp_create_user (p_login varchar)
returns void
as $$
declare
	v_login varchar;
begin
	v_login := p_login;
	if v_login = admins.login or v_login = customers.login or v_login = owners.login then 
		return;
	end if;
	exception 
	where unique_violation then
	raise notice ' caught unique violation % ', v_login;
end;
$$language plpgsql;

ERROR: syntax error at or near "where"
LINE 12: where unique_violation then
^
...
Рейтинг: 0 / 0
15.10.2008, 16:27
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35596486
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
Вы не сможете проверить уникальность не блокируя конкурентный INSERT и UPDATE

ps:
Вы имели ввиду в этой строке:
Код: plaintext
if v_login = admins.login or v_login = customers.login or v_login = owners.login then
"получить строку из таблицы admins в которой поле login равно v_login и т.п." ?

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


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
15.10.2008, 17:24
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35596634
Sad Spirit
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sad Spirit
Участник
valuez
Есть 3 таблицы каждая для типа пользователей :Т1 для админов, Т2 для заказчиков, Т3 для владельцев

В каждой таблице есть unique поле login не Primary key
login varchar(32) not null unique

Как написать функцию которая бы проверяла логин на уникальность?
Сделать одну таблицу и повесить на неё уникальный индекс. Чтобы не переписывать приложение, можно сделать T1, T2 и T3 представлениями по этой таблице.
...
Рейтинг: 0 / 0
15.10.2008, 18:09
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35597610
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
Сделать одну таблицу и повесить на неё уникальный индекс. Чтобы не переписывать приложение.. для таблиц T1, T2 и T3 написать триггеры, заполняющие новую таблицу
...
Рейтинг: 0 / 0
16.10.2008, 10:53
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35597900
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
подсказали сделать примерно так :
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
create or replace function sp_create_user (p_login varchar)
returns void
as $$
declare
	v_login varchar;
begin
	v_login := p_login;
	if exists (select login from admins where admins.login = v_login) then 
	end if;
	EXCEPTION 
		WHEN unique_violation then
		raise notice ' caught unique violation % ', v_login;
end;
$$language plpgsql;

всё вроде в порядке функция компилируется, но какогда вставляю существующего пользователя

Код: plaintext
select sp_create_user ('admin1');

мне просто выкидывается пустая таблица, а мне надо чтобы была проверка именно этого поля (login) и в случае повтора логина постгрес должен матюкаться
...
Рейтинг: 0 / 0
16.10.2008, 12:09
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598008
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
кстати решилось так
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create or replace function sp_create_user (p_login varchar)
returns void
as $$
declare
	v_login varchar;
begin
	v_login := p_login;
	if exists (select login from admins where admins.login = v_login) then 
	raise exception 'login already exists';
	end if;
end;
$$language plpgsql;

немного с exception'ами не разобрался

всем спасибо
...
Рейтинг: 0 / 0
16.10.2008, 12:35
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598074
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
Ваше решение не верно. Во первых - оно не соответствует Вашему условию авторЕсть 3 таблицывы же проверяете только в одной таблице. Во вторых - авторВы не сможете проверить уникальность не блокируя конкурентный INSERT и UPDATEво время выполнения Вашей функции sp_create_user соседняя транзакция может вставить новый логин и sp_create_user его не увидит.



--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
16.10.2008, 12:58
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598102
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
как тогда правильно сделать ?
...
Рейтинг: 0 / 0
16.10.2008, 13:08
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598134
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
поможет ли в этом случае savepoint - rollback ?
...
Рейтинг: 0 / 0
16.10.2008, 13:20
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598660
sourcer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sourcer
Участник
valuez,

Может задачу опишешь для чего все это тебе и нафига три таблицы?
Не проще ли обьеденить все в одну таблицу и добавить поле status которое означало бы кто он такой админ или не админ...

проверка на уникальность бы делалсь просто что то типа

INSERT INTO table (login) SELECT 'newlogin' WHERE NOT EXISTS (SELECT NULL FROM table WHERE login='newlogin') RETURNING id

возвратило ID значит добавилась запись и логин уникальный
...
Рейтинг: 0 / 0
16.10.2008, 15:47
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35598710
valuez
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
valuez
Гость
авторМожет задачу опишешь для чего все это тебе и нафига три таблицы?


ну описываю... имеются 3 таблицы
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
create table admins (
id integer default nextval ('user_seq') primary key,
creation_moment timestamp not null default 'now()',
login varchar( 64 ) not null,
password varchar( 72 ) not null,
display_name varchar( 255 ) not null,
description text null,
is_deleted boolean not null default false,
suspend_reason text null -- if not null then is suspended with reason else not suspended
);

create table customers (
id integer default nextval ('user_seq') primary key,
creation_moment timestamp not null default 'now()',
login varchar( 64 ) not null,
password varchar( 72 ) not null,
display_name varchar( 255 ) not null,
description text null,
company_id integer null references customers_companies(id),
is_super boolean,
is_deleted boolean not null default false,
suspend_reason text null -- if not null then is suspended with reason else not suspended
);

create table owners (
id integer default nextval ('user_seq') primary key,
creation_moment timestamp not null default 'now()',
login varchar( 64 ) not null,
password varchar( 72 ) not null,
display_name varchar( 255 ) not null,
description text null,
company_id integer null references owners_companies(id),
is_super boolean,
is_deleted boolean not null default false,
suspend_reason text null -- if not null then is suspended with reason else not suspended
);

пользователь регистрируется и надо проверить cуществует ли такой логин, при этом долджен быть захват ошибки
Код: plaintext
	raise exception 'login already exists';

поэтому и написал функцию
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
create or replace function sp_create_user (p_login varchar)
returns void
as $$
declare
	v_login varchar;
begin
	v_login := p_login;
	if exists (select login from admins where admins.login = v_login) then 
	raise exception 'login already exists';
	end if;
	if exists (select login from customers where customers.login = v_login) then 
	raise exception 'login for customers already exists';
	end if;
	if exists (select login from owners where owners.login = v_login) then
	raise exception 'login for owners already exists';
	end if;
end;
$$language plpgsql;

вот после этого последовало
[quot]Вы не сможете проверить уникальность не блокируя конкурентный INSERT и UPDATE
во время выполнения Вашей функции sp_create_user соседняя транзакция может вставить новый логин и sp_create_user его не увидит.[/quot]

вот я и спрашиваю можно (или вернее нужно ли) здесь применять SAVEPOINT - ROLLBACK ?
...
Рейтинг: 0 / 0
16.10.2008, 16:02
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599159
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
sourcerпроверка на уникальность бы делалсь просто что то типа

INSERT INTO table (login) SELECT 'newlogin' WHERE NOT EXISTS (SELECT NULL FROM table WHERE login='newlogin') RETURNING id

возвратило ID значит добавилась запись и логин уникальный

Вы не сможете проверить уникальность не блокируя конкурентный INSERT и UPDATE :)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
( 1 ) seb=> begin;
BEGIN
seb=> INSERT INTO table1 (login) SELECT 'newlogin' WHERE NOT EXISTS (SELECT NULL, pg_sleep( 10 ) FROM table1 WHERE login='newlogin') RETURNING id;
 id
----
   1 
( 1  запись)

INSERT  0   1 
    ( 2 ) seb=> begin;
        BEGIN
        seb=> INSERT INTO table1 (login) SELECT 'newlogin' WHERE NOT EXISTS (SELECT NULL, pg_sleep( 10 ) FROM table1 WHERE login='newlogin') RETURNING id;
         id
        ----
           2 
        ( 1  запись)

        INSERT  0   1 
        seb=> end;
        COMMIT
seb=> end;
COMMIT
seb=> select * from table1;
 id |  login
----+----------
   1  | newlogin
   2  | newlogin
( 2  rows)

постгрес версионник, а не блокировочник.
...
Рейтинг: 0 / 0
16.10.2008, 18:28
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599178
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
valuezвот я и спрашиваю можно (или вернее нужно ли) здесь применять SAVEPOINT - ROLLBACK ?Как Вы хотите здесь применить savepoint и зачем ? Вам нужно заблокировать все три таблицы от insert/update/delete и только после этого - проверять присутствие в них логина. но это ненужный изврат - из-за того что у Вас плохая схема данных. сделайте одну таблицу вместо трёх, с unique ограничением на login и не мучайтесь :)
...
Рейтинг: 0 / 0
16.10.2008, 18:34
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599180
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
на , pg_sleep(10) в примере не обращайте внимание - это я чёта не то написал %)


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
16.10.2008, 18:35
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599196
sourcer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sourcer
Участник
Ёш,

Вы воще поняли что написали то? )))))) Транзакции нахрен ненужны с 8.2 версии начная для этих целей, используйте RETURNING и радуйтесь.
...
Рейтинг: 0 / 0
16.10.2008, 18:47
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599205
sourcer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sourcer
Участник
Ёшvaluezвот я и спрашиваю можно (или вернее нужно ли) здесь применять SAVEPOINT - ROLLBACK ?Как Вы хотите здесь применить savepoint и зачем ? Вам нужно заблокировать все три таблицы от insert/update/delete и только после этого - проверять присутствие в них логина. но это ненужный изврат - из-за того что у Вас плохая схема данных. сделайте одну таблицу вместо трёх, с unique ограничением на login и не мучайтесь :)

тут полностью согласен
...
Рейтинг: 0 / 0
16.10.2008, 18:53
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599262
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
sourcerЁш,

Вы воще поняли что написали то? )))))) Транзакции нахрен ненужны с 8.2 версии начная для этих целей, используйте RETURNING и радуйтесь.взаимное непонимание :) для каких целей ?
пока первая транзакция не завершиться с commit - вторая НЕ увидит того что первая вставила в таблицу. даже если Вы принудительно не начинаете транзакцию - она начнётся автоматически - для запроса. пока Ваш первый insert проверяет и вставляет - в соседнем подключении второй инсерт может успеть вставить тот же логин.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
( 1 ) seb=> INSERT INTO table1 (login) SELECT 'newlogin' WHERE pg_sleep( 15 ) is not null and NOT EXISTS (SELECT NULL FROM table1 WHERE login='newlogin') RETURNING id;
--- спит 15 секунд на WHERE, а в это время в соседнем подключении...
        ( 2 ) seb=> INSERT INTO table1 (login) SELECT 'newlogin' WHERE NOT EXISTS (SELECT NULL FROM table1 WHERE login='newlogin') RETURNING id;
         id
        ----
           1 
        ( 1  запись)
 id
----
   2 
( 1  запись)

INSERT  0   1 
seb=> select * from table1;
 id |  login
----+----------
   1  | newlogin
   2  | newlogin
( 2  rows)

так понятней ?
...
Рейтинг: 0 / 0
16.10.2008, 19:26
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599527
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
Уважаемый, прислушайтесь что Вам говорят.
Предложили три варианта:
1) Ёш: блокировать - проверять
2) Sad Spirit: Создать таблицу с уникальным ключем и сделать вьишки и соотвествующими правила (чтобы приложению было прозрачно)
3) Я (Gold_) :) [естественно самая правильная в Вашем случае ;)]. Создать таблицу с уникальным ключем и заполнять, редактировать и удалать записи в ней соотвествующими триггерами повешанными на Ваши три таблицы
...
Рейтинг: 0 / 0
16.10.2008, 23:10
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599607
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
Gold_Создать таблицу с уникальным ключем и заполнять, редактировать и удалать записи в ней соотвествующими триггерами повешанными на Ваши три таблицыну тоже имхо не идеальный вариант...%) unique блокируется не всегда, а только когда есть реальный конфликт уникальности, и если вставлять значения в разном порядке - нужно быть готовым к deadlock, например:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
create table a(i int);
create table b(i int);
create table u(i int unique);

create or replace function u_ins() returns trigger language plpgsql
as $$begin
insert into u (i) values (new.i); return new;
end$$;

( 1 ) seb=> begin;
BEGIN
seb=> insert into a values ( 0 ); --- нет блокировки, в u unique 0
INSERT  0   1 
        ( 2 ) seb=> begin;
        BEGIN
        seb=> insert into b values ( 1 ); --- нет блокировки, в u unique 1
seb=> insert into a values ( 1 ); --- блокировка, 1 уже в unique из транзакции (2)
        seb=> insert into b values ( 0 ); --- блокировка и обнаружение deadlock'а, 0 уже в unique из транзакции (1)
        ERROR:  обнаружена бесконечная блокировка (deadlock)
        DETAIL:  Process  6066  waits for ShareLock on transaction  28868 ; blocked by process  5982 .
        Process  5982  waits for ShareLock on transaction  28869 ; blocked by process  6066 .
        CONTEXT:  SQL-команда: "INSERT INTO u (i) values ( $1 )"
        PL/pgSQL function "u_ins" line  1  at SQL statement
...
Рейтинг: 0 / 0
16.10.2008, 23:59
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35599611
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
угу, забыл дописать в пример создание собственно тригера %)
Код: plaintext
1.
2.
create trigger u_ins before insert on a for each row execute procedure u_ins();
create trigger u_ins before insert on b for each row execute procedure u_ins();


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
17.10.2008, 00:02
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35600652
Gold_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gold_
Гость
Ёшну тоже имхо не идеальный вариант...
Видимо из трех предложенных наихудший
...
Рейтинг: 0 / 0
17.10.2008, 12:57
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35601106
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
Gold_Ёшну тоже имхо не идеальный вариант...
Видимо из трех предложенных наихудшийвторой вариант (Sad Spirit'а) имеет ту же самую "проблему" :) просто так себя unique ведёт.
неважно как в него вставлять, через тригеры из трёх таблиц или напрямую - будет та же ситуация. видимо это плата за возможность неблокироваться если значения разные :)

просто надо посмотреть - насколько часто параллельные транзакции одновременно вставляют несколько одинаковых значений, причём в разном порядке.
в данном конкретном случае редактирования пользователей особенно если изменяется одна запись и это происходит быстро - такое имхо маловероятно и можно спокойно пользоваться unique :)
...
Рейтинг: 0 / 0
17.10.2008, 14:44
| Ответить | Цитировать | Написать  
проверка на уникальность
    #35601350
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Ёшпросто так себя unique ведёт.
неважно как в него вставлять, через тригеры из трёх таблиц или напрямую - будет та же ситуация. видимо это плата за возможность неблокироваться если значения разные :)

просто надо посмотреть - насколько часто параллельные транзакции одновременно вставляют несколько одинаковых значений, причём в разном порядке.это получается задача разработчика приложения - можно например сначала отсортировать готовые для вставки записи по уникальному полю, а потом вставлять их в этом порядке.
...
Рейтинг: 0 / 0
17.10.2008, 15:49
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / проверка на уникальность
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]