Re: Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)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 putting
set 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