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

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

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

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

Откуда: Middlebury, CT USA
Сообщений: 10051
Неофит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

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

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

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

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

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

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

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

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

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

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

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

Откуда: Владивосток
Сообщений: 18486
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

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

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


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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

Сообщение было отредактировано: 14 ноя 20, 12:34
14 ноя 20, 12:38    [22231995]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
Все форумы / Oracle Ответить