Re: [AMaViS-user] maddr_email_key

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-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/...fo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/