Re: [PATCHES] WITH RECURSIVE patch V0.1
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: WITH RECURSIVE patch V0.1 Here are patches to implement WITH RECURSIVE clause. There are some limitiations and TODO items(see the Current limitations section below). Comments are welcome. 1. Credit These patches were developed by Yoshiyuki Asaba ([EMAIL PROTECTED]) with some discussions with Tatsuo Ishii ([EMAIL PROTECTED]). This is really great! Kudos to all who made this happen :) Thanks. In addition to above, Sumitomo Electric Information Systems Co., and SRA OSS, Inc. Japan made this happen. I and Yoshiyuki Asaba are now in Ottawa to join PGCon. I hope to have some discussions on this here with anyone who are interested in this. -- Tatsuo Ishii SRA OSS, Inc. Japan I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] libpq Win32 Mutex performance patch
Magnus Hagander wrote: Alvaro Herrera wrote: Andrew Chernow wrote: Tom Lane wrote: Silently not locking is surely not very safe. Here is the dump code version of the patch. If anyone wants the return value idea, let me know. So is this a patch we want applied? Please see my other thread about libpq thread-locking which should be finished before this one, after which this patch will change. So no, this is not the version to be applied. I've applied a version of this to make libpq use CRITICAL_SECTION on win32. //Magnus -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1 Date: Mon, 19 May 2008 04:36:30 -0700 I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. statement_timeout :) Joshua D. Drake -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Joshua D. Drake [EMAIL PROTECTED] writes: Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. statement_timeout :) Good point. Though it occurs to me that if you set FETCH_COUNT in psql (or do the equivalent in your code ) statement_timeout becomes much less useful. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] \d+ should display the storage options for columns
Oleg pointed out to me here that while we have a command to *set* the toast
storage characteristics there's no actual supported way to display the current
settings.
It seems like this would be a reasonable thing to add to \d+
Index: src/bin/psql/describe.c
===
RCS file: /home/stark/src/REPOSITORY/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.170
diff -c -r1.170 describe.c
*** src/bin/psql/describe.c 5 May 2008 01:21:03 - 1.170
--- src/bin/psql/describe.c 21 May 2008 18:07:13 -
***
*** 865,871
if (verbose)
{
! cols++;
headers[cols - 1] = _(Description);
}
--- 865,872
if (verbose)
{
! cols+=2;
! headers[cols - 2] = _(Storage);
headers[cols - 1] = _(Description);
}
***
*** 877,883
\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
\n FROM pg_catalog.pg_attrdef d
\n WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum AND a.atthasdef),
! \n a.attnotnull, a.attnum);
if (verbose)
appendPQExpBuffer(buf, ,
pg_catalog.col_description(a.attrelid, a.attnum));
appendPQExpBuffer(buf, \nFROM pg_catalog.pg_attribute a);
--- 878,884
\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
\n FROM pg_catalog.pg_attrdef d
\n WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum AND a.atthasdef),
! \n a.attnotnull, a.attnum,
a.attstorage);
if (verbose)
appendPQExpBuffer(buf, ,
pg_catalog.col_description(a.attrelid, a.attnum));
appendPQExpBuffer(buf, \nFROM pg_catalog.pg_attribute a);
***
*** 957,967
--- 958,979
/* Description */
if (verbose)
+ {
+ char *storage = PQgetvalue(res, i, 6);
+
+ cells[i * cols + cols -2] =
+ (storage[0]=='p' ? PLAIN :
+(storage[0]=='m' ? MAIN :
+ (storage[0]=='x' ? EXTENDED :
+ (storage[0]=='e' ? EXTERNAL :
+ ???;
+
#ifdef WIN32
cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res,
i, 5), myopt.encoding);
#else
cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
#endif
+ }
}
/* Make title */
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] \d+ should display the storage options for columns
Gregory Stark [EMAIL PROTECTED] writes:
Oleg pointed out to me here that while we have a command to *set* the toast
storage characteristics there's no actual supported way to display the current
settings.
It seems like this would be a reasonable thing to add to \d+
Sorry, sent the wrong diff before. The previous diff didn't work due to an
array overflow.
Index: src/bin/psql/describe.c
===
RCS file: /home/stark/src/REPOSITORY/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.170
diff -c -r1.170 describe.c
*** src/bin/psql/describe.c 5 May 2008 01:21:03 - 1.170
--- src/bin/psql/describe.c 21 May 2008 20:25:26 -
***
*** 791,797
printTableOpt myopt = pset.popt.topt;
int i;
char *view_def = NULL;
! const char *headers[5];
char **cells = NULL;
char **footers = NULL;
char **ptr;
--- 791,797
printTableOpt myopt = pset.popt.topt;
int i;
char *view_def = NULL;
! const char *headers[6];
char **cells = NULL;
char **footers = NULL;
char **ptr;
***
*** 865,871
if (verbose)
{
! cols++;
headers[cols - 1] = _(Description);
}
--- 865,872
if (verbose)
{
! cols+=2;
! headers[cols - 2] = _(Storage);
headers[cols - 1] = _(Description);
}
***
*** 877,883
\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
\n FROM pg_catalog.pg_attrdef d
\n WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum AND a.atthasdef),
! \n a.attnotnull, a.attnum);
if (verbose)
appendPQExpBuffer(buf, ,
pg_catalog.col_description(a.attrelid, a.attnum));
appendPQExpBuffer(buf, \nFROM pg_catalog.pg_attribute a);
--- 878,884
\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
\n FROM pg_catalog.pg_attrdef d
\n WHERE d.adrelid = a.attrelid
AND d.adnum = a.attnum AND a.atthasdef),
! \n a.attnotnull, a.attnum,
a.attstorage);
if (verbose)
appendPQExpBuffer(buf, ,
pg_catalog.col_description(a.attrelid, a.attnum));
appendPQExpBuffer(buf, \nFROM pg_catalog.pg_attribute a);
***
*** 957,967
/* Description */
if (verbose)
#ifdef WIN32
! cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res,
i, 5), myopt.encoding);
#else
! cells[i * cols + cols - 1] = PQgetvalue(res, i, 5);
#endif
}
/* Make title */
--- 958,978
/* Description */
if (verbose)
+ {
+ char *storage = PQgetvalue(res, i, 5);
+
+ cells[i * cols + cols -2] =
+ (storage[0]=='p' ? _(plain) :
+(storage[0]=='m' ? _(main) :
+ (storage[0]=='x' ? _(extended) :
+ (storage[0]=='e' ? _(external) :
+ ???;
#ifdef WIN32
! cells[i * cols + cols - 1] = mbvalidate(PQgetvalue(res,
i, 6), myopt.encoding);
#else
! cells[i * cols + cols - 1] = PQgetvalue(res, i, 6);
#endif
+ }
}
/* Make title */
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
