Обсуждение: Re: [HACKERS] 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 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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
"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
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
> 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? In the backup rename the file and add another file <file-name-with-invalids-set-to-?>.README which explains the issue, details the steps taken (eg renaming) and offers some sort of raw binary value of the original file name. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> 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... You could add a bytea field in the migration process which holds the original data and put some "sloppily corrected" data inside the real field for the time being. Then, once corrected set the additional bytea field to NULL. Pretty much the concept of staging tables for data import. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Madison Kelly wrote: > Is there a way to store the name in raw binary? Yes: bytea. -O