Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y3O4U-0004RR-5G for pgsql-performance@arkaria.postgresql.org; Tue, 23 Dec 2014 11:56:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y3O4S-0002wO-M5 for pgsql-performance@arkaria.postgresql.org; Tue, 23 Dec 2014 11:56:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y3O4R-0002wH-FL for pgsql-performance@postgresql.org; Tue, 23 Dec 2014 11:56:11 +0000 Received: from smtp1.oma.be ([193.190.144.13]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y3O4L-00055R-Qe for pgsql-performance@postgresql.org; Tue, 23 Dec 2014 11:56:09 +0000 Received: from snow.oma.be (snow.oma.be [193.190.249.132]) by smtp1.oma.be (8.14.3/8.14.3/SuSE Linux 0.8) with ESMTP id sBNBu3PL012871 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 23 Dec 2014 11:56:03 GMT From: Alessandro Ipe To: Tom Lane Cc: pgsql-performance@postgresql.org, Torsten Zuehlsdorff Subject: Re: Excessive memory used for INSERT Date: Tue, 23 Dec 2014 12:56:03 +0100 Message-ID: <6006964.iXUoRzG9pV@snow.oma.be> Organization: Royal Meteorological Institute of Belgium User-Agent: KMail/4.14.3 (Linux/3.7.10-1.1-desktop; KDE/4.14.3; x86_64; ; ) In-Reply-To: <17790.1418922345@sss.pgh.pa.us> References: <2163931.4eVnXNjHa3@snow.oma.be> <6053205.R7VxckQEVY@snow.oma.be> <17790.1418922345@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="nextPart6776947.cjbM0etGuf" Content-Transfer-Encoding: 7Bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multi-part message in MIME format. --nextPart6776947.cjbM0etGuf Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="iso-8859-15" Hi, I guess the memory consumption is depending on the size of my database, so only giving a reduced version of it won't allow to hit the issue. The pg_dumpall file of my database can be found at the address https://gerb.oma.be/owncloud/public.php?service=files&t=5e0e9e1bb06dce1d12c95662a9ee1c03 The queries causing the issue are given in files - tmp.OqOavPYbHa (with the new upsert_func function) - tmp.f60wlgEDWB (with WITH .. AS statement) I hope it will help. Thanks. Regards, A. On Thursday 18 December 2014 12:05:45 Tom Lane wrote: > Alessandro Ipe writes: > > Hi, > > I tried also with an upsert function > > CREATE FUNCTION upsert_func(sql_insert text, sql_update text) RETURNS void > > > > LANGUAGE plpgsql > > AS $$ > > > > BEGIN > > EXECUTE sql_update; > > IF FOUND THEN > > > > RETURN; > > > > END IF; > > BEGIN > > > > EXECUTE sql_insert; > > EXCEPTION WHEN OTHERS THEN > > EXECUTE sql_update; > > END; > > > > RETURN; > > > > END; > > $$; > > with the same result on the memory used... > > If you want to provide a self-contained test case, possibly we could look > into it, but these fragmentary bits of what you're doing don't really > constitute an investigatable problem statement. > > I will note that EXCEPTION blocks aren't terribly cheap, so if you're > reaching the "EXECUTE sql_insert" a lot of times that might have something > to do with it. > > regards, tom lane --nextPart6776947.cjbM0etGuf Content-Disposition: attachment; filename="tmp.OqOavPYbHa" Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="utf-8"; name="tmp.OqOavPYbHa" \set VERBOSITY terse \set ON_ERROR_STOP select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081530', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081530', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081530', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080400', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080400', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080400', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302082115', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302082115', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302082115', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080245', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080245', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080245', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080600', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080600', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080600', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081800', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081800', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081800', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081515', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081515', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081515', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080545', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080545', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080545', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081715', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081715', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081715', 'YYYYMMDDHH24MI') and MSG=3 $$); select upsert_func($$ insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081900', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' $$, $$ update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081900', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081900', 'YYYYMMDDHH24MI') and MSG=3 $$); --nextPart6776947.cjbM0etGuf Content-Disposition: attachment; filename="tmp.f60wlgEDWB" Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="utf-8"; name="tmp.f60wlgEDWB" \set VERBOSITY terse \set ON_ERROR_STOP BEGIN; WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081100', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081100', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081100', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302081100', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080515', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080515', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080515', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080515', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080815', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080815', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080815', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080815', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080945', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080945', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080945', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080945', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080930', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080930', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080930', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080930', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080200', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080200', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080200', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080200', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302081430', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302081430', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302081430', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302081430', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302082000', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302082000', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302082000', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302082000', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302080530', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302080530', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302080530', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302080530', 'YYYYMMDDHH24MI') and MSG=3); WITH upsert AS (update MSG set (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) = (to_timestamp('201302082100', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021') where slot=to_timestamp('201302082100', 'YYYYMMDDHH24MI') and MSG=3 RETURNING *) insert into MSG (slot,MSG,HRV,VIS006,VIS008,IR_016,IR_039,WV_062,WV_073,IR_087,IR_097,IR_108,IR_120,IR_134,PRO,EPI,CLM,TAPE) select to_timestamp('201302082100', 'YYYYMMDDHH24MI'),3,'\xffffff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\xff','\x01','\x01','\x7f','LTO5_021' WHERE NOT EXISTS (SELECT * FROM upsert WHERE slot=to_timestamp('201302082100', 'YYYYMMDDHH24MI') and MSG=3); COMMIT; --nextPart6776947.cjbM0etGuf Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --nextPart6776947.cjbM0etGuf--