upsert doesn't seem to work..

Поиск
Список
Период
Сортировка
От Bert
Тема upsert doesn't seem to work..
Дата
Msg-id CAFCtE1kz502Wk6MhpBAPKGUWXsNKOk=F-54VhPkCUd=agq=X6g@mail.gmail.com
обсуждение исходный текст
Ответы Re: upsert doesn't seem to work..  (Sergey Konoplev <gray.ru@gmail.com>)
Re: upsert doesn't seem to work..  (Ben Morrow <ben@morrow.me.uk>)
Список pgsql-sql
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Jasen Betts
Дата:
Сообщение: Re: Conditional expression in an UPDATE statement
Следующее
От: Sergey Konoplev
Дата:
Сообщение: Re: upsert doesn't seem to work..