Re: [HACKERS] Differences in UTF8 between 8.0 and 8.1
Andrej Ricnik-Bay wrote: > How about an ugly kludge ... > > split -a 3 -d -b 1048576 ../path/to/dumpfile dumpfile > for i in `ls -1 dumpfile*`; do iconv -c -f UTF8 -t UTF8 $i;done > cat dumpfile* > new_dump Not with UTF-8... You might break in the middle of a multibyte character. Jeroen ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Having trouble startin off with the code..
Hi all, I am new user of postgres.. I am currently working on a project for my advisor and the project is to implement an algorithm for materialiazed view design as explained in this paper [http://www.vldb.org/conf/1997/P136.PDF - MVPP Generation part and sec 4.1 for selecting views as such.. ] This project is a part of the bigger project and hence i want to implement the algorithm rather than using already existing functionality in postgres.. I have read various documents about postgres but have not looked into the source code.. hence currently, i am [only] familiar with higher level details and not the data structures.. As i understand from the paper, the only reason i am using postgres is to be able to get ALL POSSIBLE JOIN PLANS from optimizer.. other than this my algorithm is self sufficient.. Hence i would like to know what is the best way to go about implementing the algorithm.. Here are my thougths.. 1. I can implement it as a "command" in postgres... I donno the exact steps for this, but understand that it requires modifying a LOT of files .. 2. I can write my own standalone code, if i could invoke postgres with my query from the code and be able to access optimizer to get list of all possible join plans ? is it possible to do this? How do i invoke postgres from C code? 3. Is there any other way of doing this? I would also like to know your rough guess on the time i would take to do the interfacing with postgres? [ i am above average C programmer.. pretty good in pointers.. but this is my first exposure to open source database..] I REALLY need help on this and would appreciate if you could help me figure this out.. Thanks in advance, Gayathri TK ---(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] Call for port reports
Josh Berkus wrote: > Tom, > > FreeBSD 5.4 Opteron 64 SMP pass. BSD/OS 4.3.1 i386 SMP passes. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] Differences in UTF8 between 8.0 and 8.1
However I'm running into another problem now. The command: iconv -c -f UTF8 -t UTF8 does strip out the invalid characters. However, iconv reads the entire file into memory before it writes out any data. This is not so good for multi-gigabyte dump files and doesn't allow for it to be used in a pipe between pg_dump and psql. Anyone have any other recommendations? GNU recode might do it, but I'm a bit stymied by the syntax. A quick perl script using Text::Iconv didn't work either. I'm off to look at some other perl modules and will try to create a script so I can strip out the invalid characters. recode UTF-8..UTF-8 < dump_in.sql > dump_out.sql Chris ---(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] Differences in UTF8 between 8.0 and 8.1
> does strip out the invalid characters. However, iconv reads the > entire file into memory before it writes out any data. This is not so > good for multi-gigabyte dump files and doesn't allow for it to be used > in a pipe between pg_dump and psql. > > Anyone have any other recommendations? GNU recode might do it, but > I'm a bit stymied by the syntax. A quick perl script using > Text::Iconv didn't work either. I'm off to look at some other perl > modules and will try to create a script so I can strip out the invalid > characters. How about an ugly kludge ... split -a 3 -d -b 1048576 ../path/to/dumpfile dumpfile for i in `ls -1 dumpfile*`; do iconv -c -f UTF8 -t UTF8 $i;done cat dumpfile* > new_dump Cheers, Andrej ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Is there anything in particular you'd like to see from the index file? I > made a copy of it before reindexing... Could you send me the whole file (off-list)? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Differences in UTF8 between 8.0 and 8.1
On Mon, Oct 24, 2005 at 05:07:40AM -, Andrew - Supernews wrote: > > I'm inclined to suspect that the whole sequence c1 f9 d4 c2 d0 c7 d2 b9 > was never actually a valid utf-8 string, and that the d2 b9 is only valid > by coincidence (it's a Cyrillic letter from Azerbaijani). I know the 8.0 > utf-8 check was broken, but I didn't realize it was quite so bad. Looking at the data it appears that it is a sequence of latin1 characters. They all have the eighth bit set and all seem to pass the check. In a million rows I found 2 examples of this. However I'm running into another problem now. The command: iconv -c -f UTF8 -t UTF8 does strip out the invalid characters. However, iconv reads the entire file into memory before it writes out any data. This is not so good for multi-gigabyte dump files and doesn't allow for it to be used in a pipe between pg_dump and psql. Anyone have any other recommendations? GNU recode might do it, but I'm a bit stymied by the syntax. A quick perl script using Text::Iconv didn't work either. I'm off to look at some other perl modules and will try to create a script so I can strip out the invalid characters. -- Paul Lindner| | | | | | | | | | [EMAIL PROTECTED] pgpGD3OGsKlR8.pgp Description: PGP signature
Re: [HACKERS] Call for port reports
Tom, FreeBSD 5.4 Opteron 64 SMP pass. --Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: >> Is there any way to verify what limits are in place for a running >> backend? Damifino. > Also, is an assert guaranteed to dump core? Yup ... at least, it will call abort(). 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] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 06:06:19PM -0500, Jim C. Nasby wrote: > On Wed, Oct 26, 2005 at 06:38:45PM -0400, Tom Lane wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > > Reproduced the crash, but still no core file... where exactly should it > > > have been put? Would in be outside of $PGDATA? > > > > In 8.0 I'd expect to find it in $PGDATA/base/DBOID/core (or possibly > > core.). There are some platforms like Darwin that tend to put > > core files in a fixed directory such as /cores, though. > > Grr... /etc/profile had > ulimit -S -c 0 > /dev/null 2>&1 > > Is there any way to verify what limits are in place for a running > backend? Also, is an assert guaranteed to dump core? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 06:38:45PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > Reproduced the crash, but still no core file... where exactly should it > > have been put? Would in be outside of $PGDATA? > > In 8.0 I'd expect to find it in $PGDATA/base/DBOID/core (or possibly > core.). There are some platforms like Darwin that tend to put > core files in a fixed directory such as /cores, though. Grr... /etc/profile had ulimit -S -c 0 > /dev/null 2>&1 Is there any way to verify what limits are in place for a running backend? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Reproduced the crash, but still no core file... where exactly should it > have been put? Would in be outside of $PGDATA? In 8.0 I'd expect to find it in $PGDATA/base/DBOID/core (or possibly core.). There are some platforms like Darwin that tend to put core files in a fixed directory such as /cores, though. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 04:48:22PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c",
> > Line: 89)
>
> > Looking through the code I see that's something to do with indexes, but
> > I'm not sure what. Is this likely a corrupted index?
>
> Sounds that way.
>
> > If so, is there some way I could identify which index?
>
> gdb the core file and do "p rel->rd_rel->relname" in the _bt_search
> stack frame (which is not going to be the top of stack but should be
> close to the top).
Reproduced the crash, but still no core file... where exactly should it
have been put? Would in be outside of $PGDATA?
Is there any way to verify the ulimit setings that the backend is
running under?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] sort_mem statistics ...
On Wed, Oct 26, 2005 at 06:50:49PM -0300, Marc G. Fournier wrote: > On Wed, 26 Oct 2005, Jim C. Nasby wrote: > > >On Tue, Oct 18, 2005 at 06:15:02PM -0400, Tom Lane wrote: > >>"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >>>do we maintain anything anywhere for this? mainly, some way of > >>>determining # of 'sorts to disk' vs 'sort in memory', to determine > >>>whether > >>>or not sort_mem is set to a good value? > >> > >>As of 8.1 you could turn on trace_sort to collect some data about this. > > > >While trace_sort is good, it doesn't really help for monitoring. What I > >would find useful would be statistics along the lines of: > > > >How many sorts have occured? > >How many spilled to disk? > >What's the largest amount of memory used by an in-memory sort? > >What's the largest amount of memory used by an on-disk sort? > > Actually, I'd like to see largest/smallest and average in this ... but if > all is being logged to syslog, I can easily determine those #s with a perl > script .. True, but like I said that doesn't help much for monitoring. I'm generally concerned with finding out when stuff starts spilling to disk. Is there a way to log only queries that spill to disk? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 05:47:15PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Wed, Oct 26, 2005 at 11:14:17PM +0200, Martijn van Oosterhout wrote: > >> Well, you know the query that generates it? > > > Well, since query logging only logs when a query finishes... > > Don't forget to look at debug_query_string when you get the core file. Didn't know about that, it will certainly help. There's nothing in the FAQ's about what to look for in a coredump is there? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] determining random_page_cost value
On Tue, Oct 25, 2005 at 04:37:34PM -0400, Yohanes Santoso wrote: > > All of this goes to uphold Tom's general assertion that the default of 4 is > > more or less correct > > Doesn't this show that 4:1 is a pretty optimistic value considering > that no long-running db files are fragmentation-free? > > >but the calculation in which we're using that number is > > not. > > The calculation inside the planner, IOW, how the planner uses the RPC > value? The problem with RPC is that the estimator functions are sub-optimal in many cases and tend to favor seqscan when they shouldn't. This is why many people run with RPC set unrealistically low, such as 2. IMHO until the estimator algorithms improve worrying about RPC is pointless. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] sort_mem statistics ...
On Wed, 26 Oct 2005, Jim C. Nasby wrote: On Tue, Oct 18, 2005 at 06:15:02PM -0400, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: do we maintain anything anywhere for this? mainly, some way of determining # of 'sorts to disk' vs 'sort in memory', to determine whether or not sort_mem is set to a good value? As of 8.1 you could turn on trace_sort to collect some data about this. While trace_sort is good, it doesn't really help for monitoring. What I would find useful would be statistics along the lines of: How many sorts have occured? How many spilled to disk? What's the largest amount of memory used by an in-memory sort? What's the largest amount of memory used by an on-disk sort? Actually, I'd like to see largest/smallest and average in this ... but if all is being logged to syslog, I can easily determine those #s with a perl script .. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Wed, Oct 26, 2005 at 11:14:17PM +0200, Martijn van Oosterhout wrote: >> Well, you know the query that generates it? > Well, since query logging only logs when a query finishes... Don't forget to look at debug_query_string when you get the core file. 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
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 11:14:17PM +0200, Martijn van Oosterhout wrote: > On Wed, Oct 26, 2005 at 04:03:41PM -0500, Jim C. Nasby wrote: > > Is this something that should be logged better than it is? Is there any > > value in trying to save the index/table that's busted? > > Unfortuatly, it's one of those Assert(expr) lines, they don't generally > give the opportunity for extra debug data. They're usually not even > compiled in... > > > Yeah, looks like no core files yet (they would be in $PGDATA somewhere, > > correct?), so I had them hack their startup script to include ulimit -c > > unlimited. Luckily this is pretty reproducable, so hopefully we'll have > > a core soon. > > Well, you know the query that generates it? EXPLAIN should tell you the > indexes it's using, so you could just REINDEX those... Well, since query logging only logs when a query finishes... > Or REINDEX them all... :) 242G database. That would take quite a while... :) -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] sort_mem statistics ...
On Tue, Oct 18, 2005 at 06:15:02PM -0400, Tom Lane wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > do we maintain anything anywhere for this? mainly, some way of > > determining # of 'sorts to disk' vs 'sort in memory', to determine whether > > or not sort_mem is set to a good value? > > As of 8.1 you could turn on trace_sort to collect some data about this. While trace_sort is good, it doesn't really help for monitoring. What I would find useful would be statistics along the lines of: How many sorts have occured? How many spilled to disk? What's the largest amount of memory used by an in-memory sort? What's the largest amount of memory used by an on-disk sort? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 04:03:41PM -0500, Jim C. Nasby wrote: > Is this something that should be logged better than it is? Is there any > value in trying to save the index/table that's busted? Unfortuatly, it's one of those Assert(expr) lines, they don't generally give the opportunity for extra debug data. They're usually not even compiled in... > Yeah, looks like no core files yet (they would be in $PGDATA somewhere, > correct?), so I had them hack their startup script to include ulimit -c > unlimited. Luckily this is pretty reproducable, so hopefully we'll have > a core soon. Well, you know the query that generates it? EXPLAIN should tell you the indexes it's using, so you could just REINDEX those... Or REINDEX them all... :) 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. pgp6nbAUg9bBv.pgp Description: PGP signature
Re: [HACKERS] slower merge join on sorted data chosen over
On Mon, Oct 17, 2005 at 09:30:24PM +0100, Simon Riggs wrote: > On Mon, 2005-10-17 at 14:55 -0500, Jim C. Nasby wrote: > > On Tue, Oct 11, 2005 at 10:58:58AM +0100, Simon Riggs wrote: > > > On Mon, 2005-10-10 at 15:14 -0500, Kevin Grittner wrote: > > > > We are looking at doing much more with PostgreSQL over the > > > > next two years, and it seems likely that this issue will come up > > > > again where it is more of a problem. It sounded like there was > > > > some agreement on HOW this was to be fixed, yet I don't see > > > > any mention of doing it in the TODO list. > > > > > > > Is there any sort of > > > > estimate for how much programming work would be involved? > > > > > > The main work here is actually performance testing, not programming. The > > > cost model is built around an understanding of the timings and costs > > > involved in the execution. > > > > > > Once we have timings to cover a sufficiently large range of cases, we > > > can derive the cost model. Once derived, we can program it. Discussing > > > improvements to the cost model without test results is never likely to > > > convince people. Everybody knows the cost models can be improved, the > > > only question is in what cases? and in what ways? > > > > > > So deriving the cost model needs lots of trustworthy test results that > > > can be assessed and discussed, so we know how to improve things. [...and > > > I don't mean 5 minutes with pg_bench...] > > ... > > > DBT seems to be a reasonable test database > > I was discussing finding the cost equations to use within the optimizer > based upon a series of exploratory tests using varying data. That is > different to using the same database with varying parameters. Both sound > interesting, but it is the former that, IMHO, would be the more > important. True, although that doesn't necessarily mean you can't use the same data generation. For the testing I was doing before I was just varying correlation using cluster (or selecting from different fields with different correlations). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Wed, Oct 26, 2005 at 04:48:22PM -0400, Tom Lane wrote: >> Sounds that way. > Is this something that should be logged better than it is? We don't even know what it is yet, so that question seems a bit premature. > Is there any value in trying to save the index/table that's busted? It'd probably be worth making a copy for forensic purposes before you REINDEX it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
On Wed, Oct 26, 2005 at 04:48:22PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c",
> > Line: 89)
>
> > Looking through the code I see that's something to do with indexes, but
> > I'm not sure what. Is this likely a corrupted index?
>
> Sounds that way.
Is this something that should be logged better than it is? Is there any
value in trying to save the index/table that's busted?
This is 8.0.3, btw.
> > If so, is there some way I could identify which index?
>
> gdb the core file and do "p rel->rd_rel->relname" in the _bt_search
> stack frame (which is not going to be the top of stack but should be
> close to the top).
Yeah, looks like no core files yet (they would be in $PGDATA somewhere,
correct?), so I had them hack their startup script to include ulimit -c
unlimited. Luckily this is pretty reproducable, so hopefully we'll have
a core soon.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c",
> Line: 89)
> Looking through the code I see that's something to do with indexes, but
> I'm not sure what. Is this likely a corrupted index?
Sounds that way.
> If so, is there some way I could identify which index?
gdb the core file and do "p rel->rd_rel->relname" in the _bt_search
stack frame (which is not going to be the top of stack but should be
close to the top).
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c", Line: 89)
I've got a customer who was having a problem with a backend running away
with memory. It would hit 46G before finally being running the box
completely out of memory. It didn't appear to be related to hashjoin/agg
or pending triggers, so I had them recompile with debug and assert
turned on. A bit after restarting we get:
TRAP: FailedAssertion("!((itemid)->lp_flags & 0x01)", File: "nbtsearch.c",
Line: 89)
Looking through the code I see that's something to do with indexes, but
I'm not sure what. Is this likely a corrupted index? If so, is there
some way I could identify which index?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pidfile location missing after restarting crashed server in 8.1
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Yeah: it's now being accessed by a relative path (relative to $PGDATA, > >> which we have already chdir'd into at this point). > > > So we should include PGDATA in the error message (DataDir actually, I > > guess). > > I don't think that follows. The code prints exactly the path name it's > using to access the file, and I think that's exactly what it should > print. It's not useful for the user that way. Besides, we know what we did chdir() to, so we could present that information to the user. -- Alvaro Herrera "Amanece. (Ignacio Reyes) El Cerro San Cristóbal me mira, cínicamente, con ojos de virgen" ---(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] localhost in pgpass file?
Can anyone explain to me why 'localhost' in a .pgpass file matches both a Unix socket and a tcp localhost connection? Also, there is no documentation at all that I can see to cover the Unix socket case. I found the information after much looking through asking on IRC, where luckily [EMAIL PROTECTED] knew the answer. cheers andrew ---(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] pidfile location missing after restarting crashed server in 8.1
On Wednesday 26 October 2005 14:59, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > the difference being that in 8.0 we printed the full path of the pid
> > file, in 8.1 we do not. there could be a good reason for this,
>
> Yeah: it's now being accessed by a relative path (relative to $PGDATA,
> which we have already chdir'd into at this point).
>
I thought about this, but it didn't seem to me this would actually prevent us
from explicitly stating the full path to the pid file, just make an extra
hoop we would have to go to. ISTM the helpfulness of the information and the
fact that we are in a fatal startup mode would make it worth it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] pidfile location missing after restarting crashed server in 8.1
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Yeah: it's now being accessed by a relative path (relative to $PGDATA, >> which we have already chdir'd into at this point). > So we should include PGDATA in the error message (DataDir actually, I > guess). I don't think that follows. The code prints exactly the path name it's using to access the file, and I think that's exactly what it should print. Are we going to editorialize similarly on every other message that includes a file name? 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] add_missing_from breaks existing views
I wrote: > Neil Conway <[EMAIL PROTECTED]> writes: >> On Tue, 2005-25-10 at 17:43 -0400, Tom Lane wrote: >>> What I suggest we do about this is change addImplicitRTE() to set >>> inFromCl true for implicitly added RTEs, so that the view rule will >>> later be dumped as if the query had been written per spec. >> Sounds reasonable. I wonder if this should be backpatched -- ISTM the >> proper representation of the view is with an explicit FROM list anyway, > I think it'd be reasonable to back-patch it into the branches that have > the add_missing_from variable (how far back did we add that?). I've committed the change, but only in HEAD. Even as late as 8.0, it makes the regression tests fail all over the place, because warnAutoRange gets confused (it's depending on the old behavior of inFromCl). We could possibly rejigger the code to avoid this, but I think the wiser course is to leave the back branches alone. BTW, I noticed an interesting factoid: addImplicitRTE was never modified to obey the SQL_inheritance parameter, so you always get an ONLY reference: regression=# set add_missing_from TO 1; SET regression=# create view vv as select int8_tbl.*; NOTICE: adding missing FROM-clause entry for table "int8_tbl" CREATE VIEW regression=# \d vv View "public.vv" Column | Type | Modifiers ++--- q1 | bigint | q2 | bigint | View definition: SELECT int8_tbl.q1, int8_tbl.q2 FROM ONLY int8_tbl; Considering that this is strictly a legacy feature, we probably should not change its behavior now ... but it does seem a tad inconsistent. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] add_missing_from breaks existing views
On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: >>> Pretending it's the user's mistake isn't >>> an answer that fits down my craw very well... > >> I'm not claiming it's the user's mistake. My point is that if the user >> did in fact remove add_missing_from after creating views that depend on it, >> then they have already run into a bug. > > No, you're looking at this in the wrong direction. It's quite possible that in trimming my messages for posting I'm removing too much of the context; is that the case? To recap: - you pointed out that there was an incompatibility. - I pointed out a way in which that incompatibility can be substantially reduced in scope, from affecting "everyone who has views defined using add_missing_from" to only affecting "everyone who has views defined using add_missing_from but who has subsequently turned that off, in spite of the bugs that they would encounter in doing so". - you respond by saying there is an incompatibility. Now, I don't know how I can possibly be clearer about this. I know that changing the default add_missing_from causes an incompatibility. If you prefer to keep it, rather than use a solution which will work for everyone who (a) isn't already vulnerable to dump+restore problems and (b) will use 8.1's pg_dump to upgrade, then personally I couldn't care less. I'm just surprised by the attitude. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pidfile location missing after restarting crashed server in 8.1
Tom Lane wrote: > Robert Treat <[EMAIL PROTECTED]> writes: > > the difference being that in 8.0 we printed the full path of the pid file, > > in > > 8.1 we do not. there could be a good reason for this, > > Yeah: it's now being accessed by a relative path (relative to $PGDATA, > which we have already chdir'd into at this point). So we should include PGDATA in the error message (DataDir actually, I guess). -- Alvaro Herrera Architect, http://www.EnterpriseDB.com "When the proper man does nothing (wu-wei), his thought is felt ten thousand miles." (Lao Tse) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Libpq optimization
Tom, > And, unfortunately, you've broken it. The pqFlush call visible in that > routine is not the only place that may try to send data (see also > pqPutMsgEnd). You are right, thanks for pointing that out. Still, in pqPutMsgEnd we will be sending data only after 8K is reached, which is about once in 80 for a 100 byte row size... Alon. ---(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] pidfile location missing after restarting crashed server in 8.1
Robert Treat <[EMAIL PROTECTED]> writes: > the difference being that in 8.0 we printed the full path of the pid file, in > 8.1 we do not. there could be a good reason for this, Yeah: it's now being accessed by a relative path (relative to $PGDATA, which we have already chdir'd into at this point). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative
I hate to answer my own question, but I think I may have spotted the issue. I forgot that a TIMESTAMP WITH TIME ZONE is actually stored without a time zone. This datatype would need to better comply with the ANSI/ISO standard for the ANSI/ISO operations on them to work properly. -Kevin >>> "Kevin Grittner" <[EMAIL PROTECTED]> >>> I'm not seeing it. It seems to me that timestamps can be defined WITH or WITHOUT time zone, and the semantics of calculating an interval are fairly clear in either case. An interval doesn't seem like it should have an associated time zone. Adding an interval to a timestamp would use the time zone of the timestamp. What am I missing? -Kevin >>> Tom Lane <[EMAIL PROTECTED]> >>> "Kevin Grittner" <[EMAIL PROTECTED]> writes: > The standard seems rich enough in this area to > address all of the concerns I've seen expressed on this thread. > All the usual advantages for standards compliance accrue, as well. Last I checked, the standard completely failed to deal with daylight savings time changes, making it pretty useless as a guide to solving the problems we want to deal with. 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 ---(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] add_missing_from breaks existing views
On Wed, Oct 26, 2005 at 11:41:56AM -0400, Tom Lane wrote: > Bruce Momjian writes: > > Should we allow CREATE VIEW to run with "add_missing_from = true" > > until we fix CREATE VIEW to handle this cleanly? > > No. Not only is that horridly ugly, it doesn't fix the problem, > because CREATE VIEW is just one case (see also CREATE RULE). If we > were to defeat add_missing_from for both, there wouldn't be much > left of it at all. Going back to default-true would be far cleaner. A VIEW that depends on add-missing-from is a clear case of pilot error. We can document this, warn people, and turn it off by default. Is there some fairly simple way to find VIEWs that have this property so they can be fixed? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] gist problem
Hi list I got memleak fixed, valgrind doesn't quite find problems there, I had to dig it manually. Anyway, to the point. Now that I've created gist index, I want to use it on table, here it goes: CREATE OPERATOR CLASS gist_enum2916_operators DEFAULT FOR TYPE enum2916 USING gist AS OPERATOR1 >>= , OPERATOR2 <<= , OPERATOR3 >> , OPERATOR4 << , OPERATOR5 && , OPERATOR6 = , FUNCTION1 enum_consistent (internal, enum2916, enum2916), FUNCTION2 enum_union (internal, internal), FUNCTION3 enum_compress (internal), FUNCTION4 enum_decompress (internal), FUNCTION5 enum_penalty (internal, internal, internal), FUNCTION6 enum_picksplit (internal, internal), FUNCTION7 enum_same (enum2916, enum2916, internal); than I create table and apply index to it. At this point enable_seqscan is off. CREATE TABLE dupa ( a integer, b enum2916 ); --- we can now create gist index, let's do it :P CREATE INDEX blah ON dupa USING gist (b); I am unable to use ~ or @ operators, I thought gist would supply them for me, am I right ? even through seqscan is off, on explain analyze select * from dupa where b <> 'something'; I get: QUERY PLAN Seq Scan on dupa (cost=0.00..24.50 rows=580 width=36) (actual time=2.344..29.432 rows=12 loops=1) Filter: (b <> '%.4.0.6.1.e164.digifonica.com'::enum2916) Total runtime: 29.936 ms (3 rows) Can someone shed a bit of light on this for me please. This is postgres 8.1b4, basicaly cvs head. Thanks. -- GJ Binary system, you're either 1 or 0... dead or alive ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] add_missing_from breaks existing views
Tom, > After sleeping on it, I feel that we should probably just fix the code > (to make the problem go away going forward) and document the possible > need to turn on add_missing_from to load old dump files as an > incompatibility. We've had worse ones. I'll agree with you here, for (1) reason: if we put this off for another release, the situation's not going to get any better. Eventually, for some release, we need to break something, because we can't fix the past. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pidfile location missing after restarting crashed server in 8.1
Fair warning this was in 8.1beta1 so perhaps this is fixed already.
[EMAIL PROTECTED]:~$ ./start80.sh
pg_ctl: another postmaster may be running; trying to start postmaster anyway
FATAL: pre-existing shared memory block (key 5480001, ID 4063233) is still in
use
HINT: If you're sure there are no old server processes still running, remove
the shared memory block with the command "ipcrm", or just delete the file
"/usr/local/pgsql/data/80/postmaster.pid".
pg_ctl: could not start postmaster
Examine the log output.
[EMAIL PROTECTED]:~$ ./start81.sh
pg_ctl: another postmaster may be running; trying to start postmaster anyway
FATAL: pre-existing shared memory block (key 5481001, ID 4030464) is still in
use
HINT: If you're sure there are no old server processes still running, remove
the shared memory block with the command "ipcclean", "ipcrm", or just delete
the file "postmaster.pid".
pg_ctl: could not start postmaster
Examine the log output.
the difference being that in 8.0 we printed the full path of the pid file, in
8.1 we do not. there could be a good reason for this, but istm that if we
can detect that a pid file is in our way, we should know its path.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Libpq optimization
"Alon Goldshuv" <[EMAIL PROTECTED]> writes: > As the code comment suggests, it is extremely important to consume incoming > messages from the server to prevent deadlock. However we should only worry > about it before sending data out. And, unfortunately, you've broken it. The pqFlush call visible in that routine is not the only place that may try to send data (see also pqPutMsgEnd). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] IMMUTABLE bug ?
strk <[EMAIL PROTECTED]> writes: > Why is so ? shouldn't the IMMUTABLE keywork make > it a single call ? No. There is no function value cache. What does happen here is that the planner folds those calls to constants at plan time, instead of at run time. Try select testme(1) from some-table-with-multiple-rows and note there's only one call not N. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative
I'm not seeing it. It seems to me that timestamps can be defined WITH or WITHOUT time zone, and the semantics of calculating an interval are fairly clear in either case. An interval doesn't seem like it should have an associated time zone. Adding an interval to a timestamp would use the time zone of the timestamp. What am I missing? -Kevin >>> Tom Lane <[EMAIL PROTECTED]> >>> "Kevin Grittner" <[EMAIL PROTECTED]> writes: > The standard seems rich enough in this area to > address all of the concerns I've seen expressed on this thread. > All the usual advantages for standards compliance accrue, as well. Last I checked, the standard completely failed to deal with daylight savings time changes, making it pretty useless as a guide to solving the problems we want to deal with. 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
[HACKERS] Libpq optimization
In the libpq COPY interface function PQputCopyData():
/*
* Check for NOTICE messages coming back from the server. Since the
* server might generate multiple notices during the COPY, we have to
* consume those in a reasonably prompt fashion to prevent the comm
* buffers from filling up and possibly blocking the server.
*/
if (!PQconsumeInput(conn))
return -1;/* I/O failure */
parseInput(conn);
I moved it to a different location, just a bit further, after the check for
"is output buffer full and we are ready to flush?" in the same function
if ((conn->outBufSize - conn->outCount - 5) < nbytes)
{
}
As the code comment suggests, it is extremely important to consume incoming
messages from the server to prevent deadlock. However we should only worry
about it before sending data out. Most calls to PQputCopyData don't actually
send any data but just place it in the out buffer and return. Therefore we
can perform the consumeinput/parseinput right before flushing, instead of
reading from the server every time we call PQputCopyData and not send
anything (which happens probably in 99% of the time).
Right? Or am I missing something.
This change improves COPY performance.
thx
Alon.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] IMMUTABLE bug ?
I cant get the IMMUTABLE modifier meaning. The 'testme' IMMUTABLE function is invoked 3 times in the following query: # select testme(1), testme(1), testme(1); NOTICE: called NOTICE: called NOTICE: called testme | testme | testme ++ ret| ret| ret (1 row) Why is so ? shouldn't the IMMUTABLE keywork make it a single call ? >From the 8.0 manual : IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. The function definition: CREATE OR REPLACE FUNCTION testme(integer) RETURNS text AS ' BEGIN RAISE NOTICE ''called''; return ''ret''::text; END ' LANGUAGE 'plpgsql' IMMUTABLE STRICT; PostgreSQL version 8.0.0 --strk; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] problem with custom Gist for custom type
Ok, I got it, google is my friend ;) ... > Can I use valgrind to find out the root of problem. If so, how should I > invoke it with postgres. Postgres it self was configured with: ... -- GJ Binary system, you're either 1 or 0... dead or alive ;) ---(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] problem with custom Gist for custom type
On Wednesday 26 October 2005 18:34, Oleg Bartunov wrote: > btw, if you could read russian, we have GiST programming tutorial > http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html I did try, but last time I did Russian lessons was around 10 years ago, so ... babel fish could help btw, but I would kindly ask for translating this tut. Especially if you can elaborate on picksplit method more, it would be great. -- GJ Binary system, you're either 1 or 0... dead or alive ;) ---(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] problem with custom Gist for custom type
btw, if you could read russian, we have GiST programming tutorial http://www.sai.msu.su/~megera/postgres/talks/gist_tutorial.html Oleg On Wed, 26 Oct 2005, Tom Lane wrote: Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: I created custom type, and attempt to create gist indexing for it, to make search wind quick. on select from * where something << 'something'; I get: WARNING: problem in alloc set GiST temporary context: detected write past chunk end in block 0x843e6e0, chunk 0x843e994 Can someone tell me please, what might be the cause? Looks like you're scribbling on memory that doesn't belong to you. Check for miscomputed palloc request sizes, etc. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] problem with custom Gist for custom type
On Wednesday 26 October 2005 17:48, Tom Lane wrote: > Looks like you're scribbling on memory that doesn't belong to you. > Check for miscomputed palloc request sizes, etc. Thanks very much for the tip. I thogut perhaps I am not filling out some parts of structures, or something. Can I use valgrind to find out the root of problem. If so, how should I invoke it with postgres. Postgres it self was configured with: ./configure --prefix=/usr/local/postgres --with-python --enable-cassert --with-openssl --with-gnu-ld --enable-integer-datetimes --enable-debug Thanks. -- GJ Binary system, you're either 1 or 0... dead or alive ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Increase of buffers usage count by bgwriter
ITAGAKI Takahiro <[EMAIL PROTECTED]> writes: > I found that bgwriter increments usage count of buffers when it writes > the buffers. I feel this behavior is strange, because the behavior of > bgwriter will affect buffer management strategy. Good point --- we probably don't want it to do that. > If the behavior is not intended, another UnpinBuffer that doesn't > increase the buffers usage count will be needed. > Otherwise, bgwriter should do behavior that looks like VACUUM, > using StrategyHintVacuum(true). I think it might be sufficient to not increment usage_count when UnpinBuffer's "trashOK" parameter is false --- that's what SyncOneBuffer passes, so it'd fix the issue for bgwriter activity, and arguably all the other call sites that pass false also don't want the count to increment. (Probably need a different name for trashOK ...) Comments? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] add_missing_from breaks existing views
Andrew - Supernews <[EMAIL PROTECTED]> writes: > On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: >> Pretending it's the user's mistake isn't >> an answer that fits down my craw very well... > I'm not claiming it's the user's mistake. My point is that if the user > did in fact remove add_missing_from after creating views that depend on it, > then they have already run into a bug. No, you're looking at this in the wrong direction. The problem is that the user hasn't had to do anything so far, because add_missing_from has defaulted to true in every prior release. So he could have been sailing along with views written in the old style up to now, and not noticed any problem. We are creating the problem by changing the default behavior ... or at least, that's how it will look to people who get burnt by this. After sleeping on it, I feel that we should probably just fix the code (to make the problem go away going forward) and document the possible need to turn on add_missing_from to load old dump files as an incompatibility. We've had worse ones. 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
Re: [HACKERS] problem with custom Gist for custom type
Grzegorz Jaskiewicz <[EMAIL PROTECTED]> writes: > I created custom type, and attempt to create gist indexing for it, to > make search wind quick. > on select from * where something << 'something'; I get: > WARNING: problem in alloc set GiST temporary context: detected write > past chunk end in block 0x843e6e0, chunk 0x843e994 > Can someone tell me please, what might be the cause? Looks like you're scribbling on memory that doesn't belong to you. Check for miscomputed palloc request sizes, etc. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] add_missing_from breaks existing views
Bruce Momjian writes: > Should we allow CREATE VIEW to run with "add_missing_from = true" until > we fix CREATE VIEW to handle this cleanly? No. Not only is that horridly ugly, it doesn't fix the problem, because CREATE VIEW is just one case (see also CREATE RULE). If we were to defeat add_missing_from for both, there wouldn't be much left of it at all. Going back to default-true would be far cleaner. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Determining random_page_cost value
Yohanes Santoso wrote: I then ran it on various database size on a software 2 7200RPM IDE RAID-1 volume. I found out that if the dbase size (as from du ) is less than about 500M, I got a ratio of 4.5:1. On a larger dbase, 3GB, the ratio increases to 10:1. Surely this is going to be a function of table size, not database size, isn't it? Did you scale up the size of tables to get your 3Gb database? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > The standard seems rich enough in this area to > address all of the concerns I've seen expressed on this thread. > All the usual advantages for standards compliance accrue, as well. Last I checked, the standard completely failed to deal with daylight savings time changes, making it pretty useless as a guide to solving the problems we want to deal with. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] problem with custom Gist for custom type
Hi there folks I created custom type, and attempt to create gist indexing for it, to make search wind quick. on select from * where something << 'something'; I get: WARNING: problem in alloc set GiST temporary context: detected write past chunk end in block 0x843e6e0, chunk 0x843e994 Can someone tell me please, what might be the cause? Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian writes: > Tom Lane wrote: >> Not hardly. I tried already. The existing timestamp_mi behavior is >> probably as close to 8.0 as we can get given the change in underlying >> representation. > You mean the '6432 hours' is a worse change, OK. Well, it's sure not a small change, and we're still undecided whether that's what we want in the long run. Also, we'd have to deal with some of the other TODO items I mentioned before we could make it work at all. There's at least one regression test that computes an interval larger than 2^31 hours (how do you think I found out about that problem ;-)) 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
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative
If you are going to roll this back in 8.1 to reevaluate the issue, I
think the ANSI/ISO standards should be reviewed as part of that
reevaluation. The standard seems rich enough in this area to
address all of the concerns I've seen expressed on this thread.
All the usual advantages for standards compliance accrue, as well.
So, for example, you could specify:
-- to get the interval in days, hours, and minutes:
(timestampx - timestampy) day to minute
-- to get the interval in days, to 2 decimal places:
(timestampx - timestampy) day{2)
-- to get the interval in hours:
(timestampx - timestampy) hour
>>> Bruce Momjian >>>
I guess my point is that we are changing 8.0.X behavior so we better be
sure it is now the way we want it to remain.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Tom Lane wrote: > Bruce Momjian writes: > > Keep in mind that the addition of the interval_justify_hours() did > > generate some regression test changes, so removing > > interval_justify_hours() might just take the results back to what we had > > in 8.0. > > Not hardly. I tried already. The existing timestamp_mi behavior is > probably as close to 8.0 as we can get given the change in underlying > representation. You mean the '6432 hours' is a worse change, OK. > > I guess my point is that we are changing 8.0.X behavior so we better be > > sure it is now the way we want it to remain. > > [ shrug... ] We've changed datetime behavior in every past release, > we're changing it for 8.1, we'll probably change it some more for 8.2, > and again after that. All the datetime code is a work in progress. > Get used to it. OK, as long as we are sure we are not going to change it back to 8.0 behavior. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Bruce Momjian writes: > Keep in mind that the addition of the interval_justify_hours() did > generate some regression test changes, so removing > interval_justify_hours() might just take the results back to what we had > in 8.0. Not hardly. I tried already. The existing timestamp_mi behavior is probably as close to 8.0 as we can get given the change in underlying representation. > I guess my point is that we are changing 8.0.X behavior so we better be > sure it is now the way we want it to remain. [ shrug... ] We've changed datetime behavior in every past release, we're changing it for 8.1, we'll probably change it some more for 8.2, and again after that. All the datetime code is a work in progress. Get used to it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] add_missing_from breaks existing views
Should we allow CREATE VIEW to run with "add_missing_from = true" until we fix CREATE VIEW to handle this cleanly? We emit a warning when we add a missing FROM too, as I remember. --- Andrew - Supernews wrote: > On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: > > Andrew - Supernews <[EMAIL PROTECTED]> writes: > >> On 2005-10-26, Tom Lane <[EMAIL PROTECTED]> wrote: > >>> Uh, no ... the global setting of add_missing_from does *not* tell you > >>> anything about whether there exist views in the database that were > >>> created under a different setting. > > > >> I realize that; but is it also not the case that someone who creates a > >> view that requires add_missing_from, and then turns it off, has _already_ > >> broken dump+restore on his own database? > > > > No, because we consider that a client-local setting. This argument is > > akin to saying that if a client loads some data with client_encoding FOO > > into a database with server_encoding BAR, we are not responsible for > > dumping and reloading the data correctly. > > 8.0: > > test=# show add_missing_from; > add_missing_from > -- > off > (1 row) > > test=# set add_missing_from to true; > SET > test=# create view v1 as select test.*; > CREATE VIEW > test=# \q > > % pg_dump -U pgsql -s -d test | psql -U pgsql -d test2 > > [...] > ERROR: missing FROM-clause entry for table "test" > ERROR: relation "public.v1" does not exist > > Looks broken to me. > > I wasn't arguing that the broken behaviour was correct, merely that it > exists. > > > In hindsight I think there's no doubt that we blew it in not making > > ruleutils.c reverse-list implicit RTEs some time ago. > > Obviously. Isn't hindsight wonderful. > > > Pretending it's the user's mistake isn't > > an answer that fits down my craw very well... > > I'm not claiming it's the user's mistake. My point is that if the user > did in fact remove add_missing_from after creating views that depend on it, > then they have already run into a bug. > > -- > Andrew, Supernews > http://www.supernews.com - individual and corporate NNTP services > > ---(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 > -- 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] [BUGS] BUG #1993: Adding/subtracting negative time intervals
[ bugs list removed, hackers added.]
Tom Lane wrote:
> Bruce Momjian writes:
> > I saw a lot of disussion because I forgot to specify that my tests were
> > for EST5EDT, but what about the use of interval_justify_hours() in
> > timestamp_mi(). Is this something we want to change?
>
> It's too late to mess with it for 8.1, but see my previous message
> proposing a set of TODO items for future work.
Yes, it is late, but I am worried about adding an interface change that
we will later revert in 8.2. In 8.0.X I see the query returning the '25
hour' answer:
SELECT
('2005-10-29 13:22:00-04'::timestamptz +
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
timezone
-
2005-10-30 13:22:00
(1 row)
In current CVS the top query returns '14:22:00'. Do we change this for
8.1, then change it back in 8.2? That seems bad to me.
Actually, 8.0.X returns '1 day, 1 hour' for the subtraction, which we
treat in 8.0.X as '25 hours':
SELECT
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz);
?column?
1 day 01:00:00
(1 row)
In 8.0.X, because we didn't have a 'days' field, we could treat '1 day 1
hour' as always '25 hours', and could display the results as days/hours.
If we remove interval_justify_hours(), then we are always going to
display timestamp subtraction in hours (not days), e.g. '6422 hours'
(yea, ugly) unless they manually call interval_justify_hours().
Keep in mind that the addition of the interval_justify_hours() did
generate some regression test changes, so removing
interval_justify_hours() might just take the results back to what we had
in 8.0. My point is that regression changes caused by its removal might
not be a good guide to determining compatibility with 8.0.X.
I guess my point is that we are changing 8.0.X behavior so we better be
sure it is now the way we want it to remain.
--
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 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] expanded \df+ display broken in beta4
Bruce Momjian wrote: Michael Paesold wrote: Tom Lane wrote: "Michael Paesold" <[EMAIL PROTECTED]> writes: Robert Treat wrote: ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. Should we revert the patch for the time being, and take another go at it in 8.2? One idea is to hack \d not to honor \x, and let the others honor it. That would probably hit most of the cases people will use in 8.1. In fact, \d is pretty special because it is more of a group of outputs, unlike \df, which is a single table output. +1 from me. That seems like a workable compromise and should probably meet the needs of the author of the patch to change the \x behavior. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] expanded \df+ display broken in beta4
Michael Paesold wrote: > Tom Lane wrote: > > "Michael Paesold" <[EMAIL PROTECTED]> writes: > > > >>Robert Treat wrote: > >> > >>>ISTM even a GUC to enable/disable would have been better scheme than > >>>what we have now; we are basically leaving no options for those who > >>>found the old behavior useful, while what we had before would at least > >>>let people switch back and forth. > > > > > >>I think Robert is right here and the new behaviour is a step backwards. > > > > > > Should we revert the patch for the time being, and take another go at it > > in 8.2? > > As the last option, if there is no quick fix, I'd say yes. Better than > making an incomplete change now and revert that later to get a better > solution. > > > Is it practical to have the old behavior for \df (and anything else with > > particularly wide output) while still forcing \x off for \d? > > I think there are quite some ways to handle the problem: > 1) \x to get the new behaviour, \xx or \x+ to get the new one. > > 2) Have \x+ or something automatically switch based on screen width. > > Other suggestions... > Well, unfortunately, I guess now is not the time to discuss this for 8.1. One idea is to hack \d not to honor \x, and let the others honor it. That would probably hit most of the cases people will use in 8.1. In fact, \d is pretty special because it is more of a group of outputs, unlike \df, which is a single table output. -- 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] expanded \df+ display broken in beta4
Tom Lane wrote: "Michael Paesold" <[EMAIL PROTECTED]> writes: Robert Treat wrote: ISTM even a GUC to enable/disable would have been better scheme than what we have now; we are basically leaving no options for those who found the old behavior useful, while what we had before would at least let people switch back and forth. I think Robert is right here and the new behaviour is a step backwards. > Should we revert the patch for the time being, and take another go at it in 8.2? As the last option, if there is no quick fix, I'd say yes. Better than making an incomplete change now and revert that later to get a better solution. Is it practical to have the old behavior for \df (and anything else with particularly wide output) while still forcing \x off for \d? I think there are quite some ways to handle the problem: 1) \x to get the new behaviour, \xx or \x+ to get the new one. 2) Have \x+ or something automatically switch based on screen width. Other suggestions... Well, unfortunately, I guess now is not the time to discuss this for 8.1. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #1993: Adding/subtracting negative time intervals
Sorry, I should have CC'ed hackers on this. The issue is that because
of interval_justify_hours(), subtracting a fixed interval from a
timestamp and re-adding the same value produces a different result.
---
Bruce Momjian wrote:
>
> I saw a lot of disussion because I forgot to specify that my tests were
> for EST5EDT, but what about the use of interval_justify_hours() in
> timestamp_mi(). Is this something we want to change?
>
> ---
>
> Bruce Momjian wrote:
> > Klint Gore wrote:
> > > On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
> > > > regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1
> > > > day'::interval;
> > > > ?column?
> > > >
> > > > 2005-10-30 13:22:00-05
> > > > (1 row)
> > > >
> > > > regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
> > > > 13:22:00-04'::timestamptz;
> > > > ?column?
> > > >
> > > > 1 day 01:00:00
> > > > (1 row)
> > > >
> > > > ISTM that given the former result, the latter calculation ought to
> > > > produce '1 day', not something else.
> > >
> > > Would the '1 day' result know it was 24 hours or be the new 23/24/25
> > > hour version of '1 day'?
> >
> > It has no idea. When you do a subtraction, it isn't clear if you are
> > interested in "days" or "hours", so we give hours. If you want days,
> > you should convert the timestamps to dates and just subtract them.
> >
> > > If it was the new version, could you get the original values back?
> > > i.e. what would be the result of
> > > select
> > > ('2005-10-29 13:22:00-04'::timestamptz +
> > > ('2005-10-30 13:22:00-05'::timestamptz -
> > > '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> >
> > You bring up a good point here. With current CVS your subtraction
> > yields:
> >
> > test-> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> >
> > 1 day 01:00:00
> > (1 row)
> >
> > so adding that to the first timestamp gets:
> >
> > test=> select
> > test-> ('2005-10-29 13:22:00-04'::timestamptz +
> > test(> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> > timezone
> > -
> > 2005-10-30 14:22:00
> > (1 row)
> >
> > This is certainly _not_ what someone would expect as a return value.
> > What happens is that we subtract to generate the number of hours
> > different, but then get all smart that "oh, that is one day to add, and
> > one hour" and return an unexpected value.
> >
> > This is actually a good argument that the use of
> > interval_justify_hours() in timestamp_mi() is a mistake. Without this
> > call, we have:
> >
> > test=> select
> > test-> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> > --
> > 25:00:00
> > (1 row)
> >
> > and
> >
> > test=> select
> > test-> ('2005-10-29 13:22:00-04'::timestamptz +
> > test(> ('2005-10-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
> > timezone
> > -
> > 2005-10-30 13:22:00
> > (1 row)
> >
> > but it also has the tendency to return some very high values for hours:
> >
> > test=> select
> > test-> ('2005-12-30 13:22:00-05'::timestamptz -
> > test(> '2005-10-29 13:22:00-04'::timestamptz);
> > ?column?
> >
> > 1489:00:00
> > (1 row)
> >
> > but again, if you want days, you can cast to days.
> >
> > --
> > 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 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 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If
[HACKERS] Determining random_page_cost value
I talked with neilc in #postgresql about a tool that may help in determining a suitable rpc value. If rpc is just ratio of random cost vs. sequential cost, then I can write such a tool. So I did. The tool takes a directory and do sequential read on all the files there, followed by exhaustive (each block is read) random read on each file. There is also an option to clear up buffers between runs. I then ran it on various database size on a software 2 7200RPM IDE RAID-1 volume. I found out that if the dbase size (as from du ) is less than about 500M, I got a ratio of 4.5:1. On a larger dbase, 3GB, the ratio increases to 10:1. This tells me that the ratio needs to be qualified further: access over what size of dbase? access over the disk volume instead? what other hidden variables? The full code is at http://rafb.net/paste/results/peLyIX45.html Thanks, Gnome. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PQescapeIdentifier
This has been saved for the 8.2 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Christopher Kings-Lynne wrote: > TODO item done for 8.2: > > * Add PQescapeIdentifier() to libpq > > Someone probably needs to check this :) > > Chris [ application/x-gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] memcpy SEGV on AIX 5.3
Stefan Kaltenbrunner wrote: > Seneca Cunningham wrote: > >>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. > > > looks like I'm seeing a similiar problem(using -HEAD) on AIX 5.3ML3 > using the IBM AIX c-compiler. initdb just hangs after "selecting default > max_connections ..." in a 100% CPU-loop. yeah this seems to be the very same issue. A backtrace of a stuck initdb-process looks like: (gdb) bt #0 0x10006f10 in bcopy () #1 0x1001d398 in hash_search (hashp=0x1001d85c, keyPtr=0xf020f9fc, action=804399456, foundPtr=0x20029d78 "") at dynahash.c:673 #2 0x100a5e58 in formrdesc (relationName=0x2006bf38 "t\emplat\e1", relationReltype=0, hasoids=0 '\0', natts=0, att=0x200100f8) at relcache.c:1295 #3 0x100a818c in RelationCacheInitialize () at relcache.c:2160 #4 0x102016ec in InitPostgres (dbname=0xd025b7f0 "\200A", username=0x2 "") at postinit.c:424 #5 0x102009e8 in BootstrapMain (argc=271528, argv=0xb0002) at bootstrap.c:445 #6 0x1578 in main (argc=0, argv=0x0) at main.c:285 #7 0x122c in __start () Stefan ---(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
