Search Postgresql Archives

Re: plpgsql function with offset - Postgres 9.1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 2017-06-16 10:19:45 +1200, Patrick B wrote:
> 2017-05-29 19:27 GMT+12:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>:
>     Patrick B wrote:
>     > I am running a background task on my DB, which will copy data from tableA
>     to tableB. For
>     > that, I'm writing a PL/PGSQL function which basically needs to do the
>     following:
>     >
>     >
>     > 1.    Select the data from tableA
>     > 2.    The limit will be put when calling the function
>     > 3.    insert the selected data on Step 1 onto new table
[...]
>     >               FOR row IN EXECUTE '
>     >                           SELECT
>     >                                   id,
>     >                                   path,
>     >                                   name,
>     >                                   name_last,
>     >                                   created_at
>     >                           FROM
>     >                                   tablea
>     >                           WHERE
>     >                                   ready = true
>     >                           ORDER BY 1 LIMIT ' || rows || ' OFFSET ' ||
>     rows || ''
> 
>     '... LIMIT ' || p_limit || ' OFFSET ' || p_offset
> 
>     >               LOOP
> 
>     num_rows := num_rows + 1;
>    
>     >               INSERT INTO tableB (id,path,name,name_last,created_at)
>     >               VALUES (row.id,row.path,row.name,row.
>     name_last,row.created_at);
>     >
>     >               END LOOP;
[...]
> 
>     There are two problems with this approach:
> 
>     1. It will do the wrong thing if rows are added or deleted in "tablea"
>     while
>        you process it.
> 
> 
> 
> There will be actually records being inserted in tablea while processing the
> migration.... Any ideas here?

Is id monotonically increasing? You might be able to use that, as Albe
suggests:

>     The solution is to avoid OFFSET and to use "keyset pagination":
>     http://use-the-index-luke.com/no-offset

But it works only if rows cannot become ready after their id range has
already been processed. Otherwise you will miss them.

> I can add another column in tablea, like example: row_migrated boolean --> if
> that helps

Yes that's probably the best way. Instead of using an additional column
you could also make ready tristate: New -> ready_for_migration -> migrated.

>     2. Queries with hight OFFSET values have bad performance.
> 
> 
> No problem. The plan is to perform 2k rows at once, which is not much.

Are rows deleted from tablea after they are migrated? Otherwise you will
have a problem:

    select ... limit 2000 offset 1234000

will have to retrieve 1236000 rows and then discard 1234000 of them.

        hp

-- 
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@xxxxxx         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux