Thread: For cursors, there is FETCH and MOVE, why no TELL?

Currently there are FETCH and the (non standard) MOVE commands to work
on cursors.

(I use cursors to display large datasets in a page-wise way, where the
user can move per-page, or, when displaying a single record, per record.When the user goes back from per-record view to
page-view,I have to
 
restore the cursor to the position it was on before the user changed to
per-record view.)

I have to "manually" keep track of the cursor position, but in some
cases it would definitely be easier to just query the current cursor
position directly from the database and later use "MOVE ABSOLUTE" to
rewind it to that position.  That could be achieved e.g. by a
hypothetical "TELL <cursor-name>" command.  It does, however, not exist
and I have not found an alternative.  Is there a way to query the
current cusros position at all?  If not, does a TELL command sound like
a good or bad idea?

On 02/09/2015 11:37 AM, Marc Balmer wrote:
> Currently there are FETCH and the (non standard) MOVE commands to work
> on cursors.
>
> (I use cursors to display large datasets in a page-wise way, where the
> user can move per-page, or, when displaying a single record, per record.
>   When the user goes back from per-record view to page-view, I have to
> restore the cursor to the position it was on before the user changed to
> per-record view.)
>
> I have to "manually" keep track of the cursor position, but in some
> cases it would definitely be easier to just query the current cursor
> position directly from the database and later use "MOVE ABSOLUTE" to
> rewind it to that position.  That could be achieved e.g. by a
> hypothetical "TELL <cursor-name>" command.  It does, however, not exist
> and I have not found an alternative.  Is there a way to query the
> current cusros position at all?  If not, does a TELL command sound like
> a good or bad idea?

It's the first time I hear anyone wanting that, but I guess it might 
come handy sometimes. I think you'd usually still rather keep track of 
that in the client, though, because it's easy to do, and it avoids the 
extra round-trip to execute the TELL command.

Not sure we'd want to add the TELL keyword for this. Perhaps a 
pg_cursor_pos(<cursor name>) function would be better.

You could fairly easily write an extension to do that, btw. A C function 
could call GetPortalByName() and peek into the PortalData.portalPos field.

- Heikki

> 
> 2015-02-09 10:37 GMT+01:00 Marc Balmer <marc@msys.ch <mailto:marc@msys.ch>>:
> 
>     Currently there are FETCH and the (non standard) MOVE commands to work
>     on cursors.
> 
>     (I use cursors to display large datasets in a page-wise way, where the
>     user can move per-page, or, when displaying a single record, per record.
>      When the user goes back from per-record view to page-view, I have to
>     restore the cursor to the position it was on before the user changed to
>     per-record view.)
> 
>     I have to "manually" keep track of the cursor position, but in some
>     cases it would definitely be easier to just query the current cursor
>     position directly from the database and later use "MOVE ABSOLUTE" to
>     rewind it to that position.  That could be achieved e.g. by a
>     hypothetical "TELL <cursor-name>" command.  It does, however, not exist
>     and I have not found an alternative.  Is there a way to query the
>     current cusros position at all?  If not, does a TELL command sound like
>     a good or bad idea?
> 
> 
> It sounds like good idea.
> 
> Do we need a new statement? We can implement returning the position to
> MOVE statement. It returns a delta, but it can returns a absolute
> position too.

On second thought, a new statement is not needed at all.  As Heikki
noticed in hsi reply, it could either be a new function or have move to
return the current position somehow(tm).  Or a nw option to move, maybe
"MOVE NOT" (don't move the cursor but return it's position?

Am 09.02.15 um 10:46 schrieb Heikki Linnakangas:
> [...] 
> You could fairly easily write an extension to do that, btw. A C function
> could call GetPortalByName() and peek into the PortalData.portalPos field.
> 

Would

PGresult *PQdescribePortal(PGconn *conn, const char *portalName);

from libpq also provide this information?  Should there be a way to run
PQdescribePortal() from psql (e.g. \dP) ??

Am 09.02.15 um 11:47 schrieb Marc Balmer:
> 
> 
> Am 09.02.15 um 10:46 schrieb Heikki Linnakangas:
>> [...] 
>> You could fairly easily write an extension to do that, btw. A C function
>> could call GetPortalByName() and peek into the PortalData.portalPos field.
>>
> 
> Would
> 
> PGresult *PQdescribePortal(PGconn *conn, const char *portalName);
> 
> from libpq also provide this information?  Should there be a way to run
> PQdescribePortal() from psql (e.g. \dP) ??

... and a quickly hacked test program shows that it does *not* return
the cursor position.

Am 09.02.15 um 13:13 schrieb Hakan Kocaman:
> Hi,
> 
> 2015-02-09 10:37 GMT+01:00 Marc Balmer <marc@msys.ch <mailto:marc@msys.ch>>:
> 
> 
>     (I use cursors to display large datasets in a page-wise way, where the
>     user can move per-page, or, when displaying a single record, per record.
>      When the user goes back from per-record view to page-view, I have to
>     restore the cursor to the position it was on before the user changed to
>     per-record view.)
> 
> 
> On a totaly unrelated note:
> http://use-the-index-luke.com/de/blog/2013-07/pagination-done-the-postgresql-way

yes, totally unrelated, indeed.

Am 10.02.15 um 09:06 schrieb Pavel Stehule:
> Hi
> 
> 
> the patch can be very simple:
> 
> diff --git a/src/backend/commands/portalcmds.c
> b/src/backend/commands/portalcmds.c
> new file mode 100644
> index 2794537..20b9206
> *** a/src/backend/commands/portalcmds.c
> --- b/src/backend/commands/portalcmds.c
> *************** PerformPortalFetch(FetchStmt *stmt,
> *** 181,189 ****
>  
>         /* Return command status if wanted */
>         if (completionTag)
> !               snprintf(completionTag, COMPLETION_TAG_BUFSIZE, "%s %ld",
>                                  stmt->ismove ? "MOVE" : "FETCH",
> !                                nprocessed);
>   }
>  
>   /*
> --- 181,190 ----
>  
>         /* Return command status if wanted */
>         if (completionTag)
> !               snprintf(completionTag, COMPLETION_TAG_BUFSIZE, "%s %ld
> %ld",
>                                  stmt->ismove ? "MOVE" : "FETCH",
> !                                nprocessed,
> !                                portal->portalPos);
>   }
>  
>   /*
> 

That is simple indeed.  I tend to think, however, that it would be
cleaner to return the position as a proper result from a functionn
instead of using a "side effect" from a FETCH/MOVE command.

> 
> 2015-02-09 10:59 GMT+01:00 Marc Balmer <marc@msys.ch <mailto:marc@msys.ch>>:
> 
>     >
>     > 2015-02-09 10:37 GMT+01:00 Marc Balmer <marc@msys.ch
>     <mailto:marc@msys.ch> <mailto:marc@msys.ch <mailto:marc@msys.ch>>>:
>     >
>     >     Currently there are FETCH and the (non standard) MOVE commands to work
>     >     on cursors.
>     >
>     >     (I use cursors to display large datasets in a page-wise way, where the
>     >     user can move per-page, or, when displaying a single record, per record.
>     >      When the user goes back from per-record view to page-view, I have to
>     >     restore the cursor to the position it was on before the user changed to
>     >     per-record view.)
>     >
>     >     I have to "manually" keep track of the cursor position, but in some
>     >     cases it would definitely be easier to just query the current cursor
>     >     position directly from the database and later use "MOVE ABSOLUTE" to
>     >     rewind it to that position.  That could be achieved e.g. by a
>     >     hypothetical "TELL <cursor-name>" command.  It does, however, not exist
>     >     and I have not found an alternative.  Is there a way to query the
>     >     current cusros position at all?  If not, does a TELL command sound like
>     >     a good or bad idea?
>     >
>     >
>     > It sounds like good idea.
>     >
>     > Do we need a new statement? We can implement returning the position to
>     > MOVE statement. It returns a delta, but it can returns a absolute
>     > position too.
> 
>     On second thought, a new statement is not needed at all.  As Heikki
>     noticed in hsi reply, it could either be a new function or have move to
>     return the current position somehow(tm).  Or a nw option to move, maybe
>     "MOVE NOT" (don't move the cursor but return it's position?
> 
> 
>     --
>     Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org
>     <mailto:pgsql-hackers@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-hackers
> 
> 

Marc Balmer <marc@msys.ch> writes:
> That is simple indeed.  I tend to think, however, that it would be
> cleaner to return the position as a proper result from a functionn
> instead of using a "side effect" from a FETCH/MOVE command.

Yeah.  For one thing, a command tag wouldn't help you at all if you
wanted to know the current cursor position inside a plpgsql function.

There are also backwards-compatibility reasons to be nervous about
changing the long-standing command tag values for these commands.

An issue that would have to be addressed is what the function ought
to do if posOverflow is set, which is entirely feasible on Windows
(or anyplace else where "long" is only 32 bits).  Maybe we should
redeclare portalPos as int64 and get rid of the posOverflow logic.
        regards, tom lane