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.=20
> [...]
> 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.=20

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
  ...