From: | Kevin Hunter Kesling <kmhunte2(at)ncsu(dot)edu> |
---|---|
To: | Postgres Novice List <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: how to handle Pagination > |
Date: | 2013-10-15 17:02:06 |
Message-ID: | 525D750E.2000504@ncsu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
At 10:52am -0400 Tue, 15 Oct 2013, Madhavan wrote:
>> I want to fetch records from the database and paginate in php
>> script. How this can be handled?
At 11:01am -0400 Tue, 15 Oct 2013, Jesusthefrog wrote:
> You will probably want to use OFFSET and LIMIT, which are the window
> size and page. Something like
>
> SELECT .. FROM ... WHERE ... ORDER BY (...) OFFSET y LIMIT x
>
> So say you want to get 100 records at a time. You would get page 1
> with OFFSET 0 LIMIT 100, then page 2 with OFFSET 100, OFFSET 200, and
> so on.
>
> That's just off the top of my head. There may be another solution
> which will work better in your case, but lacking details, this is
> what I would recommend.
The common wisdom (of which I'm aware, anyway) is that the limits of
this approach are in the atomicity of the 2+ pages at which a user
looks. For instance, if the user looks at page 1 (say, records 1-100),
then the SQL would look something like:
SELECT ... ORDER BY ... OFFSET 0 LIMIT 100;
Now, while the user (user A) spends time reading the results, another
user or process updates the table, say by removing records 40-49 (10
total records removed). Now, user A loads the next page of results,
which begins at offset 100:
SELECT ... ORDER BY ... OFFSET 100 LIMIT 100;
The problem is that this is a new query, so offset 100 refers to what
/was/ record 110, instead of what the user probably wanted, which is now
OFFSET 90.
See this for a better explanation:
https://coderwall.com/p/lkcaag
Then note that the Postgres community has "solved" this problem:
https://wiki.postgresql.org/wiki/File:Pagination_Done_the_PostgreSQL_Way.pdf
Cheers,
Kevin
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Hunter Kesling | 2013-10-15 17:16:23 | Re: how to handle Pagination > |
Previous Message | Vik Fearing | 2013-10-15 16:54:43 | Re: how to handle Pagination > |