From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sawyer Knoblich <scknoblich@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery
Date: Tue, 19 Mar 2024 20:37:01 -0400
Message-ID: <4085750.1710895021@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOfdcWBfDVsYqO=MJxS_D1uvN_woDjX6zD5+jyN=a2hC+pq4fg@mail.gmail.com>
References: <18399-17778030a1971177@postgresql.org>
<3877106.1710863246@sss.pgh.pa.us>
<CAOfdcWBfDVsYqO=MJxS_D1uvN_woDjX6zD5+jyN=a2hC+pq4fg@mail.gmail.com>
Sawyer Knoblich <scknoblich@gmail.com> writes:
> To make sure I'm understanding correctly, are you suggesting that this is
> not a bug? The mention of "the cast to integer is presumed to be
> side-effect-free, which isn't true if you're working with data where it
> could throw errors" to me heavily implies that this is a faulty assumption
> on behalf of the optimizer about what behaviors certain parts of the query
> may have, but I didn't get the impression that this is being considered an
> optimizer bug and is instead something that just needs to be worked around
> or avoided (although if I'm not interpreting it right then definitely
> please correct me).
It is not an optimizer bug: the optimizer is doing what the function
marking entitles it to. You could argue that it's a marking bug and
no function/operator that can throw an error should be marked stable
or immutable, because throwing an error is a kind of side-effect.
But that would be a pretty useless answer, as it would nearly destroy
our ability to optimize queries at all, since there's not all that
many functions that can be guaranteed not to throw errors.
In short, this state of affairs is an engineering compromise.
It's not perfect, but it's not easy to see how to do better either.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, scknoblich@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18399: Query plan optimization results in runtime error when hoisting cast from inside subquery
In-Reply-To: <4085750.1710895021@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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