<div>Just noticed that somehow the SQL did not get outputted nicely here is the SQL code again:<br></div><div><br></div><div>-- R/W part of the dataset (optional)<br></div><div>-- May reside in the same or in a separate database as lookups database;<br></div><div>-- REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn<br></div><div>--<br></div><div>-- Please create additional indexes on keys when needed, or drop suggested<br></div><div>-- ones as appropriate to optimize queries needed by a management application.<br></div><div>-- See your database documentation for further optimization hints.<br></div><div><br></div><div>-- provide unique id for each e-mail address, avoids storing copies<br></div><div>CREATE TABLE maddr (<br></div><div> id serial PRIMARY KEY,<br></div><div> partition_tag integer DEFAULT 0, -- see $partition_tag<br></div><div> email bytea NOT NULL, -- full e-mail address<br></div><div> domain varchar(255) NOT NULL, -- only domain part of the email address<br></div><div> -- with subdomain fields in reverse<br></div><div> CONSTRAINT part_email UNIQUE (partition_tag,email)<br></div><div>);<br></div><div><br></div><div>-- information pertaining to each processed message as a whole;<br></div><div>-- NOTE: records with a NULL msgs.content should be ignored by utilities,<br></div><div>-- as such records correspond to messages just being processed, or were lost<br></div><div>CREATE TABLE msgs (<br></div><div> partition_tag integer DEFAULT 0, -- see $partition_tag<br></div><div> mail_id bytea NOT NULL, -- long-term unique mail id, dflt 12 ch<br></div><div> secret_id bytea DEFAULT '', -- authorizes release of mail_id, 12 ch<br></div><div> am_id varchar(20) NOT NULL, -- id used in the log<br></div><div> time_num integer NOT NULL CHECK (time_num >= 0),<br></div><div> -- rx_time: seconds since Unix epoch<br></div><div> time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time<br></div><div> sid integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id<br></div><div> policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)<br></div><div> client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4 or v6)<br></div><div> size integer NOT NULL CHECK (size >= 0), -- message size in bytes<br></div><div> originating char(1) DEFAULT ' ' NOT NULL, -- sender from inside or auth'd<br></div><div> content char(1), -- content type: V/B/U/S/Y/M/H/O/T/C<br></div><div> -- virus/banned/unchecked/spam(kill)/spammy(tag2)/<br></div><div> -- /bad-mime/bad-header/oversized/mta-err/clean<br></div><div> -- is NULL on partially processed mail<br></div><div> -- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;<br></div><div> --- to avoid a need for case-insenstivity in queries)<br></div><div> quar_type char(1), -- quarantined as: ' '/F/Z/B/Q/M/L<br></div><div> -- none/file/zipfile/bsmtp/sql/<br></div><div> -- /mailbox(smtp)/mailbox(lmtp)<br></div><div> quar_loc varchar(255) DEFAULT '', -- quarantine location (e.g. file)<br></div><div> dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)<br></div><div> spam_level real, -- SA spam level (no boosts)<br></div><div> message_id varchar(255) DEFAULT '', -- mail Message-ID header field<br></div><div> from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8<br></div><div> subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8<br></div><div> host varchar(255) NOT NULL, -- hostname where amavisd is running<br></div><div> CONSTRAINT msgs_partition_mail UNIQUE (partition_tag,mail_id),<br></div><div> PRIMARY KEY (partition_tag,mail_id),<br></div><div> FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT<br></div><div>);<br></div><div>CREATE INDEX msgs_idx_sid ON msgs (sid);<br></div><div>CREATE INDEX msgs_idx_mess_id ON msgs (message_id); -- useful with pen pals<br></div><div>CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);<br></div><div>CREATE INDEX msgs_idx_time_num ON msgs (time_num); -- optional<br></div><div><br></div><div>-- per-recipient information related to each processed message;<br></div><div>-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are<br></div><div>-- orphaned and should be ignored and eventually deleted by external utilities<br></div><div>CREATE TABLE msgrcpt (<br></div><div> partition_tag integer DEFAULT 0, -- see $partition_tag<br></div><div> mail_id bytea NOT NULL, -- (must allow duplicates)<br></div><div> rseqnum integer DEFAULT 0 NOT NULL, -- recip's enumeration within msg<br></div><div> rid integer NOT NULL, -- recipient: maddr.id (duplicates allowed)<br></div><div> is_local char(1) DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign<br></div><div> content char(1) DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C<br></div><div> ds char(1) NOT NULL, -- delivery status: P/R/B/D/T<br></div><div> -- pass/reject/bounce/discard/tempfail<br></div><div> rs char(1) NOT NULL, -- release status: initialized to ' '<br></div><div> bl char(1) DEFAULT ' ', -- sender blacklisted by this recip<br></div><div> wl char(1) DEFAULT ' ', -- sender whitelisted by this recip<br></div><div> bspam_level real, -- per-recipient (total) spam level<br></div><div> smtp_resp varchar(255) DEFAULT '', -- SMTP response given to MTA<br></div><div> CONSTRAINT msgrcpt_partition_mail_rseq UNIQUE (partition_tag,mail_id,rseqnum),<br></div><div> PRIMARY KEY (partition_tag,mail_id,rseqnum),<br></div><div> FOREIGN KEY (rid) REFERENCES maddr(id) ON DELETE RESTRICT,<br></div><div> FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE<br></div><div>);<br></div><div>CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);<br></div><div>CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);<br></div><div><br></div><div>-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'<br></div><div>-- NOTE: records in quarantine without corresponding msgs.mail_id record are<br></div><div>-- orphaned and should be ignored and eventually deleted by external utilities<br></div><div>CREATE TABLE quarantine (<br></div><div> partition_tag integer DEFAULT 0, -- see $partition_tag<br></div><div> mail_id bytea NOT NULL, -- long-term unique mail id<br></div><div> chunk_ind integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..<br></div><div> mail_text bytea NOT NULL, -- store mail as chunks of octects<br></div><div> PRIMARY KEY (partition_tag,mail_id,chunk_ind),<br></div><div> FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE<br></div><div>);<br></div><div><br></div><div>-- field msgrcpt.rs is primarily intended for use by quarantine management<br></div><div>-- software; the value assigned by amavisd is a space;<br></div><div>-- a short _preliminary_ list of possible values:<br></div><div>-- 'V' => viewed (marked as read)<br></div><div>-- 'R' => released (delivered) to this recipient<br></div><div>-- 'p' => pending (a status given to messages when the admin received the<br></div><div>-- request but not yet released; targeted to banned parts)<br></div><div>-- 'D' => marked for deletion; a cleanup script may delete it<br></div><div><br></div><div>-- grant usage rights:<br></div><div>GRANT select,insert,update,delete ON maddr TO amavis;<br></div><div>GRANT usage,update ON maddr_id_seq TO amavis;<br></div><div>GRANT select,insert,update,delete ON msgs TO amavis;<br></div><div>GRANT select,insert,update,delete ON msgrcpt TO amavis;<br></div><div>GRANT select,insert,update,delete ON quarantine TO amavis;<br></div><div class="protonmail_signature_block-empty"><br></div><div class="protonmail_signature_block protonmail_signature_block-empty"><div class="protonmail_signature_block-user protonmail_signature_block-empty"><div><br></div></div><div class="protonmail_signature_block-proton protonmail_signature_block-empty"><br></div></div><div class="protonmail_signature_block-empty"><br></div><div><br></div><div><br></div><blockquote class="protonmail_quote" type="cite"><div>-------- Original Message --------<br></div><div>Subject: CREATE msgrcpt table fails on PostgreSQL<br></div><div>Local Time: November 22, 2016 9:26 PM<br></div><div>UTC Time: November 22, 2016 8:26 PM<br></div><div>From: mabi@protonmail.ch<br></div><div>To: amavis-users@amavis.org <amavis-users@amavis.org><br></div><div><br></div><div>Hello<br></div><div><br></div><div>I would like to log all quarantined messages from amavisd-new into my PostgreSQL 9.4 database. For that purpose I have created a database called amavis_log and used the SQL create table queries concerning the logging database which I found in the README.sql-pg file.<br></div><div><br></div><div>Note here also that I have also uncommented all FOREIGN KEYS in order to cascade properly the deletion of quarantined mails. See below for the exact SQL script I run against the database.<br></div><div><br></div><div>Unfortunately it can't create the 3rd "CREATE TABLE" statement concerning the msgrcpt table, the error PostgreSQL returns is the following:<br></div><div><br></div><div>ERROR: there is no unique constraint matching given keys for referenced table "msgs"<br></div><div><br></div><div>Any ideas what could be wrong here? What am I missing?<br></div><div><div><br></div><div>Many thanks in advance for your help.<br></div></div><div><br></div><div>Best regards<br></div><div>Mabi<br></div><div><br></div><div><br></div><pre>-- R/W part of the dataset (optional)
-- May reside in the same or in a separate database as lookups database;
-- REQUIRES SUPPORT FOR TRANSACTIONS; specified in @storage_sql_dsn
--
-- Please create additional indexes on keys when needed, or drop suggested
-- ones as appropriate to optimize queries needed by a management application.
-- See your database documentation for further optimization hints.
-- provide unique id for each e-mail address, avoids storing copies
CREATE TABLE maddr (
id serial PRIMARY KEY,
partition_tag integer DEFAULT 0, -- see $partition_tag
email bytea NOT NULL, -- full e-mail address
domain varchar(255) NOT NULL, -- only domain part of the email address
-- with subdomain fields in reverse
CONSTRAINT part_email UNIQUE (partition_tag,email)
);
-- information pertaining to each processed message as a whole;
-- NOTE: records with a NULL msgs.content should be ignored by utilities,
-- as such records correspond to messages just being processed, or were lost
CREATE TABLE msgs (
partition_tag integer DEFAULT 0, -- see $partition_tag
mail_id bytea NOT NULL, -- long-term unique mail id, dflt 12 ch
secret_id bytea DEFAULT '', -- authorizes release of mail_id, 12 ch
am_id varchar(20) NOT NULL, -- id used in the log
time_num integer NOT NULL CHECK (time_num >= 0),
-- rx_time: seconds since Unix epoch
time_iso timestamp WITH TIME ZONE NOT NULL,-- rx_time: ISO8601 UTC ascii time
sid integer NOT NULL CHECK (sid >= 0), -- sender: maddr.id
policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)
client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4 or v6)
size integer NOT NULL CHECK (size >= 0), -- message size in bytes
originating char(1) DEFAULT ' ' NOT NULL, -- sender from inside or auth'd
content char(1), -- content type: V/B/U/S/Y/M/H/O/T/C
-- virus/banned/unchecked/spam(kill)/spammy(tag2)/
-- /bad-mime/bad-header/oversized/mta-err/clean
-- is NULL on partially processed mail
-- (prior to 2.7.0 the CC_SPAMMY was logged as 's', now 'Y' is used;
--- to avoid a need for case-insenstivity in queries)
quar_type char(1), -- quarantined as: ' '/F/Z/B/Q/M/L
-- none/file/zipfile/bsmtp/sql/
-- /mailbox(smtp)/mailbox(lmtp)
quar_loc varchar(255) DEFAULT '', -- quarantine location (e.g. file)
dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)
spam_level real, -- SA spam level (no boosts)
message_id varchar(255) DEFAULT '', -- mail Message-ID header field
from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8
subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8
host varchar(255) NOT NULL, -- hostname where amavisd is running
CONSTRAINT msgs_partition_mail UNIQUE (partition_tag,mail_id),
PRIMARY KEY (partition_tag,mail_id),
FOREIGN KEY (sid) REFERENCES maddr(id) ON DELETE RESTRICT
);
CREATE INDEX msgs_idx_sid ON msgs (sid);
CREATE INDEX msgs_idx_mess_id ON msgs (message_id); -- useful with pen pals
CREATE INDEX msgs_idx_time_iso ON msgs (time_iso);
CREATE INDEX msgs_idx_time_num ON msgs (time_num); -- optional
-- per-recipient information related to each processed message;
-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
-- orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE msgrcpt (
partition_tag integer DEFAULT 0, -- see $partition_tag
mail_id bytea NOT NULL, -- (must allow duplicates)
rseqnum integer DEFAULT 0 NOT NULL, -- recip's enumeration within msg
rid integer NOT NULL, -- recipient: maddr.id (duplicates allowed)
is_local char(1) DEFAULT ' ' NOT NULL, -- recip is: Y=local, N=foreign
content char(1) DEFAULT ' ' NOT NULL, -- content type V/B/U/S/Y/M/H/O/T/C
ds char(1) NOT NULL, -- delivery status: P/R/B/D/T
-- pass/reject/bounce/discard/tempfail
rs char(1) NOT NULL, -- release status: initialized to ' '
bl char(1) DEFAULT ' ', -- sender blacklisted by this recip
wl char(1) DEFAULT ' ', -- sender whitelisted by this recip
bspam_level real, -- per-recipient (total) spam level
smtp_resp varchar(255) DEFAULT '', -- SMTP response given to MTA
CONSTRAINT msgrcpt_partition_mail_rseq UNIQUE (partition_tag,mail_id,rseqnum),
PRIMARY KEY (partition_tag,mail_id,rseqnum),
FOREIGN KEY (rid) REFERENCES maddr(id) ON DELETE RESTRICT,
FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
);
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
-- NOTE: records in quarantine without corresponding msgs.mail_id record are
-- orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE quarantine (
partition_tag integer DEFAULT 0, -- see $partition_tag
mail_id bytea NOT NULL, -- long-term unique mail id
chunk_ind integer NOT NULL CHECK (chunk_ind >= 0), -- chunk number, 1..
mail_text bytea NOT NULL, -- store mail as chunks of octects
PRIMARY KEY (partition_tag,mail_id,chunk_ind),
FOREIGN KEY (mail_id) REFERENCES msgs(mail_id) ON DELETE CASCADE
);
-- field msgrcpt.rs is primarily intended for use by quarantine management
-- software; the value assigned by amavisd is a space;
-- a short _preliminary_ list of possible values:
-- 'V' => viewed (marked as read)
-- 'R' => released (delivered) to this recipient
-- 'p' => pending (a status given to messages when the admin received the
-- request but not yet released; targeted to banned parts)
-- 'D' => marked for deletion; a cleanup script may delete it
-- grant usage rights:
GRANT select,insert,update,delete ON maddr TO amavis;
GRANT usage,update ON maddr_id_seq TO amavis;
GRANT select,insert,update,delete ON msgs TO amavis;
GRANT select,insert,update,delete ON msgrcpt TO amavis;
GRANT select,insert,update,delete ON quarantine TO amavis;<br></pre><div class="protonmail_signature_block-empty"><br></div><div class="protonmail_signature_block protonmail_signature_block-empty"><div class="protonmail_signature_block-user protonmail_signature_block-empty"><div><br></div></div><div class="protonmail_signature_block-proton protonmail_signature_block-empty"><br></div></div><div class="protonmail_signature_block-empty"><br></div></blockquote><div><br></div>