Providing ANSI SQL-2003 Window Functions

Now that the important OVER (PARTITION BY ORDER BY) Windowing functions have finally been added to the v8.4 community version of PostgreSQL, when will they be made available in the GridSQL shared nothing parallel architecture?

The value of these now 6-year-old ANSI standard functions is that they allow sophisticated computations to be performed within the database server without needing to extract the data into the application layer through a cursor, and are therefore much more efficient. This is especially true for GridSQL, which is designed to scale PostgreSQL to very large multi-terabyte databases, where extracting data to a separate application layer for post-processing is even more problematic.

Virtually every commercial database platform, and especially Oracle, has provided these Window Aggregate functions for years. I believe it's critical for GridSQL to do so, both to maintain compatibility with Oracle and especially with the v8.4 community version of PostgreSQL.

With the availability of these functions in non GridSQL versions of PostgreSQL, the incremental effort to implement them in GridSQL should now be relatively small.

Hi Eric,

We have discussed this, but it is not yet clear when it will implemented. It is actually not trivial to do, although there are a couple of approaches.

If you really would like to see this implemented soon, please feel free to contact us and perhaps an arrangement can be made that ensures this becomes a top priority.

Regards,

Mason Sharp

Mason-

Yes, we really would like to see the ANSI (ISO) SQL-2003 Window functions implemented in GridSQL as soon as possible, especially since they are now standard features on all of the remaining PostgreSQL platforms except GridSQL.

Without this functionality, in our view GridSQL is simply not a viable data warehousing alternative to any of the major commercial database products (Oracle, DB2, SQL Server, Teradata, or Netezza), all of which have implemented support for these important functions. We rely heavily on these functions to compute results within the data warehouse itself, rather than extracting recordsets to post-process in a separate application server layer before redepositing the results back in the database itself. This latter approach represents an extremely inefficient and inelegant solution architecture.

Since, to the best of my knowledge, none of the other Open Source databases have implemented these ANSI SQL functions, doing so represents a significant competitive advantage for PostgreSQL. Having this same functionality in GridSQL simply rationalizes the entire family of PostgreSQL products, thereby presents a viable Open Source migration path for those customers seeking parallel processing scalability in a shared-nothing Open Source environment.

Hi Eric,

I understand.

We have multiple things we are working on. If you would like to see this implemented soon, please contact our sales organization to try and work out an arrangement for sponsoring development of this feature.

Thanks,

Mason