Re: PostgreSQL 9.0.5 concat Issue

On Nov 9, 2011, at 18:00, "Keegan,Nate" <nate.keegan@prescott-az.gov> wrote:

> I'm using a Perl script to take SNMP traps and send them to Zabbix on FreeBSD 8.2-RELEASE amd64.
>
> The original Perl script can be found at http://www.zabbix.com/wiki/howto/monitor/snmp/snmp_traps_-_a_new_solution
>
> This script works just fine under PostgreSQL 9.0.4.
>
> I'm using the FreeBSD ports system to manage PostgreSQL and Zabbix and I noticed that a test upgrade to PostgreSQL
server9.0.5 causes the script to break in a predictable way. 
>
> The script debug log is showing this:
>
> /usr/local/bin/zabbix_sender --zabbix-server 127.0.0.1 --port 10051 --host Template_Dell_OpenManage
>
> This is incorrect as 'host' should be an actual hostname and not the name of a Zabbix template (the
Template_blah_blahpart). 
>
> /var/log/messages on my system has some output that takes me to the following line in the script:
>
> $dbh->selectrow_hashref("SELECT itemid,description,key_ FROM items where hostid='$hostid' and \
> '$keyoidvaluenum' like concat(key_,'%') order by length(key_) desc limit 1;");
>
> And here is the /var/log/messages output:
>
> ERROR:  function concat(character varying, unknown) does not exist at character 98
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> STATEMENT:  SELECT itemid,description,key_ FROM items where hostid='10022' and '.1.3.6.1.4.1.318.0.77' like
concat(key_,'%')order by length(key_) desc limit 1; 
>
> There is only one line in the script which uses the concat function so I'm sure I'm in the right spot.
>
> I'm guessing that the concat function changed between PostgreSQL 9.0.4 and 9.0.5 in a subtle way that is breaking
thisscript by causing the wrong information to be returned from the database. 
>
> I can see the hint being offered but I'm unsure as to how to implement this in the given line of code exactly. I
wouldbe very grateful for any advice or direction as to how I might go about fixing this item. 
>
>

Concat(key_,'%'::varchar) <-- explicit type cast from unknown to varchar

David J.