0

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this :

I have a function which runs on 4 shards. It looks like this :

CREATE OR REPLACE
FUNCTION count_comm(
    id_toto INTEGER,
    OUT out_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

It returns me a SETOF INTEGERS like this :

p_commentaire=> SELECT count_toto(42);
 count_commentaire_from_id_toto 
---------------------------------
                         2172022

So basically, each of the result is the COUNT() of each shard, it works great as I said. The thing is, I wanted to know if there's a way to SUM() all the results to get a total instead of having the 4 results individually ...

Edit : Just to add something, PlProxy needs a 'SETOF' return when you run the query on all shards.

2

You could just use the SQL statement:

SELECT sum(out_nb_comm) AS sum_out_nb_comm
FROM   count_comm($in_id_alias);

Or, if you want to wrap it in a function, a simple SQL function does the job:

CREATE OR REPLACE FUNCTION sum_count_comm(in_id_alias int)
   RETURNS int AS
$func$
SELECT sum(out_nb_comm)::int
FROM   count_comm($1));
$func$ LANGUAGE sql;
2

So, I found a workaround...

First I made a proxy function on which the first proxy function will run :

CREATE OR REPLACE
FUNCTION p_count_toto(
    in_id_toto INTEGER
) RETURNS INTEGER AS $func$
BEGIN
        RETURN (SELECT sum(tmp_nb_toto) out_nb_toto FROM count_comm(in_id_alias));
END;
$func$ LANGUAGE plpgsql;

And I modified my first PL/Proxy function which looks like this now :

CREATE OR REPLACE FUNCTION count_toto(
        in_id_toto INTEGER,
        OUT tmp_nb_toto INTEGER
) RETURNS SETOF INTEGER AS $$
        CLUSTER 'main'; RUN ON ALL;
$$ LANGUAGE plproxy;

I think that's not the most elegant way to do it, because at the application level, devs will have to change the name of the procedure they'll call, but it does the work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.