Postgres Pro
Downloads
Home   >   mailing lists

upsert doesn't seem to work.. - Mailing list pgsql-sql

From Bert
Subject upsert doesn't seem to work..
Date
Msg-id CAFCtE1kz502Wk6MhpBAPKGUWXsNKOk=F-54VhPkCUd=agq=X6g@mail.gmail.com
Whole thread Raw
Responses Re: upsert doesn't seem to work..
Re: upsert doesn't seem to work..
List pgsql-sql
Tree view
Hello,

I hope someone her can help me.

We continuously load data from flat files in our database.
We first insert the data into unlogged tables (in the loadoltp schema), and then we use the 'upsert' statement to transfer the data from the load table into the tables we are going to use.

The load tables are unlogged, and don't have indexes / pk's on them. All our 'real tables', which contains the data, always have a pk consisting out of 2 fields. In the example those are 'tick_server_id' and 'item_id'.

At first everything seems to run ok, however it seems that new fields aren't always inserted as desired.


This is an example query which causes troubles:

WITH UPSERT AS
  (UPDATE oltp.ST_ITEM ET
   SET (tick_server_id,
        item_id,
        item_desc,
        item_code,
        item_date,
        item_starttime,
        item_endtime,
        item_startsaledate,
        item_endsaledate,
        replev_id,
        evt_id,
        tkl_id,
        plan_id,
        itemtyp_id,
        item_accountcode,
        itemstat_id,
        item_seattotal_count,
        item_seatsold_count,
        tl_id,
        item_hotsales,
        item_showplan,
        item_sms_code,
        datetyp_id,
        item_start,
        item_end,
        item_validfrom,
        item_validuntil,
        item_count_sale,
        bartyp_id,
        item_scanning,
        isabo,
        etl_run_id) = (E.tick_server_id,
                       E.item_id,
                       E.item_desc,
                       E.item_code,
                       E.item_date,
                       E.item_starttime,
                       E.item_endtime,
                       E.item_startsaledate,
                       E.item_endsaledate,
                       E.replev_id,
                       E.evt_id,
                       E.tkl_id,
                       E.plan_id,
                       E.itemtyp_id,
                       E.item_accountcode,
                       E.itemstat_id,
                       E.item_seattotal_count,
                       E.item_seatsold_count,
                       E.tl_id,
                       E.item_hotsales,
                       E.item_showplan,
                       E.item_sms_code,
                       E.datetyp_id,
                       E.item_start,
                       E.item_end,
                       E.item_validfrom,
                       E.item_validuntil,
                       E.item_count_sale,
                       E.bartyp_id,
                       E.item_scanning,
                       E.isabo,
                       E.etl_run_id)
   FROM
     (SELECT *
      FROM loadoltp.ST_ITEM_INSERT
      WHERE LOADTABLETIME = '2013-02-12 10:23:51.110877') AS E
   WHERE et.tick_server_id = e.tick_server_id
     AND et.item_id = e.item_id returning ET.*)
INSERT INTO oltp.ST_ITEM
SELECT tick_server_id,
       item_id,
       item_desc,
       item_code,
       item_date,
       item_starttime,
       item_endtime,
       item_startsaledate,
       item_endsaledate,
       replev_id,
       evt_id,
       tkl_id,
       plan_id,
       itemtyp_id,
       item_accountcode,
       itemstat_id,
       item_seattotal_count,
       item_seatsold_count,
       tl_id,
       item_hotsales,
       item_showplan,
       item_sms_code,
       datetyp_id,
       item_start,
       item_end,
       item_validfrom,
       item_validuntil,
       item_count_sale,
       bartyp_id,
       item_scanning,
       isabo,
       etl_run_id
FROM
  (SELECT *
   FROM loadoltp.ST_ITEM_INSERT
   WHERE LOADTABLETIME = '2013-02-12 10:23:51.110877') AS ET
WHERE ET.tick_server_id NOT IN
    (SELECT ET.tick_server_id
     FROM upsert b)
  AND ET.item_id NOT IN
    (SELECT ET.item_id
     FROM upsert b)


this is the query plan:
"Insert on oltp.st_item  (cost=776.69..1123.53 rows=93 width=419)"
"  CTE upsert"
"    ->  Update on oltp.st_item et  (cost=23.26..776.69 rows=39 width=431)"
"          Output: et.tick_server_id, et.item_id, et.item_desc, et.item_code, et.item_date, et.item_starttime, et.item_endtime, et.item_startsaledate, et.item_endsaledate, et.replev_id, et.evt_id, et.tkl_id, et.plan_id, et.itemtyp_id, et.item_accountcode, et.itemstat_id, et.item_seattotal_count, et.item_seatsold_count, et.tl_id, et.item_hotsales, et.item_showplan, et.item_sms_code, et.datetyp_id, et.item_start, et.item_end, et.item_validfrom, et.item_validuntil, et.item_count_sale, et.bartyp_id, et.item_scanning, et.isabo, et.etl_run_id"
"          ->  Hash Join  (cost=23.26..776.69 rows=39 width=431)"
"                Output: loadoltp.st_item_insert.tick_server_id, loadoltp.st_item_insert.item_id, loadoltp.st_item_insert.item_desc, loadoltp.st_item_insert.item_code, loadoltp.st_item_insert.item_date, loadoltp.st_item_insert.item_starttime, loadoltp.st_item_insert.item_endtime, loadoltp.st_item_insert.item_startsaledate, loadoltp.st_item_insert.item_endsaledate, loadoltp.st_item_insert.replev_id, loadoltp.st_item_insert.evt_id, loadoltp.st_item_insert.tkl_id, loadoltp.st_item_insert.plan_id, loadoltp.st_item_insert.itemtyp_id, loadoltp.st_item_insert.item_accountcode, loadoltp.st_item_insert.itemstat_id, loadoltp.st_item_insert.item_seattotal_count, loadoltp.st_item_insert.item_seatsold_count, loadoltp.st_item_insert.tl_id, loadoltp.st_item_insert.item_hotsales, loadoltp.st_item_insert.item_showplan, loadoltp.st_item_insert.item_sms_code, loadoltp.st_item_insert.datetyp_id, loadoltp.st_item_insert.item_start, loadoltp.st_item_insert.item_end, loadoltp.st_item_insert.item_validfrom, loadoltp.st_item_insert.item_validuntil, loadoltp.st_item_insert.item_count_sale, loadoltp.st_item_insert.bartyp_id, loadoltp.st_item_insert.item_scanning, loadoltp.st_item_insert.isabo, loadoltp.st_item_insert.etl_run_id, et.ctid, loadoltp.st_item_insert.ctid"
"                Hash Cond: ((et.tick_server_id = loadoltp.st_item_insert.tick_server_id) AND (et.item_id = loadoltp.st_item_insert.item_id))"
"                ->  Seq Scan on oltp.st_item et  (cost=0.00..670.74 rows=10974 width=14)"
"                      Output: et.ctid, et.tick_server_id, et.item_id"
"                ->  Hash  (cost=17.66..17.66 rows=373 width=425)"
"                      Output: loadoltp.st_item_insert.tick_server_id, loadoltp.st_item_insert.item_id, loadoltp.st_item_insert.item_desc, loadoltp.st_item_insert.item_code, loadoltp.st_item_insert.item_date, loadoltp.st_item_insert.item_starttime, loadoltp.st_item_insert.item_endtime, loadoltp.st_item_insert.item_startsaledate, loadoltp.st_item_insert.item_endsaledate, loadoltp.st_item_insert.replev_id, loadoltp.st_item_insert.evt_id, loadoltp.st_item_insert.tkl_id, loadoltp.st_item_insert.plan_id, loadoltp.st_item_insert.itemtyp_id, loadoltp.st_item_insert.item_accountcode, loadoltp.st_item_insert.itemstat_id, loadoltp.st_item_insert.item_seattotal_count, loadoltp.st_item_insert.item_seatsold_count, loadoltp.st_item_insert.tl_id, loadoltp.st_item_insert.item_hotsales, loadoltp.st_item_insert.item_showplan, loadoltp.st_item_insert.item_sms_code, loadoltp.st_item_insert.datetyp_id, loadoltp.st_item_insert.item_start, loadoltp.st_item_insert.item_end, loadoltp.st_item_insert.item_validfrom, loadoltp.st_item_insert.item_validuntil, loadoltp.st_item_insert.item_count_sale, loadoltp.st_item_insert.bartyp_id, loadoltp.st_item_insert.item_scanning, loadoltp.st_item_insert.isabo, loadoltp.st_item_insert.etl_run_id, loadoltp.st_item_insert.ctid"
"                      ->  Seq Scan on loadoltp.st_item_insert  (cost=0.00..17.66 rows=373 width=425)"
"                            Output: loadoltp.st_item_insert.tick_server_id, loadoltp.st_item_insert.item_id, loadoltp.st_item_insert.item_desc, loadoltp.st_item_insert.item_code, loadoltp.st_item_insert.item_date, loadoltp.st_item_insert.item_starttime, loadoltp.st_item_insert.item_endtime, loadoltp.st_item_insert.item_startsaledate, loadoltp.st_item_insert.item_endsaledate, loadoltp.st_item_insert.replev_id, loadoltp.st_item_insert.evt_id, loadoltp.st_item_insert.tkl_id, loadoltp.st_item_insert.plan_id, loadoltp.st_item_insert.itemtyp_id, loadoltp.st_item_insert.item_accountcode, loadoltp.st_item_insert.itemstat_id, loadoltp.st_item_insert.item_seattotal_count, loadoltp.st_item_insert.item_seatsold_count, loadoltp.st_item_insert.tl_id, loadoltp.st_item_insert.item_hotsales, loadoltp.st_item_insert.item_showplan, loadoltp.st_item_insert.item_sms_code, loadoltp.st_item_insert.datetyp_id, loadoltp.st_item_insert.item_start, loadoltp.st_item_insert.item_end, loadoltp.st_item_insert.item_validfrom, loadoltp.st_item_insert.item_validuntil, loadoltp.st_item_insert.item_count_sale, loadoltp.st_item_insert.bartyp_id, loadoltp.st_item_insert.item_scanning, loadoltp.st_item_insert.isabo, loadoltp.st_item_insert.etl_run_id, loadoltp.st_item_insert.ctid"
"                            Filter: (loadoltp.st_item_insert.loadtabletime = '2013-02-12 10:23:51.110877'::timestamp without time zone)"
"  ->  Seq Scan on loadoltp.st_item_insert  (cost=0.00..346.83 rows=93 width=419)"
"        Output: loadoltp.st_item_insert.tick_server_id, loadoltp.st_item_insert.item_id, loadoltp.st_item_insert.item_desc, loadoltp.st_item_insert.item_code, loadoltp.st_item_insert.item_date, loadoltp.st_item_insert.item_starttime, loadoltp.st_item_insert.item_endtime, loadoltp.st_item_insert.item_startsaledate, loadoltp.st_item_insert.item_endsaledate, loadoltp.st_item_insert.replev_id, loadoltp.st_item_insert.evt_id, loadoltp.st_item_insert.tkl_id, loadoltp.st_item_insert.plan_id, loadoltp.st_item_insert.itemtyp_id, loadoltp.st_item_insert.item_accountcode, loadoltp.st_item_insert.itemstat_id, loadoltp.st_item_insert.item_seattotal_count, loadoltp.st_item_insert.item_seatsold_count, loadoltp.st_item_insert.tl_id, loadoltp.st_item_insert.item_hotsales, loadoltp.st_item_insert.item_showplan, loadoltp.st_item_insert.item_sms_code, loadoltp.st_item_insert.datetyp_id, loadoltp.st_item_insert.item_start, loadoltp.st_item_insert.item_end, loadoltp.st_item_insert.item_validfrom, loadoltp.st_item_insert.item_validuntil, loadoltp.st_item_insert.item_count_sale, loadoltp.st_item_insert.bartyp_id, loadoltp.st_item_insert.item_scanning, loadoltp.st_item_insert.isabo, loadoltp.st_item_insert.etl_run_id"
"        Filter: ((loadoltp.st_item_insert.loadtabletime = '2013-02-12 10:23:51.110877'::timestamp without time zone) AND (NOT (SubPlan 2)) AND (NOT (SubPlan 3)))"
"        SubPlan 2"
"          ->  CTE Scan on upsert b  (cost=0.00..0.78 rows=39 width=0)"
"                Output: loadoltp.st_item_insert.tick_server_id"
"        SubPlan 3"
"          ->  CTE Scan on upsert b  (cost=0.00..0.78 rows=39 width=0)"
"                Output: loadoltp.st_item_insert.item_id"

Can anyone see what I'm doing wrong?

wkr,
Bert

pgsql-sql by date:

Previous
From: Jasen Betts
Date:
Subject: Re: Conditional expression in an UPDATE statement
Next
From: Sergey Konoplev
Date:
Subject: Re: upsert doesn't seem to work..