Re: pg_dump: CREATE TABLE + CREATE RULE vs. relreplident

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Tom> Yeah, possibly. The existing pg_dump coding dates from before we
> Tom> had CREATE OR REPLACE VIEW.

> As it happens it does not; the issue came up originally because of a
> hack I came up with, and I've never used any pg version so old it didn't
> have CREATE OR REPLACE VIEW. Nor does it look like the change was ever
> backpatched (or at least not that far).

Sorry, that's mere historical revisionism. The oldest PG version I still
have in captivity is 7.0, and in it pg_dump does this:

$ createdb db1
CREATE DATABASE
$ psql db1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

db1=# select version();
version
------------------------------------------------------------------
PostgreSQL 7.0.3 on hppa2.0-hp-hpux10.20, compiled by gcc 2.95.3
(1 row)

db1=# create table t1 (f1 int, f2 text);
CREATE
db1=# create view v1 as select * from t1;
CREATE 148340 1
db1=# \q
$ pg_dump db1
\connect - postgres
CREATE TABLE "t1" (
"f1" int4,
"f2" text
);
CREATE TABLE "v1" (
"f1" int4,
"f2" text
);
COPY "t1" FROM stdin;
\.
CREATE RULE "_RETv1" AS ON SELECT TO v1 DO INSTEAD SELECT t1.f1, t1.f2 FROM t1;
$

Later (in 7.1, looks like) we improved the pg_dump code to dump views as
views, but the underlying ability to dump the ON SELECT rule separately
was still there. I think what you are remembering is commit
86a069bbed9264daaa85270ece0a2d5959017336, but that just re-enabled the
aboriginal behavior when we discover a circularity involving a view rule.
If I'd had to write actual new dumping code, I probably would not have
done it like that, and might have hit on the CREATE OR REPLACE VIEW
solution instead.

OTOH, some experimenting shows that 7.3 is the oldest version that accepts
the syntax CREATE OR REPLACE VIEW, so at the time we might not have wanted
to use that solution in pg_dump anyway.

regards, tom lane