Re: [HACKERS] PG Killed by OOM Condition
daveg wrote: > When this happens the machine runs out of memory and swap. Without the oom > killer it simply hangs the machine which is inconvenient as it is at a remote > location. The oom killer usually lets the machine recover and postgres restart > without a hard reboot. > If vm.overcommit is set to 2, wouldn't postgres get a memory allocation error, rather than a hung machine? By the way, what does FreeBSD do? I've never had any memory allocation related headaches on that platform (although I'm fairly new to FreeBSD). Regards, Jeff Davis ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PG Killed by OOM Condition
On Mon, Oct 24, 2005 at 11:26:52PM -0500, Bruno Wolff III wrote:
> On Mon, Oct 24, 2005 at 23:55:07 -0400,
> [EMAIL PROTECTED] wrote:
> > On Mon, Oct 24, 2005 at 10:20:39PM -0500, Bruno Wolff III wrote:
> > > On Mon, Oct 03, 2005 at 23:03:06 +1000,
> > > John Hansen <[EMAIL PROTECTED]> wrote:
> > > > Good people,
> > > > Just had a thought!
> > > > Might it be worth while protecting the postmaster from an OOM Kill on
> > > > Linux by setting /proc/{pid}/oom_adj to -17 ?
> > > > (Described vaguely in mm/oom_kill.c)
> > > Wouldn't it be better to use sysctl to tell the kernel not to over commit
> > > memory in the first place?
> >
> > Only if you don't have large processes in your system that fork()
> > frequently, pushing the reserved memory over the limit, preventing
> > PostgreSQL from allocating memory when it does need it, even though
> > copy-on-write allows plenty of memory to continue to be available -
> > it is just reserved... :-)
> >
> > There isn't a perfect answer.
>
> No, but I would think tying up some disk space as swap space would be a
> better solution. The linux oom killer is really dangerous.
I work with a client that runs 16Gb memory with 16Gb of swap on dual opterons
dedicated to postgres. They have large tables and like hash joins as they are
often the fastest way to a result, so work_mem is set fairly large. Sometimes
postgres is very inaccurate predicting real memory use verses work_mem and
will grow very much larger than expected. Which can result in two or more
postgres processes with over 10 Gb of virtual memory along with the usual 60
or so normal sized ones.
When this happens the machine runs out of memory and swap. Without the oom
killer it simply hangs the machine which is inconvenient as it is at a remote
location. The oom killer usually lets the machine recover and postgres restart
without a hard reboot.
A solution is to use ulimit to set the maximum memory available to a
process. Ideally this would be a pg_ctl or postmaster option so that all the
forked postgresql processes would inherit the ulimit. The advantage over the
oom killer is that only the overly large process fails, and it fails with an
out of memory error and exits cleanly as opposed to having the whole set
of backends restarted.
-dg
--
David Gould [EMAIL PROTECTED]
If simplicity worked, the world would be overrun with insects.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] PG Killed by OOM Condition
On Mon, Oct 24, 2005 at 23:55:07 -0400,
[EMAIL PROTECTED] wrote:
> On Mon, Oct 24, 2005 at 10:20:39PM -0500, Bruno Wolff III wrote:
> > On Mon, Oct 03, 2005 at 23:03:06 +1000,
> > John Hansen <[EMAIL PROTECTED]> wrote:
> > > Good people,
> > > Just had a thought!
> > > Might it be worth while protecting the postmaster from an OOM Kill on
> > > Linux by setting /proc/{pid}/oom_adj to -17 ?
> > > (Described vaguely in mm/oom_kill.c)
> > Wouldn't it be better to use sysctl to tell the kernel not to over commit
> > memory in the first place?
>
> Only if you don't have large processes in your system that fork()
> frequently, pushing the reserved memory over the limit, preventing
> PostgreSQL from allocating memory when it does need it, even though
> copy-on-write allows plenty of memory to continue to be available -
> it is just reserved... :-)
>
> There isn't a perfect answer.
No, but I would think tying up some disk space as swap space would be a
better solution. The linux oom killer is really dangerous.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] PG Killed by OOM Condition
On Mon, Oct 24, 2005 at 10:20:39PM -0500, Bruno Wolff III wrote:
> On Mon, Oct 03, 2005 at 23:03:06 +1000,
> John Hansen <[EMAIL PROTECTED]> wrote:
> > Good people,
> > Just had a thought!
> > Might it be worth while protecting the postmaster from an OOM Kill on
> > Linux by setting /proc/{pid}/oom_adj to -17 ?
> > (Described vaguely in mm/oom_kill.c)
> Wouldn't it be better to use sysctl to tell the kernel not to over commit
> memory in the first place?
Only if you don't have large processes in your system that fork()
frequently, pushing the reserved memory over the limit, preventing
PostgreSQL from allocating memory when it does need it, even though
copy-on-write allows plenty of memory to continue to be available -
it is just reserved... :-)
There isn't a perfect answer.
Cheers,
mark
--
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED]
__
. . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
http://mark.mielke.cc/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
On Mon, 24 Oct 2005, Tom Lane wrote: > Qingqing Zhou <[EMAIL PROTECTED]> writes: > > I tried to persuade myself that removing all WaitForSingleObjectEx() is > > safe ... the thing is we will false alarm EINTR as Magnus said (details to > > repeat it are list below in case). > > Just to repeat myself: there were false alarms before. The interleaving > you describe could equally well happen if a new signal is sent just > after the old code executes WaitForSingleObjectEx and sees that a > previous signal is waiting for it. Both old and new signals can be > cleared by the recipient before the second signal sender gets as far as > setting the event. > Oh, yeah. Just write the detailed case down for the sake of memory: -- For previous code -- false alarm case -- 1. I am killing you signal A: enter_crit; set signal bit; leave_crit; 2. He *has killed* you signal B: 3. You CHECK_FOR_INTERRUPTS(): enter_crit; sig(A); sig(B); ResetEvent(); leave_crit; 4. I finish my killing: SetEvent(); Now the event is signaled but the signal is handled already. Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [HACKERS] 'a' == 'a '
Bruce Momjian wrote: What additional documentation is needed? Some specific discussion of the relationship to the standard would be helpful, perhaps. I guess, but explaining it seems pretty complex in itself, and I am unsure what value it adds. It will give us something to keep the language lawyers occupied :-) cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [HACKERS] 'a' == 'a '
Tom Lane wrote: > Bruce Momjian writes: > > OK, I understand now. It is tempting to think that the difference > > between char() and varchar() is that internally they use a different > > collating sequences, but that isn't the case. If it were, space would > > be ignored during comparisons any place in the string, when in fact, is > > it is only trailing space that char() ignores, e.g.: > > No, you don't understand. The standard defines PAD SPACE as making > *trailing* spaces irrelevant to comparisons, not embedded or leading > spaces. And they regard PAD SPACE as an attribute of a collation rather > than of the string datatype. This seems pretty wacko to me (in > particular it's hard to see how NO PAD is useful with char(N) storage) > but that's what they did. So you can have two collating sequences where in one trailing space is significant, and another that isn't? Strange. > > What additional documentation is needed? > > Some specific discussion of the relationship to the standard would be > helpful, perhaps. I guess, but explaining it seems pretty complex in itself, and I am unsure what value it adds. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
Qingqing Zhou <[EMAIL PROTECTED]> writes: > I tried to persuade myself that removing all WaitForSingleObjectEx() is > safe ... the thing is we will false alarm EINTR as Magnus said (details to > repeat it are list below in case). Just to repeat myself: there were false alarms before. The interleaving you describe could equally well happen if a new signal is sent just after the old code executes WaitForSingleObjectEx and sees that a previous signal is waiting for it. Both old and new signals can be cleared by the recipient before the second signal sender gets as far as setting the event. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
On Mon, 24 Oct 2005, Tom Lane wrote: > > Are we all comfortable that > http://archives.postgresql.org/pgsql-hackers/2005-10/msg01009.php > is OK to apply? > > regards, tom lane I tried to persuade myself that removing all WaitForSingleObjectEx() is safe ... the thing is we will false alarm EINTR as Magnus said (details to repeat it are list below in case). There are several EINTR in the code, semop() calls, socket() calls, ..., seems they are all ok except pgwin32_backend_usleep() changes a little bit performance: it can't sleep enough because of the false alarm, but it is ok though. Conclusion: Agree to apply. Regards, Qingqing --- Consider a sequence like this: 1. I am killing you signal A: enter_crit; set signal bit; leave_crit; 2. You CHECK_FOR_INTERRUPTS(): enter_crit; sig(A); ResetEvent(); leave_crit; 3. I finish my killing: SetEvent(); Now the event is signaled but the signal is handled already. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PG Killed by OOM Condition
On Mon, Oct 03, 2005 at 23:03:06 +1000,
John Hansen <[EMAIL PROTECTED]> wrote:
> Good people,
>
> Just had a thought!
>
> Might it be worth while protecting the postmaster from an OOM Kill on
> Linux by setting /proc/{pid}/oom_adj to -17 ?
> (Described vaguely in mm/oom_kill.c)
Wouldn't it be better to use sysctl to tell the kernel not to over commit
memory in the first place?
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] [HACKERS] 'a' == 'a '
Bruce Momjian writes: > OK, I understand now. It is tempting to think that the difference > between char() and varchar() is that internally they use a different > collating sequences, but that isn't the case. If it were, space would > be ignored during comparisons any place in the string, when in fact, is > it is only trailing space that char() ignores, e.g.: No, you don't understand. The standard defines PAD SPACE as making *trailing* spaces irrelevant to comparisons, not embedded or leading spaces. And they regard PAD SPACE as an attribute of a collation rather than of the string datatype. This seems pretty wacko to me (in particular it's hard to see how NO PAD is useful with char(N) storage) but that's what they did. > What additional documentation is needed? Some specific discussion of the relationship to the standard would be helpful, perhaps. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL 8.1 Beta 4
Hi Tomas, Have you considered joining the PostgreSQL Build Farm? www.pgbuildfarm.org Chris Tomas wrote: Hello. I've joined this mailing list to report you the success I am having compiling postgresql-8.1 beta4 on DragonFly BSD, which is not supported. Because the distribution is not known to postgresql-8.1 beta4, I had to use ./configure --with-template=freebsd, since DragonFly is (for now) still very similar to FreeBSD (version 4 and 5). After that it could be compiled using gmake and installed according to the instructions in INSTALL file. Result: test=# select version(); version --- PostgreSQL 8.1beta4 on i386-unknown-dragonfly1.3.7, compiled by GCC gcc (GCC) 3.4.5 20050809 (prerelease) [DragonFly] (propolice, visibility) (1 row) test=# It would be very nice if the pgsql-8.1 knows about DragonFly so users of it wouldn't have to go through trouble installing it. Maybe it is still time to add the distribution DragonFly to the supported-platforms list before we get to RC1. If I can help, please, tell me how. Thank you. Regards, Tomas Marc G. Fournier wrote: In order to address some issues found with the Windows port, as well as GCC4, we have released a Beta 4 of the upcoming 8.1 Release. This Beta is meant to be a quick beta, baring any problems, with our first Release Candidate happening late this same week. With this beta, Tom has also put out the call for port reports, to round out our 'Supported Platforms' list: "If you don't see your favorite platform already listed as tested for 8.1 at http://developer.postgresql.org/docs/postgres/supported-platforms.html then please give it a try and send in your results." We heavily encourage any, and all, to test this Beta out, to make sure we have weeded out as many bugs before release as possible ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.1 Beta 4
Hello. I've joined this mailing list to report you the success I am having compiling postgresql-8.1 beta4 on DragonFly BSD, which is not supported. Because the distribution is not known to postgresql-8.1 beta4, I had to use ./configure --with-template=freebsd, since DragonFly is (for now) still very similar to FreeBSD (version 4 and 5). After that it could be compiled using gmake and installed according to the instructions in INSTALL file. Result: test=# select version(); version --- PostgreSQL 8.1beta4 on i386-unknown-dragonfly1.3.7, compiled by GCC gcc (GCC) 3.4.5 20050809 (prerelease) [DragonFly] (propolice, visibility) (1 row) test=# It would be very nice if the pgsql-8.1 knows about DragonFly so users of it wouldn't have to go through trouble installing it. Maybe it is still time to add the distribution DragonFly to the supported-platforms list before we get to RC1. If I can help, please, tell me how. Thank you. Regards, Tomas Marc G. Fournier wrote: In order to address some issues found with the Windows port, as well as GCC4, we have released a Beta 4 of the upcoming 8.1 Release. This Beta is meant to be a quick beta, baring any problems, with our first Release Candidate happening late this same week. With this beta, Tom has also put out the call for port reports, to round out our 'Supported Platforms' list: "If you don't see your favorite platform already listed as tested for 8.1 at http://developer.postgresql.org/docs/postgres/supported-platforms.html then please give it a try and send in your results." We heavily encourage any, and all, to test this Beta out, to make sure we have weeded out as many bugs before release as possible ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > OK, running the latest patch. Observations: > ... > I ran tests for about an hour, randomly killing/canceling backends > without any problems. Are we all comfortable that http://archives.postgresql.org/pgsql-hackers/2005-10/msg01009.php is OK to apply? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] 'a' == 'a '
Dann Corbit wrote: > > But isn't collating sequence related to ordering? How does this > relate > > to padding? > > Right. Collating sequence is how ordering is defined. But when you > compare two character types, they are supposed to pad according to the > collating sequence. So whether you blank fill or pad with some special > character when performing a comparison is defined by the collating > sequence and not by the character type. Since we see (for instance) > that bpchar(n) and varchar(n) pad differently when performing a > comparison, we must assume that they have a different collating > sequence. So the question is "what is it?" > > It is always possible that I have misread the standard. OK, I understand now. It is tempting to think that the difference between char() and varchar() is that internally they use a different collating sequences, but that isn't the case. If it were, space would be ignored during comparisons any place in the string, when in fact, is it is only trailing space that char() ignores, e.g.: test=> SELECT 'a '::CHAR(10) = 'a'::CHAR(10); ?column? -- t (1 row) test=> SELECT 'a '::VARCHAR(10) = 'a'::VARCHAR(10); ?column? -- f (1 row) test=> SELECT 'a'::CHAR(10) = ' a'::CHAR(10); ?column? -- f (1 row) test=> SELECT 'a'::VARCHAR(10) = ' a'::VARCHAR(10); ?column? -- f (1 row) Our docs already have: http://candle.pha.pa.us/main/writings/pgsql/sgml/datatype-character.html Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values. What additional documentation is needed? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
"Simon Riggs" <[EMAIL PROTECTED]> wrote >> >> How do other databases deal with this? I can't imagine we are the only >> ones. Are we doing something different than them? > > I'm not sure the people qualified to answer that are able to do so. What > do other OSS projects do about this is more likely an answerable > question. > I happened to know that a similar problem which is called "convoy phenomenon" is discussed in 1979 (Wow! :-)) But not exactly the same case maybe. I copied the paper and put it here: http://www.cs.toronto.edu/~zhouqq/convoy.pdf Regards, Qingqing ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] 'a' == 'a '
Dann Corbit wrote: > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > Sent: Monday, October 24, 2005 5:57 PM > > To: Dann Corbit > > Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]; > > pgsql-hackers@postgresql.org > > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > > > Dann Corbit wrote: > > > Document the collating sequences used for the character types. > > > > Sorry, I don't understand that. What does it mean? > > According to the standard, comparisons against character types are > supposed to PAD with either or according to the > collating sequence and not according to the data type. > > Since the padding behavior of PosgreSQL comparisons for character types > is different according to the data type and not the collating sequence, > what is needed is simply to define the collating sequence used for > different character types. But isn't collating sequence related to ordering? How does this relate to padding? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: explain analyze is your friend
Re: [HACKERS] New timezone data
Michael Fuhr <[EMAIL PROTECTED]> writes: > * Update timezone data to match latest zic database (see src/timezone/README) > So in case anybody was going to check on that prior to the upcoming > 8.1 release candidate, there is new timezone data available (2005n > vs. the current 2005m) but it appears to have only minor changes > for Kyrgyzstan and Uruguay. If anybody wants, I'll submit a patch. We don't do that via patches ... the procedure is "download tarball, copy into appropriate subdirectory, commit". (At least when the update isn't modifying the PST8PDT rules ;-)) But given that 2005m is only a month or so old, I wasn't planning to bother with another update before release. 2005n will be obsolete too in another month ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] 'a' == 'a '
> -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 5:57 PM > To: Dann Corbit > Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]; > pgsql-hackers@postgresql.org > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > Dann Corbit wrote: > > Document the collating sequences used for the character types. > > Sorry, I don't understand that. What does it mean? According to the standard, comparisons against character types are supposed to PAD with either or according to the collating sequence and not according to the data type. Since the padding behavior of PosgreSQL comparisons for character types is different according to the data type and not the collating sequence, what is needed is simply to define the collating sequence used for different character types. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] sort_mem statistics ...
Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > On Tue, 18 Oct 2005, Tom Lane wrote: > >> Looking at the code, I notice that the messages are all emitted at level > >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much > >> in-your-face if it were on all the time. Does anyone think it'd be a > >> good idea to emit the trace_sort messages at level LOG, instead? > > > If someone sets trace_sort, does it matter what level its emit'd at? > > Well, yeah. It depends whether you are thinking of the trace feature as > being used interactively, or as something turned on to gather data over > time in a production installation. In the second case you'd want the > info to go to the postmaster log, but not want to see it dumped on your > terminal all the time ... I think it should go to the logs, hence LOG. Right now it just scrolls off my screen: test=> select * from pg_class order by relname; NOTICE: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = t NOTICE: performsort starting: CPU 0.00s/0.00u sec elapsed 0.00 sec NOTICE: performsort done: CPU 0.00s/0.00u sec elapsed 0.00 sec NOTICE: sort ended: CPU 0.00s/0.00u sec elapsed 0.00 sec relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltup les | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relf ... -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] 'a' == 'a '
Dann Corbit wrote: > Document the collating sequences used for the character types. Sorry, I don't understand that. What does it mean? --- > > > -Original Message- > > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > > Sent: Monday, October 24, 2005 11:01 AM > > To: Tom Lane > > Cc: Dann Corbit; [EMAIL PROTECTED]; > [EMAIL PROTECTED]; > > pgsql-hackers@postgresql.org > > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > > > > > Is there any TODO here? > > > > > > -- > > - > > > > Tom Lane wrote: > > > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > > > I guess that additional ambiguity arises if you add additional > spaces > > to > > > > the end. Many database systems solve this by trimming the > characters > > > > from the end of the string upon storage and the returned string > will > > not > > > > have any trailing blanks. > > > > > > Can you document that? ISTM that that would effectively make > char(n) > > > and varchar(n) exactly equivalent, which is ... um ... a bit stupid. > > > > > > regards, tom lane > > > > > > ---(end of > broadcast)--- > > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > >choose an index scan if your joining column's datatypes do > not > > >match > > > > > > > -- > > Bruce Momjian| http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup.| Newtown Square, Pennsylvania > > 19073 > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Don't 'kill -9' the postmaster
Re: [HACKERS] New timezone data
On Mon, Oct 24, 2005 at 07:02:07PM -0400, Andrew Dunstan wrote: > There's nothing magical about the files, is there? A user should be able > to plug in a zic-compiled zone file from just about anywhere if they > really need it, without having to update postgres, or I have badly > misunderstood how we handle things. Sure users can do that if they need to; I was just wondering about the policy for keeping the source code current. I do see an item in src/tools/RELEASE_CHANGES suggesting that releases should have the latest timezone data: * Update timezone data to match latest zic database (see src/timezone/README) So in case anybody was going to check on that prior to the upcoming 8.1 release candidate, there is new timezone data available (2005n vs. the current 2005m) but it appears to have only minor changes for Kyrgyzstan and Uruguay. If anybody wants, I'll submit a patch. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New timezone data
Bruce Momjian wrote: Michael Fuhr wrote: I see that new timezone data is available at ftp://elsie.nci.nih.gov/pub/ It looks like the only changes from PostgreSQL's current data involve Kyrgyzstan and Uruguay. What's the policy on keeping the source code up to date? Does the data change too often to bother except just before a release? Yea, probably. There's nothing magical about the files, is there? A user should be able to plug in a zic-compiled zone file from just about anywhere if they really need it, without having to update postgres, or I have badly misunderstood how we handle things. Of course, your OS might not come with such things (e.g. Windows), but it should not be a very big deal to obtain the file. cheers amdrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
On Fri, 2005-10-21 at 17:17 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Certainly there is a lack of ideas as to how to fix it, as you mention > > in (3). This shows to me that the solution lies in one of two areas: a) > > the solution has not yet been considered or b) the solution has already > > been thought of and for whatever reason disregarded. You may be certain > > that the solution lies in a), though I am not. Rejecting ideas quickly > > may simply increase the chances of finding the solution in a b) case. > > However, building a spinlock test harness presupposes that the solution > lies in the spinlock code itself (I thought I had said) that was actually the main test, rather than a presupposition. The solution may lie there or not and when it is built we would be able to determine where it is. If the issue is with the spinlock code itself, it will then help us find the solution. If it is not, then we will know to look elsewhere. It is important that we isolate the problem in such a way that others can help fix it. If we do it only within the database, then the special context will force many to say they cannot help us. > patterns of it. So I'd throw the "rejecting ideas too quickly" > challenge right back at you. My words were inspired by a desire to leave the Doldrums only, not to cause other issues. I apologise for any offence given. > What we need to optimize is the behavior > in the real context of Postgres, not in a test harness. That is the end goal, yes. My sub-goal on that path is problem isolation (only). Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] New timezone data
Michael Fuhr wrote: > I see that new timezone data is available at > > ftp://elsie.nci.nih.gov/pub/ > > It looks like the only changes from PostgreSQL's current data involve > Kyrgyzstan and Uruguay. What's the policy on keeping the source > code up to date? Does the data change too often to bother except > just before a release? Yea, probably. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: 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] sort_mem statistics ...
On Tue, 2005-10-18 at 18:57 -0400, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > On Tue, 18 Oct 2005, Tom Lane wrote: > >> Looking at the code, I notice that the messages are all emitted at level > >> NOTICE. Perhaps that was not such a good idea --- it'd be pretty much > >> in-your-face if it were on all the time. Does anyone think it'd be a > >> good idea to emit the trace_sort messages at level LOG, instead? > > > If someone sets trace_sort, does it matter what level its emit'd at? > > Well, yeah. It depends whether you are thinking of the trace feature as > being used interactively, or as something turned on to gather data over > time in a production installation. In the second case you'd want the > info to go to the postmaster log, but not want to see it dumped on your > terminal all the time ... Yes, please set this at LOG. It will certainly provide many more data points for us to analyse. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] memcpy SEGV on AIX 5.3
On an powerPC AIX 5.3 box, initdb from 8.1beta4 segfaults at
src/backend/utils/hash/dynahash.c:673. No segfaults occur and all 98
regression tests pass if a test is added to see if keycopy is memcpy and
if it is, go through a loop memcpying one byte at a time instead of
memcpying everything at once.
Configuration details:
-bash-2.05b$ gcc -v
Using built-in specs.
Target: powerpc-ibm-aix5.3.0.0
Configured with: /opt/src/gcc4/gcc-4.0.1/configure
--prefix=/opt/dbs/gcc4 --enable-languages=c,c++
Thread model: aix
gcc version 4.0.1
-bash-2.05b$ oslevel -r
5300-03
./configure --prefix=/opt/dbs/pgsql81b4-afilias-AIX53-2005-10-24
--with-includes=/opt/freeware/include --enable-debug
--enable-thread-safety --with-libraries=/opt/freeware/lib --enable-casert
InitPostgres
`-> RelationCacheInitialize
`-> formrdesc
`-> RelationCacheInsert
`-> hash_search
`-> keycopy (keycopy == memcpy)
--- dynahash.c.orig 2005-10-14 22:49:33.0 -0400
+++ dynahash.c 2005-10-24 18:00:51.785106864 -0400
@@ -670,7 +670,14 @@
/* copy key into record */
currBucket->hashvalue = hashvalue;
- hashp->keycopy(ELEMENTKEY(currBucket), keyPtr, keysize);
+ if (hashp->keycopy == memcpy)
+ {
+ int tempfoo;
+ for (tempfoo = 0; tempfoo < keysize; tempfoo++)
+ memcpy(ELEMENTKEY(currBucket)+tempfoo,
keyPtr+tempfoo, 1);
+ }
+ else
+ hashp->keycopy(ELEMENTKEY(currBucket), keyPtr, keysize);
/* caller is expected to fill the data field on return */
--
Seneca Cunningham
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
On Mon, 2005-10-24 at 13:08 -0500, Kevin Grittner wrote: > Try getting lucky in google with "spinlock contention is greater" Kevin's reference is actually to a Sybase manual page; very interesting. This refers to the idea of subdividing the lock into may partitions. That's already been discussed, but that is the next step beyond where we are right now with a single cache but a much improved cache algorithm. That technique is an algorithmic improvement rather than a hardware specific improvment, which is what is required in the case we are dealing with here. Cool reference. The idea of named caches is more familiar to me. Tuning for that is just too hard and massively inflexible, so I don't think its the way anybody really wants to go, but I hold the door open for anybody travelling that way. > >>> Bruce Momjian >>> > > How do other databases deal with this? I can't imagine we are the only > ones. Are we doing something different than them? I'm not sure the people qualified to answer that are able to do so. What do other OSS projects do about this is more likely an answerable question. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Creating table in different database
On Mon, Oct 24, 2005 at 11:32:55PM +0530, Paresh Bafna wrote: > If there are multiple databases, say db1 and db2. > And currently we are in db1, can we create table in db2 (without > switching databases)? > Is there any query to do this? This question seems off-topic for pgsql-hackers. The mailing list's description says: The PostgreSQL developers team lives here. Discussion of current development issues, problems and bugs, and proposed new features. If your question cannot be answered by people in the other lists, and it is likely that only a developer will know the answer, you may re-post your question in this list. You must try elsewhere first! You could use contrib/dblink, but perhaps you should be using schemas instead of separate databases. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
On Mon, 24 Oct 2005, Magnus Hagander wrote: > > Is there any way to test socket? > > Send the backend a signal when it's blocking for socket input (waiting > for a user command in a psql session for example), and see that it's > delivered. Hitting hte postmaster will test the select() path, and > hitting a backend will test the recv() path, IIRC. > Ok. - Test recv() path by runing a psql with "-f time_out.sql" and a batch file executing "pg_ctl kill HUP/USR1" repeatedly. - Test select() path by running two batch files, one is doing "psql -c "set statement_timeout=10" test", the other is doing "pg_ctl kill HUP ". Regards, Qingqing ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [HACKERS] 'a' == 'a '
Document the collating sequences used for the character types. > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED] > Sent: Monday, October 24, 2005 11:01 AM > To: Tom Lane > Cc: Dann Corbit; [EMAIL PROTECTED]; [EMAIL PROTECTED]; > pgsql-hackers@postgresql.org > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > > Is there any TODO here? > > -- > - > > Tom Lane wrote: > > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > > I guess that additional ambiguity arises if you add additional spaces > to > > > the end. Many database systems solve this by trimming the characters > > > from the end of the string upon storage and the returned string will > not > > > have any trailing blanks. > > > > Can you document that? ISTM that that would effectively make char(n) > > and varchar(n) exactly equivalent, which is ... um ... a bit stupid. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 9: In versions below 8.0, the planner will ignore your desire to > >choose an index scan if your joining column's datatypes do not > >match > > > > -- > Bruce Momjian| http://candle.pha.pa.us > pgman@candle.pha.pa.us | (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: explain analyze is your friend
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot
Try getting lucky in google with "spinlock contention is greater" >>> Bruce Momjian >>> How do other databases deal with this? I can't imagine we are the only ones. Are we doing something different than them? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Creating table in different database
If there are multiple databases, say db1 and db2. And currently we are in db1, can we create table in db2 (without switching databases)? Is there any query to do this? - Paresh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] 'a' == 'a '
Is there any TODO here? --- Tom Lane wrote: > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > I guess that additional ambiguity arises if you add additional spaces to > > the end. Many database systems solve this by trimming the characters > > from the end of the string upon storage and the returned string will not > > have any trailing blanks. > > Can you document that? ISTM that that would effectively make char(n) > and varchar(n) exactly equivalent, which is ... um ... a bit stupid. > > regards, tom lane > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Don't 'kill -9' the postmaster
Re: [HACKERS] Seeing context switch storm with 10/13 snapshot of
Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Certainly there is a lack of ideas as to how to fix it, as you mention > > in (3). This shows to me that the solution lies in one of two areas: a) > > the solution has not yet been considered or b) the solution has already > > been thought of and for whatever reason disregarded. You may be certain > > that the solution lies in a), though I am not. Rejecting ideas quickly > > may simply increase the chances of finding the solution in a b) case. > > However, building a spinlock test harness presupposes that the solution > lies in the spinlock code itself, and not in (say) changing our usage > patterns of it. So I'd throw the "rejecting ideas too quickly" > challenge right back at you. What we need to optimize is the behavior > in the real context of Postgres, not in a test harness. How do other databases deal with this? I can't imagine we are the only ones. Are we doing something different than them? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Don't 'kill -9' the postmaster
Re: [HACKERS] [Slony1-general] Slony1_funcs broken with 8.1
[EMAIL PROTECTED] (Andreas Pflug) writes:
> Tom Lane wrote:
>> Andreas Pflug <[EMAIL PROTECTED]> writes:
>>
>>> So postmaster doesn't clean up pg_listener,
>> It never has. If you're complaining about this patch
>> http://archives.postgresql.org/pgsql-committers/2005-10/msg00073.php
>> you ought to say so, rather than expecting us to guess it from an
>> out-of-context quote from another mailing list.
>
> Not complaining, just RFC.
It looks as though we can accomplish an equivalent pretty readily.
create table sl_nodelock (
nl_node integer not null unique;
nl_pid integer not null;
);
The "interlock" could be accomplished via the following bit of sorta
pseudocode...
try {
execute "insert into sl_nodelock (nl_node, nl_pid) values (getlocalnodeid,
pg_backend_pid());";
} on error {
execute "SELECT pg_stat_get_backend_pid(s.backendid) AS procpid FROM (SELECT
pg_stat_get_backend_idset() AS backendid) AS s where procpid = $OTHERPID;";
if (not_found) {
printf ("Found dead slon connection info - cleaning it out!\n");
execute "delete from sl_nodelock;";
} else {
printf ("Could not start node N - other slon still running!");
}
exit -1;
}
> But I wonder why postmaster doesn't truncate pg_listener at restart,
> since PIDs can't be valid any more (truncating would reduce bloating
> too). A redesign based on shmem or so wouldn't keep the data either.
Truncating things at restart just feels scary, even though it is, in
this case, pretty appropriate. I'd rather see the shmem redisgn...
>> As near as I can tell, the technique Jan describes is an abuse of
>> pg_listener, and I won't feel any great sympathy when it does break
>> completely, which it will do before long when pg_listener goes away
>> in the planned rewrite of LISTEN/NOTIFY.
>
> Well slony uses LISTEN for its main purpose too. I'd guess there's
> always a demand to find out which backend is listening, so a
> pg_listener (probably a view wrapping a function) will be necessary.
There are two usages of LISTEN in Slony-I; the other one needs to get
improved, as well, as the event propagation system generates way too
many dead tuple entries at present. I have a patch that easily cuts
it by about half; the other half seems pretty doable too...
> AFAICS a backend that notices loss of client connection will usually
> clean up its listener entries, so apparently slony doesn't need to
> take care of this, at least for 8.1 (with the postmaster crash
> exception).
Interesting.
--
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/rdbms.html
Rules of the Evil Overlord #53. "If the beautiful princess that I
capture says "I'll never marry you! Never, do you hear me, NEVER!!!",
I will say "Oh well" and kill her."
Re: [HACKERS] dynamic loading of .so (originally from
Bruce, There are two problems, though maybe I came to the wrong solution. I'm not averse to changing it. 1) Veil starts from a user process and not from the postmaster. This means that any shared memory segments created can not necessarily be mapped to the same address space in each process, which makes using such shared memory a little more painful than just referencing pointers. 2) There is no simple way to close the shared memory segement when postgres shuts down. In an earlier version of Veil I did allocate my own shared memory and could revive the code if we can overcome problem number 2. I'd like to also overcome problem 1 as it makes the code extremely ugly but it's no show stopper. Any thoughts? __ Marc On Mon, 2005-10-24 at 09:24 -0400, Bruce Momjian wrote: > Uh, why does Veil have to allocate space from the backend shared memory > pool rather than allocating its own shared memory segment? > > --- > > Marc Munro wrote: > -- Start of PGP signed section. > > Tom, > > My project, Veil, steals some of this shared memory for itself. I wan't > > aware of the "slop factor" and was pleased that it just worked. I guess > > I should have been asking questions of this group. Sorry. > > > > I would like Veil to be a good citizen and place a legitimate request > > for its shared memory (probably about 16K normally). > > > > Veil is loaded as a shared library, which I would assume means that it > > is not present during database startup, making contributing to the > > sizing calculation and racing the lockmgr a little tricky. > > > > I see a number of possibilities: > > > > - Have a GUC to allocate shmem space for add-ons > > - Have add-ons register themselves and provide hooks for sizing and > > initial space allocation > > - Let add-ons race with the lockmgr for the slop (ie leave as it is) > > > > Thoughts? > > > > I would be happy to work on this and provide whatever patches are > > necessary. > > > > Thanks. > > __ > > Marc Munro > > > > On Mon, 2005-10-17 at 10:34 -0300, [EMAIL PROTECTED] > > wrote: > > > Date: Mon, 17 Oct 2005 00:42:17 -0400 > > > From: Tom Lane <[EMAIL PROTECTED]> > > > To: Douglas McNaught <[EMAIL PROTECTED]> > > > Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], > > > "pgsql-general@postgresql.org" > > > Subject: Re: dynamic loading of .so > > > Message-ID: <[EMAIL PROTECTED]> > > > > > > Douglas McNaught <[EMAIL PROTECTED]> writes: > > > > <[EMAIL PROTECTED]> writes: > > > >> are there any way to make them global for all the instances? > > > > > > > Put them in shared memory. This probably isn't trival, as all the > > > > shared memory is allocated up front and used for existing purposes > > > (at > > > > least, as I understand it). > > > > > > There's a "slop factor" of 100KB or so in the shared memory size > > > calculation, which means that an add-on library that requests space > > > soon > > > enough could probably get away with allocating up to a few KB without > > > causing any problems. (The slop is not totally useless, since for > > > instance the lock manager might eat it up if more locks get requested > > > than expected.) > > > > > > In the long run it might be interesting to add hooks that allow > > > preloaded libraries to contribute to the shmem sizing calculation and > > > then to snarf up the space they've requested before it can get eaten > > > by > > > the lockmgr. > > > > > > regards, tom lane > > > > -- End of PGP section, PGP failed! > signature.asc Description: This is a digitally signed message part
Re: [HACKERS] LISTEN/NOTIFY enhancement: Portable signal handling?
In case anyone's wondering why I'm replying to an article that's nearly a year old, it came up in a search while I was looking for the more recent LISTEN/NOTIFY thread... In article <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: >Sean Chittenden <[EMAIL PROTECTED]> writes: >> The async interface is nice, but not really useful to me as it requires >> polling, instead of unblocking when an event comes through, which would >> create a vastly more real time interface that should be easier on the >> database. > >BTW, this is nonsense; the backend sends a message exactly when the >NOTIFY occurs. It may well be that libpq needs some work to make it >easier to wait for a NOTIFY without polling, but again there isn't a >reason to clutter the server-side code with it. One thing that persistently annoys me about using LISTEN in clients is that much of the time I'm not using libpq directly, but some layer above libpq that implements a generic SQL interface. These interfaces generally assume that SQL calls are synchronous and blocking unless you use some kind of escape to the lower-level driver, and in a few environments there is no such escape at all. I'd really like to have something that looks as much like an SQL statement as possible, which blocks until a NOTIFY event or a timeout occurs. If not a stand-alone SQL command then at least a function. In one case where I really needed this, I implemented a really *ugly* PL/PerlU function that did the following: open a new client connection (with caching) to the server from the server's own backend set up appropriate LISTEN commands, go to sleep on a poll() from the file descriptor, then return immediately after poll() does. Thankfully this particular application was not required to scale beyond a half-dozen clients! -- Zygo Blaxell (Laptop) <[EMAIL PROTECTED]> GPG = B203 9402 B0E7 0F20 3B74 B13C DFBC C916 4395 FD03 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
Andrew Dunstan <[EMAIL PROTECTED]> writes: > We might be able to solve that for plpgsql, but in general we can't, > ISTM. What if I write a plperl function that loops forever? We have no > chance there to call CHECK_FOR_INTERRUPTS. Yeah, I was thinking about that too. Still, we can/should/already did fix it in plpgsql. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq's pollution of application namespace
This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---
Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> On Sun, Oct 16, 2005 at 06:21:37PM -0400, Tom Lane wrote:
> > I find that libpq.so exports the following symbols that have neither
> > PQ, pq, pg, nor lo_ as a prefix:
>
>
>
> > It'd be nicer if we could filter out all exported symbols that don't
> > appear in exports.txt, but I don't know any portable way to do that.
>
> With GNU LD it is trivial, using the --version-script command. If you
> use AWK to create the script from exports.txt like so:
>
> awk 'BEGIN { print "{ global: " } { if( $1 != "#" ) {print $1,";"} } END {
> print "local: *; };" }' exports.version
>
> And then add "-Wl,--version-script,exports.version" to the link
> command, viola, stray symbols removed. Given we already have a
> configure test for GNU ld, it wouldn't be too hard to make it work for
> them. For windows it already uses exports.txt. What other linkers do we
> need to support?
>
> Another possibility would be to use strip like so:
>
> strip -w -K PQ* -K pq* -K pg* -K lo_* -K *PQ* -o output.so
>
> But then, that may be a GNU strip extention... And it doesn't follow
> the exports file then.
>
> Recent gcc versions support visibility directives in the source code but
> that's a lot more work (although doing it in the code would produce a
> more efficient library). And not portable to other compilers either...
>
> Hope this helps,
> --
> Martijn van Oosterhout http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (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: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Tom wrote: >> Hmm, that suggests we need another CHECK_FOR_INTERRUPTS somewhere in >> plpgsql. Please show the exact test case you were using. > create function test_func() returns int4 as $$ BEGIN LOOP END LOOP; > select 0; END; $$ language plpgsql; Thanks, fixed. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
Tom Lane wrote: "Merlin Moncure" <[EMAIL PROTECTED]> writes: 3. A pl/pgsql function stuck in a empty loop is unkillable except by killing the process on the server, which cycles the entire server. This was the behavior before the patch, btw. Hmm, that suggests we need another CHECK_FOR_INTERRUPTS somewhere in plpgsql. Please show the exact test case you were using. We might be able to solve that for plpgsql, but in general we can't, ISTM. What if I write a plperl function that loops forever? We have no chance there to call CHECK_FOR_INTERRUPTS. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
Tom wrote: > "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > 3. A pl/pgsql function stuck in a empty loop is unkillable except by > > killing the process on the server, which cycles the entire server. This > > was the behavior before the patch, btw. > > Hmm, that suggests we need another CHECK_FOR_INTERRUPTS somewhere in > plpgsql. Please show the exact test case you were using. create function test_func() returns int4 as $$ BEGIN LOOP END LOOP; select 0; END; $$ language plpgsql; select test_func(); // :-). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > 3. A pl/pgsql function stuck in a empty loop is unkillable except by > killing the process on the server, which cycles the entire server. This > was the behavior before the patch, btw. Hmm, that suggests we need another CHECK_FOR_INTERRUPTS somewhere in plpgsql. Please show the exact test case you were using. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question about Ctrl-C and less
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Martijn van Oosterhout wrote: >> On Mon, Oct 24, 2005 at 08:20:07AM -0400, Bruce Momjian wrote: >>> This has been saved for the 8.2 release: >> Is this official "blessing" for the idea > AFAIK it's Bruce's way of not losing track of the patch, Exactly. "Held for future review" would be a more accurate reading. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
OK, running the latest patch. Observations: 1. Confirmed that time for count(*) on narrow sets is greatly improved: A real easy way to show this off is: select count(*) from generate_series(1,(10^6)::integer); with about a 60% drop in time on my XP box. 2. Did ISAM style record iteration over relatively large (50k records) bill of materials. This was previous test where I reported no performance gain...for a single user (n=1). For n=6, I am seeing about a 20% reduction in the overall running time of the test, and a greatly improved overall responsiveness of the server while running the test. I tested this by doing select * from medium_sized_table while 6 backends were busy feeding records to the ISAM app. While pg under 0 load might serve the table in one second, with the n=6 test running the time might (on win32) take 5 minutes. On linux, the worst case time was around 1/(n*2). Qingqing's patch brings win32 much closer to linux! 3. A pl/pgsql function stuck in a empty loop is unkillable except by killing the process on the server, which cycles the entire server. This was the behavior before the patch, btw. I ran tests for about an hour, randomly killing/canceling backends without any problems. Merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Question about Ctrl-C and less
Martijn van Oosterhout wrote: On Mon, Oct 24, 2005 at 08:20:07AM -0400, Bruce Momjian wrote: This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold Is this official "blessing" for the idea that psql should ignore SIGINT while the pager is running? Or does this mean the idea will be revisited again later? AFAIK it's Bruce's way of not losing track of the patch, so more a case of "revisit later" than "officially blessed". By all means submit a revised patch if you improve on it in the meantime. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] dynamic loading of .so (originally from pgsql-general)
Uh, why does Veil have to allocate space from the backend shared memory pool rather than allocating its own shared memory segment? --- Marc Munro wrote: -- Start of PGP signed section. > Tom, > My project, Veil, steals some of this shared memory for itself. I wan't > aware of the "slop factor" and was pleased that it just worked. I guess > I should have been asking questions of this group. Sorry. > > I would like Veil to be a good citizen and place a legitimate request > for its shared memory (probably about 16K normally). > > Veil is loaded as a shared library, which I would assume means that it > is not present during database startup, making contributing to the > sizing calculation and racing the lockmgr a little tricky. > > I see a number of possibilities: > > - Have a GUC to allocate shmem space for add-ons > - Have add-ons register themselves and provide hooks for sizing and > initial space allocation > - Let add-ons race with the lockmgr for the slop (ie leave as it is) > > Thoughts? > > I would be happy to work on this and provide whatever patches are > necessary. > > Thanks. > __ > Marc Munro > > On Mon, 2005-10-17 at 10:34 -0300, [EMAIL PROTECTED] > wrote: > > Date: Mon, 17 Oct 2005 00:42:17 -0400 > > From: Tom Lane <[EMAIL PROTECTED]> > > To: Douglas McNaught <[EMAIL PROTECTED]> > > Cc: [EMAIL PROTECTED], [EMAIL PROTECTED], > > "pgsql-general@postgresql.org" > > Subject: Re: dynamic loading of .so > > Message-ID: <[EMAIL PROTECTED]> > > > > Douglas McNaught <[EMAIL PROTECTED]> writes: > > > <[EMAIL PROTECTED]> writes: > > >> are there any way to make them global for all the instances? > > > > > Put them in shared memory. This probably isn't trival, as all the > > > shared memory is allocated up front and used for existing purposes > > (at > > > least, as I understand it). > > > > There's a "slop factor" of 100KB or so in the shared memory size > > calculation, which means that an add-on library that requests space > > soon > > enough could probably get away with allocating up to a few KB without > > causing any problems. (The slop is not totally useless, since for > > instance the lock manager might eat it up if more locks get requested > > than expected.) > > > > In the long run it might be interesting to add hooks that allow > > preloaded libraries to contribute to the shmem sizing calculation and > > then to snarf up the space they've requested before it can get eaten > > by > > the lockmgr. > > > > regards, tom lane > > -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: explain analyze is your friend
Re: [HACKERS] Question about Ctrl-C and less
On Mon, Oct 24, 2005 at 08:20:07AM -0400, Bruce Momjian wrote: > > This has been saved for the 8.2 release: > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold Is this official "blessing" for the idea that psql should ignore SIGINT while the pager is running? Or does this mean the idea will be revisited again later? If the former, I'll rework it into my psql fixing patch on -patches, since this one as posted is not 100% correct (as someone pointed out) although the chance that someone will notice is about nil. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpyhBOAofWiB.pgp Description: PGP signature
Re: [HACKERS] Question about Ctrl-C and less
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Sun, Oct 16, 2005 at 04:06:04PM -0400, Andrew Dunstan wrote: > > Martin, > > > > Let's see the patch. I assume it should be fairly small. If we could get > > it in early in the 8.2 cycle we would have plenty of time to bang on it. > > In principle this sounds reasonable to me, but psql can be broken quite > > easily - I know as I've done it a couple of times ;-) > > Very well, patch attached. It's quite simple actually. However, there > seems to be some push back from people suggesting that jumping back to > the main loop before the pager has quit is not buggy behaviour. > Assuming that a ^C will kill the pager is just folly. > > Tom asked if we should be blocking SIGQUIT and SIGHUP too. Standard > procedure for spawning external interactive processes includes blocking > SIGQUIT too (see system() manpage). Logically speaking, when the user > sends an interrupt from the keyboard they expect to interrupt the > currently active *interaxtive* process. Hence, once psql has spawned > the pager, it should ignore such interrupts until control is returned > (after pclose). So yes, I would suggest blocking SIGQUIT also, if only > to prevent terminal corruption problems. Interactive programs like less > trap SIGQUIT to restore the terminal settings on exit, but the exit > anyway. > > SIGHUP is different. It is sent by the system, not the user, indicating > the terminal has disconnected. psql is not a daemon expecting to > survive that and hence should quit as normal, along with all other > processes on that terminal. > > If this isn't going to make 8.1 anyway I'd probably go for a patch that > got rid of the longjmp altogether (if people will accept that), fixing > the memory leaks at the same time. At the moment I'm just looking for a > concensus that it's a problem to be solved. > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: Don't 'kill -9' the postmaster
Re: [HACKERS] Semantic optimization is posible?
On Mon, Oct 24, 2005 at 12:11:55PM +0200, Sebastián Ávila wrote: > Hello people, > > I'm thinking to add a stage before the optimizer and after the parser > with a basic rewriter in order to flatten all nested queries using a > pattern and table statistics, here I also can use assetions the > database have. Another and complementary possibility is that I can > make annotations on the query's data structure so that a later stage > can use the semantic info to either prune the search space or to pick > a specific query execution strategy. > Summarizing, I want to add semantic optimization into the query > processing execution. > > I need to hear your opinion and your ideas about this, because I have > to choose between this project about semantic optimization and other > about software analysis. My problem is that I don't know how much > dificult can be to do semantic optimization and to do it well. What is > your opinion about? I have to implement the project in 5 months. Why does it need to be a seperate stage? It is "optimising" right, so why wouldn't it be part of the "optimizer"? Currently things like pulling up subqueries, expanding immutable functions, flattening and reordering joins are all done in the optimiser. Looks like your stuff should be too... AIUI, mosts parts of the optimiser deal with questions like: give me the best plan to evaluate . So if you're dealingwith say an EXCEPT node, you can add your special checks to see if it can work a better way. If it can, evaluate the cost of the plan and compare it with other possibilites. Eventually you return the best plans. Note, generally you return sets of plans because which plan is best may depend on what uses it. A "LIMIT 1" plan may require a completely different strategy to a full scan plan, but you don't generally know that down at the lower levels. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpWgiB5TcLn4.pgp Description: PGP signature
[HACKERS] Semantic optimization is posible?
Hello people, I'm thinking to add a stage before the optimizer and after the parser with a basic rewriter in order to flatten all nested queries using a pattern and table statistics, here I also can use assetions the database have. Another and complementary possibility is that I can make annotations on the query's data structure so that a later stage can use the semantic info to either prune the search space or to pick a specific query execution strategy. Summarizing, I want to add semantic optimization into the query processing execution. I need to hear your opinion and your ideas about this, because I have to choose between this project about semantic optimization and other about software analysis. My problem is that I don't know how much dificult can be to do semantic optimization and to do it well. What is your opinion about? I have to implement the project in 5 months. Thank you to all!! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Query Progress Estimator
On Sun, 2005-10-23 at 02:04 +0530, Anuj Tripathi wrote: > Hi > I am trying to implement Query Progress estimator in postgres for > queries with long run time.I am looking for info regarding the number of > tuples already processsed by a running query . > I would be very thankful if someone can suggest where can i find it or > the data structure that holds the value. >Right now I am exploring postgres using ddd ( debugger ).Can > anyone suggest a better utility /editor/tool for exploring the same in a > better way ? You should read up on how the Executor works before using ddd. You need to see the bigger picture first. In some plans, nodes are completely executed before the next higher node begins the main phase of its execution - in other cases all nodes are active simultaneously. While it is executing, an executor node is the only place that knows how far through its own task it is. That in itself is not much guide to how the whole query is doing since you need to accumulate these individual viewpoints. In my understanding there isn't a supervisor, each node recursively calls the nodes that need to supply it with data. You'd need some kind of concept such as that to allow feedback from individual nodes to be reconciled into a total picture for the query. Incidentally, this is related to the question: "How much memory is my query currently using?" (as opposed to "How much memory is currently allocated?", which is of course externally measurable). Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] On externals sorts and other IO bottlenecks in
On Sun, 2005-10-23 at 03:11 -0400, Gregory Maxwell wrote: > I don't recall this being mentioned in the prior threads: > http://www.cs.duke.edu/TPIE/ > > GPLed, but perhaps it has some good ideas. Thanks, references are always useful. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Win32 CHECK_FOR_INTERRUPTS() performance
> > This patch passes regression and demonstrates the expected > speedup on > > my machine. > > > > Looks good from here: > - several rounds of regression test > - psql -f set_time_out.sql > - pg_ctl signal sending test > - psql deadlock test > > Is there any way to test socket? Send the backend a signal when it's blocking for socket input (waiting for a user command in a psql session for example), and see that it's delivered. Hitting hte postmaster will test the select() path, and hitting a backend will test the recv() path, IIRC. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
