On +Mar 30, Tom Lane wrote:
> > What I want to accomplish is, if the WHERE clause matches @foo.com
> > I want to select from foo_users, removing @foo.com, otherwise, return
> > the data from domain_users.
>
> Um, are there really only two tables? Or are you using "foo" to imply
> that you have a bunch of domains xxx each with its own xxx_users table?
Yes, there is only 2. I am dealing with legacy issues here.
@foo.com is a "large" community site with domains "overlayed" (that best
describes it). In the case of email we are using virtual delivery to give
more email addresses for individual domains.
I now forsee the benefit of a single table, but moving the old foo.com
domain is painful at best. (foo.com has 1.5 million users and a lot of
special case code).
The new domain_users table will be all domains and users that are newly
registered.
> You could imagine using a view like
>
> CREATE VIEW all_users as
> SELECT uid, username || '@foo.com', password, active
> FROM foo_users
> UNION ALL
> SELECT uid, username, password, active
> FROM domain_users
>
> and then selecting from this view. It won't be real efficient though,
> and it won't scale conveniently if there are many per-domain tables
> (especially if they come and go).
Yes, this works and is quite slow :/
Speed is a bit of an issue since it will be dealing with email.
One approach I had tried (my SQL background isn't the strongest) is
creation of a view (which works) that returns the data required
from the table in the format the mailserver wants it. That works
wonderfully.
I then attempted to use a SELECT rule on that view. It looked like
this (the view was imap_lookup):
CREATE RULE imap_lookup AS
ON SELECT TO imap_lookup
WHERE strpos(username, '@foo.com') DO INSTEAD
SELECT uid, username, password, active
FROM foo_users
WHERE username=rtrim(username,'@foo.com');
Thus, redirecting only the queries for @foo.com to the correct
table.
I kept running into "ON SELECT rule may not use OLD".
> My advice is to change the schema. Get rid of foo_users and store all
> the users in the single domain_users table, with @domainname.com for
> everyone. It's false economy to avoid storing the @domainname.com, at
> least if your primary traffic is queries that include it.
I agree, but I'm kinda bound to what we have at the moment. Changing it
is in the plans, but more long term.
--
Tony Holmes
Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.
