Thread: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Jan Lentfer
Date:
We found a problem with comments on databases using german umlauts (=C3=A4=
=C3=B6=C3=BC)=20
- or more generally, we found encoding problems when doing "COMMENT ON=20
database foo ..." when foo is UTF-8 encoded and the database you are=20
connected to (e.g. postgres) is SQL_ASCII.
I analyzed this with the help of Andrew Gierth (and others) on IRC - it=20
seems in that constellation you can write an non-UTF-8 comment onto a=20
UTF-8 database.
This leads to the problem, that wen trying to use pg_restore -C with a=20
dump created with -Fc the restore will fail when trying to do the=20
"COMMENT ON DATABASE.."
We stumpled across this because also pg_upgrade fails on this (during=20
the schema part), but doesn't detect this situation with the -c (check)=20
option beforehand.
This is 9.4.5 on Solaris 11 - but we did have that problem already when=20
using pg_upgrade from 8.4 to 9.1 a few years back. I am sorry we didn't=20
analyze further back then, but it was "just a comment". But now it bit=20
us again....
Here is what I did so far to analyze the problem (with instrucions from=20
Andrew Gierth). I hope this makes it clear, otherwise please don't=20
hesitate to request more details.
postgres=3D# create database comment_test template template0 encoding=20
'UTF-8';
CREATE DATABASE
#####
---> use pgadmin3 on Windows to set comment on database coment_test with=20
string "f=C3=BCr", while being connected to postgres (SQL_ASCII encoded)
#####
$ pg_dump -Fc comment_test -f comment_test.pgdump
postgres=3D# 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=20
COMMENT comment_test postgres
pg_restore: [archiver (db)] could not execute query: ERROR: invalid=20
byte sequence for encoding "UTF8": 0xfc
Command was: COMMENT ON DATABASE comment_test IS 'f=C2=A6r';
###
postgres@sz-pp-pg02-dev[~] $ pg_restore comment_test.pgdump
--
-- PostgreSQL database dump
--
SET statement_timeout =3D 0;
SET lock_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D on;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
--
-- Name: comment_test; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON DATABASE comment_test IS 'f=C2=A6r';
--
-- 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=3D# select=20
convert_to(shobj_description(oid,'pg_database'),'SQL_ASCII') from=20
pg_database where datname=3D'comment_test_orig';
convert_to
------------
\x66fc72
(1 row)
######
postgres=3D# \l
List of databases
Name | Owner | Encoding | Collate | =20
Ctype | Access privileges
--------------------+-----------+-----------+------------------+---------=
---------+-----------------------
comment_test | postgres | UTF8 | C | C =
=20
|
[...]
postgres | postgres | SQL_ASCII | C | C =
=20
|
template0 | postgres | SQL_ASCII | C | C =
=20
| =3Dc/postgres +
| | | | =
=20
| postgres=3DCTc/postgres
template1 | postgres | SQL_ASCII | C | C =
=20
| postgres=3DCTc/postgres+
| | | | =
=20
| =3Dc/postgres
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Tatsuo Ishii
Date:
SSBhbSBub3QgZmFtaWxpYXIgd2l0aCBwZ2FkbWluMyBidXQgaXQgc2VlbXMgYSBidWcgd2l0aCBw Z2FkbWluMyBvcg0KbWlzdXNlIG9mIGl0IHRvIG1lLiBQcm9iYWJseSB3aGVuIHRoaXM6DQoNCj4g LS0tPiB1c2UgcGdhZG1pbjMgb24gV2luZG93cyB0byBzZXQgY29tbWVudCBvbiBkYXRhYmFzZSBj b21lbnRfdGVzdCB3aXRoDQo+IC0tLT4gc3RyaW5nICJmw7xyIiwgd2hpbGUgYmVpbmcgY29ubmVj dGVkIHRvIHBvc3RncmVzIChTUUxfQVNDSUkgZW5jb2RlZCkNCg0Kd2FzIGV4ZWN1dGVkLCB0aGUg Y2xpZW50IGVuY29kaW5nIGlzIG5vdCBwcm9wZXJseSBzZXQgdG8gVVRGLTggZm9yDQpzb21lIHJl YXNvbnMuDQoNCkkgZGlkIGFsbW9zdCBzaW1pbGFyIHRoaW5nIGV4Y2VwdCB0aGF0IEkgdXNlZCBK YXBhbmVzZSBISVJBR0FOQQ0KY2hhcmFjdGVyIChJIGRvIG5vdCB3cml0ZSBHZXJtYW4pIGFuZCB1 c2VkIHBzcWwgdG8gaW5wdXQgQ09NTUVOVA0KY29tbWFuZC4gQWxzbyB0aGUgUG9zdGdyZVNRTCB2 ZXJzaW9uIGlzIDkuNS4wLCB3aGljaCBpcyBkaWZmZXJlbnQgZnJvbQ0KeW91cnMgYnV0IEkgZG9u J3QgdGhpbmsgdGhlcmUgaGFzIGJlZW4gbm8gY2hhbmdlIGluIHRoaXMgYXJlYS4NCg0KdC1pc2hp aUBsb2NhbGhvc3Q6IGluaXRkYiAtLW5vLWxvY2FsZSAtRSBTUUxfQVNDSUkgLUQgL3RtcC90ZXN0 ZGINClRoZSBmaWxlcyBiZWxvbmdpbmcgdG8gdGhpcyBkYXRhYmFzZSBzeXN0ZW0gd2lsbCBiZSBv d25lZCBieSB1c2VyICJ0LWlzaGlpIi4NClRoaXMgdXNlciBtdXN0IGFsc28gb3duIHRoZSBzZXJ2 ZXIgcHJvY2Vzcy4NCg0KVGhlIGRhdGFiYXNlIGNsdXN0ZXIgd2lsbCBiZSBpbml0aWFsaXplZCB3 aXRoIGxvY2FsZSAiQyIuDQpUaGUgZGVmYXVsdCB0ZXh0IHNlYXJjaCBjb25maWd1cmF0aW9uIHdp bGwgYmUgc2V0IHRvICJlbmdsaXNoIi4NCltzbmlwXQ0KU3VjY2Vzcy4gWW91IGNhbiBub3cgc3Rh cnQgdGhlIGRhdGFiYXNlIHNlcnZlciB1c2luZzoNCg0KICAgIHBnX2N0bCAtRCAvdG1wL3Rlc3Rk YiAtbCBsb2dmaWxlIHN0YXJ0DQoNCnQtaXNoaWlAbG9jYWxob3N0OiBleHBvcnQgUEdQT1JUPTU0 MzUNCnQtaXNoaWlAbG9jYWxob3N0OiBwZ19jdGwgLUQgL3RtcC90ZXN0ZGIvIHN0YXJ0DQp0LWlz aGlpQGxvY2FsaG9zdDogcHNxbCAtbA0KUGFnZXIgdXNhZ2UgaXMgb2ZmLg0KICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgTGlzdCBvZiBkYXRhYmFzZXMNCiAgIE5hbWUgICAgfCAgT3duZXIg IHwgRW5jb2RpbmcgIHwgQ29sbGF0ZSB8IEN0eXBlIHwgICAgQWNjZXNzIHByaXZpbGVnZXMgICAg DQotLS0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0rLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIHBvc3RncmVzICB8IHQtaXNoaWkgfCBTUUxfQVNDSUkg fCBDICAgICAgIHwgQyAgICAgfCANCiB0ZW1wbGF0ZTAgfCB0LWlzaGlpIHwgU1FMX0FTQ0lJIHwg QyAgICAgICB8IEMgICAgIHwgPWMvInQtaXNoaWkiICAgICAgICAgICArDQogICAgICAgICAgIHwg ICAgICAgICB8ICAgICAgICAgICB8ICAgICAgICAgfCAgICAgICB8ICJ0LWlzaGlpIj1DVGMvInQt aXNoaWkiDQogdGVtcGxhdGUxIHwgdC1pc2hpaSB8IFNRTF9BU0NJSSB8IEMgICAgICAgfCBDICAg ICB8ID1jLyJ0LWlzaGlpIiAgICAgICAgICAgKw0KICAgICAgICAgICB8ICAgICAgICAgfCAgICAg ICAgICAgfCAgICAgICAgIHwgICAgICAgfCAidC1pc2hpaSI9Q1RjLyJ0LWlzaGlpIg0KKDMgcm93 cykNCg0KdC1pc2hpaUBsb2NhbGhvc3Q6IHBzcWwgcG9zdGdyZXMNCnBvc3RncmVzPSMgY3JlYXRl IGRhdGFiYXNlIGNvbW1lbnRfdGVzdCB0ZW1wbGF0ZSB0ZW1wbGF0ZTAgZW5jb2RpbmcgJ1VURi04 JzsNCkNSRUFURSBEQVRBQkFTRQ0KcG9zdGdyZXM9IyBcbA0KICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIExpc3Qgb2YgZGF0YWJhc2VzDQogICAgIE5hbWUgICAgIHwgIE93bmVyICB8IEVu Y29kaW5nICB8IENvbGxhdGUgfCBDdHlwZSB8ICAgIEFjY2VzcyBwcml2aWxlZ2VzICAgIA0KLS0t LS0tLS0tLS0tLS0rLS0tLS0tLS0tKy0tLS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tKy0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBjb21tZW50X3Rlc3QgfCB0LWlzaGlpIHwgVVRGOCAgICAg IHwgQyAgICAgICB8IEMgICAgIHwgDQogcG9zdGdyZXMgICAgIHwgdC1pc2hpaSB8IFNRTF9BU0NJ SSB8IEMgICAgICAgfCBDICAgICB8IA0KIHRlbXBsYXRlMCAgICB8IHQtaXNoaWkgfCBTUUxfQVND SUkgfCBDICAgICAgIHwgQyAgICAgfCA9Yy8idC1pc2hpaSIgICAgICAgICAgICsNCiAgICAgICAg ICAgICAgfCAgICAgICAgIHwgICAgICAgICAgIHwgICAgICAgICB8ICAgICAgIHwgInQtaXNoaWki PUNUYy8idC1pc2hpaSINCiB0ZW1wbGF0ZTEgICAgfCB0LWlzaGlpIHwgU1FMX0FTQ0lJIHwgQyAg ICAgICB8IEMgICAgIHwgPWMvInQtaXNoaWkiICAgICAgICAgICArDQogICAgICAgICAgICAgIHwg ICAgICAgICB8ICAgICAgICAgICB8ICAgICAgICAgfCAgICAgICB8ICJ0LWlzaGlpIj1DVGMvInQt aXNoaWkiDQooNCByb3dzKQ0KDQpwb3N0Z3Jlcz0jIFxlbmNvZGluZyBVVEY4DQpwb3N0Z3Jlcz0j IGNvbW1lbnQgb24gZGF0YWJhc2UgY29tbWVudF90ZXN0IGlzICfjgYLjgYTjgYYnOw0KQ09NTUVO VA0KcG9zdGdyZXM9IyBccQ0KdC1pc2hpaUBsb2NhbGhvc3Q6IHBnX2R1bXAgLUZjIGNvbW1lbnRf dGVzdCAtZiAvdG1wL2NvbW1lbnRfdGVzdC5wZ2R1bXANCnQtaXNoaWlAbG9jYWxob3N0OiBwc3Fs IHBvc3RncmVzDQpQYWdlciB1c2FnZSBpcyBvZmYuDQpwc3FsICg5LjUuMCkNClR5cGUgImhlbHAi IGZvciBoZWxwLg0KDQpwb3N0Z3Jlcz0jIGFsdGVyIGRhdGFiYXNlIGNvbW1lbnRfdGVzdCByZW5h bWUgdG8gY29tbWVudF90ZXN0X29yaWc7DQpBTFRFUiBEQVRBQkFTRQ0KcG9zdGdyZXMtIyBccQ0K dC1pc2hpaUBsb2NhbGhvc3Q6IHBnX3Jlc3RvcmUgLUMgLWQgdGVtcGxhdGUxIC90bXAvY29tbWVu dF90ZXN0LnBnZHVtcA0KdC1pc2hpaUBsb2NhbGhvc3Q6IHBzcWwgcG9zdGdyZXMNClBhZ2VyIHVz YWdlIGlzIG9mZi4NCnBzcWwgKDkuNS4wKQ0KVHlwZSAiaGVscCIgZm9yIGhlbHAuDQoNCnBvc3Rn cmVzPSMgDQpwb3N0Z3Jlcz0jIFxsKw0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBMaXN0IG9mIGRhdGFiYXNlcw0KICAg ICAgIE5hbWUgICAgICAgIHwgIE93bmVyICB8IEVuY29kaW5nICB8IENvbGxhdGUgfCBDdHlwZSB8 ICAgIEFjY2VzcyBwcml2aWxlZ2VzICAgIHwgIFNpemUgICB8IFRhYmxlc3BhY2UgfCAgICAgICAg ICAgICAgICBEZXNjcmlwdGlvbiAgICAgICAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0tLS0t Ky0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0rLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tKy0tLS0tLS0tLSstLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBjb21tZW50X3Rlc3QgICAgICB8IHQtaXNoaWkgfCBVVEY4 ICAgICAgfCBDICAgICAgIHwgQyAgICAgfCAgICAgICAgICAgICAgICAgICAgICAgICB8IDY5OTIg a0IgfCBwZ19kZWZhdWx0IHwg44GC44GE44GGDQogY29tbWVudF90ZXN0X29yaWcgfCB0LWlzaGlp IHwgVVRGOCAgICAgIHwgQyAgICAgICB8IEMgICAgIHwgICAgICAgICAgICAgICAgICAgICAgICAg fCA2OTkyIGtCIHwgcGdfZGVmYXVsdCB8IOOBguOBhOOBhg0KIHBvc3RncmVzICAgICAgICAgIHwg dC1pc2hpaSB8IFNRTF9BU0NJSSB8IEMgICAgICAgfCBDICAgICB8ICAgICAgICAgICAgICAgICAg ICAgICAgIHwgNjk5MiBrQiB8IHBnX2RlZmF1bHQgfCBkZWZhdWx0IGFkbWluaXN0cmF0aXZlIGNv bm5lY3Rpb24gZGF0YWJhc2UNCiB0ZW1wbGF0ZTAgICAgICAgICB8IHQtaXNoaWkgfCBTUUxfQVND SUkgfCBDICAgICAgIHwgQyAgICAgfCA9Yy8idC1pc2hpaSIgICAgICAgICAgICt8IDY4NzMga0Ig fCBwZ19kZWZhdWx0IHwgdW5tb2RpZmlhYmxlIGVtcHR5IGRhdGFiYXNlDQogICAgICAgICAgICAg ICAgICAgfCAgICAgICAgIHwgICAgICAgICAgIHwgICAgICAgICB8ICAgICAgIHwgInQtaXNoaWki PUNUYy8idC1pc2hpaSIgfCAgICAgICAgIHwgICAgICAgICAgICB8IA0KIHRlbXBsYXRlMSAgICAg ICAgIHwgdC1pc2hpaSB8IFNRTF9BU0NJSSB8IEMgICAgICAgfCBDICAgICB8ID1jLyJ0LWlzaGlp IiAgICAgICAgICAgK3wgNjk5MiBrQiB8IHBnX2RlZmF1bHQgfCBkZWZhdWx0IHRlbXBsYXRlIGZv ciBuZXcgZGF0YWJhc2VzDQogICAgICAgICAgICAgICAgICAgfCAgICAgICAgIHwgICAgICAgICAg IHwgICAgICAgICB8ICAgICAgIHwgInQtaXNoaWkiPUNUYy8idC1pc2hpaSIgfCAgICAgICAgIHwg ICAgICAgICAgICB8IA0KKDUgcm93cykNCg0KQmVzdCByZWdhcmRzLA0KLS0NClRhdHN1byBJc2hp aQ0KU1JBIE9TUywgSW5jLiBKYXBhbg0KRW5nbGlzaDogaHR0cDovL3d3dy5zcmFvc3MuY28uanAv aW5kZXhfZW4ucGhwDQpKYXBhbmVzZTpodHRwOi8vd3d3LnNyYW9zcy5jby5qcA0KDQo+IFdlIGZv dW5kIGEgcHJvYmxlbSB3aXRoIGNvbW1lbnRzIG9uIGRhdGFiYXNlcyB1c2luZyBnZXJtYW4gdW1s YXV0cw0KPiAow6TDtsO8KSAtIG9yIG1vcmUgZ2VuZXJhbGx5LCB3ZSBmb3VuZCBlbmNvZGluZyBw cm9ibGVtcyB3aGVuIGRvaW5nDQo+ICJDT01NRU5UIE9OIGRhdGFiYXNlIGZvbyAuLi4iIHdoZW4g Zm9vIGlzIFVURi04IGVuY29kZWQgYW5kIHRoZQ0KPiBkYXRhYmFzZSB5b3UgYXJlIGNvbm5lY3Rl ZCB0byAoZS5nLiBwb3N0Z3JlcykgaXMgU1FMX0FTQ0lJLg0KPiBJIGFuYWx5emVkIHRoaXMgd2l0 aCB0aGUgaGVscCBvZiBBbmRyZXcgR2llcnRoIChhbmQgb3RoZXJzKSBvbiBJUkMgLQ0KPiBpdCBz ZWVtcyBpbiB0aGF0IGNvbnN0ZWxsYXRpb24geW91IGNhbiB3cml0ZSBhbiBub24tVVRGLTggY29t bWVudCBvbnRvDQo+IGEgVVRGLTggZGF0YWJhc2UuDQo+IFRoaXMgbGVhZHMgdG8gdGhlIHByb2Js ZW0sIHRoYXQgd2VuIHRyeWluZyB0byB1c2UgcGdfcmVzdG9yZSAtQyB3aXRoIGENCj4gZHVtcCBj cmVhdGVkIHdpdGggLUZjIHRoZSByZXN0b3JlIHdpbGwgZmFpbCB3aGVuIHRyeWluZyB0byBkbyB0 aGUNCj4gIkNPTU1FTlQgT04gREFUQUJBU0UuLiINCj4gV2Ugc3R1bXBsZWQgYWNyb3NzIHRoaXMg YmVjYXVzZSBhbHNvIHBnX3VwZ3JhZGUgZmFpbHMgb24gdGhpcyAoZHVyaW5nDQo+IHRoZSBzY2hl bWEgcGFydCksIGJ1dCBkb2Vzbid0IGRldGVjdCB0aGlzIHNpdHVhdGlvbiB3aXRoIHRoZSAtYw0K PiAoY2hlY2spIG9wdGlvbiBiZWZvcmVoYW5kLg0KPiBUaGlzIGlzIDkuNC41IG9uIFNvbGFyaXMg MTEgLSBidXQgd2UgZGlkIGhhdmUgdGhhdCBwcm9ibGVtIGFscmVhZHkNCj4gd2hlbiB1c2luZyBw Z191cGdyYWRlIGZyb20gOC40IHRvIDkuMSBhIGZldyB5ZWFycyBiYWNrLiBJIGFtIHNvcnJ5IHdl DQo+IGRpZG4ndCBhbmFseXplIGZ1cnRoZXIgYmFjayB0aGVuLCBidXQgaXQgd2FzICJqdXN0IGEg Y29tbWVudCIuIEJ1dCBub3cNCj4gaXQgYml0IHVzIGFnYWluLi4uLg0KPiANCj4gSGVyZSBpcyB3 aGF0IEkgZGlkIHNvIGZhciB0byBhbmFseXplIHRoZSBwcm9ibGVtICh3aXRoIGluc3RydWNpb25z DQo+IGZyb20gQW5kcmV3IEdpZXJ0aCkuIEkgaG9wZSB0aGlzIG1ha2VzIGl0IGNsZWFyLCBvdGhl cndpc2UgcGxlYXNlDQo+IGRvbid0IGhlc2l0YXRlIHRvIHJlcXVlc3QgbW9yZSBkZXRhaWxzLg0K PiANCj4gDQo+IHBvc3RncmVzPSMgY3JlYXRlIGRhdGFiYXNlIGNvbW1lbnRfdGVzdCB0ZW1wbGF0 ZSB0ZW1wbGF0ZTAgZW5jb2RpbmcNCj4gJ1VURi04JzsNCj4gQ1JFQVRFIERBVEFCQVNFDQo+ICMj IyMjDQo+IA0KPiAtLS0+IHVzZSBwZ2FkbWluMyBvbiBXaW5kb3dzIHRvIHNldCBjb21tZW50IG9u IGRhdGFiYXNlIGNvbWVudF90ZXN0IHdpdGgNCj4gLS0tPiBzdHJpbmcgImbDvHIiLCB3aGlsZSBi ZWluZyBjb25uZWN0ZWQgdG8gcG9zdGdyZXMgKFNRTF9BU0NJSSBlbmNvZGVkKQ0KPiAjIyMjIw0K PiANCj4gJCBwZ19kdW1wIC1GYyBjb21tZW50X3Rlc3QgLWYgY29tbWVudF90ZXN0LnBnZHVtcA0K PiBwb3N0Z3Jlcz0jIGFsdGVyIGRhdGFiYXNlIGNvbW1lbnRfdGVzdCByZW5hbWUgdG8gY29tbWVu dF90ZXN0X29yaWc7DQo+IEFMVEVSIERBVEFCQVNFDQo+ICMjIyMjDQo+IA0KPiAkIHBnX3Jlc3Rv cmUgLUMgLWQgdGVtcGxhdGUxIGNvbW1lbnRfdGVzdC5wZ2R1bXANCj4gcGdfcmVzdG9yZTogW2Fy Y2hpdmVyIChkYildIEVycm9yIHdoaWxlIFBST0NFU1NJTkcgVE9DOg0KPiBwZ19yZXN0b3JlOiBb YXJjaGl2ZXIgKGRiKV0gRXJyb3IgZnJvbSBUT0MgZW50cnkgMTk4OTsgMTI2MiA2NDE1MjgNCj4g Q09NTUVOVCBjb21tZW50X3Rlc3QgcG9zdGdyZXMNCj4gcGdfcmVzdG9yZTogW2FyY2hpdmVyIChk YildIGNvdWxkIG5vdCBleGVjdXRlIHF1ZXJ5OiBFUlJPUjogaW52YWxpZA0KPiBieXRlIHNlcXVl bmNlIGZvciBlbmNvZGluZyAiVVRGOCI6IDB4ZmMNCj4gICAgIENvbW1hbmQgd2FzOiBDT01NRU5U IE9OIERBVEFCQVNFIGNvbW1lbnRfdGVzdCBJUyAnZsKmcic7DQo+ICMjIw0KPiANCj4gcG9zdGdy ZXNAc3otcHAtcGcwMi1kZXZbfl0gJCBwZ19yZXN0b3JlIGNvbW1lbnRfdGVzdC5wZ2R1bXANCj4g LS0NCj4gLS0gUG9zdGdyZVNRTCBkYXRhYmFzZSBkdW1wDQo+IC0tDQo+IA0KPiBTRVQgc3RhdGVt ZW50X3RpbWVvdXQgPSAwOw0KPiBTRVQgbG9ja190aW1lb3V0ID0gMDsNCj4gU0VUIGNsaWVudF9l bmNvZGluZyA9ICdVVEY4JzsNCj4gU0VUIHN0YW5kYXJkX2NvbmZvcm1pbmdfc3RyaW5ncyA9IG9u Ow0KPiBTRVQgY2hlY2tfZnVuY3Rpb25fYm9kaWVzID0gZmFsc2U7DQo+IFNFVCBjbGllbnRfbWlu X21lc3NhZ2VzID0gd2FybmluZzsNCj4gDQo+IC0tDQo+IC0tIE5hbWU6IGNvbW1lbnRfdGVzdDsg VHlwZTogQ09NTUVOVDsgU2NoZW1hOiAtOyBPd25lcjogcG9zdGdyZXMNCj4gLS0NCj4gDQo+IENP TU1FTlQgT04gREFUQUJBU0UgY29tbWVudF90ZXN0IElTICdmwqZyJzsNCj4gDQo+IA0KPiAtLQ0K PiAtLSBOYW1lOiBwbHBnc3FsOyBUeXBlOiBFWFRFTlNJT047IFNjaGVtYTogLTsgT3duZXI6DQo+ IC0tDQo+IA0KPiBDUkVBVEUgRVhURU5TSU9OIElGIE5PVCBFWElTVFMgcGxwZ3NxbCBXSVRIIFND SEVNQSBwZ19jYXRhbG9nOw0KPiANCj4gDQo+IC0tDQo+IC0tIE5hbWU6IEVYVEVOU0lPTiBwbHBn c3FsOyBUeXBlOiBDT01NRU5UOyBTY2hlbWE6IC07IE93bmVyOg0KPiAtLQ0KPiANCj4gQ09NTUVO VCBPTiBFWFRFTlNJT04gcGxwZ3NxbCBJUyAnUEwvcGdTUUwgcHJvY2VkdXJhbCBsYW5ndWFnZSc7 DQo+IA0KPiANCj4gLS0NCj4gLS0gTmFtZTogcHVibGljOyBUeXBlOiBBQ0w7IFNjaGVtYTogLTsg T3duZXI6IHBvc3RncmVzDQo+IC0tDQo+IA0KPiBSRVZPS0UgQUxMIE9OIFNDSEVNQSBwdWJsaWMg RlJPTSBQVUJMSUM7DQo+IFJFVk9LRSBBTEwgT04gU0NIRU1BIHB1YmxpYyBGUk9NIHBvc3RncmVz Ow0KPiBHUkFOVCBBTEwgT04gU0NIRU1BIHB1YmxpYyBUTyBwb3N0Z3JlczsNCj4gR1JBTlQgQUxM IE9OIFNDSEVNQSBwdWJsaWMgVE8gUFVCTElDOw0KPiANCj4gDQo+IC0tDQo+IC0tIFBvc3RncmVT UUwgZGF0YWJhc2UgZHVtcCBjb21wbGV0ZQ0KPiAtLQ0KPiAjIyMjIyMjDQo+IA0KPiAgJCBwZ19y ZXN0b3JlIGNvbW1lbnRfdGVzdC5wZ2R1bXAgfCBoZWFkIC0xNiB8IHRhaWwgLTEgfHh4ZA0KPiAw MDAwMDAwOiA0MzRmIDRkNGQgNDU0ZSA1NDIwIDRmNGUgMjA0NCA0MTU0IDQxNDIgIENPTU1FTlQg T04gREFUQUINCj4gMDAwMDAxMDogNDE1MyA0NTIwIDYzNmYgNmQ2ZCA2NTZlIDc0NWYgNzQ2NSA3 Mzc0ICBBU0UgY29tbWVudF90ZXN0DQo+IDAwMDAwMjA6IDIwNDkgNTMyMCAyNzY2IGZjNzIgMjcz YiAwYSAgICAgICAgICAgICAgIElTICdmLnInOy4NCj4gIyMjIyMjDQo+IA0KPiBwb3N0Z3Jlcz0j IHNlbGVjdA0KPiBjb252ZXJ0X3RvKHNob2JqX2Rlc2NyaXB0aW9uKG9pZCwncGdfZGF0YWJhc2Un KSwnU1FMX0FTQ0lJJykgZnJvbQ0KPiBwZ19kYXRhYmFzZSB3aGVyZSBkYXRuYW1lPSdjb21tZW50 X3Rlc3Rfb3JpZyc7DQo+ICBjb252ZXJ0X3RvDQo+IC0tLS0tLS0tLS0tLQ0KPiAgXHg2NmZjNzIN Cj4gKDEgcm93KQ0KPiAjIyMjIyMNCj4gDQo+IA0KPiBwb3N0Z3Jlcz0jIFxsDQo+ICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgTGlzdCBvZiBkYXRhYmFzZXMNCj4g ICAgICAgICBOYW1lIHwgT3duZXIgfCBFbmNvZGluZyB8IENvbGxhdGUgfCBDdHlwZSB8IEFjY2Vz cyBwcml2aWxlZ2VzDQo+IC0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tKy0tLS0tLS0t LS0tKy0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0NCj4gIGNvbW1lbnRfdGVzdCB8IHBvc3RncmVzIHwgVVRGOCB8IEMgfCBDIHwNCj4g IFsuLi5dDQo+ICBwb3N0Z3JlcyB8IHBvc3RncmVzIHwgU1FMX0FTQ0lJIHwgQyB8IEMgfA0KPiAg dGVtcGxhdGUwIHwgcG9zdGdyZXMgfCBTUUxfQVNDSUkgfCBDIHwgQyB8ID1jL3Bvc3RncmVzICsN Cj4gICAgICAgICAgICAgICAgICAgICB8IHwgfCB8IHwgcG9zdGdyZXM9Q1RjL3Bvc3RncmVzDQo+ ICB0ZW1wbGF0ZTEgfCBwb3N0Z3JlcyB8IFNRTF9BU0NJSSB8IEMgfCBDIHwgcG9zdGdyZXM9Q1Rj L3Bvc3RncmVzKw0KPiAgICAgICAgICAgICAgICAgICAgIHwgfCB8IHwgfCA9Yy9wb3N0Z3Jlcw0K PiANCj4gDQo+IC0tIA0KPiBTZW50IHZpYSBwZ3NxbC1idWdzIG1haWxpbmcgbGlzdCAocGdzcWwt YnVnc0Bwb3N0Z3Jlc3FsLm9yZykNCj4gVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIgc3Vic2NyaXB0 aW9uOg0KPiBodHRwOi8vd3d3LnBvc3RncmVzcWwub3JnL21haWxwcmVmL3Bnc3FsLWJ1Z3MNCg==
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Jan Lentfer
Date:
Am 2016-01-27 01:45, schrieb Tatsuo Ishii: > I am not familiar with pgadmin3 but it seems a bug with pgadmin3 or > misuse of it to me. Probably when this: >=20 >> ---> use pgadmin3 on Windows to set comment on database coment_test=20 >> with >> ---> string "f=C3=BCr", while being connected to postgres (SQL_ASCII=20 >> encoded) >=20 > was executed, the client encoding is not properly set to UTF-8 for > some reasons. I think you are actually right. It seems this only happens if you=20 initially connect to a SQL_ASCII databse with pgadmin3 and then set a=20 comment containing special characters on a UTF-8 database. I could now=20 also reproduce this behaviour on a Linux host with Postgre 9.4.5. And I can also confirm that this does not happen using psql. I will forward this issue to the pgadmin mailing list. Thanks and best regards Jan
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Bruce Momjian
Date:
On Wed, Jan 27, 2016 at 08:44:27AM +0100, Jan Lentfer wrote: > Am 2016-01-27 01:45, schrieb Tatsuo Ishii: > >I am not familiar with pgadmin3 but it seems a bug with pgadmin3 or > >misuse of it to me. Probably when this: > > > >>---> use pgadmin3 on Windows to set comment on database > >>coment_test with > >>---> string "für", while being connected to postgres (SQL_ASCII > >>encoded) > > > >was executed, the client encoding is not properly set to UTF-8 for > >some reasons. > > I think you are actually right. It seems this only happens if you > initially connect to a SQL_ASCII databse with pgadmin3 and then set > a comment containing special characters on a UTF-8 database. I could > now also reproduce this behaviour on a Linux host with Postgre > 9.4.5. > And I can also confirm that this does not happen using psql. > > I will forward this issue to the pgadmin mailing list. I see no one else commented on this. We have trouble keeping the global system catalogs consistent when databases in the same cluster use different encodings. I am not sure how we could improve this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Jim Nasby
Date:
On 2/16/16 10:38 AM, Bruce Momjian wrote: > I see no one else commented on this. We have trouble keeping the global > system catalogs consistent when databases in the same cluster use > different encodings. I am not sure how we could improve this. Could we force the global catalogs to always be accessed via UTF8, at least for modification? I suspect that would mean changing encodings on the fly in the appropriate command functions (such as what's listed in src/include/commands/user.h). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Bruce Momjian
Date:
On Tue, Feb 16, 2016 at 01:36:24PM -0600, Jim Nasby wrote: > On 2/16/16 10:38 AM, Bruce Momjian wrote: > >I see no one else commented on this. We have trouble keeping the global > >system catalogs consistent when databases in the same cluster use > >different encodings. I am not sure how we could improve this. > > Could we force the global catalogs to always be accessed via UTF8, > at least for modification? I suspect that would mean changing > encodings on the fly in the appropriate command functions (such as > what's listed in src/include/commands/user.h). I don't remember us favoring UTF8 in this way in the past. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
John R Pierce
Date:
On 2/16/2016 12:06 PM, Bruce Momjian wrote: > On Tue, Feb 16, 2016 at 01:36:24PM -0600, Jim Nasby wrote: >> >On 2/16/16 10:38 AM, Bruce Momjian wrote: >>> > >I see no one else commented on this. We have trouble keeping the global >>> > >system catalogs consistent when databases in the same cluster use >>> > >different encodings. I am not sure how we could improve this. >> > >> >Could we force the global catalogs to always be accessed via UTF8, >> >at least for modification? I suspect that would mean changing >> >encodings on the fly in the appropriate command functions (such as >> >what's listed in src/include/commands/user.h). > I don't remember us favoring UTF8 in this way in the past. what encoding SHOULD the global catalogs be in when the databases are in different encodings? I'm not sure there's any answer better than utf8 ? -- john r pierce, recycling bits in santa cruz
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Jim Nasby
Date:
On 2/16/16 2:10 PM, John R Pierce wrote: >>> >Could we force the global catalogs to always be accessed via UTF8, >>> >at least for modification? I suspect that would mean changing >>> >encodings on the fly in the appropriate command functions (such as >>> >what's listed in src/include/commands/user.h). >> I don't remember us favoring UTF8 in this way in the past. > > what encoding SHOULD the global catalogs be in when the databases are in > different encodings? I'm not sure there's any answer better than utf8 ? You could probably make an argument for SQL_ASCII. The part about all of this that concerns me is what happens when people query global catalogs directly... I'm not sure if we have the ability to force specific conversions when that happens. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> On Tue, Feb 16, 2016 at 01:36:24PM -0600, Jim Nasby wrote:
>> Could we force the global catalogs to always be accessed via UTF8,
>> at least for modification? I suspect that would mean changing
>> encodings on the fly in the appropriate command functions (such as
>> what's listed in src/include/commands/user.h).
> I don't remember us favoring UTF8 in this way in the past.
Yeah. I'm pretty sure the Far Eastern contingent has specifically lobbied
against giving UTF8 such a preference. Also, if a name in the shared
catalog is UTF8, what do you do when it cannot be converted to the local
database encoding? I don't think pretending the entry isn't there will do.
Perhaps a reasonable thing for now is to document that it's a bad idea
to put non-ASCII characters in names or comments of shared objects
(databases, roles, tablespaces) unless all databases of the cluster share
the same encoding. I don't know if it would be useful/practical to try
to mechanically enforce such a rule, but we could at least warn people
about the issue.
regards, tom lane
Re: Encoding problems with "COMMENT ON DATABASE .." causing pg_restore (and pg_upgrade) to fail
From
Tatsuo Ishii
Date:
> I see no one else commented on this. We have trouble keeping the global > system catalogs consistent when databases in the same cluster use > different encodings. I am not sure how we could improve this. Long time ago I proposed an idea to solve this kind of problems: http://www.postgresql.org/message-id/20131112.155752.666523035722474275.t-ishii@sraoss.co.jp The implementation details are not necessarily the best but still I believe what we need is "round trip encoding conversion safe" universal encoding for internal use. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp