Вставить строку, если она не существует, приводит к состоянию гонки?

Ответ 1

Здесь существует неизбежная "гонка", так как две сессии не могут "видеть" друг друга неустановленными строками. В конфликте сеанс мог только откат (возможно, в точку сохранения) и повторить попытку. Это обычно означает: ссылаясь на другую только что вставленную строку, вместо создания частного дубликата.

Здесь есть проблема моделирования данных: у feed_channel есть много ключей-кандидатов, и каскадное правило из feed_content могло бы осилить много строк feed_content (я полагаю, что content- > channel равен 1:: M; больше чем одна строка содержимого может ссылаться на один и тот же канал)

Наконец, таблица feed_channel не менее нуждается в естественном ключе {link, title}. Вот где вставка/не существует. (и вся цель этой функции)

Я немного очистил функцию. Конструкция IF не нужна, делая INSERT WHERE NOT EXISTS first работает так же хорошо, и, возможно, даже лучше.

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE feed_channel
    ( id SERIAL PRIMARY KEY
    , name TEXT
    , link TEXT NOT NULL
    , title TEXT NOT NULL -- part of PK :: must be not nullable
    , CONSTRAINT feed_channel_nat UNIQUE (link,title) -- the natural key
);

CREATE TABLE feed_content
    ( id SERIAL PRIMARY KEY
    , channel INTEGER REFERENCES feed_channel(id) ON DELETE CASCADE ON UPDATE CASCADE
    , guid TEXT UNIQUE NOT NULL -- yet another primary key
    , title TEXT --
    , link TEXT  -- title && link appear to be yet another candidate key
    , description TEXT
    , pubdate TIMESTAMP
    );

-- NOTE: omitted original function channel_insert() for brevity
CREATE OR REPLACE FUNCTION channel_insert_wp(
  p_link feed_channel.link%TYPE,
  p_title feed_channel.title%TYPE
) RETURNS feed_channel.id%TYPE AS $body$
   DECLARE
    v_id feed_channel.id%TYPE;
  BEGIN
      INSERT INTO feed_channel(link,title)
      SELECT p_link,p_title
      WHERE NOT EXISTS ( SELECT *
        FROM feed_channel nx
        WHERE nx.link= p_link
        AND nx.title= p_title
        )
        ;
    SELECT id INTO v_id
    FROM feed_channel ex
    WHERE ex.link= p_link
    AND ex.title= p_title
        ;

    RETURN v_id;

  END;
$body$ LANGUAGE plpgsql;

SELECT channel_insert('Bogus_link', 'Bogus_title');
SELECT channel_insert_wp('Bogus_link2', 'Bogus_title2');

SELECT * FROM feed_channel;

Результаты:

DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  CREATE TABLE will create implicit sequence "feed_channel_id_seq" for serial column "feed_channel.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "feed_channel_pkey" for table "feed_channel"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "feed_channel_nat" for table "feed_channel"
CREATE TABLE
NOTICE:  CREATE TABLE will create implicit sequence "feed_content_id_seq" for serial column "feed_content.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "feed_content_pkey" for table "feed_content"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "feed_content_guid_key" for table "feed_content"
CREATE TABLE
NOTICE:  type reference feed_channel.link%TYPE converted to text
NOTICE:  type reference feed_channel.title%TYPE converted to text
NOTICE:  type reference feed_channel.id%TYPE converted to integer
CREATE FUNCTION
NOTICE:  type reference feed_channel.link%TYPE converted to text
NOTICE:  type reference feed_channel.title%TYPE converted to text
NOTICE:  type reference feed_channel.id%TYPE converted to integer
CREATE FUNCTION
 channel_insert 
----------------
              1
(1 row)

 channel_insert_wp 
-------------------
                 2
(1 row)

 id | name |    link     |    title     
----+------+-------------+--------------
  1 |      | Bogus_link  | Bogus_title
  2 |      | Bogus_link2 | Bogus_title2
(2 rows)

Ответ 2

Будет ли это потенциальное состояние гонки в PostgreSQL?

Да, и на самом деле это будет в любом движке базы данных.

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

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

Начнем с базового LOCK:

LOCK TABLE feed_channel

Это заблокирует таблицу, используя опцию блокировки ACCESS EXCLUSIVE.

Конфликты с блокировками всех режимов (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE и ACCESS EXCLUSIVE). Этот режим гарантирует, что держатель является единственной транзакцией, обращающейся к таблице в любом случае.

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

Что вы решили решить?

• Как вы хотите LOCK таблицу? Изучите параметры блокировки на этой ссылке, чтобы сделать ваше определение.
• Где вы хотите LOCK таблицу? Или, другими словами, вы хотите LOCK в верхней функции (которую я думаю, что вы делаете на основе возможного состояния гонки), или просто хотите LOCK прямо перед INSERT?

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

Нет, код может быть выполнен любым, кто подключен к базе данных.

Я надеюсь, что это помогло вам.

Ответ 3

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

BTW v_id будет null всякий раз, когда title равно null:

WHERE link=p_link AND title=p_title