agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Williams <valenceshell@protonmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: pg_dump compatibility level / use create view instead of create table/rule
Date: Wed, 09 Oct 2019 18:46:38 -0400
Message-ID: <17815.1570661198@sss.pgh.pa.us> (raw)
In-Reply-To: <18dWrRFcmR7qHhsVTvYfGuRT8Tx5LijYQ72SeMWaya_0FiBkS5BFiYTa47R5WVTlADzXAqI85sZgkKPFVdJP2UWsUwJty3KuZ4eZvlVXO-I=@protonmail.com>
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>
Alex Williams <valenceshell@protonmail.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
view thread (12+ messages) latest in thread
Message-ID: <17815.1570661198@sss.pgh.pa.us>
Permalink: ../17815.1570661198@sss.pgh.pa.us/
Also on: postgresql.org/message-id/17815.1570661198@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, valenceshell@protonmail.com
Subject: Re: pg_dump compatibility level / use create view instead of create table/rule
In-Reply-To: <17815.1570661198@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox