Re: [HACKERS] nodeAgg perf tweak
On Wed, 2004-12-01 at 02:48, Neil Conway wrote: I noticed that we have a bottleneck in aggregate performance in advance_transition_function(): according to callgrind, doing datumCopy() and pfree() for every tuple produced by the transition function is pretty expensive. Some queries bare this out: ... I can reproduce this performance difference consistently. I thought this might have been attributable to memory checking overhead because assertions were enabled, but that doesn't appear to be the case (the above results are without --enable-cassert). That looks like a useful performance gain, and count(*) is a weak point on the performance roadmap. Thanks. The attached patch invokes the transition function in the current memory context. I don't think that's right: a memory leak in an aggregate's transition function would be problematic when we're invoked from a per-query memory context, as is the case with advance_aggregates(). Perhaps we need an additional short-lived memory context in AggStatePerAggData: we could invoke the transition function in that context, and reset it once per, say, 1000 tuples. I'd be a little twitchy about new memory contexts at this stage of beta, but if the code is fairly well isolated that could be good. Alternatively we could just mandate that aggregate transition function's not leak memory and then invoke the transition function in, say, the aggregate's memory context, but that seems a little fragile. Would it possible to differentiate between well-known builtin functions and added transition functions? I realise nothing is that simple, but it seems we could trust some functions more than others. The trust level could be determined at planning time. [DB2 uses FENCED or UNFENCED declarations for this purpose, though the exact meaning is different to your proposal - I'm not suggesting adding external syntax though] A performance gain on the built-ins alone would satisfy 80% of users. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] nodeAgg perf tweak
On Wed, 2004-12-01 at 08:25 +, Simon Riggs wrote: I'd be a little twitchy about new memory contexts at this stage of beta, but if the code is fairly well isolated that could be good. This would be for 8.1 anyway. Would it possible to differentiate between well-known builtin functions and added transition functions? IMHO, this would be ugly and add unnecessary complexity. I'd like to find a solution that actually fixes the problem, rather than just working around it in the common case. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Increasing the length of
On Tue, 2004-11-30 at 19:32, Bruce Momjian wrote: David Parker wrote: I've been using log_min_duration_statement = 0 to get durations on all SQL statements for the purposes of performance tuning, because this logs the duration on the same line as the statement. My reading of this TODO is that now log_min_duration_statement = 0 would give me the statements but no total duration? Oh, sorry, you are right. I forgot about the duration part! I got so excited I forgot. TODO item removed. David's objection was noted, and why I hadn't coded it (yet). There are currently two ways of getting statement and duration output, which is confusing You can either 1. Individual statements - log_statement = all - log_duration = true - log_line_prefix includes processid which produces 2 log lines like statement: x duration: yy 2. log_min_duration log_min_duration_statement=0 which produces 1 log line like duration: yyy statement: xx These two things do exactly the same thing, apart from the way the output is presented to the user in the log line. I'd like to change log_min_duration_statement as suggested, but this side-effect behaviour of being a better log_statement than log_statement kindof gets in the way. It makes me wonder why we have log_statement at all. We all want to do performance tracing. I'd also like to be able to dynamically monitor what is actually happening *now* on the system. There is no way right now to monitor for rogue queries, other than to cancel anything that runs more than statement_timeout. Thats not good either, even if it does keep the current behaviour. My preference would be to do the following: - add a script to contrib to process the log file - always add processid to log_statement_prefix when both log_statement and log_duration are specified, so you can always tie up the data Anybody? -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Error handling in plperl and pltcl
Tom Lane wrote:
Wrapping each individual SPI command in a subtransaction IN NO WAY
prevents us from adding programmer-controllable savepoint features
to the PL languages later.
Ah good - I was coming to the conclusion savepoints/exception handling
were both separately necessary.
It simply ensures that we have somewhat
sane error recovery behavior in the meantime. The only valid argument
against doing it is the one of added overhead, and I already gave my
responses to that one.
The bit I still don't get is how the subtrans-per-spi gets us try/catch
functionality.
INSERT 1
INSERT 2
try {
INSERT 3
INSERT 4
}
catch WHATEVER {
INSERT 5
INSERT 6
}
So - here we (well I) would expect to see 1,2,3,4 or 1,2,5,6. That means
if #4 fails we need to rollback to a savepoint before #3. But the
problem is that we don't know whether we are in the try block, otherwise
we'd just start a savepoint there and sidestep the whole issue.
That means the only safe action is to rollback the transaction. We can't
even just write to a log table and raise our own exception, since the
calling function then won't know what to do.
I'm worried that non-intuitive behaviour here is strapping the gun to
our foot. It's going to introduce peculiarities in code-paths that are
likely to go untested until it's too late.
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
3. We can do something similar with a pgeval() in plperl. Don't know
enough to say about Python.
Basically, if exception handling doesn't work the way it should
intuitively work (IMHO plpgsql's model) then I'd rather wait until 8.1
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] createdb failed
alex yap wrote: Hi, recently i need to use pg in my project. everything going ok till when i want to createdb it appear this : Warning : could not remove database directory /var/postgresql/data/base/17147 Detail: Failing system command was : rm -rf '/var/postgresql/data/base/17147' Error: could not initialize database directory; delete failed as well detail : failing system command was : cp -r '/var/postgresql/data/base/1' '/var/postgresql/data/base/17147' This is the wrong list for support queries - in the future general would be a good choice. Things to check: 1. You are starting PG as the right user 2. Permissions are correct 3. The directory exists 4. Have you done anything with tablespaces? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error handling in plperl and pltcl
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
This will be even worse since it will impose the subtransaction overhead
on everything, even functions that never do any database access. Perhaps
this approach would be feasible if imposed on volatile functions only,
but then again, the volatility of a function cannot be trusted since we
have no way of defining a stable but with side effects type.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily understood approach that doesn't
clobber the language.
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error handling in plperl and pltcl
Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
This will be even worse since it will impose the subtransaction overhead
on everything, even functions that never do any database access. Perhaps
this approach would be feasible if imposed on volatile functions only,
but then again, the volatility of a function cannot be trusted since we
have no way of defining a stable but with side effects type.
Actually, I was thinking of setting a flag and then on the first SPI
call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily understood approach that doesn't
clobber the language.
But is the problem not that forgetting to use SAVEPOINT can get us in
trouble with clearing up after an exception? That's the main thrust of
Tom's per-statement stuff AFAICT. And again, you're not going to see the
problem until an exception is thrown.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error handling in plperl and pltcl
Richard Huxton wrote: But is the problem not that forgetting to use SAVEPOINT can get us in trouble with clearing up after an exception? I fail to see how that's different from forgetting to use pgtry instead of try. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Error handling in plperl and pltcl
Thomas Hallgren wrote: Richard Huxton wrote: But is the problem not that forgetting to use SAVEPOINT can get us in trouble with clearing up after an exception? I fail to see how that's different from forgetting to use pgtry instead of try. It feels more distinct to me. I'll grant you I'm only a sample size of 1 though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: 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
Re: [HACKERS] nodeAgg perf tweak
On Wed, 2004-12-01 at 08:37, Neil Conway wrote: On Wed, 2004-12-01 at 08:25 +, Simon Riggs wrote: I'd be a little twitchy about new memory contexts at this stage of beta, but if the code is fairly well isolated that could be good. This would be for 8.1 anyway. Would it possible to differentiate between well-known builtin functions and added transition functions? IMHO, this would be ugly and add unnecessary complexity. I'd like to find a solution that actually fixes the problem, rather than just working around it in the common case. It would be my suggestion to implement the optimisation for the common case *now*, then fix the general case later. Please shave 20% off everybody's aggregation queries, ugly or not. You'll see a lot of happy people. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Error handling in plperl and pltcl
On Wed, 01 Dec 2004 10:29:17 +0100, Thomas Hallgren [EMAIL PROTECTED] wrote: Richard Huxton wrote: But is the problem not that forgetting to use SAVEPOINT can get us in trouble with clearing up after an exception? I fail to see how that's different from forgetting to use pgtry instead of try. I see that as a non-starter. At least in the case of perl, we can actually hide pgeval behind the standard eval. If pgeval were equivelant to, say, 'savepoint(foo); CORE::eval @_;' then the onus is still on the developer to use 'eval', but that is a familiar concept to defensive developers. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 3: 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
Please release (was Re: [HACKERS] nodeAgg perf tweak)
On Wed, Dec 01, 2004 at 10:03:40AM +, Simon Riggs wrote: Please shave 20% off everybody's aggregation queries, ugly or not. You'll see a lot of happy people. When would the experimentation end, and 8.0 be finally released? There's real development waiting only for release to happen ... I have submitted three patches already that are pending for 8.1, and the code keeps drifting. There has to be a release soon. We can't keep in beta forever. Also, I think we should consider using a time-based release process instead of the unpredictable mess that it is used now. If hard development was done in branches and only merged when stable, we could do this. (This is also something that a better SCM could help us do, though GCC is living proof that it can be done with CVS too.) -- Alvaro Herrera ([EMAIL PROTECTED]) God is real, unless declared as int ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Error handling in plperl and pltcl
On 12/1/2004 4:27 AM, Richard Huxton wrote:
Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
This will be even worse since it will impose the subtransaction overhead
on everything, even functions that never do any database access. Perhaps
this approach would be feasible if imposed on volatile functions only,
but then again, the volatility of a function cannot be trusted since we
have no way of defining a stable but with side effects type.
Actually, I was thinking of setting a flag and then on the first SPI
call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily understood approach that doesn't
clobber the language.
But is the problem not that forgetting to use SAVEPOINT can get us in
trouble with clearing up after an exception? That's the main thrust of
Tom's per-statement stuff AFAICT. And again, you're not going to see the
problem until an exception is thrown.
I think the following would a) be a drop in replacement without any side
effects or performance impact for PL/Tcl functions not using catch and
b) give catch a sensible and correct behaviour.
One can _replace_ the Tcl catch command with his own C function. This
can be done during the interpreter initialization when loading the
PL/Tcl module. The new catch would
push a status NEED_SUBTRANS onto a stack
call Tcl_Eval() for the first command argument
if TCL_ERROR {
pop status from stack
if popped status == HAVE_SUBTRANS {
rollback subtransaction
}
if a second argument exists {
store interpreter result in variable
}
return TCL_ERROR
}
pop status from stack
if popped status == HAVE_SUBTRANS {
commit subtransaction
}
return result code from Tcl_Eval()
The spi functions check if the top stack entry (if there is one) is
NEED_SUBTRANS. If so, they start a subtrans and change the status to
HAVE_SUBTRANS.
This all would mean that however deeply nested a function call tree, it
would unwind and rollback everything up to the outermost catch. If there
is no catch used, no subtransactions are created and the unwinding goes
all the way up to the statement. If catch is used but no spi access
performed inside, no subtransaction overhead either.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
On Wed, Dec 01, 2004 at 09:33:18 +, Chris Green [EMAIL PROTECTED] wrote: On Wed, Dec 01, 2004 at 02:02:41AM -0600, Bruno Wolff III wrote: On Mon, Nov 29, 2004 at 12:49:46 +, Chris Green [EMAIL PROTECTED] wrote: This is a perpetual problem, if people all used the same MUA and (assuming it has the capability) all used the 'reply to list' command to reply to the list everything would be wonderful! :-) I think using mail-followup-to is better than having people do reply to list. It depends on how the 'reply to list' is implemented surely. With mutt (the MUA I use) you specify the addresses of known mailing lists and the 'reply to List' command uses this to detect from the headers whether the mail is from a list or not and acts accordingly. That doesn't work in general since the client can't know which recipients are actually on the list. However mutt does utilize mail-followup-to headers when doing group replies and that works well for replying to lists. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error handling in plperl and pltcl
Jan Wieck wrote: This all would mean that however deeply nested a function call tree, it would unwind and rollback everything up to the outermost catch. If there is no catch used, no subtransactions are created and the unwinding goes all the way up to the statement. If catch is used but no spi access performed inside, no subtransaction overhead either. Yes, this makes a lot of sense. No overhead unless you want to. Way to go. I wish I could do the same in PL/Java. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
Bruno Wolff III [EMAIL PROTECTED] writes: It is also possible for mailing list software to handle this preference for you (by not sending copies to addresses on the list that appear in the recipient headers), but I don't know if the software in use has that capability. That's a good point. It looks like our list software *can* do that (send majordomo a help set command and read about eliminatecc setting) so rather than try to force the same behavior on everyone, I think it's sufficient to tell those who prefer only one copy to enable that preference. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Manfred Spraul wrote: Bruce Momjian wrote: Comments? This seems like our only solution. This would be a transparent solution. Another approach would be: - Use the old 7.3 approach by default. This means perfect backward compatibility for single-threaded apps and broken multithreaded apps. - Add a new PQinitDB(int disableSigpipeHandler) initialization function. Document that multithreaded apps must call the function with disableSigpipeHandle=1 and handle SIGPIPE for libpq. Perhaps with a reference implementation in libpq (i.e. a sigpipeMode with 0 for old approach, 1 for do nothing, 2 for install our own handler). It would prefer that approach: It means that the multithreaded libpq apps must be updated [are there any?], but the solution is simpler and less fragile than calling 4 signal handling function in a row to selectively block SIGPIPE per-thread. I think we can get away with three function calls because we can check errno for EPIPE from the send() call. We already have two function calls in there so I don't see a third as a huge problem and not worth an API change unless someone tells us it is a performance hit. One thing I know from the broken 7.4 code is that calling signal() from inside a thread is very slow on Linux, like a 20% performance hit because I assume it has to adjust all the threads signal stacks or something. Anyway, I assume thread_sigmask() and sigpending() are not huge hits. In fact, by checking EPIPE from send() we don't need sigpending at all. We just block and (if EPIPE) clear using sigwait(), then unblock. I think we can document that if you are blocking SIGPIPE in your application and wait to handle it later, you can't keep the pending signal through a libpq call. I think that is a much easier requirement than adding a new API call. The most common case of SIG_IGN/SIG_DFL is not affected by this, only cases where they define their own signal handler, block it, and then trying to keep a call active across a libpq call. Manfred, glad you are around to discuss this. After much research I came up with a method and then found your description that matched it so I felt I was on the right track. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Increasing the length of
Simon Riggs wrote: On Tue, 2004-11-30 at 19:32, Bruce Momjian wrote: David Parker wrote: I've been using log_min_duration_statement = 0 to get durations on all SQL statements for the purposes of performance tuning, because this logs the duration on the same line as the statement. My reading of this TODO is that now log_min_duration_statement = 0 would give me the statements but no total duration? Oh, sorry, you are right. I forgot about the duration part! I got so excited I forgot. TODO item removed. David's objection was noted, and why I hadn't coded it (yet). There are currently two ways of getting statement and duration output, which is confusing You can either 1. Individual statements - log_statement = all - log_duration = true - log_line_prefix includes processid which produces 2 log lines like statement: x duration: yy 2. log_min_duration log_min_duration_statement=0 which produces 1 log line like duration: yyy statement: xx These two things do exactly the same thing, apart from the way the output is presented to the user in the log line. I'd like to change log_min_duration_statement as suggested, but this side-effect behaviour of being a better log_statement than log_statement kindof gets in the way. It makes me wonder why we have log_statement at all. We have it so you can look in the log to see currently running queries rather than just completed queries. We all want to do performance tracing. I'd also like to be able to dynamically monitor what is actually happening *now* on the system. There is no way right now to monitor for rogue queries, other than to cancel anything that runs more than statement_timeout. Thats not good either, even if it does keep the current behaviour. My preference would be to do the following: - add a script to contrib to process the log file - always add processid to log_statement_prefix when both log_statement and log_duration are specified, so you can always tie up the data I think our setup is confusing enough. Adding automatic additions seems even more confusing than what we have now. We could throw a log warning message somehow though. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Where is the connection info
In postgres, how do I find out how many connections are allowed per user? Where those connections are stored? Who has the connections? Etc. Kenny Cason [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Increasing the length of
How difficult would it be to make the combination log_statement = all log_duration = true just put the duration on the same line as the statement? Then log_min_duration_statement could be used to do the desired log-at-threshold behavior, which certainly seems valuable. You'd need a way to visually/scriptually (?) distinguish those log records, though, I guess. Note that my original post on this was more of a question than an objection - it's entirely possible to sed around having duration and statement on separate lines - I just wanted clarification. Having them on the same line IS handy, however - DAP -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 11:18 AM To: Simon Riggs Cc: David Parker; [EMAIL PROTECTED] Subject: Re: [HACKERS] Increasing the length of Simon Riggs wrote: On Tue, 2004-11-30 at 19:32, Bruce Momjian wrote: David Parker wrote: I've been using log_min_duration_statement = 0 to get durations on all SQL statements for the purposes of performance tuning, because this logs the duration on the same line as the statement. My reading of this TODO is that now log_min_duration_statement = 0 would give me the statements but no total duration? Oh, sorry, you are right. I forgot about the duration part! I got so excited I forgot. TODO item removed. David's objection was noted, and why I hadn't coded it (yet). There are currently two ways of getting statement and duration output, which is confusing You can either 1. Individual statements - log_statement = all - log_duration = true - log_line_prefix includes processid which produces 2 log lines like statement: x duration: yy 2. log_min_duration log_min_duration_statement=0 which produces 1 log line like duration: yyy statement: xx These two things do exactly the same thing, apart from the way the output is presented to the user in the log line. I'd like to change log_min_duration_statement as suggested, but this side-effect behaviour of being a better log_statement than log_statement kindof gets in the way. It makes me wonder why we have log_statement at all. We have it so you can look in the log to see currently running queries rather than just completed queries. We all want to do performance tracing. I'd also like to be able to dynamically monitor what is actually happening *now* on the system. There is no way right now to monitor for rogue queries, other than to cancel anything that runs more than statement_timeout. Thats not good either, even if it does keep the current behaviour. My preference would be to do the following: - add a script to contrib to process the log file - always add processid to log_statement_prefix when both log_statement and log_duration are specified, so you can always tie up the data I think our setup is confusing enough. Adding automatic additions seems even more confusing than what we have now. We could throw a log warning message somehow though. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Error handling in plperl and pltcl
On 12/1/2004 9:23 AM, Jan Wieck wrote:
On 12/1/2004 4:27 AM, Richard Huxton wrote:
Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
This will be even worse since it will impose the subtransaction overhead
on everything, even functions that never do any database access. Perhaps
this approach would be feasible if imposed on volatile functions only,
but then again, the volatility of a function cannot be trusted since we
have no way of defining a stable but with side effects type.
Actually, I was thinking of setting a flag and then on the first SPI
call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily understood approach that doesn't
clobber the language.
But is the problem not that forgetting to use SAVEPOINT can get us in
trouble with clearing up after an exception? That's the main thrust of
Tom's per-statement stuff AFAICT. And again, you're not going to see the
problem until an exception is thrown.
I think the following would a) be a drop in replacement without any side
effects or performance impact for PL/Tcl functions not using catch and
b) give catch a sensible and correct behaviour.
One can _replace_ the Tcl catch command with his own C function. This
can be done during the interpreter initialization when loading the
PL/Tcl module. The new catch would
push a status NEED_SUBTRANS onto a stack
call Tcl_Eval() for the first command argument
if TCL_ERROR {
pop status from stack
if popped status == HAVE_SUBTRANS {
rollback subtransaction
}
if a second argument exists {
store interpreter result in variable
}
return TCL_ERROR
er ... no ... must return a true boolean with TCL_OK here
}
pop status from stack
if popped status == HAVE_SUBTRANS {
commit subtransaction
}
return result code from Tcl_Eval()
and here it must put a false boolean into the Tcl result ... not 100%
sure about the result code. Must check if it's possible to return or
break from inside a catch block ... if not, then catch allways turns the
internal result code into TCL_OK. Anyhow, you get the idea.
Jan
The spi functions check if the top stack entry (if there is one) is
NEED_SUBTRANS. If so, they start a subtrans and change the status to
HAVE_SUBTRANS.
This all would mean that however deeply nested a function call tree, it
would unwind and rollback everything up to the outermost catch. If there
is no catch used, no subtransactions are created and the unwinding goes
all the way up to the statement. If catch is used but no spi access
performed inside, no subtransaction overhead either.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Increasing the length of
David Parker wrote: How difficult would it be to make the combination log_statement = all log_duration = true just put the duration on the same line as the statement? Then log_min_duration_statement could be used to do the desired log-at-threshold behavior, which certainly seems valuable. You'd need a way to visually/scriptually (?) distinguish those log records, though, I guess. Note that my original post on this was more of a question than an objection - it's entirely possible to sed around having duration and statement on separate lines - I just wanted clarification. Having them on the same line IS handy, however Many people want the statement printed when it starts (for real-time server monitoring), not when it finishes, meaning we don't know the duration at start time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Increasing the length of
On Wed, 2004-12-01 at 11:47, Bruce Momjian wrote:
David Parker wrote:
How difficult would it be to make the combination
log_statement = all
log_duration = true
just put the duration on the same line as the statement? Then
log_min_duration_statement could be used to
do the desired log-at-threshold behavior, which certainly seems
valuable. You'd need a way to visually/scriptually (?) distinguish those
log records, though, I guess.
Note that my original post on this was more of a question than an
objection - it's entirely possible to sed around having duration and
statement on separate lines - I just wanted clarification. Having them
on the same line IS handy, however
Many people want the statement printed when it starts (for real-time
server monitoring), not when it finishes, meaning we don't know the
duration at start time.
I think what David is asking for is log_statement and log_duration to
print on the same line at query completion time. We could then change
log_min_duration_statement to print DurationExceeded duration rather
than just Duration time like it does now, also on the same line, but
with a way to differentiate the two.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Increasing the length of
On Wednesday 01 December 2004 03:38, Simon Riggs wrote:
On Tue, 2004-11-30 at 19:32, Bruce Momjian wrote:
David Parker wrote:
I've been using log_min_duration_statement = 0 to get durations on
all SQL statements for the purposes of performance tuning, because this
logs the duration on the same line as the statement. My reading of this
TODO is that now log_min_duration_statement = 0 would give me the
statements but no total duration?
Oh, sorry, you are right. I forgot about the duration part! I got so
excited I forgot.
TODO item removed.
David's objection was noted, and why I hadn't coded it (yet).
There are currently two ways of getting statement and duration output,
which is confusing
You can either
1. Individual statements
- log_statement = all
- log_duration = true
- log_line_prefix includes processid
which produces 2 log lines like
statement: x
duration: yy
2. log_min_duration
log_min_duration_statement=0
which produces 1 log line like
duration: yyy statement: xx
These two things do exactly the same thing, apart from the way the
output is presented to the user in the log line.
I'd like to change log_min_duration_statement as suggested, but this
side-effect behaviour of being a better log_statement than log_statement
kindof gets in the way. It makes me wonder why we have log_statement at
all.
it's what we started with and has some use cases that
log_min_duration_statement doesnt. Besides, if you change
log_min_duration_statement to print at the time duration is exceeded, you'll
need something to enable printing out of durations of completed statements.
We all want to do performance tracing. I'd also like to be able to
dynamically monitor what is actually happening *now* on the system.
There is no way right now to monitor for rogue queries, other than to
cancel anything that runs more than statement_timeout. Thats not good
either, even if it does keep the current behaviour.
pg_stat_activity shows query_start, you could poll that to look for rouge
queries. you can also use log_min_duration_statement to watch for rouge
queries.
My preference would be to do the following:
- add a script to contrib to process the log file
check out the pqa project on pgfoundry
- always add processid to log_statement_prefix when both log_statement
and log_duration are specified, so you can always tie up the data
we have the option to add the process id now, I don't see why we should force
it into the line. besides, some logging tools will do this for you, so it
would be duplicate data.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Error handling in plperl and pltcl
On Wednesday 01 December 2004 04:12, Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing here afaict) in a
sub-transaction. An exception can be caught within that function, and
all the spi in that function is then rolled back. This is rubbish, but
at least it's predictable and allows you to write to a log table and
throw another exception.
This will be even worse since it will impose the subtransaction overhead
on everything, even functions that never do any database access. Perhaps
this approach would be feasible if imposed on volatile functions only,
but then again, the volatility of a function cannot be trusted since we
have no way of defining a stable but with side effects type.
Agreed.
2. For pl/tcl introduce a pgtry { } catch { } which just starts a
sub-transaction and does standard try/catch. I don't use TCL, but from
the little I know this should be straightforward.
If you know how to use special constructs like this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily understood approach that doesn't
clobber the language.
Agreed. The fewer special constructs the better imho.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Error handling in plperl and pltcl
--- Jan Wieck [EMAIL PROTECTED] wrote:
On 12/1/2004 9:23 AM, Jan Wieck wrote:
On 12/1/2004 4:27 AM, Richard Huxton wrote:
Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing
here afaict) in a
sub-transaction. An exception can be caught
within that function, and
all the spi in that function is then rolled
back. This is rubbish, but
at least it's predictable and allows you to
write to a log table and
throw another exception.
This will be even worse since it will impose the
subtransaction overhead
on everything, even functions that never do any
database access. Perhaps
this approach would be feasible if imposed on
volatile functions only,
but then again, the volatility of a function
cannot be trusted since we
have no way of defining a stable but with side
effects type.
Actually, I was thinking of setting a flag and
then on the first SPI
call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { }
which just starts a
sub-transaction and does standard try/catch. I
don't use TCL, but from
the little I know this should be
straightforward.
If you know how to use special constructs like
this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily
understood approach that doesn't
clobber the language.
But is the problem not that forgetting to use
SAVEPOINT can get us in
trouble with clearing up after an exception?
That's the main thrust of
Tom's per-statement stuff AFAICT. And again,
you're not going to see the
problem until an exception is thrown.
I think the following would a) be a drop in
replacement without any side
effects or performance impact for PL/Tcl functions
not using catch and
b) give catch a sensible and correct behaviour.
One can _replace_ the Tcl catch command with his
own C function. This
can be done during the interpreter initialization
when loading the
PL/Tcl module. The new catch would
push a status NEED_SUBTRANS onto a stack
call Tcl_Eval() for the first command
argument
if TCL_ERROR {
pop status from stack
if popped status == HAVE_SUBTRANS {
rollback subtransaction
}
if a second argument exists {
store interpreter result in variable
}
return TCL_ERROR
er ... no ... must return a true boolean with TCL_OK
here
}
pop status from stack
if popped status == HAVE_SUBTRANS {
commit subtransaction
}
return result code from Tcl_Eval()
and here it must put a false boolean into the Tcl
result ... not 100%
sure about the result code. Must check if it's
possible to return or
break from inside a catch block ... if not, then
catch allways turns the
internal result code into TCL_OK. Anyhow, you get
the idea.
Yes, you can have break, return in a catch
statement...it would return the exception code for
that statement (i.e. TCL_BREAK, TCL_RETURN).
I like this proposal, just as long as it behaves
exactly like Tcl's catch when there is no SPI function
call.
--brett
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Please release (was Re: [HACKERS] nodeAgg perf tweak)
Alvaro Herrera [EMAIL PROTECTED] wrote on 01.12.2004, 15:08:11: On Wed, Dec 01, 2004 at 10:03:40AM +, Simon Riggs wrote: Please shave 20% off everybody's aggregation queries, ugly or not. You'll see a lot of happy people. When would the experimentation end, and 8.0 be finally released? There's real development waiting only for release to happen ... Yes, I agree, there is real development waiting to happen. Users of PostgreSQL are waiting to use the new software. Which is why I'd like to see a 20% gain in aggregation performance happen. That's a huge gain, not just the little 1 percents we keep clawing at. For most of the people I work with, database performance is very important. I regard performance testing as an essential part of the release process of any performance critical software. Most earlier beta releases were fixing functional problems and now the focus has moved to performance related issues. Many people have trial-ported their software to r8.0 now that it works sufficiently well to do this and are now reporting performance issues. Also, many pure performance tests are being run on the various betas. I can only speak for myself, but I've spent a good deal of the last month trying to analyse performance issues, report or document them - though others have IMHO done much more than myself towards that goal. For me, performance is a function that requires testing too, and bug fixes for it should be allowed into the tail of the release. Where do you stop? When the further gains from doing so are small+20% tells me that point hasn't yet been reached. Moving between releases is a big pain for people, so many would tend to upgrade every other release. It will be some time before 8.0 makes it into other distributions. When it does, it would be best if it is as good as it can be. 8.1 is only a year away means it is light-years away. We've just gained what looks like 20%+ performance gain on the DBT-2 benchmark; with this perf tweak, we could see 20%+ performance gain on the DBT-3 benchmark also. I'd say if you asked most people whether we should wait another month, but if they did, they'd get a 20% boost on some of their worst queries, then they would choose to wait. 2% = don't bother; 20% = wait. I have submitted three patches already that are pending for 8.1, and the code keeps drifting. There has to be a release soon. We can't keep in beta forever. Code drift is a real pain and you and I were both tortured by this during June and July but I think we should stay in beta until we're done. Also, I think we should consider using a time-based release process instead of the unpredictable mess that it is used now. If hard development was done in branches and only merged when stable, we could do this. (This is also something that a better SCM could help us do, though GCC is living proof that it can be done with CVS too.) PostgreSQL development seems very fast to me, which is good. Timeboxing it would do little to improve the situation for prospective users of the software, whether or not it helps developers. I've no comment on the SCM design - I'll follow whatever is in place. Best Regards Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Increasing the length of
Robert Treat wrote: On Wed, 2004-12-01 at 11:47, Bruce Momjian wrote: David Parker wrote: How difficult would it be to make the combination log_statement = all log_duration = true just put the duration on the same line as the statement? Then log_min_duration_statement could be used to do the desired log-at-threshold behavior, which certainly seems valuable. You'd need a way to visually/scriptually (?) distinguish those log records, though, I guess. Note that my original post on this was more of a question than an objection - it's entirely possible to sed around having duration and statement on separate lines - I just wanted clarification. Having them on the same line IS handy, however Many people want the statement printed when it starts (for real-time server monitoring), not when it finishes, meaning we don't know the duration at start time. I think what David is asking for is log_statement and log_duration to print on the same line at query completion time. We could then change log_min_duration_statement to print DurationExceeded duration rather than just Duration time like it does now, also on the same line, but with a way to differentiate the two. I see. That just seems too confusing to me. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Bruce Momjian wrote: [... SIGPIPE suppression in libpq ...] Linux also has MSG_NOSIGNAL as a send() flag that might be useful. It suppresses generation of SIGPIPE for just that call. No, it doesn't work for SSL and it's probably not very portable, but it might be a good platform-specific optimization for the common case. -O ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Where is the connection info
On K, 2004-12-01 at 18:28, Cason, Kenny wrote: In postgres, how do I find out how many connections are allowed per user? Where those connections are stored? Who has the connections? Etc. You can start from: show all; select * from pg_stat_activity; - Hannu ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Bruce Momjian [EMAIL PROTECTED] writes:
His idea of pthread_sigmask/send/sigpending/sigwait/restore-mask. Seems
we could also check errno for SIGPIPE rather than calling sigpending.
He has a concern about an application that already blocked SIGPIPE and
has a pending SIGPIPE signal waiting already. One idea would be to
check for sigpending() before the send() and clear the signal only if
SIGPIPE wasn't pending before the call. I realize that if our send()
also generates a SIGPIPE it would remove the previous realtime signal
info but that seems a minor problem.
Supposing that we don't touch the signal handler at all, then it is
possible that the application has set it to SIG_IGN, in which case a
SIGPIPE would be discarded rather than going into the pending mask.
So I think the logic has to be:
pthread_sigmask to block SIGPIPE and save existing signal mask
send();
if (errno == EPIPE)
{
if (sigpending indicates SIGPIPE pending)
use sigwait to clear SIGPIPE;
}
pthread_sigmask to restore prior signal mask
The only case that is problematic is where the application had
already blocked SIGPIPE and there is a pending SIGPIPE signal when
we are entered, *and* we get SIGPIPE ourselves.
If the C library does not support queued signals then our sigwait will
clear both our own EPIPE and the pending signal. This is annoying but
it doesn't seem fatal --- if the app is writing on a closed pipe then
it'll probably try it again later and get the signal again.
If the C library does support queued signals then we will read the
existing SIGPIPE condition and leave our own signal in the queue. This
is no problem to the extent that one pending SIGPIPE looks just like
another --- does anyone know of platforms where there is additional info
carried by a SIGPIPE event?
This seems workable as long as we document the possible gotchas.
regards, tom lane
---(end of broadcast)---
TIP 3: 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
Re: [HACKERS] New compile warnings
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I don't currently have the resources to clean this up properly. The attached patch tries to make clear in a comment what the code is doing, and also initializes these variables to NULL. If someone wants to take a stab at cleaning this up they are welcome to - I don't expect to be able to for quite a while. OK, applied. It compiles warning-free now for me (using Perl 5.8.0) FYI, I still see compile warnings. I assume they are just related to me using Perl 5.0.5 and will ignore them: gmake[2]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/test/regress' gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/test' /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used SPI.c:59: warning: no previous prototype for `XS__elog' SPI.c:78: warning: no previous prototype for `XS__DEBUG' SPI.c:93: warning: no previous prototype for `XS__LOG' SPI.c:108: warning: no previous prototype for `XS__INFO' SPI.c:123: warning: no previous prototype for `XS__NOTICE' SPI.c:138: warning: no previous prototype for `XS__WARNING' SPI.c:153: warning: no previous prototype for `XS__ERROR' SPI.c:168: warning: no previous prototype for `XS__spi_exec_query' SPI.c:197: warning: no previous prototype for `boot_SPI' /usr/libdata/perl5/5.00503/i386-bsdos/CORE/patchlevel.h:41: warning: `local_patches' defined but not used ppport.h:564: warning: `sv_2pv_nolen' defined but not used -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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
[HACKERS] New compile warnings for inheritance
I am seeing the following regression failures with CVS head and inheritance tests. They look like sort order problems: --- *** ./expected/inherit.out Wed Dec 1 16:53:51 2004 --- ./results/inherit.out Wed Dec 1 17:10:12 2004 *** *** 561,573 relname | f1 | f2 -++- bar | 4 | 4 - bar | 3 | 103 - bar | 2 | 102 bar | 1 | 101 bar2| 4 | 4 - bar2| 3 | 103 - bar2| 2 | 102 bar2| 1 | 101 (8 rows) /* Test inheritance of structure (LIKE) */ --- 561,573 relname | f1 | f2 -++- bar | 4 | 4 bar | 1 | 101 + bar | 2 | 102 + bar | 3 | 103 bar2| 4 | 4 bar2| 1 | 101 + bar2| 2 | 102 + bar2| 3 | 103 (8 rows) /* Test inheritance of structure (LIKE) */ == -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] plperl Safe restrictions
Uh, what was the TODO here? I forgot. --- John Hansen wrote: I think it is *way* too late in the dev cycle to be proposing this. Maybe it should be a TODO item - I at least don't have time even to think about the implications os using these pragmas. The effect of the first is achievable via an environment setting, I believe. If you need these badly enough, use plperlu where there are no restrictions to overcome - the big problem is that 'use anything' requires that we enable the 'require' op, and that is certainly not going to happen without a great deal of thought. Fair enough, was just a suggestion as they seem obviously useful, even to the non-superuser plperl programmer. TODO item would suffice :) ... John ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] New compile warnings for inheritance
Bruce Momjian [EMAIL PROTECTED] writes: I am seeing the following regression failures with CVS head and inheritance tests. They look like sort order problems: Does it go away if you initdb? I suspect the size of pg_class is a factor in the choice of plan for that query. You seem to still be getting the old plan ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] plperl Safe restrictions
Bruce Momjian [EMAIL PROTECTED] writes: Uh, what was the TODO here? I forgot. I think we already did what we decided was safe there. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] plperl Safe restrictions
Bruce Momjian said: Uh, what was the TODO here? I forgot. John wanted us to allow use of the 'locale' and 'utf8' pragmas in trusted code. If there's a TODO it would be to investigate the possibility, as I am very far from certain that there is a simple way to do it safely right now. Maybe when we get plperl using GUC settings and running some interpreter initialisation it could be done. These are things on my agenda. cheers andrew --- John Hansen wrote: I think it is *way* too late in the dev cycle to be proposing this. Maybe it should be a TODO item - I at least don't have time even to think about the implications os using these pragmas. The effect of the first is achievable via an environment setting, I believe. If you need these badly enough, use plperlu where there are no restrictions to overcome - the big problem is that 'use anything' requires that we enable the 'require' op, and that is certainly not going to happen without a great deal of thought. Fair enough, was just a suggestion as they seem obviously useful, even to the non-superuser plperl programmer. TODO item would suffice :) ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error handling in plperl and pltcl
Richard Huxton, It feels more distinct to me. I'll grant you I'm only a sample size of 1 though. Perhaps more distinct, but: - Using savepoints together with try/catch is not exactly an unknown concept. Try Google and you'll see a fair amount of examples advocating the approach that I suggest. - If I have to learn yet another new thing, I'd like to learn how to use savepoints since that knowledge can be used everywhere. - There's no such thing as a pgtry in the Tcl language (nor in any other language), thus you change the language as such. - Tcl code will look different depending on if it's client code or code residing in the backend. I.e. the construct is not portable. Then again, perhaps the Tcl bindings are very different anyway so that argument may be less important. For PL/Java it makes a lot of sense since the client and server implementation uses a common set of interfaces. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Adding Reply-To: listname to Lists configuration ...
On Wed, Dec 01, 2004 at 02:02:41AM -0600, Bruno Wolff III wrote: On Mon, Nov 29, 2004 at 12:49:46 +, Chris Green [EMAIL PROTECTED] wrote: This is a perpetual problem, if people all used the same MUA and (assuming it has the capability) all used the 'reply to list' command to reply to the list everything would be wonderful! :-) I think using mail-followup-to is better than having people do reply to list. It depends on how the 'reply to list' is implemented surely. With mutt (the MUA I use) you specify the addresses of known mailing lists and the 'reply to List' command uses this to detect from the headers whether the mail is from a list or not and acts accordingly. I think the main benefit to having reply-to point to the list is for supporting clueless users on lists (who don't seem to understand the difference between reply to sender and reply to all) and I don't think we have too many of those here. The disadvantage of setting Reply-To: to point to the list for me is that it would override my specific Reply-To: and thus prevent people sending replies direct to me if they happened to want to do that. A message sent to my From: address gets sorted by procmail to a very low priority mailbox which I may well overlook. (I use a different address for list subscriptions from the one I use for personal mail) People who don't want separate copies of messages should set the mail-followup-to header to indicate that preference. This isn't perfect since not all mail clients support this and some set up is required to make your client aware of the list. It is also possible for mailing list As I said my MUA does this. software to handle this preference for you (by not sending copies to addresses on the list that appear in the recipient headers), but I don't know if the software in use has that capability. -- Chris Green ([EMAIL PROTECTED]) Never ascribe to malice, that which can be explained by incompetence. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Bruce Momjian wrote: Comments? This seems like our only solution. This would be a transparent solution. Another approach would be: - Use the old 7.3 approach by default. This means perfect backward compatibility for single-threaded apps and broken multithreaded apps. - Add a new PQinitDB(int disableSigpipeHandler) initialization function. Document that multithreaded apps must call the function with disableSigpipeHandle=1 and handle SIGPIPE for libpq. Perhaps with a reference implementation in libpq (i.e. a sigpipeMode with 0 for old approach, 1 for do nothing, 2 for install our own handler). It would prefer that approach: It means that the multithreaded libpq apps must be updated [are there any?], but the solution is simpler and less fragile than calling 4 signal handling function in a row to selectively block SIGPIPE per-thread. -- Manfred ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Index used incorrectly with regular expressions on 7.4.6
Index is used incorrectly if constant part of the string ends with \d, probably also with other escapes. foo=# explain select count(*) from loc where url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'; QUERY PLAN Aggregate (cost=3.46..3.46 rows=1 width=0) - Index Scan using url_index on loc (cost=0.00..3.46 rows=1 width=0) Index Cond: ((url = 'http://www.7-eleven.com/newsroom/articles.asp?p=d'::text) AND (url 'http://www.7-eleven.com/newsroom/articles.asp?p=e'::text)) Filter: (url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'::text) (4 rows) foo=# select count(*) from loc where url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'; count --- 0 (1 row) foo=# set enable_indexscan = off; SET foo=# explain select count(*) from loc where url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'; QUERY PLAN Aggregate (cost=3056.41..3056.41 rows=1 width=0) - Seq Scan on loc (cost=0.00..3056.40 rows=1 width=0) Filter: (url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'::text) (3 rows) foo=# select count(*) from loc where url ~ '^http://www\\.7-eleven\\.com/newsroom/articles\\.asp\\?p=\\d+'; count --- 281 (1 row) -- Antti Salmela ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] New compile warnings for inheritance
On Wed, 2004-12-01 at 17:22 -0500, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am seeing the following regression failures with CVS head and inheritance tests. They look like sort order problems: Does it go away if you initdb? I suspect the size of pg_class is a factor in the choice of plan for that query. You seem to still be getting the old plan ... I see regression test failures in both inheritence and join (after a fresh initdb). Attached is the regression.diffs, gzip'ed. -Neil regression.diffs.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] New compile warnings for inheritance
Neil Conway [EMAIL PROTECTED] writes: I see regression test failures in both inheritence and join (after a fresh initdb). Attached is the regression.diffs, gzip'ed. Yeah, so do I on a Linux machine. It looks like the dynamic size estimates are highlighting some platform dependencies, such as the effect of different MAXALIGN values on the costs, that we happened not to see in the regression tests before. I'm working on cleaning it up ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Please release (was Re: [HACKERS] nodeAgg perf tweak)
On Wed, 2004-12-01 at 11:08 -0300, Alvaro Herrera wrote: When would the experimentation end, and 8.0 be finally released? As I said, this is work for 8.1; I don't think it ought to affect the release schedule of 8.0 (perhaps in some marginal way because Tom might spend some time discussing issues with me, but I'll leave it up to Tom to decide how best to spend his time). There's real development waiting only for release to happen ... There is real development being done as we speak, that will be landed to 8.1 when it branches :) I think you've raised two distinct issues: (1) Release scheduling: time-based releases vs. feature-based releases, how long the beta period is, and so on. (2) Development methodology: how do we make it easy for people to engage in new development while the mainline is being stabilized for a release? If hard development was done in branches and only merged when stable, we could do this. I think this might be worth considering as an improvement to #2. What I've been experimenting with locally is doing development in a private VC system, and merging in mainline CVS changes every week or so. That allows me to keep long-running projects in their own private branchs and to take advantage of the VC system's merge support, without needing to change the work environment used by other developers. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New compile warnings for inheritance
Neil Conway [EMAIL PROTECTED] writes: Bruce Momjian [EMAIL PROTECTED] writes: I am seeing the following regression failures with CVS head and inheritance tests. They look like sort order problems: I see regression test failures in both inheritence and join (after a fresh initdb). Attached is the regression.diffs, gzip'ed. Okay, I committed some tweaks to reduce platform dependencies. I get passes now on all three platforms I have handy. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Fix for q with psql display paging dumps out of psql
You will be glad to know that 8.0 will use a different implementation
for thread handling of SIGPIPE, though your asynchronous handling of
SIGPIPE will still cause problems.
---
Jim Seymour wrote:
Hi,
I'm kind of wondering if anybody on the dev team noticed this and
what, if anything, they planned to do with it?
Jim
[EMAIL PROTECTED] (Jim Seymour) wrote:
Hi,
Environment:
SunOS 5.7 Generic_106541-29 sun4u sparc SUNW,UltraSPARC-IIi-Engine
Postgresql-7.4.6
Build config: --with-java --enable-thread-safety
gcc version 3.3.1
less-381
readline-4.3
$ echo $PAGER
/usr/local/bin/less
$ echo $LESS
-e
I recently upgraded from 7.4.2 to 7.4.6 and ran into an annoying
problem. Thereafter, most times, quitting out of paged display
output would dump me straight out of psql if the query result was
sufficiently large.
A bit of debugging revealed psql receiving a SIGPIPE to be the
problem.
Building pgsql w/o --enable-thread-safety eliminated the problem.
It looks like the culprit is asynchronous SIGPIPE signals under Solaris
7 when using thread-safe libraries. Here's a reference:
http://www.lambdacs.com/cpt/FAQ.html#Q339.
I do not know if Solaris 8 and beyond behave this way. (Apparently
async SIGPIPE is not POSIX-compliant, so one hopes Sun has fixed this
broken behaviour.)
Here's a fix that's simple, effective and doesn't hurt anything else:
- begin included text --
*** src/bin/psql/print.c-orig Wed Nov 17 08:04:47 2004
--- src/bin/psql/print.cSat Nov 20 10:43:22 2004
***
*** 1119,1124
--- 1119,1128
{
pclose(output);
#ifndef WIN32
+ /* The SIG_IGN is to compensate for broken Solaris 7
+* (async) SIGPIPE handling with --enable-thread-safety
+*/
+ pqsignal(SIGPIPE, SIG_IGN);
pqsignal(SIGPIPE, SIG_DFL);
#endif
}
-- end included text ---
Thanks to Andrew, over at SuperNews, for the idea.
Regards,
Jim
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: Please release (was Re: [HACKERS] nodeAgg perf tweak)
Neil Conway said: On Wed, 2004-12-01 at 11:08 -0300, Alvaro Herrera wrote: There's real development waiting only for release to happen ... There is real development being done as we speak, that will be landed to 8.1 when it branches :) I've raised this before, but would like to suggest again that there might be virtue in branching earlier in the dev cycle - maybe around the time of the first beta. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Index used incorrectly with regular expressions on 7.4.6
Antti Salmela [EMAIL PROTECTED] writes:
Index is used incorrectly if constant part of the string ends with \d,
Yeah, you're right --- that code predates our use of the new regexp
engine, and it didn't know that escapes aren't simply quoted characters.
Now that I look at it, it's got a multibyte problem too :-(
If you need a patch right away, here's what I applied to 7.4 branch.
regards, tom lane
Index: selfuncs.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.147.2.3
diff -c -r1.147.2.3 selfuncs.c
*** selfuncs.c 27 Feb 2004 21:44:44 - 1.147.2.3
--- selfuncs.c 2 Dec 2004 02:35:48 -
***
*** 3218,3223
--- 3218,3225
char *match;
int pos,
match_pos,
+ prev_pos,
+ prev_match_pos,
paren_depth;
char *patt;
char *rest;
***
*** 3278,3288
/* OK, allocate space for pattern */
match = palloc(strlen(patt) + 1);
! match_pos = 0;
/* note start at pos 1 to skip leading ^ */
! for (pos = 1; patt[pos]; pos++)
{
/*
* Check for characters that indicate multiple possible matches
* here. XXX I suspect isalpha() is not an adequately
--- 3280,3292
/* OK, allocate space for pattern */
match = palloc(strlen(patt) + 1);
! prev_match_pos = match_pos = 0;
/* note start at pos 1 to skip leading ^ */
! for (prev_pos = pos = 1; patt[pos]; )
{
+ int len;
+
/*
* Check for characters that indicate multiple possible matches
* here. XXX I suspect isalpha() is not an adequately
***
*** 3297,3302
--- 3301,3314
break;
/*
+* In AREs, backslash followed by alphanumeric is an escape, not
+* a quoted character. Must treat it as having multiple
possible
+* matches.
+*/
+ if (patt[pos] == '\\' isalnum((unsigned char) patt[pos + 1]))
+ break;
+
+ /*
* Check for quantifiers. Except for +, this means the
preceding
* character is optional, so we must remove it from the prefix
* too!
***
*** 3305,3318
patt[pos] == '?' ||
patt[pos] == '{')
{
! if (match_pos 0)
! match_pos--;
! pos--;
break;
}
if (patt[pos] == '+')
{
! pos--;
break;
}
if (patt[pos] == '\\')
--- 3317,3329
patt[pos] == '?' ||
patt[pos] == '{')
{
! match_pos = prev_match_pos;
! pos = prev_pos;
break;
}
if (patt[pos] == '+')
{
! pos = prev_pos;
break;
}
if (patt[pos] == '\\')
***
*** 3322,3328
if (patt[pos] == '\0')
break;
}
! match[match_pos++] = patt[pos];
}
match[match_pos] = '\0';
--- ,3346
if (patt[pos] == '\0')
break;
}
! /* save position in case we need to back up on next loop cycle
*/
! prev_match_pos = match_pos;
! prev_pos = pos;
! /* must use encoding-aware processing here */
! len = pg_mblen(patt[pos]);
! memcpy(match[match_pos], patt[pos], len);
! match_pos += len;
! pos += len;
}
match[match_pos] = '\0';
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] lwlocks and starvation
Neil, where are we on this? Should we add comments? Add a TODO? A patch? --- Neil Conway wrote: On Wed, 2004-11-24 at 23:30 -0500, Tom Lane wrote: It is not a 100% solution because it does not cover the case where a waiting exclusive locker is released, then a new shared locker arrives at the lock before the exclusive locker is given any cycles to acquire the lock. However I don't see any cure for the latter problem that's not worse than the disease Yeah, I don't think this is a problem -- eventually the exclusive waiter will win the coin flip anyway. On the other hand we might consider that this isn't a big problem and just leave things as they are. We haven't seen any indication that starvation is a real problem in practice, and so it might be better to avoid extra trips through the kernel scheduler. Yes, I'm a little concerned about applying a patch to address what is, so far, an entirely academic concern -- especially if it might hurt performance. -Neil -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] New compile warnings for inheritance
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Does it go away if you initdb? I suspect the size of pg_class is a factor in the choice of plan for that query. You seem to still be getting the old plan ... An initdb does not change the regression failure. Sorry. Hmph. I'm unsure why there's a platform dependency there, but we should probably just add an ORDER BY to suppress it. Will do. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] New compile warnings for inheritance
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Hmph. I'm unsure why there's a platform dependency there, but we should probably just add an ORDER BY to suppress it. Will do. Why did it just appear? Optimizer changes in the past few days? I did just change the planner, but what I don't understand at the moment is why the results changed for me and not for you. It looks like it's not actually the join to pg_class that is determining this, but the plan used for the UPDATE just above it. That involves only freshly-created tables and so it ought to be pretty much the same planner problem for everyone. Odd. I'm building now on a Linux machine to see if I can reproduce your result when using little-endian hardware. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Manfred Spraul [EMAIL PROTECTED] writes: Is that really worthwhile? There are half a dozend assumption about the C library and kernel internal efficiency of the signal handling functions in the proposal. Adding a PQinitLib function is obviously a larger change, but it solves the problem. Not really: it only solves the problem *if you change the application*, which is IMHO not acceptable. In particular, why should a non-threaded app expect to have to change to deal with this issue? But we can't safely build a thread-safe libpq.so for general use if it breaks non-threaded apps that haven't been changed. As for the efficiency argument, we have been doing two pqsignal()s per send() for years and years; I see no reason to think this is worse. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
His idea of pthread_sigmask/send/sigpending/sigwait/restore-mask. Seems
we could also check errno for SIGPIPE rather than calling sigpending.
He has a concern about an application that already blocked SIGPIPE and
has a pending SIGPIPE signal waiting already. One idea would be to
check for sigpending() before the send() and clear the signal only if
SIGPIPE wasn't pending before the call. I realize that if our send()
also generates a SIGPIPE it would remove the previous realtime signal
info but that seems a minor problem.
Supposing that we don't touch the signal handler at all, then it is
possible that the application has set it to SIG_IGN, in which case a
SIGPIPE would be discarded rather than going into the pending mask.
Right.
So I think the logic has to be:
pthread_sigmask to block SIGPIPE and save existing signal mask
send();
if (errno == EPIPE)
{
if (sigpending indicates SIGPIPE pending)
use sigwait to clear SIGPIPE;
}
pthread_sigmask to restore prior signal mask
Yes, that is the logic in my patch, except that I don't check errno, I
just call sigpending(). There are a few writes and it seemed impossible
to check them all. Also, we might get a SIGPIPE but as you mentioned
the SIGPIPE may have been ignored.
The only case that is problematic is where the application had
already blocked SIGPIPE and there is a pending SIGPIPE signal when
we are entered, *and* we get SIGPIPE ourselves.
Right.
If the C library does not support queued signals then our sigwait will
clear both our own EPIPE and the pending signal. This is annoying but
it doesn't seem fatal --- if the app is writing on a closed pipe then
it'll probably try it again later and get the signal again.
Right.
If the C library does support queued signals then we will read the
existing SIGPIPE condition and leave our own signal in the queue. This
is no problem to the extent that one pending SIGPIPE looks just like
another --- does anyone know of platforms where there is additional info
carried by a SIGPIPE event?
Right. We could add a sigpending() check and if set we just don't clear
the SIGPIPE signal, but this adds an additional function call into the
mix that seemed unnecessary, though I could be convinced otherwise.
This seems workable as long as we document the possible gotchas.
My patch documents the limitation. I finished reading the
Addison-Wesley Programming with POSIX Threads book and it has helped
me with this stuff.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 3: 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
Re: [HACKERS] New compile warnings for inheritance
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Does it go away if you initdb? I suspect the size of pg_class is a factor in the choice of plan for that query. You seem to still be getting the old plan ... An initdb does not change the regression failure. Sorry. Hmph. I'm unsure why there's a platform dependency there, but we should probably just add an ORDER BY to suppress it. Will do. Why did it just appear? Optimizer changes in the past few days? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] New compile warnings for inheritance
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I am seeing the following regression failures with CVS head and inheritance tests. They look like sort order problems: Does it go away if you initdb? I suspect the size of pg_class is a factor in the choice of plan for that query. You seem to still be getting the old plan ... An initdb does not change the regression failure. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Manfred Spraul wrote: This seems workable as long as we document the possible gotchas. Is that really worthwhile? There are half a dozend assumption about the C library and kernel internal efficiency of the signal handling functions in the proposal. Adding a PQinitLib function is obviously a The main path uses pthread_sigmask() and sigpending(). Are those possible performance problems? I see how signal() would be a thread problem, but not those. larger change, but it solves the problem. I'm aware of one minor gotcha: PQinSend() is not usable right now: it relies on the initialization of pq_thread_in_send, which is only created in the middle of the first connectDB(). That makes proper signal handling for the first connection impossible. I think that whole PQinSend thing is pretty ugly, even if I did write it. My current patch seems like a great improvement. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] nodeAgg perf tweak
Neil Conway [EMAIL PROTECTED] writes:
The attached patch invokes the transition function in the current memory
context. I don't think that's right: a memory leak in an aggregate's
transition function would be problematic when we're invoked from a
per-query memory context, as is the case with advance_aggregates().
Agreed. You can't really assume that the transition function is
leak-free, particularly not when it's returning a pass-by-ref datatype.
Perhaps we need an additional short-lived memory context in
AggStatePerAggData: we could invoke the transition function in that
context, and reset it once per, say, 1000 tuples.
This seems like it might work. Instead of copying the result into the
aggcontext on every cycle, we could copy it only when we intend to reset
the working context. However there is still a risk: the function might
return a pointer into the source tuple, not something that's in the
working context at all. (See text_smaller() as one example.) This is
problematic since the source tuple will go away on the next cycle.
The existing copying code takes care of this case as well as the one
where the result is in per_tuple_context.
I'm a bit surprised that you measured a significant performance speedup
from removing the copying step. Awhile ago I experimented with hacking
the count() aggregate function internally to avoid pallocs, and was
disappointed to measure no noticeable speedup at all. Digging in the
list archives, it looks like I neglected to report that failure, but
I do still have the patch and I attach it here. This was from late
Dec '03 so it'd be against just-past-7.4 sources. I'd be interested
to hear how this compares to what you did under your test conditions;
maybe I was using a bogus test case.
regards, tom lane
*** src/backend/executor/nodeAgg.c.orig Sat Nov 29 14:51:48 2003
--- src/backend/executor/nodeAgg.c Sun Dec 21 16:02:25 2003
***
*** 350,356
*/
/* MemSet(fcinfo, 0, sizeof(fcinfo)); */
! fcinfo.context = NULL;
fcinfo.resultinfo = NULL;
fcinfo.isnull = false;
--- 350,356
*/
/* MemSet(fcinfo, 0, sizeof(fcinfo)); */
! fcinfo.context = (void *) aggstate;
fcinfo.resultinfo = NULL;
fcinfo.isnull = false;
***
*** 528,533
--- 528,534
FunctionCallInfoData fcinfo;
MemSet(fcinfo, 0, sizeof(fcinfo));
+ fcinfo.context = (void *) aggstate;
fcinfo.flinfo = peraggstate-finalfn;
fcinfo.nargs = 1;
fcinfo.arg[0] = pergroupstate-transValue;
*** /home/postgres/pgsql/src/backend/utils/adt/int8.c.orig Mon Dec 1
17:29:19 2003
--- /home/postgres/pgsql/src/backend/utils/adt/int8.c Sun Dec 21 16:03:43 2003
***
*** 17,22
--- 17,23
#include math.h
#include libpq/pqformat.h
+ #include nodes/nodes.h
#include utils/int8.h
***
*** 565,573
Datum
int8inc(PG_FUNCTION_ARGS)
{
! int64 arg = PG_GETARG_INT64(0);
! PG_RETURN_INT64(arg + 1);
}
Datum
--- 566,594
Datum
int8inc(PG_FUNCTION_ARGS)
{
! if (fcinfo-context != NULL IsA(fcinfo-context, AggState))
! {
! /*
!* Special case to avoid palloc overhead for COUNT(). Note:
this
!* assumes int8 is a pass-by-ref type; if we ever support
pass-by-val
!* int8, this should be ifdef'd out when int8 is pass-by-val.
!*
!* When called from nodeAgg, we know that the argument is
modifiable
!* local storage, so just update it in-place.
!*/
! int64 *arg = (int64 *) PG_GETARG_POINTER(0);
!
! (*arg)++;
! PG_RETURN_POINTER(arg);
! }
! else
! {
! /* Not called by nodeAgg, so just do it the dumb way */
! int64 arg = PG_GETARG_INT64(0);
!
! PG_RETURN_INT64(arg + 1);
! }
}
Datum
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: Please release (was Re: [HACKERS] nodeAgg perf tweak)
Andrew Dunstan [EMAIL PROTECTED] writes: I've raised this before, but would like to suggest again that there might be virtue in branching earlier in the dev cycle - maybe around the time of the first beta. Given the amount of patching that's gone on, branching 8.1 earlier would have meant a great deal more work for the committers, in the form of double-patching stuff. And I don't know about the other committers, but I've certainly had no spare bandwidth for reviewing or applying 8.1-cycle patches anyway. So I think we made the right move to delay the branch. Core hasn't really talked about the point yet, but I suspect we'll make the branch after 8.0RC1 appears (which hopefully will be Real Soon). regards, tom lane ---(end of broadcast)--- TIP 3: 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
Re: [HACKERS] New compile warnings for inheritance
On Wed, Dec 01, 2004 at 08:35:40PM -0500, Tom Lane wrote: Okay, I committed some tweaks to reduce platform dependencies. I get passes now on all three platforms I have handy. It passes on my FreeBSD 4.10-STABLE box. Don't know about Solaris 9/sparc yet -- the whole dance takes over an hour on my (t)rusty ol' Ultra 1. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Tom Lane wrote: If the C library does support queued signals then we will read the existing SIGPIPE condition and leave our own signal in the queue. This is no problem to the extent that one pending SIGPIPE looks just like another --- does anyone know of platforms where there is additional info carried by a SIGPIPE event? POSIX.1b / SA_SIGINFO? SIGPIPE does not fill much of siginfo_t, but the 3rd handler arg has the interrupted execution context. -O ---(end of broadcast)--- TIP 3: 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
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Bruce Momjian [EMAIL PROTECTED] writes: Yes, that is the logic in my patch, except that I don't check errno, I just call sigpending(). No, that's wrong: if there is a pending SIGPIPE that belongs to the outer app, you'd clear it. There are a few writes and it seemed impossible to check them all. Hmm? There is only one place this needs to be done, namely pqsecure_write. BTW, have you posted the patch yet or are you still working on it? The mail server seems a bit flaky today ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: Please release (was Re: [HACKERS] nodeAgg perf tweak)
On Wed, 2004-12-01 at 19:34 +0100, [EMAIL PROTECTED] wrote: I regard performance testing as an essential part of the release process of any performance critical software. Most earlier beta releases were fixing functional problems and now the focus has moved to performance related issues. I don't agree. There is an important difference between fixing a performance regression and making a performance improvement. If a change made in 8.0 resulted in a significant, unintentional performance regression, I think there would be grounds for fixing it (assuming the fix was fairly noninvasive). The aggregate patch addresses a problem that has existed for a long time (7.4 at the least); I don't see the need to delay 8.0 to fix it. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] New compile warnings for inheritance
On Wed, Dec 01, 2004 at 08:10:24PM -0700, Michael Fuhr wrote: Don't know about Solaris 9/sparc yet -- the whole dance takes over an hour on my (t)rusty ol' Ultra 1. Solaris 9/sparc passes all tests. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Error handling in plperl and pltcl
On 12/1/2004 1:35 PM, Brett Schwarz wrote:
--- Jan Wieck [EMAIL PROTECTED] wrote:
On 12/1/2004 9:23 AM, Jan Wieck wrote:
On 12/1/2004 4:27 AM, Richard Huxton wrote:
Thomas Hallgren wrote:
Richard Huxton wrote:
Can I make some counter-proposals?
1. Wrap each function body/call (same thing
here afaict) in a
sub-transaction. An exception can be caught
within that function, and
all the spi in that function is then rolled
back. This is rubbish, but
at least it's predictable and allows you to
write to a log table and
throw another exception.
This will be even worse since it will impose the
subtransaction overhead
on everything, even functions that never do any
database access. Perhaps
this approach would be feasible if imposed on
volatile functions only,
but then again, the volatility of a function
cannot be trusted since we
have no way of defining a stable but with side
effects type.
Actually, I was thinking of setting a flag and
then on the first SPI
call start the subtrans.
2. For pl/tcl introduce a pgtry { } catch { }
which just starts a
sub-transaction and does standard try/catch. I
don't use TCL, but from
the little I know this should be
straightforward.
If you know how to use special constructs like
this, what's wrong with
actually using savepoints verbatim? I.e.
INSERT 1
INSERT 2
SAVEPOINT foo
try {
INSERT 3
INSERT 4
RELEASE foo
}
catch WHATEVER {
ROLLBACK TO foo
INSERT 5
INSERT 6
}
IMHO a very clean, sensible, and easily
understood approach that doesn't
clobber the language.
But is the problem not that forgetting to use
SAVEPOINT can get us in
trouble with clearing up after an exception?
That's the main thrust of
Tom's per-statement stuff AFAICT. And again,
you're not going to see the
problem until an exception is thrown.
I think the following would a) be a drop in
replacement without any side
effects or performance impact for PL/Tcl functions
not using catch and
b) give catch a sensible and correct behaviour.
One can _replace_ the Tcl catch command with his
own C function. This
can be done during the interpreter initialization
when loading the
PL/Tcl module. The new catch would
push a status NEED_SUBTRANS onto a stack
call Tcl_Eval() for the first command
argument
if TCL_ERROR {
pop status from stack
if popped status == HAVE_SUBTRANS {
rollback subtransaction
}
if a second argument exists {
store interpreter result in variable
}
return TCL_ERROR
er ... no ... must return a true boolean with TCL_OK
here
}
pop status from stack
if popped status == HAVE_SUBTRANS {
commit subtransaction
}
return result code from Tcl_Eval()
and here it must put a false boolean into the Tcl
result ... not 100%
sure about the result code. Must check if it's
possible to return or
break from inside a catch block ... if not, then
catch allways turns the
internal result code into TCL_OK. Anyhow, you get
the idea.
Yes, you can have break, return in a catch
statement...it would return the exception code for
that statement (i.e. TCL_BREAK, TCL_RETURN).
Yeah ... little tests are nice :-)
catch allways returns the numeric Tcl result status, with TCL_OK being
0, TCL_ERROR being 1 and so on.
I like this proposal, just as long as it behaves
exactly like Tcl's catch when there is no SPI function
call.
That's what I intended, plus that the catch-nesting automatically
represents the subtransaction nesting. I can't really see any reason why
those two should not be bound together. Does anybody?
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postresql 8.0 Beta 3 - SELECT ... FOR UPDATE
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Rod Taylor wrote: Anyway, it shows a situation where it would be nice to differentiate between statement_timeout and lock_timeout OR it demonstrates that I should be using userlocks... Wouldn't a LOCK NOWAIT be a better solution? That is new in 8.0. LOCK NOWAIT is only helpful if you can express your problem as not wanting to wait for a table-level lock. When you're trying to grab a row-level lock via SELECT FOR UPDATE, there isn't any provision for NOWAIT. The notion of a global lock_timeout setting is bogus IMHO, because every proposed application of it has failed to consider the locks taken internally by the system. But that objection wouldn't apply to a SELECT FOR UPDATE NOWAIT command where the no wait behavior only applied to the row lock being explicitly grabbed. I thought I remembered someone working on such a thing just recently. Added to TODO: * Allow FOR UPDATE queries to do NOWAIT locks -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] VACUUM FULL FREEZE is unsafe
Tom Lane wrote: Thomas F.O'Connell [EMAIL PROTECTED] writes: So why not have VACUUM FULL FREEZE just do what you propose: VACUUM FULL then VACUUM FREEZE. The objective is to make it more safe, not less so. Doing that would require rewriting a whole bunch of code, which I am not up for at this stage of the release cycle. Has this been corrected/disallowed yet? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] lwlocks and starvation
On Wed, 2004-12-01 at 21:51 -0500, Bruce Momjian wrote:
Neil, where are we on this? Should we add comments? Add a TODO? A patch?
I'm not sure what the right resolution is. As I said, I don't think it's
wise to apply a patch that could have a significant impact on
performance without (a) testing its performance effect and/or (b) having
any evidence that the problem it addresses actually effects anyone in
the real world. I'll try to run some benchmarks when I get a chance.
I wrote up most of a patch to implement the wake up all shared wakers
on LWLockRelease() behavior to see how that would change performance,
but the patch has a subtle bug in it that I can't seem to find (I've
attached it -- comments welcome).
Certainly if we decide to leave things as they are I think we ought to
document why the behavior is intentional, but I don't think we have
enough data to make that decision yet.
-Neil
--- src/backend/storage/lmgr/lwlock.c
+++ src/backend/storage/lmgr/lwlock.c
@@ -411,7 +411,7 @@
LWLockRelease(LWLockId lockid)
{
volatile LWLock *lock = LWLockArray + lockid;
- PGPROC *head;
+ PGPROC *release = NULL;
PGPROC *proc;
int i;
@@ -450,34 +450,67 @@
* any waiters if someone has already awakened waiters that haven't
* yet acquired the lock.
*/
- head = lock-head;
- if (head != NULL)
+ if (lock-head != NULL)
{
if (lock-exclusive == 0 lock-shared == 0 lock-releaseOK)
{
/*
- * Remove the to-be-awakened PGPROCs from the queue. If the
- * front waiter wants exclusive lock, awaken him only.
- * Otherwise awaken as many waiters as want shared access.
+ * Remove the to-be-awakened PGPROCs from the queue. If
+ * the front waiter wants an exclusive lock, awaken him
+ * only. Otherwise, awaken all the shared waiters in the
+ * queue.
*/
- proc = head;
- if (!proc-lwExclusive)
+ proc = lock-head;
+ if (proc-lwExclusive)
{
-while (proc-lwWaitLink != NULL
- !proc-lwWaitLink-lwExclusive)
+lock-head = proc-lwWaitLink;
+proc-lwWaitLink = NULL;
+release = proc;
+ }
+ else
+ {
+/*
+ * Walk through the wait queue. We form two linked
+ * lists: exclusive waiters, who will be the lock's
+ * new wait queue, and shared waiters, all of whom
+ * will be awaken.
+ */
+PGPROC *exclusive_head = NULL;
+PGPROC *exclusive_tail = NULL;
+PGPROC *shared_head = proc;
+PGPROC *shared_tail = proc;
+proc = proc-lwWaitLink;
+while (proc != NULL)
+{
+ if (proc-lwExclusive)
+ {
+ if (!exclusive_head)
+ exclusive_head = exclusive_tail = proc;
+ else
+ {
+ exclusive_tail-lwWaitLink = proc;
+ exclusive_tail = proc;
+ }
+ }
+ else
+ {
+ shared_tail-lwWaitLink = proc;
+ shared_tail = proc;
+ }
proc = proc-lwWaitLink;
+}
+
+/* NULL terminate both lists */
+shared_tail-lwWaitLink = NULL;
+if (exclusive_tail)
+ exclusive_tail-lwWaitLink = NULL;
+/* The exclusive list is the new wait queue */
+lock-head = exclusive_head;
+release = shared_head;
}
- /* proc is now the last PGPROC to be released */
- lock-head = proc-lwWaitLink;
- proc-lwWaitLink = NULL;
/* prevent additional wakeups until retryer gets to run */
lock-releaseOK = false;
}
- else
- {
- /* lock is still held, can't awaken anything */
- head = NULL;
- }
}
/* We are done updating shared state of the lock itself. */
@@ -486,11 +519,11 @@
/*
* Awaken any waiters I removed from the queue.
*/
- while (head != NULL)
+ while (release != NULL)
{
LOG_LWDEBUG(LWLockRelease, lockid, release waiter);
- proc = head;
- head = proc-lwWaitLink;
+ proc = release;
+ release = proc-lwWaitLink;
proc-lwWaitLink = NULL;
proc-lwWaiting = false;
PGSemaphoreUnlock(proc-sem);
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] readline/libedit selection
This recent change to readline/libedit selection isn't quite right. http://archives.postgresql.org/pgsql-committers/2004-11/msg00330.php It doesn't link in libtermcap with libedit which leads to: checking for readline.h... no configure: error: readline header not found If you have libedit already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable libedit support. And in config.log: configure:5987: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing conftest.c -ledit -lcrypt -lresolv -lcompat -lm -lutil 5 /usr/lib/libedit.so: undefined reference to `tgetnum' /usr/lib/libedit.so: undefined reference to `tgoto' /usr/lib/libedit.so: undefined reference to `tgetflag' /usr/lib/libedit.so: undefined reference to `tputs' /usr/lib/libedit.so: undefined reference to `tgetent' /usr/lib/libedit.so: undefined reference to `tgetstr' This is on x86 NetBSD 1.6. Kris Jurka ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and psql not on same page about SIGPIPE
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
His idea of pthread_sigmask/send/sigpending/sigwait/restore-mask. Seems
we could also check errno for SIGPIPE rather than calling sigpending.
He has a concern about an application that already blocked SIGPIPE and
has a pending SIGPIPE signal waiting already. One idea would be to
check for sigpending() before the send() and clear the signal only if
SIGPIPE wasn't pending before the call. I realize that if our send()
also generates a SIGPIPE it would remove the previous realtime signal
info but that seems a minor problem.
Supposing that we don't touch the signal handler at all, then it is
possible that the application has set it to SIG_IGN, in which case a
SIGPIPE would be discarded rather than going into the pending mask.
So I think the logic has to be:
pthread_sigmask to block SIGPIPE and save existing signal mask
send();
if (errno == EPIPE)
{
if (sigpending indicates SIGPIPE pending)
use sigwait to clear SIGPIPE;
}
pthread_sigmask to restore prior signal mask
The only case that is problematic is where the application had
already blocked SIGPIPE and there is a pending SIGPIPE signal when
we are entered, *and* we get SIGPIPE ourselves.
If the C library does not support queued signals then our sigwait will
clear both our own EPIPE and the pending signal. This is annoying but
it doesn't seem fatal --- if the app is writing on a closed pipe then
it'll probably try it again later and get the signal again.
If the C library does support queued signals then we will read the
existing SIGPIPE condition and leave our own signal in the queue. This
is no problem to the extent that one pending SIGPIPE looks just like
another --- does anyone know of platforms where there is additional info
carried by a SIGPIPE event?
Linux stores pid/uid together with the signal. pid doesn't matter and no
sane programmer will look at the uid, so it seems to be possible.
This seems workable as long as we document the possible gotchas.
Is that really worthwhile? There are half a dozend assumption about the
C library and kernel internal efficiency of the signal handling
functions in the proposal. Adding a PQinitLib function is obviously a
larger change, but it solves the problem.
I'm aware of one minor gotcha: PQinSend() is not usable right now: it
relies on the initialization of pq_thread_in_send, which is only created
in the middle of the first connectDB(). That makes proper signal
handling for the first connection impossible.
--
Manfred
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] nodeAgg perf tweak
On Wed, 2004-12-01 at 15:54 -0500, Tom Lane wrote: This seems like it might work. Instead of copying the result into the aggcontext on every cycle, we could copy it only when we intend to reset the working context. Right. This is problematic since the source tuple will go away on the next cycle. The existing copying code takes care of this case as well as the one where the result is in per_tuple_context. ISTM it would be reasonable to mandate that aggregate authors return one of three things from their state transition functions: (a) return the previous state value (b) return the next data item value (c) return some other value; if by a pass-by-reference type, allocated in CurrentMemoryContext In the case of (b), we need to copy it in advance_transition_function() to ensure it survives to the next invocation of the state transition function, but that should be doable. For both (a) and (c) we can assume the value has been allocated in the per-1000-rows-transition-function temporary context, and thus we need only copy it when we reset that context. Digging in the list archives, it looks like I neglected to report that failure, but I do still have the patch and I attach it here. This was from late Dec '03 so it'd be against just-past-7.4 sources. I'd be interested to hear how this compares to what you did under your test conditions; maybe I was using a bogus test case. I can reproduce the performance improvement with the patch you sent (I can repost numbers if you like, but your patch and mine resulted in about the same performance when I quickly tested it). You can find the test data at: http://www.langille.org/watch_list_elements.sql.tgz Just load the .sql file, run ANALYZE, and try the queries I posted. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
