[Dbix-class] paging question

[Peter Rabbitson]

James Marca wrote:
> Hi.  
> 
> First DBIx::Class is excellent and kudos all around.
> 
> I have recently needed to use the paging feature for the first time
> and I'm not sure of the best way to do this.
> 
> First a simple question:  How should one terminate the paging?  Is
> there some variable to check to find out how many pages of data are
> expected from a result set?  I couldn't find anything in the docs,
> but I probably missed it.
> 
> What I am doing instead is just paging until my result set comes back
> empty.  But that is a bad solution in practice and gets to my second
> issue with paging.
> 
> My query is rather large and expensive.  I'm using paging not to drive
> a UI, but because when running the request without paging psql
> complained that it ran out of disk space for the tmp file it needed
> for the cursor (I forget the exact wording of the error).  My page
> size is currently set at 4 million rows, which seems fine.
> 
> My guess is that under the hood paging is preparing the query, then
> executing that prepared statement repeatedly with different offsets.
> However, in practice it *appears* (I can't prove this yet so I could
> be wrong) that the page(2), page(3), etc queries take just as long to
> execute as the first page(1) query, as if they aren't running off of
> the same prepared statement.

The only thing that can be prepare_cached are parametrized queries
(where the query stays the same and only the bindvalues for ? change).
Limits are currently not expressed via ?'s and it is rather hard to
do so in SQLA. Let us know if PG supports bind values for the
limit/offset specification, so we know if an effort is warranted at
all.