What is the purpose of views' predefinitions in dump file

I'm working on the project where we are using :sql schema format for the Active Record dumper (to support more complex logic like triggers).

We have a lot of views and I can see in the database/structure.sql file that some of them have "predefined" schema with NULLs like:

CREATE VIEW public.my_view AS
  SELECT
    NULL::integer AS record_id,
    NULL::integer AS another_record_id,
    NULL::double precision AS some_field;

Then, thousands of lines later, the definition is added with:

CREATE OR REPLACE VIEW public.my_view AS
  -- actual query

I can't see any references to the view between schema "predefinition" and definition with my SQL query. Also, there are other views that are created right away (without that schema "predefinition").

I was looking in Active Record documentation but I couldn't find any hints. Rails uses pg_dump under the hood but I don't see anything relevant in pg_dump docs too.

Why some views require predefining the schema in advance, while others don't, even if none of them are referenced between predefinition and actual definition in database/structure.sql file? Is it to prevent some race conditions when using yet another structure (like materialized views or something)?

This is because views can have circular dependencies like this:

CREATE SCHEMA s;
CREATE OR REPLACE VIEW s.v AS SELECT 1 a;
CREATE OR REPLACE VIEW s.w AS SELECT a FROM s.v;
CREATE OR REPLACE VIEW s.v AS SELECT a FROM s.w;

Those views can't be queried in this form. E.g. select * from s.w produces:

SQL Error [42P17]: ERROR: infinite recursion detected in rules for relation "w"

But running the following pg_dump command:

pg_dump -U postgres -d postgres -s -n s

Produces this output:

CREATE SCHEMA s;

CREATE VIEW s.v AS
SELECT
    NULL::integer AS a;

CREATE VIEW s.w AS
 SELECT v.a
   FROM s.v;

CREATE OR REPLACE VIEW s.v AS
 SELECT w.a
   FROM s.w;

As you can see, there's your dummy view which has to be created, because s.v couldn't access the not-yet existing s.w at the point of creation.

You can also find this logic in pg_dump.c's createDummyViewAsClause function, whose documentation reads:

/*
 * Create a dummy AS clause for a view.  This is used when the real view
 * definition has to be postponed because of circular dependencies.
 * We must duplicate the view's external properties -- column names and types
 * (including collation) -- so that it works for subsequent references. [...]
 */

Recommended topics

Hot tags