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=85=20
bughunt=3D# INSERT INTO cars_view ( runs ) VALUES ( True );
INSERT 0 2
-- =85 one in cars=85=20
bughunt=3D# SELECT count(*) FROM ONLY cars;
count=20
-------
1
(1 row)
-- .. and the other in wrecks.
bughunt=3D# SELECT count(*) FROM wrecks;
count=20
-------
1
(1 row)
bughunt=3D#=20
-- Insert into cars though, leads to single INSERT:
bughunt=3D# INSERT INTO cars ( runs ) VALUES ( True );
INSERT 0 1
---------- end ACTION!=20
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
