Thread: A complex plproxy query

This is a complex question, and I couldn't form it in a short and easy
way, and I'm sorry for that.

First of all, let me introduce you to the DB (to form a question), for
you to understand what am I talking about. The module looks like a
social network, just the users have friends, which can be in different
groups.

Also it is clustered with PLPROXY by user_id, so the user itself, and
his friends list (the list of ID's) is always in the same DB, but the
information about the friends is not (it is clustered through all the
partitions). Here is a little sketch of a the needed tables:

CREATE TABLE friend
(
   id bigint,
   user_id integer,
   friend_id integer,
   group_id bigint,
...
);
This table is a 'friend link' from one user to another, which can be
marked as being in some 'group', and the backward link exists also (from
the 2nd user to the 1st), which can possibly be in another 'group'.

CREATE TABLE user
(
  user_id integer,
  nickname text,
.... -- lots of other info
);
This is just a user table.

Both of these are clustered by user_id. I need to form the following
query, for it to be as fast as possible (here it is written as if it the
DB was not partitioned):
SELECT something FROM user u, friend f
WHERE u.user_id = f.friend.id
AND f.user_id = $1 (this is given as an argument)
AND f.group_id = $2

So to say, give me the list of friends (not only their ID's, but all the
needed columns!) of given individual, which are in a given group. That
seems ok without plproxy, but with using it, I can't imagine how can I
form a nice query, or a function (or a set of plpgsql + plproxy
functions) to do the job.

Thanks in advance and regards,
Igor Katson.

On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:

> So to say, give me the list of friends (not only their ID's, but all the
> needed columns!) of given individual, which are in a given group. That
> seems ok without plproxy, but with using it, I can't imagine how can I
> form a nice query, or a function (or a set of plpgsql + plproxy
> functions) to do the job.

You need to do it in two steps - first run a query on the partition the
user is in to get list of friends ids, then run a second RUN ON ALL
query with

WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2

to gather all friend info in parallel

--
------------------------------------------
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
   Services, Consulting and Training

Hannu Krosing wrote:
> On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:
>
>
>> So to say, give me the list of friends (not only their ID's, but all the
>> needed columns!) of given individual, which are in a given group. That
>> seems ok without plproxy, but with using it, I can't imagine how can I
>> form a nice query, or a function (or a set of plpgsql + plproxy
>> functions) to do the job.
>>
>
> You need to do it in two steps - first run a query on the partition the
> user is in to get list of friends ids, then run a second RUN ON ALL
> query with
>
> WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2
>
> to gather all friend info in parallel
>
>
I was thinking about that. But I don't understand, how can I pass the
list of id's. Should I turn the output of a select into an array? How
then? What if the array gets hundreds of items long?

On 1/22/09, Igor Katson <descentspb@gmail.com> wrote:
> Hannu Krosing wrote:
>  > On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:
>  >
>  >
>  >> So to say, give me the list of friends (not only their ID's, but all the
>  >> needed columns!) of given individual, which are in a given group. That
>  >> seems ok without plproxy, but with using it, I can't imagine how can I
>  >> form a nice query, or a function (or a set of plpgsql + plproxy
>  >> functions) to do the job.
>  >>
>  >
>  > You need to do it in two steps - first run a query on the partition the
>  > user is in to get list of friends ids, then run a second RUN ON ALL
>  > query with
>  >
>  > WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2
>  >
>  > to gather all friend info in parallel
>  >
>  >
>
> I was thinking about that. But I don't understand, how can I pass the
>  list of id's. Should I turn the output of a select into an array? How
>  then? What if the array gets hundreds of items long?

Yes, array works fine.  And if it's long, then let it be long...

--
marko

Marko Kreen wrote:
> On 1/22/09, Igor Katson <descentspb@gmail.com> wrote:
>
>> Hannu Krosing wrote:
>>  > On Thu, 2009-01-22 at 02:33 +0300, Igor Katson wrote:
>>  >
>>  >
>>  >> So to say, give me the list of friends (not only their ID's, but all the
>>  >> needed columns!) of given individual, which are in a given group. That
>>  >> seems ok without plproxy, but with using it, I can't imagine how can I
>>  >> form a nice query, or a function (or a set of plpgsql + plproxy
>>  >> functions) to do the job.
>>  >>
>>  >
>>  > You need to do it in two steps - first run a query on the partition the
>>  > user is in to get list of friends ids, then run a second RUN ON ALL
>>  > query with
>>  >
>>  > WHERE f.friend.id in (list of ids from f1) AND f.group_id = $2
>>  >
>>  > to gather all friend info in parallel
>>  >
>>  >
>>
>> I was thinking about that. But I don't understand, how can I pass the
>>  list of id's. Should I turn the output of a select into an array? How
>>  then? What if the array gets hundreds of items long?
>>
>
> Yes, array works fine.  And if it's long, then let it be long...
>
>
Ok, thank you, guys. What is the best way to make an array out of a
column? I didn't make up anything better then writing a function:

CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
AS $$
    DECLARE
        arr int[];
        rec int;
    BEGIN
        FOR rec IN EXECUTE query
        LOOP
            arr := array_append('{}',rec);
        END LOOP;
        RETURN arr;
    END;
$$ language plpgsql;

On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:

> Ok, thank you, guys. What is the best way to make an array out of a
> column? I didn't make up anything better then writing a function:
>
> CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
> AS $$
>     DECLARE
>         arr int[];
>         rec int;
>     BEGIN
>         FOR rec IN EXECUTE query
>         LOOP
>             arr := array_append('{}',rec);
>         END LOOP;
>         RETURN arr;
>     END;
> $$ language plpgsql;

hannu=# select ARRAY(select usename from pg_user);
           ?column?
-------------------------------
 {postgres,hannu,m1,skyncuser}
(1 row)



--
------------------------------------------
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
   Services, Consulting and Training

Hannu Krosing wrote:
> On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:
>
>
>> Ok, thank you, guys. What is the best way to make an array out of a
>> column? I didn't make up anything better then writing a function:
>>
>> CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
>> AS $$
>>     DECLARE
>>         arr int[];
>>         rec int;
>>     BEGIN
>>         FOR rec IN EXECUTE query
>>         LOOP
>>             arr := array_append('{}',rec);
>>         END LOOP;
>>         RETURN arr;
>>     END;
>> $$ language plpgsql;
>>
>
> hannu=# select ARRAY(select usename from pg_user);
>            ?column?
> -------------------------------
>  {postgres,hannu,m1,skyncuser}
> (1 row)
>
>
>
>
Lots of thanks! I tried the same one, but with ARRAY[], so i didn't get
anything.

On Thu, 2009-01-22 at 16:13 +0200, Hannu Krosing wrote:
> On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:
>
> > Ok, thank you, guys. What is the best way to make an array out of a
> > column? I didn't make up anything better then writing a function:
> >
> > CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
> > AS $$
> >     DECLARE
> >         arr int[];
> >         rec int;
> >     BEGIN
> >         FOR rec IN EXECUTE query
> >         LOOP
> >             arr := array_append('{}',rec);
> >         END LOOP;
> >         RETURN arr;
> >     END;
> > $$ language plpgsql;
>
> hannu=# select ARRAY(select usename from pg_user);
>            ?column?
> -------------------------------
>  {postgres,hannu,m1,skyncuser}
> (1 row)

So what yo need is

select * from
gather_friend_info_from_all_partitions(get_friend_id_list_form_user_partition(user_id));

--
------------------------------------------
Hannu Krosing   http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
   Services, Consulting and Training

Hannu Krosing wrote:
> On Thu, 2009-01-22 at 16:13 +0200, Hannu Krosing wrote:
>
>> On Thu, 2009-01-22 at 15:10 +0300, Igor Katson wrote:
>>
>>
>>> Ok, thank you, guys. What is the best way to make an array out of a
>>> column? I didn't make up anything better then writing a function:
>>>
>>> CREATE OR REPLACE FUNCTION int_column_to_array(query text) RETURNS int[]
>>> AS $$
>>>     DECLARE
>>>         arr int[];
>>>         rec int;
>>>     BEGIN
>>>         FOR rec IN EXECUTE query
>>>         LOOP
>>>             arr := array_append('{}',rec);
>>>         END LOOP;
>>>         RETURN arr;
>>>     END;
>>> $$ language plpgsql;
>>>
>> hannu=# select ARRAY(select usename from pg_user);
>>            ?column?
>> -------------------------------
>>  {postgres,hannu,m1,skyncuser}
>> (1 row)
>>
>
> So what yo need is
>
> select * from
> gather_friend_info_from_all_partitions(get_friend_id_list_form_user_partition(user_id));
>
>
Yes, after using arrays, I figured out perfectly, how to do that. And
thanks for the help!