Horton

Re: support for MERGE

Erik Rijkers <er@xs4all.nl>

View thread View on postgr.es
From: Erik Rijkers <er@xs4all.nl>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>, Andrew Dunstan <andrew@dunslane.net>
Cc: Simon Riggs <simon.riggs@enterprisedb.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Zhihong Yu <zyu@yugabyte.com>, Daniel Westermann <dwe@dbi-services.com>, Amit Langote <amitlangote09@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Pavan Deolasee <pavan.deolasee@gmail.com>
Date: 2022-01-14T09:11:57Z
Lists: pgsql-hackers 
Op 13-01-2022 om 13:43 schreef Alvaro Herrera:
> Apologies, there was a merge failure and I failed to notice.  Here's the
> correct patch.
> 
> (This is also in github.com/alvherre/postgres/tree/merge-15)

> [20220113/v6-0001-MERGE-SQL-Command-following-SQL-2016.patch]

Good morning,


I got into this crash (may be the same as Jaime's):

#-----
# bash

t1=table1
t2=table2

psql -qX << SQL
drop table if exists $t1 cascade;
drop table if exists $t2 cascade;
create table $t1 as select /*cast(id::text as jsonb) js,*/ id from 
generate_series(1,20) as f(id);
create table $t2 as select /*cast(id::text as jsonb) js,*/ id from 
generate_series(1,20) as f(id);
delete from $t1 where id % 2 = 1;
delete from $t2 where id % 2 = 0;
(           select 't1 - target', count(*) t1 from $t1
   union all select 't2 - source', count(*) t2 from $t2 ) order by 1;

merge into $t1 as t1 using $t2 as t2 on t1.id = t2.id
when not matched and (t2.id > 10) and (t1.id > 10)
                  then  do nothing
when not matched then  insert values ( id )
when matched     then  do nothing ;

(           select 't1 - target', count(*) t1 from $t1
   union all select 't2 - source', count(*) t2 from $t2 ) order by 1 ;

SQL
#-----

Referencing alias 't1' in the WHEN NOT MATCHED member seems what 
triggers his crash: when I remove the phrase 'and (t1.id > 10)', the 
statement finishes correctly.


And I don't know if it is related but if I use this phrase:

when not matched and (id > 10)

I get:

ERROR:  column "id" does not exist
LINE 2: when not matched and id > 0 -- (t2.id > 10) and (t1.id > 10)
                              ^
HINT:  There is a column named "id" in table "t1", but it cannot be 
referenced from this part of the query.

Is that hint correct? Seems a bit strange.


Thanks,

Erik Rijkers

Commits

  1. Doc fixes for MERGE statement

  2. MERGE docs adjustments

  3. Link to MVCC docs in MERGE docs

  4. Fix EXPLAIN MERGE output when no tuples are processed

  5. Make EXPLAIN MERGE output format more compact

  6. Add support for MERGE SQL command

  7. Split ExecUpdate and ExecDelete into reusable pieces

  8. Add API of sorts for transition table handling in trigger.c

  9. Represent Lists as expansible arrays, not chains of cons-cells.