Thread: funny update, say update 1, changed 2 records.

funny update, say update 1, changed 2 records.

From
Neil Dugan
Date:

I have been having some trouble with a particular table view.  An UPDATE
command is not only changing the applicable record it is also creating a
new record as well.

wholesale=# select * from accounts_supplier;
 id |       name       | contact |   addr    | addr2 |      town      | postcode | state | phone | fax | account_type
----+------------------+---------+-----------+-------+----------------+----------+-------+-------+-----+--------------
  1 | ABC construction | TOM     |           |       |                |          | NSW   |       |     | Cash Only
  2 | test             |         |           |       |                |          |       |       |     | 7 Day
  3 | build-4-U        | boss    | somewhere |       | back of beyond |          |       |       |     | 7 Day
(3 rows)

wholesale=# update accounts_supplier set addr='nowhere' where id=3;
UPDATE 1
wholesale=# select * from accounts_supplier;
 id |       name       | contact |  addr   | addr2 |      town      | postcode | state | phone | fax | account_type
----+------------------+---------+---------+-------+----------------+----------+-------+-------+-----+--------------
  1 | ABC construction | TOM     |         |       |                |          | NSW   |       |     | Cash Only
  2 | test             |         |         |       |                |          |       |       |     | 7 Day
  6 | build-4-U        | boss    | nowhere |       | back of beyond |          |       |       |     | 7 Day
  3 | build-4-U        | boss    | nowhere |       | back of beyond |          |       |       |     | 7 Day
(4 rows)


Can anyone tell me why this is happening and how to fix it.

Here are the table and view definitions.

CREATE TABLE account_type (
    number smallint,
    name character varying(20)
);

CREATE TABLE address (
    addr character varying(40),
    addr2 character varying(40),
    town character varying(20),
    postcode character varying(10),
    state character(4)
);

CREATE TABLE supplier (
    id bigserial NOT NULL,
    name character varying(40),
    phone character varying(20),
    fax character varying(20),
    contact character varying(40),
    account_type smallint DEFAULT 0
)
INHERITS (address);

CREATE VIEW accounts_supplier AS
    SELECT supplier.id,
        supplier.name,
        supplier.contact,
        supplier.addr,
        supplier.addr2,
        supplier.town,
        supplier.postcode,
        supplier.state,
        supplier.phone,
        supplier.fax,
        account_type.name AS account_type
    FROM supplier, account_type
    WHERE (account_type.number = supplier.account_type);

CREATE RULE accounts_supplier_update
    AS ON UPDATE TO accounts_supplier
    DO INSTEAD UPDATE supplier
    SET name = new.name,
        contact = new.contact,
        addr = new.addr,
        addr2 = new.addr2,
        town = new.town,
        postcode = new.postcode,
        state = upper((new.state)::text),
        phone = new.phone,
        fax = new.fax,
        account_type = (SELECT account_type.number
            FROM account_type
            WHERE ((account_type.name)::text = (new.account_type)::text))
    WHERE (supplier.id = new.id);



wholesale=# select version();
                                                         version

--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red Hat
3.4.3-22)
(1 row)

wholesale=# select * from account_type;
 number |   name
--------+-----------
      0 | Cash Only
      1 | 7 Day
      2 | 30 Day
      3 | 60 Day
      4 | 90 Day
(5 rows)

Thanks for any help
Regards Neil.




Re: funny update, say update 1, changed 2 records.

From
Tom Lane
Date:
Neil Dugan <postgres@butterflystitches.com.au> writes:
> I have been having some trouble with a particular table view.  An UPDATE
> command is not only changing the applicable record it is also creating a
> new record as well.

I think it's because your UPDATE is updating supplier.account_type which
is part of the join key for the view's underlying join.  Somehow that
results in the update applying to all supplier rows that join to the
same account_type row.  Don't have time now to work out exactly why ...
but the easiest solution is probably to not use a join in the view.
Instead fetch the account_type.name via a subselect in the view's
output list.

            regards, tom lane

Re: funny update, say update 1, changed 2 records.

From
Neil Dugan
Date:
On Sat, 2005-06-11 at 13:29 -0400, Tom Lane wrote:
> Neil Dugan <postgres@butterflystitches.com.au> writes:
> > I have been having some trouble with a particular table view.  An UPDATE
> > command is not only changing the applicable record it is also creating a
> > new record as well.
>
> I think it's because your UPDATE is updating supplier.account_type which
> is part of the join key for the view's underlying join.  Somehow that
> results in the update applying to all supplier rows that join to the
> same account_type row.  Don't have time now to work out exactly why ...
> but the easiest solution is probably to not use a join in the view.
> Instead fetch the account_type.name via a subselect in the view's
> output list.
>
>             regards, tom lane

Hi Tom Lane,

Thanks for the reply, something doesn't sound right here.  How can an
update cause a new record to be created (id = 6) as well as updating the
correct record (id = 3)?

I am not sure what you mean by 'subselect' in the views output list.
Isn't that what I am doing in creating the view?

Regards Neil