SQL function results in unresolvable plan drift

[whoward]

Hello,
I've been able to successfully dump a function definition that is roughly defined as this:

CREATE FUNCTION stable_id(entity_id uuid, value character varying) RETURNS uuid
    LANGUAGE sql IMMUTABLE
    AS $$
  SELECT uuid_generate_v5('c4d38f4d-f7a8-4ab9-afad-e4f18e05242c', entity_id::varchar || ' ' || value)
$$;

When run, pgschema dump produces the following output:

--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 17.0
-- Dumped by pgschema version 1.4.0


--
-- Name: stable_id; Type: FUNCTION; Schema: -; Owner: -
--

CREATE OR REPLACE FUNCTION stable_id(
    entity_id uuid,
    value character varying
)
RETURNS uuid
LANGUAGE sql
SECURITY INVOKER
IMMUTABLE
AS $$
  SELECT uuid_generate_v5('c4d38f4d-f7a8-4ab9-afad-e4f18e05242c', entity_id::varchar || ' ' || value)
$$;

And if I feed that file back into pgschema plan I get the following plan:

DROP FUNCTION IF EXISTS stable_id(uuid, character varying);

CREATE OR REPLACE FUNCTION stable_id(
    entity_id uuid,
    value varchar
)
RETURNS uuid
LANGUAGE sql
SECURITY INVOKER
IMMUTABLE
AS $$
  SELECT uuid_generate_v5('c4d38f4d-f7a8-4ab9-afad-e4f18e05242c', entity_id::varchar || ' ' || value)
$$;

As far as I can tell the only difference from the original definition is the SECURITY INVOKER clause, I'm not sure it's necessarily correct to add the clause in all situations but nevertheless it makes sense here. However, after applying the plan pgschema plan continues to generate the same plan with no changes. Any idea what might be causing this?

[tianzhou]

The root cause is the function parameter value character varying vs value varchar are not normalized properly before.

[whoward]

@tianzhou interesting, that makes sense. however i don't think it's actually working, even when the function is recreated with varchar as the parameter type postgres normalizes it to character varying which causes the plan to continue perpetually.

in other words i can keep running pgschema apply with the example database provided and i continually get this plan generated:

Plan: 1 to add, 1 to drop.

Summary by type:
  functions: 1 to add, 1 to drop

Functions:
  - stable_id
  + stable_id

DDL to be executed:
--------------------------------------------------

DROP FUNCTION IF EXISTS stable_id(uuid, varchar);

CREATE OR REPLACE FUNCTION stable_id(
    entity_id uuid,
    value varchar
)
RETURNS uuid
LANGUAGE sql
SECURITY INVOKER
IMMUTABLE
AS $$
  SELECT uuid_generate_v5('c4d38f4d-f7a8-4ab9-afad-e4f18e05242c', entity_id::varchar || ' ' || value)
$$;

[whoward]

for reference this is the definition that is stored in postgres after applying:

select pg_get_functiondef('stable_id'::regproc);
                                          pg_get_functiondef
-------------------------------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.stable_id(entity_id uuid, value character varying)                 +
  RETURNS uuid                                                                                        +
  LANGUAGE sql                                                                                        +
  IMMUTABLE                                                                                           +
 AS $function$                                                                                        +
   SELECT uuid_generate_v5('c4d38f4d-f7a8-4ab9-afad-e4f18e05242c', entity_id::varchar || ' ' || value)+
 $function$

[whoward]

this works great now! thanks for taking care of that