Stored procedure work-around

As some of you know, GridSQL does not support stored procedures natively, due to the fact that a procedure may be executing statements on different nodes.

I did want to mention that if you really need stored procedures to enforce some business logic or because existing reporting applications use them, there is a work-around: using dblink.

You can create a dummy Postgres Plus proxy database that contains the stored procedures and makes use of dblink_connect(), dblink_exec(), dblink_send_query() and dblink_get_result().

The application needing to use stored procedures will need to connect to the dummy database. Keep in mind that this will add a little latency, but if it makes it a lot easier to integrate GridSQL into your existing infrastructure, you might want to consider doing something like tihs.

Thanks,

Mason Sharp

Can you provide a little more detail on this approach for GridSQL 2.0 ?

Thanks

Hi Ted,

Since GridSQL understands the communications protocol that PostgreSQL uses, you set up dblinks to GridSQL, and submit your queries that way. Your stored function code should run on a "dummy" PostgreSQL server, with all stored functions using the dblink functionality to communicate with GridSQL.

Please see the dblink documentation in PostgreSQL.

Thanks,

Mason

Sorry to resurrect an old thread if it's inappropriate.

But, as an alternative to dblink, could you use PLProxy on top of GridSQL? From what I understand, GridSQL hashes data off a defined key value to determine which partition into which to store the data, right? PLProxy does the same thing. So, if you could make PLProxy use the same partitioning algorithm as GridSQL, this should work, shouldn't it? Seems like this might give you more flexibility and possibly better performance than using dblink. Using this hybrid approach, I get all the benefits and flexibility of the stored procedures that PLProxy allows, plus I get the ad-hoc query capabilities and nice front end of GridSQL.

So, a few questions. Is it possible to make this work or am I really misunderstanding something? Is this a really bad idea for some other reason I'm not thinking of? How does GridSQL's hashing algorithm work? Would it be possible to replicate this inside of PLProxy?

Thanks for any information.

Greig Wise

Hi Greig,

Actually, this is something I thought about a long time ago, and am surprised no one has done this yet.

I think to make it work best, one should modify GridSQL to accept PL/Proxy partitioning functions. Alternatively, one could add the GridSQL partitioning code to PL/Proxy.

All DDL should go through GridSQL for things to work properly.

If you do implement it, I hope you share your experiences here.

Cheers,

Mason