Re: [SQL] RULE questions. - Mailing list pgsql-sql

>
> Hi,
>
> I have what I first thought would be a trivial problem, in that I require
> the 2 VARCHAR columns in the following table to have the data stored in
> upper case.
>
> test_table
> +-----------------------------+------------------------------+------+
> |            Field            |             Type             |Length|
> +-----------------------------+------------------------------+------+
> | user_id                     | int4                         |    4 |
> | name                        | varchar()                    |   10 |
> | password                    | varchar()                    |   10 |
> +-----------------------------+------------------------------+------+
>
>
> I considered just using UPPER() in every SELECT statement, or creating a
> view that SELECTed the 2 VARCHAR cols using UPPER but I assume that this
> would have more performance issues than having the data converted to
> uppercase only once during an insert or update.  (Please correct me if I am
> wrong).

    It's right.

>
> After looking at triggers and rules I came up with the following solution:
>
> CREATE VIEW test AS SELECT * FROM test_table;
>
> CREATE RULE insert_test AS
> ON INSERT TO test DO INSTEAD
> INSERT INTO test_table (user_id, name, password) VALUES(new.user_id,
> UPPER(new.name), UPPER(new.password));
>
> CREATE RULE update_test AS
> ON UPDATE TO test DO INSTEAD
> UPDATE test_table SET name = UPPER(new.name), password = UPPER(new.password)
> WHERE user_id = new.user_id;

    1.  Make sure user_id is unique or extend the WHERE clause in
        the UPDATE rule.  To explain why:

            user_id | name
            --------+----------
                  1 | aaa
                  1 | bbb
                  2 | ccc

            UPDATE test SET name = 'ddd' WHERE name = 'aaa';

            user_id | name
            --------+----------
                  1 | ddd
                  1 | ddd
                  2 | ccc

        This is because the rule will find the user_id 1 for name
        'aaa' and then updates any row with user_id 1.

    2.  Change  the  WHERE  clause  in the UPDATE rule to compare
        against old.user_id and add "user_id  =  new.user_id"  to
        the  SET  clause.  Otherwise  it would not be possible to
        change the user_id because this thrown away by the  rule.

    3.  Don't  forget  the  ON  DELETE rule. Maybe you don't want
        once given user_id's to be changed or  deleted.  Then  2.
        and 3. aren't right.

>
> which means that any insert or update to the test view is stored in upper
> case as required.
>
> However, I still have two concerns about this.
>
> 1) What impact on performance does using a VIEW in this way have?

    Only  the  rewriting  overhead  per query. The rewrite system
    changes the querytree generated by the parser in such  a  way
    that  the planner/optimizer will get the same input as if the
    query really was the SELECT from test_table.  If you  have  a
    view

        CREATE VIEW test AS SELECT * FROM test_table;

    the two statements

        SELECT * FROM test;
        SELECT * FROM test_table;

    are  totally  equivalent from the planners/optimizers (and so
    from the executors) point of  view.  The  rewriting  overhead
    depends  on  how  complex the statements and rule definitions
    are. But not on the number of rows affected in the statement.
    Selecting  thousands of rows has the same speed than doing it
    from the real tables behind a view.  It's very small  because
    compared  against  parser/planner/optimizer it has to do very
    few system cache lookups and works mostly with the data  that
    is already in memory.

> 2) Users can still enter data straight into test_table in lower case
> bypassing the "rules"

    Not  necessarily.  Since  v6.4  rule  actions (in contrast to
    triggers up to now) inherit the  access  permissions  of  the
    owner of the relation they're fired on.

        CREATE TABLE test_table ...;
        CREATE VIEW test AS SELECT * FROM test_table;

        REVOKE ALL ON test_table FROM public;
        GRANT ALL ON test_table TO me;

        REVOKE ALL ON test FROM public;
        GRANT ALL ON test TO me;
        GRANT SELECT, INSERT, UPDATE, DELETE ON test TO public;

    Now  any  user  can access test, but nobody but me can access
    test_table.  Not even a SELECT does work. They  can  do  most
    things  on  test. But the rule actions are executed under the
    permissions of me, so they work silently.

    YOU  MUST  NOT  GRANT  ALL  TO  PUBLIC.  ALL  includes   RULE
    permission, so a user could change the rules on test, do some
    things (maybe on any of your other tables) and reinstall  the
    original state of rules!

    In  addition to that, consider the case you really don't want
    once given user_id's ever to change. Nor you like them to  be
    ever reused. But they should disappear on DELETE.

        CREATE TABLE test_table (user_id int,
                                 name varchar(10),
                                 pass varchar(10),
                                 alive bool);

        CREATE UNIQUE INDEX test_user_id ON test_table (user_id);

        CREATE VIEW test AS SELECT * FROM test_data
            WHERE alive;

        CREATE RULE ins_test AS ON INSERT TO test
            DO INSTEAD INSERT INTO test_table
            VALUES (new.user_id, UPPER(new.name), UPPER(new.pass), 't');

        CREATE RULE upd_test AS ON UPDATE TO test
            DO INSTEAD UPDATE test_table
            SET name = UPPER(new.name), pass = UPPER(new.pass)
            WHERE user_id = old.user_id AND alive;

        CREATE RULE del_test AS ON DELETE TO test
            DO INSTEAD UPDATE test_table
            SET alive = 'f'
            WHERE user_id = old.user_id AND alive;

    Plus  all  the REVOKE and GRANT. This setup denies changes to
    user_id, makes the row's disappear on DELETE but  throw's  an
    error 'cannot insert duplicate ...' if someone tries to reuse
    a user_id. Only the owner of the test_table can reincarnate a
    once deleted account.

>
> First off, is there an easier way to ensure that data is stored in uppercase
> for certain columns (not the whole table).  And if not does anyone have
> comments on performance issues, or ways of stopping users accidentally or
> intentionally inserting lower case data straight into the table rather than
> the view?

    The  Postgres rewrite rule system is the most powerful way to
    do that.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #