Stefan Hadjistoytchev (sth@hq.bsbg.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
usedvery 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