Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM

From: michal(dot)dtz(at)gmail(dot)com
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM
Date: 2026-07-03 12:56:12
Message-ID: 028117fa-439b-4762-b647-130bc19df89c@Spark
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

A view that applies NULLIF or IS [NOT] DISTINCT FROM to a value whose
"=" operator is not on the search_path (for example an hstore column)
cannot be dumped and restored.  ruleutils.c deparses those constructs
with a bare "=", and pg_dump reloads with an empty search_path, so the
reload fails with:

   ERROR:  operator does not exist: public.hstore = public.hstore

A plain OpExpr already avoids this by deparsing as OPERATOR(schema.=),
but NULLIF and IS DISTINCT FROM have no syntactic slot for a qualified
operator name.  This is a long-standing report:

   https://stackoverflow.com/questions/23599926
   https://www.postgresql.org/message-id/53702D20.4070505@2ndquadrant.com

Proposed fix (in the deparser)
------------------------------
When the equality operator would not be found by its bare name under the
current search_path -- detected via generate_operator_name(), which
already decides when the OPERATOR(...) decoration is required -- emit an
equivalent expression that can carry the qualified operator, instead of
the normal syntax:

   NULLIF(a, b)
       -> CASE WHEN a IS NOT NULL AND b IS NOT NULL AND (a OPERATOR(s.=) b)
               THEN NULL ELSE a END

   a IS DISTINCT FROM b
       -> ((a IS NOT NULL OR b IS NOT NULL)
           AND (a IS NULL OR b IS NULL OR NOT (a OPERATOR(s.=) b)))

Both reproduce the executor's semantics exactly: NULLIF's single
evaluation of null inputs (the IS NOT NULL guards make it faithful even
for a non-strict "="), and DistinctExpr yielding NULL when "=" yields
NULL for two non-null inputs.  The DISTINCT form is always parenthesized
so an enclosing NOT (IS NOT DISTINCT FROM) binds it correctly; CASE ...
END is self-delimiting.  When no qualification is needed, the deparsed
output is unchanged.

The substitute forms evaluate their inputs more than once, whereas
NULLIF and DistinctExpr evaluate each input exactly once, so they are
used only when the inputs contain no volatile functions; otherwise the
original syntax is kept (it still reloads correctly whenever the
operator is on the search_path).

Details
-------
 * Target branch: master (the issue affects all live branches).
 * Tests: compiles cleanly; "make check" is green; new regression
   coverage added in contrib/hstore.  pgindent- and
   "git diff --check"-clean.
 * Docs: none needed -- user-visible NULLIF / IS DISTINCT FROM
   behaviour is unchanged; only the deparsed text differs in the
   qualified case.
 * Performance: negligible; one extra check on a rarely-taken deparse
   path.

Known limitation
----------------
A view whose *volatile* input uses an off-search-path operator still
cannot be reloaded; the substitute forms are skipped there to avoid
double-evaluating volatile inputs.  Fully handling that case would
require carrying the operator through the constructs' grammar, which
this patch does not attempt.  Feedback welcome on whether that is worth
pursuing.

v1 attached.

Thanks,
Michał Pasternak

Attachment Content-Type Size
v1-0001-Schema-qualify-the-equality-operator-when-deparsi.patch application/octet-stream 16.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Florin Irion 2026-07-03 13:32:56 Re: pg_plan_advice: add NO_ scan and join method tags
Previous Message Rafia Sabih 2026-07-03 12:51:12 Re: Bypassing cursors in postgres_fdw to enable parallel plans