Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> So the options seem to be:
> (1) Don't output a SQL-standard interval literal for the
> value "negative one days and negative one hours"; perhaps
> by sticking an extra '+' sign in there?
This is pretty much what the postgres style does...
> (2) Force pg_dump to a non-standard mode, at least until 8.3's
> deprecated in many years?
IOW, same as above.
> (3) Put something into the dump file that will make the old
> server reject the file rather than successfully loading
> wrong data? (Some "if intervalstyle==std and version<8.3
> abort loading the restore" logic?)
There isn't any way to do that, unless you have a time machine in
your hip pocket. The trouble with puttingset intervalstyle = something;
into the dump script is that older servers will (by default) report
an error on that line and keep right on chugging. The same is true
of standard_conforming_strings BTW, which is one of the reasons why
that's not a very good solution. But at least you're reasonably likely
to get additional errors later in the dump if you try to load it into a
server that doesn't handle standard_conforming_strings. What's scaring
me about the interval stuff is that it will *silently* adopt the wrong
reading of ambiguous interval strings. A DBA who missed seeing that
one bleat early in the restore would not know anything was wrong.
You're right that we don't have to be frozen into this forever, but
I fear that any change is going to be a long way off. We couldn't
really change pg_dump's output style until we have obsoleted all
pre-8.4 releases.
regards, tom lane