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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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