Re: pgsql: Move handling of database properties from pg_dumpall into pg_dum

On Tue, Jan 23, 2018 at 8:56 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > Specifically, I see failures like this on machines where the prevailing
> > locale isn't C or US:
>
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 4871; 2618 34337 RULE
> rtest_emp rtest_emp_del pgbf
> > pg_restore: [archiver (db)] could not execute query: ERROR: invalid
> input syntax for type money: "$0.00"
> > LINE 3: ... ("old"."ename", CURRENT_USER, 'fired'::"bpchar",
> '$0.00'::"...
> > ^
> > Command was: CREATE RULE "rtest_emp_del" AS
> > ON DELETE TO "rtest_emp" DO INSERT INTO "rtest_emplog" ("ename",
> "who", "action", "newsal", "oldsal")
> > VALUES ("old"."ename", CURRENT_USER, 'fired'::"bpchar",
> '$0.00'::"money", "old"."salary");
>
> Actually ... maybe what this is pointing out is a pre-existing deficiency
> in pg_dump, which is that it's failing to lock down lc_monetary during
> restore. Arguably, we should teach _doSetFixedOutputState to set
> lc_monetary to whatever prevailed in the dump session, just as we do
> for client_encoding. I seem now to recall some user complaints about
> unsafety of dump/restore for "money" values, which essentially is the
> problem we're seeing here.
>
> I think the reason we haven't done this already is fear of putting
> platform-dependent lc_monetary values into dump scripts. That's
> certainly an issue, but it seems a minor one: at worst, you'd have
> to not use --single-transaction when restoring on a different platform,
> so you could ignore an error from the SET command.
>
> While this would fix the specific problem we're seeing in the buildfarm,
> I'm thinking we'd still need to do what I said in the previous message,
> and change pg_dump so that the restore session will absorb ALTER DATABASE
> settings before proceeding. Otherwise, at minimum, we have a hazard of
> differing behaviors in serial and parallel restores. It might be that
> lc_monetary is the only GUC that makes a real difference for this purpose,
> but I haven't got much faith in that proposition at the moment.
>

Yes, restore session should absorb all the ALTER DATABASE and ALTER ROLE
IN DATABASE settings also to make sure that the target database is in same
state when the dump has started.

currently "default_transaction_read_only" is the only GUC that affects the
absorbing the restore of a database.

As you said in upthread, I feel splitting them into two _TOC entries and
dump
as last commands of each database? Does it have any problem with parallel
restore?

Regards,
Hari Babu
Fujitsu Australia