Thread: Couple Postgres View Questions

I have a couple questions about Postgres and Views.

I am curious about what Postgres actually does in regards to views and
the internal steps that it takes.

Are these the steps that it takes for each query that includes a view?
1. Dynamically Creates a Temp table with the view's select statement.
2. Executes the sql command against the view
3. Postgres destroys the temp table.


I understand that Postgres is not SQL 92 compliant with views because it
does not support updatable views.  Are there plans ever to support
updatable views?


Thanks,
Brian

"Brian Maguire" <bmaguire@vantage.com> writes:

> I have a couple questions about Postgres and Views.
>
> I am curious about what Postgres actually does in regards to views and
> the internal steps that it takes.
>
> Are these the steps that it takes for each query that includes a view?
> 1. Dynamically Creates a Temp table with the view's select statement.
> 2. Executes the sql command against the view
> 3. Postgres destroys the temp table.

This is not the way it works at all.  Views are implemented using
SQL rewrite rules, which are a PG feature.  So here's what actually
happens:

1.  The user runs a query against a view.
2.  The query parser changes the query using the rule, into a query
    against the underlying table(s).
3.  The query is optimized and executed.

> I understand that Postgres is not SQL 92 compliant with views because it
> does not support updatable views.  Are there plans ever to support
> updatable views?

You can make any PG view updatable by defining ON INSERT and/or ON
UPDATE rules for it, on a case-by-case basis.  The SQL2 updatable
views are theoretically implementable--there's been talk about it--but
are not currently there.

See the documentation for CREATE RULE and CREATE VIEW.

-Doug

----- Original Message -----
From: "Brian Maguire" <bmaguire@vantage.com>
To: "Postgresql General Mail List" <pgsql-general@postgresql.org>
Sent: Wednesday, July 16, 2003 6:44 PM
Subject: [GENERAL] Couple Postgres View Questions


>I have a couple questions about Postgres and Views.

>I am curious about what Postgres actually does in regards to views and
>the internal steps that it takes.

>Are these the steps that it takes for each query that includes a view?
>1. Dynamically Creates a Temp table with the view's select statement.
>2. Executes the sql command against the view
>3. Postgres destroys the temp table.

No, but if You really want to know how it works be prepared to spend few
months on this.
(I already spend much time with PostgreSQL sources, and I still do not know
enough).
However here is part from PostgreSQL documentation:

2.1. The Path of a Query
Here we give a short overview of the stages a query has to pass in order to
obtain a result.

  1.. A connection from an application program to the PostgreSQL server has
to be established. The application program transmits a query to the server
and receives the results sent back by the server.

  2.. The parser stage checks the query transmitted by the application
program (client) for correct syntax and creates a query tree.

  3.. The rewrite system takes the query tree created by the parser stage
and looks for any rules (stored in the system catalogs) to apply to the
querytree and performs the transformations given in the rule bodies. One
application of the rewrite system is given in the realization of views.

  Whenever a query against a view (i.e. a virtual table) is made, the
rewrite system rewrites the user's query to a query that accesses the base
tables given in the view definition instead.

  4.. The planner/optimizer takes the (rewritten) querytree and creates a
queryplan that will be the input to the executor.

  It does so by first creating all possible paths leading to the same
result. For example if there is an index on a relation to be scanned, there
are two paths for the scan. One possibility is a simple sequential scan and
the other possibility is to use the index. Next the cost for the execution
of each plan is estimated and the cheapest plan is chosen and handed back.

  5.. The executor recursively steps through the plan tree and retrieves
tuples in the way represented by the plan. The executor makes use of the
storage system while scanning relations, performs sorts and joins, evaluates
qualifications and finally hands back the tuples derived.


Pay attention to stage 3, there is a "secret" how View work.

>I understand that Postgres is not SQL 92 compliant with views because it
>does not support updatable views.  Are there plans ever to support
>updatable views?

PostgreSQL DOES support updatable views, but doesn't support SQL92 syntax
for updatable views.
However, rules are more powerful than updatable views. See the documentation
under "The Rule System".

Regards !