Re: [PATCHES] [HACKERS] Best practices: MERGE
Is that broken? http://momjian.postgresql.org/main/writings/pgsql/sgml/build.html Chris Bruce Momjian wrote: Patch applied. Thanks. Sorry for the delay in applying. --- David Fetter wrote: On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote: Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Pursuant to an IRC discussion to which Dennis Bjorklund and Christopher Kings-Lynne made most of the contributions, please find enclosed an example patch demonstrating an UPSERT-like capability. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Best practices: MERGE
Patch applied. Thanks. Sorry for the delay in applying. --- David Fetter wrote: On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote: Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Pursuant to an IRC discussion to which Dennis Bjorklund and Christopher Kings-Lynne made most of the contributions, please find enclosed an example patch demonstrating an UPSERT-like capability. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Best practices: MERGE
Thanks, fixed. --- Christopher Kings-Lynne wrote: Is that broken? http://momjian.postgresql.org/main/writings/pgsql/sgml/build.html Chris Bruce Momjian wrote: Patch applied. Thanks. Sorry for the delay in applying. --- David Fetter wrote: On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote: Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Pursuant to an IRC discussion to which Dennis Bjorklund and Christopher Kings-Lynne made most of the contributions, please find enclosed an example patch demonstrating an UPSERT-like capability. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Best practices: MERGE
On Mon, 2005-03-07 at 19:34 -0800, David Fetter wrote: Although the SQL:2003 command MERGE has not yet been implemented in PostgreSQL, I'm guessing that there are best practices for how to implement the MERGE functionality. To recap, MERGE means (roughly) INSERT the tuple if no tuple matches certain criteria, otherwise UPDATE using similar criteria. Don't understand that way round... I thought the logic was: UPDATE WHERE . (locate row) IF NOT FOUND THEN INSERT (new row) You can create a procedure to do that, but MERGE would work better. ISTM that would require writing some new code that was a mix of heap_update and heap_insert logic for the low level stuff would be required. The existing heap_update code is most similar, since the logic is roughly UPDATE WHERE (locate row) IF FOUND THEN INSERT (new row version) though with various changes to row visibility stuff. One might aim to do this in two stages: 1. initially support a single row upsert such as MySQL's REPLACE command 2. a full implementation of MERGE that used set logic as per the spec ... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Best practices: MERGE
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote: Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Pursuant to an IRC discussion to which Dennis Bjorklund and Christopher Kings-Lynne made most of the contributions, please find enclosed an example patch demonstrating an UPSERT-like capability. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ? upsert.diff Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.61 diff -c -r1.61 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 14 Feb 2005 00:54:26 - 1.61 --- doc/src/sgml/plpgsql.sgml 8 Mar 2005 08:19:31 - *** *** 2003,2008 --- 2003,2042 don't use literalEXCEPTION/ without need. /para /tip + example id=plpgsql-upsert-example + para + This example uses an literalEXCEPTION/ to commandUPDATE/ or + commandINSERT/, as appropriate. + + programlisting + CREATE TABLE db (a INT PRIMARY KEY, b TEXT); + + CREATE FUNCTION merge_db (key INT, data TEXT) RETURNS VOID AS + $$ + BEGIN + LOOP + UPDATE db SET b = data WHERE a = key; + IF found THEN + RETURN; + END IF; + + BEGIN + INSERT INTO db(a,b) VALUES (key, data); + RETURN; + EXCEPTION WHEN unique_violation THEN + -- do nothing + END; + END LOOP; + END; + $$ + LANGUAGE plpgsql; + + SELECT merge_db (1, 'david'); + SELECT merge_db (1, 'dennis'); + /programlisting + + /para + /example /sect2 /sect1 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Best practices: MERGE
You can create a procedure to do that, but MERGE would work better. ISTM that would require writing some new code that was a mix of heap_update and heap_insert logic for the low level stuff would be required. The existing heap_update code is most similar, since the logic is roughly UPDATE WHERE (locate row) IF FOUND THEN INSERT (new row version) though with various changes to row visibility stuff. One might aim to do this in two stages: 1. initially support a single row upsert such as MySQL's REPLACE command 2. a full implementation of MERGE that used set logic as per the spec ... The main issue is dealing with merging into unique index race conditions. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Best practices: MERGE
Folks, Although the SQL:2003 command MERGE has not yet been implemented in PostgreSQL, I'm guessing that there are best practices for how to implement the MERGE functionality. To recap, MERGE means (roughly) INSERT the tuple if no tuple matches certain criteria, otherwise UPDATE using similar criteria. The correct solution, as far as I can tell, is to acquire a LOCK on the table IN SHARE MODE at the beginning of the transaction, but this has (at least for many applications) unacceptable performance characteristics. Accepting that there is a slight risk of a race condition when *not* locking the table at the beginning of the transaction, what procedure minimizes this risk and recovers well from said race condition, should it occur? TIA for any hints, tips or pointers on this :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Best practices: MERGE
The correct solution, as far as I can tell, is to acquire a LOCK on the table IN SHARE MODE at the beginning of the transaction, but this has (at least for many applications) unacceptable performance characteristics. Accepting that there is a slight risk of a race condition when *not* locking the table at the beginning of the transaction, what procedure minimizes this risk and recovers well from said race condition, should it occur? IN SHARE MODE is not enough, you can get deadlocks. You require IN SHARE ROW EXCLUSIVE MODE. other than that, it's a sucky solution because it breaks concurrency. In pgsql 8, you can do it using pl/pgsql exception handling. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Best practices: MERGE
On Tue, Mar 08, 2005 at 11:45:19AM +0800, Christopher Kings-Lynne wrote: The correct solution, as far as I can tell, is to acquire a LOCK on the table IN SHARE MODE at the beginning of the transaction, but this has (at least for many applications) unacceptable performance characteristics. Accepting that there is a slight risk of a race condition when *not* locking the table at the beginning of the transaction, what procedure minimizes this risk and recovers well from said race condition, should it occur? IN SHARE MODE is not enough, you can get deadlocks. You require IN SHARE ROW EXCLUSIVE MODE. other than that, it's a sucky solution because it breaks concurrency. In pgsql 8, you can do it using pl/pgsql exception handling. Luckily, PG 8 is available for this. Do you have a short example? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Best practices: MERGE
Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
