postgresql 8.x text items

[drwatterworth]

Propose schema changes for postgresql:
Change items/prevvalue, lastvalue, prevorgvalue(?) type to text (from varchar(255)).
Reason: later versions of postgresql do NOT silently truncate strings that exceed the defined field size (e.g., 255)... instead, an exception is raised. Thus, UserParameter (or other) items of type 'text' that exceed 255 chars cause the server to terminate when the db error occurs. I also suggest the history_log value field be of type text since some log files may have lines exceeding 255 chars. Here's diff output for what its worth (v 1.4.4 schema):


*** postgresql.sql.new 2007-12-26 16:29:03.000000000 -0500
--- postgresql.sql 2007-12-17 08:31:01.000000000 -0500
***************
*** 255,261 ****
timestamp integer DEFAULT '0' NOT NULL,
source varchar(64) DEFAULT '' NOT NULL,
severity integer DEFAULT '0' NOT NULL,
! value text DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) with OIDS;
CREATE INDEX history_log_1 on history_log (itemid,clock);
--- 255,261 ----
timestamp integer DEFAULT '0' NOT NULL,
source varchar(64) DEFAULT '' NOT NULL,
severity integer DEFAULT '0' NOT NULL,
! value varchar(255) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) with OIDS;
CREATE INDEX history_log_1 on history_log (itemid,clock);
***************
*** 482,497 ****
history integer DEFAULT '90' NOT NULL,
trends integer DEFAULT '365' NOT NULL,
nextcheck integer DEFAULT '0' NOT NULL,
! lastvalue text NULL,
lastclock integer NULL,
! prevvalue text NULL,
status integer DEFAULT '0' NOT NULL,
value_type integer DEFAULT '0' NOT NULL,
trapper_hosts varchar(255) DEFAULT '' NOT NULL,
units varchar(10) DEFAULT '' NOT NULL,
multiplier integer DEFAULT '0' NOT NULL,
delta integer DEFAULT '0' NOT NULL,
! prevorgvalue text NULL,
snmpv3_securityname varchar(64) DEFAULT '' NOT NULL,
snmpv3_securitylevel integer DEFAULT '0' NOT NULL,
snmpv3_authpassphrase varchar(64) DEFAULT '' NOT NULL,
--- 482,497 ----
history integer DEFAULT '90' NOT NULL,
trends integer DEFAULT '365' NOT NULL,
nextcheck integer DEFAULT '0' NOT NULL,
! lastvalue varchar(255) NULL,
lastclock integer NULL,
! prevvalue varchar(255) NULL,
status integer DEFAULT '0' NOT NULL,
value_type integer DEFAULT '0' NOT NULL,
trapper_hosts varchar(255) DEFAULT '' NOT NULL,
units varchar(10) DEFAULT '' NOT NULL,
multiplier integer DEFAULT '0' NOT NULL,
delta integer DEFAULT '0' NOT NULL,
! prevorgvalue varchar(255) NULL,
snmpv3_securityname varchar(64) DEFAULT '' NOT NULL,
snmpv3_securitylevel integer DEFAULT '0' NOT NULL,
snmpv3_authpassphrase varchar(64) DEFAULT '' NOT NULL,

Just a thought.
drw

[mbarthelemy]

I had the same problem , which makes Zabbix server completly crash.
To solve the problem, I did:

alter table history_log alter column value type varchar(1000);
alter table items alter column prevvalue type varchar(1000);
alter table items alter column lastvalue type varchar(1000);

...instead of changing the type from varchar to Text.

This problem occurs frequently for example with Windows Event log monitoring, because its entries are often > 255 chars.

Zabbix Team, would it be possible to truncate too long values directly in the C code, or to modify the database create script, for next versions? Thanks, and, as I forgot to say in my previous post : happy new Zabbix year!

[cedric]

I prefer varchar to text patch.
Varchar are not commonly used in pg as text is compressed when possible. So text take the same place as varchar.

[mbarthelemy]

postgresql 8.x text items

I didn't know that Text was a better solution, thanks for your explaination.
Now it would be grate that this change get included in the schema sql script provided with the official zabbix.