From: Jan Wieck <wieck@debis.com>
To: Rick Delaney <rick@consumercontact.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Calculation dependencies in views
Date: Wed, 05 Jan 2000 22:49:00 +0100
Message-ID: <3873BC4C.3D4E4BCC@debis.com> (raw)
References: <38728CE2.C1756D3E@consumercontact.com>
<3872987B.C289AB59@debis.com>
<3873A662.37B94F7E@consumercontact.com>
Rick Delaney wrote:
> Jan Wieck wrote:
> >
> > Rick Delaney wrote:
> >
> > >
> > Be aware that 6.5.* might be unable to deal with the resulting size of
> > the rewrite rule. And I'm not sure that I'll get TOAST ready for 7.0 to
> > handle it.
>
> I'd have to take a good look at all the expressions again to know if I could do
> this but I think I probably can. Only, with what I've started on I'm already
> getting "rule plan string too big" errors. (What I've started on is just one
> view that doesn't have all the expressions in it yet).
That's what I meant with "might be unable...". You talked about a 50
column view with complicated expressions, and that's surely too large for
the current implementation.
> So I will just get the same thing with nested views, no? That is how I
> understand the "The Postgres Rule System" section of the manual and Tom Lane's
> answer to my post.
As the author of "The Postgres Rule System" I say yes, except that it
requires some recursions in the rewriter. Thus, it will take a little
longer to rewrite, but that's a constant time per query, not depending on
the amount of data you're selecting. So if you're happy with the response
times for your test queries, it's not the rewriter any more if you get
response time problems later.
>
> If I could structure things as
>
> subset_1_n -> subset_1_n-1 -> ... -> subset_1_2 -> subset_1_1
> subset_2_n -> subset_2_n-1 -> ... -> subset_2_2 -> subset_2_1
> :
> :
> subset_m_n -> subset_m_n-1 -> ... -> subset_m_2 -> subset_m_1
>
> final -> join subset_1_n, subset_2_n, ..., subset_m_n
>
> where each view subset_i_n has a usably-sized rule plan string then could I get
> this thing working?
As said, there is a limit of 10 recursions for the rewriter actually
hardcoded in rewriteHandler.c (line 1466). Raise that value if you get
"infinite loop" errors.
> This will be primarily INSERT/UPDATE a row and then SELECT the view of that row.
> Am I right that a trigger would make more sense in this case then?
If you only insert one row, then select that one row, the cascaded view
setup is best IMHO. The trigger solution would only be better, if you
seldom do INSERT/UPDATE, but often SELECT and maybe SELECT many rows.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: wieck@debis.com, rick@consumercontact.com
Subject: Re: [SQL] Calculation dependencies in views
In-Reply-To: <3873BC4C.3D4E4BCC@debis.com>
References: <38728CE2.C1756D3E@consumercontact.com> <3872987B.C289AB59@debis.com> <3873A662.37B94F7E@consumercontact.com> <3873BC4C.3D4E4BCC@debis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox