Re: Can we remove support for standard_conforming_strings = off yet?

>
> As I was working through it, I realized that there's one
> potentially-nasty point that might cause upgrading problems.
> To wit, pg_dump and pg_dumpall have historically replicated the
> source server's standard_conforming_strings setting into their
> output: they emit a SET command for that, and any string literals
> appearing in views or the like will be escaped accordingly.
> So if your old installation had standard_conforming_strings = off,
> and all you have from it is existing pg_dump output (either text
> or archive format), you are in a sticky situation because that
> dump will not restore cleanly.

I could see allowing the SET command for a few more revisions, but not
allowing it to be the global setting. This would give pg_restore a window
to catch that potentially noisy set of forlorn dump outputs. That's if
we're being really cautious.

> This isn't impossible to get
> out of, but you'd probably have to stand up a pre-v19 server,
> restore the dump into that, and take a fresh dump made with
> standard_conforming_strings = on.

This gets me thinking that we might make use of a script that does a
bridging pg_restore (taking the intermediate version's bindir as a
parameter) followed by a pg_upgrade to the current version. If we had that,
we could save ourselves a lot of future hand wringing about similar
breaking changes.

> The alternative would be
> manual correction of literals in the dump script, which seems
> far too error-prone to be recommendable.
>

Ick.

> Nonetheless, if I thought there were more than epsilon existing
> installations still running standard_conforming_strings = off
> as a global setting, I'd worry that we still can't get away with
> making this change. But if I believed that, I wouldn't be
> proposing it. This observation does raise the stakes a bit though.
>

To be fair, our epsilon is not the people who are currently running such an
installation, but people who ONCE RAN such an installation, shut it down,
and now want it back. Such people are already experiencing "downtime", so a
multi-step restoration process isn't unreasonable. Furthermore, I would
suspect that the vast majority of such restoration needs are forensic in
nature, so restoring to the same version as the dumped version is vastly
preferable if not legally required.

>
> Another comment worth making is that I oversold the code-savings
> benefit:
>
> > The code-removal aspect shouldn't be minimized either. There's
> > a nontrivial portion of scan.l that isn't reachable unless
> > standard_conforming_strings is off, and reducing the size of the
> > lexer probably translates directly to performance benefits.
>

I will admit that this potential code savings was what got me interested in
the thread, but not every prospector finds gold.

> Anyway, patches attached.

The patches look right at a glance, but I haven't been able to thoroughly
review them yet.