Re: [HACKERS] Materialized views vs. primary keys
On 2016/04/06 8:48, David Fetter wrote: > On Tue, Apr 05, 2016 at 07:10:56PM -0400, Robert Haas wrote: >> On Tue, Apr 5, 2016 at 6:50 PM, David Fetter wrote: >>> Is there a reason other than lack of tuits for this restriction? >> >> "this" lacks an antecedent. > > Try to put a primary key on a materialized view, for example: > > CREATE TABLE foo(id SERIAL PRIMARY KEY, t text); > > CREATE MATERIALIZED VIEW bar AS SELECT * FROM foo; > > REFRESH MATERIALIZED VIEW bar; > > ALTER MATERIALIZED VIEW bar ADD PRIMARY KEY(id); > > At that last step, you get an error that bar is not a table. You get > an identical error with the hoary old trick of > > ALTER TABLE bar ADD PRIMARY KEY(id); Initially I thought it may be just an oversight of forgetting to pass ATT_MATVIEW to ATSimplePermissions() in ALTER TABLE processing and that there are no deeper technical reasons why that is so. But, there seem to be. On inspecting a little, it seems I can create unique indexes on a matview, but couldn't manage to set its columns to NOT NULL. Only allowed relations in the latter case are plain tables and foreign tables. I guess that follows from how NOT NULL constraints are enforced. > This lack prevents things that depend on primary keys (foreign keys, > logical replication, etc.) from operating on the materialized views. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views vs. primary keys
On Tue, Apr 05, 2016 at 07:10:56PM -0400, Robert Haas wrote: > On Tue, Apr 5, 2016 at 6:50 PM, David Fetter wrote: > > Is there a reason other than lack of tuits for this restriction? > > "this" lacks an antecedent. Try to put a primary key on a materialized view, for example: CREATE TABLE foo(id SERIAL PRIMARY KEY, t text); CREATE MATERIALIZED VIEW bar AS SELECT * FROM foo; REFRESH MATERIALIZED VIEW bar; ALTER MATERIALIZED VIEW bar ADD PRIMARY KEY(id); At that last step, you get an error that bar is not a table. You get an identical error with the hoary old trick of ALTER TABLE bar ADD PRIMARY KEY(id); This lack prevents things that depend on primary keys (foreign keys, logical replication, etc.) from operating on the materialized views. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views vs. primary keys
On Tue, Apr 5, 2016 at 6:50 PM, David Fetter wrote: > Is there a reason other than lack of tuits for this restriction? "this" lacks an antecedent. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Materialized views vs. primary keys
Folks, Is there a reason other than lack of tuits for this restriction? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers