Single row INSERT on updatable VIEW on top of partitioned table is run against each inheriting table, giving multiple rows from single insert.

From: Terje Elde <terje(at)pam(dot)as>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Single row INSERT on updatable VIEW on top of partitioned table is run against each inheriting table, giving multiple rows from single insert.
Date: 2013-07-24 12:19:05
Message-ID: 3BF82E03-C10D-4238-9EC2-46CFF9983D8F@pam.as
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

Short summary:
If I run a single INSERT against an updateable VIEW on top of a partitioned table, it's run against all the tables/partitions. I'd expect that with UPDATE and DELETE, but not with INSERT. Result is multiple rows for a single INSERT, one for each table.

I ran into this on 9.3beta1, confirmed on 9.3beta2. Example is run against the latter.

---------- SCHEMA

-- Main table
CREATE TABLE cars (
id serial primary key,
runs boolean not null
);

-- And a table that INHERITS it.
CREATE TABLE wrecks ( ) INHERITS ( cars );

---------- VIEW

-- Simple view
CREATE VIEW cars_view AS
SELECT * FROM cars;

---------- ACTION!

-- INSERTing into the view actually inserts two rows…

bughunt=# INSERT INTO cars_view ( runs ) VALUES ( True );
INSERT 0 2

-- … one in cars…

bughunt=# SELECT count(*) FROM ONLY cars;
count
-------
1
(1 row)

-- .. and the other in wrecks.

bughunt=# SELECT count(*) FROM wrecks;
count
-------
1
(1 row)

bughunt=#

-- Insert into cars though, leads to single INSERT:

bughunt=# INSERT INTO cars ( runs ) VALUES ( True );
INSERT 0 1

---------- end ACTION!

What I'd expect to happen would be for the INSERT to only run against the parimary cars table.

To me, this looks like a bug, but opinions might differ. If this is intended and desirable behaviour, I'd say it at least warrants a mention in the docs, such as under "Updatable views" here:
http://www.postgresql.org/docs/9.3/static/sql-createview.html

Right now, that says:
"If the view is automatically updatable the system will convert any INSERT, UPDATE or DELETE statement on the view into the corresponding statement on the underlying base relation."

If the underlaying relation is cars, I'd expect it to behave more as an insert on cars.

Terje

Browse pgsql-bugs by date

  From Date Subject
Next Message Terje Elde 2013-07-24 12:37:21 Single row INSERT on updatable VIEW on top of partitioned table is run against each inheriting table, giving multiple rows from single insert.
Previous Message Willy-Bas Loos 2013-07-24 12:18:17 Re: BUG #8154: pg_dump throws error beacause of field called "new".