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
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
