Re: pg_dump output portability

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I needed to move a PostgreSQL database to another product but I noticed
> that the pg_dump output contains a few artifacts that make the output
> nonportable. Most of these should be relatively easy to fix.

Most of these look like they would break a lot of people --- for
example, we can't just arbitrarily change the results of bool_out.

> * Identifier quoting seems to be inconsistent. The -n option gives you
> portable behaviour (quoted only if mixed case or funny characters), but
> the default -N doesn't actually quote some things that are generated by
> the backend, including rule and index creation commands. Is there a point
> in having the -n behavior at all?

You mean you'd rather eliminate the -N behavior, no? I'd vote for that.

> * Nonprintable characters in string literals are currently output as octal
> escape sequences (e.g., \012). It would be more portable to just print
> out the characters as is. This should be an option -- any opinions on
> which might be a better default?

Again, I'm fairly suspicious of this; it seems likely to result in
failures to read in the data. You can't just leave data newlines as-is
for example.

> * The expression reverse-engineering code outputs ::text and similar casts
> in many cases. These should be CAST().

I will vote against this as being a major loss of legibility. Perhaps
we could compromise on controlling it by a GUC variable, though.

> * It was once proposed to make SET SESSION AUTHORIZATION the default in
> pg_dump. What became of that?

I think this is a good idea, and was meaning to do it but hadn't got
round to it.

> * Is anyone working on using standard foreign key creation commands
> instead of CREATE CONSTRAINT TRIGGER?

Rod Taylor submitted a patch for that, which I was planning to review
and apply shortly.

regards, tom lane