Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail

From: Jan Lentfer <jan(dot)lentfer(at)web(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
Date: 2016-01-26 15:10:01
Message-ID: 666abab3fe03561c77dbfc661b023985@neslonek.homeunix.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

We found a problem with comments on databases using german umlauts (äöü)
- or more generally, we found encoding problems when doing "COMMENT ON
database foo ..." when foo is UTF-8 encoded and the database you are
connected to (e.g. postgres) is SQL_ASCII.
I analyzed this with the help of Andrew Gierth (and others) on IRC - it
seems in that constellation you can write an non-UTF-8 comment onto a
UTF-8 database.
This leads to the problem, that wen trying to use pg_restore -C with a
dump created with -Fc the restore will fail when trying to do the
"COMMENT ON DATABASE.."
We stumpled across this because also pg_upgrade fails on this (during
the schema part), but doesn't detect this situation with the -c (check)
option beforehand.
This is 9.4.5 on Solaris 11 - but we did have that problem already when
using pg_upgrade from 8.4 to 9.1 a few years back. I am sorry we didn't
analyze further back then, but it was "just a comment". But now it bit
us again....

Here is what I did so far to analyze the problem (with instrucions from
Andrew Gierth). I hope this makes it clear, otherwise please don't
hesitate to request more details.

postgres=# create database comment_test template template0 encoding
'UTF-8';
CREATE DATABASE
#####

---> use pgadmin3 on Windows to set comment on database coment_test with
string "für", while being connected to postgres (SQL_ASCII encoded)
#####

$ pg_dump -Fc comment_test -f comment_test.pgdump
postgres=# alter database comment_test rename to comment_test_orig;
ALTER DATABASE
#####

$ pg_restore -C -d template1 comment_test.pgdump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1989; 1262 641528
COMMENT comment_test postgres
pg_restore: [archiver (db)] could not execute query: ERROR: invalid
byte sequence for encoding "UTF8": 0xfc
Command was: COMMENT ON DATABASE comment_test IS 'f¦r';
###

postgres(at)sz-pp-pg02-dev[~] $ pg_restore comment_test.pgdump
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: comment_test; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE comment_test IS 'f¦r';

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--
#######

$ pg_restore comment_test.pgdump | head -16 | tail -1 |xxd
0000000: 434f 4d4d 454e 5420 4f4e 2044 4154 4142 COMMENT ON DATAB
0000010: 4153 4520 636f 6d6d 656e 745f 7465 7374 ASE comment_test
0000020: 2049 5320 2766 fc72 273b 0a IS 'f.r';.
######

postgres=# select
convert_to(shobj_description(oid,'pg_database'),'SQL_ASCII') from
pg_database where datname='comment_test_orig';
convert_to
------------
\x66fc72
(1 row)
######

postgres=# \l
List of databases
Name | Owner | Encoding | Collate |
Ctype | Access privileges
--------------------+-----------+-----------+------------------+------------------+-----------------------
comment_test | postgres | UTF8 | C | C
|
[...]
postgres | postgres | SQL_ASCII | C | C
|
template0 | postgres | SQL_ASCII | C | C
| =c/postgres +
| | | |
| postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C
| postgres=CTc/postgres+
| | | |
| =c/postgres

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Dennis Kögel 2016-01-26 18:21:31 Parallel pg_restore fails to import views with triggers (dependency problem?)
Previous Message Tom Lane 2016-01-26 14:35:54 Re: BUG #13888: pg_dump write error