Ik “heb gelezen over verschillende UPSERT implementaties in PostgreSQL, maar alle deze oplossingen zijn relatief oud of relatief exotisch (bijvoorbeeld met beschrijfbare CTE ).
En ik “ben gewoon geen psql-expert in allemaal om er meteen achter te komen of deze oplossingen oud zijn omdat ze goed worden aanbevolen of dat ze (nou ja, bijna allemaal) slechts speelgoedvoorbeelden zijn die niet geschikt zijn voor gebruik in de productie.
Wat is de meest threadveilige manier om UPSERT in PostgreSQL te implementeren?
Answer
PostgreSQL nu heeft UPSERT .
De geprefereerde methode volgens een vergelijkbare StackOverflow-vraag is momenteel de volgende:
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"); Reacties
- I ' gebruik liever een beschrijfbare CTE: stackoverflow.com/a/8702291/330315
- Wat ' is het voordeel van een beschrijfbare CTE versus een functie?
- @Fran ç is bijvoorbeeld snelheid. Met een CTE raak je de database één keer. Als je het op deze manier doet, kun je het twee of meer keer raken. Bovendien kan de optimizer ' pl / pgsql-procedures net zo efficiënt optimaliseren als pure SQL-code.
- @Fran ç ois Ten tweede, concurrency. Aangezien het bovenstaande voorbeeld meerdere SQL-instructies bevat, moet u zich zorgen maken over race-omstandigheden (de reden voor de klugey-lus). Een enkele SQL-instructie is atomair. Zie deze link
- @Fran ç oisBeausoleil zie hier en hier voor waarom. In principe zonder een herhalingslus, moet je ofwel serialiseren of je hebt de mogelijkheid van mislukkingen vanwege de inherente race-conditie.
Answer
UPDATE (2015-08-20):
Er is nu een officiële implementatie voor het afhandelen van upserts door het gebruik van ON CONFLICT DO UPDATE (officiële documentatie) . Op het moment van schrijven bevindt deze functie zich momenteel in PostgreSQL 9.5 Alpha 2, die hier kan worden gedownload: Postgres-bronmappen .
Hier is een voorbeeld, ervan uitgaande dat item_id uw primaire sleutel is:
INSERT INTO my_table (item_id, price) VALUES (123456, 10.99) ON CONFLICT (item_id) DO UPDATE SET price = EXCLUDED.price Oorspronkelijke post …
Hier is een implementatie die ik tegenkwam toen ik inzicht wilde krijgen in of er een invoeging of een update plaatsvond.
De definitie van upsert_data is om te consolideren de waarden in een enkele bron, in plaats van de prijs en item_id twee keer op te geven: een keer voor de update, opnieuw voor de insert.
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 Als u niet “vind het gebruik van upsert_data niet leuk, hier is een alternatieve implementatie:
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 Reacties
- Hoe presteert het?
- @jb. niet zo goed als ik zou willen. Je ' gaat zien aanzienlijke prestatie straf ies versus het uitvoeren van rechte wisselplaten. Voor kleinere batches (zeg 1000 of minder) zou dit voorbeeld echter prima moeten presteren.
Answer
Dit zal je laten weten of de insert of update heeft plaatsgevonden:
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" ); Als de update plaatsvindt, krijg je een insert 0, anders 1 of een foutmelding.