|
||||||||||||
|
||||||||||||
|
||||||||||||
|
||||||||||||
|
||||||||||||
Sometimes it is necessary to just get a row into a table. If the row already exists, then it needs to be updated. If it doesn't it needs to be inserted. All of the methodologies proposed in the past have had race conditions since PostgreSQL at this time does not support the MERGE command. In general the technique is this: One does an update and if it fails because the row does not exist, then the row is inserted. UPDATE people SET name = in_name, email=in_email WHERE id = in_id; IF NOT FOUND THEN INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email); END IF;The race condition exists is between the UPDATE and the INSERT statements. During the race condition something else could create the row which in turn would cause the INSERT to fail. If the table in question has very few inserts or updates, this technique could work for you. For example, a table which gets only a couple of update/inserts per day (not all at once) or tables which only get updated in batch mode on a single connection. To insure that the update/insert does not fail due to a race condition, then we need to take advantage of the relatively new feature of exception handling in plpgsql. The full implementation is a bit awkward. It requires a function per table (unless you are doing some pg_catalog queries to divine the column names for the INSERT and UPDATE statements.) In this function, if the UPDATE fails, an INSERT is tried. If the INSERT fails due to something else during the race condition, then it loops back up and retries the UPDATE. It would take a lot of synchronized changes to this particular row to make the loop go around more than once. But if it needs to do so, then it will, doing the proper thing.
CREATE TABLE people (id INT PRIMARY KEY, name TEXT, email TEXT); CREATE FUNCTION merge_people(in_id INT, in_name TEXT, in_email TEXT) RETURNS VOID AS $$ BEGIN LOOP UPDATE people SET name = in_name, email=in_email WHERE id = in_id; IF found THEN RETURN; END IF; BEGIN INSERT INTO people(id,name,email) VALUES (in_id, in_name, in_email); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT merge_people(1, 'david', 'david@gmail.com'); -- Adds id 1 SELECT merge_people(1, 'david', 'david@hotmail.com'); -- Updates id 1 SELECT merge_people(2, 'harold', 'harold@hotmail.com'); -- Adds id 2 The RETURN statements end the LOOP. Usually LOOP ... END LOOP; is just asking for trouble. The UPDATE statement uses the special variable FOUND which is set to TRUE for a successful update. The exception handling enables this function to catch the case that allows the loop back. The syntax is this: BEGIN STATEMENT EXCEPTION WHEN condition THEN STATEMENT END; The condition codes are listed in Appendix A of the manual. In our case we use the unique violation test because this is the error that would be raised if we tried to insert a row that already existed.
Prior to 8.1, if you included a table name in a query PostgreSQL would add it to the FROM clause for you and leave you a NOTICE that you probably ignored. As of 8.1, you will get an error instead of a warning and your query will not work. So if you've been slacking about fixing those queries, now is the time to review them and fix them. Here is an example of a query which has a missing FROM clause. This query validates whether a certain table and column have been defined. SELECT atthasdef FROM pg_attribute WHERE attrelid=pg_class.oid AND pg_class.relname='client_overtime' AND attname= 'id_client_overtime';Table pg_class is named in the query but it does not appear in the FROM clause. All source tables must appear in the FROM clause. This query should be: select atthasdef from pg_attribute a, pg_class c WHERE attrelid=c.oid AND c.relname = 'client_overtime' AND a.attname = 'id_client_overtime'; This requirement for the FROM clause includes the UPDATE statement, too. Given the following a master and a detail table, we tried this SQL: UPDATE d126 set dt = m126.t || d126.d WHERE m126.i = d126.d and m126.t = 'a';This statement gives us the Missing FROM-clause error. To correct the statement, add the FROM clause: UPDATE d126 set dt = m.t || d126.d FROM m126 m WHERE m.i=d126.i AND m.t = 'a';
You must be sure to avoid the usual problems with UPDATES.
The updated table, d126 cannot have an alias.
The table in the FROM clause will be selected along with
the updated table and you most probably want to have some qualification
which joins the two tables. This clause is
|
||||||||||||
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in! Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009 |