Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#21 Reform alias table

open
nobody
Database (7)
5
2013-01-23
2007-12-15
GingerDog
No

On IRC last night, DRJ33 suggested that the 'alias' table is reformed to not have a comma seperated list within it. Obviously there are some scalability issues with this (i.e. you wouldn't be able to have more aliases than the field length could handle).

His suggestion is to go from :

mysql> select * from alias;
+-------------------+----------------------------------------------------+--------------+---------------------+---------------------+--------+
| address | goto | domain | created | modified | active |
| d99@example.com | don@example.com | example.com 2007-12-13 15:06:55 | 2007-12-13 15:06:55 | 1 |
| drj@example.com | don@example.com,test@example.com,ej@example.com | example.com | 2007-12-13 15:17:31 | 2007-12-14 11:40:53 | 1 |

TO :
+----+-------------------+--------------------+--------------+---------------------+---------------------+--------+
| id | address | goto | domain | created | modified | active |
| 1 | toor@example.com | root2@example.com | example.com | 2007-12-14 13:28:26 | 2007-12-14 13:28:26
| 2 | toor@example.com | root@example.com | example.com | 2007-12-14 13:28:30 | 2007-12-14 13:28:30 | 1
| 3 | toor@example.com | root3@example.com | example.com | 2007-12-14 13:28:35 | 2007-12-14 13:28:35 | 1 |

Apparently the queries for Postfix would still work fine if there are multiple rows returned, and not just a single row with multiple comma seperated lists.

This seems a great idea, and would certainly simplify some of e.g. the vacation handling logic; but it would also mean that there would need to be some sort of database reforming code for anyone wishing to migrate.

I'd like to see this implemented, but probably in 2.3?

(See also http://pastebin.com/d3bec4ec8 if it exists)

David.

Discussion

  • Logged In: YES
    user_id=593261
    Originator: NO

    Some notes in random order:

    The "goto" field is "text", which means up to 64 kB (for MySQL - please check for PgSQL). I guess nobody will have 64kB (that means >1000 targets if each address is 63 bytes long) of alias targets ;-) (That's a usecase for mailman etc.)

    Does DRJ33 have real problems with the field length?

    Support in postfix needs careful testing in different postfix versions.

    People with some performance tuning in their postfix config will have broken aliases, delivering only to the first goto target. This happens when someone uses "LIMIT 1" because up to now it's clear that there will be only one result.

    vacation logic / adding the vacation target: yes, maybe - but this is the only advantage I see right now.

    This requires several changes in postfixadmin, so we should not do it in 2.2.

    Oh, and the pastebin sniplet doesn't exist :-( If you have saved it somewhere, please upload it again or mail it to me.