Thread: Updatable view does not work [oops, quite long!]

Updatable view does not work [oops, quite long!]

From
Thiemo Kellner
Date:
Hi,

I write a little piece of software for me and my girl friend. I have
just a simple table encapsulated by a view such that we cannot see each
other's data.

This works fine so far. We insert into to the view which redirects the
insert by a rule to the base table. Perfect.

However, I have written two more rules, one for updates and one for
deletes. But those only work if all the fields have values. They do
nothing with effect on the base table when applied on rows with NULL in
the fields.

Has somebody got any ideas?

Cheers,

Thiemo

Some tests
----------
bash-2.05b$ psql -d passwort -h nyffeltrach.thiam.ch -U passwort_test
Passwort:
Willkommen bei psql 7.3.2, dem interaktiven PostgreSQL-Terminal.

Geben Sie ein:  \copyright für Urheberrechtsinformationen
                 \h für Hilfe über SQL-Anweisungen
                 \? für Hilfe über interne Anweisungen
                 \g oder Semikolon, um eine Abfrage auszuführen
                 \q um zu beenden

SSL-Verbindung (Verschlüsslungsmethode: EDH-RSA-DES-CBC3-SHA, Bits: 168)

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
(2 Zeilen)

passwort=> insert into passwort (programm, link, schluessel,
seriennummer, user_id, passwort, bemerkungen, gueltig_bis) values ('1',
'2', '3', '4', '5', '6', '7', to_date('2003-06-02', 'YYYY-MM--DD'));
INSERT 33984 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
  1        | 2    | 3          | 4            | 5       | 6        | 7
          | 2003-06-02
(3 Zeilen)

passwort=> delete from passwort where programm = 'Test';
DELETE 0

passwort=> delete from passwort where programm = '1' and link = '2' and
schluessel = '3' and seriennummer = '4' and user_id = '5' and passwort =
'6' and bemerkungen = '7' and gueltig_bis = to_date('2003-06-02',
'YYYY-MM--DD');
DELETE 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
(2 Zeilen)

passwort=> update passwort set link = '?' where programm = 'Test';
UPDATE 0

passwort=> update passwort set link = '?' where programm = 'Test' and
link is null and schluessel is null and seriennummer is null and user_id
= 'test' and passwort is null and bemerkungen is null and gueltig_bis is
null;
UPDATE 0

passwort=> delete from passwort where programm = 'Test' and link is null
and schluessel is null and seriennummer is null and user_id = 'test' and
passwort is null and bemerkungen = 'sets' and gueltig_bis is null;
DELETE 0

passwort=> select * from passwort where programm = 'Test' and link is
null and schluessel is null and seriennummer is null and user_id =
'test' and passwort is null and bemerkungen = 'sets' and gueltig_bis is
null;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
(1 Zeile)

passwort=> insert into passwort (programm, link, schluessel,
seriennummer, user_id, passwort, bemerkungen, gueltig_bis) values ('1',
'2', '3', '4', '5', '6', '7', to_date('2003-06-02', 'YYYY-MM--DD'));
INSERT 33985 1

passwort=> update passwort set programm = '10' where programm = '1' and
link = '2' and schluessel = '3' and seriennummer = '4' and user_id = '5'
and passwort = '6' and bemerkungen = '7' and gueltig_bis =
to_date('2003-06-02', 'YYYY-MM--DD');
UPDATE 1

passwort=> select * from passwort;
  programm | link | schluessel | seriennummer | user_id | passwort |
bemerkungen | gueltig_bis
----------+------+------------+--------------+---------+----------+-------------+-------------
  Test     |      |            |              | test    |          |
sets        |
  54654    |      |            |              |         |          |
          |
  10       | 2    | 3          | 4            | 5       | 6        | 7
          | 2003-06-02
(3 Zeilen)



Table
-----
CREATE TABLE pwd (
     pwd_id           INT2           DEFAULT nextval('s_pwd')
   , programm         VARCHAR(30)
   , link             VARCHAR(70)
   , schluessel       VARCHAR(30)
   , seriennummer     VARCHAR(30)
   , user_id          VARCHAR(70)
   , passwort         VARCHAR(30)
   , bemerkungen      VARCHAR(255)
   , guetlig_bis      DATE
   , benutzer         VARCHAR(30)    NOT NULL
   , CONSTRAINT pk_pwd PRIMARY KEY (pwd_id)
   , CONSTRAINT uk_pwd UNIQUE (programm, link, seriennummer, user_id)
);

View
----
CREATE VIEW AS
    SELECT
         pwd.programm
       , pwd.link
       , pwd.schluessel
       , pwd.seriennummer
       , pwd.user_id
       , pwd.passwort
       , pwd.bemerkungen
       , pwd.gueltig_bis
    FROM pwd
    WHERE (pwd.benutzer = "varchar"("current_user"()))
;

delete Rule
-----------
CREATE RULE ru_v_passwort_del
    AS ON DELETE TO passwort
       DO INSTEAD
          DELETE FROM pwd
       WHERE (
              (
           (
            (
             (
              (
               (
                (
                 (pwd.programm = old.programm)
                 AND
                 (pwd.link = old.link)
                )
                AND
                (pwd.schluessel = old.schluessel)
               )
               AND
               (pwd.seriennummer = old.seriennummer)
              )
              AND
              (pwd.user_id = old.user_id)
             )
             AND
             (pwd.passwort = old.passwort)
            )
            AND
            (pwd.bemerkungen = old.bemerkungen)
           )
           AND
           (pwd.gueltig_bis = old.gueltig_bis)
          )
          AND
          (pwd.benutzer = ("current_user"())::character varying)
         )
;

update Rule
-----------
CREATE RULE ru_v_passwort_upd
    AS ON UPDATE TO passwort
       DO INSTEAD
          UPDATE pwd
        SET
          programm = new.programm
        , link = new.link
        , schluessel = new.schluessel
        , seriennummer = new.seriennummer
        , user_id = new.user_id
        , passwort = new.passwort
        , bemerkungen = new.bemerkungen
        , gueltig_bis = new.gueltig_bis
     WHERE (
        (
         (
          (
           (
            (
             (
              (
               (pwd.programm = old.programm)
               AND
               (pwd.link = old.link)
              )
              AND
              (pwd.schluessel = old.schluessel)
             )
             AND
            (pwd.seriennummer = old.seriennummer)
           )
           AND
           (pwd.user_id = old.user_id)
          )
          AND
          (pwd.passwort = old.passwort)
         )
         AND
         (pwd.bemerkungen = old.bemerkungen)
        )
        AND
        (pwd.gueltig_bis = old.gueltig_bis)
           )
           AND
           (pwd.benutzer = ("current_user"())::character varying)
          )
;

Grants
------
insert on pwd to ...;
update, insert and delete on passwort to ...;

--
root ist die Wurzel allen Übels



Re: Updatable view does not work [oops, quite long!]

From
Tom Lane
Date:
Thiemo Kellner <thiemo@thiam.ch> writes:
> However, I have written two more rules, one for updates and one for
> deletes. But those only work if all the fields have values. They do
> nothing with effect on the base table when applied on rows with NULL in
> the fields.

Not very surprising, since "WHERE foo = bar" cannot succeed if either
foo or bar is null.

You should not be comparing every field of the rows anyway.  You should
make sure that pwd has a primary key (which by definition must be
non-null) and then check only the primary key field or fields in the
rule WHERE clauses.

            regards, tom lane

Re: Updatable view does not work [oops, quite long!]

From
Manfred Koizar
Date:
On Wed, 04 Jun 2003 22:54:34 +0200, Thiemo Kellner <thiemo@thiam.ch>
wrote:
>CREATE RULE ru_v_passwort_del
>    AS ON DELETE TO passwort
>       DO INSTEAD
>          DELETE FROM pwd
>       WHERE (
>                 [...]
>                 (pwd.link = old.link)
> [...]

This will not evaluate to true, if old.link is null.  Remember that
    NULL = NULL
is neither true nor false, but unknown.  And
    true AND unknown
is still unknown.

You need somethink bulky like
    pwd.link = old.link OR (pwd.link IS NULL AND old.link IS NULL)

or you include the primary key into your view and use
    pwd.pk = old.pk
in the rule.

Servus
 Manfred

Re: Updatable view does not work [oops, quite long!]

From
Thiemo Kellner
Date:
> Not very surprising, since "WHERE foo = bar" cannot succeed if either
> foo or bar is null.

This confirms my suspicion.

> You should not be comparing every field of the rows anyway.  You should
> make sure that pwd has a primary key (which by definition must be
> non-null) and then check only the primary key field or fields in the
> rule WHERE clauses.

Well this was a futile attempt to hide the primary key pwd_id on the
base table from the end user.

Thanks,

Thiemo

--
root ist die Wurzel allen Übels


Re: Updatable view does not work [oops, quite long!]

From
Thiemo Kellner
Date:
Manfred Koizar wrote:
> You need somethink bulky like
>     pwd.link = old.link OR (pwd.link IS NULL AND old.link IS NULL)

I choose this solution. So I can hide the technical PK from the user.
Thanks for the hint. Works perfectly.

Tschau,

Thiemo

--
root ist die Wurzel allen Übels