Schema-qualify the equality operator when deparsing NULLIF/IS DISTINCT FROM
michal.dtz@gmail.com
From: michal.dtz@gmail.com
To: pgsql-hackers@lists.postgresql.org
Date: 2026-07-03T12:56:12Z
Lists: pgsql-hackers
Attachments
- v1-0001-Schema-qualify-the-equality-operator-when-deparsi.patch (application/octet-stream) patch v1-0001
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