[HACKERS] Two proposals of DBA helper functions
Hi all, I would like to submit two ideas: 1/ In the daily tasks I have as (also) a standard DBA, I found usefull to view locks in a human point of view. I think the ~ '^pg_' part of the queries may be not so clean... Any ideas welcome :) 2/ Also, I like having a cache hit/miss ratio. This is why I add a function pg_stat_get_db_hit_miss_ratio(database oid) to pg_stat_database, in a bis view. Since I dont want to flood the mailing list with a possible useless thing, I put my sql/plpgsql code here: http://priam.dalibo.net/~jpa/propositions/ Please take a look at it and tell me if this could be usefull or not.. Thanks a lot, -- Jean-Paul Argudo www.postgresqlfr.org www.dalibo.com ---(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] pgsql: We're going to have to spell dotless i
Hi, Hannu, Hannu Krosing wrote: Are you sure it's UCS-4 ? I've always thought that XML is what is given in xml tag, and utf-8 if no charset is given. You have to distinguish between the supported charset, and the document encoding. UCS-4 and UTF-8 are both encodings for UNICODE see: http://en.wikipedia.org/wiki/UTF-32 Yes, I know. The Point I wanted to make was that the document encoding is independent from the allowed charset (except having to be a subset). That is what XML entities were defined for. So even in an document using LATIN-1 as encoding, the charset still is Unicode, giving us the possibility to use entities; to use non-latin1 characters. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] pgsql: We're going to have to spell dotless i
Hi, Bruce, Bruce Momjian wrote: I don't think that any of our SGML documentation is actually in UCS-4 encoding. The source files use nothing beyond plain ASCII (and should remain that way, IMHO) so there isn't any need to inquire very far into exactly what the toolchain thinks the document encoding is. The issue at hand here is what the *output* character set is, which is to say the document character set if I have the jargon right. That is the space over which we are permitted to use -entities. Just for reference, if we could support UTF8, I was hoping to add non-Latin names as alternates to the ASCII versions, so we could have Japanese and Russian-lettered names in the release notes. I thought it would be a nice touch. We don't need UTF8 encoding for this. It's also possible using ASCII encoding + #4711; entities. But we need the Charset to be Unicode. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Questions about guc units
Am Montag, 25. September 2006 04:04 schrieb ITAGAKI Takahiro: #shared_buffers = 32000kB # min 128kB or max_connections*16kB #temp_buffers = 8000kB # min 800kB #effective_cache_size = 8000kB Are there any reasons to continue to use 1000-unit numbers? Megabyte-unit (32MB and 8MB) seems to be more friendly for users. It increases some amount of values (4000 vs. 4096), but there is little in it. The reason with the shared_buffers is that the detection code in initdb has 400kB as minimum value, and it would be pretty complicated to code the detection code to handle both kB and MB units. If someone wants to try it, though, please go ahead. We could probably change the others. #max_fsm_pages = 160# min max_fsm_relations*16, 6 bytes each #wal_buffers = 8# min 4, 8kB each They don't have units now, but should they have GUC_UNIT_BLOCKS and GUC_UNIT_XLOG_BLCKSZ unit? I feel inconsistency in them. max_fsm_pages doesn't have a discernible unit, but wal_buffers probably should. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Increase default effective_cache_size?
current default of 1000 pages (8Mb) seems really pretty silly for modern machines; we could certainly set it to 10 times that without problems, and maybe much more. Thoughts? May be, set by default effective_cache_size equal to number of shared buffers? If pgsql is configured to use quarter or half of total memory for shared buffer, then effective_cache_size will have good approximation... -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Buildfarm failure at initdb - member Cassowary
Hello, The Cassowary buildfarm memeber is curently failing when performing initdb . The corresponding log file initdb.log is empty. When running initdb manually, it dies immediately with code 0200. Other executables (psql, createlang, createdb for example) fail in the same manner. But some others (postgres,postmaster,ecpg) seem to be ok ... My assuption is that I haven't configured something properly in Cygwin : it's a fresh install after having switched to a new machine. Unfortunately, without an error message, it's hard to identify the problem. The FAQ_CYGWIN suggests that modifying the cygserver config may be neccessary, but it doesn't provide more details. Does anyone have any advice ? Cheers, Adrian Maier $ gdb bin/initdb.exe (gdb) break main Breakpoint 1 at 0x403200: file initdb.c, line 2358. (gdb) run Starting program: /home/am/build/HEAD/inst/bin/initdb.exe gdb: unknown target exception 0xc022 at 0x77f966bc Program received signal ?, Unknown signal. Program exited with code 0200. ---(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] 8.3 Development Cycle
On Fri, 2006-09-22 at 14:16 +0100, Dave Page wrote: Following the recent discussion on this list and another on pgsql-core, we have decided that we would like to aim to meet the following schedule for the release of PostgreSQL 8.3: April 1st 2007 - Feature freeze May 1st 2007 - Beta 1 release June 1st 2007 - Release This will obviously be a short development cycle which will allow us to get some of the features that just missed 8.2 out of the door, as well as giving us the opportunity to try releasing before the summer (for those in the northern hemisphere) rather than after. We are also aware that this is a tight timetable, however given the shorter development cycle we feel it is an achievable goal. Thanks for that, Dave and Core. I'm very happy to have clearly stated dates. That means we can all plan what we'll be able to achieve in that time, which is important when some of the largest or most complex features are being considered. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] DELETE RETURNING
Hi, I just read the docs about DELETE RETURNING in 8.2, and a small idea arised: INSERT INTO logtable DELETE * FROM lifetable WHERE date'2006-01-01' RETURNING *; Will this work as expected? It might be a good example to put into the docs then. If not, it may be worth the effort to make it work in 8.3. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Windows build farm failures
On Sun, Sep 24, 2006 at 08:54:35PM +0100, Dave Page wrote: Snake and Bandicoot are still hanging in ECPG-Check at the moment. Killing the dt_test.exe program that the regression tests seem to be running frees it all up to properly report the failure. I don't have time to investigate further at the minute, but for anyone that does, Bandicoot's last run was completed only by killing dt_test.exe, whereas Snakes was a little more random :-) I just had a look at the reports and it seems we have several things going on: 1) libpq gives additional information when not able to connect: could not connect to server: Connection refused (0x274D/10061) instead of just: could not connect to server: Connection refused Any idea? 2) Printf %g with a double high enough for an exponential output gives a difference in the exponent. This is due to Windows using three digits while the Unixes use just two, e.g. e+027 instead of e+27. This double stuff creates so many headaches that I wonder if we better not test it at all in the regression suite. Comments? 3) dt_test had to be killed. Judging from the logs it seems the program hang in either PGTYPESdate_from_asc() or PGTYPEStimestamp_from_asc(). Could someone with a Windows/PostgreSQL setup run this test with debugging symbols and tell me where it hangs? It looks like an endless loop to me, but apparently nothing happens on other archs. 4) snake even stopped building the regression suite: testing sql/indicators.pgc ... make[1]: *** [check] Error 1 make[1]: Leaving directory `/usr/local/build-farm/HEAD/pgsql.4896/src/interfaces/ecpg/test' make: *** [check] Error 2 Was this killed manually too? Or did it stop on its own? I'm surprised there is no output explaning why it stops. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Windows build farm failures
-Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: 25 September 2006 11:57 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Windows build farm failures On Sun, Sep 24, 2006 at 08:54:35PM +0100, Dave Page wrote: Snake and Bandicoot are still hanging in ECPG-Check at the moment. Killing the dt_test.exe program that the regression tests seem to be running frees it all up to properly report the failure. I don't have time to investigate further at the minute, but for anyone that does, Bandicoot's last run was completed only by killing dt_test.exe, whereas Snakes was a little more random :-) I just had a look at the reports and it seems we have several things going on: 1) libpq gives additional information when not able to connect: could not connect to server: Connection refused (0x274D/10061) instead of just: could not connect to server: Connection refused Any idea? Windows error codes I guess. 2) Printf %g with a double high enough for an exponential output gives a difference in the exponent. This is due to Windows using three digits while the Unixes use just two, e.g. e+027 instead of e+27. This double stuff creates so many headaches that I wonder if we better not test it at all in the regression suite. Comments? 3) dt_test had to be killed. Judging from the logs it seems the program hang in either PGTYPESdate_from_asc() or PGTYPEStimestamp_from_asc(). Could someone with a Windows/PostgreSQL setup run this test with debugging symbols and tell me where it hangs? It looks like an endless loop to me, but apparently nothing happens on other archs. Unfortunately I'm one of those people who never, ever managed to get a useful backtrace out of GDB on Windows. The only person I've heard of who actually managed to do it enough to document it was Merlin. 4) snake even stopped building the regression suite: testing sql/indicators.pgc ... make[1]: *** [check] Error 1 make[1]: Leaving directory `/usr/local/build-farm/HEAD/pgsql.4896/src/interfaces/ecpg/test' make: *** [check] Error 2 Was this killed manually too? Or did it stop on its own? I'm surprised there is no output explaning why it stops. It was killed, but I started with some of the sh.exe's before I saw that dt_test.exe was running. I've just killed dt_test.exe (and nothing else) on Snake and Bandicoot, so you should see a new set of results for both. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Broken link in PG docs
At the end of the following page:http://www.postgresql.org/docs/8.0/static/indexes-partial.htmlthere is a link [ Generalized Partial Indexes] which is pointing to a missing link. Can someone update the link with a live doc? Probably this one Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
[HACKERS] Small docu mismatch
Hi, http://developer.postgresql.org/pgdocs/postgres/release-8-2.html tells: Add pg_dump -X no-data-for-failed-tables option to suppress loading data if table creation failed (the table already exists) (Martin Pitt) However, http://developer.postgresql.org/pgdocs/postgres/app-pgdump.html seem not to mention this option. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Getting a move on for 8.2 beta
On Sep 22, 2006, at 2:50 PM, Joshua D. Drake wrote: And how were you planning to tell if a patch cam from a regular? Hopefully you weren't planning on blindly trusting the from header. Misuse of the build farm in a way the effects other sites could get the project a big black eye, so you want to be very careful building and executing code from the patch queue. Of course not, but there's any number of ways we could handle that problem. pgp signed patches? Just one possibility. Submitting the patches via a web page that you have to log into is another. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Windows build farm failures
I just had a look at the reports and it seems we have several things going on: 1) libpq gives additional information when not able to connect: could not connect to server: Connection refused (0x274D/10061) instead of just: could not connect to server: Connection refused Any idea? Those are windows errorcodes (same code written in box hex and decimal). Not really sure why we have it different on win32, but it has been like that for ages. 2) Printf %g with a double high enough for an exponential output gives a difference in the exponent. This is due to Windows using three digits while the Unixes use just two, e.g. e+027 instead of e+27. Yeah, this is known. You'll notice we have extra ouput files for the standard regression tests to deal with this. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DELETE RETURNING
On 25-Sep-06, at 4:31 AM, Markus Schaber wrote: Hi, I just read the docs about DELETE RETURNING in 8.2, and a small idea arised: INSERT INTO logtable DELETE * FROM lifetable WHERE date'2006-01-01' RETURNING *; Will this work as expected? What is your expected result here ? It would return all the rows that were deleted ? Dave It might be a good example to put into the docs then. If not, it may be worth the effort to make it work in 8.3. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(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] Buildfarm failure at initdb - member Cassowary
It's very weird, because if you get through to this stage it means you have already run initdb successfully during the make-check stage. Certainly the trace below looks like it's a failure in the startup code - maybe a DLL mismatch, although that shouldn't be possible? Does your Cygwin install have their postgres package included? cheers andrew Adrian Maier wrote: Hello, The Cassowary buildfarm memeber is curently failing when performing initdb . The corresponding log file initdb.log is empty. When running initdb manually, it dies immediately with code 0200. Other executables (psql, createlang, createdb for example) fail in the same manner. But some others (postgres,postmaster,ecpg) seem to be ok ... My assuption is that I haven't configured something properly in Cygwin : it's a fresh install after having switched to a new machine. Unfortunately, without an error message, it's hard to identify the problem. The FAQ_CYGWIN suggests that modifying the cygserver config may be neccessary, but it doesn't provide more details. Does anyone have any advice ? Cheers, Adrian Maier $ gdb bin/initdb.exe (gdb) break main Breakpoint 1 at 0x403200: file initdb.c, line 2358. (gdb) run Starting program: /home/am/build/HEAD/inst/bin/initdb.exe gdb: unknown target exception 0xc022 at 0x77f966bc Program received signal ?, Unknown signal. Program exited with code 0200. ---(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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Increase default effective_cache_size?
Teodor Sigaev wrote: current default of 1000 pages (8Mb) seems really pretty silly for modern machines; we could certainly set it to 10 times that without problems, and maybe much more. Thoughts? May be, set by default effective_cache_size equal to number of shared buffers? If pgsql is configured to use quarter or half of total memory for shared buffer, then effective_cache_size will have good approximation... Initdb does not currently make any attempt to discover the extent of physical or virtual memory, it simply tries to start postgres with certain shared_buffer settings, starting at 4000, and going down until we get a success. max_fsm_pages is now fixed proportionally with shared_buffers, and I guess we could do something similar with effective_cache_size, but since IIRC this doesn't involve shared memory I'm inclined to agree with Tom that it should just be fixed at some substantially higher level. 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] Buildfarm alarms
On Sun, Sep 24, 2006 at 11:51:49AM +0100, Dave Page wrote: wrong to the monitoring processes - what had happened was that both had hung or got in an inifinite loop in ECPG-check, the machine was running just fine Is this still an issue? Can you provide more information? What happens if you run ecpg-check manually? Which test hangs? Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DELETE RETURNING
Hi, Dave, Dave Cramer wrote: I just read the docs about DELETE RETURNING in 8.2, and a small idea arised: INSERT INTO logtable DELETE * FROM lifetable WHERE date'2006-01-01' RETURNING *; Will this work as expected? What is your expected result here ? It would return all the rows that were deleted ? Yes. It would be shorthand for moving rows between tables, faster than first using INSERT INTO .. SELECT and then DELETE afterwards, as it saves at least on table scan. The question is whether INSERT INTO only allows SELECT as data source, or every query returning a ResultSet. I don't see any usecases for using UPDATE RETURNING and INSERT RETURNING as data source for INSERT INTO yet, especially as UPDATE RETURNING returns the new versions of the rows. And I see that the same behaviour could be achieved with triggers, but with much higher overhead for non-regular tasks. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] DROP FUNCTION IF EXISTS
Linux: $ echo 'DROP FUNCTION if exists foo(int);' | psql 1c Timing is on. SET Time: 197.941 ms NOTICE: function foo(░) does not exist ... skipping ERROR: invalid byte sequence for encoding UTF8: 0x90 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0x90 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. FreeBSD: % echo 'DROP FUNCTION if exists foo(int);' | psql wow SET NOTICE: function foo(% Looks like uninitialized pointer... -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP FUNCTION IF EXISTS
Linux: $ echo 'DROP FUNCTION if exists foo(int);' | psql 1c Timing is on. SET Time: 197.941 ms NOTICE: function foo(░) does not exist ... skipping ERROR: invalid byte sequence for encoding UTF8: 0x90 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. ERROR: invalid byte sequence for encoding UTF8: 0x90 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. FreeBSD: % echo 'DROP FUNCTION if exists foo(int);' | psql wow SET NOTICE: function foo(% Looks like uninitialized pointer... Not being an expert, but to me it looks like the client_encoding being set to UTF8 but the data being sent is something other than UTF8. I've seen this happen on Linux when connecting with PuTTY from Windows (and then psql from the linux machine) and having the wrong encoding set in PuTTY. I'd double and triple-check the client-side stuff first ;-) //Magnus ---(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] DROP FUNCTION IF EXISTS
Not being an expert, but to me it looks like the client_encoding being set to UTF8 but the data being sent is something other than UTF8. I've seen this happen on Linux when connecting with PuTTY from Windows (and then psql from the linux machine) and having the wrong encoding set in PuTTY. I'd double and triple-check the client-side stuff first ;-) I have seen another strange occurrence of such errors... I'm using linux with UTF8 client encoding, and psql gives me such errors: dbval=# select 1; ERROR: column 1 does not exist The full story is that I typed 'ü' (u-umlaut if it won't render correctly) and backspace before the '1'. I guess the backspace will delete byte-wise and will so fail to delete properly multi-byte characters. I have no idea if this is a problem of psql or some other problem, and it was not annoying enough to report it... BTW, the space in 1 is something I was not able to copy-paste from the psql command line into the mail, so I'm pretty sure it's a byte code which is invalid UTF8. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP FUNCTION IF EXISTS
Hi, Csaba, Csaba Nagy wrote: dbval=# select 1; ERROR: column 1 does not exist The full story is that I typed 'ü' (u-umlaut if it won't render correctly) and backspace before the '1'. I guess the backspace will delete byte-wise and will so fail to delete properly multi-byte characters. I have no idea if this is a problem of psql or some other problem, and it was not annoying enough to report it... I think this could be a problem with libreadline / libedit not being utf8-safe. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] DROP FUNCTION IF EXISTS
Not being an expert, but to me it looks like the client_encoding being set to UTF8 but the data being sent is something other than UTF8. I've seen this happen on Linux when connecting with PuTTY from Windows (and then psql from the linux machine) and having the wrong encoding set in PuTTY. I'd double and triple-check the client-side stuff first ;-) All characters in query are an ANSI. On freebsd encoding of client and server is a KOI8-R, not an UTF. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Buildfarm alarms
-Original Message- From: Joachim Wieland [mailto:[EMAIL PROTECTED] Sent: 25 September 2006 13:25 To: Dave Page Cc: Andrew Dunstan; pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: Re: [HACKERS] Buildfarm alarms On Sun, Sep 24, 2006 at 11:51:49AM +0100, Dave Page wrote: wrong to the monitoring processes - what had happened was that both had hung or got in an inifinite loop in ECPG-check, the machine was running just fine Is this still an issue? Can you provide more information? What happens if you run ecpg-check manually? Which test hangs? Dt_test is the one that hangs - though in actual fact what is happening is that it's crashing and popping up a 'do you wanna debug' dialogue which doesn't get seen in a non-interactive buildfarm run. After saying no to that, the complete list of failed tests is (see Snake/Bandicoot's logs for more info): testing connect/test1.pgc ... FAILED (log) testing compat_informix/dec_test.pgc ... FAILED (output) testing preproc/variable.pgc ... FAILED (log, output) testing pgtypeslib/dt_test.pgc ... FAILED (log, output) testing pgtypeslib/num_test.pgc... FAILED (output) testing pgtypeslib/num_test2.pgc ... FAILED (output) Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] DROP FUNCTION IF EXISTS
postgres=# drop type if exists foo; NOTICE: type foo does not exist, skipping DROP TYPE postgres=# drop table if exists foo; NOTICE: table foo does not exist, skipping DROP TABLE postgres=# drop function if exists foo(); NOTICE: function foo() does not exist ... skipping DROP FUNCTION postgres=# drop function if exists foo(int); NOTICE: function foo( ^^^ here psql is stopped.. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] large object regression tests
Jeremy Drake [EMAIL PROTECTED] writes: I just tried using the \lo_import command in a regression test, and I think I figured out why this will not work: ... Yes, that's the large object OID in the output there, and it is different each run (as I expect). Right. I'd suggest temporarily setting ECHO off to hide the unpredictable part of the output. There are similar measures taken in many of the contrib tests. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Notes: Major Changes in 8.2
On Fri, Sep 22, 2006 at 12:59:36PM -0700, Joe Conway wrote: Andrew Sullivan wrote: On Thu, Sep 21, 2006 at 03:05:36PM -0500, Jim C. Nasby wrote: Regardless, I think we should include a section of major new projects/developments from pgFoundry, because they ultimately make PostgreSQL a more useful database. Maybe this list should only be in the I like that. New enhancement products or something? enhancement products makes me think if Encyte and the like... :P Maybe add-ons would be better? In that case, what about things on gborg too? I just updated PL/R for 8.2 compatibility (and finally changed the status from alpha to beta). BTW, I'm happy to move PL/R over to pgFoundry, but became a little concerned about doing that after seeing the lengthy thread regarding pgFoundry concerns (but admittedly, I didn't have time to read the thread in detail, because I'm back over in Germany on a long business trip again). I didn't mention gforge since it'd depricated, but I don't see an issue with listing any add-on projects, no matter where they're hosted. For example, didn't pgAdmin just add support for Slony? That's something worth mentioning. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] DROP FUNCTION IF EXISTS
Teodor Sigaev wrote: postgres=# drop type if exists foo; NOTICE: type foo does not exist, skipping DROP TYPE postgres=# drop table if exists foo; NOTICE: table foo does not exist, skipping DROP TABLE postgres=# drop function if exists foo(); NOTICE: function foo() does not exist ... skipping DROP FUNCTION postgres=# drop function if exists foo(int); NOTICE: function foo( ^^^ here psql is stopped.. Yes, this appears to be a bug, not related to encoding etc. Maybe we should be calling func_signature_string in generating this error. I will look at it when I get a chance, might be a few days. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Notes: Major Changes in 8.2
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: 25 September 2006 15:03 To: Joe Conway Cc: Andrew Sullivan; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Release Notes: Major Changes in 8.2 For example, didn't pgAdmin just add support for Slony? That's something worth mentioning. That was our last major release. You can see what will be in 1.6 at http://www.pgadmin.org/development/changelog.php Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DROP FUNCTION IF EXISTS
Andrew Dunstan [EMAIL PROTECTED] writes: Yes, this appears to be a bug, not related to encoding etc. Maybe we should be calling func_signature_string in generating this error. Can't, because you don't have an Oid array for the types. TypeNameToString is the correct thing --- I'm considering inventing TypeNameListToString in order to make generating the message simple. 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] ReadBuffer(P_NEW) versus valid buffers
On Sun, Sep 24, 2006 at 12:26:55AM -0400, Alvaro Herrera wrote: Joshua D. Drake wrote: Tom Lane wrote: I asked around inside Red Hat but haven't gotten any responses yet ... seeing that it's a rather old Suse kernel, I can understand that RH's kernel hackers might not be too excited about investigating. (Alan Cox, for one, has got other things to worry about this weekend: http://zeniv.linux.org.uk/%7etelsa/boom/ Uhmm... doh? Telsa got fired for buying IBM? You should be fired for that pun. :P -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP FUNCTION IF EXISTS
Found a problem: ereport(NOTICE, (errmsg(function %s(%s) does not exist ... skipping, NameListToString(functionName), NameListToString(argTypes; NameListToString() suppose as an argument List of Value nodes, but argTypes is a list of TypeName... Andrew Dunstan wrote: Teodor Sigaev wrote: postgres=# drop type if exists foo; NOTICE: type foo does not exist, skipping DROP TYPE postgres=# drop table if exists foo; NOTICE: table foo does not exist, skipping DROP TABLE postgres=# drop function if exists foo(); NOTICE: function foo() does not exist ... skipping DROP FUNCTION postgres=# drop function if exists foo(int); NOTICE: function foo( ^^^ here psql is stopped.. Yes, this appears to be a bug, not related to encoding etc. Maybe we should be calling func_signature_string in generating this error. I will look at it when I get a chance, might be a few days. cheers andrew -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] DROP FUNCTION IF EXISTS
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Yes, this appears to be a bug, not related to encoding etc. Maybe we should be calling func_signature_string in generating this error. Can't, because you don't have an Oid array for the types. TypeNameToString is the correct thing --- I'm considering inventing TypeNameListToString in order to make generating the message simple. Sounds like a plan. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Small docu mismatch
Markus Schaber [EMAIL PROTECTED] writes: http://developer.postgresql.org/pgdocs/postgres/release-8-2.html tells: Add pg_dump -X no-data-for-failed-tables option to suppress loading data if table creation failed (the table already exists) (Martin Pitt) However, http://developer.postgresql.org/pgdocs/postgres/app-pgdump.html seem not to mention this option. Thinko in the release notes: the option applies to pg_restore not pg_dump. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Notes: Major Changes in 8.2
On Mon, Sep 25, 2006 at 03:10:39PM +0100, Dave Page wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim C. Nasby For example, didn't pgAdmin just add support for Slony? That's something worth mentioning. That was our last major release. You can see what will be in 1.6 at http://www.pgadmin.org/development/changelog.php Could you clarify this a bit? As far as I can tell, it's not possible to set up slony initially with pgadmin 1.4.latest. Has this changed in 1.6? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Notes: Major Changes in 8.2
-Original Message- From: David Fetter [mailto:[EMAIL PROTECTED] Sent: 25 September 2006 16:57 To: Dave Page Cc: Jim C. Nasby; Joe Conway; Andrew Sullivan; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Release Notes: Major Changes in 8.2 On Mon, Sep 25, 2006 at 03:10:39PM +0100, Dave Page wrote: From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim C. Nasby For example, didn't pgAdmin just add support for Slony? That's something worth mentioning. That was our last major release. You can see what will be in 1.6 at http://www.pgadmin.org/development/changelog.php Could you clarify this a bit? As far as I can tell, it's not possible to set up slony initially with pgadmin 1.4.latest. Has this changed in 1.6? The only change to the Slony support in 1.6 is a minor update to allow it to initialise a Slony 1.2 cluster (the version number needs to be inserted into the slony1_funcs script now). The only parts of the initial setup that pgAdmin doesn't do are the installation of the Slony shared libraries, or the copying of the schema (actually, pgAdmin can do this - it just doesn't do it automagically. Just backup and restore the relevant bits of your schema on the slave nodes). All the Slony support in pgAdmin was written as part of a contract with a Japanese company (SKC) to port Slony to Windows - that work was finished almost a year ago. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Notes: Major Changes in 8.2
Folks, On Thu, Sep 21, 2006 at 03:05:36PM -0500, Jim C. Nasby wrote: Regardless, I think we should include a section of major new projects/developments from pgFoundry, because they ultimately make PostgreSQL a more useful database. Maybe this list should only be in the I like that. New enhancement products or something? A If you're following the release drafting in pgsql-advocacy, you'll see that we're planning on including a section about pgfoundry projects in the extended release on the web, or press kit. So far, I've listed pgPool, PL/Java and Full Disjunctions; I'm not sure what else to list. Suggestions welcome. --Josh Berkus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] -HEAD planner issue wrt hash_joins on dbt3 ?
[RESENT in a modified version since the original reply seems to have ben lost by the listserver which seems to happen sometimes lately] Tom Lane wrote: Matteo Beccati [EMAIL PROTECTED] writes: Tom Lane ha scritto: Matteo Beccati [EMAIL PROTECTED] writes: I cannot see anything bad by using something like that: if (histogram is large/representative enough) Well, the question is exactly what is large enough? I feel a bit uncomfortable about applying the idea to a histogram with only 10 entries (especially if we ignore two of 'em). With 100 or more, it sounds all right. What's the breakpoint? Yes, I think 100-200 could be a good breakpoint. I've committed this change with (for now) 100 as the minimum histogram size to use. Stefan, are you interested in retrying your benchmark? spent some time retesting that and I got the following results(this is the same box as before but with a much slower disk-setup and a newly initdb'd cluster): http://www.kaltenbrunner.cc/files/analyze_q9_beta1.txt all that is with 2GB of effective_cache_size(plan does not change with much smaller settings btw) and a statistic target of 1000 and the following parameters: default planner settings for the EXPLAIN and the first EXPLAIN ANALYZE - then the same query with disabled hash_joins, the next one is with disabled nest_loops and the fastest one is with both nest_loop and hash_joins disabled (all run in that order - so there are possible caching effects). in comparision to: http://www.kaltenbrunner.cc/files/analyze_q9.txt we nearly got a 7 figure speedup due to the latest changes(much better estimates at least) - however the mergejoin+sort only plan is still faster. the other troubling query is the following: query: http://www.kaltenbrunner.cc/files/7/power1/db/plans/power_query21.txt plans: http://www.kaltenbrunner.cc/files/analyze_q21_beta1.txt (default,default,enable_nestloop=off,enable_nestloop=off and enable_hashjoin=off) despite having not-too bad estimates for most of the key-points in the plan the actual runtime of the choosen plan is quite disappointing. Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Increase default effective_cache_size?
Andrew Dunstan [EMAIL PROTECTED] writes: Initdb does not currently make any attempt to discover the extent of physical or virtual memory, it simply tries to start postgres with certain shared_buffer settings, starting at 4000, and going down until we get a success. max_fsm_pages is now fixed proportionally with shared_buffers, and I guess we could do something similar with effective_cache_size, but since IIRC this doesn't involve shared memory I'm inclined to agree with Tom that it should just be fixed at some substantially higher level. Right, the default shared_buffers doesn't have much of anything to do with actual RAM size. If the user has altered it, then it might (or might not) ... but that doesn't help us for setting a default effective_cache_size. Barring objections, I'll change it to Josh Drake's suggestion of ~ 128Mb (versus current 8Mb). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Notes: Major Changes in 8.2
On Fri, Sep 22, 2006 at 12:59:36PM -0700, Joe Conway wrote: In that case, what about things on gborg too? Yes, same idea. I don't care where the project _lives_; the important thing is its integration with PostgreSQL (and its quality). A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bitmap index status
Hi Mark, Thanks for doing the test. I checked out the link you provided below. I am a little confused about the goal of these tests. Do you plan to test the overall performance of postgreSQL on handling TPC-H queries? Thanks, Jie On 9/22/06 3:45 PM, Mark Wong [EMAIL PROTECTED] wrote: Jie Zhang wrote: Hi Heikki and all, I just sent the latest bitmap index patch to the list. I am not sure if there is any size limit for this mailing list. If you have received my previous email, please let me know. Hi Jie, I know I said I was going to get testing on this months ago but I've been juggling between 3 systems due to disk failures and other hardware configuration issues. Anyways, I've take a baseline run of only the power test using a 1GB database with the patch 09-17 patch against a snapshot of pgsql from 2006-09-17: http://dbt.osdl.org/dbt/dbt3testing/results/dev8-007/2/ Do you think the 1GB scale factor will be sufficient for testing as it will certainly be faster? Do you think testing with just a power test will be sufficient for now? I really don't have a good reason why I didn't run a throughput test other than to save time. :) I also wanted to get your opinion again on which indexes we will want to try first. Thanks, Mark ---(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] Bitmap index status
Hi Jie, Yeah, basically gather as many stats as I can to accurately profile the overall system performance. I thought it would be appropriate to use a TPC-H based workload as one measuring stick to use for bitmap indexes. Mark Jie Zhang wrote: Hi Mark, Thanks for doing the test. I checked out the link you provided below. I am a little confused about the goal of these tests. Do you plan to test the overall performance of postgreSQL on handling TPC-H queries? Thanks, Jie On 9/22/06 3:45 PM, Mark Wong [EMAIL PROTECTED] wrote: Jie Zhang wrote: Hi Heikki and all, I just sent the latest bitmap index patch to the list. I am not sure if there is any size limit for this mailing list. If you have received my previous email, please let me know. Hi Jie, I know I said I was going to get testing on this months ago but I've been juggling between 3 systems due to disk failures and other hardware configuration issues. Anyways, I've take a baseline run of only the power test using a 1GB database with the patch 09-17 patch against a snapshot of pgsql from 2006-09-17: http://dbt.osdl.org/dbt/dbt3testing/results/dev8-007/2/ Do you think the 1GB scale factor will be sufficient for testing as it will certainly be faster? Do you think testing with just a power test will be sufficient for now? I really don't have a good reason why I didn't run a throughput test other than to save time. :) I also wanted to get your opinion again on which indexes we will want to try first. Thanks, Mark ---(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] Questions about guc units
On Mon, Sep 25, 2006 at 10:03:50AM +0200, Peter Eisentraut wrote: Am Montag, 25. September 2006 04:04 schrieb ITAGAKI Takahiro: #shared_buffers = 32000kB # min 128kB or max_connections*16kB #temp_buffers = 8000kB # min 800kB #effective_cache_size = 8000kB Are there any reasons to continue to use 1000-unit numbers? Megabyte-unit (32MB and 8MB) seems to be more friendly for users. It increases some amount of values (4000 vs. 4096), but there is little in it. The reason with the shared_buffers is that the detection code in initdb has 400kB as minimum value, and it would be pretty complicated to code the detection code to handle both kB and MB units. If someone wants to try it, though, please go ahead. What about 0.4MB? Granted, it's uglier than 400kB, but anyone running on a machine that can't handle at least 1MB is already in the pretty ugly realm... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Broken link in PG docs
On Mon, 2006-09-25 at 16:44 +0530, Gurjeet Singh wrote: At the end of the following page: http://www.postgresql.org/docs/8.0/static/indexes-partial.html there is a link [Generalized Partial Indexes] which is pointing to a missing link. I agree the link should be fixed, but I can't see another canonical location for the document online: linking to CiteSeer (which itself is generated from the mention in our online docs) is probably not wise. Can anyone find a good URL for this paper? -Neil ---(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] Two proposals of DBA helper functions
On Mon, Sep 25, 2006 at 09:29:28AM +0200, Jean-Paul Argudo wrote: Hi all, I would like to submit two ideas: 1/ In the daily tasks I have as (also) a standard DBA, I found usefull to view locks in a human point of view. I think the ~ '^pg_' part of the queries may be not so clean... Any ideas welcome :) Technically, you need to look and see what schema something is in. Take a look at how the newsysviews project on pgFoundry does it. 2/ Also, I like having a cache hit/miss ratio. This is why I add a function pg_stat_get_db_hit_miss_ratio(database oid) to pg_stat_database, in a bis view. Since I dont want to flood the mailing list with a possible useless thing, I put my sql/plpgsql code here: http://priam.dalibo.net/~jpa/propositions/ Please take a look at it and tell me if this could be usefull or not.. The general consensus seems to be that the things in pg_catalog should provide a minimalist view. The newsysviews project was created in the hope that we could get a more human-friendly version of that data put into core, but it's been neglected for a while now (for one thing, there was desire to make it look more like information_schema). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] pg_dump data in BKI format
I'm playing with catalog upgrade via BKI format. I enhanced the pg_dump of BKI output (not patch ready yet). I'm using it for some test now, but I think It should be useful for some one other, for example some application with embedded postgres should use own prepared BKI for database init (with small enhancement in bootstrap mode) and this option should help to prepare a BKI script from application template database. Let me know your meaning about integration this enhancement into the pg_dump. Thanks Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump data in BKI format
Zdenek Kotala [EMAIL PROTECTED] writes: I'm playing with catalog upgrade via BKI format. I enhanced the pg_dump of BKI output (not patch ready yet). I'm using it for some test now, but I think It should be useful for some one other, for example some application with embedded postgres should use own prepared BKI for database init (with small enhancement in bootstrap mode) and this option should help to prepare a BKI script from application template database. This seems utterly useless :-( The BKI file format was never designed to be general purpose --- as an example, I do not believe it can cope with quotes or newlines embedded in data values. So as a target for pg_dump it's quite unsatisfactory. Nor do I see a particularly good reason why add-ons would need to get into the catalogs at the bootstrap stage, rather than using more conventional, much easier-to-use SQL commands to add objects later during initdb. So let's improve BKI sounds like an exercise in time-wasting, also. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump data in BKI format
Zdenek Kotala wrote: I'm playing with catalog upgrade via BKI format. I enhanced the pg_dump of BKI output (not patch ready yet). I'm using it for some test now, but I think It should be useful for some one other, for example some application with embedded postgres should use own prepared BKI for database init (with small enhancement in bootstrap mode) and this option should help to prepare a BKI script from application template database. Let me know your meaning about integration this enhancement into the pg_dump. I am not clear how this is an enhancement. The BKI file is a purely internal bootstrap mechanism, and I do not see any good reason to make pg_dump have any knowledge of it at all. pg_dump is already horribly complex (of necessity, to some extent, because it has to embody knowledge of previous releases). Anything that increases the complexity is to be avoided. In any case, surely the whole point about upgrading would be to do it without bootstrapping a new location. cheers andrew ---(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] Bitmap index status
Mark, On 9/25/06 11:32 AM, Mark Wong [EMAIL PROTECTED] wrote: Yeah, basically gather as many stats as I can to accurately profile the overall system performance. I thought it would be appropriate to use a TPC-H based workload as one measuring stick to use for bitmap indexes. Note that the TPC-H queries don't follow the typical good use case for bitmap indexes. You'd like to see queries that use multiple AND and OR clauses, otherwise there may be no benefit. Also, DBT-3/TPC-H on Postgres right now does not benefit from indices overall. The planner has limitations WRT selectivity estimates and other limitations that cause it to choose index access poorly for the query workload. We have two new features coming (for 8.3) that fix this, but for now we find that indexes are a net loss, in some queries a huge loss. If you look at the whitepaper that Ayush Parashar published, he uses the TPC-H data with some targeted queries that showcase the best use-cases for bitmap index. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_dump data in BKI format
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: I'm playing with catalog upgrade via BKI format. I enhanced the pg_dump of BKI output (not patch ready yet). I'm using it for some test now, but I think It should be useful for some one other, for example some application with embedded postgres should use own prepared BKI for database init (with small enhancement in bootstrap mode) and this option should help to prepare a BKI script from application template database. This seems utterly useless :-( The BKI file format was never designed to be general purpose --- as an example, I do not believe it can cope with quotes or newlines embedded in data values. So as a target for pg_dump it's quite unsatisfactory. \n works fine for new line, but you have right that there should be more dangerous characters. Nor do I see a particularly good reason why add-ons would need to get into the catalogs at the bootstrap stage, rather than using more conventional, much easier-to-use SQL commands to add objects later during initdb. Yes, You have right, It is much better. So let's improve BKI sounds like an exercise in time-wasting, also. Yes, I agree with you. Thanks for the answer. Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Please to technical check of upcoming release
All, Here: http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. Thanks! -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Please to technical check of upcoming release
Josh Berkus josh@agliodbs.com writes: http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. Thursday? My, we're feeling optimistic about the length of the beta period, aren't we? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] -HEAD planner issue wrt hash_joins on dbt3 ?
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: http://www.kaltenbrunner.cc/files/analyze_q9_beta1.txt The next problem seems to be the drastic misestimation of this join size: - Nested Loop (cost=0.00..6872092.36 rows=135 width=28) (actual time=94.762..14429291.129 rows=3554044 loops=1) - Merge Join (cost=0.00..519542.74 rows=449804 width=16) (actual time=48.197..49636.006 rows=474008 loops=1) Merge Cond: (part.p_partkey = partsupp.ps_partkey) - Index Scan using pk_part on part (cost=0.00..105830.22 rows=112447 width=4) (actual time=34.646..14381.644 rows=118502 loops=1) Filter: ((p_name)::text ~~ '%ghost%'::text) - Index Scan using i_ps_partkey on partsupp (cost=0.00..388943.05 rows=8000278 width=12) (actual time=13.511..22659.364 rows=7999685 loops=1) - Index Scan using i_l_suppkey_partkey on lineitem (cost=0.00..14.11 rows=1 width=24) (actual time=4.415..30.310 rows=7 loops=474008) Index Cond: ((partsupp.ps_partkey = lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) With a factor-of-25000 error in that rowcount estimate, it's amazing the plans aren't worse than they are. It evidently thinks that most of the rows in the join of part and partsupp won't have any matching rows in lineitem, whereas on average there are about 7 matching rows apiece. So that's totally wacko, and it's not immediately obvious why. Could we see the pg_stats entries for part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey, lineitem.l_partkey, lineitem.l_suppkey? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Please to technical check of upcoming release
Tom, Thursday? My, we're feeling optimistic about the length of the beta period, aren't we? It takes me a minumum of 2 weeks (preferably 3) to deal with the translations. If we stay on schedule, I'll be *just* ready for a 1-month beta. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Broken link in PG docs
Neil Conway [EMAIL PROTECTED] writes: I agree the link should be fixed, but I can't see another canonical location for the document online: linking to CiteSeer (which itself is generated from the mention in our online docs) is probably not wise. citeseer's cache still has the paper, and in it I find the authors' email addresses ... could try writing to them ... 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] Windows build farm failures
Michael Meskes [EMAIL PROTECTED] writes: This double stuff creates so many headaches that I wonder if we better not test it at all in the regression suite. Comments? If you're not prepared to support alternative expected files (as the main regression tests do), then I think you have little choice. Floating-point behavior is just too variable across platforms. However, I wonder whether you shouldn't just bite the bullet and put in support for alternative expected files. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Questions about guc units
Jim C. Nasby wrote: The reason with the shared_buffers is that the detection code in initdb has 400kB as minimum value, and it would be pretty complicated to code the detection code to handle both kB and MB units. If someone wants to try it, though, please go ahead. What about 0.4MB? That isn't valid code, so I don't know how that helps. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about guc units
On Sep 25, 2006, at 1:03 AM, Peter Eisentraut wrote: Am Montag, 25. September 2006 04:04 schrieb ITAGAKI Takahiro: #shared_buffers = 32000kB # min 128kB or max_connections*16kB #temp_buffers = 8000kB # min 800kB #effective_cache_size = 8000kB Are there any reasons to continue to use 1000-unit numbers? Megabyte-unit (32MB and 8MB) seems to be more friendly for users. It increases some amount of values (4000 vs. 4096), but there is little in it. The reason with the shared_buffers is that the detection code in initdb has 400kB as minimum value, and it would be pretty complicated to code the detection code to handle both kB and MB units. If someone wants to try it, though, please go ahead. Seems like the unit used for shared_buffers (and others) should be megabytes then with a minimum of 1 (or more). Is less than 1MB granularity really useful here? On modern hardware 1MB of RAM is in the noise. -Casey ---(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] Questions about guc units
Peter Eisentraut [EMAIL PROTECTED] wrote: #max_fsm_pages = 160# min max_fsm_relations*16, 6 bytes each max_fsm_pages doesn't have a discernible unit Yes, max_fsm_*pages* doesn't have a unit, but can we treat the value as the amount of trackable database size by fsm or estimated database size ? (the latter is a bit too radical interpretation, though.) So I think it is not so odd to give a unit to max_fsm_pages. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
