Обсуждение: Convert Cursor to array

Hi,

In a procedure, I put data of Cursor in a two-dimensional array. This allows me to sort rows.

The problem is this method is too slow.

In fact, I translate a pl/sql procedure to a plpgsql.
Under Oracle, we use bulk and I search to use equivalent of this under postgresql.

Is that exist ?

Oracle procedure

cursor childCursor is select * from CHILD WHERE......
....
open childCursor;
fetch childCursor bulk collect into children;
close childCursor;

Postgresql procedure :

FOR childRecord IN select * from nico.CHILD WHERE...
                LOOP
                -- on met les éléments dans le tableau
                children[recordcount] := '{'
                                                || childRecord.child_id || ','
                                                || childRecord.evolution || ','
                                                || childRecord.isremoved || ','
                                                || childRecord.child_class || ','
                                                || childRecord.indx || ','
                                                || childRecord.ele_id || ','
                                                || childRecord.doc_id ||
                                        '}';
               
                recordcount := recordcount + 1;
               
        END LOOP;

Bulk are native Oracle array and it is probably faster than array.
Is there native method postgresql to replace bulk ?

Best regards.

GIROIRE Nicolas (COFRAMI) wrote:
> Hi,
>
> In a procedure, I put data of Cursor in a two-dimensional array. This allows
> me to sort rows.
>
> The problem is this method is too slow.
>
> In fact, I translate a pl/sql procedure to a plpgsql.
> Under Oracle, we use bulk and I search to use equivalent of this under
> postgresql.
>
> Is that exist ?

No, but there might be other ways to do this. Can you explain why you
need to sort within the function rather than using "order by"?

If you're sure you want to sort within the function, take a look at one
of the alternative procedural languages. PL/pgsql evaluates all
expressions by passing them to the SQL parser, which keeps things simple
  and consistent but isn't efficient if you want to do a lot of data
processing.
--
   Richard Huxton
   Archonet Ltd

my sort algorithm is a very particular solution.
In fact, I put record in array to allow me to sort record. I sort them by using one column of my table but the column indx can have duplicate value and it's volontary.

here is the order algorithm :
for i in 1..recordcount loop                   
                        tmp_row := children[i];
                        indx := tmp_row[5];

                        if (indx<i) then
                                -- on déplace les éléments pour laisser la place à l'élément qu'on déplace
                                for j in 0..(i-indx-1) loop
                                        children[i-j] := children[i-j-1];
                                end loop;
                                -- on met l'élément à sa nouvelle position
                                children[indx] := tmp_row;
                        end if;
                end loop;

It's particular to my system. I won't go to explain my choice but if someone has an idea to save time.

-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : vendredi 11 mars 2005 15:01
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array

GIROIRE Nicolas (COFRAMI) wrote:
> Hi,
>
> In a procedure, I put data of Cursor in a two-dimensional array. This allows
> me to sort rows.
>
> The problem is this method is too slow.
>
> In fact, I translate a pl/sql procedure to a plpgsql.
> Under Oracle, we use bulk and I search to use equivalent of this under
> postgresql.
>
> Is that exist ?

No, but there might be other ways to do this. Can you explain why you
need to sort within the function rather than using "order by"?

If you're sure you want to sort within the function, take a look at one
of the alternative procedural languages. PL/pgsql evaluates all
expressions by passing them to the SQL parser, which keeps things simple
  and consistent but isn't efficient if you want to do a lot of data
processing.
--
   Richard Huxton
   Archonet Ltd

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.

I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.

> here is the order algorithm :
> for i in 1..recordcount loop
>             tmp_row := children[i];
>             indx := tmp_row[5];
>
>             if (indx<i) then
>                 -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
>                 for j in 0..(i-indx-1) loop
>                     children[i-j] := children[i-j-1];
>                 end loop;
>                 -- on met l'élément à sa nouvelle position
>                 children[indx] := tmp_row;
>              end if;
>          end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.

If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.

--
   Richard Huxton
   Archonet Ltd

hi

i'm a friend of nicolas and i'm working with him on this project

so this is the problem :
we are working on an application allowing to manage documentation in xml format.
At the the first level, there is the root, and at the next level there are the childs (example : chapters).
the application allows to make evolve docs (by adding chapters for example).
so, if we consider 2 chapters and that we want to add one between them, we need to have an index.
the first idea was to re-number all the index chapter when 1 was added, but it was too slow.
the second idea was to use float index. if we consider chapter 1 with index 1.0 and chapter 2 with index 2.0, and that we want to add new one between them, we set the new index at 1.5 but performances wasn't good.

so we choice to use a different solution which consist on using the index of a chapter and its evolution.
if we have this data :
chapter_id | evolution | index
   1       |     0     |   1
   2       |     0     |   2
   3       |     0     |   3
   4       |     1     |   2

by using our sort function we obtain this :
chapter_id | evolution | index
   1       |     0     |   1
   4       |     1     |   2
   2       |     0     |   2
   3       |     0     |   3

in consequence a new chapter has been added between two others.
this solution works fine under oracle, but under postgresql, performance are bad and we are working on the tunning of the DB.

although this solution works, we don't find it very elegant and we think that we can find a better one.
the principal problem is how to add leaf between two others whitout modifing too much data.
the size of the documents are between 200MB and 800MB;

By hoping to have been clear.

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Richard Huxton
Envoyé : lundi 14 mars 2005 09:31
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array

GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.

I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.

> here is the order algorithm :
> for i in 1..recordcount loop                 
>                       tmp_row := children[i];
>                       indx := tmp_row[5];
>
>                       if (indx<i) then
>                               -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
>                               for j in 0..(i-indx-1) loop
>                                       children[i-j] := children[i-j-1];
>                               end loop;
>                               -- on met l'élément à sa nouvelle position
>                               children[indx] := tmp_row;
>                       end if;
>               end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.

If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

On Mon, 2005-03-14 at 10:44 +0100, FERREIRA William (COFRAMI) wrote:

> so we choice to use a different solution which consist on using the
> index of a chapter and its evolution.
> if we have this data :
> chapter_id | evolution | index
>    1       |     0     |   1
>    2       |     0     |   2
>    3       |     0     |   3
>    4       |     1     |   2
>
> by using our sort function we obtain this :
> chapter_id | evolution | index
>    1       |     0     |   1
>    4       |     1     |   2
>    2       |     0     |   2
>    3       |     0     |   3
>

in what way is this different than
  ... ORDER BY index ASC, evolution DESC;
?

gnari

well my example was incomplete :
at the begin :
 chapter_id | evolution | index
    1       |     0     |   1
    2       |     0     |   2
    3       |     0     |   3
    4       |     1     |   2
    5       |     1     |   4
by using the sort function i obtain this :

 chapter_id | evolution | index
    1       |     0     |   1
    4       |     1     |   2
    2       |     0     |   2
    5       |     1     |   4
    3       |     0     |   3

that why i can't use ORDER BY...

-----Message d'origine-----
De : Ragnar Hafstað [mailto:gnari@simnet.is]
Envoyé : lundi 14 mars 2005 12:11
À : FERREIRA William (COFRAMI)
Cc : 'Richard Huxton'; GIROIRE Nicolas (COFRAMI);
'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array

On Mon, 2005-03-14 at 10:44 +0100, FERREIRA William (COFRAMI) wrote:

> so we choice to use a different solution which consist on using the
> index of a chapter and its evolution.
> if we have this data :
> chapter_id | evolution | index
>    1       |     0     |   1
>    2       |     0     |   2
>    3       |     0     |   3
>    4       |     1     |   2
>
> by using our sort function we obtain this :
> chapter_id | evolution | index
>    1       |     0     |   1
>    4       |     1     |   2
>    2       |     0     |   2
>    3       |     0     |   3
>

in what way is this different than
  ... ORDER BY index ASC, evolution DESC;
?

gnari

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

ok, i think that i will try to use pl/perl or pl/python
but i don't know which one is better for my problem

maybe you can help me.
thanks

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Richard Huxton
Envoyé : lundi 14 mars 2005 09:31
À : GIROIRE Nicolas (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] Convert Cursor to array

GIROIRE Nicolas (COFRAMI) wrote:
> my sort algorithm is a very particular solution.
> In fact, I put record in array to allow me to sort record. I sort them by
> using one column of my table but the column indx can have duplicate value
> and it's volontary.

I still can't see why you can't sort by that column with "order by"
There's nothing obvious in your description that rules that out. Are you
aware that PostgreSQL offers functional and partial indexes? They can
make complex selection/ordering quite efficient.

> here is the order algorithm :
> for i in 1..recordcount loop                 
>                       tmp_row := children[i];
>                       indx := tmp_row[5];
>
>                       if (indx<i) then
>                               -- on déplace les éléments pour laisser la
> place à l'élément qu'on déplace
>                               for j in 0..(i-indx-1) loop
>                                       children[i-j] := children[i-j-1];
>                               end loop;
>                               -- on met l'élément à sa nouvelle position
>                               children[indx] := tmp_row;
>                       end if;
>               end loop;
>
> It's particular to my system. I won't go to explain my choice but if someone
> has an idea to save time.

If you are going to sort, this looks a particularly slow algorithm for
large values of "recordcount". Personally, I'd use plperl/plpython or
something with built-in hash sorting capabilities. That will almost
certainly be tens or hundreds of times faster.

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

FERREIRA William (COFRAMI) wrote:
> ok, i think that i will try to use pl/perl or pl/python
> but i don't know which one is better for my problem

Whichever you know better is the best solution.

--
   Richard Huxton
   Archonet Ltd