Thread: BUG #15611: pg_dump output changes after doing a restore with certain views

The following bug has been logged on the website:

Bug reference:      15611
Logged by:          Tessa Bradbury
Email address:      teresa.bradbury@bugcrowd.com
PostgreSQL version: 11.1
Operating system:   Alpine
Description:

When I do the following:

1. Create a view with a `foo IN (arglist)` clause in the target list
2. pg_dump
3. Restore from the dump
4. Run pg_dump again

The output of the two pg_dumps differs. The two versions represent the exact
same underlying structure but because it's not exactly the same I get
significant version control churn and it's hard to distinguish the real
changes from the noise. Because dumping and restoring is tedious, you can
also see this behaviour with pg_catalog.pg_get_viewdef().

## Reproduction steps ##

docker run --name test-postgres -d -p 5433:5432 postgres:11-alpine

cat << EOF | psql --echo-all -h 127.0.0.1 -p 5433 -U postgres
create table test_table (word character varying);
create view test_view as (select word IN ('apple', 'bannana') as fruit from
test_table);
-- the string that would be used in a pg_dump
select pg_catalog.pg_get_viewdef('test_view');
-- use that to create a new view, simulating a restore
create view test_view2 as (SELECT ((test_table.word)::text = ANY
((ARRAY['apple'::character varying, 'bannana'::character varying])::text[]))
AS fruit FROM test_table);
-- the string that would be used in the second pg_dump
select pg_catalog.pg_get_viewdef('test_view2');
EOF

## Expected behaviour ##

The output of both pg_catalog.pg_get_viewdef() calls is the same

## Actual behaviour ##

The output differs. The two versions are:

SELECT ((test_table.word)::text = ANY ((ARRAY['apple'::character varying,
'bannana'::character varying])::text[])) AS fruit FROM test_table
SELECT ((test_table.word)::text = ANY (ARRAY[('apple'::character
varying)::text, ('bannana'::character varying)::text])) AS fruit FROM
test_table

PG Bug reporting form <noreply@postgresql.org> writes:
> When I do the following:

> 1. Create a view with a `foo IN (arglist)` clause in the target list
> 2. pg_dump
> 3. Restore from the dump
> 4. Run pg_dump again

> The output of the two pg_dumps differs.

Yeah, unfortunately it's difficult to do much about that without creating
worse problems than we'd solve.

The core of the issue here is that type varchar doesn't have its own
equality operator, it uses text's.  While the coercion to text is done
implicitly when you first put in the expression, pg_dump shows it
explicitly in order to be sure that the view will be re-parsed using
the same operator as before.  And then the parser is "smart" about a
construct like "ARRAY[...]::type[]" and pushes the coercion down to
the array elements; that's a bit of a hack but people would be sad
if it went away.  I've experimented with trying to make that happen
while parsing IN initially, but that also fails, on examples like

    select * from pg_class
    where oid::regclass in ('sometable', 'someothertable');

Here it's *essential* to parse the literals as type regclass, not
type OID which is what the comparison operator's input type is.
So it's quite hard to twiddle any aspect of this behavior without
causing somebody's use-case to break.

TBH you could most easily dodge this problem by declaring your
table column as type text not varchar.

            regards, tom lane