Re: [PATCHES] patch to create system view that lists cursors
On Sun, 2006-01-15 at 17:57 -0500, Neil Conway wrote: I think the view should include the portals created by DECLARE CURSOR and Bind protocol messages, but should not include the unnamed portal or any other portals that are created internally as part of the implementation of other commands (e.g. EXECUTE). I'm not sure how to handle SPI: developers using SPI would expect to find their portals in the view, but those using SPI indirectly (e.g. via PL/foo) would probably find the clutter surprising. I'd say we need to include SPI portals in the view as well. Attached is a revised version of Joachim's patch that implements this. Cursors created via SPI are part of the view, which produces somewhat unexpected results when querying the view from a procedural language as noted above, but I suppose it's the best compromise. The documentation still needs some work. Barring any objections, I'll fix that and a few other minor issues and then apply the patch tomorrow. -Neil *** doc/src/sgml/catalogs.sgml 4eec167450469237ea89094c7bc90511b4d0ebdf --- doc/src/sgml/catalogs.sgml 5d0f61713e050a48b4f6217d4e20db444ffb20b1 *** *** 4360,4365 --- 4360,4370 tbody row + entrylink linkend=view-pg-cursorsstructnamepg_cursors/structname/link/entry + entryopen cursors/entry + /row + + row entrylink linkend=view-pg-groupstructnamepg_group/structname/link/entry entrygroups of database users/entry /row *** *** 4429,4434 --- 4434,4533 /table /sect1 + sect1 id=view-pg-cursors + titlestructnamepg_cursors/structname/title + + indexterm zone=view-pg-cursors +primarypg_cursors/primary + /indexterm + + para +The view structnamepg_cursors/structname lists all declared cursors for +the current session and transaction. Note that cursors that have been +declared literalWITHOUT HOLD/literal are only valid within the current +transaction whereas literalWITH HOLD/literal cursors can exist during +the whole session. Cursors can be created using xref linkend=sql-declare +endterm=sql-declare-title and removed with xref linkend=sql-close +endterm=sql-close-title. + /para + + table +titlestructnamepg_cursors/ Columns/title + +tgroup cols=4 + thead + row + entryName/entry + entryType/entry + entryReferences/entry + entryDescription/entry + /row + /thead + + tbody + row + entrystructfieldname/structfield/entry + entrytypetext/type/entry + entry/entry + entryThe name of the cursor/entry + /row + + row + entrystructfieldstatement/structfield/entry + entrytypetext/type/entry + entry/entry + entryThe verbatim query string submitted to declare this cursor/entry + /row + + row + entrystructfieldis_holdable/structfield/entry + entrytypeboolean/type/entry + entry/entry + entry +literaltrue/literal if the cursor is holdable (that is, it +can be accessed after the transaction that declared the cursor +has committed); literalfalse/literal otherwise +/entry + /row + + row + entrystructfieldis_binary/structfield/entry + entrytypeboolean/type/entry + entry/entry + entry +literaltrue/literal if the cursor was declared +literalBINARY/literal; literalfalse/literal +otherwise !-- XXX: discuss fe/be protocol -- +/entry + /row + + row + entrystructfieldis_scrollable/structfield/entry + entrytypeboolean/type/entry + entry/entry + entry +literaltrue/ if the cursor is scrollable (that is, it +allows rows to be retrieved in a nonsequential manner); +literalfalse/literal otherwise +/entry + /row + + row + entrystructfieldcreation_time/structfield/entry + entrytypetimestamptz/type/entry + entry/entry + entryThe time at which the cursor was declared/entry + /row + /tbody +/tgroup + /table + + para +The structnamepg_cursors/structname view is read only. + /para + + /sect1 + sect1 id=view-pg-group titlestructnamepg_group/structname/title *** doc/src/sgml/ref/close.sgml 8892a94e8d7e93d729efc7f45f5f65fa47e26854 --- doc/src/sgml/ref/close.sgml e430bd034f8fd5b8a97f81063f2f9f5a7edf3891 *** *** 76,81 --- 76,86 xref linkend=sql-declare endterm=sql-declare-title statement to declare a cursor. /para + + para +You can see all available cursors by querying the +structnamepg_cursors/structname system view. + /para /refsect1 refsect1 *** doc/src/sgml/ref/declare.sgml
Re: [PATCHES] patch to create system view that lists cursors
On Thu, 2006-01-12 at 19:51 -0500, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: It would also mean that this would produce unexpected results: PREPARE foo AS SELECT * FROM pg_cursors; EXECUTE foo. Unexpected in what sense? Unexpected in the sense that the user would have no reason to expect an unnamed portal n row in the pg_cursors view, merely because we happen to create a portal internally to implement the EXECUTE command. I think the view should include the portals created by DECLARE CURSOR and Bind protocol messages, but should not include the unnamed portal or any other portals that are created internally as part of the implementation of other commands (e.g. EXECUTE). I'm not sure how to handle SPI: developers using SPI would expect to find their portals in the view, but those using SPI indirectly (e.g. via PL/foo) would probably find the clutter surprising. I'd say we need to include SPI portals in the view as well. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] patch to create system view that lists cursors
Attached is a patch to create a new system view pg_cursors to list all available cursors to the current session and transaction. The patch itself is quite analogous to the patch for pg_prepared_statements I submitted in December. The attributes is_holdable, is_binary and is_scrollable are exposed in the view. There's a TODO item that only talks about WITH HOLD cursors, however the proposed system view lists WITHOUT HOLD cursors as well. o %Allow pooled connections to list all open WITH HOLD cursors Because WITH HOLD cursors exist outside transactions, this allows them to be listed so they can be closed. I noticed that there is no regression test for binary cursors. Should there be one? I now create one to check the view but don't actually query it. Joachim diff -cr cvs/pgsql/doc/src/sgml/catalogs.sgml cvs.build/pgsql/doc/src/sgml/catalogs.sgml *** cvs/pgsql/doc/src/sgml/catalogs.sgml2006-01-08 08:00:24.0 +0100 --- cvs.build/pgsql/doc/src/sgml/catalogs.sgml 2006-01-12 09:44:04.0 +0100 *** *** 4358,4363 --- 4358,4368 tbody row + entrylink linkend=view-pg-cursorsstructnamepg_cursors/structname/link/entry + entryopen cursors/entry + /row + + row entrylink linkend=view-pg-groupstructnamepg_group/structname/link/entry entrygroups of database users/entry /row *** *** 4427,4432 --- 4432,4519 /table /sect1 + sect1 id=view-pg-cursors + titlestructnamepg_cursors/structname/title + + indexterm zone=view-pg-cursors +primarypg_cursors/primary + /indexterm + + para +The view structnamepg_cursors/structname lists all declared cursors for +the current session and transaction. Note that cursors that have been +declared literalWITHOUT HOLD/literal are only valid within the current +transaction whereas literalWITH HOLD/literal cursors can exist during +the whole session. Cursors can be created using xref linkend=sql-declare +endterm=sql-declare-title and removed with xref linkend=sql-close +endterm=sql-close-title. + /para + + table +titlestructnamepg_cursors/ Columns/title + +tgroup cols=4 + thead + row + entryName/entry + entryType/entry + entryReferences/entry + entryDescription/entry + /row + /thead + + tbody + row + entrystructfieldname/structfield/entry + entrytypetext/type/entry + entry/entry + entryThe name of the cursor./entry + /row + + row + entrystructfieldstatement/structfield/entry + entrytypetext/type/entry + entry/entry + entryThe verbatim query string submitted to declare this cursor./entry + /row + + row + entrystructfieldis_holdable/structfield/entry + entrytypeboolean/type/entry + entry/entry + entryliteraltrue/literal if the cursor was declared literalWITH HOLD/literal; literalfalse/literal if the cursor was declared literalWITHOUT HOLD/literal./entry + /row + + row + entrystructfieldis_binary/structfield/entry + entrytypeboolean/type/entry + entry/entry + entryliteraltrue/literal if the cursor was declared literalBINARY/literal; literalfalse/literal if not./entry + /row + + row + entrystructfieldis_scrollable/structfield/entry + entrytypeboolean/type/entry + entry/entry + entryliteraltrue/literal if the cursor was declared literalSCROLL/literal; literalfalse/literal if the cursor was declared literalNO SCROLL/literal. If neither the literalSCROLL/literal nor the literalNO SCROLL/literal keyword has been given, productnamePostgreSQL/productname will choose one of both, its decision can be seen in this view./entry + /row + + row + entrystructfieldcreation_time/structfield/entry + entrytypetimestamptz/type/entry + entry/entry + entryThe time at which the cursor was declared./entry + /row + /tbody +/tgroup + /table + + para +The structnamepg_cursors/structname view is read only. + /para + + /sect1 + sect1 id=view-pg-group titlestructnamepg_group/structname/title diff -cr cvs/pgsql/doc/src/sgml/ref/close.sgml cvs.build/pgsql/doc/src/sgml/ref/close.sgml *** cvs/pgsql/doc/src/sgml/ref/close.sgml 2005-01-04 01:39:53.0 +0100 --- cvs.build/pgsql/doc/src/sgml/ref/close.sgml 2006-01-12 08:20:49.0 +0100 *** *** 76,81 --- 76,86 xref linkend=sql-declare endterm=sql-declare-title statement to declare a cursor. /para + + para +You can see all available cursors by querying the +structnamepg_cursors/structname system view. + /para /refsect1 refsect1 diff -cr cvs/pgsql/doc/src/sgml/ref/declare.sgml cvs.build/pgsql/doc/src/sgml/ref/declare.sgml *** cvs/pgsql/doc/src/sgml/ref/declare.sgml 2005-01-04 01:39:53.0 +0100
Re: [PATCHES] patch to create system view that lists cursors
On Thu, 2006-01-12 at 10:51 +0100, Joachim Wieland wrote:
Attached is a patch to create a new system view pg_cursors to list all
available cursors to the current session and transaction.
+ /* loop until we find a named portal or hit the end of the list */
+ while ((hentry = hash_seq_search(hash_seq)) != NULL)
+ {
+ portal = hentry-portal;
+ /* there can be a named portal created by CreateNewPortal, its
name
+ * will be unnamed portal n (see CreateNewPortal function in
this
+ * file). Those have a status of PORTAL_NEW. The status of cursors
is
+ * PORTAL_READY however. */
+ if (portal-status != PORTAL_READY)
+ continue;
+ if (portal-name[0] != '\0')
+ break;
+ }
I think it is worth distinguishing more clearly between portals that
should be displayed to the user and those that should not (which might
be labelled internal cursors, perhaps). The tests above seem fairly
ad-hoc.
Barring any objections, I'll implement the above and apply the revised
patch tomorrow.
-Neil
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [PATCHES] patch to create system view that lists cursors
Neil Conway [EMAIL PROTECTED] writes: The tests above seem fairly ad-hoc. No kidding. But what do you think the correct test is? The comment's claim that stuff named unnamed cursor should be suppressed seems wrong to begin with, as those are perfectly good cursors. I'm also unconvinced that a test on portal-status is a good idea, as I doubt that ACTIVE should be excluded, and I'm not sure that DONE or FAILED should be either, and NEW is probably a non-issue because you'll never see it from within a running command. What is the point of having this code discriminate against any portals whatever? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] patch to create system view that lists cursors
On Thu, 2006-01-12 at 19:27 -0500, Tom Lane wrote: No kidding. But what do you think the correct test is? The comment's claim that stuff named unnamed cursor should be suppressed seems wrong to begin with, as those are perfectly good cursors. Well, I suggested to Joachim offlist that these shouldn't be listed. I think the intent of the feature is to list the available *cursors*, not all the available Portals. Why list Portals that cannot be directly manipulated by the user? It would also mean that this would produce unexpected results: PREPARE foo AS SELECT * FROM pg_cursors; EXECUTE foo. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] patch to create system view that lists cursors
Neil Conway [EMAIL PROTECTED] writes: Why list Portals that cannot be directly manipulated by the user? Define directly manipulated. AFAIR there isn't any particular distinction between portals that got made by DECLARE CURSOR and those that got made by Bind; you can use either EXECUTE or Fetch for either. Also, a portal that has gone into ERROR state should still be listed, IMHO, because the behavior the user will expect is that it's there until he CLOSEs it. It would also mean that this would produce unexpected results: PREPARE foo AS SELECT * FROM pg_cursors; EXECUTE foo. Unexpected in what sense? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] patch to create system view that lists cursors
I think it is worth distinguishing more clearly between portals that should be displayed to the user and those that should not (which might be labelled internal cursors, perhaps). The tests above seem fairly ad-hoc. With all this system view love going on, is there any point having a 'pg_savepoints' view to see what savepoints you've made? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] patch to create system view that lists cursors
Christopher Kings-Lynne [EMAIL PROTECTED] writes: With all this system view love going on, is there any point having a 'pg_savepoints' view to see what savepoints you've made? Probably not, seeing that one of the main situations where you'd want to know that would be after an error, and SELECT isn't going to work in that context. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
