On Jun 26, 9:30 pm, goofyheadedp...@xxxxxxxxx (Brian Troutwine) wrote: > Hello, all. > > CREATE OR REPLACE FUNCTION item_data_insert( > iasin TEXT, iauthor TEXT, ibinding TEXT, icurrency_code TEXT, > iisbn TEXT, iheight INTEGER, iwidth INTEGER, ilength INTEGER, > iweight INTEGER, > ilist_price INTEGER, iproduct_group TEXT, isales_rank INTEGER, > ititle TEXT, ioffer_pages INTEGER, ioffers TEXT) > RETURNS VOID AS > $$ > DECLARE > y integer[]; > BEGIN > y[1] := iwidth; > y[2] := ilength; > y[3] := iheight; > y[4] := iweight; > BEGIN > INSERT INTO item_details > (isbn, title, author, binding, list_price, dimensions) > VALUES > (iisbn, ititle, iauthor, ibinding, ilist_price, y); > EXCEPTION WHEN unique_violation THEN > UPDATE item_details SET > title = ititle, > author = iauthor, > binding = ibinding, > list_price = ilist_price, > dimensions = y > WHERE isbn = iisbn; > END; > BEGIN > INSERT INTO amazon_items > (asin, sales_rank, offers, offer_pages, isbn) > VALUES > (iasin, isales_rank, crunch(ioffers), ioffer_pages, iisbn); > EXCEPTION WHEN unique_violation THEN > IF isales_rank IS NOT NULL THEN > UPDATE amazon_items SET > sales_rank = isales_rank > WHERE asin = iasin; > END IF; > IF ioffers IS NOT NULL THEN > UPDATE amazon_items SET > offers = crunch(ioffers), > offers_last_updated = CURRENT_TIMESTAMP, > offer_pages = ioffer_pages > WHERE asin = iasin; > END IF; > END; > END; > $$ > LANGUAGE plpgsql; > Hi, did the index on isbn field help? Another note, that is more fine tuning actually, then the real cause of the slow execution of your procedure. If you are expecting to update more, then insert, then you probably should not wait for the exception to be thrown as all the BEGIN EXCEPTION END blocks are more expensive to execute, then simple calls. Have a look here: http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Also note that if you UPDATE first, and then try to INSERT only when UPDATE could not find anything to update, you really HAVE to expect INSERT to fail and then retry updating, as another, parallel transaction, could be fast enough to INSERT a record after you tried to update and before your transaction starts to insert. With best regards, -- Valentine Gogichashvili -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
