Postgresql UTF-8 weirdness

-
I was recently moving data from a Postgresql version 8.0 database to a 8.4 on a new server. The database dump was made on a UTF-8 system and it was moved to another system using the same encoding. However I was getting some errors when trying to recover the data. Several encoding errors where poping out. A closer inspection revealed that those were indeed a few encoding-rule violations. For some odd reason some data fields ended up with bad data, some double-byte characters had the first byte missing (it was 0xc2 in my case).

I solved the problem creating a small filter program to add the missing byte of these characters from the database dump. Not very nice but it worked. Why the problem developed in the first place I do not know.


#include <stdio.h>

main()  {
int a,c;
 while((c=getchar())!= EOF)
  {
  if(c>0xe0) continue;
  if(c>0xc0) { 
   a=getchar(); 
   if(a>0x7f) putchar(c); putchar(a); 
  } else if(c<=0x7f) putchar(c);              
 }  
return(0); 
}

Comments

Anonymous said…
Did you send this issue to the mailing list? Let the project find out what went wrong. If it's a bug, they can fix it.

But how did you make your backup? Did you use the lastest version of pgdump to create the backup of your old database-version? That's the way to go when upgrading.
8:52 am
misan said…
I still have to look up the mailing list to see if it is a known issue, but you are right about reporting the problem.

However, my first idea being the data coming from unfiltered web forms through PHP through Apache is that I cannot directly blame Postgresql (other than maybe accepting UTF code violations as inputs and then repeating that when queried.

Regarding the backup I created it with pg_dump. Please note it was not a software upgrade but a system upgrade. Old data is still running happily in the old server while I used the dump to configure a new server, with a more recent version of Postgresql.

I'll report back in this entry what I find out if it may be relevant to this problem.
9:00 am
Post a Comment

Popular posts from this blog

VFD control with Arduino using RS485 link

-
Image
Variable Frequency Drives ( VFD ) allow the control of spindles so speed can accurately be controlled and a detailed acceleration profile for the spindle and reverse rotation can all be handled. In essence a VFD is an three phase inverter for three-phase AC motor. I am using a popular (I mean cheap) Chinese VFD and though the reference manual is not great, I could see there is a built-in RS-485 port. I usually control the start, stop and speed selection using the keyboard on the unit but I thought it will be more useful if I could control everything from the same Arduino is doing our CNC table control. Some cheap RS-485 off eBay and some lines of code later, I can start, stop and change the speed from an Arduino. What a cool thing to have! Some configuration of your VFD are needed before you can use it like that.  You need to set PD163=1 (I am using address 1 in the code). PD=164=1 (for setting serial to 9600bps) and PD165=0 (for using ASCII and 8N1 character format)
Read more

How to get sinusoidal s-curve for a stepper motor

-
Image
After some experimentation, and delving a bit into the math of motion, I realized there can be a significant difference on the motion of a machine by just making small changes. A common and popular approach to smooth one axis of motion is to use a trapezoidal speed pattern, where acceleration and deceleration are uniformly accelerated movements (aka constant acceleration motion) separated by a portion happening at cruising speed. The problem comes with the fact that, while that approach is simple to understand and to code, it does contain sudden changes in the acceleration, that show as spikes on the acceleration derivative, called jerk. These sudden changes in the acceleration translate into undesired vibration in our machines. Most of that can be eliminated by selecting a motion pattern that does not contain sudden changes of acceleration. If we chose a mathematical function for our speed whose second derivative is continuous, then we reduce system vibration quite a lot. One ma
Read more

Importing OpenSCAD designs into Onshape

-
Image
After using Onshape CAD software for a while I am really fond of it. So one natural thing to do, specially once they have released the Instructor's kit is to start using it in the classroom too. But one thing I was not so sure how to do was to use old designs I made using OpenSCAD. It does not mean I am completely quitting using OpenSCAD but suddenly I can see I can do assemblies in Onshape that will look quite nice for showing and documentation purposes if only I could easily import from OpenSCAD. I am glad to report that the route OpenSCAD --> FreeCAD --> Onshape worked brilliantly. Even more when a few tricks were applied. While some people suggest to import CSG files from OpenSCAD to FreeCAD, I have had more success by directly importing SCAD files into FreeCAD. One trick that worked well for me was to get rid of $fn references, so now circles become real circles (or cylinders) in FreeCAD. If a part keeps some $fn references then your cylinders or circles wi
Read more