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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: pgsql: Move handling of database properties from pg_dumpall into pg_dum
Date: 2018-01-22 21:56:07
Message-ID: 17972.1516658167@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

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

In response to

Responses

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2018-01-22 23:53:10 Re: pgsql: Add parallel-aware hash joins.
Previous Message Tom Lane 2018-01-22 21:03:21 Re: pgsql: Move handling of database properties from pg_dumpall into pg_dum

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-01-22 22:01:15 Re: [HACKERS] A design for amcheck heapam verification
Previous Message Stephen Frost 2018-01-22 21:37:52 Re: [HACKERS] Proposal: generic WAL compression