Re: pg_dump compatibility level / use create view instead of create table/rule

Hi Tom,

Thanks again for your quick reply! I've attached three images of the view:

1. The result of the alter table syntax you sent ( and it's definitely a view, actually, I created a few views in the past few weeks, and they all get the same error when trying to restore.)

2. The View definition

3. View info schema result

In text here, running this:
ALTER TABLE ONLY my_view REPLICA IDENTITY DEFAULT;

Returns:
ERROR: "myschema.my_view" is not a table or materialized view
SQL state: 42809

Also, it's been about three hours so far into the restore on the server that is 9.5.18, but the restore of that view is still a table, hasn't changed to a view yet. I assumed it would run the DDL statements first, then the data copy and possibly the rules last, so I'm still waiting for the restore to complete to see if it changes. The 9.2.9 Server just fails.

And running this: select * from INFORMATION_SCHEMA.views where table_name = 'my_view' returns the expected result.

I'll be away tomorrow, but will reply back on Friday with the result of your restore direction + the result of zgrepping the dump without the Fc switch that I have (sorry, didn't have a chance to do that yet.)

Thanks again,

Alex

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Wednesday, October 9, 2019 6:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alex Williams valenceshell(at)protonmail(dot)com writes:
>
> > Ugh, sorry again, missed one more part, here is the full error for the create table in the log:
> > pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 TABLE my_view postgres
> > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "REPLICA"
> > LINE 19: ...E ONLY my_view REPLICA ID...
> > ^
> > Command was: CREATE TABLE my_view (
> > product character varying(255),
> > product_id integer,
> > payer...
>
> This seems to be a chunk of a command like
>
> ALTER TABLE ONLY my_view REPLICA IDENTITY FULL;
>
> (or possibly REPLICA IDENTITY NOTHING), which pg_dump will emit if the
> table has a non-default relreplident setting. I do not, however,
> understand your statement that this is a view. AFAIK views should never
> have non-default relreplident settings, and besides that, the TOC entry
> description says it's a table not a view. (If it's a materialized view,
> it could have relreplident, but its TOC entry still shouldn't say TABLE.)
>
> Anyway it's hardly surprising that 9.2 is choking on that syntax; it
> doesn't have the REPLICA IDENTITY feature.
>
> pg_dump actually is taking some pity on you here, in that it's emitting
> this as a separate ALTER TABLE command, not as part of CREATE TABLE
> directly. This means you just need to get 9.2 to ignore the error
> on the ALTER TABLE and keep plugging. I think what you need to do
> is something like pg_restore to stdout and then pipe stdout to psql,
> rather than connecting directly to the target server.
>
> Another fix, if this table was only accidentally labeled with
> a replica identity (which I'm suspecting because you don't seem
> to recognize the feature), is to get rid of the marking in the
> source database:
>
> ALTER TABLE ONLY my_view REPLICA IDENTITY DEFAULT;
>
> regards, tom lane