Посоветуйте что почитать подробно про транзакции

И ещё вопрос. На каком уровне исполняет триггер? В документации ничего не нашёл про это.

Не понимаю ровным счётом ничего, как будто по китайски написано.

А по мне, так очень легко и удобно.

Тынц: http://dblp.uni-trier.de/db/books/collections/JajodiaK97.html

Что это за форматы за такие BibiTeX, RIS, чем их открывать? Где скачать в pdf или html?

Ну Вы что, голубчик? Копируете название и в Гугл, в Гугл: http://www.workflowpatterns.com/documentation/documents/AAA 96.pdf http://doc.utwente.nl/64217/1/Grefen.pdf

Первые попавшиеся ссылки, пробежал глазами - вроде оно..

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

CREATE TABLE fields (
	id BIGSERIAL PRIMARY KEY,
	name CITEXT NOT NULL,
	position BIGINT NOT NULL,
	
	CONSTRAINT fields_name_unique UNIQUE (name),
	CONSTRAINT fields_name_trimmed CHECK (trim(name) = name),
	CONSTRAINT fields_name_long_enough CHECK (name <> ''),
	CONSTRAINT fields_name_short_enough CHECK (length(name) <= 256),
	
	CONSTRAINT fields_position_unique UNIQUE (position) DEFERRABLE,
	CONSTRAINT fields_position_not_negative CHECK (position >= 0)
);

Если открыть 2 psql и в первом ввести:

BEGIN; SELECT COALESCE(MAX(position) + 1, 0) AS next_val FROM fields;

После этого перейти во второй psql и ввести тоже самое. В обоих получаем в ответ 2. После этого вернуться в первый psql и ввести:

INSERT INTO fields (name, position) VALUES ('from1', 2);

После этого перейти во второй psql и ввести:

INSERT INTO fields (name, position) VALUES ('from2', 2);

второй psql блокируется. Переходим в первый psql и вводим:

COMMIT;

После этого второй psql разблокируется и выдаёт ошибку:

ERROR: duplicate key value violates unique constraint «fields_position_unique» DETAIL: Key («position»)=(2) already exists.

В таблице остаётся только ('from1', 2).

Как это корректно обрабатывать в коде? Перехватывать ошибку и повторять ещё раз? Может ли так зациклиться при большой активности такой повторяющий цикл? Может есть какой то более простой способ?

Ну вроде бы не совсем то что хотелось бы. Там не проще а ещё сложнее, всё формально абстрагированно от postgresql. А хотелось бы туториал для начинающих посвящённый полностью транзакциям и подробно на примерах их описывающий. Потому что эти транзакции с isolation levels мне с самого начала не давались и я уже начиная читать заранее не верю в то что то дойдёт. Ничего абсолютно не понятно. Перечитать что ли несколько раз и переписать 10 раз ;)

Подождите, может, кто придёт и простыми словами объяснит :-)

Не дождёшься :-). Буду перечитывать https://www.postgresql.org/docs/9.5/static/transaction-iso.html и к каждой строчке делать наглядный пример с двумя psql ;)

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

Например, почитай еще вот тут https://www.postgresql.org/docs/9.5/static/explicit-locking.html.

В общем по сабжу в очередной раз посоветую NoSQL Фаулера.

А причём тут NoSQL, это же совсем другое. Там данные ненадёжно хранятся ненормализованно и транзакций вроде вообще нет.

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

Интересный подход, это же проще чем разбирать исходный код РСУБД что бы посмотреть как там реализовано. Обязательно почитаю. Вообще хотелось бы найти такой туториал где показывается как это всё используется из приложения, какие запросы, как обрабатываются ошибки и всё такое. Потому что все книги по теме которые я видел опускают эти проблемы и описывают только тривиальные случаи типа простейший CRUD. Во всяких ORM типа ActiveRecord и т.д. эти ситуации также не предусматриваются или я не прав по этому поводу? Во всяком случае в документации по ним о таких моментах ни слова. А не хотелось бы что бы возникли проблемы с данными в продакшн системе из за этих казалось бы мелочей особенно учитывая как трудно это тестировать смоделировать разнообразные ситуации с конкурентным доступом к данным.

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

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

Эти подходы описаны, например, у того же Фаулера в Шаблонах корпоративных приложений, если не ошибаюсь.

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

Как это корректно обрабатывать в коде? Перехватывать ошибку и повторять ещё раз?

обработка зависит от решаемой задачи
в данном примере почему обе программы пытаются запихнуть в position = 2 , так и задумано ?

Так нужно при INSERT делать position = MAX(position) + 1. А что бы определить MAX(position) + 1 нужно выполнить «SELECT...». Если оба процесса одновременно добавляют в таблицу fields они оба должны сделать «SELECT MAX(position)...». Но последовательность в которой последующие запросы будут обрабатываться не предсказуема и зависит от переключателя процессов в ядре OS.

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

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

Как это корректно обрабатывать в коде? Перехватывать ошибку и повторять ещё раз? Может ли так зациклиться при большой активности такой повторяющий цикл? Может есть какой то более простой способ?

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

Самое банальное - шаблоны проектирования. POSA, например. Повеселее в статьях и, если повезет, какие-то конкретные примеры на конференциях можно найти. Я где-то находил материал с подготовительного дня HL++, вот там для новичка, на мой взгляд, было достаточно много материала для усвоения. Самое правильное, я думаю, участие в доработке подобной системы. На петпроджектах со многими проблемами просто не столкнешься, но если будешь коммитить в существующий проект, ревьюеры тебе много расскажут про то, как правильно делать. Копаться в исходниках тоже полезно, но это хорошо, когда ты ищешь что-то конкретное (иначе некоторые вещи ты просто не осознаешь, т.к. решение не сконцентрировано в конкретном месте кода).

MAX(position) + 1

Не нужно, есть sequence и даже сахар для них в виде serial

Не нужно, есть sequence и даже сахар для них в виде serial

При удалении элемента sequence перестроится что бы не было разрывов? Иначе с переупорядочиванием проблемы (если пользователь хочет поменять порядок).

Никаких проблем, просто меняешь номера у двух элементов местами и всё. Неразрывность, опять же, не нужна.

Если вообще всё как китайская грамота, то почитайте про реляционные БД, что такое отношения, кортежи и т.д., станет проще.

В документации к Оракелю довольно толково описано:
https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020