pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalculation dependencies in views
10+ messages / 3 participants
[nested] [flat]
* Calculation dependencies in views
@ 2000-01-05 00:14 Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 05:35 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Rick Delaney @ 2000-01-05 00:14 UTC (permalink / raw)
To: pgsql-sql
I have a table with, say, a dozen fields and I want to end up with a view with
around 50 fields, calculated from the original dozen.
So it would be something like this:
CREATE VIEW final AS
SELECT
x, y, z,
(x + y) as a,
(y + z) as b,
(x + y) * z as c,
(y + z) * x as d,
(x + y) * z + x as e,
(y + z) * x + x as f
FROM my_table;
except my expressions are longer and more complicated. However, my expressions
do have similar dependencies and redundancies.
My question is what is a good way of dealing with this? I was going to do
something like
CREATE VIEW one AS
SELECT
id,
(x + y) as a,
(y + z) as b,
FROM my_table;
CREATE VIEW two AS
SELECT
m.id,
o.a * m.z as c,
o.b * m.x as d,
FROM my_table m, one o
WHERE m.id = o.id;
etc. but I'll end up with a lot of levels and joins going this route which I
expect will be pretty slow.
Can someone recommend anything to me? Should I be approaching this from a
completely different angle i.e other than views?
I'm pretty new at this so any pointers will be appreciated.
--Rick
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 01:03 ` Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2000-01-05 01:03 UTC (permalink / raw)
To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql
Rick Delaney wrote:
> I have a table with, say, a dozen fields and I want to end up with a view with
> around 50 fields, calculated from the original dozen.
>
> So it would be something like this:
>
> CREATE VIEW final AS
> SELECT
> x, y, z,
> (x + y) as a,
> (y + z) as b,
> (x + y) * z as c,
> (y + z) * x as d,
> (x + y) * z + x as e,
> (y + z) * x + x as f
> FROM my_table;
>
> except my expressions are longer and more complicated. However, my expressions
> do have similar dependencies and redundancies.
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.
> My question is what is a good way of dealing with this? I was going to do
> something like
>
> CREATE VIEW one AS
> SELECT
> id,
> (x + y) as a,
> (y + z) as b,
> FROM my_table;
>
> CREATE VIEW two AS
> SELECT
> m.id,
> o.a * m.z as c,
> o.b * m.x as d,
> FROM my_table m, one o
> WHERE m.id = o.id;
>
> etc. but I'll end up with a lot of levels and joins going this route which I
> expect will be pretty slow.
In the above sample, it will end up in a join, because even after
rewriting there will be more than one used rangetable entries in the
query. But if you're able to do the nesting in a manner like this:
CREATE VIEW one AS
SELECT
x, y, z,
x + y AS sum_xy,
x + z AS sum_xz,
y + z AS sum_yz
FROM my_table;
CREATE VIEW final AS
SELECT
x, y, z,
sum_xy AS a,
sum_yz AS b,
sum_xy * z AS c,
sum_yz * x AS d,
sum_xy * z + x AS e,
sum_yz * x + x AS f
FROM one;
and keep all views follow strictly one path
final -> n-1 -> n-2 -> ... -> two -> one
you'll end up the a construct that requires nested levels of rewriting.
But the resulting query will definitely be a single table scan having
all your complicated expressions attached.
BTW: if you nest many views, you'll probably get an ERROR about
"infinite recursion" from the rewriter. In that case, you must raise the
value of the
#define REWRITE_INVOKE_MAX 10
at line 1466 in src/backend/rewrite/rewriteHander.c to some value fairly
above your nesting level.
> Can someone recommend anything to me? Should I be approaching this from a
> completely different angle i.e other than views?
Well, I'm a rewriting- and trigger-man. It really depends on the usual
behaviour of your data and application.
Keep in mind, that the above view technique requires each value to be
computed for each single view attribute for each single row at each
SELECT. So if you usually SELECT relatively small amounts of your data,
or update big amounts of it between the SELECT's, it wouldn't hurt.
But if you're dealing with huge set's of row's at SELECT time and rarely
changes, you might be better off with setting up a table having all the
fields in place, and let a trigger put all the calculated values in
place.
> I'm pretty new at this so any pointers will be appreciated.
You're welcome.
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) #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-05 20:15 ` Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rick Delaney @ 2000-01-05 20:15 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: pgsql-sql
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.
>
[snip]
> But if you're able to do the nesting in a manner like this:
>
> CREATE VIEW one AS
> SELECT
> x, y, z,
> x + y AS sum_xy,
> x + z AS sum_xz,
> y + z AS sum_yz
> FROM my_table;
>
> CREATE VIEW final AS
> SELECT
> x, y, z,
> sum_xy AS a,
> sum_yz AS b,
> sum_xy * z AS c,
> sum_yz * x AS d,
> sum_xy * z + x AS e,
> sum_yz * x + x AS f
> FROM one;
>
> and keep all views follow strictly one path
>
> final -> n-1 -> n-2 -> ... -> two -> one
>
> you'll end up the a construct that requires nested levels of rewriting.
> But the resulting query will definitely be a single table scan having
> all your complicated expressions attached.
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).
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.
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?
I can (and will) try this out with my stuff but I'm just trying to understand the
general principles.
> But if you're dealing with huge set's of row's at SELECT time and rarely
> changes, you might be better off with setting up a table having all the
> fields in place, and let a trigger put all the calculated values in
> place.
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?
Thanks a lot for your help,
--Rick
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 21:49 ` Jan Wieck <wieck@debis.com>
2000-01-05 23:13 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2000-01-05 21:49 UTC (permalink / raw)
To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql
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) #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-05 23:13 ` Rick Delaney <rick@consumercontact.com>
2000-01-06 00:10 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
0 siblings, 1 reply; 10+ messages in thread
From: Rick Delaney @ 2000-01-05 23:13 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: pgsql-sql
Jan Wieck wrote:
>
> Rick Delaney wrote:
>
> > Jan Wieck 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
Sorry to be stupid but with my terribly phrased question I can't tell if you mean
"Yes, you are correct that you will still get 'rule plan string too big' errors
with nested views" or "Yes, nested views will work fine as I described despite
your erroneous interpretation of what you read (but you may have to fix the
recursion limit as I already said)". :-)
I'm 99% sure you mean the latter, now, after re-reading "The Postgres Rule
System" and your first reply to me. I guess the only reason I'm not 100% sure is
I don't understand where the 'rule plan string too big' limit comes from. Is it
in the storage of the view/rule (related to the 8K limit on row size)? I guess
that would make sense.
I suppose if I re-read enough times I'll get it.
Thanks again,
--Rick
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 23:13 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-06 00:10 ` Jan Wieck <wieck@debis.com>
2000-01-06 01:47 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Wieck @ 2000-01-06 00:10 UTC (permalink / raw)
To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql
Rick Delaney wrote:
> Jan Wieck wrote:
> >
> > 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
>
> Sorry to be stupid but with my terribly phrased question I can't tell if you mean
> "Yes, you are correct that you will still get 'rule plan string too big' errors
> with nested views" or "Yes, nested views will work fine as I described despite
> your erroneous interpretation of what you read (but you may have to fix the
> recursion limit as I already said)". :-)
>
> I'm 99% sure you mean the latter, now, after re-reading "The Postgres Rule
> System" and your first reply to me. I guess the only reason I'm not 100% sure is
> I don't understand where the 'rule plan string too big' limit comes from. Is it
> in the storage of the view/rule (related to the 8K limit on row size)? I guess
> that would make sense.
Stop (rereading) recursion now - you got it.
It's exactly the storage of rules (views are implemented via rewriting
rules) in combo with the 8K limit, that causes this "rule plan string too
big" error. Just the the view/rule creation utility does some extra check
for it.
Rules are stored as a special kind of printable querytree string. A
querytree is the systems internal representation of a query, and they are
really verbose and thus - well - huge (compared to what the user typed in).
Example 1:
CREATE VIEW v1 AS
SELECT a, a + b AS ab, a + b + c AS abc FROM t1;
Example 2:
CREATE VIEW v1_sub AS
SELECT a, c, a + b AS ab FROM t1;
CREATE VIEW v1 AS
SELECT a, ab, ab + c AS abc FROM v1_sub;
These two examples will result in exactly the same querytree after
rewriting, if you SELECT from v1. The second needs two passes in the
rewriter, but that's the only difference.
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) #
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 23:13 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-06 00:10 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
@ 2000-01-06 01:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-06 19:42 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2000-01-06 01:47 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: Rick Delaney <rick@consumercontact.com>; pgsql-sql
Jan Wieck <wieck@debis.com> writes:
> Example 1:
> CREATE VIEW v1 AS
> SELECT a, a + b AS ab, a + b + c AS abc FROM t1;
> Example 2:
> CREATE VIEW v1_sub AS
> SELECT a, c, a + b AS ab FROM t1;
> CREATE VIEW v1 AS
> SELECT a, ab, ab + c AS abc FROM v1_sub;
> These two examples will result in exactly the same querytree after
> rewriting, if you SELECT from v1. The second needs two passes in the
> rewriter, but that's the only difference.
Actually, there's another big difference: the stored rule plan string
for v1 in the second case is shorter than it is in the first case,
because the a+b additions don't show up as operator nodes in v1's
definition in the second case. (If the references to v1_sub were
flattened out before the rule were stored, it wouldn't take two passes
of rewriting to expand the rule. But they aren't, and it does ;-).)
I tried these examples and got:
select rulename,length(ev_action) from pg_rewrite where rulename like '_RETv%';
rulename |length
----------+------
_RETv1 | 1922
_RETv1_sub| 1558
_RETv1_up | 1566
(3 rows)
So, if your problem is that you need to work around the rule plan string
length limit, then indeed eliminating common subexpressions with nested
views can be a win. There isn't much win in this example, but then we
only got rid of two additions here.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-05 23:13 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-06 00:10 ` Re: [SQL] Calculation dependencies in views Jan Wieck <wieck@debis.com>
2000-01-06 01:47 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-06 19:42 ` Rick Delaney <rick@consumercontact.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rick Delaney @ 2000-01-06 19:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jan Wieck <wieck@debis.com>; pgsql-sql
Tom Lane wrote:
>
> Jan Wieck <wieck@debis.com> writes:
>
> > These two examples will result in exactly the same querytree after
> > rewriting, if you SELECT from v1. The second needs two passes in the
> > rewriter, but that's the only difference.
>
> Actually, there's another big difference: the stored rule plan string
> for v1 in the second case is shorter than it is in the first case,
> because the a+b additions don't show up as operator nodes in v1's
> definition in the second case. (If the references to v1_sub were
> flattened out before the rule were stored, it wouldn't take two passes
> of rewriting to expand the rule. But they aren't, and it does ;-).)
Interesting. Anyway, I grok it now. Thank you both for all your help.
Cheers,
--Rick
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
@ 2000-01-05 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-05 20:14 ` Re: [SQL] Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2000-01-05 05:35 UTC (permalink / raw)
To: Rick Delaney <rick@consumercontact.com>; +Cc: pgsql-sql
Rick Delaney <rick@consumercontact.com> writes:
> I have a table with, say, a dozen fields and I want to end up with a
> view with around 50 fields, calculated from the original dozen.
> So it would be something like this:
> CREATE VIEW final AS
> SELECT
> x, y, z,
> (x + y) as a,
> (y + z) as b,
> (x + y) * z as c,
> (y + z) * x as d,
> (x + y) * z + x as e,
> (y + z) * x + x as f
> FROM my_table;
> except my expressions are longer and more complicated. However, my
> expressions do have similar dependencies and redundancies.
> My question is what is a good way of dealing with this?
I think what you are getting at is avoiding duplicate computations,
such as the repeated evaluations of x + y in the above example?
My guess is that you are wasting your time to worry about it, unless
the repeated calculations are *really* expensive (a float add is down
in the noise ... though a ten-thousand-digit NUMERIC trig function
might not be).
I don't believe that nested views will buy anything given the current
Postgres implementation of views. As far as I understand it, the
rewriter works by substitution of expressions, so that a reference to
a second-level view will end up being executed just the same as if
you'd written out the expressions in full. (This will probably change
once we have the much-discussed, little-implemented ability to write
subselects in FROM clauses; but that's a release or two away yet.)
If you are indeed dealing with 10K-digit numerics or something equally
CPU-hoggish, you might consider storing the intermediate results into
a temp table and then doing a second select using the temp table.
But you'd need to be saving an awful lot of calculations to make that
profitable, I think.
If you're just concerned about avoiding code complexity, then by
all means go with the nested views; that will sweep the complicated
expressions under the rug, and someday it will even buy you some
efficiency. But it's not going to help much if you need to shave
cycles today.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Calculation dependencies in views
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 05:35 ` Re: [SQL] Calculation dependencies in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-05 20:14 ` Rick Delaney <rick@consumercontact.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rick Delaney @ 2000-01-05 20:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
>
> I think what you are getting at is avoiding duplicate computations,
> such as the repeated evaluations of x + y in the above example?
>
> My guess is that you are wasting your time to worry about it, unless
> the repeated calculations are *really* expensive (a float add is down
> in the noise ... though a ten-thousand-digit NUMERIC trig function
> might not be).
I was more interested in reducing code complexity but this is good to know
anyway. Thanks a lot for your help. Your last remark brings another question to
mind.
If I do this:
rick=> CREATE TABLE foo (foo numeric(9,2));
CREATE
rick=> CREATE VIEW bar AS SELECT foo * foo AS bar FROM foo;
CREATE
rick=> \d foo
Table = foo
+----------------------------------+--------------------------+-------+
| Field | Type | Length|
+----------------------------------+--------------------------+-------+
| foo | numeric | 9.2 |
+----------------------------------+--------------------------+-------+
rick=> \d bar
View = bar
Query = SELECT ("foo"."foo" * "foo"."foo") AS "bar" FROM "foo";
+----------------------------------+--------------------------+-------+
| Field | Type | Length|
+----------------------------------+--------------------------+-------+
| bar | numeric |65535.65|
+----------------------------------+--------------------------+-------+
^^^^^^^^
What governs the precision of the numeric in the view "bar"? I couldn't find the
specifics of this in the docs. Is it something I need to ever think of? If so,
can I cast it to a smaller numeric? I think 18.4 should be sufficient in this
case.
Thanks again,
--Rick
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2000-01-06 19:42 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-05 00:14 Calculation dependencies in views Rick Delaney <rick@consumercontact.com>
2000-01-05 01:03 ` Jan Wieck <wieck@debis.com>
2000-01-05 20:15 ` Rick Delaney <rick@consumercontact.com>
2000-01-05 21:49 ` Jan Wieck <wieck@debis.com>
2000-01-05 23:13 ` Rick Delaney <rick@consumercontact.com>
2000-01-06 00:10 ` Jan Wieck <wieck@debis.com>
2000-01-06 01:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-06 19:42 ` Rick Delaney <rick@consumercontact.com>
2000-01-05 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-05 20:14 ` Rick Delaney <rick@consumercontact.com>
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