Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3 4 5      [все]
 Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
На правах задачки, предложенной Stax:

можно ли реализовать constraint по колонке вручную из триггера.
Вроде можно. Я использовал вспомогательную таблицу для симуляции dirty reads, и SCN для фильтра uncommited изменений.
Пример только для insert, update обрабатывается аналогично.


+
create table TEST23(uq INTEGER); -- target table
create table TEST_U(id INTEGER); -- helper table

create or replace trigger tr_test23
  before insert on test23  
  for each row
declare
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
  l integer := -1;
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- check among commited values
  select count(*) into n from dual
   where exists (select 1 from test23 where uq = :new.uq);

  if n > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- take lock to avoid TOCTOU
  l := dbms_lock.request( 1001234567, dbms_lock.X_MODE );
  if l != 0 then raise TIMEOUT_ON_RESOURCE; end if;

  -- clean helper table from rows older than commited data
  delete from test_u u 
   where u.ora_rowscn <= nvl((select max(t.ora_rowscn) from test23 t),'Inf');
  commit;
   
  -- count matching uncommited values from other sessions
  select count(*) into n from test_u u where u.id = :new.uq;

  if n > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- this will remember uncommited value from this session
  insert into test_u (id) values(:new.uq);
  commit;

  l := dbms_lock.release( 1001234567 );
exception
  when others then
    if l = 0 then l := dbms_lock.release( 1001234567 ); end if;
    raise;
end tr_test23;
13 ноя 20, 21:14    [22231730]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
K790
Member

Откуда:
Сообщений: 633
жесть какая-то. смешались в кучу кони, scn и ...
13 ноя 20, 21:32    [22231735]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Заставь дурака богу молиться....
13 ноя 20, 21:51    [22231743]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
Трава в "Маями" зачетная.
13 ноя 20, 21:54    [22231750]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL

можно ли реализовать constraint по колонке вручную из триггера.
Вроде можно. Я использовал вспомогательную таблицу для симуляции dirty reads, и SCN для фильтра uncommited изменений.
Пример только для insert, update обрабатывается аналогично.


Ты-бы тестировал свои ваяния:

SQL> insert into test23 select 1 from dual;
insert into test23 select 1 from dual
            *
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "SCOTT.TR_TEST23", line 36
ORA-06512: at "SCOTT.TR_TEST23", line 19
ORA-04088: error during execution of trigger 'SCOTT.TR_TEST23'


SQL>


  delete from test_u u 
   where u.ora_rowscn <= nvl((select max(t.ora_rowscn) from test23 t),'Inf'); -- number vs varchar2


SY.
13 ноя 20, 21:56    [22231754]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY,

Торопился с последним изменением, допустил ошибку. Замените на гугол, SCN точно меньше гугола.

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

О...

Или это слишком строгое условие уникальности? Тогда ещё проще.
14 ноя 20, 04:32    [22231884]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Красава, че

Только вот наличие в целевой табличке надо проверять тоже уже после получения блокировки

А также обязательно не забыть выполнять всю эту возню и при UPDATE и при DELETE (проверил ты что запись существует, а она уже удалена, просто не закомичена, обычный ключ бы ждал завершения этой транзакции)

Ну и использование явного номера блокировки -- не есть хорошо, Oracle может сам захотеть использовать этот номер. Чтоб его зарегистрировать за определенным именем необходимо выполнить DBMS_LOCK.ALLOCATE_UNIQUE. но это также вызывает COMMIT -- это тоже надо учитывать

Про производительность вообще молчу
14 ноя 20, 08:05    [22231910]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Красава, че
Неужели ты стал ценить слащавость речей, а не профессионализм? Оно даже не удосужилось протестировать своё %ё&ище...
14 ноя 20, 10:00    [22231919]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Oracle может сам захотеть использовать этот номер.
С какой стати?
14 ноя 20, 10:06    [22231920]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
Красава, че
Неужели ты стал ценить слащавость речей, а не профессионализм? Оно даже не удосужилось протестировать своё %ё&ище...
То, что с профессионализмом пока плохо -- согласен, но это дело наживное...
То, что не хочет читать доку -- это не совсем так, ссылки-то он на доку дает
То, что он пытается перенести аналогии с какого-нибудь 86 ассемблера -- ну оно может и не правильно, но ненаказуемо
А вот то, что чел реально изучает новые вещи (тот же DBMS_LOCK) хотя бы просто для того чтоб натянуть нос его пнувшим -- уже достойно уважения
14 ноя 20, 10:16    [22231922]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
Oracle может сам захотеть использовать этот номер.
С какой стати?
Почему бы и нет?
Вызов DBMS_LOCK.ALLOCATE_UNIQUE будет проверять зарегистрирован ли этот lock_id для какого-нибудь имени (из таблички sys.dbms_lock_allocated), и если нет вполне может его прихватизировать
14 ноя 20, 10:20    [22231924]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Почему бы и нет?
Rem     Lockids from 2000000000 to 2147483647 are reserved for products
Rem     supplied by Oracle:
14 ноя 20, 10:30    [22231930]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
А вот то, что чел реально изучает новые вещи (тот же DBMS_LOCK) хотя бы просто для того чтоб натянуть нос его пнувшим -- уже дос
Совать что ни попадя куда не попадя чревато несчастными случаями.
В психиатрии таких изолируют и не дискутируют, чтобы не ухудшить их самочувствие.
14 ноя 20, 10:37    [22231937]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
Почему бы и нет?
Rem     Lockids from 2000000000 to 2147483647 are reserved for products
Rem     supplied by Oracle:
Ну, вообще-то я имел ввиду именно диапазон пользовательских блокировок
автор
96.6.1 ALLOCATE_UNIQUE Procedure
This procedure allocates a unique lock identifier (in the range of 1073741824 to 1999999999) a specified lock name.

Но у него еще хуже: 1001234567
Насколько понимаю, это вполне может пересечься с обычными (DML) блокировками, что вообще плохо

Сообщение было отредактировано: 14 ноя 20, 10:42
14 ноя 20, 10:46    [22231942]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
А вот то, что чел реально изучает новые вещи (тот же DBMS_LOCK) хотя бы просто для того чтоб натянуть нос его пнувшим -- уже дос
Совать что ни попадя куда не попадя чревато несчастными случаями.
В психиатрии таких изолируют и не дискутируют, чтобы не ухудшить их самочувствие.
"А что будет если?"
Не верю, что ты так никогда не делал
Просто вменяемые люди это в продакшн не пускают, но как тренировка -- вполне себе помогает в изучении нового
Дык вроде неофит и не говорит, что это надо пускать в продакшн -- просто говорит, что неплохо бы ширше смотреть на вещи, тем более, которые уже много раз поменялись с новыми версиями
14 ноя 20, 10:51    [22231943]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Vadim Lejnin
Member

Откуда:
Сообщений: 7131
Вячеслав Любомудров
Elic
пропущено...
Совать что ни попадя куда не попадя чревато несчастными случаями.
В психиатрии таких изолируют и не дискутируют, чтобы не ухудшить их самочувствие.
"А что будет если?"
Не верю, что ты так никогда не делал
Просто вменяемые люди это в продакшн не пускают, но как тренировка -- вполне себе помогает в изучении нового
Дык вроде неофит и не говорит, что это надо пускать в продакшн -- просто говорит, что неплохо бы ширше смотреть на вещи, тем более, которые уже много раз поменялись с новыми версиями


Вячеслав, в целом согласен, но он к сожалению еще и новичкам советует...
А вот это уже ни в какие ворота не лезет.
14 ноя 20, 11:07    [22231946]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Согласен
Но, например, ответ в Разбиение одного запроса с JOIN на несколько волне себе адекватный (точнее, что пришло на вход, то и получайте на выходе)
С отсылкой к RTFM, прошу заметить
14 ноя 20, 11:10    [22231948]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Насколько понимаю, это вполне может пересечься с обычными (DML) блокировками
Вячеслав, ты о чём?! Они в принципе непересекаемы.
14 ноя 20, 11:56    [22231969]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Согласен
Но, например, ответ в
Редкие проблески сознания не изменяют картины в целом.
14 ноя 20, 11:57    [22231970]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2788
НеофитSQL
На правах задачки, предложенной Stax:


без блокирования ресурса (а так можно вообще лок табле)

.....
stax
14 ноя 20, 12:03    [22231972]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
"А что будет если?"
Не верю, что ты так никогда не делал
Может ты и забыл, но я всегда старался приводить легко воспроизводимые доказывающие test-case-ы.
У пациента же, в основном, поток сознания.

Ещё раз: не стоит тратить время, чтобы с ним спорить. Это типичная цель троллей - сожрать уйму чужого в пустую потраченного времени.
14 ноя 20, 12:08    [22231973]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
натянуть нос его пнувшим
Кстати. Про пнувших. Ты следил, кто это? - Станислав только ухудшает ситуацию.
14 ноя 20, 12:11    [22231975]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Дык я вроде и не спорю
Но запинывать под шконку молодого и (возможно) перспективного чела, который хоть и хочет все попробовать, но, кстати, весьма адекватно реагирует на критику, это же тоже неправильный подход

А как еще молодым изучать Oracle Database?
Понятное дело -- в первую очередь курс по Концепциям и далее в зависимости от направления -- Administration или Development
Потом, как мне кажется, для обоих очень желательно прочтение двутомника Кайта (пусть он и устарел)
Далее просто знать где искать ответы в документации

Это я так вижу (у меня это насущная тема, собираюсь увольняться, а замены так за 20 лет и не нарисовалось, вот и пытаюсь сформулировать требования к претендентам)

Ну дык у неофита все еще впереди, а что огрызается, так мы тоже не ангелы
14 ноя 20, 12:22    [22231984]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
натянуть нос его пнувшим
Кстати. Про пнувших. Ты следил, кто это? - Станислав только ухудшает ситуацию.
Он в своем праве
И просто не ставит так жестко вопрос: или-или
Или ты настоящий ораклист или заткнись и сопи в тряпочку
14 ноя 20, 12:24    [22231986]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
перспективного чела
Ты сам, лично пробовал критически оценивать ту пургу? Или доверяешься нейро-программистским способностям персонажа? "Типо" потому что больше некому?

Сообщение было отредактировано: 14 ноя 20, 12:34
14 ноя 20, 12:38    [22231995]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Но ведь так можно сразу посылать любого?

Мне дети говорят -- я бы может и устроился на такую работу, но староперды реально гнобят за незнание чего-то там
И я вместе с ними негодую
Хотя реально такой же староперд и с такими же претензиями

Я к тому, что нужно и критиковать, но и обсуждать решения, а не сразу их отметать
14 ноя 20, 13:03    [22232007]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Но ведь так можно сразу посылать любого?
Нет. Ответь сам себе на вопрос: что-то новое внесено в "базу знаний форума"? Или чудак (если он не тролль) тут решил расплескать свой личный блог в духе "О-о-о... Какая крутая бабочка!" ?
14 ноя 20, 13:23    [22232017]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
Вячеслав Любомудров
но и обсуждать решения

Зачем обсуждать решения человека который не желает учиться и вместо того чтобы сначала изучить, понять и осознать, лепит ахинею и вываливает ее на форум?
14 ноя 20, 13:23    [22232018]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Я к тому, что нужно и критиковать, но и обсуждать решения, а не сразу их отметать
А можно входить в форум адекватно. Вспомни, например, Никотина. Про Саяна я вообще не говорю.
14 ноя 20, 13:25    [22232020]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Basil A. Sidorov
Member

Откуда:
Сообщений: 10760
Вячеслав Любомудров
но староперды реально гнобят за незнание чего-то там
Вот прямо сразу в лоб гнобят или после того, как юный талант с тонкой душевной организацией начал хлопать глазами и хлюпать носом вместо "копать отсюда и до решения"?

P.S.
Никогда не обращали внимание на "военную" терминологию и риторику бизнеса?
Это, конечно плохо: командир своей жизнью отвечает за жизнь подчинённых, а у бизнеса такой ответственности - нет.
Но, по факту, бизнес это война и не очень понятно, почему надо обхаживать какого-нибудь цатилетнего лба, который ведёт себя как дошколёнок.
14 ноя 20, 13:29    [22232023]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Elic
Вячеслав Любомудров
Но ведь так можно сразу посылать любого?
Нет. Ответь сам себе на вопрос: что-то новое внесено в "базу знаний форума"? Или чудак (если он не тролль) тут решил расплескать свой личный блог в духе "О-о-о... Какая крутая бабочка!" ?
Он не первый, не последний
В его плесканиях я вижу адекватную реакцию на критику и таки чтение (и, отправление к) доке
Это уже дорогого стоит

Да блин, я точно также буду гнобить за кривую идею, построенную на неправильных предпосылках
Или не буду

Кстати, тот же Саян, как мне кажется, именно через наш форум, решил куда ему дальше расти и этот форум был очень неплохим учебником/общением, и его тут тоже за глупости неплохо ругали
И много их таких...
И, возможно, скоро именно неофит будет отвечать в команде дядюшки Тома
14 ноя 20, 13:51    [22232030]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
Кстати, тот же Саян,
Попытка сравнения оскорбительна, как по мне.
Лично я никого толкового никогда не гнобил, только направлял. Надеюсь (и не безпочвенно), это имело определённый успех.
14 ноя 20, 14:04    [22232037]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Дык я тоже на такое надеюсь
К сожалению, не все оценили
14 ноя 20, 14:10    [22232039]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
Виталий, я ведь тоже начинаю ругаться, когда приходят неофиты и начинают шатать основы
Более того, ругаюсь, когда они не просто свои фантазии реализуют, а начинают отвечать, типо это правильно
Но ведь от них никуда не деться, кто-то же должен (прийти и подхватить знамя) продолжать работать
Очень обидно, когда это знамя подхватывают просто неучи
Еще обидней, когда они в силу возраста/тупизны проталкивают вот эти свои совершенно глупые (но, кажущиеся им самыми умными) идеи

Как по мне -- этот топик как раз доведение до абсурда, но это задание на соображулесть -- и автор вполне его прошел -- так что 5- по моей скромной оценке
14 ноя 20, 14:23    [22232045]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL

Замените на гугол, SCN точно меньше гугола.


Ну заменил:

+

SQL> create table TEST23(uq INTEGER);

Table created.

SQL> create table TEST_U(id INTEGER);

Table created.

SQL>
SQL> create or replace trigger tr_test23
  2    before insert on test23
  3    for each row
  4  declare
  5    PRAGMA AUTONOMOUS_TRANSACTION;
  6    n integer;
  7    l integer := -1;
  8  begin
  9    if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL
 10
 11    -- check among commited values
 12    select count(*) into n from dual
 13     where exists (select 1 from test23 where uq = :new.uq);
 14
 15    if n > 0 then raise DUP_VAL_ON_INDEX; end if;
 16
 17    -- take lock to avoid TOCTOU
 18    l := dbms_lock.request( 1001234567, dbms_lock.X_MODE );
 19    if l != 0 then raise TIMEOUT_ON_RESOURCE; end if;
 20
 21    -- clean helper table from rows older than commited data
 22    delete from test_u u
 23     where u.ora_rowscn <= nvl((select max(t.ora_rowscn) from test23 t),power(10,100));
 24    commit;
 25
 26    -- count matching uncommited values from other sessions
 27    select count(*) into n from test_u u where u.id = :new.uq;
 28
 29    if n > 0 then raise DUP_VAL_ON_INDEX; end if;
 30
 31    -- this will remember uncommited value from this session
 32    insert into test_u (id) values(:new.uq);
 33    commit;
 34
 35    l := dbms_lock.release( 1001234567 );
 36  exception
 37    when others then
 38      if l = 0 then l := dbms_lock.release( 1001234567 ); end if;
 39      raise;
 40  end tr_test23;
 41  /

Trigger created.

SQL>



SQL> insert into test23 values(1);

1 row created.

SQL> insert into test23 values(1);

1 row created.

SQL> insert into test23 values(1);

1 row created.

SQL>


Доку по традиции не читаем (что видит и что не видит autonomous transaction).

SY.

Сообщение было отредактировано: 14 ноя 20, 14:37
14 ноя 20, 14:34    [22232047]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
Вячеслав Любомудров
так что 5- по моей скромной оценке
Заболталенг ему тобой зачтён. И всего то.
14 ноя 20, 14:47    [22232052]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 881
Вячеслав Любомудров
начинаю ругаться, когда приходят неофиты и начинают шатать основы

хз. мне именно этим и интересны посты неофита с неконвенциональными подходами. уж точно интереснее и полезнее тысяч комментариев формата "ну тупоооой", которых даже в этом треде полно.

я вообще не понимаю, что такое "расшатать основы". я либо в состоянии на пальцах и с примерами объяснить, почему что-то делать не надо, либо, если я не в состоянии, то это всегда повод спросить себя - а так ли я прав?
14 ноя 20, 14:58    [22232059]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
кит северных морей,

Неофит отказывается изучать и понимать многопользовательский доступ к одному ресурсу, ему почему то кажется, что если он напишет много бредового кода, то законы природы перестанут действовать.
14 ноя 20, 15:08    [22232063]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Elic
Member

Откуда:
Сообщений: 29980
кит северных морей
уж точно интереснее и полезнее тысяч комментариев формата "ну тупоооой"
Так в чём польза то? Про интерес не спрашиваю: попкорн - он везде попкорн.
14 ноя 20, 15:09    [22232064]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 881
Elic
Так в чём польза то?
я не знаю как складывалась ваша карьера. моя складывалась (и продолжает складываться) так, что большинству того, что я умею, меня никто специально не учил - я научился сам. читал книжки, статьи, пытался как-то осмыслить увдиенное у коллег, или просто тыкался по интернету как слепой котенок, если знаний совсем не хватало.

понятие "основы" за это время трансформировалось неоднократно. например, когда-то давно одной из "основ" для меня было мнение, что ссылочная целостность данных должна обеспечиваться декларативными ограничениями, и никак иначе. потом я ляпнул это на каком-то интервью, а мне в ответ сказали, что у них в системе вообще ни одного FK нет. разрыв шаблона, основа пошатнулась, и со временем сдвинулась в более правильном направлении.

соответственно такие, кажущиеся неконвенциальными мнения - это всегда reality check. точно ли мнение неконвенциальное, или может просто это ты сам дурак? и польза как раз в том, что дураком время от времени оказываешься ты сам.

наверное, когда вам сначала системно кладут знания в голову в правильном порядке, а потом вы начинаете их применять - это не так критично. но это, как я сказал, не мой случай.
14 ноя 20, 15:56    [22232074]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY,

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

Извините, что потратил ваше время, надеюсь, copy/paste много не отнял.
Если есть желание, проставьте 0 в nvl, или просто сделайте первый коммит чтобы сделать таблицу непустой, потом все должно работать штатно:

SQL> insert into test23 values(1);
1 row inserted

SQL> commit;
Commit complete

SQL> insert into test23 values(1);
insert into test23 values(1)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TR_TEST23", line 36
ORA-04088: error during execution of trigger 'TR_TEST23'

SQL> insert into test23 values(2);
1 row inserted

SQL> insert into test23 values(2);
insert into test23 values(2)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TR_TEST23", line 36
ORA-04088: error during execution of trigger 'TR_TEST23'


С нулем, первый коммит не нужен:

+
SQL> delete test23;
1 row deleted

SQL> delete test_u;
1 row deleted

SQL> commit; -- empty tables now
Commit complete

SQL> insert into test23 values(1);
1 row inserted

SQL> insert into test23 values(1);
insert into test23 values(1)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TR_TEST23", line 36
ORA-04088: error during execution of trigger 'TR_TEST23'
15 ноя 20, 01:04    [22232235]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Вячеслав Любомудров
Красава, че


Спасибо :)

> Только вот наличие в целевой табличке надо проверять тоже уже после получения блокировки
Действительно. Я строил constraint только для вставки, как proof of concept.
Мне было интересно посмотреть, с какими сложностями может столкнуться программист при создании constraint по колонке, и возможно ли это вообще.

> А также обязательно не забыть выполнять всю эту возню и при UPDATE и при DELETE
Конечно, это было оговорено.

> Ну и использование явного номера блокировки -- не есть хорошо
proof of concept

>Про производительность вообще молчу
proof of concept

Я поначалу рассматривал возможность использования структур в памяти (collections or bitmask), но пока не разобрался можно ли их создавать в глобальном контексте.
15 ноя 20, 01:12    [22232238]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
кит северных морей,

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


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


Давайте прекратим обсуждение ТС, и посмотрим на суть вопроса: возможно ли средствами Оракла реализовать unique constraint на таблице через триггеры?

Я столкнулся с несколькими проблемами, но решил задачу для insert. Похожим образом она решается для delete, update.

Мне видится, что задача решаема. Для уникальности естественно это упражнение не слишком полезно, т.к. есть встроенные средства.
Для какого-нибудь экзотического constraint по колонке (напр., расстояние Левенштайна между любых двух строк не может быть менее N) мой метод позволяет построить прототип.

Код опубликован, известные ошибки исправлены. Меньше страницы, можно прочитать и найти ошибку.
Попробуйте, серыйкод, обмануть мой триггер как это сделал SY для ранней версии.

А потом подумайте, почему мой интерес в возможности или невозможности задачи вам причиняет неудобство.
15 ноя 20, 01:29    [22232241]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 881
НеофитSQL
Для какого-нибудь экзотического constraint по колонке (напр., расстояние Левенштайна между любых двух строк не может быть менее N) мой метод позволяет построить прототип.
обычно проверки со сложной бизнес-логикой реализуются либо как часть API для модификации данных, либо как отдельный процесс data quality. делать это на триггерах интересно в качестве упражнения, но для продуктива много вариантов проще и надёжнее.
15 ноя 20, 07:35    [22232262]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL
SY,
Там нужен не Inf, не гугол а ноль.


Там просто не нужен NVL. Ну а теперь попробуй TRUNCATE TEST23:

SQL> insert into test23 values(1);

1 row created.

SQL> truncate table test23;

Table truncated.

SQL> insert into test23 values(1);
insert into test23 values(1)
            *
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "SCOTT.TR_TEST23", line 37
ORA-06512: at "SCOTT.TR_TEST23", line 13
ORA-04088: error during execution of trigger 'SCOTT.TR_TEST23'


SQL>


Но суть не в этом а в том что ты убиваешь транзакционность. При уникальном ключе сессия 2 нарвавшаяся на еще незакоммиченный дубль сессии 1 висит до тех пор пока сессия 1 не выполнит либо commit - тогда сессия 2 получит ORA-00001, либо rollback - тогда insert/update сессии 2 прекрасно завершится. Ты-же бежишь впереди паровоза и выставляешь сессии 2 ORA-00001 когда сессия 1 еще не завершила транзакцию:

Cессия 1:

SQL> insert into test23 values(1);

1 row created.

SQL>


Cессия 2:

SQL> insert into test23 values(1);
insert into test23 values(1)
            *
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "SCOTT.TR_TEST23", line 37
ORA-06512: at "SCOTT.TR_TEST23", line 13
ORA-04088: error during execution of trigger 'SCOTT.TR_TEST23'


SQL>


SY.
15 ноя 20, 15:01    [22232362]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
Для какого-нибудь экзотического constraint по колонке
Открой для себя user DOMAIN INDEXES.

Любые попытки извернуться через триггер заранее обречены на провал.
Подумай про более чем одного пользователя и про то когда срабатывает триггер - при окончании транзакции или немного раньше.
Всё кроме полной сериализаци нерабочее, а сериализация - бессмысленна.
15 ноя 20, 15:56    [22232383]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL,

  -- check among commited values
  select count(*) into n from dual
   where exists (select 1 from test23 where uq = :new.uq);

Во время выполнения этого запроса, другая сессия выполняет фиксацию транзакции, например ты проверяешь uq = 100500 и другая сессия в этот момент зафиксировала вставку с таким же uq, как ты думаешь, что произойдет дальше?
15 ноя 20, 17:41    [22232408]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Конструктивные ответы. Я подумаю ещё.

Кабанчег, "обречены на провал" у вас означает невыполнимо в принципе, или "нет смысла, потому что встроенные лучше?"

Про второе я не сомневаюсь. Про первое я пока не уверен, хочу разобраться.

SY: про trunc понял,я начал с реализации только insert, другие операции пока не разрешены. Если невозможно сделать insert, то невозможно вообще.
15 ноя 20, 17:58    [22232410]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL,

  -- check among commited values
  select count(*) into n from dual
   where exists (select 1 from test23 where uq = :new.uq);

Во время выполнения этого запроса, другая сессия выполняет фиксацию транзакции, например ты проверяешь uq = 100500 и другая сессия в этот момент зафиксировала вставку с таким же uq, как ты думаешь, что произойдет дальше?


Я думаю что вернёт 0 (зафиксировала это post?), дальше увидит значение во вспомогательной таблице и даст ошибку дупликата.

Кто-то уже посоветовал перенести эту проверку в критическую секцию. Как окончательно проснусь,обновлю код триггера, чтоб не обсуждать ошибки старых версий.
15 ноя 20, 18:03    [22232413]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL

SY: про trunc понял,я начал с реализации только insert, другие операции пока не разрешены. Если невозможно сделать insert, то невозможно вообще.


Забудь про truncate. Реши транзакционность.

SY.
15 ноя 20, 18:06    [22232415]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
"обречены на провал" у вас означает
Если кратко - да.

Но стоит уточнить что именно пытаешься сымитировать.

Цель недопустить двух одинаковых ключей в таблице или сэмулировать поведение с уникальным индексом, то есть
1. При наличии закомиченой записи* - exception
2. При наличии незакомиченной записи* - висим на блокировке до тех пор пока
2.1 commit - получаем exception
2.2 rollback - отлипаем
2.3 ключ изменен или удалён - отлипаем
3. При полном отсутствии записи* идём дальше

?

* - понимается [с тем же ключом]

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

Сообщение было отредактировано: 15 ноя 20, 19:31
15 ноя 20, 19:35    [22232441]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY:

я перестарался с выявлением дупликатов во время post, емулируя dirty reads. Без этого код должен стать проще.

Кобанчег,

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

В этих ситуациях имеет смысл говорить об уменьшении кода в критической секции (или другом механизме синхронизации доступа).
Даже сиквенс имеет сериализованный код, это не значет что сиквенсы плохие или медленные.
15 ноя 20, 22:28    [22232518]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег

Цель недопустить двух одинаковых ключей в таблице или сэмулировать поведение с уникальным индексом


Цель - второе. Была высказана гипотеза что это невозможно, но доказать невозможность с моим уровнем знаний довольно трудно, поэтому я работаю над контрпримером, заодно обучаясь тонкостям многосессионности.
15 ноя 20, 22:32    [22232520]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
предпринимаю новую попытку реализовать subj максимально приближенный по поведению к pk_key.

Оговорки: пока работает только для операции insert. Полагается на строковый лок через update. Может содержать ошибки.
Я протестировал из двух разных сессий commits и rollbacks. Вроде работает.

create table TEST23(  uq INTEGER ); -- это таблица с триггером, реализующим constraint
create table TEST_U(  id  INTEGER not null,  val INTEGER default 0 not null ); -- вспомогательная

-- вспомогательная функция, которая вставляет во вспомогательную таблицу ID и коммитит ее, 
-- чтобы показать всем сессиям. Функция должна быть атомарная (атомическая?), DBMS лок не показан
create or replace procedure CommitForAll(uq in integer) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  n integer;
begin
  select id into n from test_u where id = uq;
exception
  when NO_DATA_FOUND then 
    insert into test_u values (uq,0);
    commit write nowait batch;
end CommitForAll;

-- собственно триггер. эмуляция поведения при вставлении дупликата реализована через update.
-- поскольку update после освобождения строчного лока не вылетает, а срабатывает,
-- приходится повторно проверять главную таблицу на предмет закоммиченных UQ
create or replace trigger tr_test23
  before insert on test23  
  for each row
begin
  if :new.uq is null then raise VALUE_ERROR; end if; -- better ORA-01400: cannot insert NULL

  -- remember the posted id for other sessions
  CommitForAll( :new.uq );

  -- first, check among session-visible data (commited +posted in this session)
  for t in (select * from test23 where uq = :new.uq)
  loop
    raise DUP_VAL_ON_INDEX;
  end loop;

  -- try causing row-level lock in helper table of uncommitted inserts
  update test_u u
     set val = u.val+1
   where u.id = :new.uq;

  -- if updated, lock worked; see if uq now among committed
  if SQL%ROWCOUNT > 0 then
    for r in (select * from test23 where uq = :new.uq)
    loop
      raise DUP_VAL_ON_INDEX;
    end loop;
  end if;
end tr_test23;
16 ноя 20, 02:14    [22232564]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
Про транзакционность и блокировки следует узнавать из документации или на худой конец из книг а не от всяких кабанов с форумов.
Ну о том, что надо сначала читать, потом переваривать и думать и только потом выкладывать свой бред на потеху публике высказалось уже множество людей.
НеофитSQL
Может содержать ошибки.
Это одна сплошная ошибка.
SQL> insert into test23 select 0 from dual;
insert into test23 select 0 from dual
            *
ERROR at line 1:
ORA-04091: table TEST.TEST23 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'
16 ноя 20, 07:08    [22232572]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег,


У меня почему-то работало.
Для "before" триггера таблица не должна мутировать.

Завтра разберусь.
16 ноя 20, 08:29    [22232590]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
НеофитSQL,
применение dbms_lock по сути не отличается от Вашего исходного варианта с unique constraint на дополнительной таблице, поскольку примитивом синхронизации остается PK на доп. таблице:
+

select referenced_owner, referenced_name
  from dba_dependencies
 where (owner, name, referenced_type) = any(('SYS', 'DBMS_LOCK', 'TABLE'))
;
 
REFERENCED_OWNER          REFERENCED_NAME
------------------------- -------------------------
SYS                       DUAL
SYS                       DBMS_LOCK_ALLOCATED

select constraint_type, constraint_name, table_name
  from dba_constraints
 where (owner, table_name) = any(('SYS','DBMS_LOCK_ALLOCATED'))
;
 
CONSTRAINT_TYPE CONSTRAINT_NAME           TABLE_NAME
--------------- ------------------------- -------------------------
P               SYS_C004841               DBMS_LOCK_ALLOCATED
U               SYS_C004842               DBMS_LOCK_ALLOCATED
 
SQL> 
16 ноя 20, 14:13    [22232919]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
andrey_anonymous,

Я не использую DBMS lock в последнем варианте кода.

> применение dbms_lock по сути не отличается от Вашего исходного варианта с unique constraint на дополнительной таблице, поскольку примитивом синхронизации остается PK на доп. таблице:

Такое можно было бы сказать, если бы я использовал DBMS_LOCK на каждую строчку, но до такого я не додумался :)

Использование одного уникального объекта схемы не приравнивается к использованию свойств PK.
16 ноя 20, 18:41    [22233223]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег
НеофитSQL
С улучшенным пониманием как работаet встроенный constraint уникальности (спасибо, Кабанчег, за серый текст!)
Про транзакционность и блокировки следует узнавать из документации или на худой конец из книг а не от всяких кабанов с форумов.
Ну о том, что надо сначала читать, потом переваривать и думать и только потом выкладывать свой бред на потеху публике высказалось уже множество людей.
НеофитSQL
Может содержать ошибки.
Это одна сплошная ошибка.
SQL> insert into test23 select 0 from dual;
insert into test23 select 0 from dual
            *
ERROR at line 1:
ORA-04091: table TEST.TEST23 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-06512: at "TEST.TR_TEST23", line 8
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'


Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?
Решение известно, например compound trigger. На время, сужаю применимость моего решения для одиночных insert.
(про delete, update, truncate было сказано в начале).
Можно ли считать мое решение неотличимым от PK в поведении для одиночных insert?
16 ноя 20, 18:57    [22233238]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL

Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?


INSERT/UPDATE/DELETE - атомарная операция независимо от числа вставляемых/изменяемых/удаляемых строк.
Mutating - это когда состояние таблицы когда уже не до INSERT/UPDATE/DELETE но еще не после. А вот INSERT VALUES это тот единственный случай когда ORACLE точно знает - вставка одной строки и посему BEFORE EACH ROW это точно до INSERT.

SY.
16 ноя 20, 19:52    [22233308]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.

SY.
16 ноя 20, 20:17    [22233320]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY
НеофитSQL

Да, я вижу то же самое у себя. Тесты делал с "insert into test32 values(n)", ошибок не было.
Различие в поведении между двумя способами доступа мне непонятно - Оракл особо обрабатывает однострочный insert?


INSERT/UPDATE/DELETE - атомарная операция независимо от числа вставляемых/изменяемых/удаляемых строк.
Mutating - это когда состояние таблицы когда уже не до INSERT/UPDATE/DELETE но еще не после. А вот INSERT VALUES это тот единственный случай когда ORACLE точно знает - вставка одной строки и посему BEFORE EACH ROW это точно до INSERT.
SY.


В случае "insert .. select 0 from dual" оракл тоже точно знает что одна строка, но не использует это знание.

Получается, Оракл особым образом обрабатывает insert..values() не так, как вставку таблицы из одной строки.
Я такой технической детали не знал. Если такое поведение задокументировано, я теперь могу написать триггер который позволит вставлять строчки только через values(), и никаким другим способом. Но вряд ли пригодится :)

Интересно почему для values() Оракл не использовал общий код мультистрочной вставки. Сомневаюсь что для оптимизации скорости, однострочные вставки заведомо не помогают производительности. Возможно, внутри у Оракла есть системный триггер которому удобно читать из модифицируемой таблицы, и оракл сделал специальную обработку insert..values() чтобы помочь себе.
16 ноя 20, 20:26    [22233326]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.
SY.


Мне это уточнение непонятно.
Мой "before insert by row" триггер вылетал на строке которая только читала из таблицы триггера.

Вот максимально упрощенный пример:
SQL> create table test24 ( id integer );
Table created

SQL> create trigger tr_test24
  2  before insert on test24
  3  for each row
  4  declare
  5    n integer;
  6  begin
  7    select count(*) into n from test24;
  8  end;
  9  /
Trigger created

SQL> insert into test24 values(0);
1 row inserted

SQL> commit;
Commit complete

SQL> insert into test24 select 1 from dual;
insert into test24 select 1 from dual
ORA-04091: table TEST.TEST24 is mutating, trigger/function may not see it
ORA-06512: at "TEST.TR_TEST24", line 4
ORA-04088: error during execution of trigger 'TEST.TR_TEST24'
16 ноя 20, 20:36    [22233331]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Переделал свое решение чтобы позволить мультистрочные вставки.

Проверил из двух разных сессий, сессии помечены #1/#2.

---------------------------- session #1 ------------------------------
#1> insert into test23 select 1 from dual;
1 row inserted

#1> select * from test23;
                                     UQ
---------------------------------------
                                      1

---------------------------- session #2 ------------------------------
#2> insert into test23 select 2 from dual;

1 row created.

#2> select * from test23;

        UQ
----------
         2

#2> insert into test23 select 2 from dual;
insert into test23 select 2 from dual
            *
ERROR at line 1:
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 43
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'

#2> insert into test23 select 1 from dual;
--(sqlplus подвис)
---------------------------- session #1 ------------------------------
#1> rollback;
Rollback complete

#1> select * from test23;
                                     UQ
---------------------------------------

---------------------------- session #2 ------------------------------
#2 (sql+ проснулся, был блокирован на 'insert into test23 select 1 from dual;')

1 row created.

#2> select * from test23;

        UQ
----------
         2
         1
---------------------------- session #1 ------------------------------
#1> insert into test23 values(2);
(сессия 1 подвисла)
---------------------------- session #2 ------------------------------
#2> commit;

Commit complete.
---------------------------- session #1 ------------------------------
#1 (проснулась, висела на 'insert into test23 values(2);')
insert into test23 values(2)
ORA-00001: unique constraint (.) violated
ORA-06512: at "TEST.TR_TEST23", line 52
ORA-04088: error during execution of trigger 'TEST.TR_TEST23'
16 ноя 20, 21:01    [22233336]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
С мультистрочными insert пока не тестировал.
Просматриваю свой код на предмет race conditions (когда commit/rollback произойдет в другой сессии во время исполнения триггера, и сразу несколько ожидающих сессий разблокируются).

Проверю с двумя-тремя ожидающими сессиями, потом запощу код.
16 ноя 20, 21:26    [22233343]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
На время, сужаю применимость моего решения для одиночных insert.
Пора уже сузить применимость до нуля. :)

1. Может случится, что две параллельные сессии проверят что в test_u нет строк и каждая вставит по одной.
2. Нет никаких гарантий что вторая сессия (в которой инсерт запустили после первой) не захватит блокировку в test_u первой.

итд

Градус абсурда зашкаливает, я уже пожалел что влез в эту тему.
16 ноя 20, 21:40    [22233353]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег
НеофитSQL
На время, сужаю применимость моего решения для одиночных insert.
Пора уже сузить применимость до нуля. :)

1. Может случится, что две параллельные сессии проверят что в test_u нет строк и каждая вставит по одной.
2. Нет никаких гарантий что вторая сессия (в которой инсерт запустили после первой) не захватит блокировку в test_u первой.

итд

Градус абсурда зашкаливает, я уже пожалел что влез в эту тему.


1. не влияет на логику. test_u позволительно содержать дубликаты.
2. одинаково относится к PK, там нет гарантий очередности насколько мне известно.
16 ноя 20, 22:02    [22233368]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL
SY
Уточню. При условии BEFORE INSERT statement триггер таблицу не модифицировал - иначе и INSERT VALUES выдаст мутацию.
SY.


Мне это уточнение непонятно.


drop table test purge;
create table test(n number);
create or replace
  trigger test_trg
  for insert
  on test
  compound trigger
      v_cnt number;
  before statement
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before statement;
  before each row
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before each row;
end;
/
insert
  into test
  values(1)
/

1 row created.

create or replace
  package level_pkg
    is
        g_level number := 0;
end;
/
create or replace
  trigger test_trg
  for insert
  on test
  compound trigger
      v_cnt number;
  before statement
    is
      begin
          if level_pkg.g_level = 0
            then
            level_pkg.g_level := 1;
            insert
              into test
              select  rownum
                from  emp;
          end if;
  end before statement;
  before each row
    is
      begin
          select  count(*)
            into  v_cnt
            from test;
  end before each row;
end;
/
insert
  into test
  values(1)
/
  into test
       *
ERROR at line 2:
ORA-04091: table SCOTT.TEST is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TEST_TRG", line 18
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRG'
ORA-06512: at "SCOTT.TEST_TRG", line 9
ORA-04088: error during execution of trigger 'SCOTT.TEST_TRG'

SQL>


SY.
17 ноя 20, 00:44    [22233419]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY,

Спасибо за иллюстрацию!

В этом коде триггер пишет в таблицу, это выглядит невероятно взрывоопасно.

Я так понял глобальная переменная помогает избежать бесконечной рекурсии триггера,
но неужели кто-то такое пишет?
17 ноя 20, 02:32    [22233453]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
НеофитSQL
С мультистрочными insert пока не тестировал.
Просматриваю свой код на предмет race conditions (когда commit/rollback произойдет в другой сессии во время исполнения триггера, и сразу несколько ожидающих сессий разблокируются).

Проверю с двумя-тремя ожидающими сессиями, потом запощу код.


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

+
create or replace trigger tr_test23
  before insert on test23  
  for each row
declare
  procedure PrepLock is
    PRAGMA AUTONOMOUS_TRANSACTION;
    n integer;
  begin
    select id into n from test_u where id = :new.uq;
  exception
    when NO_DATA_FOUND then 
      insert into test_u values (:new.uq);
      commit write nowait batch;
  end PrepLock;

  function FindCommitted return integer is
    PRAGMA AUTONOMOUS_TRANSACTION;
  begin
    for t in (select count(*) n from dual where exists 
            (select null from test23 where uq = :new.uq))
    loop
      return t.n;
    end loop;
  end FindCommitted;
  
  function FindPosted return integer is
  begin
    for t in (select count(*) n from dual where exists 
            (select null from test_t where id = :new.uq))
    loop
      return t.n;
    end loop;
  end FindPosted;
  
  function WaitOnLock return integer is
    n integer;
  begin
    select 1 into n from test_u where id = :new.uq for update nowait;
    return 0; -- first lock, no wait
  exception
    when others then -- catching ORA-00054 resource busy and NOWAIT specified
      select 0 into n from test_u where id = :new.uq for update;
      return 1;
  end WaitOnLock;

begin
  -- get ready to use the row lock for this value
  PrepLock; -- :new.uq is implicit

  -- first, check among posted values in this session
  if FindPosted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- second, check among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- dequeue multiple sessions one by one
  while WaitOnLock != 0
  loop
    null;
  end loop;

  -- check again among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- count this id as posted within this session
  insert into test_t values ( :new.uq );
end tr_test23;



Пришлось в дополнение к вспомогательной таблице test_u, которая используется для row-level locks, завести GTT test_t, в которой находятся posted, not committed строчки из каждой сессии. Тело триггера стало напоминать псевдокод:

  -- get ready to use the row lock for this value
  PrepLock; -- :new.uq is implicit

  -- first, check among posted values in this session
  if FindPosted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- second, check among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- dequeue multiple sessions one by one
  while WaitOnLock != 0
  loop
    null;
  end loop;

  -- check again among commited values
  if FindCommitted > 0 then raise DUP_VAL_ON_INDEX; end if;

  -- count this id as posted within this session
  insert into test_t values ( :new.uq );
17 ноя 20, 02:44    [22233454]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
НеофитSQL
Пришлось

Но и это не помогло реализовать unique

SQL> create table dropme_t (uq integer) partition by range(uq) interval (100) (partition p0 values less than (10)) enable row movement;

Table created.

SQL> create table dropme_t2 (uq integer);

Table created.

SQL> alter table dropme_t2 add constraint dropme_t2_ux unique (uq);

Table altered.

SQL> insert into dropme_t select mod(level,17) from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> alter table dropme_t exchange partition p0 with table dropme_t2;
alter table dropme_t exchange partition p0 with table dropme_t2
*
ERROR at line 1:
ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> alter table dropme_t exchange partition p0 with table test23;

Table altered.

SQL> select uq, count(*) from test23 group by uq order by uq;

        UQ   COUNT(*)
---------- ----------
         0         58
         1         59
         2         59
         3         59
         4         59
         5         59
         6         59
         7         59
         8         59
         9         59

10 rows selected.
17 ноя 20, 09:48    [22233504]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6727
Реализация уникальности через триггер - идея заведомо обречённая на провал, т.к. есть операции при которых триггер не будет задействован.

Можно сделать, что вы и продемонстрировали, кучу кода для вырожденного случая, но не более того.
17 ноя 20, 09:53    [22233507]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL
SY,

Спасибо за иллюстрацию!

В этом коде триггер пишет в таблицу, это выглядит невероятно взрывоопасно.

Я так понял глобальная переменная помогает избежать бесконечной рекурсии триггера,
но неужели кто-то такое пишет?


За всю Одессу я не скажу. Мой пример просто иллюстрация что в принципе таки можно.

SY.
17 ноя 20, 13:36    [22233682]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
env
Реализация уникальности через триггер - идея заведомо обречённая на провал, т.к. есть операции при которых триггер не будет задействован.

Можно сделать, что вы и продемонстрировали, кучу кода для вырожденного случая, но не более того.


Я не сомневаюсь что самодельная реализация будет проигрывать встроенной, тут иллюзий нет.
Мой constraint не покажется в списке constraints, его можно отключить деактивацией триггера, со вспомогательными таблицами можно проводить манипуляции, и т.д.

Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.
Уникальность была выбрана, потому что это наипростейший из встроенных, и его поведение хорошо описано.

В данный момент я разбираюсь, в какой очередности select... for update отпускает сессии которые на нем застряли,
и есть ли способ узнать попал ли select.. for update на лок, или успешно отработал.
Мне не нравится моя конструкция с "nowait/retry", из-за возможных race conditions, а сериализировать эту часть кода нельзя.

 function WaitOnLock return integer is
    n integer;
  begin
    select 1 into n from test_u where id = :new.uq for update nowait;
    return 0; -- first lock, no wait
  exception
    when others then -- catching ORA-00054 resource busy and NOWAIT specified
      select 0 into n from test_u where id = :new.uq for update;
      return 1;
  end WaitOnLock;
17 ноя 20, 14:06    [22233722]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Из моих экспериментов, Оракл соблюдает очередность для select.. for update, и мой row-locking код значительно упростился.
Чтобы не утомлять публику частыми апдейтами, дам триггеру еще помариноваться.

Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?

Эффективно - значит без table lock или строгой сериализации доступа к таблице.
17 ноя 20, 14:15    [22233739]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL
Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.

Есть, инкапсулируешь логику работы с сущностью в пакете и не даешь прямого доступа к таблице. Триггеры, как ты уже убедился, не самый лучший способ реализации бизнес логики.
17 ноя 20, 14:40    [22233791]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
НеофитSQL
Эффективно - значит без table lock или строгой сериализации доступа к таблице.

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.
17 ноя 20, 14:43    [22233798]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Цель была - понять есть ли в языке достаточно возможностей для реализации несложных constraints по колонке.

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


Извините, это общие слова.
PL/SQL он одинаковый, что в триггере что в функции.

Например, я реализую constraint двуникальности.

Допустим я готов сделать таблицу недоступной, тем самым поломав парадигму SQL.
Я умею написать пакет с функциями, но я не умею:

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

Constraint по колонке - это непростая в целом задача для многопользовательского режима.
17 ноя 20, 14:55    [22233813]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
andrey_anonymous
НеофитSQL
Эффективно - значит без table lock или строгой сериализации доступа к таблице.

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.


Первичный ключ не даст мне вставить второе значение, или я чего-то не понял.
17 ноя 20, 14:56    [22233815]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
andrey_anonymous

Материализованное представление с первичным ключем и check constraint в режиме refresh fast on commit.


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

SY.
17 ноя 20, 15:14    [22233845]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
SY
А зачем с первичным ключем? Ну и следует упомянуть это будет имитация отложенного ключа.

Для упрощения жизни - mat.view все-таки.
...deferred - да.
17 ноя 20, 15:20    [22233854]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
andrey_anonymous

Для упрощения жизни - mat.view все-таки.


A что GROUP BY может выдать дубли?

SY.
17 ноя 20, 15:26    [22233866]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL,

А кто тебе сказал, что реальная бизнес логика должна решаться исключительно парадигмой SQL, и что такое парадигма SQL?

Если массовые вставки не планируются и вторых значений не много, то можно инкапсулировать вставку в триггере на вьюхе или процедуре, добавить поле номер двууникального значения и сделать уникальный ключ, вставка в процедуре/триггере off вьюхи идет с номером 1, если ошибка по уникальности то повторяем вставку с номером 2, если все равно ошибка по уникальности, то прокидываем ее наверх.
17 ноя 20, 15:34    [22233878]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL,

А кто тебе сказал, что реальная бизнес логика должна решаться исключительно парадигмой SQL, и что такое парадигма SQL?

Если массовые вставки не планируются и вторых значений не много, то можно инкапсулировать вставку в триггере на вьюхе или процедуре, добавить поле номер двууникального значения и сделать уникальный ключ, вставка в процедуре/триггере off вьюхи идет с номером 1, если ошибка по уникальности то повторяем вставку с номером 2, если все равно ошибка по уникальности, то прокидываем ее наверх.


> что такое парадигма SQL?
Я имел в виду, когда к данным применимы табличные операции.

Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).
17 ноя 20, 16:15    [22233922]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL
Я имел в виду, когда к данным применимы табличные операции.

К представлениям применимы табличные операции.

НеофитSQL
Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).

Да.
17 ноя 20, 16:22    [22233929]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Я имел в виду, когда к данным применимы табличные операции.

К представлениям применимы табличные операции.


Хорошо. Я спрятал таблицу, но чтобы к ней можно было писАть через SQL, сделал представление.
Теперь мне снова нужен триггер, только теперь на вьюхе, чтобы мой "особый" constraint исполнить.

Так что ли? И как мне "прятать" исходную таблицу, чтобы руками не лезли, а только через представление?
17 ноя 20, 16:35    [22233942]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18484
НеофитSQL
Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?
ЗадачкаНасколько помню, все закончилось тем, что это жутко неэффективно, если вообще возможно (покрывает все случаи)

Ну и я таки разочаровался в твоей адекватности
17 ноя 20, 16:41    [22233947]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Вячеслав Любомудров
НеофитSQL
Вопрос по теме: как в Оракле можно эффективно реализовать constraint двуникальности?
Чтобы в одной колонке находилось не более двух одинаковых значений?
ЗадачкаНасколько помню, все закончилось тем, что это жутко неэффективно, если вообще возможно (покрывает все случаи)


С удовольствием почитал 15-летнюю тему, где все еще были молодые, решали задачки и не боялись ошибаться :)
Да, там было много решений, но ни одно так и не оказалось полным.
Решение с построчным dbmslock мне понравилось больше всего, т.к. у меня похоже, только я использую ITL locks, которых больше.
17 ноя 20, 17:34    [22234002]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Если я правильно понял ваше решение, оно полагается на составной PK из двух колонок - одно для значения, второе для счетчика (1-2).

Да.


К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).
17 ноя 20, 17:35    [22234005]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18351
НеофитSQL
я использую ITL locks, которых больше.

Вы уверены что понимаете что есть ITL?
А решение есть - и оно Вам было озвучено и тут, и там. Mat.view Refresh on commit + constraint на mat.view.
17 ноя 20, 17:37    [22234007]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL
К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).

У тебя штатный PK перестал работать для двух сессий?

Просто и понятно, но катастрофически для производительности при массовых вставках или большом количестве дублей.
17 ноя 20, 18:39    [22234068]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
andrey_anonymous
НеофитSQL
я использую ITL locks, которых больше.

Вы уверены что понимаете что есть ITL?
А решение есть - и оно Вам было озвучено и тут, и там. Mat.view Refresh on commit + constraint на mat.view.


Я услышал про Mat.view Refresh on commit. Там совершенно другое поведение, запоздалое, с уникальными граблями.
https://dnikiforov.wordpress.com/2011/08/25/materialized-view-and-unique-constraints/

Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.
17 ноя 20, 18:59    [22234085]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
graycode
Member

Откуда:
Сообщений: 461
НеофитSQL
Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.

Ты до сих пор носишься с этой бессмысленной идеей? В PL/SQL нет средств построения своих хранимых, независящих от таблиц индексов и блокировки их листов, поэтому решить эффективно можно только пользуясь стандартными индексами.
17 ноя 20, 19:09    [22234092]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
К сожалению, такое не работает для двух сессий. Жаль, выглядит просто и понятно (как это обычно и происходит в мире с одной сессией).

У тебя штатный PK перестал работать для двух сессий?

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


Я думал о следующем примере:

сессия1> insert into two_max values ('aa');
1 row inserted
------------------------------------------
сессия2> insert into two_max values ('aa');
(подвис на попытке вставить аа:1)


Неоправданное блокирование второй сессии.
17 ноя 20, 19:13    [22234097]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL

Неоправданное блокирование второй сессии.


А что, при обычной уникальности не висит?

SY.
17 ноя 20, 19:24    [22234104]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
graycode
НеофитSQL
Кроме того, в этой теме обсуждается реализация unique constraint без использования встроенного, прикрепленного к чему-то другому.

Ты до сих пор носишься с этой бессмысленной идеей? В PL/SQL нет средств построения своих хранимых, независящих от таблиц индексов и блокировки их листов, поэтому решить эффективно можно только пользуясь стандартными индексами.


Это "hello world" для constraints по колонкам. Если я могу решить задачу уникальности, я могу решить общий случай, где критерий уникальности ключа является внешней функцией Distance(key1,key2) < М.

Например: на период пандемии натуральным id не разрешается соприкасаться (разница должна быть > 1) :)
Реализовать эффективный constraint на колонке, удовлетворяющий это правило.

Второй пример: для таблицы регистрации торговых знаков нужно реализовать constraint, который не позволит им быть слишком похожими друг на друга. Похожесть определяется функцией расстояния DistanceTM(clob, clob) (описание торгового знака содержится в колонке типа CLOB).

Третий пример: для улучшения диверсити лотереи гринкарты было решено отсеять "слишком похожих" кандидатов, соблюдая очередность поступления. Для этого DistanceGC(row,row) считает корреляцию по 300 параметрам, и кандидат "слишком похожий" на уже имеющихся в базе, считается "дупликатом".

Насколько я понимаю, в многосессионном режиме Оракл такое не умеет.
17 ноя 20, 19:36    [22234112]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY
НеофитSQL

Неоправданное блокирование второй сессии.


А что, при обычной уникальности не висит?

SY.


При обычной уникальности висит.

При "двуникальности" не должен, т.к. разрешено одно повторение.

При "стоникальности" подвисание 99 сессий без необходимости становится серьезным барьером для производительности.
17 ноя 20, 19:39    [22234115]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
Это "hello world" для constraints по колонкам. Если я могу решить задачу уникальности, я могу решить общий случай, где критерий уникальности ключа является внешней функцией Distance(key1,key2) < М.

Например: на период пандемии натуральным id не разрешается соприкасаться (разница должна быть > 1) :)
Реализовать эффективный constraint на колонке, удовлетворяющий это правило.

Второй пример: для таблицы регистрации торговых знаков нужно реализовать constraint, который не позволит им быть слишком похожими друг на друга. Похожесть определяется функцией расстояния DistanceTM(clob, clob) (описание торгового знака содержится в колонке типа CLOB).

Третий пример: для улучшения диверсити лотереи гринкарты было решено отсеять "слишком похожих" кандидатов, соблюдая очередность поступления. Для этого DistanceGC(row,row) считает корреляцию по 300 параметрам, и кандидат "слишком похожий" на уже имеющихся в базе, считается "дупликатом".

Насколько я понимаю, в многосессионном режиме Оракл такое не умеет.
Допустим ты решил простой случай через сериализацию.

Кстати, раньше один эксперт хорошо известный в узких кругах пытался продавать такой продукт.
В гугл - Oracle RuleGen
Сейчас он сайт удалил и многое выпилено (но при умении искать можно найти), а раньше была доступна вся документация и даже исходники.
Так вот "фишка" его решения была сериализация через dbms_lock.

Ключевое различие в "id не разрешается соприкасаться" и озученного тобой ранее что-то типа "расстояние Левенштейна не превышает N"
это то что в первом случае достаточно сравнить с двумя соседями а во втором со всеми записями в таблице.
Вот тут можно пытаться сводить проблему полного перебора к неполному с помощью domain indexes (как, например, сделано в Spatial).
17 ноя 20, 20:01    [22234130]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег

Ключевое различие в "id не разрешается соприкасаться" и озученного тобой ранее что-то типа "расстояние Левенштейна не превышает N"
это то что в первом случае достаточно сравнить с двумя соседями а во втором со всеми записями в таблице.


Для меня это не ключевое различие, это свойство функции расстояния, которое может повлиять на скорость.
На логику кода реализующего constraint уникальности влияет мало. Индекс это полезная, но ортогональная оптимизация.

Если строки короткие, я могу построить индекс в пространстве Левенштейна (а не линейный) и вместо полного перебора сравнивать только 2N соседей в N измерениях.

Мое желание скопировать поведение PK связано с тем, что оно хорошо отполировано и широко известно.
Лучше для общего случая я не придумаю.

Пока я решил для insert (в пределах возможностей моих 3-сессионных тестов).
Если не найду интереснее задачек, допилю delete/update.
17 ноя 20, 20:16    [22234144]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Почитал про Oracle RuleGen, интересно.

Учитывая количество подводных камней для multirow constraints, было бы намного лучше чтобы такие задачи решались профессионально. Еще лучше - чтобы были встроены в БД производителем, или продавались как надстройка.

Даже если окажется что я худо-бедно и в основном на ощупь один раз правильно реализовал multirow constraint в триггере,
это не значит что такой код легко сопровождаемый, или что я не сделаю ошибку в следующий раз в немного усложненной ситуации.

Свои цели в этом вопросе я достиг, обработку конфликтов в PK понимаю намного лучше чем пару дней назад, использовал select..for update почти по прямому назначению, и увидел что большинство решений тяготеют или к MV-on-commit или GTT+автономные транзакции (я использовал второе).
17 ноя 20, 20:40    [22234158]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 841
НеофитSQL
Учитывая количество подводных камней для multirow constraints, было бы намного лучше чтобы такие задачи решались профессионально.
Еще лучше - чтобы были встроены в БД производителем, или продавались как надстройка.
Ну так вроде уже говорили, что запрети изменения таблицы напрямую и сделай API с хранимками.
Проверки в хранимках и сериализация нужна только на время выполнения проверки.
Зачем еще какие-то надстройки? Дать возможность декларативно описывать multirow constraints слишком опасная граната в руках обезьян.
НеофитSQL
Даже если окажется что я худо-бедно и в основном на ощупь один раз правильно реализовал multirow constraint в триггере
Скорее всего окажется что это только кажется.
17 ноя 20, 21:12    [22234179]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
Кобанчег,

Хранимки или триггер - это я считаю вопрос обертки.
Для реализации insert главной проблемой оказалось научиться делать dirty reads.

Потому что PK это делает, а Оракл сессиям это запрещает.
Тогда и начинаются пляски с бубном как сделать row lock на строке, которой ещё нет в таблице.

Забудем про триггеры на минутку. Вот хранимка
InsertUniqueindex(id in integer) которая вставляет уникальный id в одну определенную таблицу, изначально пустую.

Надо чтобы первый вызов для id=1 прошел успешно, второй отказался (это легко, контекст одной сессии).

А теперь надо чтобы из всех других сессий вызовы с id=1 застряли пока первая сессия не совершит commit/rollback.
Вот где не хватает dirty read.

Тут нужен глобальный сериализующий объект который соблюдает очередность. По одному такому локу на каждый id, и желательно используемый без лишних переключений контекста. Я использовал ITL, как это делает сам оракл.
17 ноя 20, 21:36    [22234192]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
НеофитSQL,

В принципе все что нужно было бы это возможность указать NOWAIT в INSERT/UPDATE.

SY.
17 ноя 20, 22:35    [22234231]     Ответить | Цитировать Сообщить модератору
 Re: Реализация уникальности без ключей и индексов  [new]
НеофитSQL
Member [заблокирован]

Откуда: Маями
Сообщений: 760
SY
НеофитSQL,

В принципе все что нужно было бы это возможность указать NOWAIT в INSERT/UPDATE.

SY.


Если вы о реализации двуникального ключа, то да. Но это противоречит принципу запрета dirty reads, позволяя узнавать что постит другая сессия до commit.

Если вы о моей попытке смастерить PK своими силами, то вряд ли поможет, ведь insert не блокирует в отсутствие встроенного unique constraint.
17 ноя 20, 23:00    [22234242]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3 4 5      [все]
Все форумы / Oracle Ответить