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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi

2015-02-09 10:37 GMT+01:00 Marc Balmer <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.

Regards

Pavel

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?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-02-09 10:59 GMT+01:00 Marc Balmer <marc@msys.ch>:

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?

returning a absolute position in FETCH, MOVE statements has minimal
overhead probably, so you can get a current position as side effect of last
statement

and we support MOVE RELATIVE 0;

Regards

Pavel

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) ??

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,

2015-02-09 10:37 GMT+01:00 Marc Balmer <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

kind regards
hakm kocaman

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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);
}

/*

2015-02-09 10:59 GMT+01:00 Marc Balmer <marc@msys.ch>:

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-02-10 14:32 GMT+01:00 Marc Balmer <marc@msys.ch>:

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.

I have not strong opinion about it

Pavel

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-02-10 16:21 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

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.

It can solved via GET DIAGNOSTICS statement

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

yes, this is serious risk - and this is too high cost for relative less
used feature.

Regards

Pavel