Re: [HACKERS] window function v03 against HEAD
On Thu, Jul 31, 2008 at 02:46:29PM +0900, Hitoshi Harada wrote: 2008/7/31 David Fetter [EMAIL PROTECTED]: On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: 2008/7/29 David Fetter [EMAIL PROTECTED]: On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: I happily announce that the first design of window function was finished and the patch against HEAD is released online. See http://umitanuki.net/pgsql/wfv03/design.html I've put up a git repository at http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary Thanks a lot. I have tried to get clone from the URL but it didn't work. $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ Getting alternates list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting pack list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 which contains c13ba377a6e58a05b5d9e39e36674af42126d48d here it stops and tells nothing. This occurs on both Linux and Windows clients. How long does it hang for? Sorry, finally I got it. It took about an hour... Sorry about that. Apparently, at least the way things are set up, there's a *lot* of history you can rewind. Further changes should move pretty quickly :) 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] \ef function in psql
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements \ef function in psql based on (1).
Comments appreciated.
-- ams
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 1ba20b0..ccf0d68 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -551,6 +551,7 @@ extern Datum pg_get_expr(PG_FUNCTION_ARGS);
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0d28310..71e601a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -137,6 +137,7 @@ static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
bool print_table_args);
+static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
int prettyFlags);
static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -1398,6 +1399,137 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
/*
+ * pg_get_functiondef
+ * Returns the CREATE OR REPLACE FUNCTION ... statement for the
+ * specified function.
+ */
+Datum
+pg_get_functiondef(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ StringInfoData buf;
+ StringInfoData dq;
+ HeapTuple proctup;
+ HeapTuple langtup;
+ Form_pg_proc proc;
+ Form_pg_language lang;
+ bool isnull;
+ Datum tmp;
+ const char *prosrc;
+ const char *name;
+ const char *nsp;
+ float4 cost;
+ int n;
+
+ initStringInfo(buf);
+
+ proctup = SearchSysCache(PROCOID, ObjectIdGetDatum(funcid), 0, 0, 0);
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, cache lookup failed for function %u, funcid);
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+
+ langtup = SearchSysCache(LANGOID, ObjectIdGetDatum(proc-prolang), 0, 0, 0);
+ if (!HeapTupleIsValid(langtup))
+ elog(ERROR, cache lookup failed for language %u, proc-prolang);
+ lang = (Form_pg_language) GETSTRUCT(langtup);
+
+ name = NameStr(proc-proname);
+ nsp = get_namespace_name(proc-pronamespace);
+ appendStringInfo(buf, CREATE OR REPLACE FUNCTION %s(,
+ quote_qualified_identifier(nsp, name));
+ (void) print_function_arguments(buf, proctup, false);
+ appendStringInfoString(buf, )\n RETURNS );
+ print_function_rettype(buf, proctup);
+ appendStringInfo(buf, \n LANGUAGE '%s'\n, NameStr(lang-lanname));
+
+ n = 1;
+
+ switch (proc-provolatile) {
+ case PROVOLATILE_IMMUTABLE:
+ appendStringInfoString(buf, IMMUTABLE);
+ break;
+ case PROVOLATILE_STABLE:
+ appendStringInfoString(buf, STABLE);
+ break;
+ case PROVOLATILE_VOLATILE:
+ default:
+ n--;
+ break;
+ }
+
+ if (proc-proisstrict)
+ {
+ n++;
+ appendStringInfoString(buf, STRICT);
+ }
+
+ if (proc-prosecdef)
+ {
+ n++;
+ appendStringInfoString(buf, SECURITY DEFINER);
+ }
+
+ cost = 100;
+ if (proc-prolang == INTERNALlanguageId ||
+ proc-prolang == ClanguageId)
+ cost = 1;
+
+ if (proc-procost != cost)
+ {
+ n++;
+ appendStringInfo(buf, COST %.0f, proc-procost);
+ }
+
+ if (proc-prorows != 0 proc-prorows != 1000)
+ {
+ n++;
+ appendStringInfo(buf, ROWS %.0f, proc-prorows);
+ }
+
+ if (n != 0)
+ appendStringInfoString(buf, \n);
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_proconfig, isnull);
+ if (!isnull)
+ {
+ int i;
+ ArrayType *a = DatumGetArrayTypeP(tmp);
+
+ for (i = 1; i = ARR_DIMS(a)[0]; i++)
+ {
+ Datum d;
+ bool isnull;
+
+ d = array_ref(a, 1, i, -1, -1, false, 'i', isnull);
+ if (!isnull)
+ {
+const char *s = TextDatumGetCString(d);
+appendStringInfo(buf, SET %s\n, s);
+ }
+ }
+ }
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosrc, isnull);
+ if (isnull)
+ elog(ERROR, null prosrc);
+ prosrc = TextDatumGetCString(tmp);
+
+ initStringInfo(dq);
+ appendStringInfoString(dq, $);
+ while (strstr(prosrc, dq.data) != NULL)
+ appendStringInfoString(dq, x);
+ appendStringInfoString(dq, $);
+
+ appendStringInfo(buf, AS %s\n%s\n%s;, dq.data, prosrc, dq.data);
+
+ ReleaseSysCache(langtup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/*
* pg_get_function_arguments
* Get a nicely-formatted list of arguments for a function.
* This is everything that would go between the parentheses in
@@ -1436,8 +1568,6 @@ pg_get_function_result(PG_FUNCTION_ARGS)
Oid funcid = PG_GETARG_OID(0);
StringInfoData buf;
HeapTuple proctup;
- Form_pg_proc procform;
- int ntabargs = 0;
initStringInfo(buf);
@@ -1446,32 +1576,46 @@
Re: [HACKERS] Type Categories for User-Defined Types
Hi, Le mercredi 30 juillet 2008, David E. Wheeler a écrit : On Jul 30, 2008, at 10:34, Tom Lane wrote: [ move preferred-type info into the system catalogs ] UUID and so on aren't considered part of the string category, and shouldn't be IMHO ... any type that has semantics significantly different from arbitrary string of text doesn't belong. Yes, that was essentially my point. arbitrary string of text types are probably fairly rare, since one can just use text or citext or varchar. I'm not following this thread closely (enough) but my prefix_range type (from the pgfoundry prefix module) certainly is a user defined (in C) arbitrary string of text type. The fact that it's user visible could be questionned, its usage is for GiST indexing prefix searches when the prefix is in the table, not in the query literal: SELECT * FROM prefixes WHERE prefix @ 'literal'; This query currently only profits from the GiST indexing if prefix column is of type prefix_range, I'd like to be able to index text and store prefix_range in the index, it's on the TODO list (and certainly is doable AFAIUI). I'm not sure this input is valuable for the topic, but would rather not miss the example in case it is :) The behavior that's hard-wired into parse_coerce.c at the moment is that only text, varchar, bpchar can be sources or targets of I/O conversions. While opening it up to citext sounds reasonable, I'm a lot less sure about domains. I still remember the sour taste of cancelling a domain usage for some type needs here after discovering you can't create CAST for domains. Would the categorising stuff help here? Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Should creating a new base type require superuser status?
On Wed, Jul 30, 2008 at 06:07:53PM -0400, Alvaro Herrera wrote: I do agree that creating base types should require a superuser though. It too seems dangerous just on principle, even if today there's no actual hole (that we already know of). I agree. -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] window function v03 against HEAD
2008/7/31 David Fetter [EMAIL PROTECTED]: On Thu, Jul 31, 2008 at 02:46:29PM +0900, Hitoshi Harada wrote: 2008/7/31 David Fetter [EMAIL PROTECTED]: On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote: 2008/7/29 David Fetter [EMAIL PROTECTED]: On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote: I happily announce that the first design of window function was finished and the patch against HEAD is released online. See http://umitanuki.net/pgsql/wfv03/design.html I've put up a git repository at http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary Thanks a lot. I have tried to get clone from the URL but it didn't work. $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git Initialized empty Git repository in /home/forcia/repo/window_functions/.git/ Getting alternates list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting pack list for http://git.postgresql.org/git/~davidfetter/window_functions/.git Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555 which contains c13ba377a6e58a05b5d9e39e36674af42126d48d here it stops and tells nothing. This occurs on both Linux and Windows clients. How long does it hang for? Sorry, finally I got it. It took about an hour... Sorry about that. Apparently, at least the way things are set up, there's a *lot* of history you can rewind. Further changes should move pretty quickly :) 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 Thankfully, I succeeded to learn about git and started to use it on my local. Then I read through the -hackers list about it but some points are still not clear. - Am I eligible to push git.postgresql.org/git/~davidfetter/window_functions? - Or to get qualified do I need to claim? - The repositories on git.postgresql.org need reviewing for new push/pull or is it free to push there? How is the developing flow? I just got some message when tried pushing: error: Cannot access URL http://git.postgresql.org/git/~davidfetter/window_functions/.git/, return code 22 error: failed to push some refs to 'http://git.postgresql.org/git/~davidfetter/window_functions/.git' If possible, I would like an account to push for me. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dumping datconfig
It seems the only way to dump stuff in pg_database.datconfig (ALTER DATABASE foo SET something=bar) is to do an unqualified pg_dumpall. I think this should *at least* be dumped with pg_dump when it's set to create the database, and I think a case could be made that it should *always* be dumped when a complete database is dumped by pg_dump (not restricted by schema or table or function or whatever). Comments? (I'll await the result of the discussion that's sure to happen before I write up a patch for it, but I'm willing to do said patch once agreement has been reached) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Plans for 8.4
Stephen Frost wrote: * Henry B. Hotz ([EMAIL PROTECTED]) wrote: I'm making no promises, but what would people think of a hostgss hba option? As described, sounds like a win to me. It'd be very nice to be able to just use GSSAPI encryption on the link. That, combined w/ Magnus' work on username/princ mappings, would really bring PostgreSQL up to date wrt GSSAPI support. Yeah, +1 on this feature, it would be quite useful. It'd really be great to have this support in the ODBC and JDBC drivers too.. I think in JDBC it might 'just work', I'm less sure about ODBC. ODBC will need hackery I think. They use libpq for authentication only, but have their own SSL code and such. I do think ODBC would be a fairly major point to it being a success, though, so it'd be good if a plan could be secured for it. But it's not a showstopper, of course. As a practical question- would you really need a seperate explicit pg_hba option for it? It'd be nice to be able to require it, if desired, but that strikes me as more sensible as an option to the 'gss' auth mechanism? Yeah, if we can get rid of that, that'd be good. The stuff I'm working on will allow us to have multiple parameters for each row in name/value pairs, so if we could use that, it'd be better. (I've been considering changing how host/hostssl work that way as well - by having a parameter similar to what we have on the client side with sslmode=...) A thought that I came across - is it even possible to use GSSAPI encryption *without* using GSSAPI authentication? If not, it really seems like it should belong more in the parameter part of the field. Since in that case it is also not possible to enable encryption *before* authentication, or is it? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Dumping datconfig
Magnus Hagander wrote: It seems the only way to dump stuff in pg_database.datconfig (ALTER DATABASE foo SET something=bar) is to do an unqualified pg_dumpall. I think this should *at least* be dumped with pg_dump when it's set to create the database, and I think a case could be made that it should *always* be dumped when a complete database is dumped by pg_dump (not restricted by schema or table or function or whatever). Comments? (I'll await the result of the discussion that's sure to happen before I write up a patch for it, but I'm willing to do said patch once agreement has been reached) Hm. Ok, it seems I suck, and this discussion happened just over a month ago at http://archives.postgresql.org/pgsql-hackers/2008-06/msg01035.php. I agree with Richard that the behavior is not very nice though :-) And I think http://archives.postgresql.org/pgsql-hackers/2008-06/msg01142.php would be the simplest way to fix it. If there are no further comments, I'll crawl back under and try to work up a complete proposal incl. code :-) /Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS][PATCHES] odd output in restore mode
Martin Zaun wrote: Heikki Linnakangas wrote: Andrew Dunstan wrote: Greg Smith wrote: On Wed, 23 Jul 2008, Kevin Grittner wrote: I've been working on an improved archive_command shell script that I expect to submit for comments and potential inclusion in the documentation as a better base for other people to build on. This is one of the options for how it can operate. It would be painful but not impossible to convert a subset of that script to run under Windows as well, at least enough to cover this particular issue. A Perl script using the (standard) File::Copy module along with the builtin function rename() should be moderately portable. It would to be nice not to have to maintain two scripts. It's also not very nice to require a Perl installation on Windows, just for a replacement of Copy. Would a simple .bat script work? With these avenues to be explored, can the pg_standby patch on the CommitFest wiki be moved to the Returned with Feedback section? Yes, I think we can conclude that we don't want this patch as it is. Instead, we want a documentation patch that describes the problem, mentioning that GNU cp is safe, or you can use the copy+rename trick. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS][PATCHES] odd output in restore mode
Heikki Linnakangas [EMAIL PROTECTED] writes: Martin Zaun wrote: With these avenues to be explored, can the pg_standby patch on the CommitFest wiki be moved to the Returned with Feedback section? Yes, I think we can conclude that we don't want this patch as it is. Instead, we want a documentation patch that describes the problem, mentioning that GNU cp is safe, or you can use the copy+rename trick. Right, after which we remove the presently hacked-in delay. I've updated the commitfest page accordingly. 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
Re: [HACKERS] window function v03 against HEAD
On Thu, Jul 31, 2008 at 11:00:15PM +0900, Hitoshi Harada wrote: 2008/7/31 David Fetter [EMAIL PROTECTED]: Sorry about that. Apparently, at least the way things are set up, there's a *lot* of history you can rewind. Further changes should move pretty quickly :) Thankfully, I succeeded to learn about git and started to use it on my local. Then I read through the -hackers list about it but some points are still not clear. I guess we'll all learn at once :) - Am I eligible to push git.postgresql.org/git/~davidfetter/window_functions? You will be as soon as I can arrange it. I may move or re-create that repository. Please send me a username and an RSA public key so I can give you git-shell access. - Or to get qualified do I need to claim? - The repositories on git.postgresql.org need reviewing for new push/pull or is it free to push there? How is the developing flow? Working on that :) I just got some message when tried pushing: error: Cannot access URL http://git.postgresql.org/git/~davidfetter/window_functions/.git/, return code 22 error: failed to push some refs to 'http://git.postgresql.org/git/~davidfetter/window_functions/.git' If possible, I would like an account to push for me. Right. I will start that this afternoon, PDT. 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_regress inputdir
Jorgen Austvik - Sun Norway wrote: Do we also agree that if you set --inputdir to anything other than the default, pg_regress will not work (will write a file to one folder, and try to read the same file from another)? And if we agree above - should we make setting --inputdir work (read and write from/to same directory), remove the --inputdir parameter (since setting it to anything different from default value doesn't work), or keep it there (to confuse people)? I think the problem here is that you have to set --outputdir too. $ LC_ALL=C /pgsql/build/00head/src/test/regress/pg_regress --inputdir=/pgsql/source/00head/src/test/regress --outputdir=/pgsql/build/00head/src/test/regress timetz (using postmaster on Unix socket, port 55432) == dropping database regression == DROP DATABASE == creating database regression == CREATE DATABASE ALTER DATABASE == running regression test queries== test timetz ... ok = All 1 tests passed. = Note that this is a VPATH build, so the input and output dirs are different. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] window function v03 against HEAD
2008/8/1 David Fetter [EMAIL PROTECTED]: You will be as soon as I can arrange it. I may move or re-create that repository. Please send me a username and an RSA public key so I can give you git-shell access. Thank you for your consideration. But right now, I'm going to be off for three days or so. As soon as coming back, I'll send it to you. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_regress inputdir
Alvaro Herrera wrote: Jorgen Austvik - Sun Norway wrote: Do we also agree that if you set --inputdir to anything other than the default, pg_regress will not work (will write a file to one folder, and try to read the same file from another)? And if we agree above - should we make setting --inputdir work (read and write from/to same directory), remove the --inputdir parameter (since setting it to anything different from default value doesn't work), or keep it there (to confuse people)? I think the problem here is that you have to set --outputdir too. Huh, scratch that, I chose a bad test. create_function_2 obviously fails as you say: $ LC_ALL=C /pgsql/build/00head/src/test/regress/pg_regress --inputdir=/pgsql/source/00head/src/test/regress --srcdir=/pgsql/source/00head/src/test/regress/ create_function_2 (using postmaster on Unix socket, port 55432) == dropping database regression == DROP DATABASE == creating database regression == CREATE DATABASE ALTER DATABASE == running regression test queries== test create_function_2... /bin/sh: /pgsql/source/00head/src/test/regress/sql/create_function_2.sql: No such file or directory diff: /pgsql/source/00head/src/test/regress/expected/create_function_2.out: No such file or directory diff: ./results/create_function_2.out: No such file or directory diff command failed with status 512: diff -w /pgsql/source/00head/src/test/regress/expected/create_function_2.out ./results/create_function_2.out ./results/create_function_2.out.diff I'm not sure if the problem here is --inputdir or --srcdir, or the fact that we fail to provide a --builddir switch. In my opinion, the need for running tests outside the test dir is not very strong (or we would have heard complaints before), and thus the solution is to remove --inputdir and --outputdir. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fixing DISTINCT ON for duplicate keys
I looked into this trouble report: http://archives.postgresql.org/pgsql-sql/2008-07/msg00123.php The problem is that by the time we get to transformDistinctClause(), any duplicate entries in the ORDER BY list have been eliminated (see addTargetToSortList). But transformDistinctClause expects a one-for-one match of the two lists, and so it complains. Clearly, duplicate DISTINCT ON items are just as redundant as duplicate ORDER BY items are, and so it seems that suppressing them is a reasonable thing to do. But I'm thinking that as long as we're touching this old code, there are some other things that should be fixed: * There's not really any semantic significance to the ordering of the DISTINCT ON list anyway, so it would be reasonable to rearrange the ordering of the list to match the ORDER BY list, rather than making the user do it. * It's really bletcherous that the code physically modifies the user-given ORDER BY. This damage is visible in stored rules --- they don't come out the same way you wrote them. While I don't mind the idea of dropping redundant entries, adding ORDER BY entries that the user never wrote seems bogus. It overconstrains the query, in a way that doesn't matter given our current implementation but could matter in the future. What I am thinking we could do about the latter is modify the querytree semantics a bit. Instead of insisting that the transformed distinctClause be equal to a prefix of the sortClause, allow either one to be a prefix of the other. Then the planner simply takes the longer one as its internal sort-order target. With this rule, the sortClause stays as what the user wrote (less any duplicate keys). The parser is required to remove any duplicate keys from the distinctClause and rearrange it if needed so that it has a common prefix with the sortClause (or throw error if this is not possible). This would be invisible to the user in plain SELECT DISTINCT, and in SELECT DISTINCT ON would mean that the list is dumped in a canonical order that matches ORDER BY, but isn't changed in any semantic way. Now this is probably too big a change to be prudent to back-patch. Is it worth coming up with a second patch that just tries to get transformDistinctClause to remove duplicates? Since the problem has existed for a very long time (at least back to 7.0 according to my testing) with no prior reports, it doesn't seem very important to fix. I'm a bit worried about putting a patch into only the back branches --- it would go out with little testing and so the odds of introducing a fresh problem seem uncomfortably high compared to the benefit. Comments? 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
Re: [HACKERS] Type Categories for User-Defined Types
On Jul 30, 2008, at 13:10, Tom Lane wrote: Yes, that was essentially my point. arbitrary string of text types are probably fairly rare, since one can just use text or citext or varchar. Good point --- so new members of STRING category aren't going to be that common, except for domains which apparently aren't bothering people anyway. I'll go ahead and make the change. (I think it's just a trivial change in find_coercion_pathway, and everything else should Just Work. If it turns out not to be trivial maybe we should reconsider.) Wow. Really nice, Tom. Thanks! The attached patch has all the tests I added to my svn version against 8.3, and for which I had to write 60 additional cast functions. With your changes, I had to add only two more: CREATE OR REPLACE FUNCTION citext(boolean) RETURNS citext AS 'booltext' LANGUAGE 'internal' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION citext(inet) RETURNS citext AS 'SELECT text( $1 )::citext' LANGUAGE SQL IMMUTABLE STRICT; So the I/O casts are working beautifully. This is true even for ENUMs, which I couldn't get to magically cast with a function in 8.3. Thank you! If you don't want so many cast tests, I can remove all but a few of them. I wrote them for all the core user-visible types I could identify so that I could see how they behaved with text and then make citext work the same way. Such is not as important in HEAD, thanks to the I/O casting, so let me know if you want me to cut down on the number of tests (I was particularly uncertain about the xml type, since --with-libxml won't always be true, I expect). The other change in this patch is the addition of functions to make various string-comparison functions behave case-insensitively. I've started with all those that I was aware of from the previous documentation, and I've made them behave case-insensitively by writing SQL functions to hack it in. Ideally these would be done in C, but that started to get beyond my abilities. Suggestions welcome. The only other thing I wanted to look at doing with citext was to look for any other string-comparison functions I might have missed and do the same for them. Otherwise, I think we're golden. Many thanks, David citext_casting.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] compilig libpq with borland 5.5
Hi everybody Iam traying to build libpq.lib and libpq.dll library using Borland c++ 5.5 and i got these error: Error libpq.rc 1 11: Cannot open file: winver.h I opened the libpq.rc file and i saw the reference to that file #include winver.h I cheched out the path and the existence of the file and everything is all right. I actually find the file in the desired location. I don't know what to do to carry on my work. Can somebody help me out please?. Thanks in advance. Claudio Lezcano
Re: [HACKERS] Fixing DISTINCT ON for duplicate keys
On Thu, Jul 31, 2008 at 01:38:37PM -0400, Tom Lane wrote: I looked into this trouble report: http://archives.postgresql.org/pgsql-sql/2008-07/msg00123.php Comments? It seems to me that DISTINCT ON is just a special case of the more general windowing functions http://archives.postgresql.org/pgsql-hackers/2008-07/msg01277.php Harada-san has been working on. Could these use the same machinery? 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Robert Lor wrote: Hi, What I suggest might be a reasonable compromise is to copy needed typedefs directly into the probes.d file: Implemented this suggestion. There are some weirdness with the OS X compiler causing some of the probe declarations not to compile (see comments in probe.d). The compiler spits out some warnings because the types don't show up in the function prototype in probes.h, but the probes work okay. I think we can safely ignore the warnings. These make sense, because they are already typedef's in our code: +typedef unsigned int LocalTransactionId; +typedef int LWLockId; +typedef int LWLockMode; +typedef int LOCKMODE; +typedef unsigned int BlockNumber; +typedef unsigned int Oid; But I don't see a reason to define the rest: +typedef unsigned int locktag_field2; +typedef const char * query_string; +typedef int sortType; +typedef int trueFalse; +typedef int nkeys; +typedef int workMem; +typedef int randomAccess; +typedef unsigned long LogicalTapeSetPtr; +typedef long spaceUsed; +typedef int isLocalBuf; +typedef int found; +typedef int flags; +typedef int num_to_write; +typedef int num_written; +typedef int NBuffers; +typedef int buf_id; I think you should add a #define Size, perhaps #define bool, and use those where applicable, and the plain types (int, long, etc) in the rest. + /* The following probe declarations cause compilation errors + * on Mac OS X but not on Solaris. Need further investigation. + * probe lock__wait__start(locktag_field2, LOCKMODE); + * probe lock__wait__done(locktag_field2, LOCKMODE); + */ + probe lock__wait__start(unsigned int, int); + probe lock__wait__done(unsigned int, int); For example I think this should look like probe lock__wait__start(unsigned int, LOCKMODE); That Mac OS X problem merits some extra investigation, I think. Other than this, I think this patch can be committed. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] window function v03 against HEAD
On Fri, Aug 01, 2008 at 02:13:52AM +0900, Hitoshi Harada wrote: 2008/8/1 David Fetter [EMAIL PROTECTED]: You will be as soon as I can arrange it. I may move or re-create that repository. Please send me a username and an RSA public key so I can give you git-shell access. Thank you for your consideration. But right now, I'm going to be off for three days or so. As soon as coming back, I'll send it to you. Enjoy your days off :) 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating Universal OSX binaries
Bruce Momjian [EMAIL PROTECTED] writes: Do we know this trick for creating Universal (four-architecture) binaries for OSX? This seems largely irrelevant to CVS HEAD: http://archives.postgresql.org/pgsql-hackers/2008-07/msg00884.php The proposed trick doesn't actually work in any case, since he didn't deal with the pg_config.h problem. 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
Re: [HACKERS] Plans for 8.4
On Jul 31, 2008, at 7:58 AM, Magnus Hagander wrote: Stephen Frost wrote: * Henry B. Hotz ([EMAIL PROTECTED]) wrote: I'm making no promises, but what would people think of a hostgss hba option? As described, sounds like a win to me. It'd be very nice to be able to just use GSSAPI encryption on the link. That, combined w/ Magnus' work on username/princ mappings, would really bring PostgreSQL up to date wrt GSSAPI support. Yeah, +1 on this feature, it would be quite useful. It'd really be great to have this support in the ODBC and JDBC drivers too.. I think in JDBC it might 'just work', I'm less sure about ODBC. ODBC will need hackery I think. They use libpq for authentication only, but have their own SSL code and such. I do think ODBC would be a fairly major point to it being a success, though, so it'd be good if a plan could be secured for it. But it's not a showstopper, of course. I don't know enough about ODBC. If ODBC does SSL independently of PG then it requires thought by someone who understands ODBC. As a practical question- would you really need a seperate explicit pg_hba option for it? It'd be nice to be able to require it, if desired, but that strikes me as more sensible as an option to the 'gss' auth mechanism? Yeah, if we can get rid of that, that'd be good. The stuff I'm working on will allow us to have multiple parameters for each row in name/ value pairs, so if we could use that, it'd be better. (I've been considering changing how host/hostssl work that way as well - by having a parameter similar to what we have on the client side with sslmode=...) A thought that I came across - is it even possible to use GSSAPI encryption *without* using GSSAPI authentication? If not, it really seems like it should belong more in the parameter part of the field. Since in that case it is also not possible to enable encryption *before* authentication, or is it? You're on the right track. My problem isn't the hba file parsing at all. My problem is the interaction between the buffering logic and the encrypted I/O routines. The technical issue is that to make a GSSAPI security layer independent of SSL you need to invent a whole new buffering layer. That's a lot of work, and it only buys you the ability to do both SSL and GSSAPI at the same time. That doesn't seem worth it. The code being affected is what's currently configured in column 1 of hba. The ability to use the new capability requires that SSL *NOT* be configured in column 1 for the relevant client addresses. In short, no, it doesn't make sense to make it an option to the gss authentication method, even though it requires it. If we make it an option to the gss authentication method it would still need to act like it was specified in column 1, which would be confusing. GSSAPI security layers are negotiated after the authentication (or at least after the start of authentication). There are GSSAPI status flags that indicate if the security layer is available yet. The GSSAPI security layer code would check those flags and gss_wrap() or not accordingly. (-: There's a flush() or two from my original patch that will need to be added back in, otherwise we'll encrypt a message that tells the other end how to decrypt messages. Not a big deal. ;-) -- The opinions expressed in this message are mine, not those of Caltech, JPL, NASA, or the US Government. [EMAIL PROTECTED], or [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fixing DISTINCT ON for duplicate keys
David Fetter [EMAIL PROTECTED] writes: It seems to me that DISTINCT ON is just a special case of the more general windowing functions http://archives.postgresql.org/pgsql-hackers/2008-07/msg01277.php Harada-san has been working on. Could these use the same machinery? Perhaps at some point we could deprecate DISTINCT ON in favor of using windowing functions, but I'd not want to weigh down the windowing work with a mandate that it be bug-compatible with DISTINCT ON. 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
Re: [HACKERS] Should creating a new base type require superuser status?
On Wed, 30 Jul 2008, Alvaro Herrera wrote: I think being able to return cstring from a user defined function is quite dangerous already. I doubt we would ever give that capability to non-superusers. I do agree that creating base types should require a superuser though. It too seems dangerous just on principle, even if today there's no actual hole (that we already know of). pl/java already allows non-superusers to create functions returning cstring and base types built off of these functions. It seems safe to me if pl/java is doing the construction of cstring from a user provided java.lang.String. http://wiki.tada.se/display/pljava/Creating+a+Scalar+UDT+in+Java Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fixing DISTINCT ON for duplicate keys
On Thu, Jul 31, 2008 at 03:10:35PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: It seems to me that DISTINCT ON is just a special case of the more general windowing functions http://archives.postgresql.org/pgsql-hackers/2008-07/msg01277.php Harada-san has been working on. Could these use the same machinery? Perhaps at some point we could deprecate DISTINCT ON in favor of using windowing functions, but I'd not want to weigh down the windowing work with a mandate that it be bug-compatible with DISTINCT ON. Good point. I was just thinking of going the other way, namely fixing any misbehaviors of DISTINCT ON via machinery to be used by the windowing functions. :) 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Alvaro Herrera wrote:
But I don't see a reason to define the rest:
+typedef unsigned int locktag_field2;
+typedef const char * query_string;
+typedef int sortType;
+typedef int trueFalse;
+typedef int nkeys;
+typedef int workMem;
+typedef int randomAccess;
+typedef unsigned long LogicalTapeSetPtr;
+typedef long spaceUsed;
+typedef int isLocalBuf;
+typedef int found;
+typedef int flags;
+typedef int num_to_write;
+typedef int num_written;
+typedef int NBuffers;
+typedef int buf_id;
I think you should add a #define Size, perhaps #define bool, and use
those where applicable, and the plain types (int, long, etc) in the rest.
Fixed. Patch attached.
That Mac OS X problem merits some extra investigation, I think.
I'm investigating this one and will find the root cause, but I don't
think it should hold back this patch.
Other than this, I think this patch can be committed.
I'd appreciate if it can be committed today.
Alvaro, thanks a bunch for the feedback!
--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql
Index: src/backend/access/transam/clog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/clog.c,v
retrieving revision 1.46
diff -u -3 -p -r1.46 clog.c
--- src/backend/access/transam/clog.c 1 Jan 2008 19:45:46 - 1.46
+++ src/backend/access/transam/clog.c 31 Jul 2008 20:09:15 -
@@ -36,6 +36,7 @@
#include access/slru.h
#include access/transam.h
#include postmaster/bgwriter.h
+#include pg_trace.h
/*
* Defines for CLOG page sizes. A page is the same BLCKSZ as is used
@@ -313,7 +314,9 @@ void
ShutdownCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(false);
SimpleLruFlush(ClogCtl, false);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(false);
}
/*
@@ -323,7 +326,9 @@ void
CheckPointCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(true);
SimpleLruFlush(ClogCtl, true);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/multixact.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/multixact.c,v
retrieving revision 1.27
diff -u -3 -p -r1.27 multixact.c
--- src/backend/access/transam/multixact.c 1 Jan 2008 19:45:46 -
1.27
+++ src/backend/access/transam/multixact.c 31 Jul 2008 20:09:16 -
@@ -57,6 +57,7 @@
#include storage/lmgr.h
#include utils/memutils.h
#include storage/procarray.h
+#include pg_trace.h
/*
@@ -1497,8 +1498,10 @@ void
ShutdownMultiXact(void)
{
/* Flush dirty MultiXact pages to disk */
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(false);
SimpleLruFlush(MultiXactOffsetCtl, false);
SimpleLruFlush(MultiXactMemberCtl, false);
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(false);
}
/*
@@ -1526,6 +1529,8 @@ MultiXactGetCheckptMulti(bool is_shutdow
void
CheckPointMultiXact(void)
{
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(true);
+
/* Flush dirty MultiXact pages to disk */
SimpleLruFlush(MultiXactOffsetCtl, true);
SimpleLruFlush(MultiXactMemberCtl, true);
@@ -1540,6 +1545,8 @@ CheckPointMultiXact(void)
*/
if (!InRecovery)
TruncateMultiXact();
+
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(true);
}
/*
Index: src/backend/access/transam/subtrans.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/subtrans.c,v
retrieving revision 1.22
diff -u -3 -p -r1.22 subtrans.c
--- src/backend/access/transam/subtrans.c 26 Mar 2008 18:48:59 -
1.22
+++ src/backend/access/transam/subtrans.c 31 Jul 2008 20:09:17 -
@@ -32,6 +32,7 @@
#include access/subtrans.h
#include access/transam.h
#include utils/snapmgr.h
+#include pg_trace.h
/*
@@ -265,7 +266,9 @@ ShutdownSUBTRANS(void)
* This is not actually necessary from a correctness point of view. We
do
* it merely as a debugging aid.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(false);
SimpleLruFlush(SubTransCtl, false);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(false);
}
/*
@@ -281,7 +284,9 @@ CheckPointSUBTRANS(void)
* it merely to improve the odds that writing of dirty pages is done by
* the checkpoint process and not by backends.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(true);
SimpleLruFlush(SubTransCtl, true);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/twophase.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.43
diff -u -3
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Robert Lor wrote: That Mac OS X problem merits some extra investigation, I think. I'm investigating this one and will find the root cause, but I don't think it should hold back this patch. Other than this, I think this patch can be committed. I'd appreciate if it can be committed today. I'm looking at it. FWIW I found that the machinery to compile on non-probes-enabled machines needs to be updated per the attached patch. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Index: src/backend/utils/Makefile === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/Makefile,v retrieving revision 1.27 diff -c -p -r1.27 Makefile *** src/backend/utils/Makefile 17 Mar 2008 19:44:41 - 1.27 --- src/backend/utils/Makefile 31 Jul 2008 20:40:34 - *** $(SUBDIRS:%=%-recursive): fmgroids.h *** 20,25 --- 20,29 fmgroids.h fmgrtab.c: Gen_fmgrtab.sh $(top_srcdir)/src/include/catalog/pg_proc.h AWK='$(AWK)' $(SHELL) $ $(top_srcdir)/src/include/catalog/pg_proc.h + ifneq ($(enable_dtrace), yes) + probes.h: Gen_dummy_probes.sed + endif + probes.h: probes.d ifeq ($(enable_dtrace), yes) $(DTRACE) -h -s $ -o [EMAIL PROTECTED] Index: src/backend/utils/Gen_dummy_probes.sed === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/Gen_dummy_probes.sed,v retrieving revision 1.1 diff -c -p -r1.1 Gen_dummy_probes.sed *** src/backend/utils/Gen_dummy_probes.sed 17 Mar 2008 19:44:41 - 1.1 --- src/backend/utils/Gen_dummy_probes.sed 31 Jul 2008 20:40:41 - *** *** 6,16 # $PostgreSQL: pgsql/src/backend/utils/Gen_dummy_probes.sed,v 1.1 2008-03-17 19:44:41 petere Exp $ #- ! /^probe /!d ! s/^probe \([^(]*\)\(.*\);/\1\2/ s/__/_/g y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/ s/^/#define TRACE_POSTGRESQL_/ ! s/(INT, INT)/(INT1, INT2)/ P s/(.*$/_ENABLED() (0)/ --- 6,21 # $PostgreSQL: pgsql/src/backend/utils/Gen_dummy_probes.sed,v 1.1 2008-03-17 19:44:41 petere Exp $ #- ! /^[ ]*probe /!d ! s/^[ ]*probe \([^(]*\)\(.*\);/\1\2/ s/__/_/g y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/ s/^/#define TRACE_POSTGRESQL_/ ! s/([^,)]\+)/(INT1)/ ! s/([^,)]\+, [^,)]\+)/(INT1, INT2)/ ! s/([^,)]\+, [^,)]\+, [^,)]\+)/(INT1, INT2, INT3)/ ! s/([^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+)/(INT1, INT2, INT3, INT4)/ ! s/([^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+)/(INT1, INT2, INT3, INT4, INT5)/ ! s/([^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+, [^,)]\+)/(INT1, INT2, INT3, INT4, INT5, INT6)/ P s/(.*$/_ENABLED() (0)/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Robert Lor wrote: Tom Lane wrote: * The probes that pass buffer tag elements are already broken by the pending relation forks patch: there is soon going to be another field in buffer tags. Perhaps it'd be feasible to pass the buffer tag as a single probe argument to make that a bit more future-proof? I'm not sure if that would complicate the use of the probe so much as to be counterproductive. Took out the buffer tag argument for now. Will figure out how to best solve this after this relation forks patch is committed. I was checking the DTrace docs for other reasons and I came across this, which maybe can be useful here: http://docs.sun.com/app/docs/doc/817-6223/chp-xlate?a=view -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should creating a new base type require superuser status?
Kris Jurka [EMAIL PROTECTED] writes: On Wed, 30 Jul 2008, Alvaro Herrera wrote: I do agree that creating base types should require a superuser though. It too seems dangerous just on principle, even if today there's no actual hole (that we already know of). pl/java already allows non-superusers to create functions returning cstring and base types built off of these functions. So in other words, if pl/java is installed we have a security hole a mile wide. 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
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Here's what I have. Please confirm that this compiles for you.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/access/transam/clog.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/clog.c,v
retrieving revision 1.46
diff -c -p -r1.46 clog.c
*** src/backend/access/transam/clog.c 1 Jan 2008 19:45:46 - 1.46
--- src/backend/access/transam/clog.c 31 Jul 2008 22:01:49 -
***
*** 35,40
--- 35,41
#include access/clog.h
#include access/slru.h
#include access/transam.h
+ #include pg_trace.h
#include postmaster/bgwriter.h
/*
*** void
*** 313,319
--- 314,322
ShutdownCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(false);
SimpleLruFlush(ClogCtl, false);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(false);
}
/*
*** void
*** 323,329
--- 326,334
CheckPointCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(true);
SimpleLruFlush(ClogCtl, true);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/multixact.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/multixact.c,v
retrieving revision 1.27
diff -c -p -r1.27 multixact.c
*** src/backend/access/transam/multixact.c 1 Jan 2008 19:45:46 - 1.27
--- src/backend/access/transam/multixact.c 31 Jul 2008 22:02:22 -
***
*** 53,62
#include access/transam.h
#include access/xact.h
#include miscadmin.h
#include storage/backendid.h
#include storage/lmgr.h
- #include utils/memutils.h
#include storage/procarray.h
/*
--- 53,63
#include access/transam.h
#include access/xact.h
#include miscadmin.h
+ #include pg_trace.h
#include storage/backendid.h
#include storage/lmgr.h
#include storage/procarray.h
+ #include utils/memutils.h
/*
*** void
*** 1497,1504
--- 1498,1507
ShutdownMultiXact(void)
{
/* Flush dirty MultiXact pages to disk */
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(false);
SimpleLruFlush(MultiXactOffsetCtl, false);
SimpleLruFlush(MultiXactMemberCtl, false);
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(false);
}
/*
*** MultiXactGetCheckptMulti(bool is_shutdow
*** 1526,1531
--- 1529,1536
void
CheckPointMultiXact(void)
{
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(true);
+
/* Flush dirty MultiXact pages to disk */
SimpleLruFlush(MultiXactOffsetCtl, true);
SimpleLruFlush(MultiXactMemberCtl, true);
*** CheckPointMultiXact(void)
*** 1540,1545
--- 1545,1552
*/
if (!InRecovery)
TruncateMultiXact();
+
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(true);
}
/*
Index: src/backend/access/transam/subtrans.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/subtrans.c,v
retrieving revision 1.22
diff -c -p -r1.22 subtrans.c
*** src/backend/access/transam/subtrans.c 26 Mar 2008 18:48:59 - 1.22
--- src/backend/access/transam/subtrans.c 31 Jul 2008 22:02:34 -
***
*** 31,36
--- 31,37
#include access/slru.h
#include access/subtrans.h
#include access/transam.h
+ #include pg_trace.h
#include utils/snapmgr.h
*** ShutdownSUBTRANS(void)
*** 265,271
--- 266,274
* This is not actually necessary from a correctness point of view. We do
* it merely as a debugging aid.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(false);
SimpleLruFlush(SubTransCtl, false);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(false);
}
/*
*** CheckPointSUBTRANS(void)
*** 281,287
--- 284,292
* it merely to improve the odds that writing of dirty pages is done by
* the checkpoint process and not by backends.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(true);
SimpleLruFlush(SubTransCtl, true);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/twophase.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.43
diff -c -p -r1.43 twophase.c
*** src/backend/access/transam/twophase.c 19 May 2008 18:16:26 - 1.43
--- src/backend/access/transam/twophase.c 31 Jul 2008 22:02:50 -
***
*** 51,56
--- 51,57
#include catalog/pg_type.h
#include funcapi.h
#include miscadmin.h
+ #include pg_trace.h
#include pgstat.h
#include storage/fd.h
#include storage/procarray.h
*** CheckPointTwoPhase(XLogRecPtr redo_horiz
***
[HACKERS] Fixing the representation of ORDER BY/GROUP BY/DISTINCT
So while I was fooling with Steve Midgley's problem I got a bit of a bee
in my bonnet about the way that the parser emits ORDER BY, GROUP BY,
and DISTINCT lists.
* Currently, ORDER BY and DISTINCT use lists of SortClause, while GROUP
BY is a list of GroupClause --- but these are actually the same struct,
and there's a fair amount of code that relies on that. The advantage of
them being the same is that it's easy to compare them when considering
sort-and-uniq-style grouping plans. Except it's not easy enough: I
tried to use a list_member test in one place, and of course it didn't
work because equal() never sees distinct node tags as equal. So I'm
thinking we ought to unify the two node types logically as well as
physically, and just have one node type (SortGroupClause, maybe).
* The current representation is fine for ORDER BY but leaves something
to be desired for GROUP BY and DISTINCT: there isn't anyplace to specify
the equality operator for a hash-based grouping operation. This results
in repeat lookups in the planner to fetch information that was readily
available at parse time. But what's worse IMHO is that we simply cannot
represent a grouping query for a datatype that hasn't got a btree sort
opclass --- even though we could implement it, by means of hashing, if
the type has a hashable equality operator. (This isn't academic: it's
easy to imagine datatypes that have equality but no natural linear sort
order. A practical example is XID, which in fact has a hash opclass
but not a btree opclass, because it violates the law of transitivity.)
So what I'm thinking we want is something like
typedef struct SortGroupClause
{
NodeTag type;
Index tleSortGroupRef;/* reference into targetlist */
Oid eqop; /* the equality operator ('=' op) */
Oid sortop; /* the ordering operator ('' op), or 0 */
boolnulls_first;/* do NULLs come before normal values? */
} SortGroupClause;
In an ORDER BY item the sortop and nulls_first flag *must* be supplied.
The eqop isn't really useful for ORDER BY, but it's trivial to get when
we get the sortop, and always including it simplifies comparisons to
GROUP/DISTINCT items. In GROUP BY/DISTINCT items, the eqop *must* be
supplied, and if it is a btree equality operator then the associated
sortop should be given as well. We'd continue the current practice of
duplicating the ordering properties of any ORDER BY clause given for the
same targetlist item, so that compatible ordering and grouping items are
just equal().
* Another thing I've gotten tired of is the difficulty of telling
DISTINCT from DISTINCT ON in the parsed representation. Surely we can
afford to stick another bool field into Query to make that distinction
unambiguous. This is important for making the world safe for hashed
DISTINCT, since AFAICS we probably can't ever use hashing for DISTINCT
ON --- its definition is too dependent on the assumption of sorting.
Barring objections, I'm going to go off and make this happen. It won't
immediately result in supporting hashed DISTINCT, but it's another
necessary step on the road to that.
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
Re: [HACKERS] Copy storage parameters on CREATE TABLE LIKE/INHERITS
Tom Lane [EMAIL PROTECTED] wrote:
There are a couple of other things that need to be thought about:
* LIKE is intended to copy a table as a *portion* of another table;
You're absolutely right. I just thought it's a *better default* behavior
because LIKE and INHERITS are often used in single inheritance, but
in definition they declare columns, not whole tables.
In other words, we don't have an ability of copying tables as units...
However, I think we'd better to have an ability to copy reloptions easily.
There were requests about adding a configuration parameter to modify
default fillfactor. I think reloption-cloning would be a solution about it.
How about adding a new WITH-LIKE syntax?
CREATE TABLE newtbl ( ... ) WITH (LIKE template-table)
It is expanded to an option array as below:
SELECT 'WITH (' || array_to_string(
array_append(reloptions, 'OIDS=' || relhasoids), ',') || ')'
FROM pg_class
WHERE oid = template-table;
I think therefore that having LIKE copy anything global to a table,
such as tablespace or reloptions, is fundamentally wrongheaded. What
will you do about conflicts? The same is true for inheritance cases,
since a table can inherit from multiple parents.
Currently I uses the first setting found in multiple tables
and directly specified options have the highest priority.
For example, setting are used in order of [A] - [B] - [C].
Conflicted parameters are ignored silently for now.
CREATE TABLE newtbl (LIKE [B], LIKE [C]) WITH ([A])
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Alvaro Herrera wrote:
Here's what I have. Please confirm that this compiles for you.
I made some changes to the sed script so it works with the sed on
Solaris OS X. I tested this patch on both Solaris and OS X with DTrace
enabled and disabled and also verified that the sed script works with
GNU sed. I hope this is the final change for this patch. Thanks for
catching all the issues, and my bad for not testing with DTrace disabled.
--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql
Index: src/backend/Makefile
===
RCS file: /projects/cvsroot/pgsql/src/backend/Makefile,v
retrieving revision 1.128
diff -u -3 -p -r1.128 Makefile
--- src/backend/Makefile17 Mar 2008 19:44:40 - 1.128
+++ src/backend/Makefile1 Aug 2008 03:56:13 -
@@ -147,7 +147,7 @@ $(top_builddir)/src/include/utils/probes
ifeq ($(PORTNAME), solaris)
utils/probes.o: utils/probes.d $(SUBDIROBJS)
- $(DTRACE) $(DTRACEFLAGS) -G -s $(call expand_subsys,$^) -o $@
+ $(DTRACE) $(DTRACEFLAGS) -C -G -s $(call expand_subsys,$^) -o $@
endif
Index: src/backend/access/transam/clog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/clog.c,v
retrieving revision 1.46
diff -u -3 -p -r1.46 clog.c
--- src/backend/access/transam/clog.c 1 Jan 2008 19:45:46 - 1.46
+++ src/backend/access/transam/clog.c 1 Aug 2008 03:56:14 -
@@ -36,6 +36,7 @@
#include access/slru.h
#include access/transam.h
#include postmaster/bgwriter.h
+#include pg_trace.h
/*
* Defines for CLOG page sizes. A page is the same BLCKSZ as is used
@@ -313,7 +314,9 @@ void
ShutdownCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(false);
SimpleLruFlush(ClogCtl, false);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(false);
}
/*
@@ -323,7 +326,9 @@ void
CheckPointCLOG(void)
{
/* Flush dirty CLOG pages to disk */
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_START(true);
SimpleLruFlush(ClogCtl, true);
+ TRACE_POSTGRESQL_CLOG_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/multixact.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/multixact.c,v
retrieving revision 1.27
diff -u -3 -p -r1.27 multixact.c
--- src/backend/access/transam/multixact.c 1 Jan 2008 19:45:46 -
1.27
+++ src/backend/access/transam/multixact.c 1 Aug 2008 03:56:15 -
@@ -57,6 +57,7 @@
#include storage/lmgr.h
#include utils/memutils.h
#include storage/procarray.h
+#include pg_trace.h
/*
@@ -1497,8 +1498,10 @@ void
ShutdownMultiXact(void)
{
/* Flush dirty MultiXact pages to disk */
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(false);
SimpleLruFlush(MultiXactOffsetCtl, false);
SimpleLruFlush(MultiXactMemberCtl, false);
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(false);
}
/*
@@ -1526,6 +1529,8 @@ MultiXactGetCheckptMulti(bool is_shutdow
void
CheckPointMultiXact(void)
{
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_START(true);
+
/* Flush dirty MultiXact pages to disk */
SimpleLruFlush(MultiXactOffsetCtl, true);
SimpleLruFlush(MultiXactMemberCtl, true);
@@ -1540,6 +1545,8 @@ CheckPointMultiXact(void)
*/
if (!InRecovery)
TruncateMultiXact();
+
+ TRACE_POSTGRESQL_MULTIXACT_CHECKPOINT_DONE(true);
}
/*
Index: src/backend/access/transam/subtrans.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/subtrans.c,v
retrieving revision 1.22
diff -u -3 -p -r1.22 subtrans.c
--- src/backend/access/transam/subtrans.c 26 Mar 2008 18:48:59 -
1.22
+++ src/backend/access/transam/subtrans.c 1 Aug 2008 03:56:15 -
@@ -32,6 +32,7 @@
#include access/subtrans.h
#include access/transam.h
#include utils/snapmgr.h
+#include pg_trace.h
/*
@@ -265,7 +266,9 @@ ShutdownSUBTRANS(void)
* This is not actually necessary from a correctness point of view. We
do
* it merely as a debugging aid.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(false);
SimpleLruFlush(SubTransCtl, false);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(false);
}
/*
@@ -281,7 +284,9 @@ CheckPointSUBTRANS(void)
* it merely to improve the odds that writing of dirty pages is done by
* the checkpoint process and not by backends.
*/
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_START(true);
SimpleLruFlush(SubTransCtl, true);
+ TRACE_POSTGRESQL_SUBTRANS_CHECKPOINT_DONE(true);
}
Index: src/backend/access/transam/twophase.c
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Alvaro Herrera wrote: I was checking the DTrace docs for other reasons and I came across this, which maybe can be useful here: http://docs.sun.com/app/docs/doc/817-6223/chp-xlate?a=view Yes, I think using the translator is the best approach to expose internal structures in a stable manner. -- Robert Lor Sun Microsystems Austin, USA http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
