Hi,
In [1] I added some verification to projection building, to check if the
tupleslot passed to ExecBuildProjectionInfo() is compatible with the target
list. One query in merge.sql [2] got flagged.
Trying to debug that issue, I found another problem. This leaves us with:
1) w/ inheritance INSERTed rows are not returned by RETURNING. This seems to
just generally not work with MERGE
2) w/ inheritance the projection for insert that ExecInitMerge() builds
sometimes uses mismatching columns between the slot passed to
ExecBuildProjectionInfo() and tgtslot. The only reason this doesn't seem
to immediately crash is 1).
E.g. the attached repro-merge-inheritance.sql triggers [3] if the attached
debugging patch is applied.
I wouldn't be surprised if there were other issues around inheritance with
mismatched column (order), but I didn't look further.
Greetings,
Andres Freund
[1] https://postgr.es/m/smj2mopf4t654xinuyyq4azik2d5zp2g3lsv2o7vficegqq5c5%40v35iq4llqjpw
[2] MERGE into measurement m
USING new_measurement nm ON
(m.city_id = nm.city_id and m.logdate=nm.logdate)
WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
WHEN MATCHED THEN UPDATE
SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
unitsales = m.unitsales + coalesce(nm.unitsales, 0)
WHEN NOT MATCHED THEN INSERT
(city_id, logdate, peaktemp, unitsales)
VALUES (city_id, logdate, peaktemp, unitsales);
triggers
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: type mismatch: resno 1: slot (type: 0,
name:........pg.dropped.1........) vs expr (type: 23, name: city_id)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL: {TARGETENTRY
:expr
{VAR
:varno -1
:varattno 3
:vartype 23
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 2
:varattnosyn 1
:location 379
}
:resno 1
:resname city_id
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: type mismatch: resno 2: slot (type: 23,
name:peaktemp) vs expr (type: 1082, name: logdate)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL: {TARGETENTRY
:expr
{VAR
:varno -1
:varattno 4
:vartype 1082
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 2
:varattnosyn 2
:location 388
}
:resno 2
:resname logdate
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: type mismatch: resno 3: slot (type: 1082,
name:logdate) vs expr (type: 23, name: peaktemp)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL: {TARGETENTRY
:expr
{VAR
:varno -1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 2
:varattnosyn 3
:location 397
}
:resno 3
:resname peaktemp
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: type mismatch: resno 5: slot (type: 23,
name:unitsales) vs expr (type: 25, name: last_action)
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] WARNING: TargetEntry:
2025-05-20 14:32:31.039 EDT [1617074][client backend][0/49:0][psql] DETAIL: {TARGETENTRY
:expr
{CONST
:consttype 25
:consttypmod -1
:constcollid 100
:constlen -1
:constbyval false
:constisnull false
:location 418
:constvalue 16 [ 64 0 0 0 109 101 114 103 101 45 105 110 115 101 114 116
]
}
:resno 5
:resname last_action
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
[3] 2025-05-21 11:24:55.111 EDT [1838296][client backend][0/16:0][psql] WARNING: type mismatch: resno 1: slot (type:
1700,name: dropme) vs expr (type: 25, name: key)
2025-05-21 11:24:55.112 EDT [1838296][client backend][0/16:0][psql] WARNING: TargetEntry:
2025-05-21 11:24:55.112 EDT [1838296][client backend][0/16:0][psql] DETAIL: {TARGETENTRY
:expr
{VAR
:varno -1
:varattno 1
:vartype 25
:vartypmod -1
:varcollid 100
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 2
:varattnosyn 1
:location 179
}
:resno 1
:resname key
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
