Skip site navigation (1) Skip section navigation (2)

View options (as in "security_barrier") break pg_dump

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: View options (as in "security_barrier") break pg_dump
Date: 2012-08-11 21:23:04
Message-ID: 13980.1344720184@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
CREATE OR REPLACE VIEW makes it possible to create views that have
circular dependencies, for instance:

regression=# create table tt(f1 int, f2 int);
CREATE TABLE
regression=# create view v1 as select * from tt;
CREATE VIEW
regression=# create view v2 as select * from v1;
CREATE VIEW
regression=# create or replace view v1 with (security_barrier = true) as select * from v2;

Now try to pg_dump this.  What you will get is

CREATE TABLE v1 (
    f1 integer,
    f2 integer
)
WITH (security_barrier=true);

CREATE VIEW v2 AS
    SELECT v1.f1, v1.f2 FROM v1;

CREATE RULE "_RETURN" AS ON SELECT TO v1 DO INSTEAD SELECT v2.f1, v2.f2 FROM v2;

and of course that will fail to reload:
ERROR:  unrecognized parameter "security_barrier"

pg_dump is doing its best to deal with the circularity by breaking the
definition of v1 into two parts, but we have not given it syntax that
will work for that when the view has options.

You might argue that the above example is useless, which it is, but
less-useless examples of circular dependencies can be contrived.
This is just an easy example to show the problem.

			regards, tom lane


pgsql-hackers by date

Next:From: Jeff JanesDate: 2012-08-11 22:11:40
Subject: Re: New statistics for WAL buffer dirty writes
Previous:From: Tom LaneDate: 2012-08-11 18:05:30
Subject: Re: error handling in logging hooks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group