Semantic error in `CREATE VIEW' breaks database - Mailing list pgsql-bugs

Short version:
I mistakenly created a view with a conditional containing a table name
instead of a column name and got a broken database.  I am left with a
`view?' that cannot be dropped, and a database that cannot be pg_dump'ed
without errors.

This is with PostgreSQL-6.4-BETA5.

I have attempted to recover by taking the result of `pg_dump -z'
despite the error message, editing out the offensive `CREATE TABLE
packagelist' in it, and putting it in a new database.  Is this correct?

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        :    SL Baur
Your email address    : steve@xemacs.org


System Configuration
- ---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux-2.1.127 (glibc-2.07)

  PostgreSQL version (example: PostgreSQL-6.4)  :   PostgreSQL-6.4-BETA5

  Compiler used (example:  gcc 2.8.0)        : pgcc-1.1a


Please enter a FULL description of your problem:
- ------------------------------------------------
See attached transaction log.  I created a view:

create view packagelist as
    select sw_index,
           package,
           sw_name,
           status,
           ss_name,
           ss_description,
           build_date,
           build_host,
           sw_version,
           sw_description,
           sw_home_url,
           sw_directory
    from builds, software, sw_status
    where package=sw_index and
          status=sw_status and
          (build_host='altair' or build_host='deanna');

and now cannot delete it after several NOTICE: messages at the time of
creation.

The conditional `status=sw_status' is a typo, `sw_status' is a table
not a column, but it shouldn't cause PostgreSQL to break, I think.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
- ----------------------------------------------------------------------
The database is not proprietary and ~70k uncompressed in pg_dump
format and I can email a copy if you need it.




If you know how this problem might be fixed, list the solution below:
- ---------------------------------------------------------------------
No.

=======================================================================
steve=> create view packagelist as select sw_index,package,sw_name,status,ss_name,
ss_description,build_date,build_host,sw_version,sw_description,sw_home_url,sw_directory from builds, software,
sw_statuswhere package=sw_index and status=sw_status and (build_host='altair' or build_host='deanna'); 
NOTICE:  unknown node tag 704 in OffsetVarNodes()
NOTICE:  Node is: { IDENT "sw_status" }
NOTICE:  copyObject: don't know how to copy 704
CREATE
steve=> drop view packagelist;
ERROR:  badly formatted planstring "IDENT "sw_"...
steve=> create view packagelist as select sw_index,package,sw_name,status,ss_name,
ss_description,build_date,build_host,sw_version,sw_description,sw_home_url,sw_directory from builds, software,
sw_statuswhere package=sw_index and status=sw_index and (build_host='altair' or build_host='deanna'); 
ERROR:  packagelist relation already exists
steve=> drop view packagelist;
ERROR:  badly formatted planstring "IDENT "sw_"...

steve=> drop table packagelist;
ERROR:  badly formatted planstring "IDENT "sw_"...
steve=> \d

Database    = steve
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | steve            | build_types                      | table    |
 | steve            | builds                           | table    |
 | steve            | builds_test                      | table    |
 | steve            | date_test                        | table    |
 | steve            | packagelist                      | view?    |
 | steve            | software                         | table    |
 | steve            | sw_status                        | table    |
 | steve            | test_sequence                    | sequence |
 | steve            | time_test                        | table    |
 +------------------+----------------------------------+----------+
steve=> \d software

Table    = software
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| sw_index                         | int4                             |     4 |
| sw_name                          | varchar()                        |    20 |
| sw_description                   | varchar()                        |    80 |
| sw_home_url                      | varchar()                        |   100 |
| sw_directory                     | varchar()                        |    64 |
+----------------------------------+----------------------------------+-------+
steve=> \d sw_status

Table    = sw_status
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| ss_index                         | int4                             |     4 |
| ss_name                          | varchar()                        |    20 |
| ss_description                   | varchar()                        |    80 |
+----------------------------------+----------------------------------+-------+
steve=> \d builds

Table    = builds
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| package                          | int4                             |     4 |
| status                           | int4                             |     4 |
| build_date                       | date                             |     4 |
| build_host                       | varchar()                        |    20 |
| sw_version                       | varchar()                        |    20 |
+----------------------------------+----------------------------------+-------+


$ pgdump -z steve > steve.0
zsh: correct 'pgdump' to 'pg_dump' [nyae]? y
dumpRules(): SELECT failed for table packagelist