pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedMERGE and parsing with prepared statements
16+ messages / 6 participants
[nested] [flat]
Commits from this thread:
2022-09-09 5dcebd10ba 5bb88967ed Doc fixes for MERGE statement
2022-08-12 3d895bc846 d6d1fbf353 MERGE docs adjustments
* MERGE and parsing with prepared statements
@ 2022-07-14 16:26 Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Justin Pryzby @ 2022-07-14 16:26 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: 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 ?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-07-15 09:25 ` Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 16+ messages in thread
From: Matthias van de Meent @ 2022-07-15 09:25 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-hackers
On Thu, 14 Jul 2022, 18:26 Justin Pryzby, <pryzby@telsasoft.com> wrote:
>
> 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
>
> Why is $1 construed to be of type text ?
The select statement that generates the row type of T `(select $1 CID,
$2 TxV) AS T` does not put type bounds on the input parameters, so it
remains `unknown` for the scope of that subselect. Once stored into
the row type, the type of that column defaults to text, as a row type
should not have 'unknown'-typed columns.
You'll see the same issue with other subselects that select input
parameters without casts, such as `select a from (select $1 a) A where
A.a = 1;`. It's a pre-existing issue that has popped up earlier, and I
think it's not something we've planned to fix in backbranches.
Kind regards,
Matthias van de Meent
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
@ 2022-07-15 17:58 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 16+ messages in thread
From: Justin Pryzby @ 2022-07-15 17:58 UTC (permalink / raw)
To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-hackers
On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote:
> On Thu, 14 Jul 2022, 18:26 Justin Pryzby, <pryzby@telsasoft.com> wrote:
> >
> > Why is $1 construed to be of type text ?
>
> The select statement that generates the row type of T `(select $1 CID,
> $2 TxV) AS T` does not put type bounds on the input parameters, so it
> remains `unknown` for the scope of that subselect. Once stored into
> the row type, the type of that column defaults to text, as a row type
> should not have 'unknown'-typed columns.
Thanks for looking into it.
I see now that the same thing can happen with "ON CONFLICT" if used with a
subselect.
PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a
ON CONFLICT (i) DO UPDATE SET i=excluded.i;
ERROR: column "i" is of type integer but expression is of type text
It seems a bit odd that it's impossible to use merge with prepared statements
without specifically casting the source types (which I did now to continue my
experiment).
--
Justin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-07-15 18:40 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:14 ` Re: MERGE and parsing with prepared statements Tom Lane <tgl@sss.pgh.pa.us>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Alvaro Herrera @ 2022-07-15 18:40 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On 2022-Jul-15, Justin Pryzby wrote:
> On Fri, Jul 15, 2022 at 11:25:35AM +0200, Matthias van de Meent wrote:
> Thanks for looking into it.
Definitely! Thanks, Matthias.
> I see now that the same thing can happen with "ON CONFLICT" if used with a
> subselect.
>
> PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a
> ON CONFLICT (i) DO UPDATE SET i=excluded.i;
> ERROR: column "i" is of type integer but expression is of type text
Right, I didn't think that MERGE was doing anything peculiar in this
respect.
> It seems a bit odd that it's impossible to use merge with prepared statements
> without specifically casting the source types (which I did now to continue my
> experiment).
I have no comments on this. Maybe it can be improved, but I don't know
how.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-07-15 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2022-07-15 19:14 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2022-Jul-15, Justin Pryzby wrote:
>> I see now that the same thing can happen with "ON CONFLICT" if used with a
>> subselect.
>>
>> PREPARE p AS INSERT INTO t SELECT a FROM (SELECT $1 AS a)a
>> ON CONFLICT (i) DO UPDATE SET i=excluded.i;
>> ERROR: column "i" is of type integer but expression is of type text
> Right, I didn't think that MERGE was doing anything peculiar in this
> respect.
Yeah. The current theory about this is that if we haven't assigned a
type to an unknown-type parameter (or literal) that is an output
column of a sub-SELECT, we will as a rule force it to text.
That MERGE USING clause is a sub-SELECT, so that rule applies.
There is a hoary old exception to that rule, which is that if you
write INSERT INTO tab SELECT ..., $1, ...
we will figure out the type of the column of "tab" that $1 is going
into, and force $1 to that type instead of text. It looks like this
also works in INSERT ... VALUES. You could make a case that MERGE
should be equally smart, but it's not clear to me that the info is
available sufficiently close by to make it reasonable to do that.
It looks like the MERGE syntax has a couple of levels of indirection,
which'd probably be enough to put the kibosh on that idea -- in
particular, AFAICS there might not be a unique target column
corresponding to a given data_source column.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-07-15 19:17 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 16+ messages in thread
From: David G. Johnston @ 2022-07-15 19:17 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2022-Jul-15, Justin Pryzby wrote:
>
> > It seems a bit odd that it's impossible to use merge with prepared
> statements
> > without specifically casting the source types (which I did now to
> continue my
> > experiment).
>
> I have no comments on this. Maybe it can be improved, but I don't know
> how.
>
>
Not tested, but the example prepare command fails to make use of the
optional data types specification. Using that should remove the need to
cast the parameter placeholder within the query itself.
That said, in theory the INSERT specification of the MERGE could be used to
either resolve unknowns or even forcibly convert the data types of the
relation produced by the USING clause to match the actual types required
for the INSERT (since that will happen at insert time anyway). This would
make the UPDATE behavior slightly different than a top-level UPDATE command
though, since that does not have the same context information.
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
@ 2022-07-15 19:40 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:53 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Justin Pryzby @ 2022-07-15 19:40 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 12:17:51PM -0700, David G. Johnston wrote:
> On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> > On 2022-Jul-15, Justin Pryzby wrote:
> >
> > > It seems a bit odd that it's impossible to use merge with prepared statements
> > > without specifically casting the source types (which I did now to continue my
> > > experiment).
> >
> > I have no comments on this. Maybe it can be improved, but I don't know
> > how.
>
> Not tested, but the example prepare command fails to make use of the
> optional data types specification. Using that should remove the need to
> cast the parameter placeholder within the query itself.
What optional data type specification ?
> That said, in theory the INSERT specification of the MERGE could be used to
> either resolve unknowns or even forcibly convert the data types of the
> relation produced by the USING clause to match the actual types required
> for the INSERT (since that will happen at insert time anyway).
Yeah. I hadn't looked before, but just noticed this:
https://www.postgresql.org/docs/devel/sql-merge.html
| If the expression for any column is not of the correct data type, automatic type conversion will be attempted.
That appears to be copied from the INSERT page.
What does that mean, if not that data types will be resolved as needed ?
Note that if I add casts to the "ON" condition, MERGE complains about the
INSERT VALUES.
PREPARE p AS
MERGE INTO CustomerAccount CA
USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T
ON CA.CustomerId = T.CustomerId::int
WHEN NOT MATCHED THEN
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
UPDATE SET Balance = Balance + TransactionValue;
ERROR: column "customerid" is of type integer but expression is of type text
LINE 7: VALUES (T.CustomerId, T.TransactionValue)
postgres: pryzbyj postgres [local] PREPARE(transformAssignedExpr+0x3b6) [0x5605f9699e8e]
postgres: pryzbyj postgres [local] PREPARE(transformInsertRow+0x2ce) [0x5605f9653a47]
postgres: pryzbyj postgres [local] PREPARE(transformMergeStmt+0x7ec) [0x5605f968fe3b]
postgres: pryzbyj postgres [local] PREPARE(transformStmt+0x70) [0x5605f9656071]
postgres: pryzbyj postgres [local] PREPARE(+0x1fa350) [0x5605f9657350]
postgres: pryzbyj postgres [local] PREPARE(transformTopLevelStmt+0x11) [0x5605f9657385]
postgres: pryzbyj postgres [local] PREPARE(parse_analyze_varparams+0x5b) [0x5605f96574f4]
postgres: pryzbyj postgres [local] PREPARE(pg_analyze_and_rewrite_varparams+0x38) [0x5605f991edfe]
postgres: pryzbyj postgres [local] PREPARE(PrepareQuery+0xcc) [0x5605f96f4155]
postgres: pryzbyj postgres [local] PREPARE(standard_ProcessUtility+0x4ea) [0x5605f99251a0]
postgres: pryzbyj postgres [local] PREPARE(ProcessUtility+0xdb) [0x5605f992587e]
--
Justin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-07-15 19:53 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: David G. Johnston @ 2022-07-15 19:53 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> On Fri, Jul 15, 2022 at 12:17:51PM -0700, David G. Johnston wrote:
> > On Fri, Jul 15, 2022 at 11:40 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
> > > On 2022-Jul-15, Justin Pryzby wrote:
> > >
> > > > It seems a bit odd that it's impossible to use merge with prepared
> statements
> > > > without specifically casting the source types (which I did now to
> continue my
> > > > experiment).
> > >
> > > I have no comments on this. Maybe it can be improved, but I don't know
> > > how.
> >
> > Not tested, but the example prepare command fails to make use of the
> > optional data types specification. Using that should remove the need to
> > cast the parameter placeholder within the query itself.
>
> What optional data type specification ?
>
The one documented here:
https://www.postgresql.org/docs/current/sql-prepare.html
PREPARE name [ ( data_type [, ...] ) ] AS statement
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-07-15 19:59 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 16+ messages in thread
From: David G. Johnston @ 2022-07-15 19:59 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> That appears to be copied from the INSERT page.
> What does that mean, if not that data types will be resolved as needed ?
>
Yep, and the system needs to resolve the type at a point where there is no
contextual information and so it chooses text.
> Note that if I add casts to the "ON" condition, MERGE complains about the
> INSERT VALUES.
>
> PREPARE p AS
> MERGE INTO CustomerAccount CA
> USING (SELECT $1 AS CustomerId, $2 AS TransactionValue) AS T
> ON CA.CustomerId = T.CustomerId::int
> WHEN NOT MATCHED THEN
> INSERT (CustomerId, Balance)
> VALUES (T.CustomerId, T.TransactionValue)
> WHEN MATCHED THEN
> UPDATE SET Balance = Balance + TransactionValue;
>
> ERROR: column "customerid" is of type integer but expression is of type
> text
> LINE 7: VALUES (T.CustomerId, T.TransactionValue)
>
>
Noted. Not surprised. That error was always present, it's just that the
join happens first. Since your fix narrowly targeted the join this error
remained to be discovered.
David J.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
@ 2022-07-15 20:43 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-18 13:20 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 16+ messages in thread
From: Justin Pryzby @ 2022-07-15 20:43 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 12:59:34PM -0700, David G. Johnston wrote:
> On Fri, Jul 15, 2022 at 12:40 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> >
> | If the expression for any column is not of the correct data type, automatic type conversion will be attempted.
> > That appears to be copied from the INSERT page.
> > What does that mean, if not that data types will be resolved as needed ?
>
> Yep, and the system needs to resolve the type at a point where there is no
> contextual information and so it chooses text.
I don't know if that's a viable interpretation. The parser "resolved the type"
by assuming a default, which caused an error before finishing parsing.
In the case of INSERT, "conversion will be attempted" means it looks for a cast
from the source type to the target type, and its "automatic type conversion"
will fail if (for example) you try to insert a timestamp into an int.
In the case of MERGE, that same sentence evidently means that it assumes a
default source type (rather than looking for a cast) and then fails if it
doesn't exactly match the target type.
Should that sentence be removed from MERGE ?
--
Justin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-07-18 13:20 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 16+ messages in thread
From: Justin Pryzby @ 2022-07-18 13:20 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers
On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote:
> Should that sentence be removed from MERGE ?
Also, I think these examples should be more similar.
doc/src/sgml/ref/merge.sgml
> <programlisting>
> MERGE INTO CustomerAccount CA
> USING RecentTransactions T
> ON T.CustomerId = CA.CustomerId
> WHEN MATCHED THEN
> UPDATE SET Balance = Balance + TransactionValue
> WHEN NOT MATCHED THEN
> INSERT (CustomerId, Balance)
> VALUES (T.CustomerId, T.TransactionValue);
> </programlisting>
> </para>
>
> <para>
> Notice that this would be exactly equivalent to the following
> statement because the <literal>MATCHED</literal> result does not change
> during execution.
>
> <programlisting>
> MERGE INTO CustomerAccount CA
> USING (Select CustomerId, TransactionValue From RecentTransactions) 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;
> </programlisting>
> </para>
The "ON" lines can be the same.
The "MATCHED" can be in the same order.
--
Justin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
@ 2022-08-12 11:20 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-12 11:46 ` Re: MERGE and parsing with prepared statements Simon Riggs <simon.riggs@enterprisedb.com>
1 sibling, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2022-08-12 11:20 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers@postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Amit Langote <amitlangote09@gmail.com>; Japin Li <japinli@hotmail.com>; Zhihong Yu <zyu@yugabyte.com>; Simon Riggs <simon.riggs@enterprisedb.com>; pgsql-hackers@lists.postgresql.org, Tomas Vondra <tomas.vondra@enterprisedb.com>; Daniel Westermann <dwe@dbi-services.com>; Erik Rijkers <er@xs4all.nl>; Jaime Casanova <jcasanov@systemguards.com.ec>; Andres Freund <andres@anarazel.de>
On 2022-Jul-15, Justin Pryzby wrote:
> Should that sentence be removed from MERGE ?
Removed
On 2022-Jul-18, Justin Pryzby wrote:
> On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote:
> > Should that sentence be removed from MERGE ?
>
> Also, I think these examples should be more similar.
Agreed, done.
On 2022-Aug-09, Justin Pryzby wrote:
> On Tue, Aug 09, 2022 at 11:48:23AM +0200, Álvaro Herrera wrote:
> > So I propose to leave it as
> >
> > If <command>MERGE</command> attempts an <command>INSERT</command>
> > and a unique index is present and a duplicate row is concurrently
> > inserted, then a uniqueness violation error is raised;
> > <command>MERGE</command> does not attempt to avoid such
> > errors by restarting evaluation of <literal>MATCHED</literal>
> > conditions.
>
> I think by "leave it as" you mean "change it to".
> (Meaning, without referencing UPDATE).
Yes. I suppose we could add a parenthical comment, given that it's
likely the most popular option? Feel free to suggest something
specific.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"People get annoyed when you try to debug them." (Larry Wall)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-12 11:46 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-12 11:53 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-09-09 11:58 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 16+ messages in thread
From: Simon Riggs @ 2022-08-12 11:46 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; David G. Johnston <david.g.johnston@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Amit Langote <amitlangote09@gmail.com>; Japin Li <japinli@hotmail.com>; Zhihong Yu <zyu@yugabyte.com>; PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Daniel Westermann <dwe@dbi-services.com>; Erik Rijkers <er@xs4all.nl>; Jaime Casanova <jcasanov@systemguards.com.ec>; Andres Freund <andres@anarazel.de>
On Fri, 12 Aug 2022 at 12:20, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2022-Jul-18, Justin Pryzby wrote:
>
> > On Fri, Jul 15, 2022 at 03:43:41PM -0500, Justin Pryzby wrote:
> > > Should that sentence be removed from MERGE ?
> >
> > Also, I think these examples should be more similar.
>
> Agreed, done.
Sorry, but I disagree with this chunk in the latest commit,
specifically, changing the MATCHED from after to before the NOT
MATCHED clause.
The whole point of the second example was to demonstrate that the
order of the MATCHED/NOT MATCHED clauses made no difference.
By changing the examples so they are the same, the sentence at line
573 now makes no sense.
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-12 11:46 ` Re: MERGE and parsing with prepared statements Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-08-12 11:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-19 13:30 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 16+ messages in thread
From: Alvaro Herrera @ 2022-08-12 11:53 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; David G. Johnston <david.g.johnston@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Amit Langote <amitlangote09@gmail.com>; Japin Li <japinli@hotmail.com>; Zhihong Yu <zyu@yugabyte.com>; PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Daniel Westermann <dwe@dbi-services.com>; Erik Rijkers <er@xs4all.nl>; Jaime Casanova <jcasanov@systemguards.com.ec>; Andres Freund <andres@anarazel.de>
On 2022-Aug-12, Simon Riggs wrote:
> Sorry, but I disagree with this chunk in the latest commit,
> specifically, changing the MATCHED from after to before the NOT
> MATCHED clause.
>
> The whole point of the second example was to demonstrate that the
> order of the MATCHED/NOT MATCHED clauses made no difference.
>
> By changing the examples so they are the same, the sentence at line
> 573 now makes no sense.
Hmm, I thought the point of the example was to show that you can replace
the table in the USING clause with a query that retrieves the column;
but you're right, we lost the thing there. Maybe it was too subtle to
the point that I failed to understand it. Perhaps we can put it back
the way it was and explain these two differences (change of data source
*and* clause ordering) more explicitly.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"La virtud es el justo medio entre dos defectos" (Aristóteles)
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-12 11:46 ` Re: MERGE and parsing with prepared statements Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-12 11:53 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2022-08-19 13:30 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 16+ messages in thread
From: Justin Pryzby @ 2022-08-19 13:30 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Simon Riggs <simon.riggs@enterprisedb.com>; David G. Johnston <david.g.johnston@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Amit Langote <amitlangote09@gmail.com>; Japin Li <japinli@hotmail.com>; Zhihong Yu <zyu@yugabyte.com>; PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Daniel Westermann <dwe@dbi-services.com>; Erik Rijkers <er@xs4all.nl>; Jaime Casanova <jcasanov@systemguards.com.ec>; Andres Freund <andres@anarazel.de>
On Fri, Aug 12, 2022 at 01:53:25PM +0200, Alvaro Herrera wrote:
> On 2022-Aug-12, Simon Riggs wrote:
>
> > Sorry, but I disagree with this chunk in the latest commit,
> > specifically, changing the MATCHED from after to before the NOT
> > MATCHED clause.
3d895bc84 also moved a semicolon into the middle of the sql statement.
> > The whole point of the second example was to demonstrate that the
> > order of the MATCHED/NOT MATCHED clauses made no difference.
> >
> > By changing the examples so they are the same, the sentence at line
> > 573 now makes no sense.
>
> Hmm, I thought the point of the example was to show that you can replace
> the table in the USING clause with a query that retrieves the column;
> but you're right, we lost the thing there. Maybe it was too subtle to
> the point that I failed to understand it. Perhaps we can put it back
> the way it was and explain these two differences (change of data source
> *and* clause ordering) more explicitly.
Evidently I misunderstood it, too.
--
Justin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: MERGE and parsing with prepared statements
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Re: MERGE and parsing with prepared statements Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:17 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:59 ` Re: MERGE and parsing with prepared statements David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Re: MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Re: MERGE and parsing with prepared statements Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-12 11:46 ` Re: MERGE and parsing with prepared statements Simon Riggs <simon.riggs@enterprisedb.com>
@ 2022-09-09 11:58 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2022-09-09 11:58 UTC (permalink / raw)
To: Simon Riggs <simon.riggs@enterprisedb.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; David G. Johnston <david.g.johnston@gmail.com>; Matthias van de Meent <boekewurm+postgres@gmail.com>; pgsql-hackers; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Amit Langote <amitlangote09@gmail.com>; Japin Li <japinli@hotmail.com>; Zhihong Yu <zyu@yugabyte.com>; PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Daniel Westermann <dwe@dbi-services.com>; Erik Rijkers <er@xs4all.nl>; Jaime Casanova <jcasanov@systemguards.com.ec>; Andres Freund <andres@anarazel.de>
On 2022-Aug-12, Simon Riggs wrote:
> Sorry, but I disagree with this chunk in the latest commit,
> specifically, changing the MATCHED from after to before the NOT
> MATCHED clause.
>
> The whole point of the second example was to demonstrate that the
> order of the MATCHED/NOT MATCHED clauses made no difference.
>
> By changing the examples so they are the same, the sentence at line
> 573 now makes no sense.
Not sure how to fix this. We could add put the WHEN clauses in the
order they were, and add another phrase to that paragraph to explain
more explicitly that the order is not relevant (while at the same time
explaining that if you have multiple WHEN MATCHED clauses, the relative
order of those *is* relevant).
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"At least to kernel hackers, who really are human, despite occasional
rumors to the contrary" (LWN.net)
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2022-09-09 11:58 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-14 16:26 MERGE and parsing with prepared statements Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 09:25 ` Matthias van de Meent <boekewurm+postgres@gmail.com>
2022-07-15 17:58 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 18:40 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-07-15 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-07-15 19:17 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:40 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-15 19:53 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 19:59 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-15 20:43 ` Justin Pryzby <pryzby@telsasoft.com>
2022-07-18 13:20 ` Justin Pryzby <pryzby@telsasoft.com>
2022-08-12 11:20 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-12 11:46 ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-08-12 11:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2022-08-19 13:30 ` Justin Pryzby <pryzby@telsasoft.com>
2022-09-09 11:58 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox