From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | teresa(dot)bradbury(at)bugcrowd(dot)com |
Subject: | BUG #15611: pg_dump output changes after doing a restore with certain views |
Date: | 2019-01-29 04:27:03 |
Message-ID: | 15611-7efe329c2185c9b9@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15611
Logged by: Tessa Bradbury
Email address: teresa(dot)bradbury(at)bugcrowd(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2019-01-29 04:59:40 | Re: psql and readline comments |
Previous Message | PG Bug reporting form | 2019-01-29 02:27:50 | BUG #15610: Performance problem of PostgreSQL 11.1 Windows version (EDB created version) |