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

Stepper motor step signal timing calculation

-
Image
While we use stepper motors in many devices, the timing of the step signals is not always well understood. Most of us can see how a fixed speed is a bad idea unless the speed of motion is really slow, but it is fixed speed what is really to generate with simple code. You just can use the Arduino Blink example to send a fixed frequency of pulses to a stepper motor driver. If too slow, just reduce the delay value. But for most real-life applications variable speed controls will perform better. These systems will speed up the motor to a given maximum speed and it will slow it down to stop at the desired step count. But understanding the math behind this process is sometimes not obvious. Let us start with the simple case of the so-called trapezoidal speed motion profile. Here motion takes place in three phases: acceleration, constant speed (cruising) and deceleration. But the point is how can our code create such a behavior. Stepper motor drivers usually accept two signals to control
Read more