Date: Sat, 26 Oct 2019 13:56:30 -0700
From: Andres Freund <andres@anarazel.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alex Williams <valenceshell@protonmail.com>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: pg_dump compatibility level / use create view instead of create
 table/rule
Message-ID: <20191026205630.cubjcxgs2rhke7xa@alap3.anarazel.de>
References: 
 <NFqxoEi7-8Rw9OW0f-GwHcjvS2I4YQXov4g9OoWv3i7lVOZdLWkAWl9jQQqwEaUq6WV0vdobromhW82e8y5I0_59yZTXcZnXsrmFuldlmZc=@protonmail.com>
 <30565.1570597261@sss.pgh.pa.us>
 <KAJrgQ84Kmh-_zwP7MtMPVbx32uE78hQaiAhxpdylcsiISMqRSL3UUGiJQZlbpkT4L5R6BMj-cCpBqUXK1R34HgUSq1Y3I8QjMpLBACQKDo=@protonmail.com>
 <MzQZmmoeor-gQY3YlRIVMCXpdWHT8oohKzczWGjPrm4tvY8o8S74QYm_kOcKMztQvME5vhi6MQy8eVZYxtRPAE9uSe30gjTdr2MOYy-L6wE=@protonmail.com>
 <18dWrRFcmR7qHhsVTvYfGuRT8Tx5LijYQ72SeMWaya_0FiBkS5BFiYTa47R5WVTlADzXAqI85sZgkKPFVdJP2UWsUwJty3KuZ4eZvlVXO-I=@protonmail.com>
 <17815.1570661198@sss.pgh.pa.us>
 <-pa2tdKbasT5nWrYgCtvhKtMDyPIboaoG53ZSPb0MfCvzMUaVJDgQddN1ve-XLllFBKmQ8kGJ7InqBcYwq5CJY2DnLI4HVWTHyYx8wwBTtM=@protonmail.com>
 <1095.1570720814@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1095.1570720814@sss.pgh.pa.us>
Precedence: bulk

Hi,

On 2019-10-10 11:20:14 -0400, Tom Lane wrote:
> regression=# create table mytab (f1 int primary key, f2 text);
> CREATE TABLE
> regression=# create view myview as select * from mytab group by f1;
> CREATE VIEW
> 
> This situation is problematic for pg_dump because validity of the
> view depends on the existence of mytab's primary key constraint,
> and we don't create primary keys till late in the restore process.
> So it has to break myview into two parts, one to emit during normal
> table/view creation and one to emit after index creation.
> 
> With 9.5's pg_dump, what comes out is:
> 
> --
> -- Name: myview; Type: TABLE; Schema: public; Owner: postgres
> --
> 
> CREATE TABLE public.myview (
>     f1 integer,
>     f2 text
> );
> 
> ALTER TABLE ONLY public.myview REPLICA IDENTITY NOTHING;

Ick.


> The reason we get "REPLICA IDENTITY NOTHING" is that a view's relreplident
> is set to 'n' not 'd', which might not have been a great choice.

Hm, yea. I wonder if we should add a REPLICA_IDENTITY_INVALID or such,
for non relation relkinds?  I'm mildly inclined to think that setting it
to REPLICA_IDENTITY_DEFAULT is at least as confusing as
REPLICA_IDENTITY_DEFAULT...


> This is fixed in v10 and up thanks to d8c05aff5.  I was hesitant to
> back-patch that at the time, but now that it's survived in the field
> for a couple years, I think a good case could be made for doing so.

+1


> 	/* pretend view is a plain table and dump it that way */
> 	viewinfo->relkind = 'r';	/* RELKIND_RELATION */
> 	viewinfo->relkind = 'r';	/* RELKIND_RELATION */
> +	viewinfo->relreplident = 'd';	/* REPLICA_IDENTITY_DEFAULT */
> 
> That's mighty ugly but it doesn't seem to carry any particular
> risk.

I also could live with this, given it'd only be in older back-branches.

Greetings,

Andres Freund