Thread: Re: [GENERAL] Invalid unicode in COPY problem
Tatsuo Ishii wrote: > Sent: Sunday, May 08, 2005 12:01 PM > To: linux@alteeve.com > Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] [GENERAL] Invalid unicode in COPY problem > > We have developed patches which relaxes the character > validation so that PostgreSQL accepts invalid characters. It > works like this: That is just plain 100% wrong!! Under no circumstances should there be invalid data in a database. And if you're trying to make a database of invalid data, then at least encode it using a valid encoding. In fact, I've proposed strengthening the validation routines for UTF-8. ... John
"John Hansen" <john@geeknet.com.au> writes:
> Tatsuo Ishii wrote:
>> We have developed patches which relaxes the character
>> validation so that PostgreSQL accepts invalid characters.
> That is just plain 100% wrong!!
That was my first reaction too. Why would this be a good idea?
If someone does want an encoding-agnostic database, they can
set it as SQL_ASCII.
regards, tom lane
> Tatsuo Ishii wrote: > > Sent: Sunday, May 08, 2005 12:01 PM > > To: linux@alteeve.com > > Cc: pgsql-general@postgresql.org; pgsql-hackers@postgresql.org > > Subject: Re: [HACKERS] [GENERAL] Invalid unicode in COPY problem > > > > We have developed patches which relaxes the character > > validation so that PostgreSQL accepts invalid characters. It > > works like this: > > That is just plain 100% wrong!! > > Under no circumstances should there be invalid data in a database. > And if you're trying to make a database of invalid data, then at > least encode it using a valid encoding. > > In fact, I've proposed strengthening the validation routines for UTF-8. Actually I myself thought as you are before. Later I found that it was not so good idea. People already have invalid encoded data in their precious database and have very hard time to migrate to newer version of PostgreSQL because of encoding validation. Think about this kind of situation: There is a table t1(member_id integer primary key, member_name text, address text, phone text, email text). I have to reach each member by either adress, phone or email. Unfortunately some of address field have wrong encoded data. In this case I will use phone or email to reach them. Now I need to upgrade to newer PostgreSQL within 1 day. I know I have to fix wrong encoded field but it will take more than 1 day. So I would like to import the data first then fix wrong encoded field on running database since I can reach members by phone or email even with wrong encoded address field... I saw this kind of situation in the real world and that's why we developed the patches. -- Tatsuo Ishii
John Hansen wrote: > Tatsuo Ishii wrote: > >>We have developed patches which relaxes the character >>validation so that PostgreSQL accepts invalid characters. It >>works like this: > > > That is just plain 100% wrong!! > Under no circumstances should there be invalid data in a database. > And if you're trying to make a database of invalid data, then at > least encode it using a valid encoding. > > In fact, I've proposed strengthening the validation routines for UTF-8. > > ... John > Under most circumstances I would agree with you completely. In my case though I have to decide between risking a loss of a user's data or attempt to store the file name in some manner that would return the same name used by the file system. The user (or one of his/her users in the case of an admin) may be completely unaware of the file name being an invalid unicode name. The file itself though may still be quite valid and contain information worthy of backing up. I could notify the user/admin that the name is not valid but there is no way I could rely on the name being changed. Given the choices, I would prefer to attempt to store/use the file name with the invalid unicode character than simply ignore the file. Is there a way to store the name in raw binary? If so, would this not be safe because to postgresql it should no longer matter what data is or represents, right? Maybe there is a third option I am not yet concidering? Madison -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Madison Kelly (Digimer) TLE-BU, The Linux Experience; Back Up http://tle-bu.thelinuxexperience.com -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Am Sonntag, den 08.05.2005, 14:30 +0900 schrieb Tatsuo Ishii: ... > Actually I myself thought as you are before. Later I found that it was > not so good idea. People already have invalid encoded data in their > precious database and have very hard time to migrate to newer version > of PostgreSQL because of encoding validation. ... > Now I need to upgrade to newer PostgreSQL within 1 day. I know I have > to fix wrong encoded field but it will take more than 1 day. So I > would like to import the data first then fix wrong encoded field on > running database since I can reach members by phone or email even with > wrong encoded address field... Well, if you are so in a hurry you better not migrate. Postgres is proud of validating the input and to have no invalid data. So if you have invalid data, better fix it. > I saw this kind of situation in the real world and that's why we > developed the patches. Why not developing a helper for contrib to help reencoding the database instead? Regards Tino
Madison Kelly wrote: > Is there a way to store the name in raw binary? Yes: bytea. -O