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.sgml 2006-01-08 08:00:24.000000000
+0100
--- cvs.build/pgsql/doc/src/sgml/catalogs.sgml 2006-01-12 09:44:04.000000000
+0100
***************
*** 4358,4363 ****
--- 4358,4368 ----
<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>
***************
*** 4427,4432 ****
--- 4432,4519 ----
</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 was declared <literal>WITH
HOLD</literal>; <literal>false</literal> if the cursor was declared
<literal>WITHOUT HOLD</literal>.</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> if not.</entry>
+ </row>
+
+ <row>
+ <entry><structfield>is_scrollable</structfield></entry>
+ <entry><type>boolean</type></entry>
+ <entry></entry>
+ <entry><literal>true</literal> if the cursor was declared
<literal>SCROLL</literal>; <literal>false</literal> if the cursor was declared
<literal>NO SCROLL</literal>. If neither the <literal>SCROLL</literal> nor the
<literal>NO SCROLL</literal> keyword has been given,
<productname>PostgreSQL</productname> will choose one of both, its decision can
be seen in this view.</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>
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.000000000
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/close.sgml 2006-01-12 08:20:49.000000000
+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
+ <structname>pg_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.000000000
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/declare.sgml 2006-01-12
08:20:49.000000000 +0100
***************
*** 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>
diff -cr cvs/pgsql/src/backend/catalog/system_views.sql
cvs.build/pgsql/src/backend/catalog/system_views.sql
*** cvs/pgsql/src/backend/catalog/system_views.sql 2006-01-08
08:00:25.000000000 +0100
--- cvs.build/pgsql/src/backend/catalog/system_views.sql 2006-01-12
09:17:48.000000000 +0100
***************
*** 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
diff -cr cvs/pgsql/src/backend/commands/portalcmds.c
cvs.build/pgsql/src/backend/commands/portalcmds.c
*** cvs/pgsql/src/backend/commands/portalcmds.c 2005-11-03 18:11:35.000000000
+0100
--- cvs.build/pgsql/src/backend/commands/portalcmds.c 2006-01-12
09:36:39.000000000 +0100
***************
*** 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),
diff -cr cvs/pgsql/src/backend/utils/mmgr/portalmem.c
cvs.build/pgsql/src/backend/utils/mmgr/portalmem.c
*** cvs/pgsql/src/backend/utils/mmgr/portalmem.c 2005-11-22
19:17:27.000000000 +0100
--- cvs.build/pgsql/src/backend/utils/mmgr/portalmem.c 2006-01-12
09:38:37.000000000 +0100
***************
*** 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,195 ****
--- 194,200 ----
"Portal");
/* initialize portal fields that don't start off zero */
+ /* portal->status will be set to PORTAL_NEW */
portal->cleanup = PortalCleanup;
portal->createSubid = GetCurrentSubTransactionId();
portal->strategy = PORTAL_MULTI_QUERY;
***************
*** 197,202 ****
--- 202,215 ----
portal->atStart = true;
portal->atEnd = true; /* disallow fetches until query is set
*/
+ /* record the creation time only for named portals such that we can
display
+ * it in the pg_cursors system view (this will also be set for portals
that
+ * are called "<unnamed portal n>" but it's less expensive to just call
+ * GetCurrentTimestamp() for those as well than to check more
thoroughly)
+ * */
+ if (name[0])
+ portal->creation_time = GetCurrentTimestamp();
+
/* put portal in table (sets portal->name) */
PortalHashTableInsert(portal, name);
***************
*** 756,758 ****
--- 769,884 ----
PortalDrop(portal, false);
}
}
+
+ /* Find all available cursors */
+ Datum
+ pg_cursor(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ HASH_SEQ_STATUS *hash_seq;
+ PortalHashEnt *hentry;
+ Portal portal;
+
+ /* 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 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;
+ }
+
+ /* it's sufficient to check for hentry here because either we are not
+ * at the end of the list and hentry is != NULL, but then we loop above
+ * until we find a named portal. Then we have the named portal here.
+ * Or, we are at the end of the list but then hentry is NULL */
+ if (hentry)
+ {
+ Datum result;
+ HeapTuple tuple;
+ Datum values[6];
+ bool nulls[6];
+
+ 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);
+ }
+
diff -cr cvs/pgsql/src/include/catalog/pg_proc.h
cvs.build/pgsql/src/include/catalog/pg_proc.h
*** cvs/pgsql/src/include/catalog/pg_proc.h 2006-01-12 08:09:19.000000000
+0100
--- cvs.build/pgsql/src/include/catalog/pg_proc.h 2006-01-12
08:20:49.000000000 +0100
***************
*** 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_ ));
diff -cr cvs/pgsql/src/include/utils/builtins.h
cvs.build/pgsql/src/include/utils/builtins.h
*** cvs/pgsql/src/include/utils/builtins.h 2006-01-12 08:09:25.000000000
+0100
--- cvs.build/pgsql/src/include/utils/builtins.h 2006-01-12
08:20:49.000000000 +0100
***************
*** 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 */
diff -cr cvs/pgsql/src/include/utils/portal.h
cvs.build/pgsql/src/include/utils/portal.h
*** cvs/pgsql/src/include/utils/portal.h 2005-10-15 04:49:46.000000000
+0200
--- cvs.build/pgsql/src/include/utils/portal.h 2006-01-12 08:20:49.000000000
+0100
***************
*** 107,112 ****
--- 107,113 ----
ResourceOwner resowner; /* resources owned by portal */
void (*cleanup) (Portal portal); /* cleanup hook
*/
SubTransactionId createSubid; /* the ID of the creating
subxact */
+ TimestampTz creation_time;
/*
* if createSubid is InvalidSubTransactionId, the portal is held over
from
diff -cr cvs/pgsql/src/test/regress/expected/portals.out
cvs.build/pgsql/src/test/regress/expected/portals.out
*** cvs/pgsql/src/test/regress/expected/portals.out 2005-04-11
21:51:16.000000000 +0200
--- cvs.build/pgsql/src/test/regress/expected/portals.out 2006-01-12
10:20:37.000000000 +0100
***************
*** 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,875 ----
drop function count_tt1_v();
drop function count_tt1_s();
+ --
+ -- Create a cursor with the BINARY option and check the pg_cursor view along
+ --
+ 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)
+
+ CLOSE bc;
+ --
+ -- We should not see <unnamed portal %d> named portals
+ --
+ 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)
+
+ DEALLOCATE cprep;
+ ROLLBACK;
diff -cr cvs/pgsql/src/test/regress/expected/rules.out
cvs.build/pgsql/src/test/regress/expected/rules.out
*** cvs/pgsql/src/test/regress/expected/rules.out 2006-01-08
08:00:26.000000000 +0100
--- cvs.build/pgsql/src/test/regress/expected/rules.out 2006-01-12
10:20:37.000000000 +0100
***************
*** 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;
diff -cr cvs/pgsql/src/test/regress/sql/portals.sql
cvs.build/pgsql/src/test/regress/sql/portals.sql
*** cvs/pgsql/src/test/regress/sql/portals.sql 2005-04-11 21:51:16.000000000
+0200
--- cvs.build/pgsql/src/test/regress/sql/portals.sql 2006-01-12
10:17:08.000000000 +0100
***************
*** 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,316 ----
drop function count_tt1_v();
drop function count_tt1_s();
+
+
+ --
+ -- Create a cursor with the BINARY option and check the pg_cursor view along
+ --
+ 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;
+
+ CLOSE bc;
+
+ --
+ -- We should not see <unnamed portal %d> named portals
+ --
+
+ PREPARE cprep AS SELECT name, statement, is_holdable, is_binary,
is_scrollable FROM pg_cursors;
+
+ EXECUTE cprep;
+
+ DEALLOCATE cprep;
+
+ ROLLBACK;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
