Re: Detect supported SET parameters when pg_restore is run

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Sep 26, 2016 at 9:56 PM, Vitaly Burovoy
> <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
>> We do dump/restore schemas/data via custom/dir formats and we have to
>> keep several client versions for 9.2, 9.4 and 9.5 versions on local
>> workstations because after pg_restore95 connects to 9.2, it fails when
>> it sets run-time parameters via SET:

> I think our policy is that a newer pg_dump needs to work with an older
> version of the database, but I don't think we make any similar
> guarantee for other tools, such as pg_restore. It's an interesting
> question whether we should try a little harder to do that, but I
> suspect it might require more changes than what you've got in this
> patch....

The general policy has always been that pg_dump output is only expected to
restore without errors into a server that's the same or newer version as
pg_dump (regardless of the server version being dumped from). If you try
to restore into an older server version, you may get some errors, which we
try to minimize the scope of when possible. But it will never be possible
to promise none at all. I think the correct advice here is simply "don't
use pg_restore -e -1 when trying to restore into an older server version".

Taking a step back, there are any number of ways that you might get
errors during a pg_restore into a DB that's not set up exactly as pg_dump
expected. Missing roles or tablespaces, for example. Again, the dump
output is constructed so that you can survive those problems and bull
ahead ... but not with "-e -1". I don't see a very good reason why
older-server-version shouldn't be considered the same type of problem.

The patch as given seems rather broken anyway --- won't it change text
output from pg_dump as well as on-line output from pg_restore? (That is,
it looks to me like the SETs emitted by pg_dump to text format would
depend on the source server version, which they absolutely should not.
Either that, or the patch is overwriting pg_dump's idea of what the
source server version is at the start of the output phase, which is
likely to break all kinds of stuff when dumping from an older server.)

It's possible that we could alleviate this specific symptom by arranging
for the BEGIN caused by "-1" to come out after the initial SETs, but
I'm not sure how complicated that would be or whether it's worth the
trouble.

regards, tom lane