Archive for Postgresql
Juni 24, 2009 at 9:59 pm · Filed under Postgresql
Die im vorletzten Artikel vorgestellte Lösung hat den Nachteil, den ganzen Table my_table zu locken.
Das ist dann ein Problem, wenn criterion mittels unique constraint eindeutig gemacht wird und nach der Funktion eine zeitaufwendige Funktion (z.B. eine Suche) in derselben Transaktion folgt: Dann warten nämlich alle Prozesse, die ein findOrCreate machen wollen, unabhängig mit welchem Wert für criterion, auf den Abschluss der ersten Transaktion, da erst zu diesem Zeitpunkt sichergestellt werden kann, dass die Spalte wirklich unique ist.
Eigentlich sollte es aber möglich sein, dass parallele Transaktionen mit anderen Werten für criterion unbehelligt ausgeführt werden.
Mit dem folgenden, von Martin Heistermann und Stephan Lüderitz vorgeschlagenen Ansatz, umgeht man dieses Problem elegant:
CREATE OR REPLACE FUNCTION findOrCreate(
IN criterion_in text,
OUT record_id integer, OUT is_new bool) AS
$$
DECLARE
BEGIN
INSERT INTO
my_table (criterion)
VALUES
(criterion_in)
RETURNING id INTO record_id;
SELECT true INTO is_new;
EXCEPTION
WHEN unique_violation THEN
SELECT false, id into is_new, record_id FROM my_table
WHERE criterion=criterion_in;
END
$$ language plpgsql;
Wie in dieser Präsentation (PDF) beschrieben, läßt PostgreSQL das parallele Einfügen verschiedener Werte mittels findOrCreate zu, zwingt aber bei zwei Transaktionen, die denselben Wert einfügen wollen, die zweite, mit dem Insert auf das Ende der ersten zu warten.
Findet die (potentiell teure) Suche in der Transaktion nach findOrCreate statt, wird diese gar nicht mehr ausgeführt, da findOrCreate per Exception die von der ersten Transaktion neu eingefügte Zeile ermittelt und gleich mit deren Ergebnissen weitermachen kann.
Mai 16, 2009 at 5:31 pm · Filed under Postgresql
Oft steht man vor dem Problem, einen Datensatz anhand eines Kriteriums zu finden bzw. ihn, falls nicht vorhanden, einzufügen. Bei derartigen Operationen ist es wichtig darauf zu achten, dass nicht mehrere Prozesse, die quasi gleichzeitig diesen Datensatz suchen, eine race condition hervorrufen.
Hier eine Lösung mittels stored procedure für postgresql. Dabei wird eine race condition zwischen Suche und Einfügen vermieden und nur minimales Locking gebraucht:
CREATE OR REPLACE FUNCTION findOrCreate(
IN criterion_in text,
OUT id int, OUT is_new bool) AS
$$
DECLARE
BEGIN
LOCK TABLE my_table;
SELECT false, id FROM my_table WHERE criterion=criterion_in INTO is_new, id;
IF id IS NULL THEN
INSERT INTO
my_table (criterion)
VALUES
(criterion_in)
RETURNING id INTO id;
SELECT true INTO is_new;
END IF;
RETURN;
END
$$ language plpgsql;
Man beachte die praktischen OUT-Parameter, die es ermöglichen, diese Funktion wie folgt aufzurufen:
SELECT id, is_new FROM findOrCreate(criterion);
Rückgabewerte sind
id und
is_new, die einem die Datenbank-Id angeben, und ob diese neu angelegt wurde.
Der Table-Lock wird beim Beenden der Funktion (die immer implizit eine Transaktion ist) automatisch beendet.
April 17, 2007 at 12:08 pm · Filed under Postgresql
A lot of nice things can be done with postgresql’s hstore type.
They are a nice way to store flags, for example, and by providing an aggregate function to sum them up, one can easily compute complex unions of properties.
First we define an aggregate function to sum postgresql hstores, straightforward as this:
CREATE AGGREGATE sum(
sfunc = hs_concat,
basetype = hstore,
stype = hstore,
initcond = ''
);
Allows for beautiful constructs as
CREATE OR REPLACE FUNCTION array_to_hstore(text[]) RETURNS hstore AS $$
DECLARE
arr text[];
result hstore;
BEGIN
SELECT INTO arr $1;
SELECT INTO result sum(key=>1) FROM (
select
btrim(arr[idx.i]) as key
from
generate_series(1, array_upper(arr,1)) as idx(i)
) as dummy;
RETURN result;
END
$$ LANGUAGE plpgsql;
Now what can be done with that? For example we can turn comma separated lists into hstores:
mydb=> SELECT array_to_hstore(string_to_array('a, b, c, a', ','));
array_to_hstore
-------------------------------------------
"a"=>"1", "b"=>"1", "c"=>"1"
(1 Zeile)
