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
