It just inserts nb records in a loop in 4 different maneers: - Directly in an int field - Then in a numeric field (that's where we're having problems) - Then in the same numeric field, but trying a cast (it doesn't change a thing) - Then tries with an intermediary temp variable of numeric type (which solves the problem).
Here are the runtimes (tables were truncated beforehand):
9.1.9: select test_insert(1000000); NOTICE: time for int:00:00:09.526009 NOTICE: time for numeric:00:00:10.557126 NOTICE: time for numeric, casted:00:00:10.821369 NOTICE: time for numeric with tmp variable:00:00:10.850847
9.2.4: select test_insert(1000000); NOTICE: time for int:00:00:09.477044 NOTICE: time for numeric:00:00:24.757032 <---- NOTICE: time for numeric, casted:00:00:24.791016 <---- NOTICE: time for numeric with tmp variable:00:00:10.89332
I really don't know exactly where the problem comes from… but it's been hurting a function very badly (there are several of these static queries with types mismatch). And of course, the problem is not limited to numeric… text has the exact same problem.
Regards,
Marc
I got the same problem today. Unfortunately, we need to rollback to 9.1 in our production site. Of course the team needed to make better tests before go to production. Of course they really need to write better functions in PL/pgSQL, but this problem was a really "no go" for us.