Postgres: Missing Primary Keys

[Zyphen]

Hello,

I've recently completed a zabbix setup using 4.0+ and using Postgres 9.4 and while it appears to work I've found that the housekeeper cannot delete historical data so the DB size keeps growing and growing.
Here's the error:

STATEMENT: delete from history_uint where itemid=199157 and ctid = any(array(select ctid from history_uint where itemid=199157 limit 1000))
ERROR: Cannot run UPDATE or DELETE on table history_uint because it does not have a PRIMARY KEY.
HINT: Add a PRIMARY KEY to the table

It seems there's also a few missing Primary Keys elsewhere in the latest schema for Postgres, When I try and update zabbix the database upgrade fails with a similar error above but for the dbversion table.

Any ideas on how to get around this?

[kmpm]

I can't help you but I also got same type of errors after upgrading from Zabbix 3.4 to 4.0.
In my case it probably happened when I tried to restore a bad backup of the database. When I tok a new backup of the 3.4 database and restored it again it worked.
Sounds as if something didn't get right when you created the database... but just guessing.

[Zyphen]

This was a fresh install with 4.0, there was no previous installation of Zabbix when I ran into this issue.
I've had a look at the create.sql.gz schema and the Primary Keys are infact missing from the schema all together.

I even updated to a later version 4.0.1 to see if the schema changed at all, and it did not.

Here's the current schema for creating that table specifically which has no primary key:

CREATE TABLE history_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value numeric(20) DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL
);

[kmpm]

I'm running 4.0.2. That might do it. I don't have access to the server just now so I can't check the schema file.

[Zyphen]

Hey, I've done a fresh install of 4.0.2 seperately and confirmed this still happens, the schema is unchanged.

cat schema.sql | grep "history_uint" -A5 -B5
        clock                    integer         DEFAULT '0'               NOT NULL,
        value                    numeric(16,4)   DEFAULT '0.0000'          NOT NULL,
        ns                       integer         DEFAULT '0'               NOT NULL
);
CREATE INDEX history_1 ON history (itemid,clock);
CREATE TABLE history_uint (
        itemid                   bigint                                    NOT NULL,
        clock                    integer         DEFAULT '0'               NOT NULL,
        value                    numeric(20)     DEFAULT '0'               NOT NULL,
        ns                       integer         DEFAULT '0'               NOT NULL
);

Out of interest, what version of Postgres are you using? Have you changed anything major in the configs that could perhaps have an affect on this?

[pbootly]

Not looking to hijack here but I'm experiencing the same issue on 4.0.2 fresh install with a postgres database.

[stevereaver]

Did you ever solve this? I have a DB that I have upgraded from Zabbix 2.2 to 4.0 and found that history and history_uint don't have primary keys.