Re: upsert doesn't seem to work..

On Tue, Feb 12, 2013 at 1:38 AM, Bert <biertie(at)gmail(dot)com> wrote:
> At first everything seems to run ok, however it seems that new fields aren't
> always inserted as desired.

Could you please explain how are you expecting the new fields to be
inserted and what exactly made you think they were inserted wrong?

>
>
> 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

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com