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
Date: Mon, 22 Jan 2018 16:56:07 -0500
Message-ID: <17972.1516658167@sss.pgh.pa.us> (raw)
In-Reply-To: <12453.1516655001@sss.pgh.pa.us>
References: <E1edhT6-0002lM-3m@gemulon.postgresql.org>
<12453.1516655001@sss.pgh.pa.us>
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Move handling of database properties from pg_dumpall into pg_dum
In-Reply-To: <17972.1516658167@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox