How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

From: Vick Khera <vivek(at)khera(dot)org>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
Date: 2024-02-21 21:11:30
Message-ID: CALd+dcf0vAfGKOLEjoh3Qsg5GaLnwrq_iti6Kind1dERZfy-9g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've created a database which my vendor (Supabase) cannot
dump/restore/upgrade. Ultimately, it comes down to this trigger statement,
and the fact that the underlying operations needed to perform the `IS
DISTINCT FROM` comparison in the WHEN clause need to be found in the
`public` schema. During the restore, the search path is empty, so it fails.

Full example file is below.

The trigger:

CREATE TRIGGER record_content_update BEFORE UPDATE OF content, embedding ON
t1
FOR EACH ROW
WHEN (((new.content <> old.content) OR (new.embedding IS DISTINCT FROM
old.embedding)))
EXECUTE FUNCTION t1_content_update_handler();

The content field is a JSONB, and the embedding field is a vector from the
pg_vector extension.

I make a backup using pg_dump, and upon restore it errors out with this:

psql:dump1.sql:122: ERROR: operator does not exist: public.vector =
public.vector
LINE 1: ... (((new.content <> old.content) OR (new.embedding IS DISTINC...
^
HINT: No operator matches the given name and argument types. You might
need to add explicit type casts.

The ^ is under the "IS DISTINCT" in case the formatting makes it unclear.

If I make the operator just `<>` the pg_dump properly schema-qualifies it
in the dump as

new.embedding OPERATOR(public.<>) old.embedding

but I need to account for the NULLs. I cannot find a way to schema-quailify
the `IS DISTINCT FROM` comparison.

How do I make this trigger definition survive pg_dump/pg_restore? I cannot
alter the dump file between the steps.

I'm running version: psql (PostgreSQL) 15.5. For my tests I'm on FreeBSD
14, but Supabase runs whatever version of linux they do and Pg version 15.1.

Full reproduction steps:

Save the file below as create.sql then run these commands:

createdb -U postgres t1
psql -U postgres -f create.sql t1
pg_dump -U postgres t1 > dump.sql
createdb -U postgres t2
psql -U postgres -f dump.sql t2

On the last step, the above referenced error will occur.

Is there a way to fix this, or is it a "don't do that" situation?

The only workaround I can think of is to move the IS DISTINCT FROM test to
be inside my trigger function.

--- create.sql file ---
CREATE EXTENSION IF NOT EXISTS "vector";

CREATE TABLE t1 (
id SERIAL PRIMARY KEY,
content JSONB DEFAULT '{}'::JSONB NOT NULL,
embedding vector
);

CREATE FUNCTION t1_content_update_handler() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE INFO '% trigger called for id=%', TG_TABLE_NAME, OLD.id;
RETURN NEW;
END;
$$;

CREATE TRIGGER record_content_update BEFORE UPDATE OF content, embedding ON
t1
FOR EACH ROW
WHEN (((new.content <> old.content) OR (new.embedding IS DISTINCT FROM
old.embedding)))
EXECUTE FUNCTION t1_content_update_handler();
--- end ---

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2024-02-21 21:27:07 Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
Previous Message Alec Lazarescu 2024-02-21 16:26:41 Re: Partitioning options