Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...

Поиск
Список
Период
Сортировка
От Jim Garrison
Тема Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...
Дата
Msg-id 391418bb91fe4570a2fe8bda3281b976@BN1PR06MB839.namprd06.prod.outlook.com
обсуждение исходный текст
Ответы Re: Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...  (David G Johnston <david.g.johnston@gmail.com>)
Re: Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...  (Jerry Sievers <gsievers19@comcast.net>)
Re: Atomicity of INSERT INTO ... SELECT ... WHERE NOT EXISTS ...  (Craig Ringer <craig@2ndquadrant.com>)
Список pgsql-general
Given (pseudocode)

    CREATE TABLE kvstore (
        k varchar primary key,
        v varchar);

    CREATE OR REPLACE FUNCTION store_key_value_pair(k varchar, v varchar) returns boolean as $$
    BEGIN
      INSERT INTO kvstore (k, v)
      SELECT :k, :v
      WHERE NOT EXISTS (select 1 from kvstore where k = :k);
      RETURN FOUND;
    END;
    $$ LANGUAGE plpgsql;

I have a few questions:

1) Does INSERT statement set FOUND based on whether or not the row was inserted?
2) If this is invoked without a transaction in progress, is there any guarantee of atomicity between checking the
EXISTSand attempting to insert the row?  If this is being executed in two (or more) sessions, can the SELECT succeed
butthen have the INSERT fail with a duplicate-key exception? 
3) Will the behavior be different if the invoking processes have a transaction in progress?



В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: error restarting DB
Следующее
От: Rich Shepard
Дата:
Сообщение: UPDATE table: Syntax to Remove Terminal '\n'