Closing some 8.4 open items
If there are no objections, I'm going to remove the following items
from the list at
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items
finishing posix_fadvise patch
Push to TODO
change cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
Change the empty-input case for string_to_array?
Drop; there's no consensus that this should be changed
change psql's \df output for window functions?
Drop; there's no consensus that this should be changed
Polymorphic types vs. domains
Push to TODO
regards, tom lane
Tom Lane wrote:
If there are no objections, I'm going to remove the following items
from the list at
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Itemschange cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
I don't think we should let this go quite so easily, as this is a new
function, so the bias should be to "getting it right" rather than "don't
change it".
The supplied functionality is not only surprising, but easily obtained
by an existing function. ISTM if we're supplying a new function it
should have new functionality.
cheers
andrew
On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
Tom Lane wrote:
If there are no objections, I'm going to remove the following items
from the list at
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Itemschange cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
I don't think we should let this go quite so easily, as this is a new
function, so the bias should be to "getting it right" rather than "don't
change it".
I think it is right already, but the point is debatable.
The supplied functionality is not only surprising, but easily obtained by an
existing function. ISTM if we're supplying a new function it should have new
functionality.
Well, it's a compatibility function...
...Robert
Robert Haas wrote:
On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
Tom Lane wrote:
If there are no objections, I'm going to remove the following items
from the list at
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Itemschange cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
I don't think we should let this go quite so easily, as this is a new
function, so the bias should be to "getting it right" rather than "don't
change it".I think it is right already, but the point is debatable.
The supplied functionality is not only surprising, but easily obtained by an
existing function. ISTM if we're supplying a new function it should have new
functionality.Well, it's a compatibility function...
compatible with what?
The other thing that frankly bothers me is that we appear to have
acquired this function by a curious process which involved no proposal
or discussion that I have discovered. If there had been proper and
adequate discussion before the item was committed I wouldn't be making a
fuss now, whether or not I agreed with the result.
cheers
andrew
On Sun, Apr 05, 2009 at 07:55:44AM -0400, Robert Haas wrote:
On Sun, Apr 5, 2009 at 7:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
Tom Lane wrote:
If there are no objections, I'm going to remove the following items
from the list at
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Itemschange cardinality() for multi-dim arrays?
� � � �Drop; there's no consensus that this should be changed
I don't think we should let this go quite so easily, as this �is a
new function, so the bias should be to "getting it right" rather
than "don't change it".I think it is right already, but the point is debatable.
The supplied functionality is not only surprising, but easily
obtained by an existing function. ISTM if we're supplying a new
function it should have new functionality.Well, it's a compatibility function...
It's actually in SQL:2008.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Andrew Dunstan <andrew@dunslane.net> writes:
Robert Haas wrote:
Well, it's a compatibility function...
compatible with what?
It's required by the SQL standard.
The other thing that frankly bothers me is that we appear to have
acquired this function by a curious process which involved no proposal
or discussion that I have discovered. If there had been proper and
adequate discussion before the item was committed I wouldn't be making a
fuss now, whether or not I agreed with the result.
I think Peter put it in under the assumption that meeting spec-required
syntax would always pass muster. It is however fair to question whether
he made the right extrapolation of the spec's definition to cases that
are not in the spec.
Personally I am in favor of changing it to give the total number of
array elements, on the grounds that (1) that's as defensible a reading
of the spec as the other and (2) it would add actual new functionality
rather than being only a relabeling of array_length.
I will leave that item on the Open Items list. I take it no one's
excited about the others?
regards, tom lane
On Sun, Apr 5, 2009 at 12:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Robert Haas wrote:
Well, it's a compatibility function...
compatible with what?
It's required by the SQL standard.
The other thing that frankly bothers me is that we appear to have
acquired this function by a curious process which involved no proposal
or discussion that I have discovered. If there had been proper and
adequate discussion before the item was committed I wouldn't be making a
fuss now, whether or not I agreed with the result.I think Peter put it in under the assumption that meeting spec-required
syntax would always pass muster. It is however fair to question whether
he made the right extrapolation of the spec's definition to cases that
are not in the spec.Personally I am in favor of changing it to give the total number of
array elements, on the grounds that (1) that's as defensible a reading
of the spec as the other and (2) it would add actual new functionality
rather than being only a relabeling of array_length.I will leave that item on the Open Items list. I take it no one's
excited about the others?
I'm excited about some of them, but not to the point of not wanting to
ship beta. So +1 for removing them as per your suggestions.
...Robert
On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote:
I will leave that item on the Open Items list. I take it no one's
excited about the others?
When the windowing functions become a pain point, let's revisit :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote:
I will leave that item on the Open Items list. I take it no one's
excited about the others?
When the windowing functions become a pain point, let's revisit :)
The \df thing? That's something it'd be okay to revisit during beta,
IMHO. The things I'd really like to get right before beta are the ones
that are going to require an initdb to change. Like, say, the
cardinality() issue ...
regards, tom lane
On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
I'm excited about some of them, but not to the point of not wanting to
ship beta. So +1 for removing them as per your suggestions.
I'm somewhat excited about posix_fadvise but my general feeling was
that it was best to do nothing anyways. I don't know how to test these
questions though because they depend a lot on workload and pgbench or
synthetic queries which stress prefetching aren't especially good at
measuring how fast pages get evicted.
As far as reimplementing regular index scans -- I don't currently see
any way to do it in a way that would satisfy your demands that
wouldn't be insanely complex. Hopefully I'm missing something obvious
and if someone sees what I would be happy to go ahead and implement
something. But everything I've tried has turned into a monster.
--
greg
Greg Stark <stark@enterprisedb.com> writes:
On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
I'm excited about some of them, but not to the point of not wanting to
ship beta. �So +1 for removing them as per your suggestions.
I'm somewhat excited about posix_fadvise but my general feeling was
that it was best to do nothing anyways.
Yeah. One of the things in the back of my mind is that the planner is
going to prefer bitmap scans anyway for anything that fetches more than
a very few rows. So it's not clear that prefetching plain indexscans
is going to buy enough to justify a whole lotta work or ugliness there.
I'm content to throw this one on TODO.
regards, tom lane
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 12:21:41PM -0400, Tom Lane wrote:
I will leave that item on the Open Items list. I take it no one's
excited about the others?When the windowing functions become a pain point, let's revisit :)
The \df thing? That's something it'd be okay to revisit during beta,
IMHO.
OK, I'll work on this tomorrow :)
Cheers,
David.
The things I'd really like to get right before beta are the ones
that are going to require an initdb to change. Like, say, the
cardinality() issue ...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
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during beta,
IMHO.
OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do,
not code ...
regards, tom lane
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...
I was thinking I'd knock out a proposal or two.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...I was thinking I'd knock out a proposal or two.
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.
Another way, patch coming tomorrow, would be to add a \dw and remove
the functions where pg_proc.iswindowing is true from \df.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
window_prefix_result_data_type.difftext/plain; charset=us-asciiDownload
*** describe.c.orig 2009-04-06 19:47:44.000000000 -0700
--- describe.c 2009-04-06 20:02:25.000000000 -0700
***************
*** 202,207 ****
--- 202,208 ----
if (pset.sversion >= 80400)
appendPQExpBuffer(&buf,
+ " CASE WHEN p.proiswindow THEN 'WINDOW ' END ||\n"
" pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
" pg_catalog.pg_get_function_arguments(p.oid) as \"%s\"",
gettext_noop("Result data type"),
Tom Lane wrote:
Greg Stark <stark@enterprisedb.com> writes:
On Sun, Apr 5, 2009 at 6:54 PM, Robert Haas <robertmhaas@gmail.com> wrote:
I'm excited about some of them, but not to the point of not wanting to
ship beta. ���So +1 for removing them as per your suggestions.I'm somewhat excited about posix_fadvise but my general feeling was
that it was best to do nothing anyways.Yeah. One of the things in the back of my mind is that the planner is
going to prefer bitmap scans anyway for anything that fetches more than
a very few rows. So it's not clear that prefetching plain indexscans
is going to buy enough to justify a whole lotta work or ugliness there.I'm content to throw this one on TODO.
I am not inclined to add a TODO until we see actual value in doing it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Mon, Apr 06, 2009 at 10:51:22PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...I was thinking I'd knock out a proposal or two.
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.Another way, patch coming tomorrow, would be to add a \dw and remove
the functions where pg_proc.iswindowing is true from \df.
Here's another way, adding \dw.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
dw.patchtext/plain; charset=us-asciiDownload
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..626eadc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -396,6 +396,9 @@ exec_command(const char *cmd,
case 'u':
success = describeRoles(pattern, show_verbose);
break;
+ case 'w':
+ success = describeWindowingFunctions(pattern, show_system);
+ break;
case 'F': /* text search subsystem */
switch (cmd[2])
{
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index cc88984..c718267 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -286,6 +286,9 @@ describeFunctions(const char *pattern, bool verbose, bool showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
+ if (pset.sversion >= 80400)
+ appendPQExpBuffer(&buf, " AND NOT p.proiswindow\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
@@ -3069,6 +3072,60 @@ listUserMappings(const char *pattern, bool verbose)
return true;
}
+bool
+describeWindowingFunctions(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 80400)
+ {
+ fprintf(stderr, _("The server (version %d.%d) does not support windowing functions.\n"),
+ pset.sversion / 10000, (pset.sversion / 100) % 100);
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname as \"%s\",\n"
+ " p.proname as \"%s\",\n"
+ " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
+ " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\""
+ "\nFROM pg_catalog.pg_proc p"
+ "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
+ "WHERE p.proiswindow\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Result data type"),
+ gettext_noop("Argument data types"));
+
+ if (!showSystem && !pattern)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "p.proname", NULL,
+ "pg_catalog.pg_function_is_visible(p.oid)");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of windowing functions");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* printACLColumn
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 57e5c7b..3c6f2b1 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -75,5 +75,8 @@ extern bool listForeignServers(const char *pattern, bool verbose);
/* \deu */
extern bool listUserMappings(const char *pattern, bool verbose);
+/* \dw */
+extern bool describeWindowingFunctions(const char *pattern, bool showSystem);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 389feb0..d647069 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -221,6 +221,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\dw[S] [PATTERN] list windowing functions\n"));
fprintf(output, _(" \\l[+] list all databases\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, "\n");
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5602007..9f489c7 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -347,6 +347,21 @@ static const SchemaQuery Query_for_list_of_views = {
};
+static const SchemaQuery Query_for_list_of_windowing_functions = {
+ /* catname */
+ "pg_catalog.pg_proc p",
+ /* selcondition */
+ "p.proiswindow",
+ /* viscondition */
+ "pg_catalog.pg_function_is_visible(p.oid)",
+ /* namespace */
+ "p.pronamespace",
+ /* result */
+ "pg_catalog.quote_ident(p.proname)",
+ /* qualresult */
+ NULL
+};
+
/*
* Queries to get lists of names of various kinds of things, possibly
* restricted to names matching a partially entered name. In these queries,
@@ -629,7 +644,7 @@ psql_completion(char *text, int start, int end)
"\\a", "\\connect", "\\C", "\\cd", "\\copy", "\\copyright",
"\\d", "\\da", "\\db", "\\dc", "\\dC", "\\dd", "\\dD", "\\des", "\\deu", "\\dew", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl",
- "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du",
+ "\\dn", "\\do", "\\dp", "\\ds", "\\dS", "\\dt", "\\dT", "\\dv", "\\du", "\\dw",
"\\e", "\\echo", "\\encoding",
"\\f", "\\g", "\\h", "\\help", "\\H", "\\i", "\\l",
"\\lo_import", "\\lo_export", "\\lo_list", "\\lo_unlink",
@@ -2213,6 +2228,8 @@ psql_completion(char *text, int start, int end)
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
else if (strncmp(prev_wd, "\\dv", strlen("\\dv")) == 0)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL);
+ else if (strncmp(prev_wd, "\\dw", strlen("\\dw")) == 0)
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_windowing_functions, NULL);
/* must be at end of \d list */
else if (strncmp(prev_wd, "\\d", strlen("\\d")) == 0)
Tom,
finishing posix_fadvise patch
Push to TODO
So has fadvise been completely dropped from 8.4, or only partially?
change psql's \df output for window functions?
Drop; there's no consensus that this should be changed
Also, Fetter is currently working on a \dw for 8.5.
Polymorphic types vs. domains
Push to TODO
Agreed.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus <josh@agliodbs.com> wrote:
Tom,
finishing posix_fadvise patch
Push to TODO
So has fadvise been completely dropped from 8.4, or only partially?
Bitmap scans will support it, but index scans will not.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus <josh@agliodbs.com> wrote:
So has fadvise been completely dropped from 8.4, or only partially?
Bitmap scans will support it, but index scans will not.
And please note that we think bitmap scans are the larger part of
the win anyway. What's left undone there is some marginal mopup.
regards, tom lane
On Tue, Apr 07, 2009 at 07:42:51PM -0700, Josh Berkus wrote:
Tom,
finishing posix_fadvise patch
Push to TODO
So has fadvise been completely dropped from 8.4, or only partially?
change psql's \df output for window functions?
Drop; there's no consensus that this should be changed
Also, Fetter is currently working on a \dw for 8.5.
I sent it, targeted for 8.4 :) Windowing functions are new in 8.4, so
I'm thinking it should go into the front-end tools, too.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus <josh@agliodbs.com> wrote:
So has fadvise been completely dropped from 8.4, or only partially?
Bitmap scans will support it, but index scans will not.
And please note that we think bitmap scans are the larger part of
the win anyway. What's left undone there is some marginal mopup.
Can you elaborate on this? I'm fuzzy on why index scans can't benefit
from this as much as bitmap index scans.
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
And please note that we think bitmap scans are the larger part of
the win anyway. �What's left undone there is some marginal mopup.
Can you elaborate on this? I'm fuzzy on why index scans can't benefit
from this as much as bitmap index scans.
The main point is that the planner will prefer a bitmap scan for any
query that's estimated to return more than quite a small number of rows.
(In my experience the cutover point is in the single digits.) So
there's just not that much room to win for plain indexscans. Their
principal application is really for fetching single rows, a case where
prefetch is entirely useless because you have nothing to overlap.
regards, tom lane
On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 8, 2009 at 1:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
And please note that we think bitmap scans are the larger part of
the win anyway. What's left undone there is some marginal mopup.Can you elaborate on this? I'm fuzzy on why index scans can't benefit
from this as much as bitmap index scans.The main point is that the planner will prefer a bitmap scan for any
query that's estimated to return more than quite a small number of rows.
(In my experience the cutover point is in the single digits.) So
there's just not that much room to win for plain indexscans. Their
principal application is really for fetching single rows, a case where
prefetch is entirely useless because you have nothing to overlap.
That makes sense, but what about the nestloop-over-inner-indexscan case?
...Robert
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The main point is that the planner will prefer a bitmap scan for any
query that's estimated to return more than quite a small number of rows.
That makes sense, but what about the nestloop-over-inner-indexscan case?
What about it? The provided patch made no attempt to optimize that
case.
Doing so might well be interesting, but it's not getting done for 8.4.
I think it would be quite an invasive patch --- it's hard to see how to
do it without explicit support at the nestloop join level, so that you
could pipeline the processing of multiple key values coming from the outer
side of the join.
regards, tom lane
On Wed, Apr 8, 2009 at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Apr 8, 2009 at 10:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
The main point is that the planner will prefer a bitmap scan for any
query that's estimated to return more than quite a small number of rows.That makes sense, but what about the nestloop-over-inner-indexscan case?
What about it? The provided patch made no attempt to optimize that
case.Doing so might well be interesting, but it's not getting done for 8.4.
I think it would be quite an invasive patch --- it's hard to see how to
do it without explicit support at the nestloop join level, so that you
could pipeline the processing of multiple key values coming from the outer
side of the join.
OK, I think I'm now understanding your line of thinking. Thanks for
the explanation.
...Robert
On 4/7/09 10:17 PM, Tom Lane wrote:
Robert Haas<robertmhaas@gmail.com> writes:
On Tue, Apr 7, 2009 at 10:42 PM, Josh Berkus<josh@agliodbs.com> wrote:
So has fadvise been completely dropped from 8.4, or only partially?
Bitmap scans will support it, but index scans will not.
What about seq scans?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
What about seq scans?
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think. Do you have reason to think differently?
regards, tom lane
On 4/8/09 9:44 AM, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
What about seq scans?
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think. Do you have reason to think differently?
Well, Solaris 10 + UFS should be helped by fadvise -- in theory at
least, it would eliminate the need to modify your mount points for
better readahead when setting up a PG-Solaris server. Solaris-UFS quite
lazy about readahead. Zdenek, Jignesh?
You're probably correct about Linux and FreeBSD. I don't know if OSX +
HFS supports fadvise. If so, it could only help; readahead on HFS right
now is nonexistant.
Presumably fadvise is useless on Windows. Anyone know?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Wednesday, April 8, 2009, Josh Berkus <josh@agliodbs.com> wrote:
On 4/8/09 9:44 AM, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
What about seq scans?
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think. Do you have reason to think differently?Well, Solaris 10 + UFS should be helped by fadvise -- in theory at least, it would eliminate the need to modify your mount points for better readahead when setting up a PG-Solaris server. Solaris-UFS quite lazy about readahead. Zdenek, Jignesh?
You're probably correct about Linux and FreeBSD. I don't know if OSX + HFS supports fadvise. If so, it could only help; readahead on HFS right now is nonexistant.
Presumably fadvise is useless on Windows. Anyone know?
It is.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Josh Berkus wrote:
On 4/8/09 9:44 AM, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
What about seq scans?
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think. Do you have reason to think differently?Well, Solaris 10 + UFS should be helped by fadvise -- in theory at
least, it would eliminate the need to modify your mount points for
better readahead when setting up a PG-Solaris server. Solaris-UFS quite
lazy about readahead. Zdenek, Jignesh?You're probably correct about Linux and FreeBSD. I don't know if OSX +
HFS supports fadvise. If so, it could only help; readahead on HFS right
now is nonexistant.Presumably fadvise is useless on Windows. Anyone know?
It's important to distinguish what kind of fadvise we're talking about.
The bitmap scan code issues hints about individual pages, using
posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of
a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe
the support for the latter is much more widespread than for the former.
xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the OS
cache after writing them.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the
OS cache after writing them.
Even when archiving is on?
-Kevin
Kevin Grittner wrote:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
xlog.c now also uses POSIX_FADV_WONTNEED to drop WAL pages from the
OS cache after writing them.Even when archiving is on?
No, not in that case.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Dave Page wrote:
On Wednesday, April 8, 2009, Josh Berkus <josh@agliodbs.com> wrote:
Presumably fadvise is useless on Windows. Anyone know?
It is.
cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
it), but not any of the other flags. It maps it to
NtSetInformationFile() like this:
if (advice == POSIX_FADV_SEQUENTIAL)
fmi.Mode |= FILE_SEQUENTIAL_ONLY;
status = NtSetInformationFile (get_handle (), &io, &fmi, sizeof fmi,
FileModeInformation);
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote:
Dave Page wrote:
On Wednesday, April 8, 2009, Josh Berkus <josh@agliodbs.com> wrote:
Presumably fadvise is useless on Windows. Anyone know?
It is.
cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
it), but not any of the other flags. It maps it to
NtSetInformationFile() like this:
We set this in our open() wrapper in the code today. That doesn't
support changing it after the fact, of course.
//Magnus
Heikki,
It's important to distinguish what kind of fadvise we're talking about.
The bitmap scan code issues hints about individual pages, using
posix_fadvise(... POSIX_FADV_WILLNEED). For increasing the readahead of
a sequential scan, you'd want to use POSIX_FADV_SEQUENTIAL. I believe
the support for the latter is much more widespread than for the former.
OK, so this is potentially useful (pending testing) but it's a different
feature. We'll discuss it for 8.5.
The other thing I was going to ask you about is using posix_fadvise as
an alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK,
linux-only, whereas there are "direct write" fadvise flags which work on
multiple OSes.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Josh Berkus wrote:
The other thing I was going to ask you about is using posix_fadvise as
an alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK,
linux-only, whereas there are "direct write" fadvise flags which work on
multiple OSes.
What flags are those? I don't see any posix_fadvise flags that would do
anything like O_DIRECT.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Magnus Hagander <magnus@hagander.net> writes:
Heikki Linnakangas wrote:
cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
it), but not any of the other flags. It maps it to
NtSetInformationFile() like this:
We set this in our open() wrapper in the code today.
Really? Where? I didn't find any of the mentioned symbols in a quick
grep.
I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that
really means that it assumes *only* sequential accesses will happen,
I'm not sure that we'd want to turn it on.
regards, tom lane
Tom Lane wrote:
Magnus Hagander <magnus@hagander.net> writes:
Heikki Linnakangas wrote:
cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert
it), but not any of the other flags. It maps it to
NtSetInformationFile() like this:We set this in our open() wrapper in the code today.
Really? Where? I didn't find any of the mentioned symbols in a quick
grep.
We pass FILE_FLAG_SEQUENTIAL_SCAN to the open call if O_SEQUENTIAL is
specified.
I'm not sure how Windows interprets FILE_SEQUENTIAL_ONLY, but if that
really means that it assumes *only* sequential accesses will happen,
I'm not sure that we'd want to turn it on.
It's an access-optimization hint, that's all.
//Magnus
On Wed, Apr 8, 2009 at 6:42 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
Dave Page wrote:
On Wednesday, April 8, 2009, Josh Berkus <josh@agliodbs.com> wrote:
Presumably fadvise is useless on Windows. Anyone know?
It is.
cygwin supports POSIX_FADV_SEQUENTIAL (and POSIX_FADV_NORMAL to revert it),
but not any of the other flags. It maps it to NtSetInformationFile() like
this:if (advice == POSIX_FADV_SEQUENTIAL)
fmi.Mode |= FILE_SEQUENTIAL_ONLY;
status = NtSetInformationFile (get_handle (), &io, &fmi, sizeof fmi,
FileModeInformation);
Which is only useful with async IO as far as I'm aware.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Tom,
change cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
Andrew pinged me on this. While there's no consensus that it should be
changed, there's no consensus it shouldn't, either. And once we release
it, we've set the way it operates in cement, so I'd like to get a
consensus one way or the other. I think if we *can't* get a consensus,
it's better to omit the syntax from 8.4 then risk deploying syntax we'll
want to change later.
For my part, I'd like to know what things other than arrays
<collection_expression> in the standard applies to. I think the most
sensible course is to make cardinality(array[]) behave consistently with
cardinality(other_stuff) when we get around to implementing it.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Wed, 8 Apr 2009, Heikki Linnakangas wrote:
Josh Berkus wrote:
The other thing I was going to ask you about is using posix_fadvise as an
alternative to O_DIRECT for the xlog. O_DIRECT is, AFAIK, linux-only,
whereas there are "direct write" fadvise flags which work on multiple OSes.What flags are those? I don't see any posix_fadvise flags that would do
anything like O_DIRECT.
A good implementation of FADV_NOREUSE would work similarly to O_DIRECT,
writing things out but not keeping them around the OS cache. (suggested
long ago even:
http://archives.postgresql.org//pgsql-hackers/2003-10/msg01492.php )
I know there's a problem with O_DIRECT not working on Solaris; see the
following:
http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and
http://blogs.sun.com/roch/entry/zfs_and_directio
http://docs.sun.com/app/docs/doc/816-5168/directio-3c
I'm not sure whether using an fadvise call like FADV_NOREUSE will work any
better though; it may be the case that only that directio call is
sufficient on Solaris. A Solaris-specific code path that calls directio
is what MySQL does here:
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method
I wanted to include such a patch in 8.4 but my one Solaris project got
sidelined.
Saying that O_DIRECT is "linux-only" doesn't seem right though. The same
thread referenced above started by announcing O_DIRECT support on FreeBSD:
http://archives.postgresql.org//pgsql-hackers/2003-10/msg01482.php and the
above MySQL documentation supports that it works on FreeBSD, too. I've
seen claims that it works fine on Mac OS X, too, although MySQL may not
support that:
http://labs.cybozu.co.jp/blog/kazuhoatwork/2009/02/using_o_direct_on_mac_os_x.php
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Wed, 8 Apr 2009, Tom Lane wrote:
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think.
There's some interesting comments on this subject (and about what fadvise
DONTNEED does) in the RRD research paper about managing their buffer
cache:
http://www.usenix.org/event/lisa07/tech/full_papers/plonka/plonka_html/index.html
They suggest the Linux read-ahead is pretty aggressive by default, which
might explain why I wasn't able to replicate any speed-up with the
seqeuential scan patch on my system. (The original submission showed a
significant speedup on Linux, but was from what sounded like a somewhat
broken kernel--known buggy controller driver I seem to recall)
I suspect we may need to find a platform where the default OS readahead is
a slacker, *and* that pays attention to POSIX_FADV_SEQUENTIAL, in order to
show any improvement from that patch.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Josh Berkus <josh@agliodbs.com> writes:
Tom,
change cardinality() for multi-dim arrays?
Drop; there's no consensus that this should be changed
Andrew pinged me on this. While there's no consensus that it should be
changed, there's no consensus it shouldn't, either. And once we release
it, we've set the way it operates in cement, so I'd like to get a
consensus one way or the other.
Yeah. I would like to change it; Peter evidently thinks it's good
as-is. Where do we go from here?
For my part, I'd like to know what things other than arrays
<collection_expression> in the standard applies to. I think the most
sensible course is to make cardinality(array[]) behave consistently with
cardinality(other_stuff) when we get around to implementing it.
There is no equivalent of multi-dimensional arrays in other kinds of
collections, so I'm not seeing that there is any good guide there.
regards, tom lane
Tom,
There is no equivalent of multi-dimensional arrays in other kinds of
collections, so I'm not seeing that there is any good guide there.
What else *does* SQL:2008 consider a collection?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On Wednesday 08 April 2009 21:56:38 Tom Lane wrote:
For my part, I'd like to know what things other than arrays
<collection_expression> in the standard applies to. I think the most
sensible course is to make cardinality(array[]) behave consistently with
cardinality(other_stuff) when we get around to implementing it.There is no equivalent of multi-dimensional arrays in other kinds of
collections, so I'm not seeing that there is any good guide there.
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).
Collection types in SQL are arrays and multisets. Multisets are essentially
arrays without ordering. Many people already use arrays like that, and I
would find it interesting to support real multisets in the future.
Currently, we don't support collections of collections, specifically arrays of
arrays. We only have multidimensional arrays. Multidimensional arrays in
PostgreSQL and arrays of arrays in SQL are actually pretty close in the
interface they present, except that the subscript order is reversed. If you
ignore that, the current cardinality() function gives you pretty much
conforming behavior on "nested arrays", at least for the first level.
The question now is, if we want to move toward supporting multisets and
arbitrary nested collections in the future, do we
1. Transform our view of a multidimensional array into nested arrays, and then
extend that to allow multisets. (The implementation could stay quite the
same; just mark some dimensions as "this is a multiset".) And then perhaps
address the subscript ordering issue by some hitherto unknown plan.
- or -
2. Extend the system so you can have nested multidimensional arrays (e.g., a
4x4 array containing 3x3 arrays), and then extend that to also allow nesting
with a separate multiset structure (possibly also multidimensional). I think
this would probably make a mess out of the subscripting.
- or -
3. SQL DIE DIE DIE!!!
If you think (1) then the current implementation of cardinality() is correct,
if you think (2) then Tom's proposed change is correct, if you think (3) the
function should be removed.
Peter Eisentraut <peter_e@gmx.net> writes:
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).
At this point I'd support that too. It doesn't seem that getting
cardinality() into 8.4 is important enough to risk making a decision
that we'd regret later. And I think it's not hard to make the case
that we might regret either of the other choices later, depending on
where we go with arrays.
regards, tom lane
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).At this point I'd support that too. It doesn't seem that getting
cardinality() into 8.4 is important enough to risk making a decision
that we'd regret later. And I think it's not hard to make the case
that we might regret either of the other choices later, depending on
where we go with arrays.
+1
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).At this point I'd support that too.
+1
Since there were no objections, and there is no time left before beta1,
I'm going to go ahead and remove cardinality().
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).At this point I'd support that too.
+1
Since there were no objections, and there is no time left before beta1,
I'm going to go ahead and remove cardinality().
Do we want a TODO?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On 4/9/09 10:42 AM, Bruce Momjian wrote:
Tom Lane wrote:
Andrew Dunstan<andrew@dunslane.net> writes:
Tom Lane wrote:
Peter Eisentraut<peter_e@gmx.net> writes:
Here is my thinking, and considering that that would basically involve a
forward-looking design decision right now, I would support dropping the
cardinality() function from 8.4 (if people agree that this is in fact the
design decision to make).At this point I'd support that too.
+1
Since there were no objections, and there is no time left before beta1,
I'm going to go ahead and remove cardinality().Do we want a TODO?
No, I don't think so. It would just be a tag-on to whatever TODO we
already have about implementing multisets and collections.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
On 04/08/09 13:10, Josh Berkus wrote:
On 4/8/09 9:44 AM, Tom Lane wrote:
Josh Berkus<josh@agliodbs.com> writes:
What about seq scans?
If the kernel can't read-ahead a seqscan by itself, it's unlikely to
be smart enough to be helped by posix_fadvise ... or at least so I
would think. Do you have reason to think differently?Well, Solaris 10 + UFS should be helped by fadvise -- in theory at
least, it would eliminate the need to modify your mount points for
better readahead when setting up a PG-Solaris server. Solaris-UFS
quite lazy about readahead. Zdenek, Jignesh?
Definitely this helps.. specially since forcedirectio hurts CLOGs and
helps WAL .. something that can be done without really impacting the
whole file system always helps.
Solaris by default only does readahead upto 56K and max tunable is 1MB.
If you use forcedirectio there is no readahead by the filesystem itself
ZFS is different it has no forcedirectio and hence fadvise flag for now
is ignored.
Regards,
Jignesh
Show quoted text
You're probably correct about Linux and FreeBSD. I don't know if OSX
+ HFS supports fadvise. If so, it could only help; readahead on HFS
right now is nonexistant.Presumably fadvise is useless on Windows. Anyone know?
On Tue, Apr 07, 2009 at 07:28:25PM -0700, David Fetter wrote:
On Mon, Apr 06, 2009 at 10:51:22PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 05:57:46PM -0700, David Fetter wrote:
On Sun, Apr 05, 2009 at 08:55:07PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sun, Apr 05, 2009 at 02:07:32PM -0400, Tom Lane wrote:
The \df thing? That's something it'd be okay to revisit during
beta, IMHO.OK, I'll work on this tomorrow :)
I think what we were lacking was consensus on what it should do, not
code ...I was thinking I'd knock out a proposal or two.
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.Another way, patch coming tomorrow, would be to add a \dw and remove
the functions where pg_proc.iswindowing is true from \df.Here's another way, adding \dw.
Revised patch attached. \dw does not need an 'S' decorator, and would
be confusing with one now as there are only a few windowing functions,
and all of those system.
Also included are SGML docs. Mea culpa.
There is one translatable string added. Sorry about that.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachments:
dw2.difftext/plain; charset=us-asciiDownload
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 10d42ca..2e6484f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1257,6 +1257,18 @@ testdb=>
<varlistentry>
+ <term><literal>\dw [ <replaceable class="parameter">pattern</replaceable> ]</literal></term>
+ <listitem>
+ <para>
+ Lists all windowing functions. If <replaceable
+ class="parameter">pattern</replaceable> is specified, only
+ those windowing functions whose names match the pattern are listed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\edit</literal> (or <literal>\e</literal>) <literal><optional> <replaceable class="parameter">filename</replaceable> </optional></literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index b39466d..b737daf 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -396,6 +396,9 @@ exec_command(const char *cmd,
case 'u':
success = describeRoles(pattern, show_verbose);
break;
+ case 'w':
+ success = describeWindowingFunctions(pattern);
+ break;
case 'F': /* text search subsystem */
switch (cmd[2])
{
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 731baf8..5699e29 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -280,6 +280,9 @@ describeFunctions(const char *pattern, bool verbose, bool showSystem)
appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname <> 'information_schema'\n");
+ if (pset.sversion >= 80400)
+ appendPQExpBuffer(&buf, " AND NOT p.proiswindow\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
@@ -3059,6 +3062,56 @@ listUserMappings(const char *pattern, bool verbose)
return true;
}
+bool
+describeWindowingFunctions(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ if (pset.sversion < 80400)
+ {
+ fprintf(stderr, _("The server (version %d.%d) does not support windowing functions.\n"),
+ pset.sversion / 10000, (pset.sversion / 100) % 100);
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname as \"%s\",\n"
+ " p.proname as \"%s\",\n"
+ " pg_catalog.pg_get_function_result(p.oid) as \"%s\",\n"
+ " pg_catalog.pg_get_function_arguments(p.oid) as \"%s\""
+ "\nFROM pg_catalog.pg_proc p"
+ "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
+ "WHERE p.proiswindow\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Result data type"),
+ gettext_noop("Argument data types"));
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "p.proname", NULL,
+ "pg_catalog.pg_function_is_visible(p.oid)");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1, 2, 4;");
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of windowing functions");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
/*
* printACLColumn
*
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 57e5c7b..3cad080 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -75,5 +75,8 @@ extern bool listForeignServers(const char *pattern, bool verbose);
/* \deu */
extern bool listUserMappings(const char *pattern, bool verbose);
+/* \dw */
+extern bool describeWindowingFunctions(const char *pattern);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 389feb0..494d42f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -221,6 +221,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
+ fprintf(output, _(" \\dw [PATTERN] list windowing functions\n"));
fprintf(output, _(" \\l[+] list all databases\n"));
fprintf(output, _(" \\z [PATTERN] same as \\dp\n"));
fprintf(output, "\n");
David Fetter <david@fetter.org> writes:
Revised patch attached. \dw does not need an 'S' decorator,
Yes it does. We have only painfully gotten to the point of having
consistent behavior across all the \d commands. We are not going
to break that consistency before it's even shipped.
Perhaps more to the point: the previous round of discussion about this
already rejected the idea of treating window functions as a category
fundamentally separate from plain functions --- that is, we are not
following the "aggregate" model of having separate commands for
aggregate functions. So it's not apparent to me that a separate \dw
command is a good solution to start with.
regards, tom lane
Tom Lane wrote:
David Fetter <david@fetter.org> writes:
Revised patch attached. \dw does not need an 'S' decorator,
Yes it does. We have only painfully gotten to the point of having
consistent behavior across all the \d commands. We are not going
to break that consistency before it's even shipped.Perhaps more to the point: the previous round of discussion about this
already rejected the idea of treating window functions as a category
fundamentally separate from plain functions --- that is, we are not
following the "aggregate" model of having separate commands for
aggregate functions. So it's not apparent to me that a separate \dw
command is a good solution to start with.
Yea, I thought we were going to do this:
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.
but I don't see this behavior in CVS.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
Yea, I thought we were going to do this:
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.
but I don't see this behavior in CVS.
IIRC, my original proposal involved adding something to the argument
list --- it seems more natural to regard window-ness as having something
to do with the arguments than the result. But that was shot down on the
grounds of not fitting in well unless we wanted to add more decoration,
like parens around the regular argument list.
Another idea was to add a new column to the \df output to mark
window-ness. Which, as I recall, *nobody* liked. But maybe if we
only did it for \df+ it would be more tolerable?
regards, tom lane
On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
Revised patch attached. \dw does not need an 'S' decorator,
Yes it does. We have only painfully gotten to the point of having
consistent behavior across all the \d commands. We are not going to
break that consistency before it's even shipped.
I'd be happy to revert that part.
Perhaps more to the point: the previous round of discussion about
this already rejected the idea of treating window functions as a
category fundamentally separate from plain functions --- that is, we
are not following the "aggregate" model of having separate commands
for aggregate functions.
I hadn't seen any such a consensus. If anything, the consensus seemed
to be going toward the \da and not away from it, hence the revised
patch.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
On Fri, Apr 10, 2009 at 11:30:30AM -0400, Tom Lane wrote:
Perhaps more to the point: the previous round of discussion about
this already rejected the idea of treating window functions as a
category fundamentally separate from plain functions --- that is, we
are not following the "aggregate" model of having separate commands
for aggregate functions.
I hadn't seen any such a consensus.
We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION, ALTER
WINDOW FUNCTION, etc. If psql uses \dw it will be presenting a
different world view than exists at the SQL level.
regards, tom lane
Tom Lane escreveu:
Bruce Momjian <bruce@momjian.us> writes:
Yea, I thought we were going to do this:
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.but I don't see this behavior in CVS.
IIRC, my original proposal involved adding something to the argument
list --- it seems more natural to regard window-ness as having something
to do with the arguments than the result. But that was shot down on the
grounds of not fitting in well unless we wanted to add more decoration,
like parens around the regular argument list.Another idea was to add a new column to the \df output to mark
window-ness. Which, as I recall, *nobody* liked. But maybe if we
only did it for \df+ it would be more tolerable?
Adding another column to \df+ is not a good idea; there are already too much
columns. Window functions are special functions (they even have an different
syntax and separate section in docs) and are not less special than aggregate
functions. So +1 to add \dw for them.
--
Euler Taveira de Oliveira
http://www.timbira.com/
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Perhaps more to the point: the previous round of discussion about
this already rejected the idea of treating window functions as a
category fundamentally separate from plain functions --- that is,
we are not following the "aggregate" model of having separate
commands for aggregate functions.
I hadn't seen any such a consensus.
Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION,
Tom> ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be
Tom> presenting a different world view than exists at the SQL level.
I'm not sure why that would matter. The fact that it is CREATE
FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean
that window functions aren't a distinctly different animal to normal
functions. The usage and syntax is different enough that putting them
all together under \df seems forced.
--
Andrew (irc:RhodiumToad)
2009/4/11 Andrew Gierth <andrew@tao11.riddles.org.uk>:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> Perhaps more to the point: the previous round of discussion about
>>> this already rejected the idea of treating window functions as a
>>> category fundamentally separate from plain functions --- that is,
>>> we are not following the "aggregate" model of having separate
>>> commands for aggregate functions.>> I hadn't seen any such a consensus.
Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION,
Tom> ALTER WINDOW FUNCTION, etc. If psql uses \dw it will be
Tom> presenting a different world view than exists at the SQL level.I'm not sure why that would matter. The fact that it is CREATE
FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean
that window functions aren't a distinctly different animal to normal
functions. The usage and syntax is different enough that putting them
all together under \df seems forced.
Yeah, but all the window functions are stored in pg_proc.
Regards,
--
Hitoshi Harada
2009/4/11 Tom Lane <tgl@sss.pgh.pa.us>:
Bruce Momjian <bruce@momjian.us> writes:
Yea, I thought we were going to do this:
Please find enclosed one way to handle it, this being prepending
WINDOW to the result types in \df.but I don't see this behavior in CVS.
IIRC, my original proposal involved adding something to the argument
list --- it seems more natural to regard window-ness as having something
to do with the arguments than the result. But that was shot down on the
grounds of not fitting in well unless we wanted to add more decoration,
like parens around the regular argument list.
And someone has claimed the argument column won't fit the syntax of
DROP FUNCTION, which is not sure to be harmful or not.
Another idea was to add a new column to the \df output to mark
window-ness. Which, as I recall, *nobody* liked. But maybe if we
only did it for \df+ it would be more tolerable?
The only negative opinion of this is added column is useful for only
window function so far. And nobody can find the future possible
extension by this column.
So I'm +1 for "do nothing now", and let's wait for users reactions.
The changes for this in the future seems not so painful.
Regards,
--
Hitoshi Harada
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
2009/4/11 Andrew Gierth <andrew@tao11.riddles.org.uk>:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
�>>> Perhaps more to the point: the previous round of discussion about
�>>> this already rejected the idea of treating window functions as a
�>>> category fundamentally separate from plain functions --- that is,
�>>> we are not following the "aggregate" model of having separate
�>>> commands for aggregate functions.�>> I hadn't seen any such a consensus.
�Tom> We do not have CREATE WINDOW FUNCTION, DROP WINDOW FUNCTION,
�Tom> ALTER WINDOW FUNCTION, etc. �If psql uses \dw it will be
�Tom> presenting a different world view than exists at the SQL level.I'm not sure why that would matter. The fact that it is CREATE
FUNCTION ... WINDOW rather than CREATE WINDOW FUNCTION doesn't mean
that window functions aren't a distinctly different animal to normal
functions. The usage and syntax is different enough that putting them
all together under \df seems forced.Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)
Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.
Regards,
--
Hitoshi Harada
On 11 Apr 2009, at 08:01, Hitoshi Harada wrote:
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.
Maybe trigger functions should be displayed separately too than ?
2009/4/11 Grzegorz Jaskiewicz <gj@pointblue.com.pl>:
On 11 Apr 2009, at 08:01, Hitoshi Harada wrote:
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.Maybe trigger functions should be displayed separately too than ?
You don't catch the point. The aggregate entries in pg_proc have
prosrc = 'aggregate_dummy', which means they're dummy and the entities
are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they
are actually plain functions with trigger return type.
Regards,
--
Hitoshi Harada
On 11 Apr 2009, at 13:33, Hitoshi Harada wrote:
Maybe trigger functions should be displayed separately too than ?
You don't catch the point. The aggregate entries in pg_proc have
prosrc = 'aggregate_dummy', which means they're dummy and the entities
are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they
are actually plain functions with trigger return type.
yes, that's from strictly insider's point of view. Based on the
implementation of that in postgresql, but you guys talk about user
perspective, after all - psql is for users, not only for postgresql
hackers.
So the better question would be, can you use window, aggregate,
trigger functions the same way other procedures ? I guess the answer
is no :)
Hence, if classify - than I would suggest to do it completely and
fair, and not judge it only from postgresql-hacker perspective.
I am hardly the postgresql-hacker myself, so it is my opinion from
user perspective, that also understands where your opinion comes from.
On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz
<gj@pointblue.com.pl> wrote:
On 11 Apr 2009, at 08:01, Hitoshi Harada wrote:
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.Maybe trigger functions should be displayed separately too than ?
We're up to at least four different categories of functions that
people think might require special treatment: window, trigger, I/O,
everything else. And then there are other categories you might want
to include/exclude: conversion functions, referential integrity
functions, operator functions, ... it quickly gets out of control.
Maybe we should consider some sort of option syntax for blackslash
commands. Like, \df -w to see just window functions, \df -c to see
just conversion functions, \df -wc to see those two types but not
anything else. Actually, I don't really like that syntax either,
because it's just propagating the existing dubious design decision of
identifying the behavior you want with longer and longer strings of
inscrutable single-digit modifiers. But some sort of more powerful
syntax would be good.
This problem is not limited to searching either - for example, I'd
like to be able to do "\d foo, except don't show me the foreign-keys
because there are a zillion of them and they make the output not fit
on the screen".
...Robert
On Sat, Apr 11, 2009 at 01:39:47PM +0100, Grzegorz Jaskiewicz wrote:
On 11 Apr 2009, at 13:33, Hitoshi Harada wrote:
Maybe trigger functions should be displayed separately too than ?
You don't catch the point. The aggregate entries in pg_proc have
prosrc = 'aggregate_dummy', which means they're dummy and the
entities are stored in pg_aggregate. Triggers in pg_proc are dummy?
No, they are actually plain functions with trigger return type.yes, that's from strictly insider's point of view. Based on the
implementation of that in postgresql, but you guys talk about user
perspective, after all - psql is for users, not only for postgresql
hackers.
That was my thought on this, too.
So the better question would be, can you use window, aggregate, trigger
functions the same way other procedures ? I guess the answer is no :)
For the first two, it's no. For the third, it's what people are used
to, including people who are extending a helping hand via our many
help channels.
Hence, if classify - than I would suggest to do it completely and
fair, and not judge it only from postgresql-hacker perspective.I am hardly the postgresql-hacker myself, so it is my opinion from
user perspective, that also understands where your opinion comes
from.
The amount of code I've gotten into the back end is absolutely
minuscule. It's psql where I can currently help people see a new
feature.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote:
On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz
<gj@pointblue.com.pl> wrote:On 11 Apr 2009, at 08:01, Hitoshi Harada wrote:
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.Maybe trigger functions should be displayed separately too than ?
We're up to at least four different categories of functions that
people think might require special treatment: window, trigger, I/O,
everything else.
The current psql has \da and \df, the latter of which now includes I/O
functions. I contend that windowing functions are different enough
that they require a separate category.
You do bring up an interesting point for 8.5 or later, which would be
a complete redo of psql from the ground up. Let's hash out a proposal
for that in a separate thread once we get 8.4 out the door :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/4/11 Grzegorz Jaskiewicz <gj@pointblue.com.pl>:
On 11 Apr 2009, at 13:33, Hitoshi Harada wrote:
Maybe trigger functions should be displayed separately too than ?
You don't catch the point. The aggregate entries in pg_proc have
prosrc = 'aggregate_dummy', which means they're dummy and the entities
are stored in pg_aggregate. Triggers in pg_proc are dummy? No, they
are actually plain functions with trigger return type.yes, that's from strictly insider's point of view. Based on the
implementation of that in postgresql, but you guys talk about user
perspective, after all - psql is for users, not only for postgresql hackers.
So the better question would be, can you use window, aggregate, trigger
functions the same way other procedures ? I guess the answer is no :)
Hence, if classify - than I would suggest to do it completely and fair, and
not judge it only from postgresql-hacker perspective.I am hardly the postgresql-hacker myself, so it is my opinion from user
perspective, that also understands where your opinion comes from.
It seems I that didn't catch the point. Still, I don't like such
variable syntax for psql -- window, aggregate, plain function,
trigger, i/o, cast and more -- I cannot remeber them all :(
Regards,
--
Hitoshi Harada
David Fetter <david@fetter.org> writes:
On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote:
We're up to at least four different categories of functions that
people think might require special treatment: window, trigger, I/O,
everything else.
The current psql has \da and \df, the latter of which now includes I/O
functions. I contend that windowing functions are different enough
that they require a separate category.
I think the fact that aggregates have a separate command is somewhat
historical. However, the fact remains that at the SQL level there is
CREATE/DROP/etc AGGREGATE and CREATE/DROP/etc FUNCTION, and nothing
else. If we don't hang psql's hat on that same hook then we are going
to confuse users --- not to mention that this thread will never reach a
resolution because there will be too many alternatives.
regards, tom lane
On Sat, Apr 11, 2009 at 10:32:14AM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote:
We're up to at least four different categories of functions that
people think might require special treatment: window, trigger,
I/O, everything else.The current psql has \da and \df, the latter of which now includes
I/O functions. I contend that windowing functions are different
enough that they require a separate category.I think the fact that aggregates have a separate command is somewhat
historical. However, the fact remains that at the SQL level there
is CREATE/DROP/etc AGGREGATE and CREATE/DROP/etc FUNCTION, and
nothing else. If we don't hang psql's hat on that same hook then we
are going to confuse users --- not to mention that this thread will
never reach a resolution because there will be too many
alternatives.
The "do nothing" solution is unacceptable because windowing functions
behave in a way that's essentially different, from the user's
perspective, from other functions including aggregates. Speaking of
aggregates, they should probably show up in the windowing functions
section too, as they behave differently there. For example, a sum()
over a window with ordering is a *running* sum (to the extent that the
ORDER BY clause causes unique values), a completely different behavior
from its normal aggregate/non-ordered windowing behavior.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
The "do nothing" solution is unacceptable because windowing functions
behave in a way that's essentially different, from the user's
perspective, from other functions including aggregates.
I don't like doing nothing either, but I disagree with your conclusion
that window functions are as different from regular ones as aggregates
are. Aggregates cause a sea-change in the behavior of the query around
them; window functions do not. The call syntax of window functions is
a bit odd (which is why \df needs to label them) but they still produce
one output value where a regular function would produce one output
value, and they don't have an impact on the semantics of the surrounding
query.
My own take on it is that actually I'd prefer one command for all of
these. If I say "\df sum" it would be good if the output included the
sum() aggregates; the reason being that I might be wondering if I can
create a plain function named sum. If I have to check not only \df and
\da but also \dw for conflicts, that's going to be a real PITA. Also,
pity the poor newbie who is unclear on the distinctions between these
different function-looking animals, and is just trying to find some
documentation on rank().
If we were designing in a green field I think you could make a real
strong case for a single \df command with an output column "type" having
the alternatives regular, aggregate, window, and maybe trigger.
regards, tom lane
On Sat, Apr 11, 2009 at 11:13:59AM -0400, Tom Lane wrote:
My own take on it is that actually I'd prefer one command for all of
these. If I say "\df sum" it would be good if the output included the
sum() aggregates; the reason being that I might be wondering if I can
create a plain function named sum. If I have to check not only \df and
\da but also \dw for conflicts, that's going to be a real PITA. Also,
pity the poor newbie who is unclear on the distinctions between these
different function-looking animals, and is just trying to find some
documentation on rank().If we were designing in a green field I think you could make a real
strong case for a single \df command with an output column "type" having
the alternatives regular, aggregate, window, and maybe trigger.
What would it do for triggers?
Sounds like a general identifier search; there seem to be two big
namespaces in PG at the moment, that of things that look like function
calls and that of relations (and their types).
CREATE TABLE foo ( i int, t text );
and
CREATE TYPE foo AS ( t text);
both go into the same namespace so would appear to be a similar symptom
as above. I have a feeling this is going a bit further than you're
thinking above.
Not sure about the newbie argument; I'd expect them to be using google
and wouldn't know much about the backslash commands in psql.
--
Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> writes:
On Sat, Apr 11, 2009 at 11:13:59AM -0400, Tom Lane wrote:
If we were designing in a green field I think you could make a real
strong case for a single \df command with an output column "type" having
the alternatives regular, aggregate, window, and maybe trigger.
What would it do for triggers?
Well, I was just reacting to a comment upthread about triggers not being
callable in the same contexts as other functions. I'm not hot to label
them separately. The return type would be shown as trigger, which in
theory is enough to tell you it's a trigger.
regards, tom lane
On Sat, Apr 11, 2009 at 8:58 AM, David Fetter <david@fetter.org> wrote:
On Sat, Apr 11, 2009 at 08:52:31AM -0400, Robert Haas wrote:
On Sat, Apr 11, 2009 at 5:06 AM, Grzegorz Jaskiewicz
<gj@pointblue.com.pl> wrote:On 11 Apr 2009, at 08:01, Hitoshi Harada wrote:
2009/4/11 David Fetter <david@fetter.org>:
On Sat, Apr 11, 2009 at 03:48:33PM +0900, Hitoshi Harada wrote:
Yeah, but all the window functions are stored in pg_proc.
So are aggregate functions, and they have their own separate way of
being addressed in psql :)Aggregate functions are stored in pg_aggregate. And they are
combinations of plain function which is stored in pg_proc.Maybe trigger functions should be displayed separately too than ?
We're up to at least four different categories of functions that
people think might require special treatment: window, trigger, I/O,
everything else.The current psql has \da and \df, the latter of which now includes I/O
functions. I contend that windowing functions are different enough
that they require a separate category.You do bring up an interesting point for 8.5 or later, which would be
a complete redo of psql from the ground up. Let's hash out a proposal
for that in a separate thread once we get 8.4 out the door :)
:-)
I'm sure consensus will be reached quickly and painlessly. :-)
...Robert
All,
Having an extra column in \df for "Windowing" was rejected out of hand.
Why?
\df (let alone \df+) already displays too many wide columns to fit in
any standard terminal window. You're pretty much forced to use \x
regardless. What's one more column?
And has it occurred to anyone that a pg_functions view is *way* overdue?
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
Having an extra column in \df for "Windowing" was rejected out of hand.
Why?
I'd definitely support adding it to \df+. Basic \df might be a harder
sell, because it still does mostly fit in 80 columns now, but would
certainly no longer do so with another column.
And has it occurred to anyone that a pg_functions view is *way* overdue?
Too late for 8.4 I'm afraid, but we could talk about it for 8.5. What
have you got in mind that would be different from \df?
regards, tom lane
Tom,
It fits into 80 columns if you don't have any functions with 11
parameters. ;-)
Actually, I'm thinking the new column ought to be called "type". That
way, it could be "window" or "trigger" or "internal", and more types
later if we develop any (like "MED").
Too late for 8.4 I'm afraid, but we could talk about it for 8.5. What
have you got in mind that would be different from \df?
Well, \df+. It would have the same columns. But you'd be able to query
just the column you want, and just the types you want.
SELECT name, parameters FROM pg_functions WHERE function_type = "window".
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
Tom,
It fits into 80 columns if you don't have any functions with 11
parameters. ;-)
Well, yeah, but in typical cases I think it fits. A look at the current
regression database shows all but 6 of 117 functions fitting. With
another ten characters eaten by a new column, a lot more of them would
wrap.
Actually, I'm thinking the new column ought to be called "type".
Yes, that's what I had in mind too.
regards, tom lane
On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
Tom, It fits into 80 columns if you don't have any functions with
11 parameters. ;-)Well, yeah, but in typical cases I think it fits. A look at the
current regression database shows all but 6 of 117 functions
fitting. With another ten characters eaten by a new column, a lot
more of them would wrap.Actually, I'm thinking the new column ought to be called "type".
Yes, that's what I had in mind too.
Excellent idea. I just plain don't believe that there's anything
process-critical and automated that depends on \da, although we could
have it rewritten as an alias for convenience.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote:
On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
Tom, It fits into 80 columns if you don't have any functions with
11 parameters. ;-)Well, yeah, but in typical cases I think it fits. A look at the
current regression database shows all but 6 of 117 functions
fitting. With another ten characters eaten by a new column, a lot
more of them would wrap.Actually, I'm thinking the new column ought to be called "type".
Yes, that's what I had in mind too.
Excellent idea. I just plain don't believe that there's anything
process-critical and automated that depends on \da, although we could
have it rewritten as an alias for convenience.
I assume the 'type' column will identify triggers, i/o functions
(cstring), window functions, and maybe aggregates too; this solves
several problems at once.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
On Sat, Apr 11, 2009 at 2:47 PM, Josh Berkus <josh@agliodbs.com> wrote:
All,
Having an extra column in \df for "Windowing" was rejected out of hand.
Why?
I have no idea. I suggested it and the only one I remember speaking
against it was Tom.
\df (let alone \df+) already displays too many wide columns to fit in any
standard terminal window. You're pretty much forced to use \x regardless.
What's one more column?And has it occurred to anyone that a pg_functions view is *way* overdue?
For what purpose?
...Robert
On Sat, Apr 11, 2009 at 03:34:31PM -0400, Bruce Momjian wrote:
David Fetter wrote:
On Sat, Apr 11, 2009 at 03:12:39PM -0400, Tom Lane wrote:
Josh Berkus <josh@agliodbs.com> writes:
Tom, It fits into 80 columns if you don't have any functions with
11 parameters. ;-)Well, yeah, but in typical cases I think it fits. A look at the
current regression database shows all but 6 of 117 functions
fitting. With another ten characters eaten by a new column, a lot
more of them would wrap.Actually, I'm thinking the new column ought to be called "type".
Yes, that's what I had in mind too.
Excellent idea. I just plain don't believe that there's anything
process-critical and automated that depends on \da, although we could
have it rewritten as an alias for convenience.I assume the 'type' column will identify triggers, i/o functions
(cstring), window functions, and maybe aggregates too; this solves
several problems at once.
Lemme whip up a patch :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Bruce Momjian <bruce@momjian.us> writes:
I assume the 'type' column will identify triggers, i/o functions
(cstring), window functions, and maybe aggregates too; this solves
several problems at once.
+1 for all except i/o functions. The cstring check for that was always
flat-out wrong, and getting it right is far more expensive than it's
worth --- AFAICS you'd have to grovel through all entries in pg_type.
But aggregates are only relevant if we decide to start showing
aggregates in \df --- is there consensus for that?
regards, tom lane
On Sat, Apr 11, 2009 at 04:30:02PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
I assume the 'type' column will identify triggers, i/o functions
(cstring), window functions, and maybe aggregates too; this solves
several problems at once.+1 for all except i/o functions. The cstring check for that was always
flat-out wrong, and getting it right is far more expensive than it's
worth --- AFAICS you'd have to grovel through all entries in pg_type.
I'll leave it out :)
But aggregates are only relevant if we decide to start showing
aggregates in \df --- is there consensus for that?
I'd throw 'em in.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, Apr 11, 2009 at 01:43:35PM -0700, David Fetter wrote:
On Sat, Apr 11, 2009 at 04:30:02PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
I assume the 'type' column will identify triggers, i/o functions
(cstring), window functions, and maybe aggregates too; this solves
several problems at once.+1 for all except i/o functions. The cstring check for that was always
flat-out wrong, and getting it right is far more expensive than it's
worth --- AFAICS you'd have to grovel through all entries in pg_type.I'll leave it out :)
But aggregates are only relevant if we decide to start showing
aggregates in \df --- is there consensus for that?I'd throw 'em in.
It occurs to me that we ought to allow for a possibility that a
function can be more than one special case. For example, sum() is
both an aggregate and a windowing function, while rank() is only a
windowing function.
Working on a patch that allows a concise description of both.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Robert,
For what purpose?
See above, in thread.
--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com
David Fetter <david@fetter.org> writes:
It occurs to me that we ought to allow for a possibility that a
function can be more than one special case. For example, sum() is
both an aggregate and a windowing function, while rank() is only a
windowing function.
If it makes the display even one character wider, -1 from me.
That's a purely hypothetical problem for the classification
we're discussing. (No, I don't feel a need for \df to remind
me every time that aggregates can also be window functions.)
regards, tom lane
On Sat, Apr 11, 2009 at 07:35:54PM -0400, Tom Lane wrote:
David Fetter <david@fetter.org> writes:
It occurs to me that we ought to allow for a possibility that a
function can be more than one special case. For example, sum() is
both an aggregate and a windowing function, while rank() is only a
windowing function.If it makes the display even one character wider, -1 from me.
That's a purely hypothetical problem for the classification
we're discussing. (No, I don't feel a need for \df to remind
me every time that aggregates can also be window functions.)
OK :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Hi,
Reacting somewhat late, but maybe not too late?
Le 11 avr. 09 à 17:13, Tom Lane a écrit :
My own take on it is that actually I'd prefer one command for all of
these. If I say "\df sum" it would be good if the output included the
sum() aggregates; the reason being that I might be wondering if I can
create a plain function named sum. If I have to check not only \df
and
\da but also \dw for conflicts, that's going to be a real PITA.
[...]
If we were designing in a green field I think you could make a real
strong case for a single \df command with an output column "type"
having
the alternatives regular, aggregate, window, and maybe trigger.
It seems this proposal got a consensus vote, and I'd like to add to
it: what about having specialized \df views, per type, with an
additional qualifier:
\dfa list aggregate functions
\dfw list window functions (and aggregates?)
\dft list trigger functions
... you get the idea
Nothing fundamentally new, just some more convenience to support for
users wanting to list functions of a given known type: it allows not
to have to \set ECHO_HIDDEN, \df, copy/paste/adapt where/launch again.
Regards,
--
dim