Re: Dependency graph of all tuples relied upon in a query answer

On Fri, Sep 01, 2006 at 09:30:57AM -0400, Merlin Moncure wrote:
> On 8/31/06, Randall Lucas <rlucas(at)tercent(dot)com> wrote:
> >Now that I have this query, in order to make my case, I need to "sign
> >off" on all of the individual data that went into it. I would like to
> >do something like:
> >
> > select last_query_shown_tuples();
> > schema | table_name | pk_columns | pk_values
> > --------+---------------+------------+-----------
> > public | company | [id] | [2]
> > public | officer | [id] | [3]
> > public | insider_trade | [id] | [1]
> > (3 rows)
>
> right. in sql, except for a few miscellaneous things that are session
> based, information state is kept in the tables and if you want to keep
> things relational all information should be have a primary key.

Agreed. As noted in my example above, all of my tables have primary
keys (serials for simplicity, although I represented the pk columns and
values as arrays because of the possibility of other sorts of keys,
like multicolumn).

> A key tenet of relational thinking is to reduce all information to its
> functional dependencies, and to try and avoid as much as possible
> keeping information state in the data in a declarative sense.
> last_query_shown_tuples() is imo a violation in that sense.

I used last_query_shown_tuples() as a quick example. It seems more
likely that it would be implemented in fact as something like EXPLAIN,
where it acts upon a given query. Like,

EXPLAIN DEPENDENCIES SELECT * FROM ...

It seems that the query planner *must* know which rows, from which
tables, actually get used in producing the output for a given query.
Given that this info is present somewhere in the depths, is there a way
to get this information out to the app level?

Best,

Randall

--
Randall Lucas Tercent, Inc. DF93EAD1