Re: erroneous restore into pg_catalog schema

On Sun, January 13, 2013 22:09, Tom Lane wrote:

>
> BTW, although Erik claimed this behaved more sanely in 9.2, a closer
> look at the commit logs says that the bogus commit shipped in 9.2,
> so AFAICS it's broken there too.  But earlier releases would have
> rejected the SET as expected.  I think we should assume that existing
> code is expecting the pre-9.2 behavior.
>


$ psql
psql (9.2.2-REL9_2_STABLE-20130113_1054-4ae5ee6c9b4dd7cd7e4471a44d371b228a9621c3)

Running the same on 9.2.2 (with latest patches):

$ ../../pgsql.HEAD/bug/test.sh
DROP DATABASE backupbug;
CREATE DATABASE backupbug;
drop schema if exists s cascade;
DROP SCHEMA
create schema s;
CREATE SCHEMA
create table s.t as select i from generate_series(1,1000) as f(i);
SELECT 1000
\dt+ pg_catalog.t
No matching relations found.
drop schema if exists s cascade;
DROP SCHEMA
pg_restore: connecting to database for restore
pg_restore: creating TABLE t
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 169; 1259 26204 TABLE t aardvark
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied to create
"pg_catalog.t"
DETAIL:  System catalog modifications are currently disallowed.
    Command was: CREATE TABLE t (
    i integer
);



pg_restore: restoring data for table "t"
pg_restore: [archiver (db)] Error from TOC entry 2780; 0 26204 TABLE DATA t aardvark
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "t" does not exist
    Command was: COPY t (i) FROM stdin;

pg_restore: setting owner and privileges for TABLE t
pg_restore: setting owner and privileges for TABLE DATA t
WARNING: errors ignored on restore: 2
\dn
  List of schemas
  Name  |  Owner
--------+----------
 public | aardvark
(1 row)

\dt+ s.
No matching relations found.
\dt+ pg_catalog.t
No matching relations found.