Thread: Modifying WHERE input conditions for a VIEW

Hi,

I'm stuck with some libraries that severely limit how queries are formed
and sent to postgresql and decided to use views and rules to solve my
problem. However, I've bumped my head on an issue.

I have 2 tables that need to be selected from, depending on the input
WHERE condition.

Here is their very simplified schema:

table foo_users (
  uid serial,
  username character varying(16),    -- No @domainname.com
  password character varying(16),
  active boolean default 't'
);

table domain_users (
  uid serial,
  username character varying(255),    -- Includes @domainname.com
  password character varying(16),
  active boolean default 't'
)

Now, I'm dealing with pgsql commands (from an imap system FYI) that always
appends @domain.com to the username for the where clause:

  SELECT username, password FROM xxxx WHERE username='user@domain.com';


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.

I have experimented with views, rules and looking all over the place
and am stuck!

How can I accomplish this?

Thanks in advance for you assistance!

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.

Tony Holmes <tony@crosswinds.net> writes:
> I have 2 tables that need to be selected from, depending on the input
> WHERE condition.

> Here is their very simplified schema:

> table foo_users (
>   uid serial,
>   username character varying(16),    -- No @domainname.com
>   password character varying(16),
>   active boolean default 't'
> );

> table domain_users (
>   uid serial,
>   username character varying(255),    -- Includes @domainname.com
>   password character varying(16),
>   active boolean default 't'
> )

> 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?

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).

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.

            regards, tom lane

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.

Tony Holmes <tony@crosswinds.net> writes:
> 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

This isn't going to work; we do not support WHERE conditions in ON
SELECT rules at all.  (There is no difference between ON SELECT rules
and views, actually.)

>> My advice is to change the schema.

> I agree, but I'm kinda bound to what we have at the moment. Changing it
> is in the plans, but more long term.

You might be able to make it work efficiently with a functional index.
To meet the syntactic limitations of functional indexes, you'd need to
define a function "addfoo(text) returns text" that returns the input
with "@foo.com" appended.  (Be sure to mark it strict and immutable.)
Now you can create an index on addfoo(username) for foo_users, and
then your view becomes

    CREATE VIEW all_users as
        SELECT uid, addfoo(username) AS username, password, active
        FROM foo_users
        UNION ALL
        SELECT uid, username, password, active
        FROM domain_users

I haven't tested, but I believe 7.3 will optimize this fairly decently
when the query is "SELECT ... FROM all_users WHERE username = something'.

            regards, tom lane

On +Mar 30, Tom Lane wrote:
>
> You might be able to make it work efficiently with a functional index.
> To meet the syntactic limitations of functional indexes, you'd need to
> define a function "addfoo(text) returns text" that returns the input
> with "@foo.com" appended.  (Be sure to mark it strict and immutable.)
> Now you can create an index on addfoo(username) for foo_users, and
> then your view becomes
>
>     CREATE VIEW all_users as
>         SELECT uid, addfoo(username) AS username, password, active
>         FROM foo_users
>         UNION ALL
>         SELECT uid, username, password, active
>         FROM domain_users
>
> I haven't tested, but I believe 7.3 will optimize this fairly decently
> when the query is "SELECT ... FROM all_users WHERE username = something'.

For not testing this solution works amazingly well! :)

There is 3 orders of magnitude improvement in query speed, making it
suitable for our needs! Now on to the task of fixing our schema and
data.

Thank you Tom. This kind of assistance is exactly what makes PostgreSQL
great! (well, on top of being a superior product, IMHO).

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.

Is it possible to export an entire DB in a form where it can be
re-imported?  For each table in a DB, output to a line-sequential file?

I have found that:

echo "select 'insert into $table values', * from $table |
    psql -F " " -A -t $database >> outfile.txt

Does at least give a file of sql statements.  But the strings are not
quoted or escaped.  So multi-word fields, and field''''s with quotes
mess up.

For this I also need a list of tables in a DB.  I can use:

for $table in read `echo "\dt" | psql -F " " -A -t rr | awk '{ print $2 }'`
do
    (as above)
done

But it's getting a little weigted!  I can't help fealing there must be
some clever option I am missing for dumping a DB in a form designed for
re-import, without all this work!


Thanks in advance if anybody can give me a hand...

Ben