hi.
I just noticed this in my cronjob log:
Repairing DBMAIL headernames integrity...
Oct 15 03:15:08 lordvan.com dbmail-util[2340]: [0x114b030] Error:[db]
db_query(+360): SQLException: ERROR: canceling statement due to
statement timeout
Oct 15 03:15:08 lordvan.com dbmail-util[2340]: [0x114b030] Error:[db]
db_query(+361): failed query [SELECT hn.id FROM dbmail_headername hn
LEFT JOIN dbmail_header h ON hn.id = h.headername_id WHERE
h.headername_id IS NULL]
Ok. Found [0] unconnected headernames.
--- Repairing unconnected headernames took 300 seconds
now I was wondering .. why is this taking so long so I took the query
and (tried to) run it .. it really runs like forever ..
after checking some postgres config (shared_mem,..) i did some tuning
but to no avail ..
then I looked at the db schema:
-- Name: dbmail_header; Type: TABLE; Schema: public; Owner: dbmail;
Tablespace:
--
CREATE TABLE dbmail_header (
physmessage_id bigint NOT NULL,
headername_id bigint NOT NULL,
headervalue_id bigint NOT NULL
);
ALTER TABLE public.dbmail_header OWNER TO dbmail;
--
-- Name: dbmail_headername_id_seq; Type: SEQUENCE; Schema: public;
Owner: dbmail
--
CREATE SEQUENCE dbmail_headername_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.dbmail_headername_id_seq OWNER TO dbmail;
--
-- Name: dbmail_headername; Type: TABLE; Schema: public; Owner: dbmail;
Tablespace:
--
CREATE TABLE dbmail_headername (
id bigint DEFAULT nextval('dbmail_headername_id_seq'::regclass) NOT
NULL,
headername character varying(100) DEFAULT 'BROKEN_HEADER'::character
varying NOT NULL
);
ALTER TABLE public.dbmail_headername OWNER TO dbmail;
--
-- Name: dbmail_headervalue_id_seq; Type: SEQUENCE; Schema: public;
Owner: dbmail
--
CREATE SEQUENCE dbmail_headervalue_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.dbmail_headervalue_id_seq OWNER TO dbmail;
And then I was confused .. dbmail-util issues a query which does check a
column set in the database as NOT NULL is NULL ... seems pointless since
the DB wouldn't allow that ..
Is this some remnant of the past or for some other DB ?
In any case it seems pointless to me to run this at all - at least on
postgres- and from what I remember the other DB systems should also not
allow this ..
as for why the query takes forever (>5min) i don'T know since there are
no columns where this is NULL anyway .. (running the query without the
WHERE clause is as fast as one would expect)
maybe I'm missing something here .. if so please enlighten me.
Regards
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
