MERGE and parsing with prepared statements
Justin Pryzby <pryzby@telsasoft.com>
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-hackers@postgresql.org
Date: 2022-07-14T16:26:18Z
Lists: pgsql-hackers
We've used INSERT ON CONFLICT for a few years (with partitions as the target).
That's also combined with prepared statements, for bulk loading.
I was looking to see if we should use MERGE (probably not, but looking anyway).
And came across this behavior. I'm not sure if it's any issue.
CREATE TABLE CustomerAccount (CustomerId int, Balance float);
PREPARE p AS
MERGE INTO CustomerAccount CA
USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T
ON CA.CustomerId = T.CustomerId
WHEN NOT MATCHED THEN
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
UPDATE SET Balance = Balance + TransactionValue;
ERROR: operator does not exist: integer = text
LINE 3: ON CA.CustomerId = T.CustomerId
postgres: pryzbyj postgres [local] PREPARE(+0x2337be) [0x56108322e7be]
postgres: pryzbyj postgres [local] PREPARE(oper+0x198) [0x56108322f1fb]
postgres: pryzbyj postgres [local] PREPARE(make_op+0x7e) [0x56108322f55a]
postgres: pryzbyj postgres [local] PREPARE(+0x228f2b) [0x561083223f2b]
postgres: pryzbyj postgres [local] PREPARE(+0x227aa9) [0x561083222aa9]
postgres: pryzbyj postgres [local] PREPARE(transformExpr+0x1c) [0x5610832227f9]
postgres: pryzbyj postgres [local] PREPARE(transformMergeStmt+0x339) [0x56108322d988]
postgres: pryzbyj postgres [local] PREPARE(transformStmt+0x70) [0x5610831f4071]
postgres: pryzbyj postgres [local] PREPARE(+0x1fa350) [0x5610831f5350]
postgres: pryzbyj postgres [local] PREPARE(transformTopLevelStmt+0x11) [0x5610831f5385]
postgres: pryzbyj postgres [local] PREPARE(parse_analyze_varparams+0x5b) [0x5610831f54f4]
postgres: pryzbyj postgres [local] PREPARE(pg_analyze_and_rewrite_varparams+0x38) [0x5610834bcdfe]
postgres: pryzbyj postgres [local] PREPARE(PrepareQuery+0xcc) [0x561083292155]
postgres: pryzbyj postgres [local] PREPARE(standard_ProcessUtility+0x4ea) [0x5610834c31a0]
Why is $1 construed to be of type text ?
Commits
-
Doc fixes for MERGE statement
- 5dcebd10baa8 16.0 landed
- 5bb88967ed5c 15.0 landed
-
MERGE docs adjustments
- d6d1fbf353d1 15.0 landed
- 3d895bc846f2 16.0 landed
-
Link to MVCC docs in MERGE docs
- ffffeebf2447 15.0 landed
-
Fix EXPLAIN MERGE output when no tuples are processed
- 12e423e21d8e 15.0 landed
-
Make EXPLAIN MERGE output format more compact
- 598ac10be1c2 15.0 landed
-
Add support for MERGE SQL command
- 7103ebb7aae8 15.0 landed
-
Split ExecUpdate and ExecDelete into reusable pieces
- 25e777cf8e54 15.0 landed
-
Add API of sorts for transition table handling in trigger.c
- 3a46a45f6f00 15.0 landed
-
Represent Lists as expansible arrays, not chains of cons-cells.
- 1cff1b95ab6d 13.0 cited