Re: PostgreSQL 14.4 ERROR: out of memory issues

On 2022-08-05 17:47:02 +0200, Aleš Zelený wrote:
> the construct surprised me when I saw it in the function the first time, but it
> is correct and works as expected - it allows writing the function as SQL
> instead of PLPGSQL while it ensures that for a parameter null value it
> evaluates to true instead of filtering the resultset.
> What is the issue

The problem Joe spotted is that a variable name is misspelled in one
place. It should (probably) be external_complete_id, not
_external_complete_id).

> better solution proposal?

I think you can make that clearer by using IS [NOT] DISTINCT FROM:

SELECT ... simple join of two tables...
WHERE opd.id_data_provider = _id_data_provider
  AND external_id IS NOT DISTINCT FROM _external_id
AND external_complete_id IS NOT DISTINCT FROM _external_complete_id
;

However, your version may be faster, as last time I looked (it's been
some time) the optimizer wasn't especially good at handlung DISTINCT
FROM (probably because it's so rarely used).

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"