Re: Multithreaded query onto 4 postgresql instances

On 10 Feb 2011, at 9:01, Alessandro Candini wrote:

> I have installed 4 different instances of postgresql-9.0.2 on the same machine, on ports 5433, 5434, 5435, 5436.

I do hope you intend to put those databases on different machines eventually, or some such? Otherwise you probably
didn'tgain anything by splitting your database up like that - you've just reduced the available resources on that
singlemachine. 

> Why I have to do something tricky like this is long too explain...

It would help to know what you're trying to achieve by splitting your database up like this. We don't need the full
story,just a summary is fine; Maybe this is some experimental setup that's more related to multi-threading than to the
actualdatabase design? Maybe management smoked something outlandish and put you up with this? Maybe this is a
macroscopicattempt to table partitioning? 

> Then I have developed a C function using libpq which creates 4 threads, each one which query a 1/4 of the db. After
thatI merge the results in one single response. 
>
> My function works fine, but I need to include it inside a postgresql instance in order to launch it as a normal SQL
query(SELECT myfunc(...);). 

> I have read the documentation here
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET,but I'm pretty confuse and I
don'tknow if this is the right way to accomplish my goal. 

What are you confused about? That's a chapter about set-returning functions written in SQL - perhaps you're looking for
informationabout set-returning functions from an external library like yours? Perhaps you're looking for:
http://www.postgresql.org/docs/9.0/interactive/xfunc-c.html

You say you don't know if this is the right way to accomplish your goal - which is...?

> Have you got any ideas or suggestions?


As others have suggested, you should probably have a look at table-partitioning, possibly in combination with
tablespacesif you want to divide your database among multiple disks/filesystems. Putting them in different servers on
thesame hardware is probably not going to be a very good solution. As you've already found out, it makes querying the
datasilly difficult. But, we don't know the reason you're doing that of course. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d567a3611733933816998!