Обсуждение: View definition changes after reloading pg_dump export

Hello all,

For our application we are generating a template.sql file with our 
schema in it by using pg_dump to a template.sql file. When we apply a DB 
change we load this template into a new database, apply the changes and 
export it again. This works, but we run into a small issue recently 
where we see a weird diff showing up in our template.sql after 
regenerating the schema.

There is a view that we create and it uses a function and the view 
definition changes between runs. I'm not sure why this is happening, 
does someone know? This is all done with Postgres 12. Both versions 
12.12 and 12.11 exhibit this behaviour.

The reproduction path is down below.

Our upgrade script defines a select myfunction as seen here:

```
SELECT myfunc(
   hstore(ARRAY['bar', 'baz'], ARRAY[ foo.bar, foo.baz])
)
```

When we run the SQL and export the database schema this becomes:

```
SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, 
foo.baz::character varying]::text[])
```

When we create a new database with the resulting template.sql, the 
definition becomes:

```
SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], 
ARRAY[foo.bar::text, foo.baz::character varying::text])) AS sometext
```

And this is the final form, shouldn't the first iteration of the view 
definition be this final form?

Cheers,
Wesley


The reproduction path:

```
CREATE EXTENSION IF NOT EXISTS "hstore";

DROP VIEW IF EXISTS foo_view;
DROP FUNCTION IF EXISTS myfunc cascade;
DROP TABLE IF EXISTS foo;

CREATE table foo (
   bar varchar(50),
   baz text,
   here text
);

CREATE OR REPLACE FUNCTION myfunc(IN foo public.hstore, OUT sometext text)
RETURNS text LANGUAGE plpgsql AS $$
     DECLARE
         bar text;
         baz text;
     BEGIN
         bar := foo->'bar';
         baz := foo->'baz';
         sometext := concat(bar, ' ', baz);
     END;
$$;

CREATE VIEW foo_view AS (
     SELECT myfunc(
         hstore(ARRAY['bar', 'baz'], ARRAY[ foo.bar, foo.baz])
     ) AS sometext
     FROM foo
);

\d+ foo_view
-- yields as View definition
-- SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, 
foo.baz::character varying]::text[])) AS sometext
--   FROM foo;

CREATE VIEW foo_view_copy AS (
  SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], ARRAY[foo.bar, 
foo.baz::character varying]::text[])) AS sometext
    FROM foo
);

\d+ foo_view_copy
-- yields as View definition
-- SELECT myfunc(hstore(ARRAY['bar'::text, 'baz'::text], 
ARRAY[foo.bar::text, foo.baz::character varying::text])) AS sometext
--   FROM foo;
```

-- 
Wesley Schwengle, Developer
xxllnc Zaaksysteem, https://www.zaaksysteem.nl

Wesley Schwengle <wesley.schwengle@xxllnc.nl> writes:
> There is a view that we create and it uses a function and the view 
> definition changes between runs. I'm not sure why this is happening, 
> does someone know?

The core reason for the discrepancy is that the parser inserts
implicit coercion steps into your initial, not-very-consistently-typed
query; but the view-dumping logic (ruleutils.c) tends to display
those steps as explicit coercions.  Formally speaking it shouldn't
do that, because the parser may act differently depending on whether
a cast is explicitly present, as indeed you see here.  But if we don't
write the coercions explicitly then there is a risk of the reloaded view
being interpreted differently than it was before (perhaps because there
are more or different overloaded functions/operators available at reload
time).  So the dumping logic is kind of between a rock and a hard place.
Our experience has been that printing the coercions explicitly causes
fewer problems than not doing so, so that's what it does.

            regards, tom lane

On 9/1/22 10:08, Tom Lane wrote:
> Wesley Schwengle <wesley.schwengle@xxllnc.nl> writes:
>> There is a view that we create and it uses a function and the view
>> definition changes between runs. I'm not sure why this is happening,
>> does someone know?
> 
> The core reason for the discrepancy is that the parser inserts
> implicit coercion steps into your initial, not-very-consistently-typed
> query; but the view-dumping logic (ruleutils.c) tends to display
> those steps as explicit coercions.

Thanks for the clarification, it makes a whole lot of sense. And indeed, 
when I type the SQL like so ARRAY[foo.bar::text, foo.baz::varchar::text] 
the view definition does not change between runs.

Cheers,
Wesley

-- 
Wesley Schwengle, Developer
xxllnc Zaaksysteem, https://www.zaaksysteem.nl

On 9/1/22 09:08, Tom Lane wrote:
> Wesley Schwengle <wesley.schwengle@xxllnc.nl> writes:
>> There is a view that we create and it uses a function and the view
>> definition changes between runs. I'm not sure why this is happening,
>> does someone know?
> The core reason for the discrepancy is that the parser inserts
> implicit coercion steps into your initial, not-very-consistently-typed
> query; but the view-dumping logic (ruleutils.c) tends to display
> those steps as explicit coercions.  Formally speaking it shouldn't
> do that, because the parser may act differently depending on whether
> a cast is explicitly present, as indeed you see here.  But if we don't
> write the coercions explicitly then there is a risk of the reloaded view
> being interpreted differently than it was before (perhaps because there
> are more or different overloaded functions/operators available at reload
> time).  So the dumping logic is kind of between a rock and a hard place.
> Our experience has been that printing the coercions explicitly causes
> fewer problems than not doing so, so that's what it does.
>
>             regards, tom lane
>
>

"foo.baz::character varying::text" seems pretty odd.


-- 
Angular momentum makes the world go 'round.