From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Move handling of database properties from pg_dumpall into
 pg_dum
In-reply-to: <12453.1516655001@sss.pgh.pa.us>
References: <E1edhT6-0002lM-3m@gemulon.postgresql.org>
 <12453.1516655001@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 22 Jan 2018 16:03:21 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17971.1516658167.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 22 Jan 2018 16:56:07 -0500
Message-ID: <17972.1516658167@sss.pgh.pa.us>
Precedence: bulk

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 r=
test_emp rtest_emp_del pgbf
> pg_restore: [archiver (db)] could not execute query: ERROR:  invalid inp=
ut 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", "w=
ho", "action", "newsal", "oldsal")
>   VALUES ("old"."ename", CURRENT_USER, 'fired'::"bpchar", '$0.00'::"mone=
y", "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.

			regards, tom lane