Materialized views vs. primary keys

On Tue, Apr 5, 2016 at 6:50 PM, David Fetter <david@fetter.org> 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

On Tue, Apr 05, 2016 at 07:10:56PM -0400, Robert Haas wrote:

On Tue, Apr 5, 2016 at 6:50 PM, David Fetter <david@fetter.org> 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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@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

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 <david@fetter.org> 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