Skip site navigation (1) Skip section navigation (2)

Bad Data back Door

From: "David E(dot) Wheeler" <david(at)justatheory(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Bad Data back Door
Date: 2012-10-06 00:54:55
Message-ID: A9E454DD-DB0A-4D1F-A6E6-888ED4FCFE4F@justatheory.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hackers,

I’ve discovered something a bit disturbing at $work. We’re migrating (slowly) from Oracle to PostgreSQL, and in some cases are using oracle_fdw to copy data over. Alas, there are a fair number of text values in the Oracle database that, although the database is UTF-8, are actually something else (CP1252 or Latin1). When we copy from an oracle_fdw foreign table into a PostgreSQL table, PostgreSQL does not complain, but ends up storing the mis-encoded strings, even though the database is UTF-8.

I assume that this is because the foreign table, as a table, is assumed by the system to have valid data, and therefor additional character encoding validation is skipped, yes?

If so, I’m wondering if it might be possible to add some sort of option to the CREATE FOREIGN TABLE statement to the effect that certain values should not be trusted to be in the encoding they say they are.

At any rate, I’m spending some quality time re-encoding bogus values I never expected to see in our systems. :-(

Thanks,

David



Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2012-10-06 01:00:19
Subject: why repl_gram.h?
Previous:From: Tom LaneDate: 2012-10-05 23:56:40
Subject: Bugs in CREATE/DROP INDEX CONCURRENTLY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group