Re: OLAP, Aggregates, and order of operations

Tom Lane wrote:
>
> mlw <markw(at)mohawksoft(dot)com> writes:
> >> If the needed parameters are all the same datatype, maybe you could put
> >> them into an array and pass the array as a single argument to the
> >> aggregate.
>
> > How would you do this without having to make multiple SQL calls?
>
> I was thinking something like
>
> select my_aggregate(my_array_constructor(foo, bar, baz)) from ...
>
> where my_array_constructor is a quick hack C routine to build a
> 3-element array from 3 input arguments (s/3/whatever you need/).
> Someday we ought to have SQL syntax to build an array value from
> a list of scalars, but in the meantime an auxiliary function is the
> only way to do it.

Interesting. Kind of ugly, but interesting.

So, what would the order of operation be?

I assume "my_array_constructor()" would be called first, and the return value
then be passed to "my_aggregate()" along with the state value being set to the
initial state, then subsequent calls to "my_array_constructor()", followed by
"my_aggregate()" for each additional row in the group?

I need to think about that.

>
> The overhead of constructing and then interpreting the temporary
> array value is slightly annoying, but I don't think it'll be horribly
> expensive. See the existing aggregate-related routines in numeric.c
> if you need some help with the C coding.

<postgres use story>
I can do the C stuff, I have tons of C and C++ functions written for Postgres
already, when I get the time to make them clean enough to contribute to the
Postgres project, I will. (Text manipulation, search engine, date manipulation,
xmcd, analysis functions, decode, and others) If you are interested in seeing a
half Oracle, half Postgres site, take a look at http://www.dotclick.com. (You
will need a Windows box)

It is pretty evenly split between postgres and oracle. All "member" related
data is on Oracle. All music related data is in Postgres. It has saved us
probably $50K to $100 in Oracle database licenses and hardware to do it this
way.

We have three postgres boxes. One master, and two slaves. The master gets
updated with new information from various sites. The program which does the
updating, on the master, creates a SQL log script of everything it does. The
script is then run against the slaves to maintain consistency. A web farm is
split evenly between the two slaves.

It is pretty cool.

(As a side note, we are using Oracle for session management across a bunch of
servers. Sadly we can not use postgres for this (we would love too), sessions
are mostly updates and deletes, maybe when 7.2 comes out, but I'm still not
sure about that.)

</postgres use story>