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>
+ <entry><link linkend="view-pg-cursors"><structname>pg_cursors</structname></link></entry>
+ <entry>open cursors</entry>
+ </row>
+
+ <row>
<entry><link linkend="view-pg-group"><structname>pg_group</structname></link></entry>
<entry>groups of database users</entry>
</row>
***************
*** 4429,4434 ****
--- 4434,4533 ----
</table>
</sect1>
+ <sect1 id="view-pg-cursors">
+ <title><structname>pg_cursors</structname></title>
+
+ <indexterm zone="view-pg-cursors">
+ <primary>pg_cursors</primary>
+ </indexterm>
+
+ <para>
+ The view <structname>pg_cursors</structname> lists all declared cursors for
+ the current session and transaction. Note that cursors that have been
+ declared <literal>WITHOUT HOLD</literal> are only valid within the current
+ transaction whereas <literal>WITH 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>
+ <title><structname>pg_cursors</> Columns</title>
+
+ <tgroup cols=4>
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry><structfield>name</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>The name of the cursor</entry>
+ </row>
+
+ <row>
+ <entry><structfield>statement</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry></entry>
+ <entry>The verbatim query string submitted to declare this cursor</entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_holdable</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> if the cursor is holdable (that is, it
+ can be accessed after the transaction that declared the cursor
+ has committed); <literal>false</literal> otherwise
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_binary</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</literal> if the cursor was declared
+ <literal>BINARY</literal>; <literal>false</literal>
+ otherwise <!-- XXX: discuss fe/be protocol -->
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_scrollable</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry>
+ <literal>true</> if the cursor is scrollable (that is, it
+ allows rows to be retrieved in a nonsequential manner);
+ <literal>false</literal> otherwise
+ </entry>
+ </row>
+
+ <row>
+ <entry><structfield>creation_time</structfield></entry>
+ <entry><type>timestamptz</type></entry>
+ <entry></entry>
+ <entry>The time at which the cursor was declared</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The <structname>pg_cursors</structname> view is read only.
+ </para>
+
+ </sect1>
+
<sect1 id="view-pg-group">
<title><structname>pg_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
+ <structname>pg_cursors</structname> system view.
+ </para>
</refsect1>
<refsect1>
============================================================
*** doc/src/sgml/ref/declare.sgml 6875bafecc3a25bb5d9c8117b430afb6d0cd2474
--- doc/src/sgml/ref/declare.sgml 4566aed3538b627eb804e67913c75a8d4aebf100
***************
*** 253,258 ****
--- 253,263 ----
the standard SQL cursor conventions, including those involving
<command>DECLARE</command> and <command>OPEN</command> statements.
</para>
+
+ <para>
+ You can see all available cursors by querying the
+ <structname>pg_cursors</structname> system view.
+ </para>
</refsect1>
<refsect1>
============================================================
*** src/backend/catalog/system_views.sql 103ab494a8ed13f44f6c8ba5d00028c70a2a7abb
--- src/backend/catalog/system_views.sql 56327ceb9618cb9c211f4c85162721957202f949
***************
*** 148,153 ****
--- 148,160 ----
transactionid xid, classid oid, objid oid, objsubid int2,
transaction xid, pid int4, mode text, granted boolean);
+ CREATE VIEW pg_cursors AS
+ SELECT C.name, C.statement, C.is_holdable, C.is_binary,
+ C.is_scrollable, C.creation_time
+ FROM pg_cursor() AS C
+ (name text, statement text, is_holdable boolean, is_binary boolean,
+ is_scrollable boolean, creation_time timestamptz);
+
CREATE VIEW pg_prepared_xacts AS
SELECT P.transaction, P.gid, P.prepared,
U.rolname AS owner, D.datname AS database
============================================================
*** src/backend/commands/portalcmds.c ba8e42896ec9179f64b4b1841199ca5b087456a9
--- src/backend/commands/portalcmds.c db3146dd3f642a6882d0bfdcb90be5ead6270dd4
***************
*** 28,33 ****
--- 28,34 ----
#include "optimizer/planner.h"
#include "rewrite/rewriteHandler.h"
#include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
#include "utils/memutils.h"
***************
*** 106,112 ****
plan = copyObject(plan);
PortalDefineQuery(portal,
! NULL, /* unfortunately don't have sourceText */
"SELECT", /* cursor's query is always a SELECT */
list_make1(query),
list_make1(plan),
--- 107,113 ----
plan = copyObject(plan);
PortalDefineQuery(portal,
! pstrdup(debug_query_string),
"SELECT", /* cursor's query is always a SELECT */
list_make1(query),
list_make1(plan),
============================================================
*** src/backend/commands/prepare.c de4ce2755737e05dd3c763b759c7f0234ddceb22
--- src/backend/commands/prepare.c f8504f84b1188c8277944f7a083505bba7f1e7c7
***************
*** 162,172 ****
paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
}
! /*
! * Create a new portal to run the query in
! */
portal = CreateNewPortal();
!
/*
* For CREATE TABLE / AS EXECUTE, make a copy of the stored query so that
* we can modify its destination (yech, but this has always been ugly).
--- 162,172 ----
paramLI = EvaluateParams(estate, stmt->params, entry->argtype_list);
}
! /* Create a new portal to run the query in */
portal = CreateNewPortal();
! /* Don't display the portal in pg_cursors, it is for internal use only */
! portal->visible = false;
!
/*
* For CREATE TABLE / AS EXECUTE, make a copy of the stored query so that
* we can modify its destination (yech, but this has always been ugly).
============================================================
*** src/backend/tcop/postgres.c c1b95dafdc4eca86cadc2d1fcc1ca9e56be29a6f
--- src/backend/tcop/postgres.c 46535289caf3ff630d2d41c207a91059aee38bae
***************
*** 956,961 ****
--- 956,963 ----
* already is one, silently drop it.
*/
portal = CreatePortal("", true, true);
+ /* Don't display the portal in pg_cursors */
+ portal->visible = false;
PortalDefineQuery(portal,
query_string,
============================================================
*** src/backend/utils/mmgr/portalmem.c 8591a97deb86749a2a0b98fc469288d474010003
--- src/backend/utils/mmgr/portalmem.c 7a160e7ba26930007ae971565179b08684d1a954
***************
*** 18,26 ****
*/
#include "postgres.h"
! #include "miscadmin.h"
#include "commands/portalcmds.h"
#include "executor/executor.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
#include "utils/portal.h"
--- 18,30 ----
*/
#include "postgres.h"
! #include "access/heapam.h"
! #include "catalog/pg_type.h"
#include "commands/portalcmds.h"
#include "executor/executor.h"
+ #include "funcapi.h"
+ #include "miscadmin.h"
+ #include "utils/builtins.h"
#include "utils/hsearch.h"
#include "utils/memutils.h"
#include "utils/portal.h"
***************
*** 190,201 ****
--- 194,208 ----
"Portal");
/* initialize portal fields that don't start off zero */
+ portal->status = PORTAL_NEW;
portal->cleanup = PortalCleanup;
portal->createSubid = GetCurrentSubTransactionId();
portal->strategy = PORTAL_MULTI_QUERY;
portal->cursorOptions = CURSOR_OPT_NO_SCROLL;
portal->atStart = true;
portal->atEnd = true; /* disallow fetches until query is set */
+ portal->visible = true;
+ portal->creation_time = GetCurrentTimestamp();
/* put portal in table (sets portal->name) */
PortalHashTableInsert(portal, name);
***************
*** 756,758 ****
--- 763,868 ----
PortalDrop(portal, false);
}
}
+
+ /* Find all available cursors */
+ Datum
+ pg_cursor(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ HASH_SEQ_STATUS *hash_seq;
+ PortalHashEnt *hentry;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ TupleDesc tupdesc;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /*
+ * switch to memory context appropriate for multiple function
+ * calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PortalHashTable)
+ {
+ hash_seq = (HASH_SEQ_STATUS *) palloc(sizeof(HASH_SEQ_STATUS));
+ hash_seq_init(hash_seq, PortalHashTable);
+ funcctx->user_fctx = (void *) hash_seq;
+ }
+ else
+ funcctx->user_fctx = NULL;
+
+ /*
+ * build tupdesc for result tuples. This must match the
+ * definition of the pg_cursors view in system_views.sql
+ */
+ tupdesc = CreateTemplateTupleDesc(6, false);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "name",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "statement",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 3, "is_holdable",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 4, "is_binary",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 5, "is_scrollable",
+ BOOLOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 6, "creation_time",
+ TIMESTAMPTZOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* stuff done on every call of the function */
+ funcctx = SRF_PERCALL_SETUP();
+ hash_seq = (HASH_SEQ_STATUS *) funcctx->user_fctx;
+
+ /* if the hash table is uninitialized, we're done */
+ if (hash_seq == NULL)
+ SRF_RETURN_DONE(funcctx);
+
+ /* loop until we find a visible portal or hit the end of the list */
+ while ((hentry = hash_seq_search(hash_seq)) != NULL)
+ {
+ if (hentry->portal->visible)
+ break;
+ }
+
+ if (hentry)
+ {
+ Portal portal;
+ Datum result;
+ HeapTuple tuple;
+ Datum values[6];
+ bool nulls[6];
+
+ portal = hentry->portal;
+ MemSet(nulls, 0, sizeof(nulls));
+
+ values[0] = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
+ /* XXX can this happen? */
+ if (!portal->sourceText)
+ nulls[1] = true;
+ else
+ values[1] = DirectFunctionCall1(textin,
+ CStringGetDatum(portal->sourceText));
+ values[2] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_HOLD);
+ values[3] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_BINARY);
+ /* that should be sufficient, since if the cursor is not scrollable,
+ * the CURSOR_OPT_SCROLL bit won't be set */
+ values[4] = BoolGetDatum(portal->cursorOptions & CURSOR_OPT_SCROLL);
+ values[5] = TimestampTzGetDatum(portal->creation_time);
+
+ tuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ result = HeapTupleGetDatum(tuple);
+ SRF_RETURN_NEXT(funcctx, result);
+ }
+
+ SRF_RETURN_DONE(funcctx);
+ }
+
============================================================
*** src/include/catalog/pg_proc.h 3986a9b84ebba188349a63e6f0b7d62ea70c74e1
--- src/include/catalog/pg_proc.h 7e4d05e3247ae6c2ec5b2a904abd101b194e15cb
***************
*** 3621,3626 ****
--- 3621,3628 ----
DESCR("deparse an encoded expression with pretty-print option");
DATA(insert OID = 2510 ( pg_prepared_statement PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_prepared_statement - _null_ ));
DESCR("get the prepared statements for this session");
+ DATA(insert OID = 2511 ( pg_cursor PGNSP PGUID 12 f f t t s 0 2249 "" _null_ _null_ _null_ pg_cursor - _null_ ));
+ DESCR("get the open cursors for this session");
/* non-persistent series generator */
DATA(insert OID = 1066 ( generate_series PGNSP PGUID 12 f f t t v 3 23 "23 23 23" _null_ _null_ _null_ generate_series_step_int4 - _null_ ));
============================================================
*** src/include/utils/builtins.h c696e31dc8dee5eb01f4dca58febefc9836bffc1
--- src/include/utils/builtins.h f8881733a8275dae08876c154b51701e58d61fed
***************
*** 865,868 ****
--- 865,871 ----
/* commands/prepare.c */
extern Datum pg_prepared_statement(PG_FUNCTION_ARGS);
+ /* utils/mmgr/portalmem.c */
+ extern Datum pg_cursor(PG_FUNCTION_ARGS);
+
#endif /* BUILTINS_H */
============================================================
*** src/include/utils/portal.h 9448c7e015885a27c6d82cac4a07574c81b5c6d4
--- src/include/utils/portal.h d81d4d9d0013ce25a1bc7b25ed2d19654361d1fb
***************
*** 72,78 ****
* PORTAL_MULTI_QUERY: all other cases. Here, we do not support partial
* execution: the portal's queries will be run to completion on first call.
*/
-
typedef enum PortalStrategy
{
PORTAL_ONE_SELECT,
--- 72,77 ----
***************
*** 166,171 ****
--- 165,174 ----
bool atEnd;
bool posOverflow;
long portalPos;
+
+ /* Presentation data, primarily used by the pg_cursors system view */
+ TimestampTz creation_time; /* time at which this portal was defined */
+ bool visible; /* include this portal in pg_cursors? */
} PortalData;
/*
============================================================
*** src/test/regress/expected/portals.out 568d43dd96ea5d7e041ac763261553cce806327e
--- src/test/regress/expected/portals.out 57839151bce53b43bce63a269c1fd21338c5f03e
***************
*** 676,682 ****
--- 676,705 ----
CLOSE foo11;
CLOSE foo12;
-- leave some cursors open, to test that auto-close works.
+ -- record this in the system view as well (don't query the time field there
+ -- however)
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ -------+------------------------------------------------------+-------------+-----------+---------------
+ foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo14 | DECLARE foo14 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1; | f | f | t
+ foo18 | DECLARE foo18 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo20 | DECLARE foo20 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo22 | DECLARE foo22 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ foo16 | DECLARE foo16 SCROLL CURSOR FOR SELECT * FROM tenk2; | f | f | t
+ (11 rows)
+
END;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ ------+-----------+-------------+-----------+---------------
+ (0 rows)
+
--
-- NO SCROLL disallows backward fetching
--
***************
*** 695,700 ****
--- 718,728 ----
--
-- Cursors outside transaction blocks
--
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ ------+-----------+-------------+-----------+---------------
+ (0 rows)
+
BEGIN;
DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
FETCH FROM foo25;
***************
*** 728,733 ****
--- 756,767 ----
2968 | 9999 | 0 | 0 | 8 | 8 | 68 | 968 | 968 | 2968 | 2968 | 136 | 137 | EKAAAA | PUOAAA | VVVVxx
(1 row)
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ -------+----------------------------------------------------------------+-------------+-----------+---------------
+ foo25 | DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; | t | f | t
+ (1 row)
+
CLOSE foo25;
--
-- ROLLBACK should close holdable cursors
***************
*** 808,810 ****
--- 842,871 ----
drop function count_tt1_v();
drop function count_tt1_s();
+ -- Create a cursor with the BINARY option and check the pg_cursors view
+ BEGIN;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ ------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+ (1 row)
+
+ DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ name | statement | is_holdable | is_binary | is_scrollable
+ ------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+ bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; | f | t | t
+ (2 rows)
+
+ ROLLBACK;
+ -- We should not see the portal that is created internally to
+ -- implement EXECUTE in pg_cursors
+ PREPARE cprep AS
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ EXECUTE cprep;
+ name | statement | is_holdable | is_binary | is_scrollable
+ ------+----------------------------------------------------------------------+-------------+-----------+---------------
+ c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f
+ (1 row)
+
============================================================
*** src/test/regress/expected/rules.out b34e70eeb88ed7655fb82d371c119c6249cf473c
--- src/test/regress/expected/rules.out a03bc3b5ce39ec7afbd45d65e27d2c2ef69a26a4
***************
*** 1277,1282 ****
--- 1277,1283 ----
viewname | definition
--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath);
+ pg_cursors | SELECT c.name, c."statement", c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name text, "statement" text, is_holdable boolean, is_binary boolean, is_scrollable boolean, creation_time timestamp with time zone);
pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin);
pg_indexes | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char"));
pg_locks | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid, l.objid, l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database" oid, relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint, "transaction" xid, pid integer, "mode" text, "granted" boolean);
***************
*** 1321,1327 ****
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (45 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
--- 1322,1328 ----
shoelace_obsolete | SELECT shoelace.sl_name, shoelace.sl_avail, shoelace.sl_color, shoelace.sl_len, shoelace.sl_unit, shoelace.sl_len_cm FROM shoelace WHERE (NOT (EXISTS (SELECT shoe.shoename FROM shoe WHERE (shoe.slcolor = shoelace.sl_color))));
street | SELECT r.name, r.thepath, c.cname FROM ONLY road r, real_city c WHERE (c.outline ## r.thepath);
toyemp | SELECT emp.name, emp.age, emp."location", (12 * emp.salary) AS annualsal FROM emp;
! (46 rows)
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
============================================================
*** src/test/regress/sql/portals.sql c5f9f0c2dedbec4d51afa62f781221e80460b613
--- src/test/regress/sql/portals.sql f993e71778eed98dce1e56d901b1a58597479e91
***************
*** 168,175 ****
--- 168,181 ----
-- leave some cursors open, to test that auto-close works.
+ -- record this in the system view as well (don't query the time field there
+ -- however)
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
END;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
--
-- NO SCROLL disallows backward fetching
--
***************
*** 188,193 ****
--- 194,202 ----
-- Cursors outside transaction blocks
--
+
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
BEGIN;
DECLARE foo25 SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
***************
*** 204,209 ****
--- 213,220 ----
FETCH ABSOLUTE -1 FROM foo25;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
CLOSE foo25;
--
***************
*** 278,280 ****
--- 289,305 ----
drop function count_tt1_v();
drop function count_tt1_s();
+
+
+ -- Create a cursor with the BINARY option and check the pg_cursors view
+ BEGIN;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1;
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ ROLLBACK;
+
+ -- We should not see the portal that is created internally to
+ -- implement EXECUTE in pg_cursors
+ PREPARE cprep AS
+ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+ EXECUTE cprep;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
