Re: Changing pg_dump default file format

On 11/08/2013 12:55 AM, Joshua D. Drake wrote:
>
> We have certainly done worse in the past. 8.3 comes to mind. I am trying
> to consider the future here, as we continue to grow. Why leave thorns
> for the sake of leaving thorns?

8.3 was _bad_. It's been a major upgrade barrier, and a huge pain.

I was also very unhappy with the way that the bytea_output change worked
out. I didn't have any idea it'd work out that way, but it caused a fair
bit of data corruption for real-world users because some client drivers
liked to interpret 'hex' format data as 'escape', then re-escape it when
writing it back to the server.

PHP's `pg' driver was notably at fault here - I'm not sure it supports
'hex' even now. It's mostly deprecated in favour of PDO, but it's still
in heavy use even in new code (you know, PHP users). It also took quite
some time for Ruby's "Pg" gem to stop mangling round-tripped bytea data.

If we changed the default, it would be absolutely vital to modify the
pg_restore output and psql's behaviour so that this didn't happen:

psql:/home/craig/tmp/rt-db-2013-07-09.dump:1: ERROR: syntax error at or
near "PGDMP"
LINE 1: PGDMP^A^L^A^A^A^AREVOKE ALL ON SCHEMA londiste FROM dbadmin;
^
psql:/home/craig/tmp/rt-db-2013-07-09.dump:2: ERROR: schema "londiste"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:3: ERROR: schema "londiste"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:4: ERROR: syntax error at or
near "^A"
LINE 1: ^A^AREVOKE ALL ON SCHEMA pgq FROM dbadmin;
^
psql:/home/craig/tmp/rt-db-2013-07-09.dump:5: ERROR: schema "pgq" does
not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:6: ERROR: schema "pgq" does
not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:7: ERROR: syntax error at or
near "^A"
LINE 1: ^A^AREVOKE ALL ON SCHEMA pgq_ext FROM dbadmin;
^
psql:/home/craig/tmp/rt-db-2013-07-09.dump:8: ERROR: schema "pgq_ext"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:9: ERROR: schema "pgq_ext"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:10: ERROR: syntax error at
or near "^A"
LINE 1: ^A^AREVOKE ALL ON SCHEMA pgq_node FROM dbadmin;
^
psql:/home/craig/tmp/rt-db-2013-07-09.dump:11: ERROR: schema "pgq_node"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:12: ERROR: schema "pgq_node"
does not exist
psql:/home/craig/tmp/rt-db-2013-07-09.dump:13: ERROR: syntax error at
or near "^A"
LINE 1: ^A^AREVOKE ALL ON SCHEMA public FROM postgres;
^

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services