Re: [HACKERS] PL/Python SQL error code pass-through
On 23/11/11 17:24, Mika Eloranta wrote:
Hi all,
[PL/Python in 9.1 does not preserve SQLSTATE of errors]
Oops, you're right, it's a regression from 9.0 behaviour.
The fix looks good to me, I changed one place to indent with tabs
instead of spaces and added a regression test.
I think this should be backpatched to 9.1, no?
Thanks for the report and the patch!
Cheers,
Jan
diff --git a/src/pl/plpython/expected/plpython_error.out b/src/pl/plpython/expected/plpython_error.out
index dbf19fd..bab07fb 100644
*** a/src/pl/plpython/expected/plpython_error.out
--- b/src/pl/plpython/expected/plpython_error.out
*** CONTEXT: PL/Python function specific_e
*** 351,356
--- 351,378
(1 row)
+ /* SPI errors in PL/Python functions should preserve the SQLSTATE value
+ */
+ CREATE FUNCTION python_unique_violation() RETURNS void AS $$
+ plpy.execute(insert into specific values (1))
+ plpy.execute(insert into specific values (1))
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$
+ begin
+ begin
+ perform python_unique_violation();
+ exception when unique_violation then
+ return 'ok';
+ end;
+ return 'not reached';
+ end;
+ $$ language plpgsql;
+ SELECT catch_python_unique_violation();
+ catch_python_unique_violation
+ ---
+ ok
+ (1 row)
+
/* manually starting subtransactions - a bad idea
*/
CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/expected/plpython_error_0.out b/src/pl/plpython/expected/plpython_error_0.out
index b2194ff..6cb2ed0 100644
*** a/src/pl/plpython/expected/plpython_error_0.out
--- b/src/pl/plpython/expected/plpython_error_0.out
*** CONTEXT: PL/Python function specific_e
*** 351,356
--- 351,378
(1 row)
+ /* SPI errors in PL/Python functions should preserve the SQLSTATE value
+ */
+ CREATE FUNCTION python_unique_violation() RETURNS void AS $$
+ plpy.execute(insert into specific values (1))
+ plpy.execute(insert into specific values (1))
+ $$ LANGUAGE plpythonu;
+ CREATE FUNCTION catch_python_unique_violation() RETURNS text AS $$
+ begin
+ begin
+ perform python_unique_violation();
+ exception when unique_violation then
+ return 'ok';
+ end;
+ return 'not reached';
+ end;
+ $$ language plpgsql;
+ SELECT catch_python_unique_violation();
+ catch_python_unique_violation
+ ---
+ ok
+ (1 row)
+
/* manually starting subtransactions - a bad idea
*/
CREATE FUNCTION manual_subxact() RETURNS void AS $$
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 93e8043..afd5dfc 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*** static char *PLy_procedure_name(PLyProce
*** 383,389
static void
PLy_elog(int, const char *,...)
__attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
! static void PLy_get_spi_error_data(PyObject *exc, char **detail, char **hint, char **query, int *position);
static void PLy_traceback(char **, char **, int *);
static void *PLy_malloc(size_t);
--- 383,389
static void
PLy_elog(int, const char *,...)
__attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
! static void PLy_get_spi_error_data(PyObject *exc, int *sqlerrcode, char **detail, char **hint, char **query, int *position);
static void PLy_traceback(char **, char **, int *);
static void *PLy_malloc(size_t);
*** PLy_spi_exception_set(PyObject *excclass
*** 4441,4447
if (!spierror)
goto failure;
! spidata = Py_BuildValue((zzzi), edata-detail, edata-hint,
edata-internalquery, edata-internalpos);
if (!spidata)
goto failure;
--- 4441,4447
if (!spierror)
goto failure;
! spidata = Py_BuildValue((izzzi), edata-sqlerrcode, edata-detail, edata-hint,
edata-internalquery, edata-internalpos);
if (!spidata)
goto failure;
*** PLy_elog(int elevel, const char *fmt,...
*** 4481,4486
--- 4481,4487
*val,
*tb;
const char *primary = NULL;
+ intsqlerrcode = 0;
char *detail = NULL;
char *hint = NULL;
char *query = NULL;
*** PLy_elog(int elevel, const char *fmt,...
*** 4490,4496
if (exc != NULL)
{
if (PyErr_GivenExceptionMatches(val, PLy_exc_spi_error))
! PLy_get_spi_error_data(val, detail, hint, query, position);
else if (PyErr_GivenExceptionMatches(val, PLy_exc_fatal))
elevel = FATAL;
}
--- 4491,4497
if (exc != NULL)
{
if (PyErr_GivenExceptionMatches(val, PLy_exc_spi_error))
! PLy_get_spi_error_data(val, sqlerrcode, detail, hint, query, position);
else if (PyErr_GivenExceptionMatches(val, PLy_exc_fatal))
elevel = FATAL;
}
*** PLy_elog(int elevel, const char *fmt,...
*** 4531,4537
PG_TRY();
{
ereport(elevel,
! (errmsg_internal(%s, primary ? primary : no
Re: [HACKERS] Notes on implementing URI syntax for libpq
On Nov 24, 2011, at 9:40 AM, Martijn van Oosterhout wrote: On Thu, Nov 24, 2011 at 08:59:56AM +0200, Alexander Shulgin wrote: How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. Uh, no it doesn't. -h localhost uses TCP/IP (try it). This is one piece of mysql magic we don't copy. If you want to use the socket you need to specify -h /tmp or wherever you keep it. Leaving out the -h parameter also uses UNIX domain sockets. Which does raise the valid question of how to represent that in URI syntax. SQLAlchemy (for example) doesn't try with it's URL syntax, to connect to a non-default UNIX socket, you need to create the URL object directly. How about the service option, that's a nice way of handling non-default socket options. Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. -- Alexey Klyukinhttp://www.commandprompt.com The PostgreSQL Company – Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] PostgreSQL fails to build with 32bit MinGW-w64
Hi PostgreSQL hackers,
support for Mingw-w64 compiler was added to postgres with commit 91812df.
Unfortunately only the 64 bit output is working right now. This issue was
already highlighted with initial patch in
http://archives.postgresql.org/pgsql-bugs/2011-07/msg00059.php
Mingw-w64 uses the same header files for 32 and 64 bit compiles. So the same
conditions apply to mingw-w32 bit as for the WIN64 case. In WIN64 WSAAPI is
defined to nothing, but in 32 bit to stdcall, so it needs to be used in the
accept-parameter check, too. Maybe you prefer PASCAL instead of WSAAPI in
configure.
I tested successful compilation for the following platforms:
- i686-w64-mingw32 - gcc v4.6.1
- x86_64-w64-mingw32 - gcc v4.6.1
- i586-mingw32msvc - gcc v4.4.4
- x86_64-linux-gnu - gcc v4.6.1
--
Kind regards,
Lars Kanis
diff --git a/config/ac_func_accept_argtypes.m4 b/config/ac_func_accept_argtypes.m4
index 1e77179..a82788d 100644
--- a/config/ac_func_accept_argtypes.m4
+++ b/config/ac_func_accept_argtypes.m4
@@ -46,7 +46,7 @@ AC_DEFUN([AC_FUNC_ACCEPT_ARGTYPES],
[AC_CACHE_VAL(ac_cv_func_accept_arg1,dnl
[AC_CACHE_VAL(ac_cv_func_accept_arg2,dnl
[AC_CACHE_VAL(ac_cv_func_accept_arg3,dnl
-[for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET'; do
+[for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET WSAAPI'; do
for ac_cv_func_accept_arg1 in 'int' 'unsigned int' 'SOCKET'; do
for ac_cv_func_accept_arg2 in 'struct sockaddr *' 'const struct sockaddr *' 'void *'; do
for ac_cv_func_accept_arg3 in 'int' 'size_t' 'socklen_t' 'unsigned int' 'void'; do
diff --git a/configure b/configure
index 58fea90..4118caf 100755
--- a/configure
+++ b/configure
@@ -18808,7 +18808,7 @@ else
if test ${ac_cv_func_accept_arg3+set} = set; then
$as_echo_n (cached) 6
else
- for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET'; do
+ for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET WSAAPI'; do
for ac_cv_func_accept_arg1 in 'int' 'unsigned int' 'SOCKET'; do
for ac_cv_func_accept_arg2 in 'struct sockaddr *' 'const struct sockaddr *' 'void *'; do
for ac_cv_func_accept_arg3 in 'int' 'size_t' 'socklen_t' 'unsigned int' 'void'; do
diff --git a/src/include/c.h b/src/include/c.h
index 0391860..040990f 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -58,17 +58,22 @@
#endif
#include postgres_ext.h
-#if _MSC_VER = 1400 || defined(WIN64)
-#define errcode __msvc_errcode
-#include crtdefs.h
-#undef errcode
-#endif
-
#include stdio.h
#include stdlib.h
#include string.h
#include stddef.h
#include stdarg.h
+
+/* __MINGW64_VERSION_MAJOR is related to both 32/64 bit gcc compiles by
+ * mingw-w64, however it gots defined only after
+ * #include any standard mingw header
+ */
+#if _MSC_VER = 1400 || defined(__MINGW64_VERSION_MAJOR)
+#define errcode __msvc_errcode
+#include crtdefs.h
+#undef errcode
+#endif
+
#ifdef HAVE_STRINGS_H
#include strings.h
#endif
diff --git a/src/include/port/win32.h b/src/include/port/win32.h
index 34f4004..d4acfae 100644
--- a/src/include/port/win32.h
+++ b/src/include/port/win32.h
@@ -31,7 +31,7 @@
* The Mingw64 headers choke if this is already defined - they
* define it themselves.
*/
-#if !defined(WIN64) || defined(WIN32_ONLY_COMPILER)
+#if !defined(__MINGW64_VERSION_MAJOR) || defined(WIN32_ONLY_COMPILER)
#define _WINSOCKAPI_
#endif
#include winsock2.h
diff --git a/src/port/getaddrinfo.c b/src/port/getaddrinfo.c
index db19878..60c522f 100644
--- a/src/port/getaddrinfo.c
+++ b/src/port/getaddrinfo.c
@@ -329,7 +329,7 @@ gai_strerror(int errcode)
return Not enough memory;
#endif
#ifdef EAI_NODATA
-#if !defined(WIN64) !defined(WIN32_ONLY_COMPILER) /* MSVC/WIN64 duplicate */
+#if !defined(__MINGW64_VERSION_MAJOR) !defined(WIN32_ONLY_COMPILER) /* MSVC/WIN64 duplicate */
case EAI_NODATA:
return No host data of that type was found;
#endif
--
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] PostgreSQL fails to build with 32bit MinGW-w64
Hi, Isn't it better to check the value of macros itsef rather than checking for system dependent macros that does not directly relate to the issue? specifically for getaddrinfo.c case I think #if EAI_NODATA != EAI_NONAME is a better check than checking for #if !defined(__MINGW64_VERSION_MAJOR) !defined(WIN32_ONLY_COMPILER) /* MSVC/WIN64 duplicate */ For the win32.h, I really don't understand why _WINSOCKAPI_ was defined before winsock2.h some google suggests that defining _WINSOCKAPI_ before windows.h prevents inclusion of winsock.h but that does not have relation to inclusion of winsock2.h and if winsock2.h is included first, it should be ok. If this guess is right, perhaps it could be better to remove the three lines. #if !defined(WIN64) || defined(WIN32_ONLY_COMPILER) #define _WINSOCKAPI_ #endif +/* __MINGW64_VERSION_MAJOR is related to both 32/64 bit gcc compiles by + * mingw-w64, however it gots defined only after Why not use __MINGW32__, which is defined without including any headers? On 2011/11/24, at 17:24, Lars Kanis wrote: Hi PostgreSQL hackers, support for Mingw-w64 compiler was added to postgres with commit 91812df. Unfortunately only the 64 bit output is working right now. This issue was already highlighted with initial patch in http://archives.postgresql.org/pgsql-bugs/2011-07/msg00059.php Mingw-w64 uses the same header files for 32 and 64 bit compiles. So the same conditions apply to mingw-w32 bit as for the WIN64 case. In WIN64 WSAAPI is defined to nothing, but in 32 bit to stdcall, so it needs to be used in the accept-parameter check, too. Maybe you prefer PASCAL instead of WSAAPI in configure. I tested successful compilation for the following platforms: - i686-w64-mingw32 - gcc v4.6.1 - x86_64-w64-mingw32 - gcc v4.6.1 - i586-mingw32msvc - gcc v4.4.4 - x86_64-linux-gnu - gcc v4.6.1 -- Kind regards, Lars Kanis fix-mingw-w64-32bit_v1.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] PostgreSQL fails to build with 32bit MinGW-w64
Hello, Lars. You wrote: LK Hi PostgreSQL hackers, LK LK support for Mingw-w64 compiler was added to postgres with commit LK 91812df. Unfortunately only the 64 bit output is working right LK now. This issue was already highlighted with initial patch in LK http://archives.postgresql.org/pgsql-bugs/2011-07/msg00059.php LK LK Mingw-w64 uses the same header files for 32 and 64 bit compiles. LK So the same conditions apply to mingw-w32 bit as for the WIN64 LK case. In WIN64 WSAAPI is defined to nothing, but in 32 bit to LK stdcall, so it needs to be used in the accept-parameter check, LK too. Maybe you prefer PASCAL instead of WSAAPI in configure. LK LK I tested successful compilation for the following platforms: LK - i686-w64-mingw32 - gcc v4.6.1 LK - x86_64-w64-mingw32 - gcc v4.6.1 LK - i586-mingw32msvc - gcc v4.4.4 LK - x86_64-linux-gnu - gcc v4.6.1 LK Can you please provide me with some howto on building PG sources with mingw-w64? -- With best wishes, Pavel mailto:pa...@gf.microolap.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] Notes on implementing URI syntax for libpq
Excerpts from Martijn van Oosterhout's message of Thu Nov 24 09:40:42 +0200 2011: On Thu, Nov 24, 2011 at 08:59:56AM +0200, Alexander Shulgin wrote: How would you specifiy a local port/UNIX domain socket? Missed that in my previous reply. If host part of the URI points to localhost, the UNIX domain socket would be considered by libpq just as if you would pass -h localhost -p 5433. Uh, no it doesn't. -h localhost uses TCP/IP (try it). This is one piece of mysql magic we don't copy. If you want to use the socket you need to specify -h /tmp or wherever you keep it. Leaving out the -h parameter also uses UNIX domain sockets. Oh, you're right -- I was under wrong impression (hacking in the wrong local install, you know.) Which does raise the valid question of how to represent that in URI syntax. SQLAlchemy (for example) doesn't try with it's URL syntax, to connect to a non-default UNIX socket, you need to create the URL object directly. Well, whatever syntax we're going to invent here: it is not supported by the JDBC driver. Because Java does not support using unix sockets the PostgreSQL™ server must be configured to allow TCP/IP connections. http://jdbc.postgresql.org/documentation/head/prepare.html Or, this has to be done not in the URI syntax itself, but with the use of some external option. Or maybe we can just add unixsocket=... and hope that JDBC simply ignores that? I think I will try the last option to see if that's the case. (Looking at libpq code, I think we will also need to verify that host/hostaddr parameter is pointing to the local host and reset it to NULL, to actually make libpq consider UNIX sockets.) How about the service option, that's a nice way of handling non-default socket options. The service handling isn't going to be affected with the proposed approach. So, if PGSERVICE is given, the options from the service file are applied after the URI is parsed, filling any parameters not set using previous methods. -- Alex -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Wire protocol: type-specific opt-in to binary format
I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. This doesn't have to be a flag on the wire protocol, it could be a regular run-time parameter. Do you think this would make sense? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Making TEXT NUL-transparent
Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). I haven't even looked how difficult it would be to implement this. Do you think it's worth the trouble? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Making TEXT NUL-transparent
Hello 2011/11/24 Florian Weimer fwei...@bfk.de: Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). and why you don't use bytea ? Text should be correct literal. Regards Pavel Stehule I haven't even looked how difficult it would be to implement this. Do you think it's worth the trouble? -- Florian Weimer fwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] PostgreSQL fails to build with 32bit MinGW-w64
Hi, 2011-11-24 09:55 keltezéssel, Pavel Golub írta: Can you please provide me with some howto on building PG sources with mingw-w64? Install Fedora 15 or 16, add this repo file into /etc/yum.repos.d : http://build1.openftd.org/fedora-cross/fedora-cross.repo Then yum install mingw*. This will install mingw64 both 32 and 64-bit. Replaces a lot of official mingw32 packages from Fedora. Then you can do for 32-bit: cd postgresql-9.1.1 mingw32-configure mingw32-make sudo mingw32-make install or for 64-bit: mingw64-configure mingw64-make sudo mingw64-make install Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de 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] Making TEXT NUL-transparent
* Pavel Stehule: Hello 2011/11/24 Florian Weimer fwei...@bfk.de: Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). and why you don't use bytea ? Text should be correct literal. It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown LINE 1: SELECT '\x006500'::bytea ~ 'A'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Making TEXT NUL-transparent
Excerpts from Florian Weimer's message of Thu Nov 24 11:27:51 +0200 2011: and why you don't use bytea ? Text should be correct literal. It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown And how will those TEXT functions behave on a value with an embedded NUL? Or is it not only about being able to *store* NULs in a text field? -- Alex -- 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] PostgreSQL fails to build with 32bit MinGW-w64
Can you please provide me with some howto on building PG sources with mingw-w64? For 32/64 bit mingw-v4.6.1 on ubuntu 11.10: apt-get install flex gcc-mingw-w64 ./configure --host=i686-w64-mingw32 --build=x86_64-linux --without-zlib make and ./configure --host=x86_64-w64-mingw32 --build=x86_64-linux --without-zlib make For 32 bit mingw-v4.4.4 on ubuntu 11.10: apt-get install flex gcc-mingw32 ./configure --host=i586-mingw32msvc --build=x86_64-linux --without-zlib make Regards, Lars Kanis
Re: [HACKERS] Making TEXT NUL-transparent
2011/11/24 Florian Weimer fwei...@bfk.de: * Pavel Stehule: Hello 2011/11/24 Florian Weimer fwei...@bfk.de: Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). and why you don't use bytea ? Text should be correct literal. It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown LINE 1: SELECT '\x006500'::bytea ~ 'A'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. then you can prepare some custom function that can filter zeroes and prepare valid text functions - some postgresql's functions (based on external libs) cannot to work with string containing zeroes too. Probably you can do it in plperl. I don't think so this functionality is generally requested. If you have broken strings, then you have to clean it before storing to database. Regards Pavel -- Florian Weimer fwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Making TEXT NUL-transparent
* Alexander Shulgin: It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown And how will those TEXT functions behave on a value with an embedded NUL? They need to be audited and fixed if necessary. I'm not saying that this would be a trivial change. Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Making TEXT NUL-transparent
2011/11/24 Florian Weimer fwei...@bfk.de: * Alexander Shulgin: It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown And how will those TEXT functions behave on a value with an embedded NUL? They need to be audited and fixed if necessary. I'm not saying that this would be a trivial change. Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. I have a different question. What is reason for embedded NULs inside strings? Regards Pavel Stehule -- Florian Weimer fwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] xpath_table
I noticed in the Postgres 9.1 manual that the xml2 module has been removed. I was looking to use the xpath_table functionality (making an xml doc look like a table so I could join it with other tables) but it looks like I can't. Is there another way to make an xml doc look like a table? Mike Bender 53 Year Old Mom Looks 33 The Stunning Results of Her Wrinkle Trick Has Botox Doctors Worried http://thirdpartyoffers.juno.com/TGL3131/4ecc2e3f9a155393b1est05vuc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time bug with small years
I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 (1 row) sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:17:32-05 (1 row) sk_test=# select '1896-01-01'::timestamp with time zone; timestamptz 1896-01-01 00:00:00-05 (1 row) sk_test=# show timezone; TimeZone --- localtime (1 row) sk_test=# set timezone= 'est5edt'; SET sk_test=# select '1895-01-01'::timestamp with time zone; timestamptz 1895-01-01 00:00:00-05 (1 row) sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz 1894-01-01 00:00:00-05 (1 row) I can duplicate with the exact same version of Pg on Intel hardware with kernel: Linux infongd2888 2.6.28.8-20101130b-iscsi-ntacker-fasync-mremap-amd-sec6-grsec #1 SMP Tue Nov 30 18:27:29 CET 2010 i686 GNU/Linux
Re: [HACKERS] Making TEXT NUL-transparent
* Pavel Stehule: By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. I have a different question. What is reason for embedded NULs inside strings? The source system does not enforce that constraint, so from time to time, such data slips through. I don't know why it's there in the first place, and I have no control over the original data source. Usually, it's okay to silently strip NUL bytes; it's just a bit awkward. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] PostgreSQL fails to build with 32bit MinGW-w64
Isn't it better to check the value of macros itsef rather than checking for
system dependent macros that does not directly relate to the issue?
specifically for getaddrinfo.c case I think
#if EAI_NODATA != EAI_NONAME
is a better check than checking for
#if !defined(__MINGW64_VERSION_MAJOR) !defined(WIN32_ONLY_COMPILER)
/* MSVC/WIN64 duplicate */
Yes it's better and it works for all described test environments.
For the win32.h, I really don't understand why _WINSOCKAPI_ was defined before
winsock2.h
some google suggests that defining _WINSOCKAPI_ before windows.h prevents
inclusion of winsock.h but that does not have relation to inclusion of
winsock2.h and if winsock2.h is included first, it should be ok.
If this guess is right, perhaps it could be better to remove the three lines.
#if !defined(WIN64) || defined(WIN32_ONLY_COMPILER)
#define _WINSOCKAPI_
#endif
I only changed this for consistency. For me, it works without that define in
all test
environments, too.
+/* __MINGW64_VERSION_MAJOR is related to both 32/64 bit gcc compiles by
+ * mingw-w64, however it gots defined only after
Why not use __MINGW32__, which is defined without including any headers?
At least in mingw32 v4.4.4 there is no crtdefs.h. I couldn't find a proper
define that relates directly
to that issue, so attached is a somewhat cumbersome MINGW version check.
--
Regards,
Lars Kanis
diff --git a/config/ac_func_accept_argtypes.m4 b/config/ac_func_accept_argtypes.m4
index 1e77179..a82788d 100644
--- a/config/ac_func_accept_argtypes.m4
+++ b/config/ac_func_accept_argtypes.m4
@@ -46,7 +46,7 @@ AC_DEFUN([AC_FUNC_ACCEPT_ARGTYPES],
[AC_CACHE_VAL(ac_cv_func_accept_arg1,dnl
[AC_CACHE_VAL(ac_cv_func_accept_arg2,dnl
[AC_CACHE_VAL(ac_cv_func_accept_arg3,dnl
-[for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET'; do
+[for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET WSAAPI'; do
for ac_cv_func_accept_arg1 in 'int' 'unsigned int' 'SOCKET'; do
for ac_cv_func_accept_arg2 in 'struct sockaddr *' 'const struct sockaddr *' 'void *'; do
for ac_cv_func_accept_arg3 in 'int' 'size_t' 'socklen_t' 'unsigned int' 'void'; do
diff --git a/configure b/configure
index de9ba5a..150ceb0 100755
--- a/configure
+++ b/configure
@@ -18808,7 +18808,7 @@ else
if test ${ac_cv_func_accept_arg3+set} = set; then
$as_echo_n (cached) 6
else
- for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET'; do
+ for ac_cv_func_accept_return in 'int' 'unsigned int PASCAL' 'SOCKET WSAAPI'; do
for ac_cv_func_accept_arg1 in 'int' 'unsigned int' 'SOCKET'; do
for ac_cv_func_accept_arg2 in 'struct sockaddr *' 'const struct sockaddr *' 'void *'; do
for ac_cv_func_accept_arg3 in 'int' 'size_t' 'socklen_t' 'unsigned int' 'void'; do
diff --git a/src/include/c.h b/src/include/c.h
index 0391860..db2cb60 100644
--- a/src/include/c.h
+++ b/src/include/c.h
@@ -58,7 +58,7 @@
#endif
#include postgres_ext.h
-#if _MSC_VER = 1400 || defined(WIN64)
+#if _MSC_VER = 1400 || (__MINGW32__ (__GNUC__ 4 || (__GNUC__ = 4 __GNUC_MINOR__ = 6)))
#define errcode __msvc_errcode
#include crtdefs.h
#undef errcode
diff --git a/src/include/port/win32.h b/src/include/port/win32.h
index 34f4004..2e72ecc 100644
--- a/src/include/port/win32.h
+++ b/src/include/port/win32.h
@@ -27,13 +27,6 @@
#undef ERROR
-/*
- * The Mingw64 headers choke if this is already defined - they
- * define it themselves.
- */
-#if !defined(WIN64) || defined(WIN32_ONLY_COMPILER)
-#define _WINSOCKAPI_
-#endif
#include winsock2.h
#include ws2tcpip.h
#include windows.h
diff --git a/src/port/getaddrinfo.c b/src/port/getaddrinfo.c
index db19878..721b335 100644
--- a/src/port/getaddrinfo.c
+++ b/src/port/getaddrinfo.c
@@ -328,12 +328,10 @@ gai_strerror(int errcode)
case EAI_MEMORY:
return Not enough memory;
#endif
-#ifdef EAI_NODATA
-#if !defined(WIN64) !defined(WIN32_ONLY_COMPILER) /* MSVC/WIN64 duplicate */
+#if defined(EAI_NODATA) EAI_NODATA != EAI_NONAME /* MSVC/WIN64 duplicate */
case EAI_NODATA:
return No host data of that type was found;
#endif
-#endif
#ifdef EAI_SERVICE
case EAI_SERVICE:
return Class type not found;
--
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] Making TEXT NUL-transparent
Excerpts from Florian Weimer's message of Thu Nov 24 12:59:09 +0200 2011: I have a different question. What is reason for embedded NULs inside strings? The source system does not enforce that constraint, so from time to time, such data slips through. I don't know why it's there in the first place, and I have no control over the original data source. Usually, it's okay to silently strip NUL bytes; it's just a bit awkward. Strip? Like this: loss\0less = lossless? That would be awkward for sure. -- 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] Making TEXT NUL-transparent
2011/11/24 Alexander Shulgin a...@commandprompt.com: Excerpts from Florian Weimer's message of Thu Nov 24 12:59:09 +0200 2011: I have a different question. What is reason for embedded NULs inside strings? The source system does not enforce that constraint, so from time to time, such data slips through. I don't know why it's there in the first place, and I have no control over the original data source. Usually, it's okay to silently strip NUL bytes; it's just a bit awkward. the source should be buggy application that has broken replace or concat of strings. Pavel Strip? Like this: loss\0less = lossless? That would be awkward for sure. -- 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] Notes on implementing URI syntax for libpq
On Nov 24, 2011, at 1:57 AM, Alexander Shulgin a...@commandprompt.com wrote: While it is really tempting to provide support for all that fancy stuff (or at least support user:password@host part instead of the ugly ?user=password=) this will make psql URIs backward-incompatible with the JDBC syntax, which is exactly what we want to avoid. I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. ...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] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 13:57:17 +0200 2011: I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. Well, I don't believe JDBC syntax is ideal either, but I don't recall any better option proposed in the original discussion: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01945.php Do you suggest that we should reconsider? -- Alex -- 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] Time bug with small years
On 24/11/11 04:45, Rod Taylor wrote: I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 Floating-point timestamps? Although I thought integer was the default for 9.x - hmm INSTALL says since 8.4 -- 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] pg_upgrade relation OID mismatches
On 24.11.2011 07:01, Bruce Momjian wrote: Bruce Momjian wrote: OK, that is a heap table. My only guess is that the heap is being created without binary_upgrade_next_heap_pg_class_oid being set. Looking at the code, I can't see how the heap could be created without this happening. Another idea is that pg_dumpall isn't output the proper value, but again, how is this data type different from the others. I have reproduced the failure and found it was code I added to pg_dump back in 9.0. The code didn't set the index oid for exclusion constraint indexes. Once these were added to the regression tests for range types recently, pg_upgrade threw an error. My assumption is that anyone trying to use an exclusion constraint with pg_upgrade will get the same type of error. Patch attached. Should it be backpatched to 9.0 and 9.1? If I understood correctly, pg_upgrade of a database with exclusion constraints won't work without this patch? In that case, it should be backpatched. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Alexander Shulgin's message of jue nov 24 05:58:57 -0300 2011: Excerpts from Martijn van Oosterhout's message of Thu Nov 24 09:40:42 +0200 2011: Which does raise the valid question of how to represent that in URI syntax. SQLAlchemy (for example) doesn't try with it's URL syntax, to connect to a non-default UNIX socket, you need to create the URL object directly. Well, whatever syntax we're going to invent here: it is not supported by the JDBC driver. Because Java does not support using unix sockets the PostgreSQL™ server must be configured to allow TCP/IP connections. http://jdbc.postgresql.org/documentation/head/prepare.html Or, this has to be done not in the URI syntax itself, but with the use of some external option. Or maybe we can just add unixsocket=... and hope that JDBC simply ignores that? I think this is misguided. We don't need to have a URL that specifies a Unix socket to work on JDBC, because it's obviously not going to work; if you just have it ignore the unixsocket bit, then the URI is no longer the same and you could have it connecting to a completely different server. I think we should just propose something that will not work in JDBC. Surely if the user wants an URL that works both in JDBC and libpq, they should just not use a Unix-domain-socket specifying URI in the first place. What about something like postgresql://path-to-dir:port/database where the are present, i.e. if you want to specify a different socket directory, postgresql:///var/run/postgresql:5433/database and if you just want to use the default location, postgresql://:5433/database A coworker also suggested using a different designator: postgresqli:///path/to/socket:5433/database postgresqli://:5433/database How about the service option, that's a nice way of handling non-default socket options. The service handling isn't going to be affected with the proposed approach. So, if PGSERVICE is given, the options from the service file are applied after the URI is parsed, filling any parameters not set using previous methods. I think the question is allowing the URI to specify a service. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath_table
On Tue, Nov 22, 2011 at 6:19 PM, magti...@juno.com magti...@juno.com wrote: I noticed in the Postgres 9.1 manual that the xml2 module has been removed. No, it hasn't. We talked about it, but we didn't do it. We're still planning to remove it three releases ago. http://www.postgresql.org/docs/9.1/static/xml2.html -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Notes on implementing URI syntax for libpq
On Thu, Nov 24, 2011 at 7:33 AM, Alexander Shulgin a...@commandprompt.com wrote: Excerpts from Robert Haas's message of Thu Nov 24 13:57:17 +0200 2011: I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. Well, I don't believe JDBC syntax is ideal either, but I don't recall any better option proposed in the original discussion: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01945.php Do you suggest that we should reconsider? I guess my feeling is that if we're going to have URLs, we ought to try to adhere to the same conventions that are used for pretty much every other service that supports URLs. user:pw@host:port is widely supported by multiple protocols, so I think we would need a very good reason to decide to go off in a completely different direction. It would be nice to be compatible with whatever JDBC does (link?) but I'm not prepared to put that ahead of general good design. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Making TEXT NUL-transparent
On Thu, Nov 24, 2011 at 4:54 AM, Florian Weimer fwei...@bfk.de wrote: By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. This seems like a key point. If UTF-8 allows embedded NULs, then a text field supposedly encoded as UTF-8 ought to allow them, too. However, our propensity to translate between text and cstring might make this difficult to implement in practice. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Notes on implementing URI syntax for libpq
Excerpts from Alvaro Herrera's message of Thu Nov 24 15:21:49 +0200 2011: I think the question is allowing the URI to specify a service. Huh? The service definitions are read from a local pg_service.conf, and are specified by setting PGSERVICE (and PGSERVICEFILE) environment variables, no? What would you do with such URI if you need to other people to connect to the same service? Send them URI along with the pg_service.conf? Or are we talking about different things completely? -- 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] Making TEXT NUL-transparent
On Nov24, 2011, at 10:54 , Florian Weimer wrote: Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. That's unlikely to happen. A more realistic approach would be to solve this only for UTF-8 encoded strings by encoding the NUL character not as a single 0 byte, but as sequence of non-0 bytes. Such a thing is possible in UTF-8 because there are multiple ways to encode the same character once you drop the requirement that characters be encoded in the *shortest* possible way. Since we very probably won't loosen up UTF-8's integrity checks to allow that, it'd have to be done as a new encoding, say 'utf8-loose'. That new encoding could, for example, use 0xC0 0x80 to represent NUL characters. This byte sequence is invalid in standard-conforming UTF-8 because it's a non-normalized (i.e. overly long) representation a code point (the code point NUL, incidentally). A bit of googling suggests that quite a few piece of software use this kind of modified UTF-8 encoding. Java, for example, seems to use it to serialize Strings (which may contain NUL characters) to UTF-8. Should you try to add a new encoding which supports that, you might also want to allow CESU-8-style encoding of UTF-16 surrogate pairs. This means that code points representable by UTF-16 surrogate pairs may be encoded by separately encoding the two surrogate characters in UTF-8. best regards, Florian Pflug -- 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] Time bug with small years
On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor p...@rbt.ca wrote: sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 (1 row) I believe that -05:17:32 is the offset of your local time zone as compared with UTC. For example: rhaas=# select now(); now -- 2011-11-24 13:46:46.68016+00 (1 row) rhaas=# set time zone 'Australia/Eucla'; SET rhaas=# select now(); now -- 2011-11-24 22:31:55.792565+08:45 (1 row) rhaas=# set time zone 'UTC'; SET rhaas=# select now(); now --- 2011-11-24 13:46:58.480484+00 (1 row) On my system, all current time zone offsets are multiples of 15 minutes, but historically that wasn't the case. It seems that in your local time zone, the offset versus UTC was, as of January 1, 1894, minus five hours, seventeen minutes, and 32 seconds. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 15:35:36 +0200 2011: Do you suggest that we should reconsider? I guess my feeling is that if we're going to have URLs, we ought to try to adhere to the same conventions that are used for pretty much every other service that supports URLs. user:pw@host:port is widely supported by multiple protocols, so I think we would need a very good reason to decide to go off in a completely different direction. It would be nice to be compatible with whatever JDBC does (link?) but I'm not prepared to put that ahead of general good design. What JDBC supports is rather weird and far from being ideal: http://jdbc.postgresql.org/documentation/head/connect.html The problem with supporting multiple syntaxes, IMO is that it makes libpq compatible in only one direction: from particular foreign syntax to libpq, but not from libqp to any other particular foreign syntax. So when you see psql -d URL you wouldn't know if you can copy that URL to JDBC or any other connection interface parameter, unless you check the docs thoroughly. -- Alex -- 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] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of jue nov 24 10:35:36 -0300 2011: On Thu, Nov 24, 2011 at 7:33 AM, Alexander Shulgin a...@commandprompt.com wrote: Excerpts from Robert Haas's message of Thu Nov 24 13:57:17 +0200 2011: I think it would be really weird not to support user:pw@host:port. You can presumably also support the JDBC style for backward compatibility, but I don't think we should adopt that syntax as project standard. Well, I don't believe JDBC syntax is ideal either, but I don't recall any better option proposed in the original discussion: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01945.php Do you suggest that we should reconsider? I guess my feeling is that if we're going to have URLs, we ought to try to adhere to the same conventions that are used for pretty much every other service that supports URLs. user:pw@host:port is widely supported by multiple protocols, so I think we would need a very good reason to decide to go off in a completely different direction. It would be nice to be compatible with whatever JDBC does (link?) but I'm not prepared to put that ahead of general good design. Apparently there's no standard: http://www.petefreitag.com/articles/jdbc_urls/ Supporting the usual user:pw@host convention, _in addition to_ what our own JDBC driver already supports, seems reasonable to me. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Martijn van Oosterhout's message of jue nov 24 04:40:42 -0300 2011: How about the service option, that's a nice way of handling non-default socket options. What about it? Are you suggesting we should support some way to specify a service name in the URI? If so, consider this: if you set up a pg_service.conf file, and then pass around a URI that specifies a service, no one else can use the URI until you also pass around the service file. So, in that light, do we still think that letting the user specify a service name in the URI makes sense? (My personal opinion is yes). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Storing hot members of PGPROC out of the band
On Thu, Nov 24, 2011 at 1:30 PM, Robert Haas robertmh...@gmail.com wrote: I'm going to run some more tests, but my thought is that we should probably leave the recentglobalxmin changes out for the time being, pending further study and consideration of other alternatives. Agreed -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Making TEXT NUL-transparent
On Thu, Nov 24, 2011 at 4:54 AM, Florian Weimer fwei...@bfk.de wrote: By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. Refuse away, but I don't think you know what you're asking for. Given the number of places that treat text values as being nul-terminated in the code, we'd be many years fixing bugs from a change like this, IMNSHO. cheers andrew
Re: [HACKERS] Notes on implementing URI syntax for libpq
On Thu, Nov 24, 2011 at 8:50 AM, Alexander Shulgin a...@commandprompt.com wrote: What JDBC supports is rather weird and far from being ideal: http://jdbc.postgresql.org/documentation/head/connect.html The problem with supporting multiple syntaxes, IMO is that it makes libpq compatible in only one direction: from particular foreign syntax to libpq, but not from libqp to any other particular foreign syntax. So when you see psql -d URL you wouldn't know if you can copy that URL to JDBC or any other connection interface parameter, unless you check the docs thoroughly. Well, based on that document, I think that trying to be bug-compatible with the JDBC syntax is a, erm, doomed effort. I mean, what are you going to do with things like loglevel or logUnclosedConnections that change the behavior of JDBC, not PostgreSQL? I think we could do something like: postgresql://user:pw@host:port/database?param1=val1param2=val2param3=val3... ...where the param and val bits are standard libpq connection parameters. And for compatibility you could allow user and password to be specified as connection parameters rather than included in the host portion of the string. But you're still not going to be 100% compatible with JDBC, because we're not going support unknownLenghth=42 in libpq just because JDBC has chosen to implement some weirdness in that area. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] bug of recovery?
On Tue, Oct 4, 2011 at 7:43 AM, Fujii Masao masao.fu...@gmail.com wrote: On Mon, Oct 3, 2011 at 4:32 PM, Simon Riggs si...@2ndquadrant.com wrote: I don't think this should use the rm_safe_restartpoint machinery. As you said, it's not tied to any specific resource manager. And I've actually been thinking that we will get rid of rm_safe_restartpoint altogether in the future. The two things that still use it are the b-tree and gin, and I'd like to change both of those to not require any post-recovery cleanup step to finish multi-page operations, similar to what I did with GiST in 9.1. I thought that was quite neat doing it that way, but there's no specific reason to do it that way I guess. If you're happy to rewrite the patch then I guess we're OK. I certainly would like to get rid of rm_safe_restartpoint in the longer term, hopefully sooner. Though Heikki might be already working on that,... anyway, the attached patch is the version which doesn't use rm_safe_restartpoint machinery. Heikki - I see you are down on the CF app to review this. I'd been working on it as well, just forgot to let Greg know. Did you start already? Should I stop? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Wire protocol: type-specific opt-in to binary format
On Nov24, 2011, at 10:03 , Florian Weimer wrote: I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. best regards, Florian Pflug -- 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] Notes on implementing URI syntax for libpq
Excerpts from Alvaro Herrera's message of jue nov 24 10:21:49 -0300 2011: A coworker also suggested using a different designator: postgresqli:///path/to/socket:5433/database postgresqli://:5433/database I forgot to mention: this i thing comes from LDAP. Apparently you can use ldapi:// to specify a Unix-domain socket connection. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 15:59:08 +0200 2011: Well, based on that document, I think that trying to be bug-compatible with the JDBC syntax is a, erm, doomed effort. I mean, what are you going to do with things like loglevel or logUnclosedConnections that change the behavior of JDBC, not PostgreSQL? The proposition was to ignore keywords not known to libpq (see top of this thread.) -- 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] Notes on implementing URI syntax for libpq
On Thu, Nov 24, 2011 at 8:54 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Martijn van Oosterhout's message of jue nov 24 04:40:42 -0300 2011: How about the service option, that's a nice way of handling non-default socket options. What about it? Are you suggesting we should support some way to specify a service name in the URI? If so, consider this: if you set up a pg_service.conf file, and then pass around a URI that specifies a service, no one else can use the URI until you also pass around the service file. So, in that light, do we still think that letting the user specify a service name in the URI makes sense? (My personal opinion is yes). service is just a connection parameter, so if we choose a URL format that allows any connection parameter to be specified, this falls out naturally, without any additional work. And if we don't choose such a URL format, we are, in my humble opinion, crazy. e.g. if we used the format suggested in my previous email, this would just boil down to: postgresql:///?service=foo -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Wire protocol: type-specific opt-in to binary format
* Florian Pflug: On Nov24, 2011, at 10:03 , Florian Weimer wrote: I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. Interesting. Doesn't this introduce another roundtrip? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Notes on implementing URI syntax for libpq
* Alvaro Herrera: I think we should just propose something that will not work in JDBC. I'm not sure if this is a good idea. 8-) I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Wire protocol: type-specific opt-in to binary format
On Nov24, 2011, at 15:04 , Florian Weimer wrote: * Florian Pflug: On Nov24, 2011, at 10:03 , Florian Weimer wrote: I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. Interesting. Doesn't this introduce another roundtrip? Yes. Maybe that's also something that should go on the next protocol version todo list. In any case, this is something that solely concerns the client library (libpq, JDBC, ...), not users of that library, and should thus be handled on the protocol level, not via a GUC. best regards, Florian Pflug -- 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] Notes on implementing URI syntax for libpq
Excerpts from Alexey Klyukin's message of Thu Nov 24 10:22:21 +0200 2011: Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. So the whole thing would look like this: postgresql://local:/dir/name/dbname?param1=val1... Where /dir/name is the absolute path to the directory containing the socket file. If one wants to use the default directory the following syntax may serve the need: postgresql://local:/dbname -- Alex -- 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] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of jue nov 24 11:31:29 -0300 2011: * Alvaro Herrera: I think we should just propose something that will not work in JDBC. I'm not sure if this is a good idea. 8-) I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). Well, in that case, the JDBC could simply adopt whatever syntax that libpq ends up adopting. I just meant something that will not work in JDBC *right now* (i.e. with no local socket support). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 16:02:38 +0200 2011: So, in that light, do we still think that letting the user specify a service name in the URI makes sense? (My personal opinion is yes). service is just a connection parameter, so if we choose a URL format that allows any connection parameter to be specified, this falls out naturally, without any additional work. And if we don't choose such a URL format, we are, in my humble opinion, crazy. The patch draft I have uses that format, yes: so any keyword libqp recognizes can be given in form of param=value URI query parameter. e.g. if we used the format suggested in my previous email, this would just boil down to: postgresql:///?service=foo Oh, well, that would make sense. It also appeared to me that we should deny overriding host, port and dbname by the query parameters to prevent confusion, e.g: postgresql://host:port/dbname?host=otherhostport=otherportdbname=otherdb -- Alex -- 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_upgrade relation OID mismatches
Heikki Linnakangas wrote: On 24.11.2011 07:01, Bruce Momjian wrote: Bruce Momjian wrote: OK, that is a heap table. My only guess is that the heap is being created without binary_upgrade_next_heap_pg_class_oid being set. Looking at the code, I can't see how the heap could be created without this happening. Another idea is that pg_dumpall isn't output the proper value, but again, how is this data type different from the others. I have reproduced the failure and found it was code I added to pg_dump back in 9.0. The code didn't set the index oid for exclusion constraint indexes. Once these were added to the regression tests for range types recently, pg_upgrade threw an error. My assumption is that anyone trying to use an exclusion constraint with pg_upgrade will get the same type of error. Patch attached. Should it be backpatched to 9.0 and 9.1? If I understood correctly, pg_upgrade of a database with exclusion constraints won't work without this patch? In that case, it should be backpatched. Yes, that is my guess. I will test it today or tomorrow. I am surprised we had _no_ exclusion constraint tests in the regression tests until now. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Notes on implementing URI syntax for libpq
Excerpts from Florian Weimer's message of Thu Nov 24 16:31:29 +0200 2011: I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). Do you maybe plan to support user:pw@host syntax too? :-) Apparently, that would make people happier, also JDBC and libpq URIs will become fully compatible (eventually.) -- Alex -- 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] Wire protocol: type-specific opt-in to binary format
* Florian Pflug: If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. Interesting. Doesn't this introduce another roundtrip? Yes. Maybe that's also something that should go on the next protocol version todo list. Okay. In any case, this is something that solely concerns the client library (libpq, JDBC, ...), not users of that library, and should thus be handled on the protocol level, not via a GUC. How is this different from the bytea_output GUC? libpq doesn't hide that at all, but the JDBC driver does---similar to the text/binary distinction. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Notes on implementing URI syntax for libpq
* Alvaro Herrera: Excerpts from Florian Weimer's message of jue nov 24 11:31:29 -0300 2011: * Alvaro Herrera: I think we should just propose something that will not work in JDBC. I'm not sure if this is a good idea. 8-) I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). Well, in that case, the JDBC could simply adopt whatever syntax that libpq ends up adopting. I just meant something that will not work in JDBC *right now* (i.e. with no local socket support). Ah, okay, your proposal looked like something which couldn't work with JDBC *at all* because of invalid URI syntax (but admittedly, I haven't checked that yet). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] Notes on implementing URI syntax for libpq
On Thu, Nov 24, 2011 at 9:40 AM, Alexander Shulgin a...@commandprompt.com wrote: Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. So the whole thing would look like this: postgresql://local:/dir/name/dbname?param1=val1... Where /dir/name is the absolute path to the directory containing the socket file. If one wants to use the default directory the following syntax may serve the need: postgresql://local:/dbname I think this is just weird. libpq treats any hostname that starts with a slash as hostname. And there's a standard way of URL-encoding characters that would otherwise be treated as terminators: you write a percent sign followed by two hex digits. So if you want the host to be /tmp, you just should just write: postgresql://%2Ftmp/fred Which is the equivalent of the connection string: host=/tmp dbname=fred This may appear to be slightly inconvenient notation, but there is little reason to reinvent syntax that the URL gods have already devised, and in practice specifying an explicit pathname in a connection string is quite rare. One normally specifies a local socket connection by omitting to specify a hostname at all, and that can work here, too. That is, postgresql:///fred should be equivalent to the connection string: dbname=fred ...which means it will use the default socket directory on UNIX, and a loopback connection on Windows. And postgresql:/// should be equivalent to an empty connection string, defaulting everything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] tsvector term positions and character offset
Hello, I'm working on text data, actually some tsvectors of the text. The tsvector provides terms and positions for each term, I would need to map these positions to the character offsets of the terms in the original text. 'This is an example text for example' tsvector - 'an':3 'exampl':4,7 'for':6 'is':2 'text':5 'this':1 What I need would be for the first term 'This' : 0, or the term 'example' : 11,28. I've searched for anything able to do that without success (also asked on general pg list). As the offset positions seem to be not stored or used at any time in the fulltext functions, the only way I figured out would be to parse the text again counting terms AND characters read. I coded this function as a very very dirty external C function, with many tsearch code copied because it can't be used outside of the source file. My questions 1) Is there any other way to achieve what I need ? 2) Could my need be part of future more general functionality of the tsearch module ? If not, any suggestion about the way to code it as clean and robust as possible ? Regards, Yoann Moreau -- 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] PL/Python SQL error code pass-through
On 24.11.2011 10:07, Jan Urbański wrote: On 23/11/11 17:24, Mika Eloranta wrote: Hi all, [PL/Python in 9.1 does not preserve SQLSTATE of errors] Oops, you're right, it's a regression from 9.0 behaviour. The fix looks good to me, I changed one place to indent with tabs instead of spaces and added a regression test. Thank you, both. Is there some other fields that we should propagate from the original error message that we're missing? Like, context and file/line information? Or are those left out on purpose? I wonder if we should have a more wholesale approach, and store the whole ErrorData struct somewhere, and only add some extra context information with errcontext(). I think this should be backpatched to 9.1, no? Yeah, it should. Your patch probably makes most sense for backpatching, even if we do something more radical on master. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
Excerpts from Robert Haas's message of Thu Nov 24 17:02:13 +0200 2011: On Thu, Nov 24, 2011 at 9:40 AM, Alexander Shulgin a...@commandprompt.com wrote: Another idea is to use local:/dir/name for UNIX domain socket instead of hostname:port, like it's displayed in the psql prompt. So the whole thing would look like this: postgresql://local:/dir/name/dbname?param1=val1... Where /dir/name is the absolute path to the directory containing the socket file. If one wants to use the default directory the following syntax may serve the need: postgresql://local:/dbname I think this is just weird. libpq treats any hostname that starts with a slash as hostname. And there's a standard way of URL-encoding characters that would otherwise be treated as terminators: you write a percent sign followed by two hex digits. So if you want the host to be /tmp, you just should just write: postgresql://%2Ftmp/fred Which is the equivalent of the connection string: host=/tmp dbname=fred Yeah, that should work, but it's giving the pathname a really weird look. Given that this is going to be used only rarely, this is less of a problem, though. This may appear to be slightly inconvenient notation, but there is little reason to reinvent syntax that the URL gods have already devised, and in practice specifying an explicit pathname in a connection string is quite rare. One normally specifies a local socket connection by omitting to specify a hostname at all, and that can work here, too. That is, postgresql:///fred should be equivalent to the connection string: dbname=fred ...which means it will use the default socket directory on UNIX, and a loopback connection on Windows. And postgresql:/// should be equivalent to an empty connection string, defaulting everything. Hm... that's neat. Didn't appear to me due to a bit too restrictive parser rules in my draft patch. Now that I allow host to be empty string, the above works like a charm! -- Alex -- 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_upgrade relation OID mismatches
Bruce Momjian wrote: Heikki Linnakangas wrote: On 24.11.2011 07:01, Bruce Momjian wrote: Bruce Momjian wrote: OK, that is a heap table. My only guess is that the heap is being created without binary_upgrade_next_heap_pg_class_oid being set. Looking at the code, I can't see how the heap could be created without this happening. Another idea is that pg_dumpall isn't output the proper value, but again, how is this data type different from the others. I have reproduced the failure and found it was code I added to pg_dump back in 9.0. The code didn't set the index oid for exclusion constraint indexes. Once these were added to the regression tests for range types recently, pg_upgrade threw an error. My assumption is that anyone trying to use an exclusion constraint with pg_upgrade will get the same type of error. Patch attached. Should it be backpatched to 9.0 and 9.1? If I understood correctly, pg_upgrade of a database with exclusion constraints won't work without this patch? In that case, it should be backpatched. Yes, that is my guess. I will test it today or tomorrow. I am surprised we had _no_ exclusion constraint tests in the regression tests until now. I do see EXCLUDE constraints in 9.0, so I need to do some more research: CREATE TABLE circles ( c1 CIRCLE, c2 TEXT, EXCLUDE USING gist (c1 WITH , (c2::circle) WITH ) WHERE (circle_center(c1) '(0,0)') ); It seems it is only the range-type EXCLUDE constraints that are causing a problem. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] RangeVarGetRelid()
On Fri, Nov 18, 2011 at 9:12 AM, Noah Misch n...@leadboat.com wrote: Good call. All right, here's an updated patch, and a couple of follow-on patches. I updated the main patch (rangevargetrelid-callback-v2.patch) per previous discussion. I also added a callback_arg argument to the RangeVarGetRelidExtended(), because it's a law of nature that all callback functions need such a thing, and this case proved to be no exception: the old version of the REINDEX INDEX callback was flaky, since it assumed that the index it locked during one iteration would still exist during the next iteration, which won't be true when the retry is caused by the index having been concurrently dropped. There were some other bugs as well, which I believe I've now fixed. fix-lock-table.patch applies over rangevargetrelid-callback-v2.patch and adjusts LOCK TABLE so that we never obtain a relation lock before validating that the object is a table and we have permission to lock it. fix-drop-relation.patch applies over that, and makes similar corrections to the logic for DROP TABLE/INDEX/SEQUENCE/VIEW/FOREIGN TABLE. This means that it's no longer possible to use these commands to launch a denial-of-service attack against a table you have no rights to. Sadly, there are a bunch of other commands that can be used the same way. I'd like to fix them all, but it's a decent amount of work, so I'm working through them one at a time. In the case of DROP, this also improves handling of concurrent DROP, DROP-and-CREATE, RENAME-and-CREATE, and similar situations. For example, in unpatched master: rhaas=# create table t (a int); CREATE TABLE rhaas=# begin; BEGIN rhaas=# drop table t; DROP TABLE rhaas=# create table t (b int); CREATE TABLE And then, from another session: rhaas=# drop table t; When the first session commits, you get something like this: ERROR: cache lookup failed for relation 16401 With these patches, that goes away: the concurrent DROP correctly sees the new relation and drops that one. Or, if the concurrent transaction just does a DROP rather than a DROP-and-CREATE, then you get an error - but instead of a somewhat incomprehensible complaint about a cache lookup having failed, you get the same error you would have gotten had the relation not existed in the first place: ERROR: table t does not exist -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company rangevargetrelid-callback-v2.patch Description: Binary data fix-lock-table.patch Description: Binary data fix-drop-relation.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] logging in high performance systems.
On Wed, Nov 23, 2011 at 11:45 PM, Greg Smith g...@2ndquadrant.com wrote: My assumption has been that eventually a lossy logger was going to be necessary for busier sites, I just haven't been suffering from one enough to hack on it yet. If it's possible to work this out in enough detail to figure out where the hooks go, and to prove they work with at least one consumer of them, I'd consider that a really useful thing to try and squeeze into 9.2. I think it's possible. I did both in my patches 1) placed hooks where logging exists today and 2) provided a useful consumer of them. and I tested it (only on a single system) under high simulated load. I see the next steps being: 1) agreeing that a problem exists (I know one does, but I suppose consensus is req'd) 2) agreeing that hooks are the right approach, if not propose a different approach. (fwiw, it's incredible common) 3) reworking the implementation to fit in the project; I assume the implementation I proposed will, at best, vaguely resemble anything that gets integrated. It was just a PoC. Also, I put the sample consumer in contrib in a separate commit, just to make it easy to review -- while I'm not against it, I am not proposing that a fifo logger be in contrib. The processing parts can always be further improved later based on production feedback, going along with my recent them of letting extensions that poke and probe existing hooks be one place to brew next version features at. I think that a generalized hook framework (like that offered by apr-utils) would be generally useful in cases like these. It makes it simple to add them and auto document them. But, I'm not proposing that here. I'm trying to keep the patch to logging so I can solve a critical production issue we seem to be running into more and more. Thanks for you time. -- Theo Schlossnagle http://omniti.com/is/theo-schlossnagle -- 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] Making TEXT NUL-transparent
Andrew Dunstan and...@dunslane.net writes: On Thu, Nov 24, 2011 at 4:54 AM, Florian Weimer fwei...@bfk.de wrote: By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. Refuse away, but I don't think you know what you're asking for. Given the number of places that treat text values as being nul-terminated in the code, we'd be many years fixing bugs from a change like this, IMNSHO. Yeah. This has been discussed before (see the archives) and every time we've concluded that the amount of pain and developer effort would outweigh the benefit by many orders of magnitude. Just to start with, we'd have to change the convention that datatype I/O functions take and return cstring, thus breaking every core and add-on datatype. If you really need to store nulls, use bytea. If there are specific operations you need that aren't available on bytea, we'd probably entertain proposals to add them. 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] Wire protocol: type-specific opt-in to binary format
Florian Weimer fwei...@bfk.de writes: I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. This doesn't have to be a flag on the wire protocol, it could be a regular run-time parameter. Do you think this would make sense? No. Especially not as a GUC. People have gotten this idea that they can whack behavior around arbitrarily under the control of some GUC or other, and that won't break things and create security holes. Generally that's way too optimistic, and I think it's way too optimistic in this particular case. As near as I can tell, you are proposing a GUC that will cause the server to flat-out violate the existing protocol document, ie send something in binary when the client's request clearly specified text (or perhaps vice versa). That is a recipe for breakage, not a recipe for making anybody's life better. It might be good to add some ability like this when we do the next set of protocol revisions. But trying to pretend that it's not a protocol change will lead to nothing but a dangerous kluge. 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] Time bug with small years
Robert Haas robertmh...@gmail.com writes: On Wed, Nov 23, 2011 at 11:45 PM, Rod Taylor p...@rbt.ca wrote: sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 (1 row) On my system, all current time zone offsets are multiples of 15 minutes, but historically that wasn't the case. It seems that in your local time zone, the offset versus UTC was, as of January 1, 1894, minus five hours, seventeen minutes, and 32 seconds. And in fact, a quick grep through the timezone database shows that you must be using America/Toronto: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] Zone America/Toronto-5:17:32 - LMT 1895 -5:00 Canada E%sT1919 -5:00 Toronto E%sT1942 Feb 9 2:00s -5:00 Canada E%sT1946 -5:00 Toronto E%sT1974 -5:00 Canada E%sT The general practice in the Olson database is to show localities as using local mean time until the year in which they adopted some form of standardized time. So local midnight of 1894-01-01 is in fact at an unusual (for us) offset from UTC. 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] proposal: use errcontext for custom exception too
Hello
There are small issue in PL/pgSQL and custom exceptions. Custom
exception doesn't set a CONTEXT field. I propose change this behave
for WARNING or EXCEPTION level. The goal is same behave for custom
exception and builtin exception and it can help to identify a RAISE
statement that is responsible to exception.
./pl_exec.c
*** ./pl_exec.c.orig2011-11-24 17:29:08.0 +0100
--- ./pl_exec.c 2011-11-24 18:23:51.513136718 +0100
***
*** 2827,2833
/*
* Throw the error (may or may not come back)
*/
! estate-err_text = raise_skip_msg; /* suppress traceback of raise
*/
ereport(stmt-elog_level,
(err_code ? errcode(err_code) : 0,
--- 2827,2834
/*
* Throw the error (may or may not come back)
*/
! if (stmt-elog_level WARNING)
! estate-err_text = raise_skip_msg; /* suppress traceback
of raise notice */
ereport(stmt-elog_level,
(err_code ? errcode(err_code) : 0,
Regards
Pavel Stehule
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-11-24 17:29:08.0 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2011-11-24 18:23:51.513136718 +0100
***
*** 2827,2833
/*
* Throw the error (may or may not come back)
*/
! estate-err_text = raise_skip_msg; /* suppress traceback of raise */
ereport(stmt-elog_level,
(err_code ? errcode(err_code) : 0,
--- 2827,2834
/*
* Throw the error (may or may not come back)
*/
! if (stmt-elog_level WARNING)
! estate-err_text = raise_skip_msg; /* suppress traceback of raise notice */
ereport(stmt-elog_level,
(err_code ? errcode(err_code) : 0,
*** ./src/test/regress/expected/plpgsql.out.orig 2011-11-24 17:32:30.0 +0100
--- ./src/test/regress/expected/plpgsql.out 2011-11-24 18:26:30.0 +0100
***
*** 1518,1544
DETAIL: Key (name)=(PF1_1) already exists.
update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
ERROR: WS.not.there does not exist
! CONTEXT: PL/pgSQL function tg_backlink_a() line 17 at assignment
update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
ERROR: illegal backlink beginning with XX
! CONTEXT: PL/pgSQL function tg_backlink_a() line 17 at assignment
update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
ERROR: PS.not.there does not exist
! CONTEXT: PL/pgSQL function tg_slotlink_a() line 17 at assignment
update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
ERROR: illegal slotlink beginning with XX
! CONTEXT: PL/pgSQL function tg_slotlink_a() line 17 at assignment
insert into HSlot values ('HS', 'base.hub1', 1, '');
ERROR: duplicate key value violates unique constraint hslot_name
DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.
insert into HSlot values ('HS', 'base.hub1', 20, '');
ERROR: no manual manipulation of HSlot
delete from HSlot;
ERROR: no manual manipulation of HSlot
insert into IFace values ('IF', 'notthere', 'eth0', '');
ERROR: system notthere does not exist
insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
ERROR: IFace slotname IF.orion.ethernet_interface_name_too_long too long (20 char max)
--
-- The following tests are unrelated to the scenario outlined above;
-- they merely exercise specific parts of PL/pgSQL
--- 1518,1552
DETAIL: Key (name)=(PF1_1) already exists.
update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
ERROR: WS.not.there does not exist
! CONTEXT: PL/pgSQL function tg_backlink_set(character,character) line 30 at RAISE
! PL/pgSQL function tg_backlink_a() line 17 at assignment
update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
ERROR: illegal backlink beginning with XX
! CONTEXT: PL/pgSQL function tg_backlink_set(character,character) line 47 at RAISE
! PL/pgSQL function tg_backlink_a() line 17 at assignment
update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
ERROR: PS.not.there does not exist
! CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 30 at RAISE
! PL/pgSQL function tg_slotlink_a() line 17 at assignment
update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
ERROR: illegal slotlink beginning with XX
! CONTEXT: PL/pgSQL function tg_slotlink_set(character,character) line 77 at RAISE
! PL/pgSQL function tg_slotlink_a() line 17 at assignment
insert into HSlot values ('HS', 'base.hub1', 1, '');
ERROR: duplicate key value violates unique constraint hslot_name
DETAIL: Key (slotname)=(HS.base.hub1.1 ) already exists.
insert into HSlot values ('HS', 'base.hub1', 20, '');
ERROR: no manual manipulation of HSlot
+ CONTEXT: PL/pgSQL function tg_hslot_biu() line 12 at RAISE
delete from HSlot;
ERROR: no manual manipulation
Re: [HACKERS] [JDBC] Optimize postgres protocol for fixed size arrays
On 11/24/2011 02:36 AM, Kevin Grittner wrote: Oliver Jowett wrote: Can we get a mechanism for minor protocol changes in this future version? Something as simple as exchanging a list of protocol features during the initial handshake (then use only features that are present on both sides) would be enough. The difficulty of making any protocol changes at the moment is a big stumbling block. I've been thinking the same thing. Any new protocol should include a way for each side to publish a list of what it can accept from the other during initial handshaking. (You could probably retrofit that to the current protocol version) Perhaps. It would be great if both sides could recognize the case where the feature negotiation was absent and use a default feature list for the protocol available on the other end. What about a hand-shake protocol based on simple binary-protocol minor version instead of features. We keep the v3 protocol as is but can add cumulative conditionally enabled features when we bump the minor version. The hand shake requires that the server sends a parameter back with it's highest supported minor version: FE= StartupPacket =BE ParameterStatus(binary_minor = 23) And the client can send any number between 1=binary_minor back to enable newer protocol versions and/or limit what the server sends FE= Execute(SET binary_minor = 20) To keep full backwards compatibility: 1) if backend does not send a binary_minor parameter on connection the highest supported minor version is assumed to be 0 (current format) 2) the backend assumes the active minor version is 0 unless the SET binary_minor is received I think bumping a minor version is better than feature flags because: 1) the hand shake is simpler and faster 2) coding is easier as all previous features are known to be supported and active when implementing feature+1 I'm not exactly sure about the COPY BINARY feature Tom mentioned. But probably we could prefix the data with the int4 containing the minor version? -Mikko -- 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] Obstacles to user-defined range canonicalization functions
On Wed, 2011-11-23 at 22:33 -0500, Tom Lane wrote: * The underlying range_serialize function is only exposed at the C level. If you try to write something in, say, plpgsql then you are going to end up going through range_constructorN or range_in to produce your result value, and those call the type's canonical function. Infinite recursion, here we come. That seems solvable, unless I'm missing something. * The only way to create a canonicalization function in advance of declaring the range type is to declare it against a shell type. But the PL languages all reject creating PL functions that take or return a shell type. Maybe we could relax that, but it's nervous-making, and anyway the first problem still remains. That seems a little more challenging. One possibility that just came to me is to decree that every discrete range type has to be based on an underlying continuous range type (with all the same properties except no canonicalization function), and then the discrete range's canonicalization function could be declared to take and return the underlying range type instead of the discrete type itself. Haven't worked through the details though. An interesting approach. I wonder if there would be a reason to tie such types together for a reason other than just the canonical function? Would you have to define everything in terms of the continuous range, or could it be a constraint hierarchy; e.g. a step size 100 is based on a step size of 10 which is based on numeric? 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
[HACKERS] Better implementation of CREATE TABLE AS ... WITH NO DATA
In http://archives.postgresql.org/message-id/52ccaa8f414ba8anzai-na...@mxu.nes.nec.co.jp it's pointed out that our documentation claims the WITH [NO] DATA option works for all variants of CREATE TABLE AS. But in fact it's only implemented for the SelectStmt variant, not for the ExecuteStmt variant, and the ugly kluge used for SelectStmt (paste on a LIMIT 0 at parse time) isn't going to work for the ExecuteStmt case. What I'm considering doing to improve this is to add a bool field skipData or some such to IntoClause. By default that would be false, but the CREATE TABLE AS productions could set it true when they have WITH NO DATA. Then we'd need to add an execution-time test; the most expedient way seems to be to add something like this to ExecutorRun: if (estate-es_select_into queryDesc-plannedstmt-intoClause-skipData) direction = NoMovementScanDirection; which will cause it to skip the ExecutePlan call. In the normal case this adds just one boolean test to ExecutorRun, so I don't think it represents a measurable slowdown. The EXECUTE code path needs no changes since it's just passing the IntoClause through to the executor. While at it, it occurs to me that we could handle the IntoClause.colNames option for the ExecuteStmt case if we were to move the responsibility for plastering on the substitute column names into the executor's OpenIntoRel, instead of making parse analysis do this (which is also pretty klugy, see applyColumnNames callers). Any objections or better ideas? 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] Notes on implementing URI syntax for libpq
On tor, 2011-11-24 at 09:02 -0500, Robert Haas wrote: e.g. if we used the format suggested in my previous email, this would just boil down to: postgresql:///?service=foo More correct would be postgresql:?service=foo See http://en.wikipedia.org/wiki/URI_scheme for some inspiration. -- 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] Notes on implementing URI syntax for libpq
On tor, 2011-11-24 at 15:43 +0200, Alexander Shulgin wrote: Huh? The service definitions are read from a local pg_service.conf, and are specified by setting PGSERVICE (and PGSERVICEFILE) environment variables, no? What would you do with such URI if you need to other people to connect to the same service? Send them URI along with the pg_service.conf? A full URI would also rely on host names or IP addresses being the same everywhere. It's all a matter of degree ... -- 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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Excerpts from Alvaro Herrera's message of vie nov 11 00:32:33 -0300 2011: Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011: On 10 November 2011 23:56, Thom Brown t...@linux.com wrote: The dump correctly contains: CREATE TABLE a ( num integer, CONSTRAINT meow CHECK ((num 20)) NOT VALID ); Actually I mean incorrectly contains, because the constraint needs adding after the data insertion, not as part of the create table statement. Interesting, thanks -- I'll look into it. I have just pushed a fix for this. Thanks for the report and sorry for the delay. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python SQL error code pass-through
On 24/11/11 16:15, Heikki Linnakangas wrote: On 24.11.2011 10:07, Jan Urbański wrote: On 23/11/11 17:24, Mika Eloranta wrote: Hi all, [PL/Python in 9.1 does not preserve SQLSTATE of errors] Oops, you're right, it's a regression from 9.0 behaviour. The fix looks good to me, I changed one place to indent with tabs instead of spaces and added a regression test. Thank you, both. Is there some other fields that we should propagate from the original error message that we're missing? Like, context and file/line information? Or are those left out on purpose? I wonder if we should have a more wholesale approach, and store the whole ErrorData struct somewhere, and only add some extra context information with errcontext(). In case of SPI errors we're preserving the following from the original ErrorData: * sqlerrcode (as of Mika's patch) * detail * hint * query * internalpos that leaves us with the following which are not preserved: * message * context * detail_log The message is being constructed from the Python exception name and I think that's useful. The context is being taken by the traceback string. I'm not sure if detail_log is ever set in these types of errors, probably not? So I guess we're safe. The problem with storing the entire ErrorData struct is that this information has to be transformed to Python objects, because we attach it to the Python exception that gets raised and in case it bubbles all the way up to the topmost PL/Python function, we recover these Python objects and use them to construct the ereport call. While the exception is inside Python, user code can interact with it, so it'd be hard to have C pointers to non-Python stuff there. Cheers, Jan -- 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] [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
On 24 November 2011 21:50, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alvaro Herrera's message of vie nov 11 00:32:33 -0300 2011: Excerpts from Thom Brown's message of jue nov 10 21:28:06 -0300 2011: On 10 November 2011 23:56, Thom Brown t...@linux.com wrote: The dump correctly contains: CREATE TABLE a ( num integer, CONSTRAINT meow CHECK ((num 20)) NOT VALID ); Actually I mean incorrectly contains, because the constraint needs adding after the data insertion, not as part of the create table statement. Interesting, thanks -- I'll look into it. I have just pushed a fix for this. Thanks for the report and sorry for the delay. Thanks :) Thom -- 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] [JDBC] Optimize postgres protocol for fixed size arrays
On 25 November 2011 07:54, Mikko Tiihonen mikko.tiiho...@nitorcreations.com wrote: =BE ParameterStatus(binary_minor = 23) FE= Execute(SET binary_minor = 20) Yeah this was almost exactly what I was thinking about how to retrofit it, except it might be clearer to have, say, supported_binary_minor (read-only, advertised by the server on startup) vs. binary_minor (read-write, defaults to 0) as otherwise you have special behavior for just one parameter where the advertised version doesn't actually match the currently-set version. Re list vs. always-incrementing minor version, you could just use an integer and set bits to represent features, which would keep it simple but also let clients be more selective about which features they implement (you could support feature 21 and 23 without supporting 22) Oliver -- 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] Inlining comparators as a performance optimisation
On 23 November 2011 19:24, Robert Haas robertmh...@gmail.com wrote: Well, right now the decision as to which mechanism should be used here gets made in tuplesort_performsort(), which has no good way of communicating with EXPLAIN anyway. You could pretty easily add something to Tuplesortstate to accomplish this. That isn't an endorsement of doing so, but I'm not sure that it isn't appropriate. Actually, I think that's a modularity violation; using the address of comparetup_heap as a flag value seems quite ugly. How about moving that logic up to tuplesort_begin_heap() I'll post a patch soon that does just that in the next day or two. Tuplesortstate has a pointer to a sort specialisation. and having it set some state inside the Tuplesort, maybe based on a flag in the opclass (or would it have to attach to the individual operator)? I'm not sure that there's much point in such a flag. At least on my machine, your latest patch reliably crashes the regression tests in multiple places. TRAP: FailedAssertion(!(state-nKeys == 1), File: tuplesort.c, Line: 1261); Yes, sorry about that. Should have been discriminating against nKeys 1 cases. As of this evening, for sorts with multiple scankeys, I'm using optimisations for the first scankey but not subsequent scankeys, which is frequently almost as good as having optimisations for all scanKeys. The formatting of src/include/utils/template_qsort_arg.h is hard to read. At ts=8, the backslashes line up, but the code doesn't fit in 80 columns. If you set ts=4, then it fits in 80 columns, but the backslashes don't line up any more, and the variable declarations don't either. I believe ts=4 is project standard. Fair enough. My working copy and .vimrc have been updated. I still think it would be a good idea to provide a mechanism to override heap_comparetup() with a type-specific function. I don't think that would take much extra code, and then any data type could get at least that much benefit out of this. It seems like it could be a good idea to do some per-assembler-instruction profiling of this code, and perhaps also of the original code. I'm curious where the time is being spent. How would you go about doing that? The instrumentation that profilers use actually caused a big drop in performance here when I attempted it a few weeks ago. There's a kind of Heisenberg effect. This optimisation *more than doubles* raw sort performance for the cases. There is nothing contrived or cherry picked about the query that I selected to represent this optimisation - it was literally the first one that I selected. Sometimes, I see even a markedly better gain than a doubling of raw sort performance - I think my earlier experiments that indicated a much smaller improvement past a certain point may have been methodologically flawed. Sorry about that. If I double-up the data in the orderlines table a few times, until it reaches 385 MB (duplicate ever tuple with an insert into ...select ), then warm the cache, I get very interesting results. Here, we see a few runs of the same old query unoptimised (note that I've excluded some cold-cache runs before these runs): Before optimisation == Total runtime: 7785.473 ms - 3.517310 secs just sorting Total runtime: 8203.533 ms - 3.577193 secs just sorting Total runtime: 8559.743 ms - 3.892719 secs just sorting Total runtime: 9032.564 ms - 3.844746 secs just sorting Total runtime: 9637.179 ms - 4.434431 secs just sorting Total runtime: 9647.215 ms - 4.440560 secs just sorting Total runtime: 9669.701 ms - 4.448572 secs just sorting After optimisation == Total runtime: 5462.419 ms - 1.169963 secs just sorting Total runtime: 5510.660 ms - 1.234393 secs just sorting Total runtime: 5511.703 ms - 1.208377 secs just sorting Total runtime: 5588.604 ms - 1.175536 secs just sorting Total runtime: 5899.496 ms - 1.250403 secs just sorting Total runtime: 6023.132 ms - 1.338760 secs just sorting Total runtime: 6717.177 ms - 1.486602 secs just sorting This is a 800109kB sort. So, taking the median value as representative here, that looks to be just shy of a 40% improvement, or 3.4 seconds. My /proc/cpuinfo is attached on the off chance that someone is interested in that. More work is needed here, but this seems promising. It will be interesting to see how far all of this can be taken with comparetup_index_btree. Certainly, I'm sure there's some gain to be had there by applying lessons learned from comparetup_heap. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Core(TM)2 Duo CPU E6750 @ 2.66GHz stepping: 11 cpu MHz : 2000.000 cache size : 4096 KB physical id : 0 siblings: 2 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0
Re: [HACKERS] proposal : backend startup hook / after logon trigger
2011/11/10 Tomas Vondra t...@fuzzy.cz: Is there any particular reason why there's not a backend start hook, executed right after a backend is initialized? I've tried a very simple PoC (basically just a new hook definition, called from PostgresMain(), see the after-logon-hook.diff (and a simple module that uses it is in logon.c). This obviously is not a complete patch or something, but is there a good reason why this is a stupid idea? [ catching up on some old email ] I've thought of this before, but I'm not exactly clear on what the use cases are. The particular place where you've put this hook doesn't look right to me. I think it would get re-executed after each ERROR. Also, if you're going to insist that the triggers be written in C (blech!) then there's not much point in including a TRY/CATCH block here. Let the user do that themselves if they are so inclined; it's not free. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] logging in high performance systems.
On Wed, Nov 23, 2011 at 9:28 PM, Theo Schlossnagle je...@omniti.com wrote: Thoughts? Feedback? Can you add it to the next CommitFest? https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] proposal: use errcontext for custom exception too
On Thu, Nov 24, 2011 at 12:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: There are small issue in PL/pgSQL and custom exceptions. Custom exception doesn't set a CONTEXT field. I propose change this behave for WARNING or EXCEPTION level. The goal is same behave for custom exception and builtin exception and it can help to identify a RAISE statement that is responsible to exception. That seems completely arbitrary. I think we discussed before providing an option to allow the user to control this, which seems better than implementing some hardcoded rule that may or may not be what a given user wants. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] PostgreSQL fails to build with 32bit MinGW-w64
On 11/24/2011 04:39 AM, Lars Kanis wrote: Can you please provide me with some howto on building PG sources with mingw-w64? For 32/64 bit mingw-v4.6.1 on ubuntu 11.10: apt-get install flex gcc-mingw-w64 ./configure --host=i686-w64-mingw32 --build=x86_64-linux --without-zlib make and ./configure --host=x86_64-w64-mingw32 --build=x86_64-linux --without-zlib make For 32 bit mingw-v4.4.4 on ubuntu 11.10: apt-get install flex gcc-mingw32 ./configure --host=i586-mingw32msvc --build=x86_64-linux --without-zlib make The only thing I at least am going to be interested in supporting is a native compiler, not a cross-compiler. It's impossible to automate cross-compiler testing. The way to build natively with a mingw-w64 compiler is doumented fairly simply at http://www.postgresql.org/docs/current/static/installation-platform-notes.html#INSTALLATION-NOTES-MINGW: To build 64 bit binaries using MinGW, install the 64 bit tool set from http://mingw-w64.sourceforge.net/, put its bin directory in the PATH, and run configure with the --host=x86_64-w64-mingw option. This is exactly how the buildfarm member pitta was set up. The only issue here is how to add support for using the 32bit mingw-w64 compiler in pretty much the same way. 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] [PATCH] PostgreSQL fails to build with 32bit MinGW-w64
Hi, The way to build natively with a mingw-w64 compiler is doumented fairly simply at http://www.postgresql.org/docs/current/static/installation-platform-notes.html#INSTALLATION-NOTES-MINGW: To build 64 bit binaries using MinGW, install the 64 bit tool set from http://mingw-w64.sourceforge.net/, put its bin directory in the PATH, and run configure with the --host=x86_64-w64-mingw option. This is exactly how the buildfarm member pitta was set up. The only issue here is how to add support for using the 32bit mingw-w64 compiler in pretty much the same way. I hope that configure --host=i686-w64-mingw use 32 bit tool sets and construct a 32 bit binary. It is mostly the same procedure and should have sufficient information to distinguish them. Both 64 bit and 32 bit binaries should be runnable on the same 64 bit system. Best regards, -- 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] Support for foreign keys with arrays
On mån, 2011-11-21 at 10:30 -0600, Merlin Moncure wrote: I like the idea of being able to define more flexible foreign keys, but are we gilding the lily here? The proposed solution is really quite specific to the nuances of arrays. Perhaps there is a more general expression based syntax that leaves the door open for other types conditions such as dealing fields dependent on other fields? Yeah, basically you'd just need a contains and/or is-contained-by operator between the two types. -- 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] Storing hot members of PGPROC out of the band
On Thu, Nov 24, 2011 at 7:24 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, Nov 24, 2011 at 1:30 PM, Robert Haas robertmh...@gmail.com wrote: I'm going to run some more tests, but my thought is that we should probably leave the recentglobalxmin changes out for the time being, pending further study and consideration of other alternatives. Agreed +1. These are independent patches and should be pursued like that. BTW, I reviewed the pgxact-v2.patch and I have no objections to that and it looks good to go in. Thanks Robert for making the necessary changes and also running the benchmark tests. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: use errcontext for custom exception too
2011/11/25 Robert Haas robertmh...@gmail.com: On Thu, Nov 24, 2011 at 12:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: There are small issue in PL/pgSQL and custom exceptions. Custom exception doesn't set a CONTEXT field. I propose change this behave for WARNING or EXCEPTION level. The goal is same behave for custom exception and builtin exception and it can help to identify a RAISE statement that is responsible to exception. That seems completely arbitrary. I think we discussed before providing an option to allow the user to control this, which seems better than implementing some hardcoded rule that may or may not be what a given user wants. A some option via #option or GUC has sense for lower levels like NOTICE or WARNING. For exception level CONTEXT should be filled every time - usually you have a stack of CONTEXT calls, because exception must not be on direct call, but the last CONTEXT (where exception was created missing). It is confusing. When a advanced developer see a exception without CONTEXT, then he know so exception is related to RAISE statement, but still is not simple find a statement, that raised exception - the line number is missing. Compromise solution can be GUC where CONTEXT is default for ERROR level like plpgsql.log_context_error_level = ERROR A new option on RAISE STATEMENT is not well, usually you want to same behave on complete application. Regards Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
