Gary,
> Just to clarify, to upgrade to 2.6.0, you have us
drop:
> <...>
> ALTER TABLE maddr DROP CONSTRAINT maddr_email_key;
> <...>
>
> Yet, I could find no where in the documentation where a
constraint
> called maddr_email_key is created (so it's not clear
if
> 'maddr_email_key' is literal or not).
It is a constraint name implicitly given by PostgreSQL.
With MySQL the key name is probably just 'maddr'.
> This created an 'email' INDEX:
>
> CREATE TABLE maddr (
> id int unsigned NOT NULL AUTO_INCREMENT
PRIMARY KEY,
> email varchar(255) NOT NULL UNIQUE, -- full mail
address
> domain varchar(255) NOT NULL -- only domain
part of the email
> address -- with subdomain fields in reverse )
ENGINE=InnoDB;
>
> It is this index you wish dropped (and subsequently
replaced)?
Yes.
I have now (hopefully) clarified this in the updated docs:
README.sql-mysql:
CREATE TABLE maddr (
partition_tag integer DEFAULT 0, -- see
$sql_partition_tag
id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY
KEY,
email varchar(255) NOT NULL, -- full 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)
) ENGINE=InnoDB;
README.sql-pg:
CREATE TABLE maddr (
partition_tag integer DEFAULT 0, -- see
$sql_partition_tag
id serial PRIMARY KEY,
email varchar(255) 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)
);
RELEASE_NOTES:
COMPATIBILITY WITH 2.5.3
- when using SQL for logging (e.g. for a pen pals feature)
or for
quarantining, SQL tables tables maddr, msgs, msgrcpt and
quarantine need
to be extended by a new field 'partition_tag'; see below
for details;
Should a need arise to revert to amavisd-new-2.5.4 while
keeping the
new partition_tag field, the 'SELECT id FROM maddr ...'
may become slow
due to dropped index on a field email, which is replaced
by an index
on a pair (partition_tag,email). The following change to
amavisd 2.5.4
solves the problem:
 -901,2
+901,2
'sel_adr' =>
- 'SELECT id FROM maddr WHERE email=?',
+ 'SELECT id FROM maddr WHERE partition_tag=0 AND
email=?',
[...]
To convert tables of an existing database, please use
ALTER command.
Here is a conversion example (MySQL or PostgreSQL,
probably others):
ALTER TABLE maddr ADD partition_tag integer DEFAULT
0;
ALTER TABLE msgs ADD partition_tag integer DEFAULT
0;
ALTER TABLE msgrcpt ADD partition_tag integer DEFAULT
0;
ALTER TABLE quarantine ADD partition_tag integer DEFAULT
0;
As the maddr.email is no longer guaranteed to be unique,
but the pair
of (maddr.partition_tag, maddr.email) is unique, the
constraint and
an associated index needs to be changed:
=> PostgreSQL:
ALTER TABLE maddr
DROP CONSTRAINT maddr_email_key,
ADD CONSTRAINT maddr_email_key UNIQUE
(partition_tag,email);
=> MySQL:
ALTER TABLE maddr
DROP KEY email,
ADD UNIQUE KEY part_email (partition_tag,email);
Sorry for a late reply.
Mark
------------------------------------------------------------
-------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
AMaViS-user mailing list
AMaViS-userlists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
|
