Re: Bug in pg_dump

The example from Tom Lane below results in a database which is not
possible to correctly dump using pg_dump.

The view v1 strangely becomes a table in the dump output?!

It's probably a quite useless database to dump in the first place, but
that is no excuse to generate an invalid dump, it would be better to
throw an exception and complain about "your database is retarded,
refusing to dump" or something like that.

regression=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tt | table | postgres
public | v1 | view | postgres
public | v2 | view | postgres
(3 rows)

ubuntu(at)ubuntu:/crypt/postgresql-9.1alpha3/src/bin/pg_dump$ ./pg_dump
regression | grep -v -E '^--' | grep -E '^.+$' | grep -v SET
CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO ubuntu;
CREATE TABLE tt (
f1 integer,
f2 integer
);
ALTER TABLE public.tt OWNER TO postgres;
CREATE TABLE v1 (
f1 integer,
f2 integer
);
ALTER TABLE public.v1 OWNER TO postgres;
CREATE VIEW v2 AS
SELECT v1.f1, v1.f2 FROM v1;
ALTER TABLE public.v2 OWNER TO postgres;
COPY tt (f1, f2) FROM stdin;
\.
CREATE RULE "_RETURN" AS ON SELECT TO v1 DO INSTEAD SELECT v2.f1, v2.f2 FROM v2;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM ubuntu;
GRANT ALL ON SCHEMA public TO ubuntu;
GRANT ALL ON SCHEMA public TO PUBLIC;

2011/1/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> regression=# create table tt(f1 int, f2 int);
> CREATE TABLE
> regression=# create view v1 as select * from tt;
> CREATE VIEW
> regression=# create view v2 as select * from v1;
> CREATE VIEW
> regression=# create or replace view v1 as select * from v2;
> CREATE VIEW
> regression=# drop view v1;
> ERROR:  cannot drop view v1 because other objects depend on it
> DETAIL:  view v2 depends on view v1
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
> regression=# drop view v2;
> ERROR:  cannot drop view v2 because other objects depend on it
> DETAIL:  view v1 depends on view v2
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.
>
> This isn't particularly *useful*, maybe, but it's hardly "impossible".
> And if we analyzed function dependencies in any detail, circular
> dependencies among functions would be possible (and useful).
>
>                        regards, tom lane

--
Best regards,

Joel Jacobson
Glue Finance

* Joel Jacobson wrote:

> The example from Tom Lane below results in a database which is not
> possible to correctly dump using pg_dump.
>
> The view v1 strangely becomes a table in the dump output?!

This is no bug, it's a feature (tm).

pg_dump is clever enough to detect the circular dependency and break it
open by creating v1 in two steps.

A view in PostgreSQL is simply an empty table with an ON SELECT DO
INSTEAD rule named "_RETURN" on it. pg_dump first creates the empty
table, then view v2 depending on that table, and finally the _RETURN
rule turning v1 into a view and reintroducing the circular dependency.

--
Christian

On 2011-01-13 11:31 AM +0200, Joel Jacobson wrote:
> The example from Tom Lane below results in a database which is not
> possible to correctly dump using pg_dump.
>
> The view v1 strangely becomes a table in the dump output?!

> CREATE RULE "_RETURN" AS ON SELECT TO v1 DO INSTEAD SELECT v2.f1, v2.f2 FROM v2;

This statement turns the table into a view.

Regards,
Marko Tiikkaja

Excerpts from Joel Jacobson's message of jue ene 13 06:31:06 -0300 2011:
> The example from Tom Lane below results in a database which is not
> possible to correctly dump using pg_dump.

I wouldn't care too much about that particular case -- you can't query
any of the views either.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Excerpts from Joel Jacobson's message of jue ene 13 06:31:06 -0300 2011:
>> The example from Tom Lane below results in a database which is not
>> possible to correctly dump using pg_dump.

> I wouldn't care too much about that particular case -- you can't query
> any of the views either.

Yeah, the particular case is useless, but IIRC it's possible to
construct non-useless cases where there's a circular dependency
involving a view and something else (probably a function, but I'm too
lazy to try to make an example right now). pg_dump's hack to break
the circularity by separating the view from its rule can save the day
in such cases.

regards, tom lane