Zabbix 1.4.2 and Postgres 8.3

[mbarthelemy]

Hi all,

To get rid of problems with our Postgres 8.2 causing high load on our zabbix server, I upgraded to Postgres 8.3 to see if thing were better (8.3 has, amongst other things, improvements for rows being updated very often, which is really the case with Zabbix). So I downloaded, compiled and ran the latest beta, imported my postgres 8.2 Zabbix database dump, I tried to start Zabbix.

The server always crashes due to casting errors, for example :

5019:20071212:105954 Query failed:PGRES_FATAL_ERROR:ERROR: operator is not unique: bigint || character varying at character 199
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
4984:20071212:105954 One child process died. Exiting ...
4984:20071212:105956 ZABBIX Server stopped


And the fact is, Postgres in its version 8.3 is much more strict with types and does not allow anymore comparisons between fields having different types.

I finally managed to solve (I think) the problem, here is what I did.
-First, correct some SQL code in nodewatcher/nodewatcher.c, around line 116:
$diff -uwB ./nodewatcher/nodewatcher.c ./nodewatcher/nodewatcher.c_orig

--- ./nodewatcher/nodewatcher.c 2007-12-12 11:24:52.000000000 +0100
+++ ./nodewatcher/nodewatcher.c_orig    2007-12-12 11:21:37.000000000 +0100
@@ -110,7 +109,7 @@
                                zbx_snprintf_alloc(&sql, &sql_allocated, &sql_offset, 128, "coalesce(%s,'1234567890'),",
                                        tables[i].fields[j].name);
 #else
-                               zbx_snprintf_alloc(&sql, &sql_allocated, &sql_offset, 128, "coalesce( CAST(%s AS varchar) ,'1234567890')||",
+                                zbx_snprintf_alloc(&sql, &sql_allocated, &sql_offset, 128, "coalesce(%s,'1234567890')||",
                                        tables[i].fields[j].name);
 #endif
                                j++;
@@ -345,3 +341,4 @@
                }
        }
 }
+

Then I could start the server but it crashed soon, with the same kind of cause.
Insted of correcting all the code (didn't have the time to do so), I finally added two functions in the Postgres database, which make implicit casts :

create function text2bigint(text) returns bigint as $$select $1::bigint$$ language sql;
create function bigint2text(bigint) returns text as $$select $1::text$$ language sql;
create cast (text as bigint) with function text2bigint(text) as implicit;
create cast (bigint as text) with function bigint2text(bigint) as implicit;

Now my zabbix server has been running fine and non-stop for more than 1 week.

Do you know if zabbix 1.6 will be compatible with Postgres 8.3?
If not, would it help if I send patches to correct all needed SQL statements?