* PostgreSQL fixes

 - missing set/clear action subroutines added for postgres
 - postgres doesn't need to run the optimize routine
   assuming autovacuum isn't disabled
 - postgres was missing heartbeat table -- added in the
   sql schema
 - extended the shadow_transport column from 128 to 255
   chars -- was
 - running into issues with too long fields trying to be
   inserted
 - fix heartbeat feature - PostgreSQL doesn't support a
   REPLACE SQL function, fixes #178

Signed-off-by: Mark Felder <felderado@gmail.com>

2 files changed, 242 insertions, 230 deletions

diff --git a/doc/pgsql-db-script.sql b/doc/pgsql-db-script.sql
index 425f2b4..4345529 100644
--- a/doc/pgsql-db-script.sql
+++ b/doc/pgsql-db-script.sql
@@ -2,24 +2,29 @@
 -- PostgreSQL database dump
 --
 
-SET client_encoding = 'LATIN1';
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
 SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
 
 --
--- TOC entry 3 (OID 17145)
--- Name: exilog; Type: SCHEMA; Schema: -; Owner: 
+-- Name: exilog; Type: SCHEMA; Schema: -; Owner: exilog
 --
 
-CREATE SCHEMA exilog AUTHORIZATION exilog;
+CREATE SCHEMA exilog;
 
 
-SET SESSION AUTHORIZATION 'exilog';
+ALTER SCHEMA exilog OWNER TO exilog;
 
 SET search_path = exilog, pg_catalog;
 
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
 --
--- TOC entry 4 (OID 17181)
--- Name: deferrals; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: deferrals; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE TABLE deferrals (
@@ -34,17 +39,18 @@ CREATE TABLE deferrals (
     tls_cipher character varying(128),
     router character varying(128),
     transport character varying(128),
-    shadow_transport character varying(128),
+    shadow_transport character varying(255),
     errmsg character varying(2048)
 );
 
 
+ALTER TABLE exilog.deferrals OWNER TO exilog;
+
 --
--- TOC entry 5 (OID 17194)
--- Name: errors; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: deliveries; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE TABLE errors (
+CREATE TABLE deliveries (
     server character varying(32) NOT NULL,
     message_id character(16) NOT NULL,
     "timestamp" bigint NOT NULL,
@@ -56,17 +62,17 @@ CREATE TABLE errors (
     tls_cipher character varying(128),
     router character varying(128),
     transport character varying(128),
-    shadow_transport character varying(128),
-    errmsg character varying(2048)
+    shadow_transport character varying(255)
 );
 
 
+ALTER TABLE exilog.deliveries OWNER TO exilog;
+
 --
--- TOC entry 6 (OID 17207)
--- Name: deliveries; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: errors; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE TABLE deliveries (
+CREATE TABLE errors (
     server character varying(32) NOT NULL,
     message_id character(16) NOT NULL,
     "timestamp" bigint NOT NULL,
@@ -78,48 +84,27 @@ CREATE TABLE deliveries (
     tls_cipher character varying(128),
     router character varying(128),
     transport character varying(128),
-    shadow_transport character varying(128)
+    shadow_transport character varying(255),
+    errmsg character varying(2048)
 );
 
 
---
--- TOC entry 7 (OID 17220)
--- Name: queue; Type: TABLE; Schema: exilog; Owner: exilog
---
-
-CREATE TABLE queue (
-    server character varying(32) NOT NULL,
-    message_id character(16) NOT NULL,
-    mailfrom character varying(255),
-    "timestamp" bigint,
-    num_dsn integer,
-    frozen bigint,
-    recipients_delivered bytea,
-    recipients_pending bytea,
-    spool_path character varying(64),
-    subject character varying(255),
-    msgid character varying(255),
-    headers bytea,
-    "action" character varying(64)
-);
-
+ALTER TABLE exilog.errors OWNER TO exilog;
 
 --
--- TOC entry 8 (OID 17249)
--- Name: unknown; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: heartbeats; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE TABLE "unknown" (
+CREATE TABLE heartbeats (
     server character varying(32) NOT NULL,
-    message_id character(16) NOT NULL,
-    "timestamp" bigint NOT NULL,
-    line character varying(255) NOT NULL
+    "timestamp" bigint NOT NULL
 );
 
 
+ALTER TABLE exilog.heartbeats OWNER TO exilog;
+
 --
--- TOC entry 9 (OID 695844)
--- Name: messages; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: messages; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE TABLE messages (
@@ -141,9 +126,33 @@ CREATE TABLE messages (
 );
 
 
+ALTER TABLE exilog.messages OWNER TO exilog;
+
 --
--- TOC entry 10 (OID 695860)
--- Name: rejects; Type: TABLE; Schema: exilog; Owner: exilog
+-- Name: queue; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
+--
+
+CREATE TABLE queue (
+    server character varying(32) NOT NULL,
+    message_id character(16) NOT NULL,
+    mailfrom character varying(255),
+    "timestamp" bigint,
+    num_dsn integer,
+    frozen bigint,
+    recipients_delivered bytea,
+    recipients_pending bytea,
+    spool_path character varying(64),
+    subject character varying(255),
+    msgid character varying(255),
+    headers bytea,
+    action character varying(64)
+);
+
+
+ALTER TABLE exilog.queue OWNER TO exilog;
+
+--
+-- Name: rejects; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE TABLE rejects (
@@ -160,465 +169,447 @@ CREATE TABLE rejects (
 );
 
 
+ALTER TABLE exilog.rejects OWNER TO exilog;
+
 --
--- TOC entry 16 (OID 17188)
--- Name: deferrals_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: unknown; Type: TABLE; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_server ON deferrals USING btree (server);
+CREATE TABLE unknown (
+    server character varying(32) NOT NULL,
+    message_id character(16) NOT NULL,
+    "timestamp" bigint NOT NULL,
+    line character varying(255) NOT NULL
+);
+
 
+ALTER TABLE exilog.unknown OWNER TO exilog;
 
 --
--- TOC entry 12 (OID 17189)
--- Name: deferrals_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_message_id ON deferrals USING btree (message_id);
+ALTER TABLE ONLY deferrals
+    ADD CONSTRAINT deferrals_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
 
 
 --
--- TOC entry 18 (OID 17190)
--- Name: deferrals_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_timestamp ON deferrals USING btree ("timestamp");
+ALTER TABLE ONLY deliveries
+    ADD CONSTRAINT deliveries_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
 
 
 --
--- TOC entry 14 (OID 17191)
--- Name: deferrals_rcpt; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_rcpt ON deferrals USING btree (rcpt);
+ALTER TABLE ONLY errors
+    ADD CONSTRAINT errors_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
 
 
 --
--- TOC entry 15 (OID 17192)
--- Name: deferrals_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: heartbeats_pkey; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_rcpt_final ON deferrals USING btree (rcpt_final);
+ALTER TABLE ONLY heartbeats
+    ADD CONSTRAINT heartbeats_pkey PRIMARY KEY (server, "timestamp");
 
 
 --
--- TOC entry 11 (OID 17193)
--- Name: deferrals_host_addr; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_host_addr ON deferrals USING btree (host_addr);
+ALTER TABLE ONLY messages
+    ADD CONSTRAINT "primary" PRIMARY KEY (server, message_id);
 
 
 --
--- TOC entry 24 (OID 17199)
--- Name: errors_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_server ON errors USING btree (server);
+ALTER TABLE ONLY queue
+    ADD CONSTRAINT queue_primary PRIMARY KEY (server, message_id);
 
 
 --
--- TOC entry 20 (OID 17200)
--- Name: errors_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_unique; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_message_id ON errors USING btree (message_id);
+ALTER TABLE ONLY rejects
+    ADD CONSTRAINT rejects_unique UNIQUE (server, "timestamp", host_addr, errmsg);
 
 
 --
--- TOC entry 26 (OID 17201)
--- Name: errors_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: unknown_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_timestamp ON errors USING btree ("timestamp");
+ALTER TABLE ONLY unknown
+    ADD CONSTRAINT unknown_primary PRIMARY KEY (server, message_id, "timestamp", line);
 
 
 --
--- TOC entry 22 (OID 17202)
--- Name: errors_rcpt; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: bounce_parent; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_rcpt ON errors USING btree (rcpt);
+CREATE INDEX bounce_parent ON messages USING btree (bounce_parent);
 
 
 --
--- TOC entry 23 (OID 17203)
--- Name: errors_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_host_addr; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_rcpt_final ON errors USING btree (rcpt_final);
+CREATE INDEX deferrals_host_addr ON deferrals USING btree (host_addr);
 
 
 --
--- TOC entry 19 (OID 17204)
--- Name: errors_host_addr; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_host_addr ON errors USING btree (host_addr);
+CREATE INDEX deferrals_message_id ON deferrals USING btree (message_id);
 
 
 --
--- TOC entry 32 (OID 17212)
--- Name: deliveries_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_rcpt; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_server ON deliveries USING btree (server);
+CREATE INDEX deferrals_rcpt ON deferrals USING btree (rcpt);
 
 
 --
--- TOC entry 28 (OID 17213)
--- Name: deliveries_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_message_id ON deliveries USING btree (message_id);
+CREATE INDEX deferrals_rcpt_final ON deferrals USING btree (rcpt_final);
 
 
 --
--- TOC entry 34 (OID 17214)
--- Name: deliveries_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_timestamp ON deliveries USING btree ("timestamp");
+CREATE INDEX deferrals_server ON deferrals USING btree (server);
 
 
 --
--- TOC entry 30 (OID 17215)
--- Name: deliveries_rcpt; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_rcpt ON deliveries USING btree (rcpt);
+CREATE INDEX deferrals_server_message_id ON deferrals USING btree (server, message_id);
 
 
 --
--- TOC entry 31 (OID 17216)
--- Name: deliveries_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deferrals_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_rcpt_final ON deliveries USING btree (rcpt_final);
+CREATE INDEX deferrals_timestamp ON deferrals USING btree ("timestamp");
 
 
 --
--- TOC entry 27 (OID 17217)
--- Name: deliveries_host_addr; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_host_addr; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE INDEX deliveries_host_addr ON deliveries USING btree (host_addr);
 
 
 --
--- TOC entry 41 (OID 17241)
--- Name: queue_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_server ON queue USING btree (server);
+CREATE INDEX deliveries_message_id ON deliveries USING btree (message_id);
 
 
 --
--- TOC entry 38 (OID 17242)
--- Name: queue_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_rcpt; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_message_id ON queue USING btree (message_id);
+CREATE INDEX deliveries_rcpt ON deliveries USING btree (rcpt);
 
 
 --
--- TOC entry 37 (OID 17243)
--- Name: queue_mailfrom; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_mailfrom ON queue USING btree (mailfrom);
+CREATE INDEX deliveries_rcpt_final ON deliveries USING btree (rcpt_final);
 
 
 --
--- TOC entry 44 (OID 17244)
--- Name: queue_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_timestamp ON queue USING btree ("timestamp");
+CREATE INDEX deliveries_server ON deliveries USING btree (server);
 
 
 --
--- TOC entry 36 (OID 17245)
--- Name: queue_frozen; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_frozen ON queue USING btree (frozen);
+CREATE INDEX deliveries_server_message_id ON deliveries USING btree (server, message_id);
 
 
 --
--- TOC entry 43 (OID 17246)
--- Name: queue_spool_path; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: deliveries_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_spool_path ON queue USING btree (spool_path);
+CREATE INDEX deliveries_timestamp ON deliveries USING btree ("timestamp");
 
 
 --
--- TOC entry 39 (OID 17247)
--- Name: queue_msgid; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_host_addr; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_msgid ON queue USING btree (msgid);
+CREATE INDEX errors_host_addr ON errors USING btree (host_addr);
 
 
 --
--- TOC entry 35 (OID 17248)
--- Name: queue_action; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_action ON queue USING btree ("action");
+CREATE INDEX errors_message_id ON errors USING btree (message_id);
 
 
 --
--- TOC entry 47 (OID 17253)
--- Name: unknown_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_rcpt; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX unknown_server ON "unknown" USING btree (server);
+CREATE INDEX errors_rcpt ON errors USING btree (rcpt);
 
 
 --
--- TOC entry 45 (OID 17254)
--- Name: unknown_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_rcpt_final; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX unknown_message_id ON "unknown" USING btree (message_id);
+CREATE INDEX errors_rcpt_final ON errors USING btree (rcpt_final);
 
 
 --
--- TOC entry 49 (OID 17255)
--- Name: unknown_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX unknown_timestamp ON "unknown" USING btree ("timestamp");
+CREATE INDEX errors_server ON errors USING btree (server);
 
 
 --
--- TOC entry 17 (OID 237716)
--- Name: deferrals_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deferrals_server_message_id ON deferrals USING btree (server, message_id);
+CREATE INDEX errors_server_message_id ON errors USING btree (server, message_id);
 
 
 --
--- TOC entry 33 (OID 237717)
--- Name: deliveries_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: errors_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX deliveries_server_message_id ON deliveries USING btree (server, message_id);
+CREATE INDEX errors_timestamp ON errors USING btree ("timestamp");
 
 
 --
--- TOC entry 25 (OID 237719)
--- Name: errors_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: host_addr; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX errors_server_message_id ON errors USING btree (server, message_id);
+CREATE INDEX host_addr ON messages USING btree (host_addr);
 
 
 --
--- TOC entry 42 (OID 237725)
--- Name: queue_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: mailfrom; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX queue_server_message_id ON queue USING btree (server, message_id);
+CREATE INDEX mailfrom ON messages USING btree (mailfrom);
 
 
 --
--- TOC entry 48 (OID 237821)
--- Name: unknown_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX unknown_server_message_id ON "unknown" USING btree (server, message_id);
+CREATE INDEX message_id ON messages USING btree (message_id);
 
 
 --
--- TOC entry 57 (OID 695849)
--- Name: server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: messages_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX server ON messages USING btree (server);
+CREATE INDEX messages_server_message_id ON messages USING btree (server, message_id);
 
 
 --
--- TOC entry 53 (OID 695850)
--- Name: message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: msgid; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX message_id ON messages USING btree (message_id);
+CREATE INDEX msgid ON messages USING btree (msgid);
 
 
 --
--- TOC entry 55 (OID 695851)
--- Name: msgid; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_action; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX msgid ON messages USING btree (msgid);
+CREATE INDEX queue_action ON queue USING btree (action);
 
 
 --
--- TOC entry 58 (OID 695852)
--- Name: timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_frozen; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX "timestamp" ON messages USING btree ("timestamp");
+CREATE INDEX queue_frozen ON queue USING btree (frozen);
 
 
 --
--- TOC entry 51 (OID 695853)
--- Name: host_addr; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_mailfrom; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX host_addr ON messages USING btree (host_addr);
+CREATE INDEX queue_mailfrom ON queue USING btree (mailfrom);
 
 
 --
--- TOC entry 50 (OID 695854)
--- Name: bounce_parent; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX bounce_parent ON messages USING btree (bounce_parent);
+CREATE INDEX queue_message_id ON queue USING btree (message_id);
 
 
 --
--- TOC entry 59 (OID 695855)
--- Name: user; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_msgid; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX "user" ON messages USING btree ("user");
+CREATE INDEX queue_msgid ON queue USING btree (msgid);
 
 
 --
--- TOC entry 52 (OID 695856)
--- Name: mailfrom; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX mailfrom ON messages USING btree (mailfrom);
+CREATE INDEX queue_server ON queue USING btree (server);
 
 
 --
--- TOC entry 54 (OID 695857)
--- Name: messages_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX messages_server_message_id ON messages USING btree (server, message_id);
+CREATE INDEX queue_server_message_id ON queue USING btree (server, message_id);
 
 
 --
--- TOC entry 64 (OID 695865)
--- Name: rejects_server; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_spool_path; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX rejects_server ON rejects USING btree (server);
+CREATE INDEX queue_spool_path ON queue USING btree (spool_path);
 
 
 --
--- TOC entry 66 (OID 695866)
--- Name: rejects_timestamp; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: queue_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX rejects_timestamp ON rejects USING btree ("timestamp");
+CREATE INDEX queue_timestamp ON queue USING btree ("timestamp");
 
 
 --
--- TOC entry 60 (OID 695867)
--- Name: rejects_host_addr; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_host_addr; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE INDEX rejects_host_addr ON rejects USING btree (host_addr);
 
 
 --
--- TOC entry 61 (OID 695868)
--- Name: rejects_mailfrom; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_mailfrom; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE INDEX rejects_mailfrom ON rejects USING btree (mailfrom);
 
 
 --
--- TOC entry 63 (OID 695869)
--- Name: rejects_rcpt; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
+--
+
+CREATE INDEX rejects_message_id ON rejects USING btree (message_id);
+
+
+--
+-- Name: rejects_rcpt; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE INDEX rejects_rcpt ON rejects USING btree (rcpt);
 
 
 --
--- TOC entry 62 (OID 695870)
--- Name: rejects_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-CREATE INDEX rejects_message_id ON rejects USING btree (message_id);
+CREATE INDEX rejects_server ON rejects USING btree (server);
 
 
 --
--- TOC entry 65 (OID 695871)
--- Name: rejects_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog
+-- Name: rejects_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
 CREATE INDEX rejects_server_message_id ON rejects USING btree (server, message_id);
 
 
 --
--- TOC entry 13 (OID 17186)
--- Name: deferrals_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: rejects_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY deferrals
-    ADD CONSTRAINT deferrals_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
+CREATE INDEX rejects_timestamp ON rejects USING btree ("timestamp");
 
 
 --
--- TOC entry 21 (OID 17205)
--- Name: errors_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY errors
-    ADD CONSTRAINT errors_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
+CREATE INDEX server ON messages USING btree (server);
 
 
 --
--- TOC entry 29 (OID 17218)
--- Name: deliveries_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY deliveries
-    ADD CONSTRAINT deliveries_primary PRIMARY KEY (server, message_id, "timestamp", rcpt, rcpt_final);
+CREATE INDEX "timestamp" ON messages USING btree ("timestamp");
 
 
 --
--- TOC entry 40 (OID 17239)
--- Name: queue_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: unknown_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY queue
-    ADD CONSTRAINT queue_primary PRIMARY KEY (server, message_id);
+CREATE INDEX unknown_message_id ON unknown USING btree (message_id);
 
 
 --
--- TOC entry 46 (OID 17251)
--- Name: unknown_primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: unknown_server; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY "unknown"
-    ADD CONSTRAINT unknown_primary PRIMARY KEY (server, message_id, "timestamp", line);
+CREATE INDEX unknown_server ON unknown USING btree (server);
 
 
 --
--- TOC entry 56 (OID 695858)
--- Name: primary; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: unknown_server_message_id; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
 --
 
-ALTER TABLE ONLY messages
-    ADD CONSTRAINT "primary" PRIMARY KEY (server, message_id);
+CREATE INDEX unknown_server_message_id ON unknown USING btree (server, message_id);
 
 
 --
--- TOC entry 67 (OID 695872)
--- Name: rejects_unique; Type: CONSTRAINT; Schema: exilog; Owner: exilog
+-- Name: unknown_timestamp; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
+--
+
+CREATE INDEX unknown_timestamp ON unknown USING btree ("timestamp");
+
+
+--
+-- Name: user; Type: INDEX; Schema: exilog; Owner: exilog; Tablespace:
+--
+
+CREATE INDEX "user" ON messages USING btree ("user");
+
+
+--
+-- Name: public; Type: ACL; Schema: -; Owner: postgres
+--
+
+REVOKE ALL ON SCHEMA public FROM PUBLIC;
+REVOKE ALL ON SCHEMA public FROM postgres;
+GRANT ALL ON SCHEMA public TO postgres;
+GRANT ALL ON SCHEMA public TO PUBLIC;
+
+
+--
+-- PostgreSQL database dump complete
 --
 
-ALTER TABLE ONLY rejects
-    ADD CONSTRAINT rejects_unique UNIQUE (server, "timestamp", host_addr, errmsg);
-    
diff --git a/lib/exilog_sql.pm b/lib/exilog_sql.pm
index 8dcc348..e1dd3cd 100644
--- a/lib/exilog_sql.pm
+++ b/lib/exilog_sql.pm
@@ -149,10 +149,20 @@ sub _pgsql_sql_count {
   return @{$tmp}[0];
 };
 
+# Postgres has no REPLACE command so the best thing to do is
+# check to see if there exists any heartbeat record before
+# attempting to UPDATE one. If it's not there, lets INSERT one.
 sub _pgsql_sql_update_heartbeat {
   my $now = time();
+  my $existing_beat = $dbh->do("SELECT timestamp from heartbeats WHERE server = '". $config->{agent}->{server} ."'");
 
-  $dbh->do("REPLACE heartbeats SET server='". $config->{agent}->{server} ."', timestamp='". $now ."'");
+  if ($existing_beat != '1') {
+      $dbh->do("INSERT INTO heartbeats(timestamp, server) VALUES('". $now ."', '". $config->{agent}->{server} ."')");
+     }
+      else
+     {
+      $dbh->do("UPDATE heartbeats SET timestamp = '". $now ."' WHERE server = '". $config->{agent}->{server} ."'");
+     }
 };
 
 sub _pgsql_sql_queue_delete {
@@ -213,15 +223,26 @@ sub _pgsql_sql_queue_add {
   $dbh->do("INSERT INTO queue (".join(',',@fields).") VALUES(".join(',',@vals).")");
 };
 
-sub _pgsql_sql_optimize {
-  my $where = shift || "nothing";
+sub _pgsql_sql_queue_set_action {
+  my $server = shift;
+  my $message_id = shift;
+  my $action = shift;
 
-  my $sql = "OPTIMIZE TABLE ".$where;
-  my $sh = $dbh->prepare($sql);
-  $sh->execute;
-  $sh->finish;
+  $dbh->do("UPDATE queue SET action=".$dbh->quote($action).
+           " WHERE server=".$dbh->quote($server).
+           " AND message_id=".$dbh->quote($message_id));
+};
 
-  return 1;
+sub _pgsql_sql_queue_clear_action {
+  my $server = shift;
+  my $message_id = shift;
+
+  $dbh->do("UPDATE queue SET action=NULL WHERE server=".$dbh->quote($server).
+           " AND message_id=".$dbh->quote($message_id));
+};
+
+sub _pgsql_sql_optimize {
+  return 1; #postgres doesn't need to do anything as long as autovaccum is on
 };
 
 sub _pgsql_sql_delete {