pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
5+ messages / 3 participants
[nested] [flat]

* How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
@ 2024-02-21 21:11  Vick Khera <vivek@khera.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Vick Khera @ 2024-02-21 21:11 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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 ---

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
@ 2024-02-21 21:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Vick Khera <vivek@khera.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2024-02-21 21:27 UTC (permalink / raw)
  To: Vick Khera <vivek@khera.org>; +Cc: pgsql-general@lists.postgresql.org

Vick Khera <vivek@khera.org> writes:
> 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.

Yeah.  We've had some discussions about inventing a version of IS
DISTINCT FROM (and some other SQL-spec syntaxes with the same problem)
that supports schema-qualification of the underlying operator.  But
it hasn't gotten further than preliminary discussion.

For the moment, I think the only feasible solution is for your trigger
function to set the search path it needs by adding a "SET search_path
= whatever" clause to the function's CREATE command.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
@ 2024-02-22 21:14  Vick Khera <vivek@khera.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Vick Khera @ 2024-02-22 21:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On Wed, Feb 21, 2024 at 4:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> For the moment, I think the only feasible solution is for your trigger
> function to set the search path it needs by adding a "SET search_path
> = whatever" clause to the function's CREATE command.


The error is not in the function, it is the WHEN clause of the trigger.
There's no way to set a search path on the trigger as far as I see.

The only option I see is to remove the WHEN clause on the trigger and wrap
my function with an IF with those same conditions. I hope this will not
result in any noticeable difference in speed.

It is rather unfortunate that one can end up with a schema that a backup
with pg_dump cannot be restored. Feel free to keep my example for
regression testing when postgres does grow the ability to schema-qualify
such operators.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
@ 2024-02-22 22:06  Erik Wienhold <ewie@ewie.name>
  parent: Vick Khera <vivek@khera.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Erik Wienhold @ 2024-02-22 22:06 UTC (permalink / raw)
  To: Vick Khera <vivek@khera.org>; +Cc: pgsql-general@lists.postgresql.org

On 2024-02-22 22:14 +0100, Vick Khera wrote:
> On Wed, Feb 21, 2024 at 4:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > For the moment, I think the only feasible solution is for your trigger
> > function to set the search path it needs by adding a "SET search_path
> > = whatever" clause to the function's CREATE command.
> 
> 
> The error is not in the function, it is the WHEN clause of the trigger.
> There's no way to set a search path on the trigger as far as I see.
> 
> The only option I see is to remove the WHEN clause on the trigger and wrap
> my function with an IF with those same conditions. I hope this will not
> result in any noticeable difference in speed.

You may also try the equivalent CASE expression in the WHEN clause.

https://wiki.postgresql.org/wiki/Is_distinct_from#Writing_with_CASE_statements

-- 
Erik





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)
@ 2024-02-23 14:32  Vick Khera <vivek@khera.org>
  parent: Erik Wienhold <ewie@ewie.name>
  0 siblings, 0 replies; 5+ messages in thread

From: Vick Khera @ 2024-02-23 14:32 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org

On Thu, Feb 22, 2024 at 5:06 PM Erik Wienhold <ewie@ewie.name> wrote:

> On 2024-02-22 22:14 +0100, Vick Khera wrote:
> > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > > For the moment, I think the only feasible solution is for your trigger
> > > function to set the search path it needs by adding a "SET search_path
> > > = whatever" clause to the function's CREATE command.
> >
> >
> > The error is not in the function, it is the WHEN clause of the trigger.
> > There's no way to set a search path on the trigger as far as I see.
> >
> > The only option I see is to remove the WHEN clause on the trigger and
> wrap
> > my function with an IF with those same conditions. I hope this will not
> > result in any noticeable difference in speed.
>
> You may also try the equivalent CASE expression in the WHEN clause.
>
>
> https://wiki.postgresql.org/wiki/Is_distinct_from#Writing_with_CASE_statements
>

Nice. It makes for a big ugly trigger statement, but probably my better
choice.  I was considering doing this but didn't want to risk making my own
interpretation.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2024-02-23 14:32 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-21 21:11 How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored) Vick Khera <vivek@khera.org>
2024-02-21 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-22 21:14   ` Vick Khera <vivek@khera.org>
2024-02-22 22:06     ` Erik Wienhold <ewie@ewie.name>
2024-02-23 14:32       ` Vick Khera <vivek@khera.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox