Re: I incrementally altered my database into a state where backups couldn't be restored.

> Which version of Postgres are you using?
PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)

> I can't reproduce that on 8.1 or 8.2 or CVS HEAD.
> [...]
> Not the CREATE OR REPLACE with a SELECT * you posted.

I was lazy, I apologize. I typed the CREATE OR REPLACE myself. I get
the same thing as Heikki from pg_dump. The point is that it breaks when
restoring with psql[1]

> I think that the described behavior is actually pretty harmless:

Having been bitten by this, I must respectfully disagree. When I found
the problem, I was copying a database over to a demonstration laptop
for a show. I missed the error message when it scrolled past, so it
seemed to work. Fortunately, I happened to check the area of my app
that used the missing view, so I found it before trying to demo.

Now that I know about it, I can fix my scripts to help me watch out for
it. It's just one of those things I didn't think to test for until I
ran into it.

> Hm, we could call pg_get_viewdef() on dependent views and then verify
that the resulting view compiles without error.

For what it's worth, I like this idea. I do ALTER TABLEs infrequently
enough that I would prefer to spend a little extra time to make sure
things are consistent.

Adam Tomjack

[1]
$ pg_dump -U postgres -S postgres test > test.sql
$ psql -U postgres -d test -c "DROP SCHEMA public CASCADE;"
$ psql -U postgres -d test -c "DROP LANGUAGE plpgsql CASCADE;"
$ psql -U postgres -d test -c "CREATE SCHEMA public AUTHORIZATION
dbusername;"
$ psql -U postgres -d test -f test.sql
...
psql:test.sql:1396: ERROR: common column name "aid" appears more than
once in left table
psql:test.sql:1399: ERROR: relation "public.v_a_b_c" does not exist
...