Re: [HACKERS] Materialized views vs. primary keys

2016-04-05 Thread Amit Langote 
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

2016-04-05 Thread David Fetter 
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

2016-04-05 Thread Robert Haas 
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

2016-04-05 Thread David Fetter 
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