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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"