Re: [PATCHES] Re: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?
On Sat, 1 Dec 2007, Tom Lane wrote:
I wrote:
I got around to trying it with a dusty 5.6.1 I have laying about on my
HPPA machine, and the news is not good: CREATE LANGUAGE plperl dumps
core deep inside libperl. With or without this patch.
As best I can tell at the moment, I have not tested 5.6.1 with anything
later than our 7.2 branch, so I don't know exactly where the breakage
slipped in. It may be of long standing.
Actually, libperl seems to dump core in the same place in every PG
version, back to and including 7.2, so what seems more likely is that
this copy of perl is just plain broken. Since we didn't have any form
of regression test for plperl back then, it's entirely possible that
I never tested any further than compiling plperl with that setup.
So we still need someone to try it with a good copy of 5.6 ...
I tested cvs head which includes the patch on Solaris 9/SPARC with perl
5.6.1 and it seems to work fine.
Test output attached.
Steve
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
CREATE OR REPLACE FUNCTION test(TEXT) RETURNS bool language plperl as $$
return (shift =~
/[a-z\u0105\u0107\u0119\u0142\u0144ó\u015b\u017a\u017c\u0104\u0106\u0118\u0141\u0143\u015aÓ\u0179\u017b0-9_-]+/i)
|| 0;
$$;
CREATE FUNCTION
select test('depesz');
test
--
t
(1 row)
select test('depesz\u0105\u0107\u0119\u0142');
test
--
t
(1 row)
select test('depesz\u0105\u0107\u0119\u0142$');
test
--
t
(1 row)
select test('dePEsz\u0105\u0106\u0119\u0142$');
test
--
t
(1 row)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] TCL fix in HEAD
Bruce Momjian wrote: Marshall, Steve wrote: The recent TCL patch assumed Tcl_NotifierProcs.initNotifierProc was added in TCL 8.2: In fact it was added in 8.4 so I have modified the CVS with the following patch. I confirmed this against the 8.2.5 release. Sorry I did not notice that when I initially created the patch. While the patch will not work for the earlier releases, it should be noted that the multithhreading problem still exists when linking postgresql against TCL 8.2 and 8.3 libraries that are compiled with the preprocessor symbol TCL_THREADS defined. Unfortunately, we cannot override the initNotifier behavior in those releases, so we don't have a workable solution. This is probably not a big problem, since TCL was not commonly compiled with multithreading enabled prior to 8.4. However, perhaps there should be a warning in the documentation on PL/TCL directing users to avoid linking postgresql against TCL libraries earlier than 8.4 that have multithreading enabled? Can you send in a patch against pltcl.sgml? PL/TCL documentation patch is attached. *** pltcl.sgml 2007-10-01 08:29:06.667578247 -0400 --- pltcl.sgml.new 2007-10-01 08:24:41.736708719 -0400 *** *** 70,75 --- 70,87 literalcreatelang pltcl replaceabledbname//literal or literalcreatelang pltclu replaceabledbname//literal. /para +para + Care should be taken when linking the pltcl shared object code against + TCL libraries earlier than the TCL 8.4 release. The pre-8.4 versions of TCL must + be built emphasiswithout/emphasis multithreading support, i.e. with TCL_THREADS + undefined. Otherwise, the first use of PL/TCL functions will cause the postgres + backend to become multithreaded, resulting in subsequent unexpected state errors. + PL/TCL emphasiscan/emphasis be safely linked against multithreaded versions of the + TCL library for TCL versions 8.4 and later. In these cases, the pltcl source code uses + capabilities introduced in TCL 8.4 to override and disable the multithreading behavior. + Note that pre-8.4 versions of TCL were rarely built with multithreading support in + pre-compiled distributions, so this problem is rather rare. +/para /sect1 !-- PL/Tcl description -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TCL fix in HEAD
I'm fine with Tom's wording and placement. This seems a bit wordy, as well as wrongly placed --- the time to tell people they need a non-multithreaded Tcl is in the installation instructions. I added the following instead. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] TCL fix in HEAD
The recent TCL patch assumed Tcl_NotifierProcs.initNotifierProc was added in TCL 8.2: In fact it was added in 8.4 so I have modified the CVS with the following patch. I confirmed this against the 8.2.5 release. Sorry I did not notice that when I initially created the patch. While the patch will not work for the earlier releases, it should be noted that the multithhreading problem still exists when linking postgresql against TCL 8.2 and 8.3 libraries that are compiled with the preprocessor symbol TCL_THREADS defined. Unfortunately, we cannot override the initNotifier behavior in those releases, so we don't have a workable solution. This is probably not a big problem, since TCL was not commonly compiled with multithreading enabled prior to 8.4. However, perhaps there should be a warning in the documentation on PL/TCL directing users to avoid linking postgresql against TCL libraries earlier than 8.4 that have multithreading enabled? Yours, Steve Marshall ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded
I'm glad to see the patch making its way through the process. I'm also glad you guys do comprehensive testing before accepting it, since we are only able to test in a more limited range of environments. We have applied the patch to our 8.2.4 installations and are running it in a high transaction rate system (processing lots and lots of continually changing weather data). Let me know if there is any information we could provide that would be of help in making the back-patching decision. Yours, Steve Marshall -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, September 20, 2007 8:33 PM To: Marshall, Steve Cc: pgsql-patches@postgresql.org Subject: Re: [PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded Marshall, Steve [EMAIL PROTECTED] writes: There is a problem in PL/TCL that can cause the postgres backend to become multithreaded. Postgres is not designed to be multithreaded, so this causes downstream errors in signal handling. We have seen this cause a number of unexpected state errors associated with notification handling; however, unpredictable signal handling would be likely to cause other errors as well. I've applied this patch to CVS HEAD (8.3-to-be). I'm a bit hesitant to back-patch it however, at least not till it gets through some beta testing. Thanks for the detailed explanation, test case, and patch! regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] PL/TCL Patch to prevent postgres from becoming multithreaded
There is a problem in PL/TCL that can cause the postgres backend to become multithreaded. Postgres is not designed to be multithreaded, so this causes downstream errors in signal handling. We have seen this cause a number of unexpected state errors associated with notification handling; however, unpredictable signal handling would be likely to cause other errors as well. Some sample scripts are attached which will reproduce this problem when running against a multithreaded version of TCL, but will work without error with single-threaded TCL library. The scripts are a combination of Unix shell, perl DBI, and SQL commands. The postgres process can be seen to have multiple threads using the Linux command ps -Lwfu postgres. In this command the NLWP columns will be 2 for multithreaded backend processes. The threaded/non-threaded state of the TCL library can be ascertained on Linux using ldd to determine if libpthread.so is linked to the TCL library (e.g. ldd /usr/lib/libtcl8.4.so). The multithreaded behavior occurs the first time PL/TCL is used in a postgres backend, but only when postgres is linked against a multithread-enabled version of libtcl. Thus, this problem can be side-stepped by linking against the proper TCL library. However multithreaded TCL libraries are becoming the norm in Linux distributions and seems ubiquitous in the Windows world. Therefore a fix to the PL/TCL code is warrented. We determined that postgres became multithreaded during the creation of the TCL interpreter in a function called tcl_InitNotifier. This function is part of TCL's Notifier subsystem, which is used to monitor for events asynchronously from the TCL event loop. Although initialized when an interpreter is created, the Notifier subsystem is not used until a process enters the TCL event loop. This never happens within a postgres process, because postgres implements its own event loop. Therefore the initialization of the Notifier subsystem is not necessary within the context of PL/TCL. Our solution was to disable the Notifier subsystem by overriding the functions associated with it using the Tcl_SetNotifier function. This allows 8 functions related to the Notifier to overriden. Even though we found only two of the functions were ever called within postgres, we overrode 8 functions with no-op versions, just for completeness. A patch file containing the changes to pltcl.c from its 8.2.4 version is also attached. We tested this patch with PostgreSQL 8.2.4 on both RedHat Enterprise 4.0 usingTCL 8.4 (single threaded) and RHE 5.0 using TCL 8.4.13 (multithreaded). We expect this solution to work with Windows as well, although we have not tested it. There may be some problems using this solution with old versions of TCL that pre-date the Tcl_SetNotifier function. However this function has been around for quite a while; it was added in in the TCL 8.2 release, circa 2000. We hope this patch will be considered for a future PostgreSQL release. Steve Marshall Paul Bayer Doug Knight WSI Corporation pltcl_multithread_bug_test.tar.gz Description: GNU Zip compressed data *** pltcl.c.orig2007-09-10 12:58:34.0 -0400 --- pltcl.c 2007-09-11 11:37:33.363222114 -0400 *** *** 163,168 --- 163,258 static void pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc, Tcl_DString *retval); + /** + * Declarations for functions overriden using Tcl_SetNotifier. + **/ + static int fakeThreadKey; /* To give valid address for ClientData */ + + static ClientData + pltcl_InitNotifier _ANSI_ARGS_((void)); + + static void + pltcl_FinalizeNotifier _ANSI_ARGS_((ClientData clientData)); + + static void + pltcl_SetTimer _ANSI_ARGS_((Tcl_Time *timePtr)); + + static void + pltcl_AlertNotifier _ANSI_ARGS_((ClientData clientData)); + + static void + pltcl_CreateFileHandler _ANSI_ARGS_((int fd, int mask, Tcl_FileProc *proc, ClientData clientData)); + + static void + pltcl_DeleteFileHandler _ANSI_ARGS_((int fd)); + + static void + pltcl_ServiceModeHook _ANSI_ARGS_((int mode)); + + static int + pltcl_WaitForEvent _ANSI_ARGS_((Tcl_Time *timePtr)); + + /** + * Definitions for functions overriden using Tcl_SetNotifier. + * These implementations effectively disable the TCL Notifier subsystem. + * This is okay because we never enter the TCL event loop from postgres, + * so the notifier capabilities are initialized, but never used. + * + * NOTE: Only InitNotifier and DeleteFileHandler ever seem to get called + *by postgres, but we implement all the functions for completeness. + **/ + + ClientData
[PATCHES] Re: [HACKERS] choose_bitmap_and again (was Re: [PERFORM] Strangely Variable Query Performance)
Can you find any cases where it makes a worse choice than before? Another thing to pay attention to is whether the planning time gets noticeably worse. If we can't find any cases where it loses badly on those measures, I'll feel comfortable in applying it... Okay, here's the vedict; all the extremely slow queries (i.e. queries that took more than 30 seconds and upwards of several minutes to complete) are now running in the realm of reason. In fact, most queries that took between 1 and 4 minutes are now down to taking about 9 seconds which is obviously a tremendous improvement. A few of the queries that were taking 9 seconds or less are slightly slower -- meaning a second or two slower. However most of them are running at the same speed they were before, or better. So I'd say as far as I can tell with my application and my dataset, this change is solid and an obvious improvement. Talk to you later, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Re: [HACKERS] choose_bitmap_and again (was Re: [PERFORM] Strangely Variable Query Performance)
Can you find any cases where it makes a worse choice than before? Another thing to pay attention to is whether the planning time gets noticeably worse. If we can't find any cases where it loses badly on those measures, I'll feel comfortable in applying it... I'll see what I can find -- I'll let you know on Monday if I can find any queries that perform worse. My tests so far have shown equivalent or better performance so far but I've only done sort of a survey so far ... I've got plenty of special cases to test that should put this through the paces. Steve ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Re: [HACKERS] choose_bitmap_and again (was Re: [PERFORM] Strangely Variable Query Performance)
Steve, can you try this out on your queries and see if it makes better or worse decisions? It seems to fix your initial complaint but I do not have a large stock of test cases to try. Wow, this is a remarkable difference. Queries that were taking minutes to complete are coming up in seconds. Good work, I think this'll solve my customer's needs for their demo on the 19th :) Thank you so much! Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Improve psql's handling of multi-line queries
On 21/03/06, Bruce Momjian pgman@candle.pha.pa.us wrote: Steve, we have already applied a patch by Sergey E. Koposov to do this. Would you review CVS HEAD and see that everything works as you would like. Thanks. Yeah it's good; stores multiline queries in .psql_history which is an improvement over my patch. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Make 2PC error messages match docs
Hi,
This makes the error messages for PREPARE TRANSACTION, COMMIT PREPARED
etc. match the docs, which talk about transaction identifier not
gid or global transaction identifier.
Regards, Steve Woodcock
Index: src/backend/access/transam/twophase.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.11
diff -c -r1.11 twophase.c
*** src/backend/access/transam/twophase.c 29 Aug 2005 21:38:18 - 1.11
--- src/backend/access/transam/twophase.c 2 Oct 2005 10:38:57 -
***
*** 221,227
if (strlen(gid) = GIDSIZE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(global transaction identifier \%s\ is too long,
gid)));
LWLockAcquire(TwoPhaseStateLock, LW_EXCLUSIVE);
--- 221,227
if (strlen(gid) = GIDSIZE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(transaction identifier \%s\ is too long,
gid)));
LWLockAcquire(TwoPhaseStateLock, LW_EXCLUSIVE);
***
*** 256,262
{
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
! errmsg(global transaction identifier \%s\ is already in use,
gid)));
}
}
--- 256,262
{
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
! errmsg(transaction identifier \%s\ is already in use,
gid)));
}
}
***
*** 380,386
if (TransactionIdIsActive(gxact-locking_xid))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg(prepared transaction with gid \%s\ is busy,
gid)));
gxact-locking_xid = InvalidTransactionId;
}
--- 380,386
if (TransactionIdIsActive(gxact-locking_xid))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg(prepared transaction with identifier \%s\ is busy,
gid)));
gxact-locking_xid = InvalidTransactionId;
}
***
*** 403,409
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg(prepared transaction with gid \%s\ does not exist,
gid)));
/* NOTREACHED */
--- 403,409
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg(prepared transaction with identifier \%s\ does not exist,
gid)));
/* NOTREACHED */
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Improve psql's handling of multi-line queries
Hi all,
Attached is a patch for the following TODO item:
o Improve psql's handling of multi-line queries
Currently, while \e saves a single query as one entry, interactive
queries are saved one line at a time. Ideally all queries
whould be saved like \e does.
I know now's probably not a good time, but it caught my eye and looked
easy enough even for my rusty C.
Regards, Steve Woodcock
Index: input.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/input.c,v
retrieving revision 1.45
diff -c -r1.45 input.c
*** input.c 10 Jun 2005 15:40:41 - 1.45
--- input.c 28 Aug 2005 13:00:30 -
***
*** 24,29
--- 24,31
#ifdef USE_READLINE
static bool useReadline;
static bool useHistory;
+ static PQExpBuffer hist_buf; /* buffer for history entry being accumulated */
+ static PQExpBuffer prev_buf; /* previous history entry */
char *psql_history;
***
*** 90,97
#ifdef USE_READLINE
char *s;
- static char *prev_hist = NULL;
-
if (useReadline)
/* On some platforms, readline is declared as readline(char *) */
s = readline((char *) prompt);
--- 92,97
***
*** 100,120
if (useHistory s s[0])
{
! enum histcontrol HC;
!
! HC = GetHistControlConfig();
!
! if (((HC hctl_ignorespace) s[0] == ' ') ||
! ((HC hctl_ignoredups) prev_hist strcmp(s, prev_hist) == 0))
! {
! /* Ignore this line as far as history is concerned */
! }
! else
! {
! free(prev_hist);
! prev_hist = pg_strdup(s);
! add_history(s);
! }
}
return s;
--- 100,109
if (useHistory s s[0])
{
! /* Append line to history buffer, separating with newline */
! if (hist_buf-len 0)
! appendPQExpBufferChar(hist_buf, '\n');
! appendPQExpBufferStr(hist_buf, s);
}
return s;
***
*** 197,202
--- 186,194
if (psql_history)
read_history(psql_history);
+
+ hist_buf = createPQExpBuffer();
+ prev_buf = createPQExpBuffer();
}
#endif
***
*** 228,233
--- 220,257
}
+ void
+ maybeAddHistory(void)
+ {
+ #ifdef USE_READLINE
+ if (useHistory)
+ {
+ enum histcontrol HC;
+
+ HC = GetHistControlConfig();
+
+ if (((HC hctl_ignorespace) hist_buf-data[0] == ' ') ||
+ ((HC hctl_ignoredups) strcmp(hist_buf-data, prev_buf-data) == 0))
+ {
+ /* Ignore this buffer as far as history is concerned */
+ resetPQExpBuffer(hist_buf);
+ }
+ else
+ {
+ PQExpBuffer tmp;
+
+ add_history(hist_buf-data);
+
+ /* swap hist and prev and reset hist */
+ tmp = prev_buf;
+ prev_buf = hist_buf;
+ hist_buf = tmp;
+ resetPQExpBuffer(hist_buf);
+ }
+ }
+ #endif
+ }
+
static void
#ifdef HAVE_ATEXIT
Index: input.h
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/input.h,v
retrieving revision 1.23
diff -c -r1.23 input.h
*** input.h 1 Jan 2005 05:43:08 - 1.23
--- input.h 28 Aug 2005 13:00:30 -
***
*** 38,42
--- 38,43
void initializeInput(int flags);
bool saveHistory(char *fname);
+ void maybeAddHistory(void);
#endif /* INPUT_H */
Index: mainloop.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/mainloop.c,v
retrieving revision 1.67
diff -c -r1.67 mainloop.c
*** mainloop.c 22 Feb 2005 04:40:55 - 1.67
--- mainloop.c 28 Aug 2005 13:00:31 -
***
*** 286,291
--- 286,297
break;
}
+ #ifdef USE_READLINE
+ if (pset.cur_cmd_interactive
+ scan_result == PSCAN_INCOMPLETE)
+ maybeAddHistory();
+ #endif
+
/* fall out of loop if lexer reached EOL */
if (scan_result == PSCAN_INCOMPLETE ||
scan_result == PSCAN_EOL)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] CVS should die (was: Possible make_oidjoins_check ...)
This doesn't really answer the question of what tool Postgres might change to, but it seems that Subversion is a good tool one should consider. And by golly, CVS is bad. Just consider the cons having to forbid renames in all but the most necessary cases it just invites cruft into any project. Interesting reading: http://better-scm.berlios.de/comparison/comparison.html http://zooko.com/revision_control_quick_ref.html Cheers, Steve ---(end of broadcast)--- TIP 8: explain analyze is your friend
