Olvastam a PostgreSQL különböző UPSERT megvalósításairól, de mindegyiket ezek a megoldások viszonylag régiek vagy viszonylag egzotikusak (például írható CTE-t használva ).
És én csak nem vagyok psql-szakértő a mindezt annak érdekében, hogy azonnal kiderüljön, hogy ezek a megoldások régiek-e, mert jól ajánlottak, vagy (jóllehet, szinte mindegyikük) csak játékpéldák, amelyek nem megfelelőek a gyártáshoz.
Mi a legbiztonságosabb módszer az UPSERT bevezetésére a PostgreSQL-ben?
Válasz
A PostgreSQL most UPSERT van.
Az előnyben részesített módszer szerint hasonló StackOverflow kérdés jelenleg a következő:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ BEGIN LOOP -- first try to update the key UPDATE db SET b = data WHERE a = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_db(1, "david"); SELECT merge_db(1, "dennis"); Megjegyzések
- I ' d inkább írható CTE-t használjon: stackoverflow.com/a/8702291/330315
- Mi ' s az írható CTE és egy függvény előnye?
- @Fran ç egy dolog, a sebesség. A CTE használatával egyszer eltalálja az adatbázist. Ilyen módon kétszer vagy többször eltalálhatja. Az optimalizáló nem tudja ' optimalizálni a pl / pgsql eljárásokat, mint a tiszta SQL kód.
- @Fran ç ois Egy másik dolog, az egyidejűség. Mivel a fenti példának több SQL-mondata van, aggódnia kell a versenyfeltételek miatt (a klugey hurok oka). Egyetlen SQL utasítás lesz atom. Lásd ezt a linket
- @Fran ç oisBeausoleil lásd itt és itt miért. Alapvetően újrapróbálkozási ciklus nélkül vagy sorozatot kell készítenie, vagy lehetősége van kudarcokra az eredendő versenyállapot miatt.
Válasz
UPDATE (2015-08-20):
Mostantól hivatalos végrehajtás van a támadások kezelésére a ON CONFLICT DO UPDATE (hivatalos dokumentáció) használatával. . Az írás idején ez a szolgáltatás jelenleg a PostgreSQL 9.5 Alpha 2 verzióban található, amely innen letölthető: Postgres forráskönyvtárak .
Itt van egy példa, feltételezve, hogy a item_id az Ön elsődleges kulcsa:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Eredeti üzenet …
Itt van egy megvalósítás, amellyel akkor szerettem volna látni, hogy beillesztés vagy frissítés történt-e.
A upsert_data definíciója konszolidáció. az értékeket egyetlen erőforrásba, ahelyett, hogy kétszer kellene megadnia az árat és az elem_idet: Egyszer a frissítéshez, ismét a beillesztéshez.
WITH upsert_data AS ( SELECT "19.99"::numeric(10,2) AS price, "abcdefg"::character varying AS item_id ), update_outcome AS ( UPDATE pricing_tbl SET price = upsert_data.price FROM upsert_data WHERE pricing_tbl.item_id = upsert_data.item_id RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT upsert_data.price AS price, upsert_data.item_id AS item_id FROM upsert_data WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome Ha nem “Nem tetszik a upsert_data használata, itt van egy alternatív megvalósítás:
WITH update_outcome AS ( UPDATE pricing_tbl SET price = "19.99" WHERE pricing_tbl.item_id = "abcdefg" RETURNING "update"::text AS action, item_id ), insert_outcome AS ( INSERT INTO pricing_tbl (price, item_id) SELECT "19.99" AS price, "abcdefg" AS item_id WHERE NOT EXISTS (SELECT item_id FROM update_outcome LIMIT 1) RETURNING "insert"::text AS action, item_id ) SELECT * FROM update_outcome UNION ALL SELECT * FROM insert_outcome megjegyzések
- Hogyan teljesít?
- @jb. nem annyira, mint szeretném. ' látni fogja jelentős teljesítmény penalt s vs egyenes betétek végrehajtása. Kisebb kötegeknél (mondjuk 1000 vagy kevesebb) ennek a példának azonban remekül kell teljesítenie.
Válasz
Ez tudatja Önnel, hogy a beillesztés vagy a frissítés történt-e:
with "update_items" as ( -- Update statement here update items set price = 3499, name = "Uncle Bob" where id = 1 returning * ) -- Insert statement here insert into items (price, name) -- But make sure you put your values like so select 3499, "Uncle Bob" where not exists ( select * from "update_items" ); Ha a frissítés megtörténik, akkor 0 beillesztést kap, különben beilleszt 1 vagy hibát.