Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Hello,
Thank you for correcting patch.
However, I think the following block have to use VirualFree*Ex*().
(yes, this should never happen, maybe there is actually no problem.
but for logical correctness)
+ if (address != UsedShmemSegAddr)
+ {
+ /*
+ * Should never happen - in theory if allocation granularity
causes strange
+ * effects it could, so check just in case.
+ *
+ * Don't use FATAL since we're running in the postmaster.
+ */
+ elog(LOG, reserved shared memory region got incorrect address %p,
expected %p,
+ address, UsedShmemSegAddr);
+ VirtualFree(address, 0, MEM_RELEASE);
VirtualFreeEx(hChild, address, 0, MEM_RELEASE);
+ return false;
+ }
Regards,
--
Tsutomu Yamada
SRA OSS, Inc. Japan
--
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] revised hstore patch
On Jul 22, 2009, at 11:17 AM, Andrew Gierth wrote: To me (A) is looking like the obvious choice (the people smart enough to be using hstore-new from CVS already can handle the minor pain of updating the on-disk format). Unless I hear any objections I will proceed accordingly... Yes, that seems like the smarter path to me, too, as long as the new format does not continue the bug, of course. But should the bug be fixed in maintenance branches? I'm thinking, since its likelihood is so rare, probably not. Best, David -- 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] extension facility (was: revised hstore patch)
On Jul 22, 2009, at 1:11 PM, Robert Haas wrote: If you keep an old and a new version of the datatype, you can't upgrade a tuple at a time, but you can at least upgrade one column at a time, which is still better than a kick in the head. And as long as you're willing to deprecate how far back you'll go in doing such updates, thus keeping the maintenance of your code reasonable over time. If you make the extension-upgrade facility rewrite everything, you have to do your entire cluster in one shot. That will work for some people, but not for all. And unless you ship both versions of hstore with either PG 8.4 or PG 8.5, you're going to need the conversion to be done inside pg_migrator, which introduces a whole new level of complexity that I think we'd be better off without. Well, it depends. If there could be some sort of defined interface for pg_migrator could call to migrate any data type (this issue applies mainly to types, yes?), then an extension author just needs to implement that interface. No? Best, David -- 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] Extension Facility
On Jul 22, 2009, at 11:46 AM, Dimitri Fontaine wrote: Here are from memory the problems we don't have a solution for yet: - how to give user the ability to install the extension's objects in another schema than the pg_extension default one Was that not a part of your original proposal, or the ensuing discussion? Hrm, perhaps not. So I suggest that we take your proposed syntax: create extension foo ... And just allow it to take a schema-qualified argument like any other SQL command: create extension myschema.foo ... - how to provide extension author a way to have major PG version dependant code without having to implement and maintain a specific function in their install.sql file For a lot of extensions this may not be necessary. So I don't think I'd hold up an initial implementation waiting for this to be figured out. My $0.02. Best, David -- 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] DefaultACLs
Hi, Anyway, while this patch might not necessary get commited in this commit fest, I'd still like to have opinion from one of the commiters on the VIEW problem which also affects grant on all patch ( see http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I fear returned with feedback might prevent that until next commit fest. OK, hopefully one of them will chime in with an opinion. As I say, I would like to see this get committed if it's done, I just wasn't sure it was. But now it seems that was due to insufficiently careful reading of the thread, with the exception of this issue and the need to finish the docs. IMO, the committers should have a go at the GRANT ON ALL (simpler than DefaultACLs) patch first. Whatever consensus is arrived for the VIEW issue as part of its review can then spill over to the DefaultACLs patch too. As mentioned by Petr, he does not seem to be in a hurry to get the DefaultACLs patch in, in this commitfest.. Regards, Nikhils -- 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] [PATCH] could not reattach to shared memory on Windows
On Thu, Jul 23, 2009 at 08:04, Tsutomu Yamadatsut...@sraoss.co.jp wrote: Hello, Thank you for correcting patch. However, I think the following block have to use VirualFree*Ex*(). (yes, this should never happen, maybe there is actually no problem. but for logical correctness) That is definitely correct. I have updated the patch in my tree and will make sure to include that in the eventual commit. FYI, and others, I have received a couple of off-list reports from people testing out the patch, and so far only positive results. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] trace hooks (for 2nd commitfest)
I wrote:
How about export dtrace functions as hook function pointers?
Here is a proposal to integrate profiler to postgres without adding
any tracing markers. The goal is to provide platform-independent
and easy-to-use performance profiler. (typically just adding some
configuration to postgresql.conf.)
1. Add Gen_trace_hooks.sed to generate hook functions from probes.d.
It appends hook variables at the tail of probes.h like:
extern void (*TRANSACTION_START_hook)(LocalTransactionId arg1);
2. Rewrite trace function calls into PG_TRACE(name, (args...)).
Trace macros are defined as:
#define PG_TRACE(name, args) \
do { \
TRACE_POSTGRESQL_##name args; \
if (name##_hook) \
name##_hook args; \
} while(0)
and called as:
PG_TRACE(TRANSACTION_START, (vxid.localTransactionId));
The changes are not always necessary, but PG_TRACE macro is
useful to add common logic for all probes. We can also use it
to disable probes; Gen_dummy_probes.sed will be no longer needed.
3. Implement profiler using trace hooks.
Timer callbacks might be needed for periodical sampling,
but I'll try to use simple polling from sql for now.
I tested performance regression by empty dtrace-probes and empty
trace-hooks, but the differences were 1-2%. Close enough to dtrace.
$ pgbench -n -S -c8 -T60
No probes : tps = 28103
ENABLE_TRACE_HOOK only : tps = 28101
ENABLE_DTRACE only : tps = 27945
Enable both: tps = 27760
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] Lock Wait Statistics (next commitfest)
Jaime Casanova wrote: On Fri, Jul 17, 2009 at 3:38 AM, Mark Kirkwoodmar...@paradise.net.nz wrote: With respect to the sum of wait times being not very granular, yes - quite true. I was thinking it is useful to be able to answer the question 'where is my wait time being spent' - but it hides cases like the one you mention. What would you like to see? would max and min wait times be a useful addition, or are you thinking along different lines? track number of locks, sum of wait times, max(wait time). but actually i started to think that the best is just make use of log_lock_waits send the logs to csvlog and analyze there... Right - I'll look at adding max (at least) early next week. Yeah, enabling log_lock_waits is certainly another approach, however you currently miss out on those that are deadlock_timeout - and potentially they could be the source of your problem (i.e millions of waits all deadlock_timeout but taken together rather significant). This shortcoming could be overcome by making the cutoff wait time decoupled from deadlock_timeout (e.g a new parameter log_min_lock_wait_time or similar). I'm thinking that having the lock waits analyzable via sql easily may mean that for most people they don't need to collect and analyze their logs for this stuff (they just examine the lock stats view from Pgadmin or similar). Cheers Mark -- 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] Extension Facility
Hi,
David E. Wheeler da...@kineticode.com writes:
On Jul 22, 2009, at 11:46 AM, Dimitri Fontaine wrote:
- how to give user the ability to install the extension's objects in
another schema than the pg_extension default one
And just allow it to take a schema-qualified argument like any other SQL
command:
create extension myschema.foo ...
The problem is to allow extension code to refer to other extension code
without security problems related to search_path: in short, as an
extension author you want to be able to schema qualify your function
calls or even the PROCEDURE attached to your operators.
Now how to be able to refer to the extension schema in the install.sql
file if user is allowed to install where he wants?
Easy answer for first version: don't allow user to install extension in
another place than what we think will better suit him, and that's the
new schema pg_extension, which always lies just before pg_catalog in the
search_path.
- how to provide extension author a way to have major PG version dependant
code without having to implement and maintain a specific function in
their install.sql file
For a lot of extensions this may not be necessary. So I don't think I'd hold
up an initial implementation waiting for this to be figured out. My $0.02.
Yes. I came up with the beginning of something (major version dependant
additional install.sql files) but then you need to control ordering, so
maybe pre and post install files with major version dependant
derivatives. Over engineered is certainly the comment I'll hear about
it.
Regards,
--
dim
P.S: the best way to help me with the extension stuff as of now would be
to confirm the syntax proposal (separating extension metadata creation
from installation step) is sound for you, and possibly giving hint about
the proposed completion plan up in this thread.
http://archives.postgresql.org/pgsql-hackers/2009-06/msg01281.php
http://archives.postgresql.org/pgsql-hackers/2009-07/msg01425.php
Tom, in particular, what do you think about implementing a general
purpose backend function similar to psql's \i (except without support
for \commands and :variables):
SELECT pg_execute_commands_from_file('path/ to/file.sql');
Your recent work about having a re-entrant parser should make it
possible to implement, by either extending or copy/pasting the
postgres.c:exec_simple_query, right?
(Difference is about not overriding current unnamed portal and maybe
forcing PortalRunMulti() usage, and that there's already a started
transaction (but start_xact_command() is a noop in this case))
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH v4] [libpq] Try to avoid manually masking SIGPIPEs on every send()
Currently, libpq will wrap each send() call on the connection with
two system calls to mask SIGPIPEs. This results in 3 syscalls instead
of one, and (on Linux) can lead to high contention on the signal
mask locks in threaded apps.
We have a couple of other methods to avoid SIGPIPEs:
sockopt(SO_NOSIGPIPE) and the MSG_NOSIGNAL flag to send().
This change attempts to use these if they're available at compile-
and run-time. If not, we drop back to manipulating the signal mask as
before.
Signed-off-by: Jeremy Kerr j...@ozlabs.org
---
v4: roll into one patch, use macros
---
src/interfaces/libpq/fe-connect.c | 42
src/interfaces/libpq/fe-secure.c | 131 ++
src/interfaces/libpq/libpq-int.h |2
3 files changed, 136 insertions(+), 39 deletions(-)
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***
*** 1089,1094 keep_going: /* We
will come back to here until there is
--- 1089,1097
while (conn-addr_cur != NULL)
{
struct addrinfo *addr_cur =
conn-addr_cur;
+ #ifdef SO_NOSIGPIPE
+ int optval;
+ #endif /* SO_NOSIGPIPE */
/* Remember current address for
possible error msg */
memcpy(conn-raddr.addr,
addr_cur-ai_addr,
***
*** 1153,1158 keep_going: /* We
will come back to here until there is
--- 1156,1200
}
#endif /* F_SETFD */
+ /* We have three methods of blocking
sigpipe during
+* send() calls to this socket:
+*
+* - setsockopt(sock, SO_NOSIGPIPE)
+* - send(sock, ..., MSG_NOSIGNAL)
+* - setting the signal mask to
SIG_IGN during send()
+*
+* The first two reduce the number of
syscalls (for the
+* third, we require three syscalls to
implement a send()),
+* so use them if they're available.
Their availability is
+* flagged in the following members of
PGconn:
+*
+* conn-sigpipe_so - we
have set up SO_NOSIGPIPE
+* conn-sigpipe_flag - we're
specifying MSG_NOSIGNAL
+*
+* If we can use SO_NOSIGPIPE, then set
sigpipe_so here and
+* we don't need to care about anything
else. Otherwise,
+* try MSG_NOSIGNAL by setting
sigpipe_flag. If we get an
+* error with MSG_NOSIGNAL, we clear
the flag and revert
+* to manual masking.
+*/
+ conn-sigpipe_so = false;
+ #ifdef MSG_NOSIGNAL
+ conn-sigpipe_flag = true;
+ #else /* !MSG_NOSIGNAL */
+ conn-sigpipe_flag = false;
+ #endif /* MSG_NOSIGNAL */
+
+ #ifdef SO_NOSIGPIPE
+ optval = 1;
+ if (!setsockopt(conn-sock, SOL_SOCKET,
SO_NOSIGPIPE,
+ (char *)optval,
sizeof(optval)))
+ {
+ conn-sigpipe_so = true;
+ conn-sigpipe_flag = false;
+ }
+ #endif /* SO_NOSIGPIPE */
+
+
/*
* Start/make connection. This should
not block, since we
* are in nonblock mode. If it does,
well, too bad.
*** a/src/interfaces/libpq/fe-secure.c
--- b/src/interfaces/libpq/fe-secure.c
***
*** 118,161 static long win32_ssl_create_mutex = 0;
/*
* Macros to handle disabling and then restoring the state of SIGPIPE
handling.
- * Note that DISABLE_SIGPIPE() must appear at the start of a block.
*/
#ifndef WIN32
#ifdef ENABLE_THREAD_SAFETY
! #define DISABLE_SIGPIPE(failaction) \
! sigset_tosigmask; \
! boolsigpipe_pending; \
! boolgot_epipe = false; \
! \
! if
Re: [HACKERS] Extension Facility
On Jul 23, 2009, at 1:08 AM, Dimitri Fontaine wrote: Easy answer for first version: don't allow user to install extension in another place than what we think will better suit him, and that's the new schema pg_extension, which always lies just before pg_catalog in the search_path. Well, I think that it's reasonable to allow an extension to be in any schema, with the default being pg_extension, but all of the objects in a single extension should assume that they're all in the same schema, at least to start. I mean, I can see the need for secondary schemas (or sub-schemas?) for encapsulation, but do we really need to go there in the first rev? Yes. I came up with the beginning of something (major version dependant additional install.sql files) but then you need to control ordering, so maybe pre and post install files with major version dependant derivatives. Over engineered is certainly the comment I'll hear about it. Yeah, so omit it for now, I say. Start with what's widely agreed-upon and relatively simple. We can iterate this pony over time. Best, David -- 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] Upgrading our minimum required flex version for 8.5
On Thu, Jul 23, 2009 at 2:57 AM, Greg Smithgsm...@gregsmith.com wrote: I got bit by this tonight as part of testing a patch on CentOS 5, which like RHEL 5 still ships flex 2.5.4. I just wrote a little guide on how to grab a source RPM from a Fedora version and install it to work around that problem: http://notemagnet.blogspot.com/2009/07/upgrading-flex-from-source-rpm-to.html Kind of annoying, but as special software you have to install on a server just to build something from CVS goes it's only a minor inconvenience. *sigh*. So does OS X Tiger. And (unsurprisingly), CentOS 4, which is what we use for all our Linux build servers. This is getting quite tiresome :-( -- Dave Page EnterpriseDB UK: 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] Upgrading our minimum required flex version for 8.5
On Thu, Jul 23, 2009 at 10:52, Dave Pagedp...@pgadmin.org wrote: On Thu, Jul 23, 2009 at 2:57 AM, Greg Smithgsm...@gregsmith.com wrote: I got bit by this tonight as part of testing a patch on CentOS 5, which like RHEL 5 still ships flex 2.5.4. I just wrote a little guide on how to grab a source RPM from a Fedora version and install it to work around that problem: http://notemagnet.blogspot.com/2009/07/upgrading-flex-from-source-rpm-to.html Kind of annoying, but as special software you have to install on a server just to build something from CVS goes it's only a minor inconvenience. *sigh*. So does OS X Tiger. And (unsurprisingly), CentOS 4, which is what we use for all our Linux build servers. This is getting quite tiresome :-( Much as I dislike it, we may need to revisit the idea about putting the flex output files in CVS... -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Extension Facility
David E. Wheeler da...@kineticode.com writes: On Jul 23, 2009, at 1:08 AM, Dimitri Fontaine wrote: Easy answer for first version: don't allow user to install extension in another place than what we think will better suit him, and that's the new schema pg_extension, which always lies just before pg_catalog in the search_path. Well, I think that it's reasonable to allow an extension to be in any schema, with the default being pg_extension, but all of the objects in a single extension should assume that they're all in the same schema, at least to start. I mean, I can see the need for secondary schemas (or sub-schemas?) for encapsulation, but do we really need to go there in the first rev? Well the problem with that is if for example I define foo() and bar() functions in my extension, and the user also has a foo() function in his own stuff (possibly lying in public, say). Now if in my extenion in function bar() I call foo(), how do I make sure I'm calling my extension's foo()? -- dim -- 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] Upgrading our minimum required flex version for 8.5
Magnus Hagander wrote: On Thu, Jul 23, 2009 at 10:52, Dave Pagedp...@pgadmin.org wrote: On Thu, Jul 23, 2009 at 2:57 AM, Greg Smithgsm...@gregsmith.com wrote: I got bit by this tonight as part of testing a patch on CentOS 5, which like RHEL 5 still ships flex 2.5.4. I just wrote a little guide on how to grab a source RPM from a Fedora version and install it to work around that problem: http://notemagnet.blogspot.com/2009/07/upgrading-flex-from-source-rpm-to.html Kind of annoying, but as special software you have to install on a server just to build something from CVS goes it's only a minor inconvenience. *sigh*. So does OS X Tiger. And (unsurprisingly), CentOS 4, which is what we use for all our Linux build servers. This is getting quite tiresome :-( Much as I dislike it, we may need to revisit the idea about putting the flex output files in CVS... Why? This only affects developers building from a CVS pull. You don't need any flex at all to build from a tarball. If developers can't install flex on a *nix box they need to get out of the business. cheers andrew -- 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] Determining client_encoding from client locale
On Thursday 23 July 2009 02:29:23 Jaime Casanova wrote: this little test compiles fine until i applied your patch :( postg...@casanova1:~/pg_releases/pgtests$ gcc -o test-libpq test-libpq.o -L/usr/local/pgsql/head/lib -lpq /usr/local/pgsql/head/lib/libpq.so: undefined reference to `pg_get_encoding_from_locale' collect2: ld returned 1 exit status libpq fails to link in chklocale.c. -- 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] DefaultACLs
On Thursday 23 July 2009 06:26:05 Petr Jelinek wrote: I'd still like to have opinion from one of the commiters on the VIEW problem which also affects grant on all patch ( see http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I fear returned with feedback might prevent that until next commit fest. I see potential for confusion in that GRANT ON TABLE x works if x is a base table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you need to make up a different syntax to affect only base tables, e.g., GRANT ON ALL BASE TABLES. -- 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] psql - small fix in \du
Tom Lane schrieb: Andreas Wenk a.w...@netzmeister-st-pauli.de writes: I am not sure when the function shobj_description(oid, name) is giving a result. That retrieves the comment for the object (the role, in this case). regards, tom lane attached you can find an updated patch. The changes are: - change \du and \dg to \du+ and \dg+ in the docu - change the same in psql/help.c - change the translation files in psql/po two questions on this: - is it ok to provide all these changes in one patch? - I did the patch with git format-patch and left the header in the patch. Ok? Although Tom asked if the translation files should be patched at all, I sent them. vi showed the foreign language signs in the patch utf-8 encoded and not as the character. I imported the patch in my local master. The md5checksum was the same (e.g ja.po) an the character are in original language. Everything was ok - so if there are problems, please keep me informed. Cheers Andy From 6a54ecafa1626631c6de78f59f72825ab554b9a3 Mon Sep 17 00:00:00 2001 From: Andreas Wenk a.w...@netzmeister-st-pauli.de Date: Thu, 23 Jul 2009 11:15:05 +0200 Subject: [PATCH] In psql \du and \dg can also be used as \du+ and \dg+. This was missing in psql help. All affected files have been changed. --- doc/src/sgml/ref/psql-ref.sgml | 10 +++--- src/bin/psql/help.c|4 ++-- src/bin/psql/po/cs.po |8 src/bin/psql/po/de.po |8 src/bin/psql/po/es.po |8 src/bin/psql/po/fr.po |8 src/bin/psql/po/ja.po |8 src/bin/psql/po/pt_BR.po |8 src/bin/psql/po/sv.po |8 src/bin/psql/po/tr.po |8 10 files changed, 41 insertions(+), 37 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index cd94af1..6de4b83 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1124,13 +1124,15 @@ testdb=gt; varlistentry -termliteral\dg [ replaceable class=parameterpattern/replaceable ]/literal/term +termliteral\dg[+] [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Lists all database roles. If replaceable class=parameterpattern/replaceable is specified, only those roles whose names match the pattern are listed. -(This command is now effectively the same as literal\du/.) +(This command is now effectively the same as literal\du/literal). + If the form literal\dg+/literal is used, additional information + is shown about each role, including the comment for each role. /para /listitem /varlistentry @@ -1247,12 +1249,14 @@ testdb=gt; varlistentry -termliteral\du [ replaceable class=parameterpattern/replaceable ]/literal/term +termliteral\du[+] [ replaceable class=parameterpattern/replaceable ]/literal/term listitem para Lists all database roles. If replaceable class=parameterpattern/replaceable is specified, only those roles whose names match the pattern are listed. + If the form literal\du+/literal is used, additional information + is shown about each role, including the comment for each role. /para /listitem /varlistentry diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 5f13b8a..74c379f 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -210,7 +210,7 @@ slashUsage(unsigned short int pager) fprintf(output, _( \\dFd[+] [PATTERN] list text search dictionaries\n)); fprintf(output, _( \\dFp[+] [PATTERN] list text search parsers\n)); fprintf(output, _( \\dFt[+] [PATTERN] list text search templates\n)); - fprintf(output, _( \\dg [PATTERN] list roles (groups)\n)); + fprintf(output, _( \\dg[+] [PATTERN] list roles (groups)\n)); fprintf(output, _( \\di[S+] [PATTERN] list indexes\n)); fprintf(output, _( \\dllist large objects, same as \\lo_list\n)); fprintf(output, _( \\dn[+] [PATTERN] list schemas\n)); @@ -219,7 +219,7 @@ slashUsage(unsigned short int pager) fprintf(output, _( \\ds[S+] [PATTERN] list sequences\n)); fprintf(output, _( \\dt[S+] [PATTERN] list tables\n)); fprintf(output, _( \\dT[S+] [PATTERN] list data types\n)); - fprintf(output, _( \\du [PATTERN] list roles (users)\n)); + fprintf(output, _( \\du[+] [PATTERN] list roles (users)\n)); fprintf(output, _( \\dv[S+] [PATTERN] list views\n)); fprintf(output, _( \\l[+] list all databases\n)); fprintf(output, _( \\z [PATTERN] same as \\dp\n)); diff --git a/src/bin/psql/po/cs.po b/src/bin/psql/po/cs.po index 196bfe3..eed7ada 100644 --- a/src/bin/psql/po/cs.po +++ b/src/bin/psql/po/cs.po @@ -965,8 +965,8 @@ msgstr \\dFt[+] [VZOR]
Re: [HACKERS] Extensions User Design
On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote: === installing and removing an extension begin; install extension foo with search_path = foo; commit; Extensions authors are asked not to bother about search_path in their sql scripts so that it's easy for DBAs to decide where to install them. The with strange syntax is there to allow for the install extension command to default to, e.g., pg_extension, which won't typically be the first schema in the search_path. begin; drop extension foo [cascade]; commit; The cascade option is there to care about reverse depends. I have been thinking about a different use case for this, and I wonder whether that can fit into your proposal. Instead of installing an extension, that is, say, a collection of types and functions provided by a third-party source, I would like to have a mechanism to deploy my own actual database application code. That is, after all, how I work with non-database deployments: I build a package (deb, rpm) from the code, and install it on the target machine. The package system here functions as a deployment aid both for extensions of the operating system and for local custom code. Applying this method to database code, with regard to your proposal, means first of all that naming this thing extension is questionable, and that installing everything by default into some schema like pg_extensions is inappropriate. If you look at how a dpkg or rpm package is structured, it's basically an archive (ar or cpio) of the files to install plus some control information such as name, version, dependencies, and various pre/post scripts. We already have the first part of this: pg_dump/pg_restore are basically tools to create an archive file out of a database and extract an archive file into a database. I have been toying with the idea lately to create a thin wrapper around pg_restore that would contain a bit of metainformation of the kind listed above. That would actually solve a number of problems already. And then, if pg_restore could be taught to do upgrades instead of just overwriting (e.g., ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all fall into place nicely. What this needs below the surface is basically librpm: an interface to describe and query which objects belong to which package and to associate pre/post scripts with packages. And I think that that interface is quite like the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts could be functions, actually, instead of scripts.) On the matter of schemas, I suggest that we consider two ideas that have helped RPM in its early days, when everyone had their own very specific ideas about what should be installed where: - file system hierarchy standard - relocations This means, we'd write up standard of where we think you *should* install things. And we expect that quality packages/bundles/extensions created for wider distribution install themselves in the right place without additional user intervention. But the packaging tool would provide a way to override this. Then, something that is a true extension could in fact be set up to install itself by default into pg_extensions, but a bundle containing local custom code would be set up so that it installs into a different schema or schemas by default. What do you think? -- 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] Extensions User Design
Peter Eisentraut wrote: Instead of installing an extension, that is, say, a collection of types and functions provided by a third-party source, I would like to have a mechanism to deploy my own actual database application code. On the matter of schemas, I suggest that we consider two ideas that have helped RPM in its early days, when everyone had their own very specific ideas about what should be installed where: - file system hierarchy standard - relocations Of course if you have IMPORT from an extension, it's down to the DBA: INSTALL chinese_calendar; IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; INSTALL peter_e_app; IMPORT FROM peter_e_app SECTION (all) INTO SCHEMA public; Of course this means two things: 1. Every extension has to have its own schema mappings. 2. The application view of the database is a sort of default extension Pros: - Namespace collisions begone! - Anything to help extension upgrades could be re-used for applications (and vice-versa) - Some stuff isn't visible outside the extension *at all* - You can separate extension installation from usage (good for multi-user setups). Cons: - Extra layer of indirection (find my namespace = namespace lookup = object) - Extensions need to list what they export in what sections - More code required -- Richard Huxton Archonet Ltd -- 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] extension facility (was: revised hstore patch)
On Jul 23, 2009, at 2:44 AM, David E. Wheeler da...@kineticode.com wrote: On Jul 22, 2009, at 1:11 PM, Robert Haas wrote: If you keep an old and a new version of the datatype, you can't upgrade a tuple at a time, but you can at least upgrade one column at a time, which is still better than a kick in the head. And as long as you're willing to deprecate how far back you'll go in doing such updates, thus keeping the maintenance of your code reasonable over time. Of course. If you make the extension-upgrade facility rewrite everything, you have to do your entire cluster in one shot. That will work for some people, but not for all. And unless you ship both versions of hstore with either PG 8.4 or PG 8.5, you're going to need the conversion to be done inside pg_migrator, which introduces a whole new level of complexity that I think we'd be better off without. Well, it depends. If there could be some sort of defined interface for pg_migrator could call to migrate any data type (this issue applies mainly to types, yes?), then an extension author just needs to implement that interface. No? Yes... but if and just can paper over a good deal of complexity, and it's not clear to me that there's any compensating advantage. ...Robert -- 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] Higher TOAST compression.
On Wed, Jul 22, 2009 at 10:54 AM, Laurent Labordekerdez...@gmail.com wrote:
My 1st applied patch is the safest and simpliest :
in pg_lzcompress.c :
static const PGLZ_Strategy strategy_default_data = {
256, /* Data chunks less than 256 are not compressed */
256, /* force compression on data chunks on record = 256bytes */
1, /* compression rate below 1% fall back to uncompressed */
256, /* Stop history lookup if a match of 256 bytes is found */
6 /* lower good match size b 6% at every lookup iteration */
};
const PGLZ_Strategy *const PGLZ_strategy_default = strategy_default_data;
I'm testing in production since yesterday.
It greatly improved %IOwait.
My 1st guess is that postgresql keep more data inline instead of
moving it in extern to toast table, reducing massively the IOseek and
resulting in a higher IO througput.
(iostat show a 5~25MB/s bandwidth at 100%util instead of 2~5MB/s at 100%util).
So... now i'm not sure anymore about lowering the tuple per page from 4 to 8.
Doing that would mean more data in TOAST table ...
--
Laurent ker2x Laborde
Sysadmin @ http://www.over-blog.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] extension facility (was: revised hstore patch)
Robert Haas robertmh...@gmail.com writes: On Jul 23, 2009, at 2:44 AM, David E. Wheeler da...@kineticode.com wrote: Well, it depends. If there could be some sort of defined interface for pg_migrator could call to migrate any data type (this issue applies mainly to types, yes?), then an extension author just needs to implement that interface. No? Yes... but if and just can paper over a good deal of complexity, and it's not clear to me that there's any compensating advantage. Well there's already an API for this in the extension design: create extension foo ... upgrade function upgrade_foo(old version, new version) So pg_migrator would have to look on previous cluster for which version of the module was there and on the new cluster which is installed, and run the function accordingly... All the burden is then on the extension's author. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] query decorrelation in postgres
Hello, I am a master's student in computer science at IIT Bombay. As part of my project, I need to get a decorrelated version of a SQL query. Please could anyone let me know if we have query decorrelation feature implemented in postgres ? Thanks, Mahendra Chavan
Re: [HACKERS] Extensions User Design
Peter Eisentraut pete...@gmx.net writes: On Tuesday 23 June 2009 20:44:39 Dimitri Fontaine wrote: Instead of installing an extension, that is, say, a collection of types and functions provided by a third-party source, I would like to have a mechanism to deploy my own actual database application code. I'd like for the extension facility to cover application code in the database too, yes. Short of install time choice of schema I think we're there, but please refer to the infamous search_path vs extensions debate we had, that I wanted to consider as a pre-requisite for User Extension Design: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00912.php After this, I'm considering that if we want to have anything, we'll have to begin implementing extensions and find a schema relocation facility later on. Unless you have one now? :) That is, after all, how I work with non-database deployments: I build a package (deb, rpm) from the code, and install it on the target machine. The package system here functions as a deployment aid both for extensions of the operating system and for local custom code. Applying this method to database code, with regard to your proposal, means first of all that naming this thing extension is questionable, and that installing everything by default into some schema like pg_extensions is inappropriate. I'll be happy to be provided a better name if we manage to implement both ideas into the same facility, or see a way to get there in a near future :) And then, if pg_restore could be taught to do upgrades instead of just overwriting (e.g., ALTER TABLE ADD COLUMN instead of DROP TABLE + CREATE TABLE), this would all fall into place nicely. I'm not sure about this. What we want when using pg_restore is typically an upgrade, of PostgreSQL itself but of the extensions too... and I don't think we can manage from the metadata what the extension upgrading needs are. What this needs below the surface is basically librpm: an interface to describe and query which objects belong to which package and to associate pre/post scripts with packages. And I think that that interface is quite like the CREATE/DROP EXTENSION stuff that you are describing. (Pre/post scripts could be functions, actually, instead of scripts.) Yes, and we're having both an entry into pg_catalog.pg_extension containing the metadata and pg_catalog.pg_depend entries to cook up a query acting as either `dpkg -L` or `rpm -ql`. Now, pre and post script if needed could also be pre_install.sql and post_install.sql with some support at the CREATE EXTENSION level. I didn't want to add them on the first round to avoid being pointed at doing over engineering, but now that it is you asking for it, let's do that :) On the matter of schemas, I suggest that we consider two ideas that have helped RPM in its early days, when everyone had their own very specific ideas about what should be installed where: - file system hierarchy standard - relocations This means, we'd write up standard of where we think you *should* install things. And we expect that quality packages/bundles/extensions created for wider distribution install themselves in the right place without additional user intervention. The aim is for users to \i extension.sql which only contains the CREATE EXTENSION command, then INSTALL EXTENSION extension, and be done with it. But the packaging tool would provide a way to override this. Then, something that is a true extension could in fact be set up to install itself by default into pg_extensions, but a bundle containing local custom code would be set up so that it installs into a different schema or schemas by default. What do you think? How do you implement relocate in a way to guarantee there's no security disaster waiting to happen? Namely that a function foo() calling another function foo_support_fn() from within the extension won't be calling a (malicious?) user defined foo_support_fn() from another schema, depending on run time search_path? Having both extension function calls schema qualified and relocations is the biggest problem we're facing, and it seems we're still short of a solution for it... or did I just miss it? -- dim -- 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] Extensions User Design
Richard Huxton d...@archonet.com writes: INSTALL chinese_calendar; IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; Please see Andrew Dunstan mail about using some notion of ALIAS (is that a standard compliant SYNONYM?) for handling this: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php Regards, -- dim -- 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] Aggregate-function space leakage
2009/7/23 Greg Stark gsst...@mit.edu: On Wed, Jul 22, 2009 at 10:14 PM, Tom Lanet...@sss.pgh.pa.us wrote: The reason for that turns out to be that we deliberately lobotomized array_agg that way, just last month: http://archives.postgresql.org/pgsql-committers/2009-06/msg00259.php in response to this problem: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01186.php We need a better idea. Rereading your diagnosis of Merlin Moncure's original problem I'm a bit puzzled. Why do we have to rerun the final function when we rescan the hash table? Surely the logical thing to do is to store the final value in the hash table with some flag saying that value has been finalized rather than to reexecute the final function every time it's rescanned. I'm not sure that really solves anything though since there's no guarantee that the first scan was finished when it's reset so there could still be unfinalized elements in the hash table. Would it be too costly to finalize all the hash elements in a single pass before returning any? It looks like Agg node builds whole of the hash table before returning a tuple in hash-mode. If it stores all the results somewhere and just return them on rescan, an issue is volatile final functions (and I know it's so rare case), but except for that it sounds sane, though I don't know exact requirement of rescaning and reexecuting in the Exectuor. If it really needs to release anything when rescan, this approach also fails. So two ideas from Tom seem to me a little worse than that. Modifying Agg.c might add overhead to reset context group by group and forcing array_agg() (i.e. user aggregates) to distinguish hash-mode and group-mode is definitely heavy for users. The real problem here is how/when to release transvalue stored by aggregates in new method introduced in 8.4 with array_agg(), which is to pass pointers by transfunc's arguments. Maybe array_agg should not do that thing introduced in 8.4. We may go back to array_accum() possibly. Regards, -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Split-up ECPG patches
Michael Meskes írta: On Wed, Jul 15, 2009 at 07:17:17PM +0200, Böszörményi Zoltán wrote: as asked by Michael Meskes, I have split up our ECPG patchset: Just a couple quick comments. It appears to me (without much testing) that the patches still partly rely on each other. But I cannot see a reason for this. There are three reasons: 1. sqlda and string type support both add one constant in ecptypes.h 2. dynamic cursorname and DESCRIBE support both modify the FetchStmt rule. 3. DESCRIBE support partially builds on sqlda support I saw no point creating patches that are applicable standalone when they would conflict each other. The point would be to have all patches upstreamed, reviewed and applied in the order indicated by the patch filenames. Another point was that where to split features? SQLDA and DESCRIBE [OUTPUT] features overlap. 1. dynamic cursorname (DECLARE :cursorname ..., etc) 2. SQLDA support in Informix compat mode (C structure used for descriptor and data query) One file has this: * (C) 2009 Cybertec GmbH * Zolt??n B??sz??rm??nyi z...@cybertec.at * Hans-J??rgen Sch??nig h...@cybertec.at Shouldn't this also list a license? In general I wonder whether we need some statement for every patch submitted? Anyone more into licensing might comment here. What is the correct way to indicate that the licence is the same as the PostgreSQL licence but we are the authors? We aren't license experts. :-) 3. DESCRIBE OUTPUT support for named and sqlda descriptors I don't think we have to add ECPGdescribe2 to keep the old API. The old ECPGdescribe function does nothing, so it's not worth being kept. I thought about easing transition and letting old binaries work as is. IIRC a common wisdom is that if you add API calls, you only need to increase the minor library version. But if you modify an existing call you create an incompatibility (even when the usage of said call was unlikely) and the major library version need to be increased. 4. string pseudo-type in Informix compat mode There is still a lot of stuff being done when not in compatibility mode. I thought you wanted to change that? The things is that in Informix mode, the patch refuses typedef ... string;, in native mode it lets string typename through. make check under ecpg passes. Isn't that enough? Is there a particular place you didn't like? Thanks for the review so far. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] Upgrading our minimum required flex version for 8.5
Andrew Dunstan and...@dunslane.net writes: Magnus Hagander wrote: Much as I dislike it, we may need to revisit the idea about putting the flex output files in CVS... Why? This only affects developers building from a CVS pull. You don't need any flex at all to build from a tarball. If developers can't install flex on a *nix box they need to get out of the business. I wonder if it would be helpful to have a buildfarm option whereby it would fetch the latest nightly-snapshot tarball and use that instead of a CVS pull. This would have the dual advantage of actually testing builds from tarballs and requiring less stuff on the buildfarm machine. It wouldn't be useful for more-than-once-a-day builds, but a lot of the machines only build that often anyhow. 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] Upgrading our minimum required flex version for 8.5
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Magnus Hagander wrote: Much as I dislike it, we may need to revisit the idea about putting the flex output files in CVS... Why? This only affects developers building from a CVS pull. You don't need any flex at all to build from a tarball. If developers can't install flex on a *nix box they need to get out of the business. I wonder if it would be helpful to have a buildfarm option whereby it would fetch the latest nightly-snapshot tarball and use that instead of a CVS pull. This would have the dual advantage of actually testing builds from tarballs and requiring less stuff on the buildfarm machine. It wouldn't be useful for more-than-once-a-day builds, but a lot of the machines only build that often anyhow. That is certainly doable. It would be in effect a forced run, because we would have no notion of what had changed. Presumably, this would only be for HEAD - we don't do daily snapshots of the back branches, do we? cheers andrew -- 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] Upgrading our minimum required flex version for 8.5
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I wonder if it would be helpful to have a buildfarm option whereby it would fetch the latest nightly-snapshot tarball and use that instead of a CVS pull. That is certainly doable. It would be in effect a forced run, because we would have no notion of what had changed. Presumably, this would only be for HEAD - we don't do daily snapshots of the back branches, do we? There was just recently some discussion of providing daily snapshots for the most recent back branch or two. I'm not sure if that's up and running, but the consensus seemed to be that it was a good idea. 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] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: (iostat show a 5~25MB/s bandwidth at 100%util instead of 2~5MB/s at 100%util). Any numbers for overall benefit at the application level? So... now i'm not sure anymore about lowering the tuple per page om 4 to 8. Doing that would mean more data in TOAST table ... Yeah, I've been skeptical that it would be a good thing for your situation unless the lower target only applied to more aggressive compression, not out-of-line storage. If you can wait for a week or two, I can give you a proof of concept patch to use separate targets for compression and out-of-line storage. It would be interesting to see how much that helps when combined with your more aggressive compression configuration. -Kevin -- 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] Extension Facility
On Jul 23, 2009, at 2:11, Dimitri Fontaine dfonta...@hi-media.com wrote: Well the problem with that is if for example I define foo() and bar() functions in my extension, and the user also has a foo() function in his own stuff (possibly lying in public, say). Now if in my extenion in function bar() I call foo(), how do I make sure I'm calling my extension's foo()? Part of the behavior of CREATE EXTENSION would be to automatically schema-qualify references to objects in the extension. Or perhaps extension authors would need to use some sort of variable for the schema that would be properly resolved when CREATE EXTENSION installed an extension. Those are the first ideas that come to kind for me, anyway. Best, David -- 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] extension facility (was: revised hstore patch)
On Jul 23, 2009, at 4:08, Robert Haas robertmh...@gmail.com wrote: Yes... but if and just can paper over a good deal of complexity, and it's not clear to me that there's any compensating advantage. It seems reasonable not to worry about this issue in the first rev, or at least not to let it stop development of other features, so that it gas time to gel via discussion over time. Best, David -- 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] Extension Facility
David E. Wheeler da...@kineticode.com writes: Part of the behavior of CREATE EXTENSION would be to automatically schema-qualify references to objects in the extension. Or perhaps extension authors would need to use some sort of variable for the schema that would be properly resolved when CREATE EXTENSION installed an extension. What about embedded calls in, say, plperl functions. -- dim -- 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] Determining client_encoding from client locale
Jaime Casanova jcasa...@systemguards.com.ec writes: On Wed, Jul 22, 2009 at 7:30 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Do you have an older version of libpq.so around? the one that installed with 8.4.0 but i thougth that when you specify -L to gcc you're telling it where to pick libraries from, no? On most Linux systems, -L doesn't have any effect on what happens at runtime --- the dynamic linker's search path will determine that. Try ldd on the executable to see which shlibs really get picked up. 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] generic explain options v3
Robert Haas robertmh...@gmail.com writes: Ugh. I took a look at this and it turns out that there are some tentacles. It doesn't seem very sane to actually do anything with a list of DefElem nodes, so we really need to parse that list and convert it to a more sensible format right away (this also seems important for proper error checking). Yeah, the standard approach is to convert it into a group of values at the start of execution of the utility command. The obvious solution to that is to create the ExplainState sooner, back up at the ExplainQuery level. If we do that, though, then ExplainState will need to become a public API, because contrib/auto_explain calls ExplainPrintPlan(). Well, if we add any more options to EXPLAIN then auto_explain may well be interested in them, so I'm not sure this is bad. The alternative is to keep adding retail parameters to the public functions. And if we do that, then probably we should declare it in include/nodes/execnodes.h and make it a node type... No, just a struct declared in commands/explain.h. There's no reason for it to be part of the Node system. 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] Extensions User Design
Dimitri Fontaine wrote: Richard Huxton d...@archonet.com writes: INSTALL chinese_calendar; IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; Please see Andrew Dunstan mail about using some notion of ALIAS (is that a standard compliant SYNONYM?) for handling this: http://archives.postgresql.org/pgsql-hackers/2009-05/msg01391.php http://archives.postgresql.org/pgsql-hackers/2009-05/msg01401.php Please particularly see this sentence: But unless someone wants to tackle that I think we should leave schema management entirely alone, and leave it up to the extension author / DBA between them. I think we are in some danger of massively overdesigning this feature (and of repeating past discussions with little extra content). Please don't keep adding bells and whistles. The best development is almost always incremental. Let's start simple and then add features. cheers andrew -- 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood mar...@paradise.net.nz writes: Yeah, enabling log_lock_waits is certainly another approach, however you currently miss out on those that are deadlock_timeout - and potentially they could be the source of your problem (i.e millions of waits all deadlock_timeout but taken together rather significant). This shortcoming could be overcome by making the cutoff wait time decoupled from deadlock_timeout (e.g a new parameter log_min_lock_wait_time or similar). The reason that they're tied together is to keep from creating unreasonable complexity (and an unreasonable number of extra kernel calls) in management of the timeout timers. You will find that you can't just wave your hand and decree that they are now decoupled. 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] Extension Facility
On Jul 23, 2009, at 8:09 AM, Dimitri Fontaine wrote: What about embedded calls in, say, plperl functions. Hence the variable suggestion. In fact, it might go back to the idea of subschemas, perhaps the name of the extension should be part of the qualifying? I dunno, I'm just kind of throwing ideas out there, but it's starting to remind me of packages or classes. Inside a class, a call to a method without an invocant automatically delegates to the method in the class. That sort of thing. But I'm wary of over- designing here, so I'm not sure what the right thing to do is, unless it's to punt. Best, David -- 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] Determining client_encoding from client locale
On Thu, Jul 23, 2009 at 11:02 AM, Tom Lanet...@sss.pgh.pa.us wrote: On most Linux systems, -L doesn't have any effect on what happens at runtime --- the dynamic linker's search path will determine that. Try ldd on the executable to see which shlibs really get picked up. yeah! it's using the one that ships with 8.4.0 postg...@casanova1:~/pg_releases/pgtests$ ldd test-libpq [...other no related libraries...] libpq.so.5 = /opt/PostgreSQL/8.4/lib/libpq.so.5 (0x7f7ef6db2000) The only way i can compile with the patched version of libpq is with this gcc -o test-libpq test-libpq.o -L../pgsql/src/port -lpgport -L../pgsql/src/interfaces/libpq -lpq -L../pgsql/src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/head/lib' -lpgport BTW, i can compile with the unpatched version if i add -lpgport (seems like this patch is adding a dependency) gcc -o test-libpq test-libpq.o -L/usr/local/pgsql/head/lib -lpq -lpgport -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Extensions User Design
On Jul 23, 2009, at 9:09 AM, Andrew Dunstan wrote: Please particularly see this sentence: But unless someone wants to tackle that I think we should leave schema management entirely alone, and leave it up to the extension author / DBA between them. I think we are in some danger of massively overdesigning this feature (and of repeating past discussions with little extra content). Please don't keep adding bells and whistles. The best development is almost always incremental. Let's start simple and then add features. This is what I was trying to get at in my last post in the other thread. While throwing some ideas out on how to handle some of these issues, where there is no clear agreement on what to do, I think we should punt in favor of implementing those parts for which there *is* general agreement. Best, David -- 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] join regression failure on cygwin
Tom Lane wrote: I see it claims to have working erand48, but maybe not so much in reality? Good guess. I removed erand48 from the configure file and added erand48.o to OBJS in src/port/Makefile and it suddenly sailed through. cheers andrew -- 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] join regression failure on cygwin
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I see it claims to have working erand48, but maybe not so much in reality? Good guess. I removed erand48 from the configure file and added erand48.o to OBJS in src/port/Makefile and it suddenly sailed through. Hmm. So we need to figure out how to improve configure's check so that it rejects whatever broken version you've got ... 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] generic explain options v3
On Thu, Jul 23, 2009 at 12:08 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Ugh. I took a look at this and it turns out that there are some tentacles. It doesn't seem very sane to actually do anything with a list of DefElem nodes, so we really need to parse that list and convert it to a more sensible format right away (this also seems important for proper error checking). Yeah, the standard approach is to convert it into a group of values at the start of execution of the utility command. The obvious solution to that is to create the ExplainState sooner, back up at the ExplainQuery level. If we do that, though, then ExplainState will need to become a public API, because contrib/auto_explain calls ExplainPrintPlan(). Well, if we add any more options to EXPLAIN then auto_explain may well be interested in them, so I'm not sure this is bad. The alternative is to keep adding retail parameters to the public functions. And if we do that, then probably we should declare it in include/nodes/execnodes.h and make it a node type... No, just a struct declared in commands/explain.h. There's no reason for it to be part of the Node system. Oh, OK. That will work. Thanks. ...Robert -- 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] Determining client_encoding from client locale
On Thursday 23 July 2009 20:16:39 Jaime Casanova wrote: On Thu, Jul 23, 2009 at 11:02 AM, Tom Lanet...@sss.pgh.pa.us wrote: On most Linux systems, -L doesn't have any effect on what happens at runtime --- the dynamic linker's search path will determine that. Try ldd on the executable to see which shlibs really get picked up. yeah! it's using the one that ships with 8.4.0 postg...@casanova1:~/pg_releases/pgtests$ ldd test-libpq [...other no related libraries...] libpq.so.5 = /opt/PostgreSQL/8.4/lib/libpq.so.5 (0x7f7ef6db2000) The only way i can compile with the patched version of libpq is with this gcc -o test-libpq test-libpq.o -L../pgsql/src/port -lpgport -L../pgsql/src/interfaces/libpq -lpq -L../pgsql/src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/head/lib' -lpgport BTW, i can compile with the unpatched version if i add -lpgport (seems like this patch is adding a dependency) gcc -o test-libpq test-libpq.o -L/usr/local/pgsql/head/lib -lpq -lpgport Which proves my point, because libpgport includes chkconfig.c. But this is just a workaround. -- 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] extension facility (was: revised hstore patch)
On Thu, Jul 23, 2009 at 11:05 AM, David E. Wheelerda...@kineticode.com wrote: On Jul 23, 2009, at 4:08, Robert Haas robertmh...@gmail.com wrote: Yes... but if and just can paper over a good deal of complexity, and it's not clear to me that there's any compensating advantage. It seems reasonable not to worry about this issue in the first rev, or at least not to let it stop development of other features, so that it gas time to gel via discussion over time. Yes, I still think the most fundamental issue here is getting to the point where pg_dump dumps the right thing. The central aspect of that is a system for keeping track of which objects are part of an extension using pg_depend. ...Robert -- 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] Aggregate-function space leakage
Hitoshi Harada umi.tan...@gmail.com writes: So two ideas from Tom seem to me a little worse than that. Modifying Agg.c might add overhead to reset context group by group and forcing array_agg() (i.e. user aggregates) to distinguish hash-mode and group-mode is definitely heavy for users. I agree that the second choice would be a pain. I think you are overestimating the cost of the first choice though. We have already taken steps to ensure that MemoryContextReset is *extremely* cheap when there is nothing for it to do. If there is something for it to do, well, that's the case that we have a memory leak now. Also, resetting the context should be cheaper than retail pfree's anyway. Anyway, I'll go take a look at exactly what would be involved in the first choice. 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] Aggregate-function space leakage
Greg Stark gsst...@mit.edu writes: Rereading your diagnosis of Merlin Moncure's original problem I'm a bit puzzled. Why do we have to rerun the final function when we rescan the hash table? Surely the logical thing to do is to store the final value in the hash table with some flag saying that value has been finalized rather than to reexecute the final function every time it's rescanned. In the normal case where we're not going to do a rescan, this would very likely make things slower because we'd have to make a never-used extra copy of the function's output. It might be worth doing if we knew we were likely to get rescanned; but I'm not eager to have two significantly different operational modes for that. nodeAgg is complicated enough already... 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] join regression failure on cygwin
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: I see it claims to have working erand48, but maybe not so much in reality? Good guess. I removed erand48 from the configure file and added erand48.o to OBJS in src/port/Makefile and it suddenly sailed through. Hmm. So we need to figure out how to improve configure's check so that it rejects whatever broken version you've got ... Yeah. Any ideas? I'd hate just to exclude the system erand48 on Cygwin and then find out later it's broken on some other abstruse system. cheers andrew -- 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] Determining client_encoding from client locale
On Thu, Jul 23, 2009 at 1:24 PM, Peter Eisentrautpete...@gmx.net wrote: Which proves my point, because libpgport includes chkconfig.c. But this is just a workaround. yeah! actually the problem i had was because we need to add the -lpgport to use pg_get_encoding_from_locale and that is something that this patch introduced the other unrelated problem i had is my little knowledge about the search path of libraries, the minimun i need to compile the test program with the correct libpq is this: gcc -o test-libpq test-libpq.o -Wl,-rpath,'/usr/local/pgsql/head/lib' -L /usr/local/pgsql/head/lib -lpq -lpgport so, at least, the second problem is a documentation one,our docs says: When linking the final program, specify the option -lpq so that the libpq library gets pulled in, as well as the option -Ldirectory to point the compiler to the directory where the libpq library resides. (Again, the compiler will search some directories by default.) For maximum portability, put the -L option before the -lpq option. For example: cc -o testprog testprog1.o testprog2.o -L/usr/local/pgsql/lib -lpq which is clearly not accurate, we also need to add the -Wl,rpath stuff -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] mixed, named notation support
--On Donnerstag, März 05, 2009 08:41:28 +0100 Pavel Stehule
pavel.steh...@gmail.com wrote:
Hello
I did some cleaning on this feature, and I hope so I solve some Tom's
objections
features:
* PostgreSQL's specific syntax for named parameter: value AS name,
* Doesn't change rules for defaults,
* Get defaults for named, mixed notation in planner time.
Pavel, consider the following function:
CREATE OR REPLACE FUNCTION ftest(a int, b text)
RETURNS RECORD
LANGUAGE SQL
AS
$$
SELECT $1, $2 ;
$$;
#= SELECT ftest('blubb' AS b, 128 AS a);
ERROR: function ftest(unknown, integer) does not exist at character 8
#= SELECT ftest(128 AS a, 'abcd' AS b);
ftest
(128,abcd)
(1 row)
Isn't the first one supposed to work?
--
Thanks
Bernd
--
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] mixed, named notation support
2009/7/23 Bernd Helmle maili...@oopsware.de:
--On Donnerstag, März 05, 2009 08:41:28 +0100 Pavel Stehule
pavel.steh...@gmail.com wrote:
Hello
I did some cleaning on this feature, and I hope so I solve some Tom's
objections
features:
* PostgreSQL's specific syntax for named parameter: value AS name,
* Doesn't change rules for defaults,
* Get defaults for named, mixed notation in planner time.
Pavel, consider the following function:
CREATE OR REPLACE FUNCTION ftest(a int, b text)
RETURNS RECORD
LANGUAGE SQL
AS
$$
SELECT $1, $2 ;
$$;
#= SELECT ftest('blubb' AS b, 128 AS a);
ERROR: function ftest(unknown, integer) does not exist at character 8
#= SELECT ftest(128 AS a, 'abcd' AS b);
ftest
(128,abcd)
(1 row)
Isn't the first one supposed to work?
it is probably bug. I'll look on it tomorrow.
Pavel
--
Thanks
Bernd
--
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] Aggregate-function space leakage
I wrote: Anyway, I'll go take a look at exactly what would be involved in the first choice. Actually, it seems this way results in a net *savings* of code, because we can simply remove the code that was responsible for retail pfree'ing of the transition values. I suppose that code must have predated the introduction of MemoryContextReset, or it would have occurred to us to do it like this to begin with. I think that WindowAgg does not need any changes because it already does MemoryContextResetAndDeleteChildren(winstate-wincontext) at partition boundaries. Hitoshi, do you agree? regards, tom lane Index: src/backend/executor/nodeAgg.c === RCS file: /cvsroot/pgsql/src/backend/executor/nodeAgg.c,v retrieving revision 1.167 diff -c -r1.167 nodeAgg.c *** src/backend/executor/nodeAgg.c 17 Jun 2009 16:05:34 - 1.167 --- src/backend/executor/nodeAgg.c 23 Jul 2009 19:19:33 - *** *** 55,60 --- 55,62 * in either case its value need not be preserved. See int8inc() for an * example. Notice that advance_transition_function() is coded to avoid a * data copy step when the previous transition value pointer is returned. + * Also, some transition functions make use of the aggcontext to store + * working state. * * * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group *** *** 273,290 } /* -* If we are reinitializing after a group boundary, we have to free -* any prior transValue to avoid memory leakage. We must check not -* only the isnull flag but whether the pointer is NULL; since -* pergroupstate is initialized with palloc0, the initial condition -* has isnull = 0 and null pointer. -*/ - if (!peraggstate-transtypeByVal - !pergroupstate-transValueIsNull - DatumGetPointer(pergroupstate-transValue) != NULL) - pfree(DatumGetPointer(pergroupstate-transValue)); - - /* * (Re)set transValue to the initial value. * * Note that when the initial value is pass-by-ref, we must copy it --- 275,280 *** *** 911,920 } /* !* Clear the per-output-tuple context for each group */ ResetExprContext(econtext); /* * Initialize working state for a new input tuple group */ --- 901,915 } /* !* Clear the per-output-tuple context for each group, as well as !* aggcontext (which contains any pass-by-ref transvalues of the !* old group). We also clear any child contexts of the aggcontext; !* some aggregate functions store working state in such contexts. */ ResetExprContext(econtext); + MemoryContextResetAndDeleteChildren(aggstate-aggcontext); + /* * Initialize working state for a new input tuple group */ *** *** 1234,1240 * structures and transition values. NOTE: the details of what is stored * in aggcontext and what is stored in the regular per-query memory * context are driven by a simple decision: we want to reset the !* aggcontext in ExecReScanAgg to recover no-longer-wanted space. */ aggstate-aggcontext = AllocSetContextCreate(CurrentMemoryContext, --- 1229,1236 * structures and transition values. NOTE: the details of what is stored * in aggcontext and what is stored in the regular per-query memory * context are driven by a simple decision: we want to reset the !* aggcontext at group boundaries (if not hashing) and in ExecReScanAgg !* to recover no-longer-wanted space. */ aggstate-aggcontext = AllocSetContextCreate(CurrentMemoryContext, Index: src/backend/utils/adt/array_userfuncs.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/array_userfuncs.c,v retrieving revision 1.31 diff -c -r1.31 array_userfuncs.c *** src/backend/utils/adt/array_userfuncs.c 20 Jun 2009 18:45:28 - 1.31 --- src/backend/utils/adt/array_userfuncs.c 23 Jul 2009 19:19:33 - *** *** 539,545 /* * Make the result. We cannot release the ArrayBuildState because !* sometimes aggregate final functions are re-executed. */ result = makeMdArrayResult(state, 1, dims, lbs,
Re: [HACKERS] Determining client_encoding from client locale
On Mon, Jul 6, 2009 at 10:00 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Here's my first attempt at setting client_encoding automatically from locale. when i apply your patch and try to compile in windows i get this error dllwrap -o libpq.dll --dllname libpq.dll --def ./libpqdll.def fe-auth.o fe-connect.o fe-exec.o fe-misc.o fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o fe-secure.o libpq-events.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o crypt.o inet_aton.o strlcpy.o getaddrinfo.o open.o win32error.o snprintf.o win32.o pgsleep.o libpqrc.o pthread-win32.o -L../../../src/port -lshfolder -lwsock32 -lws2_32 -lsecur32 fe-connect.o: In function `PQsetClientEncoding':C:/msys/1.0/home/Administrador/pgsql/src/interfaces/libpq/fe-connect.c:3668: undefined reference to `pg_get_encoding_from_locale' fe-connect.o: In function `connectOptions2':C:/msys/1.0/home/Administrador/pgsql/src/interfaces/libpq/fe-connect.c:562: undefined reference to `pg_get_encoding_from_locale' collect2: ld returned 1 exit status c:\mingw\bin\dllwrap.exe: c:\mingw\bin\gcc exited with status 1 make[3]: *** [libpq.dll] Error 1 make[3]: Leaving directory `/home/Administrador/pgsql/src/interfaces/libpq' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/Administrador/pgsql/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/Administrador/pgsql/src' make: *** [all] Error 2 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] DefaultACLs
Peter Eisentraut wrote: On Thursday 23 July 2009 06:26:05 Petr Jelinek wrote: I'd still like to have opinion from one of the commiters on the VIEW problem which also affects grant on all patch ( see http://archives.postgresql.org/pgsql-hackers/2009-07/msg00957.php ) and I fear returned with feedback might prevent that until next commit fest. I see potential for confusion in that GRANT ON TABLE x works if x is a base table or a view, but GRANT ON ALL TABLES would not affect views. Maybe you need to make up a different syntax to affect only base tables, e.g., GRANT ON ALL BASE TABLES. That's not what I mean the problem is what is the best way of handling the views in implementation itself (there were IIRC 3 possible solutions devised and I don't think we have consensus on which is better). In short, 1. add ACL_OBJECT_VIEW into GrantObjectType enum and track that inside code 2. create new enum with table, view, function and sequence objects in it (that works well for DefaultACLs but not for GRANT ON ALL) 3. add some boolean into GrantStmt that would indicate that relation is a view (that works for GRANT ON ALL but does not solve anything for DefaultACLs) Currently DefaultACLs patch uses method 2 (because Stephen does not like method 1) and GRANT ON ALL patch uses method 1 and it might be better if both patches uses only one of those. If we went with method 1 we probably should just ditch GrantObjectType alltogether and work with subset of ObjectType as other commands do (I haven't found any reason for GrantObjectType to exist other than having single object type for both TABLE and VIEW). And If we choose not to use method 1 then we should probably go with 2 for DefaultACLs and 3 for GRANT ON ALL. That is unless somebody has a better solution. -- Regards Petr Jelinek (PJMODOS)
Re: [HACKERS] join regression failure on cygwin
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Hmm. So we need to figure out how to improve configure's check so that it rejects whatever broken version you've got ... Yeah. Any ideas? I'd hate just to exclude the system erand48 on Cygwin and then find out later it's broken on some other abstruse system. Seems like it would be useful to figure out exactly why it's failing. I don't personally have a problem with just forcing use of our own erand48 on Cygwin; it's not a lot of code and it would make the behavior of that build more like the MSVC build. But it's curious that such a simple library function is seemingly broken on Cygwin ... especially when their random() and srandom() evidently work. 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
[HACKERS] When is a record NULL?
How can a record be neither NULL or NOT NULL? try=# select ROW(1, NULL) IS NULL; ?column? -- f (1 row) try=# select ROW(1, NULL) IS NOT NULL; ?column? -- f (1 row) This makes it rather hard to tell, in PL/pgSQL, when I've fetched the last record from a cursor… Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: plpython3
http://github.com/jwp/postgresql-plpython3/tree/plpython3 [branch name: plpython3] [src/pl/plpython3] (Yeah, I'm going to try to move it to git.postgresql.org soon-ish) In a recent thread[1], Peter said: That also means that maintaining a separate, parallel code base for a Python 3 variant can only be acceptable if it gives major advantages. Here are the features that I plan/hope to implement before submitting any patch: * Native Typing [Python types that represent Postgres types] * Reworked function structure (Python modules, not function fragments) * Improved SQL interfaces (prepared statement objects[2]) * Better SRF support(?) (uses iterators, will support composites, vpc mat) * Direct function calls (to other Postgres functions) * IST support (with xact(): ...) * Full tracebacks for Python exceptions(CONTEXT support) * Cached bytecode (presuming a procache attributes patch would be acceptable[3]) The first two features are why a new PL should be incorporated. Native typing alone is that desirable because it allows for Postgres type semantics to be retained inside Python. Using conversion for some types--the existing solution in plpython--may not be desirable due to potential inconsistencies in value. A notable example is that Python's datetime.timedelta cannot support interval's month field. And from a performance perspective, creating Python objects representing a parameter is approximately the cost of allocating memory for a Python object and datumCopy. The second feature, function structure, is actually new to the PL. Originally PL/Py took a pl/python-like approach to triggers and functions. *Currently*, I want to change procedures to be Python modules with specific entry points used to handle an event. Mere invocation: main. Or, a trigger event: before_insert, after_insert, before_update, etc. So, a regular function might look like: CREATE OR REPLACE FUNCTION foo(int) RETURNS int LANGUAGE plpython3u AS $python$ import Postgres def main(i): return i $python$; Despite the signature repetition, this is an improvement for the user and the developer. The user now has an explicit initialization section that is common to Python(it's a module). The PL developer no longer needs to munge the source, and can work with common Python APIs to manage and introspect the procedure's module(...thinking: procedure settings..). A trigger function might look like: CREATE OR REPLACE FUNCTION trig() RETURNS TRIGGER LANGUAGE plpython3u AS $python$ import Postgres def check(i): ... def before_insert(new): ... def before_update(new, old): # The default action is for the manipulation to occur, # so users must explicitly raise FilterEvent in order to # stop a row from being inserted, updated, deleted. if check(new[column_name]): raise StopEvent() def after_delete(old): ... $python$; Thoughts? [...it still has a *long* ways to go =] [1] http://archives.postgresql.org/pgsql-hackers/2009-05/msg01376.php [2] http://python.projects.postgresql.org/docs/0.9/driver.html#prepared-statement-interface-points [3] http://archives.postgresql.org/pgsql-hackers/2006-05/ msg01160.php (I think a new column would be wise) -- 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] When is a record NULL?
On Jul 23, 2009, at 3:19 PM, David E. Wheeler wrote: How can a record be neither NULL or NOT NULL? try=# select ROW(1, NULL) IS NULL; ?column? -- f (1 row) try=# select ROW(1, NULL) IS NOT NULL; ?column? -- f (1 row) This makes it rather hard to tell, in PL/pgSQL, when I've fetched the last record from a cursor… Also: select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL); ?column? -- t As expected, but the IS NULL above is not expected (by this bunny, anyway). Best, David -- 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] When is a record NULL?
On Thu, 2009-07-23 at 15:19 -0700, David E. Wheeler wrote: How can a record be neither NULL or NOT NULL? You could do: not ROW(1, NULL) is null and not ROW(1, NULL) is not null I don't know what the SQL standard says about this. Regards, Jeff Davis -- 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] join regression failure on cygwin
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Hmm. So we need to figure out how to improve configure's check so that it rejects whatever broken version you've got ... Yeah. Any ideas? I'd hate just to exclude the system erand48 on Cygwin and then find out later it's broken on some other abstruse system. Seems like it would be useful to figure out exactly why it's failing. I don't personally have a problem with just forcing use of our own erand48 on Cygwin; it's not a lot of code and it would make the behavior of that build more like the MSVC build. But it's curious that such a simple library function is seemingly broken on Cygwin ... especially when their random() and srandom() evidently work. I'll work on it, but for now I propose to make the following change to configure.in and the corresponding change in configure: diff -u -r1.605 configure.in --- configure.in16 Jul 2009 17:43:52 - 1.605 +++ configure.in23 Jul 2009 22:39:19 - @@ -1249,7 +1249,7 @@ pgac_save_LIBS=$LIBS LIBS=`echo $LIBS | sed -e 's/-ledit//g' -e 's/-lreadline//g'` -AC_REPLACE_FUNCS([crypt erand48 getopt getrusage inet_aton random rint srandom strdup strerror strlcat strlcpy strtol strtoul]) +AC_REPLACE_FUNCS([crypt getopt getrusage inet_aton random rint srandom strdup strerror strlcat strlcpy strtol strtoul]) case $host_os in @@ -1262,6 +1262,12 @@ ;; esac +# Cygwin's erand48 sometimes hangs, so force use of ours +if test $PORTNAME = cygwin; then + AC_LIBOBJ(erand48) +else + AC_REPLACE_FUNCS([erand48]) +fi LIBS=$pgac_save_LIBS 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] When is a record NULL?
On Jul 23, 2009, at 4:08 PM, Tom Lane wrote: This is per SQL standard. IS NULL is true if *all* the record's fields are null; IS NOT NULL is true if *none* of them are. Yeah, it's a bit dubious, but that's what they said. And yet they're DISTINCT FROM each other if either or both contain NULLs and some other values? It seems to me that, to be consistent, it should be: select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL); ?column? -- f No? Best, David -- 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] When is a record NULL?
David E. Wheeler da...@kineticode.com writes: How can a record be neither NULL or NOT NULL? This is per SQL standard. IS NULL is true if *all* the record's fields are null; IS NOT NULL is true if *none* of them are. Yeah, it's a bit dubious, but that's what they said. 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] When is a record NULL?
David E. Wheeler da...@kineticode.com writes: And yet they're DISTINCT FROM each other if either or both contain NULLs and some other values? Well, that would depend on what the values were and in what columns... It seems to me that, to be consistent, it should be: select ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL); ?column? -- f Dunno how you can possibly come to that conclusion. Those row values are certainly distinct (according to both PG and the spec). 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] pg_dump Add dumping of comments on index columns
Brendan Jurd dire...@gmail.com writes: I've also done an initial review of the patch. Everything looks sane and the patch works as advertised. I made a couple of minor tweaks for code-style and comment consistency, and my version 3 is attached. I'm marking this patch Ready for Committer. Applied with minor revisions --- mostly, it leaked memory in the case of no comments, and the query wasn't very schema-safe. 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] WIP: to_char, support for EEEE format
Brendan Jurd escreveu: 2009/4/26 Brendan Jurd dire...@gmail.com: I've done some work updating Pavel's sci notation patch for to_char(). That patch again, now with a couple of minor tweaks to make it apply cleanly against the current HEAD. Here is my review. The patch applied without problems. The docs and regression tests are included. Both of them worked as expected. Also, you included a fix in RN format, do it in another patch. The behavior is not the same as Oracle. Oracle accepts an invalid scientific notation '999.9'. Will we support it too? I think so. euler=# SELECT to_char(1234.56789, '999.9'); ERRO: invalid format for scientific notation DETALHE: requires exactly one digit before the decimal point. DICA: For example, 9.999 is a valid format. TO_CHAR(1234.56789,'999.9') --- 1.2E+03 1 rows selected The '9.999' format error message is misleading. euler=# select to_char(123, '9.999'); ERRO: cannot use twice You could include an example in manual too. You could add the two failing cases above in regression tests too. -- Euler Taveira de Oliveira http://www.timbira.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] When is a record NULL?
On Jul 23, 2009, at 3:33 PM, Jeff Davis wrote: not ROW(1, NULL) is null and not ROW(1, NULL) is not null I don't know what the SQL standard says about this Thanks Jeff, that's just what I needed. Best, David -- 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] When is a record NULL?
On Jul 23, 2009, at 4:22 PM, Tom Lane wrote: Dunno how you can possibly come to that conclusion. Those row values are certainly distinct (according to both PG and the spec). Are they not both null? David -- 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] multi-threaded pgbench
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: Greg Smith gsm...@gregsmith.com wrote: That second code path, when --enable-thread-safety is turned off, crashes and burns on my Linux system: It comes from confliction of identifiers. Renaming identifiers with #define can solve the errors: #define pthread_t pg_pthread_t Here is a patch to fix compile errors by identifier-renaming when thread-safety is disabled on linux. Also I fixed file descriptor leaks at the end of benchmark. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgbench-mt_20090724.patch Description: Binary data -- 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] query decorrelation in postgres
mahendra chavan mah...@gmail.com wrote: I am a master's student in computer science at IIT Bombay. As part of my project, I need to get a decorrelated version of a SQL query. Please could anyone let me know if we have query decorrelation feature implemented in postgres ? What do you mean by query decorrelation? Is it an addtional method for query optimization? At least there is no word 'decorrelation' in the postgres documentation. 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] When is a record NULL?
On Fri, Jul 24, 2009 at 1:32 AM, David E. Wheelerda...@kineticode.com wrote: On Jul 23, 2009, at 4:22 PM, Tom Lane wrote: Dunno how you can possibly come to that conclusion. Those row values are certainly distinct (according to both PG and the spec). Are they not both null? Isn't that just what you were complaining about not being the case: On Thu, Jul 23, 2009 at 11:29 PM, David E. Wheelerda...@kineticode.com wrote: try=# select ROW(1, NULL) IS NULL; ?column? -- f (1 row) -- greg http://mit.edu/~gsstark/resume.pdf -- 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] join regression failure on cygwin
Andrew Dunstan and...@dunslane.net writes: I'll work on it, but for now I propose to make the following change to configure.in and the corresponding change in configure: I believe you can just add AC_LIBOBJ(erand48) in the Cygwin-specific section without touching the other part; that's supposed to be a no-op if the filename has already been added to LIBOBJS. 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] When is a record NULL?
On Thu, 2009-07-23 at 17:32 -0700, David E. Wheeler wrote: On Jul 23, 2009, at 4:22 PM, Tom Lane wrote: Dunno how you can possibly come to that conclusion. Those row values are certainly distinct (according to both PG and the spec). Are they not both null? [ Is that a play on words? ;) ] Here's the logical breakdown of your argument: x = ROW(1, NULL) 0. x IS NOT NULL = false 1. = NOT x IS NOT NULL = true 2. = NOT NOT x IS NULL = true 3. = x IS NULL = true However, in step 2, you transformed: x IS NOT NULL = NOT x IS NULL But in SQL that is not a tautology! I don't think it's wise to assume SQL is consistent. I think it is possible to create a consistent 3VL system, but you have to give up some other very important property. I can't remember the details at the moment, but there's an interesting proof in Logic and Databases by C.J. Date. Does the SQL spec lay out the tautologies anywhere, so that we can get a clear picture of what's going on with NULLs? I won't make the claim that SQL is inconsistent without actually seeing the system as a whole, but, at a minimum, many of the tautologies that people are accustomed to are not true in SQL. Regards, Jeff Davis -- 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] SE-PostgreSQL?
On Sat, Jul 18, 2009 at 12:06 PM, David Fetterda...@fetter.org wrote: At this point, SE-PostgreSQL has taken up a *lot* of community resources, not to mention an enormous and doubtless frustrating amount of Kohei-san's time and effort, thus far without a single committed patch, or even a consensus as to what it should (or could) do. Rather than continuing to blunder into the future, I think we need to do a reality check in the form of a couple of questions: 1. Among the committers who could maintain the features, whatever they turn out to be, who is actually volunteering to do so? 2. Apart from Kohei-san and Stephen Frost, is anybody actually interested in having this feature at all? I would submit that if we get fewer than three enthusiastic, me!s on the first, or fewer people than five on the second, we just need to bounce this feature and move on. As I see it, those numbers are a bare minimum, although one could fairly argue that I've underestimated the minimum for the second. I count zero for the first question and five for the second, although two of those five (Josh Berkus and Ron Mayer) expressed doubt about this patch set as an implementation of this feature, and only one person (Greg Williamson) volunteered to help. I think, though, that we have on the other thread gotten closer to a solution to some of the problems that have been plaguing this feature, including, in particular, the need for a clear spec and very complete docs. I think the best thing for this patch right now is to move it to Returned with Feedback. I can't see any way that this patch is going to be made committable for this CommitFest, and I think that pretending otherwise is only encouraging KaiGai to do another of his lighting rework-and-resubmits. While those are very impressive, they're not getting us where we need to be. I think that what KaiGai needs to do here is get the spec written (with the help of Greg Williamson and anyone else who is willing to pitch in), and submit it for comments. I don't think there will be a problem getting that reviewed outside of a CommitFest, and it's not a patch anyway, so the time that it gets submitted is not crucial. What is crucial is that it is a good spec that everyone can read, and hopefully understand and discuss. There is no point writing any more code, or submitting any more patches, until we have agreement on what those patches are supposed to do. I am going to go ahead and mark this as Returned with Feedback. ...Robert -- 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] When is a record NULL?
On Jul 23, 2009, at 6:21 PM, Greg Stark wrote: Are they not both null? Isn't that just what you were complaining about not being the case: Yes, but given that the standard says that `ROW(1, NULL)` is NULL, then I would expect it to be NOT DISTINCT from `ROW(2, NULL)`. Best, David -- 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] When is a record NULL?
On Jul 23, 2009, at 6:06 PM, Jeff Davis wrote: However, in step 2, you transformed: x IS NOT NULL = NOT x IS NULL But in SQL that is not a tautology! No, that's not the problem I see -- that solved the problem in my particular code. The problem I see is that, given that the standard says (according to Tom) that if any value is NULL then the record is NULL, then I would expect this to return false: SELECT ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL); But it returns true. I don't think it's wise to assume SQL is consistent. I think it is possible to create a consistent 3VL system, but you have to give up some other very important property. I can't remember the details at the moment, but there's an interesting proof in Logic and Databases by C.J. Date. If the standard says that, in the case of records, two NULLs are distinct, then fine. Completely bizarre, but fine. But I suspect that such is not the case. Best, David -- 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] When is a record NULL?
On Jul 23, 2009, at 6:52 PM, David E. Wheeler wrote: No, that's not the problem I see -- that solved the problem in my particular code. The problem I see is that, given that the standard says (according to Tom) that if any value is NULL then the record is NULL, then I would expect this to return false: SELECT ROW(1, NULL) IS DISTINCT FROM ROW(2, NULL); But it returns true. Sorry, I'm confused. Understandable I think. So: ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it is, but I guess that's the standard. In this case, IS DISTINCT FROM gives me a sensible return value -- it assumes that the records are NOT NULL, I guess, for its purposes. This is still inconsistent, since the records are neither NULL nor NOT NULL, but perhaps sensible. It's pretty insane, frankly. Gotta love SQL. Best, David -- 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] Determining client_encoding from client locale
On Mon, Jul 6, 2009 at 10:00 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Here's my first attempt at setting client_encoding automatically from locale. Sorry for the many mails on this issue.. i will do a recolect of my findings: 1) it introduces a dependency for -lpgport when compiling a client that uses libpq http://archives.postgresql.org/pgsql-hackers/2009-07/msg01511.php 2) It doesn't compile in windows http://archives.postgresql.org/pgsql-hackers/2009-07/msg01515.php 3) why do you need to modify psql at all? i think you need to send the patch with the api change first and the a second patch that changes client app that can use it -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] When is a record NULL?
2009/7/24 David E. Wheeler da...@kineticode.com: ROW(1, NULL) is neither NULL nor NOT NULL. I've no idea what state it is, but I guess that's the standard. Well, a ROW is an ordered set of values, each one of which may be either NULL or NOT NULL. It doesn't really make sense to talk about the ROW itself being NULL or NOT NULL, only its member values (but for extra confusion, contrast with the treatment of arrays, which can themselves be NULL). It does make sense, however, to talk about the ROW's member values being entirely NULL or entirely NOT NULL, and that's what the IS NULL and IS NOT NULL tests tell you about. I guess the spec authors figured they might as well make IS [NOT] NULL do something useful when applied to a row rather than throwing an error. I tend to agree. I hope that provides some clarity. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GeQo Pool Size and plan worth
Hi, While trying out query executions using the geqo, I could not understand the following output. The geqo paramaters are set to default values. (pool_size=0). When the pool_size and the worth of the plans was displayed, the following values were obtained, irrespective of the number of relations in the query. For a query with 50 relations: GEQO selected 250 pool entries, best 83859549630953568.00, midvl 83859549630953568.00, worst 83859549630953568.00. For a query with 20 relations: GEQO selected 250 pool entries, best 45771829382598713000.00, midvl 45771829382598713000.00, worst 45771829382598713000.00. For a query with 15 relations: GEQO selected 250 pool entries, best 6801044405876.81, midvl 6801044405876.81, worst 6801044405876.81. Please help me understand this. Regards Swati
Re: [HACKERS] display previous query string of idle-in-transaction
On Thu, Jun 04, 2009 at 10:22:41PM -0400, Robert Haas wrote: The only thing I don't like about this is that I think it's kind of a hack to shove the IDLE in transaction designation and the query string into the same database column. I've never liked having to write: select sum(1) from pg_stat_activity where current_query = 'IDLE in transaction'; ...and I like it even less if I now have to modify that query to use like. We should find some way to represent this as structured data... maybe make a separate column called idle that's a boolean, or something, and let the query column contain the most recent query (whether or not it's still executing). I like this idea a lot. Possibly it would be useful to have the end time of the last query too, then one could find idle sessions that were old and truly idle rather than just waiting for a busy client to send the next query. select ... from pg_stat_activity where idle and last_statement_endtime now() - interval '1 minute'; -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SE-PostgreSQL Specifications
Here is the initial draft of SE-PostgreSQL specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft I've described it from the scratch again with paying attention for the people knowing nothing about SELinux. In some points, it uses comparison between the database privilege mechanism and SE-PostgreSQL for easy understanding. Please point out, if ... - Its composition can be improved. - Here is not enough introductions for what user wants to know. - Here is too much explanations, more brief one will be available. - Here is not easy understandable for database folks. - Here is not enough English quality. - And so on... In addition, I would like to fix its specifications during the discussion. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
