Re: Bug #521: Backup - Restore Problem in VIEWs

Stefan Hadjistoytchev (sth(at)hq(dot)bsbg(dot)net) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Backup - Restore Problem in VIEWs

Long Description
1. Tested on:
OS: Red Hat 6.2 Linux, SlackWare 7.0 Linux, CygWin
PostgreSQL: 7.1.3, 7.2b3

2. Base configuration
- I create a database test using "createdb testdb"
- I create a TABLE "table1" with a columns "col1" and "col2" and inserted a single values "1" and "2".
- Then I create a FUNCTION "f1" that selects everything from table "table1".
- Finally I create a VIEW "view1" that shows only the first column of "f1()";
==> Result: Everything works as expected.

3. Backup
- I do a BACKUP using "pg_dump -d -D testdb > dump1"
==> Result: the View description is not written as expected

4. Restore
- I drop the "testdb" database using "dropdb testdb"
- I created an empty database "testdb" using "createdb testdb"
- I do a RESTORE using "psql testdb -f dump1"
==>Result: ERROR: parser: parse error at or near "."

CONCLUSION: I could not BACKUP & RESTORE PostgreSQL databases that contain this type of VIEWS which are very simple and used very often :(

Sample Code
-- Create Database
createdb testdb

-- Commands send to "psql"
CREATE TABLE "table1" ( "col1" smallint, "col2" smallint );
INSERT INTO "table1" ("col1", "col2") VALUES (1,2);
CREATE FUNCTION "f1" () RETURNS table1 AS 'SELECT * FROM table1' LANGUAGE 'sql';
CREATE VIEW "view1" AS SELECT col1( f1() );

-- received from "pg_dump -d -D testdb > dump1"
CREATE TABLE "table1" ( "col1" smallint );
INSERT INTO "table1" ("col1") VALUES (1);
CREATE FUNCTION "f1" () RETURNS table1 AS 'SELECT * FROM table1;
CREATE VIEW "view1" as SELECT f1().col1;

-- Drop and create database
dropdb testdb
createdb testdb

-- Restore Database using "psql testdb < dump1"
ERROR: parser: parse error at or near "."

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)org writes:
> - I do a BACKUP using "pg_dump -d -D testdb > dump1"
> ==> Result: the View description is not written as expected

I've repaired this for 7.2. If you need a solution sooner, I believe
that the 7.2 patch would apply cleanly to 7.1. See
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c.diff?r1=1.88&r2=1.89&f=c

regards, tom lane