Re: [HACKERS] Re: Which qsort is used
On Thu, Dec 22, 2005 at 01:43:34AM +0100, Manfred Koizar wrote: > Qsorting N elements costs O(N*lnN), so excluding H elements from the > sort reduces the cost by at least O(H*lnN). The merge step costs O(N) > plus some (<=50%) more memory, unless someone knows a fast in-place > merge. So depending on the constant factors involved there might be a > usable solution. But where are you including the cost to check how many cells are already sorted? That would be O(H), right? This is where we come back to the issue that comparisons in PostgreSQL are expensive. The cpu_cost in the tests I saw so far is unrealistically low. > I've been playing with some numbers and assuming the constant factors > to be equal for all the O()'s this method starts to pay off at > H for N > 20 100 20% >130 1000 13% > 800010 8% Hmm, what are the chances you have 10 unordered items to sort and that the first 8% will already be in order. ISTM that that probability will be close enough to zero to not matter... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpg6RoCjt5SA.pgp Description: PGP signature
Re: [HACKERS] to_char and i18n
Gavin Sherry <[EMAIL PROTECTED]> writes: > There's some functionality in 10g which PostgreSQL does not have: Good to know. I'm not an Oracle expert, actually I knew this reading an article in a past issue of the Oracle's magazine about i18n; essentially they were talking about how easy was for an Oracle database to get i18n as each parameter in the to_char functions will behave accordingly to the i18n settings. Regards, Manuel. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] to_char and i18n
On Wed, 21 Dec 2005, Tom Lane wrote: > Manuel Sugawara writes: > > (Some time ago I proposed an--incomplete--patch and it was rejectd by > > Karel arguing that to_char functions should behave *exactly* the same > > way that they do in Oracle.) > > That is the accepted plan for to_char ... of course, if Oracle changes > to_char every so often, it'll get more interesting to decide what to do. There's some functionality in 10g which PostgreSQL does not have: TZD - returns the short timezone string with daylight saving information, eg: PDT TZM - timezone offset minutes part TZH - timezone offset hours part TZR - timezone region (US/Pacific, for example) RR/ - accept 'rounded' years, eg 99-1-1 = 1999-1-1 (kind of pointless) FF - specify how many digits to the right of the decimal place to display, when looking at factions of seconds. Eg: HH:MM:SS.FF3 would produce 15:56:22.123 X - the local radix character. Eg: HH:MM:SSXFF would produce 15:56:22.123 E - Era name (like, Japanese Imperial) (kind of pointless) EE - Full era name DS - Locale formatted short date. For example, DD/MM/ for the Brits, MM/DD/ for the Yanks DL - Locale formatted long date. Eg: fmDay, dd. Month in Germany SCC - Like 'CC', but will carry a - (minus) for BC dates (I'm not sure if this implies that Oracle wants BC dates to be marked 'BC'. I don't have an Oracle system around at the moment to check though :-() TS - Locale formatted short time. YEAR - Year in words SYEAR - Year in words, prefixed by minus sign for BC dates S - , prefixed by minus sign for BC dates Gavin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char and i18n
Tom Lane <[EMAIL PROTECTED]> writes: > Can we spell the names differently but keep to the same field widths? > I can see where it might cause problems to change the widths --- other > than that, no objection. Quite impossible. But if someone is relaying in the current behavior of to_char she might set lc_time accordingly as this parameter is not used in the code, AFAIK Regards, Manuel. ---(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] to_char and i18n
Manuel Sugawara writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> I thought to_char already had i18n behavior. What exactly are you >> thinking of changing? > The modifiers that are suitable to localize. Month and day names comes > to mind and maybe others, I'm not sure what the state of the code is, > but I can say that, at least, the 'month' and 'day' modifiers does not > behave in a localized way. Can we spell the names differently but keep to the same field widths? I can see where it might cause problems to change the widths --- other than that, no objection. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] to_char and i18n
Qingqing Zhou wrote: > > "Manuel Sugawara" wrote > > > > i18n == Internationalization (maybe I should say l10n == > > localization). > > Good hint, I got it :-) Just like a crossword puzzle. 18 means there are 18 > characters between 'i' and 'n' ... Huh? I don't understand. -- 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] to_char and i18n
"Manuel Sugawara" wrote > > i18n == Internationalization (maybe I should say l10n == > localization). Good hint, I got it :-) Just like a crossword puzzle. 18 means there are 18 characters between 'i' and 'n' ... Regards, Qingqing ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] to_char and i18n
Manuel Sugawara writes: > (Some time ago I proposed an--incomplete--patch and it was rejectd by > Karel arguing that to_char functions should behave *exactly* the same > way that they do in Oracle.) That is the accepted plan for to_char ... of course, if Oracle changes to_char every so often, it'll get more interesting to decide what to do. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] to_char and i18n
Tom Lane <[EMAIL PROTECTED]> writes: > I thought to_char already had i18n behavior. What exactly are you > thinking of changing? The modifiers that are suitable to localize. Month and day names comes to mind and maybe others, I'm not sure what the state of the code is, but I can say that, at least, the 'month' and 'day' modifiers does not behave in a localized way. Regards, Manuel. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] to_char and i18n
"Qingqing Zhou" <[EMAIL PROTECTED]> writes: > Can you give a small introduction of i18n and what's your plan in > PostgreSQL? i18n == Internationalization (maybe I should say l10n == localization). This means that to_char functions might lead to different results depending on the i18n settings. For instance, nowadays, select to_char(now(), 'dd-mon-yy') returns 21-dec-05 regardless of the i18n settings. This should lead 21-dic-05 in the es_MX localization. This also applies to the concurrency symbol, thousand separator, etc. (Some time ago I proposed an--incomplete--patch and it was rejectd by Karel arguing that to_char functions should behave *exactly* the same way that they do in Oracle.) Regards, Manuel. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] to_char and i18n
Manuel Sugawara writes: > Now that Oracle supports i18n dependant behavior in its to_char > formatting functions (at least for its 10g release) I was wondering if > a patch to support this in PostgreSQL will get accepted. I thought to_char already had i18n behavior. What exactly are you thinking of changing? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Automatic function replanning
Jim C. Nasby wrote: > On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote: > > Rick Gigger wrote: > > > It seems to me like there are two classes of problems here: > > > > > > 1) Simply invalidating plans made with out of date statistics. > > > 2) Using run-time collected data to update the plan to something more > > > intelligent. > > > > > > It also seems like #1 would be fairly straightforward and simple > > > whereas #2 would be much more complex. #1 would do me a world of > > > good and probably other people as well. Postgres's query planning > > > has always been fine for me, or at least I have always been able to > > > optimize my queries when I've got a representative data set to work > > > with. Query plan caching only gets me when the query plan is created > > > before the statistics are present to create a good plan. > > > > > > Just one users 2 cents. > > > > Agreed. I just can't add #2 unless we get more agreement from the > > group, because it has been a disputed issue in the past. > > Well, how about this, since it's a prerequisit for #2 and would be > generally useful anyway: > > Track normal resource consumption (ie: tuples read) for planned queries > and record parameter values that result in drastically different > resource consumption. > > This would at least make it easy for admins to identify prepared queries > that have a highly variable execution cost. We have that TODO already: * Log statements where the optimizer row estimates were dramatically different from the number of rows actually found? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] to_char and i18n
"Manuel Sugawara" wrote > Now that Oracle supports i18n dependant behavior in its to_char > formatting functions (at least for its 10g release) I was wondering if > a patch to support this in PostgreSQL will get accepted. I was hoping > to work on this now that I have some spare time. > Can you give a small introduction of i18n and what's your plan in PostgreSQL? Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] catalog corruption bug
Jeremy Drake <[EMAIL PROTECTED]> writes: > We have encountered a very nasty but apparently rare bug which appears to > result in catalog corruption. How much of this can you reproduce on 8.1.1? We've fixed a few issues already. > This was built from the gentoo ebuild version 8.1.0 I'd be even more interested if you can reproduce it on a non-gentoo machine. Gentoo is not noted for stability. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Better path-matching for package relocatability (was Re:
Tom Lane wrote: > Well, more generally what we need is a better match algorithm in > make_relative_path. After a few moment's thought I propose: > > * Determine the common prefix of the compiled-in target_path and > bin_path (for typical cases this would be "/usr" or "/usr/local"; > worst case is that the common prefix is just "/"). Call everything > to the right of the common prefix the "tail" of these paths. The > currently expected scenario is that the tails are "share" and "bin", > but there might be more than one directory level in them. > > * Try to match the tail of the bin_path to the end of the actual binary > location (my_exec_path without the executable's name). > > * If match, take everything to the left of the match in my_exec_path, > and append the tail of target_path to produce the result. > > * If no match, use target_path as-is, same as now. > > I think this would get right all of the cases the current code gets > right, and more generally would work when we need to substitute N > levels of directory names instead of just one. It may still be a > few bricks shy of a load, however. Any thoughts? Sounds fine. When I did the original code, I was very conservative about where I would look in the fear I might hit something strange. Now that we have used this code in product with little problem, having it be more aggressive seems logical. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] to_char and i18n
Now that Oracle supports i18n dependant behavior in its to_char formatting functions (at least for its 10g release) I was wondering if a patch to support this in PostgreSQL will get accepted. I was hoping to work on this now that I have some spare time. Regards, Manuel. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
Martijn van Oosterhout wrote: -- Start of PGP signed section. > On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote: > > If you add a version number to the Win32 libpq name, you have to update > > any command-line compile tools that mention libpq after an upgrade. The > > Unix linker knows about version numbers, but the Win32 linker doesn't, > > so adding version numbers does add quite a bit of chaos to the Win32 > > compile world. > > The funny thing about it is that the UNIX linker doesn't know about > version numbers at all. It just looks for a lib.so (no > version) which is symlinked to the actual version to use. Thus just by > changing a few symlinks you can control which library version is linked > in. Delete the .so and the linker won't find the library anymore (and > fall back to the .a lib) though runtime users still will find it > because they *do* have the version number, which is extracted from the > library itself. Yes, important distinction. Thanks for the clarification. -- 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
[HACKERS] catalog corruption bug
We have encountered a very nasty but apparently rare bug which appears to result in catalog corruption. I have not been able to pin down an exact sequence of events which cause this problem, it appears to be a race condition of some sort. This is what I have been able to figure out so far. * It appears to be related to temp table creation/deletion. * It requires at least 2 clients be connected simultaneously. * It seems to be related to the autovacuum (not sure, just a theory). I will attempt to explain the circumstances leading up to the problem, and then show the symptoms. We are working on a project which uses postgresql to store data which has been decomposed into a large number of rows in stored procedures (plpgsql/plperl). The implementation we have been working with recently has used temp tables to store intermediate stages of this decomposition so that we can run multiple queries over it in the course of adding it to our final tables without having to regenerate the set each time. We were running a timing test for a load of data which would result in tens of millions of rows. This load creates temp tables with "on commit drop" and also explitly drops them. It appears to do so at a rate of approximately 10 per second (also transactions are being created/committed at that same rate). This works fine. While this load was running we were working on some testing code to determine whether it might be better to create the temp table with "on commit delete rows" instead and use a plpgsql function to create the temp table with an EXCEPTION duplicate_table block to handle when the table has already been created for this connection. We wrote the function at first on a postgres 8.0 box which was not running the load, but when we were attempting to determine what the error code thrown was we noticed that the SQLSTATE variable was not available in 8.0 and copied the function onto the 8.1 box (which was running the load) to try it out. We ran this function a couple times to get the error, and then had it catch the duplicate_table exception. We got the function working, and when we looked at the status of our load we found that it had died with a message saying "ERROR: pg_class entry for relid 7502381 vanished during vacuuming" We found this interesting, figuring it was a bug in postgres. Googling the non-variable pieces of that message turned up nothing relevant, so we set about trying to reproduce it. During the course of doing so, we restarted our load several times and called the function. We later put the calling of the function into a loop in bash calling psql (so we could disconnect/reconnect) to speed up the finding of the problem. These are some of the interesting errors which we got while doing this (all from the server log): ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" CONTEXT: SQL statement "CREATE TEMP TABLE foo (a integer, b integer) ON COMMIT DELETE ROWS" PL/pgSQL function "temp_table_test" line 2 at SQL statement ERROR: relation "foo" does not exist ERROR: duplicate key violates unique constraint "pg_class_relname_nsp_index" CONTEXT: SQL statement "CREATE TEMP TABLE foo (a integer, b integer) ON COMMIT DELETE ROWS" PL/pgSQL function "temp_table_test" line 2 at SQL statement ERROR: relation "foo" does not exist FATAL: cache lookup failed for relation 7600066 LOG: server process (PID 20942) exited with exit code 1 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. We also managed to get an error which was more bothersome than the mysterious disappearing/reappearing temp tables. ERROR: relation "windowpos" does not exist ERROR: type "windowpos" already exists ERROR: cache lookup failed for relation 794218 Later: ERROR: relation "windowpos" already exists ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 ERROR: catalog is missing 14 attribute(s) for relid 7577269 Here is the temp table function we were testing: CREATE OR REPLACE FUNCTION temp_table_test() RETURNS boolean AS $$ BEGIN CREATE TEMP TABLE foo (a integer, b integer) ON COMMIT DELETE ROWS; RETURN true; EXCEPTION WHEN duplicate_table THEN RETURN false; END; $$ LANGUAGE plpgsql; And our bash command l
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... ---(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] Improving planning of outer joins
I'm not sure whether we'd need any additional planner knobs to control this. I think that the existing join_collapse_limit GUC variable should continue to exist, but its effect on left/right joins will be the same as for inner joins. If anyone wants to force join order for outer joins more than for inner joins, we'd need some other control setting, but I don't currently see why that would be very useful. Does this seem like a reasonable agenda, or am I thinking too small? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] Automatic function replanning
On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote: > Rick Gigger wrote: > > It seems to me like there are two classes of problems here: > > > > 1) Simply invalidating plans made with out of date statistics. > > 2) Using run-time collected data to update the plan to something more > > intelligent. > > > > It also seems like #1 would be fairly straightforward and simple > > whereas #2 would be much more complex. #1 would do me a world of > > good and probably other people as well. Postgres's query planning > > has always been fine for me, or at least I have always been able to > > optimize my queries when I've got a representative data set to work > > with. Query plan caching only gets me when the query plan is created > > before the statistics are present to create a good plan. > > > > Just one users 2 cents. > > Agreed. I just can't add #2 unless we get more agreement from the > group, because it has been a disputed issue in the past. Well, how about this, since it's a prerequisit for #2 and would be generally useful anyway: Track normal resource consumption (ie: tuples read) for planned queries and record parameter values that result in drastically different resource consumption. This would at least make it easy for admins to identify prepared queries that have a highly variable execution cost. -- 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] Re: Which qsort is used
On Sat, 17 Dec 2005 00:03:25 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: >I've still got a problem with these checks; I think they are a net >waste of cycles on average. [...] > and when they fail, those cycles are entirely wasted; >you have not advanced the state of the sort at all. How can we make the initial check "adavance the state of the sort"? One answer might be to exclude the sorted sequence at the start of the array from the qsort, and merge the two sorted lists as the final stage of the sort. Qsorting N elements costs O(N*lnN), so excluding H elements from the sort reduces the cost by at least O(H*lnN). The merge step costs O(N) plus some (<=50%) more memory, unless someone knows a fast in-place merge. So depending on the constant factors involved there might be a usable solution. I've been playing with some numbers and assuming the constant factors to be equal for all the O()'s this method starts to pay off at H for N 20 100 130 1000 800010 Servus Manfred ---(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] Unsplitting btree index leaf pages
Simon Riggs <[EMAIL PROTECTED]> writes: > While we scan, if we found two adjacent pages, both of which have less > than (say) 40% rows, we could re-join or "unsplit" those pages together. Curiously enough, this has been thought of before. It is not as easy as you think, or it would have been done the first time around. nbtree/README explains why: : We consider deleting an entire page from the btree only when it's become : completely empty of items. (Merging partly-full pages would allow better : space reuse, but it seems impractical to move existing data items left or : right to make this happen --- a scan moving in the opposite direction : might miss the items if so. We could do it during VACUUM FULL, though.) 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] Unsplitting btree index leaf pages
When we discussed online REINDEX recently we focused on the REINDEX command itself rather than look at alternative approaches. One reason to REINDEX is because of index page splits getting things out of sequence and generally bloating the index. When we VACUUM, each index is scanned in logical order. While we scan, if we found two adjacent pages, both of which have less than (say) 40% rows, we could re-join or "unsplit" those pages together. The index blocks are fully locked during the read anyway and there is no MVCC problem with moving index rows between blocks. All we have to do is to lock both blocks, having locked them in the correct order. The rows would always be moved to the lowest physical block id, so that data would naturally migrate towards the start of the index file. Blocks would then be marked half-dead just as if they had just had their last index row removed by the vacuum. We could start the scan by locking block 1 AND block2, then scan forward always holding 2 locks as we go. That way we would not need to unlock and relock the blocks to lock two blocks. The concurrency loss would not be that great, but we would gain the ability to unsplit the two blocks into one. If we do this, we would could possibly avoid the need to full REINDEX entirely. If this method checks out we could do one of: - make VACUUM do this always - add an option for REINDEX: CLEAN/COMPRESS/VACUUM etc to do this upon command only Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] problem with nasty latin2 sorting
Anna Domachowska <[EMAIL PROTECTED]> writes: > I've got a problem with sorting polish words in postgresql 7.4.2 > ... > I belive that postgres was compiled with "--enable-locale", and the > right locale is set. How sure are you of that? This sure sounds like a wrong-locale problem to me. Try "show lc_collate" and "show server_encoding". 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] [pgadmin-hackers] Client-side password encryption
On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote: > If you add a version number to the Win32 libpq name, you have to update > any command-line compile tools that mention libpq after an upgrade. The > Unix linker knows about version numbers, but the Win32 linker doesn't, > so adding version numbers does add quite a bit of chaos to the Win32 > compile world. The funny thing about it is that the UNIX linker doesn't know about version numbers at all. It just looks for a lib.so (no version) which is symlinked to the actual version to use. Thus just by changing a few symlinks you can control which library version is linked in. Delete the .so and the linker won't find the library anymore (and fall back to the .a lib) though runtime users still will find it because they *do* have the version number, which is extracted from the library itself. I'm often impressed by the way UNIX is highly configurable yet trivially transparant at the same time. The chances that anything remotely similar would work on windws seems slim at best. -- 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. pgpJVFX2JqvDG.pgp Description: PGP signature
Re: [HACKERS] status of concurrent VACUUM patch ...
Ühel kenal päeval, K, 2005-12-21 kell 09:50, kirjutas Hannu Krosing: > Ühel kenal päeval, T, 2005-12-20 kell 17:18, kirjutas Tom Lane: > > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > > > i was just wondering about the status of hannu's concurrent vacuum patch. > > > are there any plans to integrate this > > > > I still don't trust it (assuming that you're thinking of the same patch > > I am). > > What could I do to increase your trust in it ? > > Could you think of any specific case it could break ? Or any specific > tests to check for it ? > > I attach the version of the patch for 8.1.0 for anyone interested in > checking it. It was pointed out to me that I did'nt. So here it is: --- Hannu Index: src/backend/access/transam/twophase.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/twophase.c,v retrieving revision 1.16 diff -c -r1.16 twophase.c *** src/backend/access/transam/twophase.c 29 Oct 2005 00:31:50 - 1.16 --- src/backend/access/transam/twophase.c 21 Dec 2005 23:09:31 - *** *** 279,284 --- 279,286 gxact->proc.pid = 0; gxact->proc.databaseId = databaseid; gxact->proc.roleId = owner; + gxact->proc.inVacuum = false; + gxact->proc.nonInVacuumXmin = InvalidTransactionId; gxact->proc.lwWaiting = false; gxact->proc.lwExclusive = false; gxact->proc.lwWaitLink = NULL; Index: src/backend/access/transam/xact.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v retrieving revision 1.215 diff -c -r1.215 xact.c *** src/backend/access/transam/xact.c 15 Oct 2005 02:49:09 - 1.215 --- src/backend/access/transam/xact.c 21 Dec 2005 23:09:32 - *** *** 1507,1512 --- 1507,1514 LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); MyProc->xid = InvalidTransactionId; MyProc->xmin = InvalidTransactionId; + MyProc->inVacuum = false; /* must be cleared with xid/xmin */ + MyProc->nonInVacuumXmin = InvalidTransactionId; /* this too */ /* Clear the subtransaction-XID cache too while holding the lock */ MyProc->subxids.nxids = 0; *** *** 1740,1745 --- 1742,1749 LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); MyProc->xid = InvalidTransactionId; MyProc->xmin = InvalidTransactionId; + MyProc->inVacuum = false; /* must be cleared with xid/xmin */ + MyProc->nonInVacuumXmin = InvalidTransactionId; /* this too */ /* Clear the subtransaction-XID cache too while holding the lock */ MyProc->subxids.nxids = 0; *** *** 1902,1907 --- 1906,1913 LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE); MyProc->xid = InvalidTransactionId; MyProc->xmin = InvalidTransactionId; + MyProc->inVacuum = false; /* must be cleared with xid/xmin */ + MyProc->nonInVacuumXmin = InvalidTransactionId; /* this too */ /* Clear the subtransaction-XID cache too while holding the lock */ MyProc->subxids.nxids = 0; Index: src/backend/access/transam/xlog.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.222 diff -c -r1.222 xlog.c *** src/backend/access/transam/xlog.c 29 Oct 2005 00:31:50 - 1.222 --- src/backend/access/transam/xlog.c 21 Dec 2005 23:09:34 - *** *** 5294,5300 * StartupSUBTRANS hasn't been called yet. */ if (!InRecovery) ! TruncateSUBTRANS(GetOldestXmin(true)); if (!shutdown) ereport(DEBUG2, --- 5294,5300 * StartupSUBTRANS hasn't been called yet. */ if (!InRecovery) ! TruncateSUBTRANS(GetOldestXmin(true, false)); if (!shutdown) ereport(DEBUG2, Index: src/backend/catalog/index.c === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/index.c,v retrieving revision 1.261 diff -c -r1.261 index.c *** src/backend/catalog/index.c 15 Oct 2005 02:49:12 - 1.261 --- src/backend/catalog/index.c 21 Dec 2005 23:09:34 - *** *** 1427,1433 else { snapshot = SnapshotAny; ! OldestXmin = GetOldestXmin(heapRelation->rd_rel->relisshared); } scan = heap_beginscan(heapRelation, /* relation */ --- 1427,1434 else { snapshot = SnapshotAny; ! /* okay to ignore lazy VACUUMs here */ ! OldestXmin = GetOldestXmin(heapRelation->rd_rel->relisshared, true); } scan = heap_beginscan(heapRelation, /* relation */ Index: src/backend/commands/vacuum.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v retrieving revision 1.317 diff -c -r1.317 vacuum.c *** src/backend/commands/vacuum.c 15 Oct 2005 02:49:16 - 1.317 --- src/backend/commands/vacuum.c 21 Dec 2005 23:09:35 - *** *** 36,41 --- 36,
Re: [HACKERS] Automatic function replanning
Rick Gigger wrote: > It seems to me like there are two classes of problems here: > > 1) Simply invalidating plans made with out of date statistics. > 2) Using run-time collected data to update the plan to something more > intelligent. > > It also seems like #1 would be fairly straightforward and simple > whereas #2 would be much more complex. #1 would do me a world of > good and probably other people as well. Postgres's query planning > has always been fine for me, or at least I have always been able to > optimize my queries when I've got a representative data set to work > with. Query plan caching only gets me when the query plan is created > before the statistics are present to create a good plan. > > Just one users 2 cents. Agreed. I just can't add #2 unless we get more agreement from the group, because it has been a disputed issue in the past. --- > > - Rick Gigger > > > On Dec 19, 2005, at 12:00 PM, Jim C. Nasby wrote: > > > On Sat, Dec 17, 2005 at 01:07:10PM -0500, Bruce Momjian wrote: > >> Jim C. Nasby wrote: > >>> Is cardinality the only thing we'd need to worry about? My idea was > >>> actually to track the amount of work normally required by a > >>> stored query > >>> plan, and if a query uses that plan but requires a very different > >>> amount > >>> of work it's a good indication that we either need to replan or > >>> store > >>> multiple plans for that query. Though if we're certain that > >>> cardinality > >>> is the only thing that could make a cached plan go bad it would > >>> certainly simplify things greatly. > >> > >> This gets into another area of re-optimizing when the executor finds > >> that the actual tables don't match the optimizer estimates. I > >> think we > >> decided that was too hard/risky, but invalidating the plan might > >> help, > >> though I am thinking re-planning might just generate the same plan as > >> before. I think something would need to have happened since the last > >> planning, like ANALYZE or something. > > > > Well, in the stored plan case, presumably what's changed is one of the > > bound parameters. And if we want to be sophisticated about it, we > > won't > > just throw out the old plan; rather we'll try and figure out what > > parameter it is that's wanting a different plan. > > > >> Updated TODO: > >> > >>* Flush cached query plans when the dependent objects change, > >> when the cardinality of parameters changes dramatically, or > >> when new ANALYZE statistics are available > > > > Probably worth pointing to this therad in the TODO... > > -- > > 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 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 5: don't forget to increase your free space map settings
Re: [HACKERS] problem with nasty latin2 sorting
Anna Domachowska <[EMAIL PROTECTED]> writes: > So "¯arnów" is placed in wrong place, because it should be found after letter > "Z" ?? > > What's wrong? Is there any patch to fix this? Does anybody had similar > problem? > I belive that postgres was compiled with "--enable-locale", and the right > locale is set. What does "show lc_collate" say? What does \l show in psql? what do you get on your machine if you save that same list in a file and run "sort" on it on the commandline set (with LC_COLLATE set to the same value as above)? -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] replicating tsearch2 across versions of postgres
Thanks, that might be easier than first thought. Dave On 21-Dec-05, at 2:04 PM, Tom Lane wrote: Dave Cramer <[EMAIL PROTECTED]> writes: Is it possible to add the old signatures back for backward compatibility ? Something like a tsearch2-compat lib ? The old signatures were security holes. We are not going to put them back. I would suggest changing the functions on the 7.4 machine to the new signatures --- see the 7.4.8 release notes. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] where is the output
On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp@pyrenet.fr wrote: > Not sure it's the right group, but I've spent the afternoon googling and > trying on this. > > In PHP (Apache Module) pgsql-php might be more appropriate, or possibly a PHP mailing list. > I try pg_exec("COPY blah TO STDOUT WITH blah"); > It runs for ever... > > How can I get the output of COPY in PHP? At the libpq layer you'd use PQgetCopyData(); the only place I see a call to that function in the PHP 5.1.1 source code is in the pg_copy_to() function, which is for copying a table into an array. > Copy_from is not an option because the goal is to get a CSV file. > Writing and re-reading a file doesn't seem to be an option either because > of safe_mode. Could you use pg_copy_to() to read the table into an array and write each row of the array as CSV? I'm not sure if the following is the best way or even entirely correct, but it appears to work in PHP 5.1.1, even with safe_mode enabled: $rows = pg_copy_to($conn, "tablename"); $fh = fopen("php://output", "w"); foreach ($rows as $row) { fputcsv($fh, explode("\t", rtrim($row))); } fclose($fh); According to the PHP documentation fputcsv() is new as of 5.1.0RC1; for earlier versions you could probably find a module to generate CSV output. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] problem with nasty latin2 sorting
Hello, I've got a problem with sorting polish words in postgresql 7.4.2 For example, I've got couple of polish cities in my table 'x', results of sorting are: (select city from x order by city asc) Bydgoszcz Gdańsk Iława Łódź Żarnów Malbork Warszawa So "Żarnów" is placed in wrong place, because it should be found after letter "Z" ?? What's wrong? Is there any patch to fix this? Does anybody had similar problem? I belive that postgres was compiled with "--enable-locale", and the right locale is set. Anna Domachowska ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
On 12/21/05, Bruce Momjian wrote: > Andreas Pflug wrote: > > Martijn van Oosterhout wrote: > > > > > > > > So it's only an issue if you have a policy of removing old versions of > > > libpq on upgrades... I'm not sure what's "best practice" on windows in > > > this area. > > > > When removing the application (in this case: pgsql), you'd remove that > > old lib as well if it's the only app using it. If you have another > > application installed, the deinstaller should observe this, and keep the > > version. > > > > > > I'm voting +1 for lib name versions. > > If you add a version number to the Win32 libpq name, you have to update > any command-line compile tools that mention libpq after an upgrade. The > Unix linker knows about version numbers, but the Win32 linker doesn't, > so adding version numbers does add quite a bit of chaos to the Win32 > compile world. > win32 compile world *is* a chaos... it's very frustating when you try to run a program and it fails because a library (when you actually has the library, at least _a_ version of the library)... IMHO, adding version numbers to the name of library for windows is a the cleanest thing you can do... > -- > 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 > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(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] [pgadmin-hackers] Client-side password encryption
Andreas Pflug wrote: > Martijn van Oosterhout wrote: > > > > > So it's only an issue if you have a policy of removing old versions of > > libpq on upgrades... I'm not sure what's "best practice" on windows in > > this area. > > When removing the application (in this case: pgsql), you'd remove that > old lib as well if it's the only app using it. If you have another > application installed, the deinstaller should observe this, and keep the > version. > > > I'm voting +1 for lib name versions. If you add a version number to the Win32 libpq name, you have to update any command-line compile tools that mention libpq after an upgrade. The Unix linker knows about version numbers, but the Win32 linker doesn't, so adding version numbers does add quite a bit of chaos to the Win32 compile world. -- 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] Function call with offset and limit
Have you tried SELECT *, test_func(idkeyword) FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1) ; ? This should probably have been on -general, btw. On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote: > Hi all, > > We need to find a solution for a strange problem. > We have a plpgsql FUNCTION which performs an heavy job (named > test_func). > > CREATE or replace function test_func(z int) returns integer as $$ > declare > tst integer; > begin > -- > -- Large jobs with z > -- > tst := nextval('test_truc'); > return tst; > end; > $$ LANGUAGE plpgsql; > > > So I made this test: > > test=# select setval('test_truc',1); > setval > > 1 > (1 row) > > test=# select currval('test_truc') ; > currval > - >1 > (1 row) > > test=# select *,test_func(idkeyword) from tag offset 5000 limit 1; > idkeyword | test_func > ---+- > 5001 | 5002 > (1 row) > > test=# select currval('test_truc') ; > currval > - > 5002 > (1 row) > > > This demonstrates that the function is called 5001 times though only one > row is returned. Problem is that this heavy job is performed much, much > more than needed. > > But, If I do: > test=# select *,(select test_func(1)) from tag offset 5000 limit 1; > My function is called only once. > > Is there any work around ? > > > Thanks > -- > REYNAUD Jean-Samuel <[EMAIL PROTECTED]> > Elma > > > ---(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 > -- 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] Automatic function replanning
Trent Shipley wrote: > On Saturday 2005-12-17 16:28, Lukas Smith wrote: > > Bruce Momjian wrote: > > > * Flush cached query plans when the dependent objects change, > > > when the cardinality of parameters changes dramatically, or > > > when new ANALYZE statistics are available > > > > Wouldn't it also make sense to flush a cached query plan when after > > execution it is determined that one or more assumptions that the cached > > query plan was based on was found to be off? Like the query plan was > > based on the assumption that a particular table would only return a hand > > full of rows, but in reality it returned a few thousand. > > > > regards, > > Lukas > > > > Proposed rewrite > > * Mark query plan for flush (opportunistic replan) when: > ** dependent objects change, > ** cardinality of parameters changes sufficiently (per planner > parameter) > ** when new ANALYZE statistics are available and per planner parameter > differ > sufficiently from prior statistics. Frankly, I think any new ANALYZE statistics should just invalidate the plan. I don't think it is worth trying to determine if they changed sufficiently or not --- you might as we just replan. > * Mark plan as "tried" when parameters of returned set out of statistical > control, create alternate plan hill-climbing to statical control. > ** Too many/too few rows relative to plan expectations > *** Auto-sample for better statistics? > ** History of plan shows throughput time for result set varies > excessively > (need more execution stability, possibly at expense of median optimality). This is a new idea, that you are remembering bad plans. I am unsure how we would track that information. It gets into the area of having the optimizer change its behavior based on previous runs, and I am not sure we have ever agreed to get into that kind of 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 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] replicating tsearch2 across versions of postgres
Dave Cramer <[EMAIL PROTECTED]> writes: > Is it possible to add the old signatures back for backward > compatibility ? Something like a tsearch2-compat lib ? The old signatures were security holes. We are not going to put them back. I would suggest changing the functions on the 7.4 machine to the new signatures --- see the 7.4.8 release notes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] where is the output
On Wed, Dec 21, 2005 at 07:16:28PM +0100, ohp@pyrenet.fr wrote: > In PHP (Apache Module) > > I try pg_exec("COPY blah TO STDOUT WITH blah"); > It runs for ever... > > How can I get the output of COPY in PHP? You need to use the API functions for copy. In C they are: PQgetCopyData PQputCopyData PQputCopyEnd I'm sure PHP has these functions, or perhaps the older versions: PQgetline PQputline PQendcopy Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpGxacZJby5Y.pgp Description: PGP signature
[HACKERS] where is the output
hi all, Not sure it's the right group, but I've spent the afternoon googling and trying on this. In PHP (Apache Module) I try pg_exec("COPY blah TO STDOUT WITH blah"); It runs for ever... How can I get the output of COPY in PHP? Copy_from is not an option because the goal is to get a CSV file. Writing and re-reading a file doesn't seem to be an option either because of safe_mode. Any php gurus out there? Regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(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
[HACKERS] replicating tsearch2 across versions of postgres
I am getting the following error(s) when replicating tsearch2 from v 7.4.x to v 8.1.0remoteWorkerThread_1: copy from stdin on local node - PGRES_FATAL_ERROR ERROR: function "spell_init(text)" does not [EMAIL PROTECTED] CONTEXT: COPY pg_ts_dict, line 1, column dict_init: "spell_init(text)"the problem is that the function signatures have changedspell_init(text) is now spell_init(internal) so the function can't be found on the destination node.My solution is to not replicate the internal tsearch2 tables. Is this going to be problematic ? Is it possible to add the old signatures back for backward compatibility ? Something like a tsearch2-compat lib ?Dave
[HACKERS] Function call with offset and limit
Hi all, We need to find a solution for a strange problem. We have a plpgsql FUNCTION which performs an heavy job (named test_func). CREATE or replace function test_func(z int) returns integer as $$ declare tst integer; begin -- -- Large jobs with z -- tst := nextval('test_truc'); return tst; end; $$ LANGUAGE plpgsql; So I made this test: test=# select setval('test_truc',1); setval 1 (1 row) test=# select currval('test_truc') ; currval - 1 (1 row) test=# select *,test_func(idkeyword) from tag offset 5000 limit 1; idkeyword | test_func ---+- 5001 | 5002 (1 row) test=# select currval('test_truc') ; currval - 5002 (1 row) This demonstrates that the function is called 5001 times though only one row is returned. Problem is that this heavy job is performed much, much more than needed. But, If I do: test=# select *,(select test_func(1)) from tag offset 5000 limit 1; My function is called only once. Is there any work around ? Thanks -- REYNAUD Jean-Samuel <[EMAIL PROTECTED]> Elma ---(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] pgxs/windows
... seems to be behaving oddly: dllwrap -o rainbow.dll --def rainbow.def rainbow.o c:/PROGRA~1/POSTGR~1/8.1/lib/pgxs/src/MAKEFI~1/../../src/utils/dllinit.o -Lc:/PROGRA~1/POSTGR~1/8.1/bin -lpostgres c:\mingw\bin\..\lib\gcc-lib\mingw32\3.2.3\..\..\..\..\mingw32\bin\ld.exe: cannot find -lpostgres should we be pointing to the bin directory or the lib directory? ISTR we've seen something like this before. any thoughts? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] localization problem (and solution)
Tom Lane said: > "Andrew Dunstan" <[EMAIL PROTECTED]> writes: >> We need to test any solution carefully on Windows, which deals with >> locales very differently from *nix, and where we still have some known >> locale issues > > Right, of course. I was thinking that this change might actually bring > the Windows and Unix code closer together --- at least for LC_MESSAGES > it seems it would do so. > > If I prepare a patch, do you want to test it on Windows before it goes > in, or is it easier just to commit and then test CVS tip? > Can't do anything for cvs tip until the md5 mess is fixed. I don't have much time to spare for testing till at least next week - maybe someone else does. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] localization problem (and solution)
"Andrew Dunstan" <[EMAIL PROTECTED]> writes: > We need to test any solution carefully on Windows, which deals with locales > very differently from *nix, and where we still have some known locale issues Right, of course. I was thinking that this change might actually bring the Windows and Unix code closer together --- at least for LC_MESSAGES it seems it would do so. If I prepare a patch, do you want to test it on Windows before it goes in, or is it easier just to commit and then test CVS tip? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Better path-matching for package relocatability (was Re: [BUGS] horology regression test failure)
Martin Pitt <[EMAIL PROTECTED]> writes: > Tom Lane [2005-12-20 16:39 -0500]: >> We could doubtless improve make_relative_path to some extent, but the >> mess you have above seems impossible to deal with. How is a mere >> program supposed to deduce where things were moved to, given only >> knowledge of the actual location of --bindir? I see little if any >> pattern that would allow prediction of the corresponding --datadir, >> let alone --libexecdir or --includedir ... > Right, with make_relative_path's current approach that seems to be > impossible. However, in a test suite I had expected a semantics like > $DESTDIR, i. e. instead of mangling the path somewhere in the middle, > the test suite should just prepend the tmp_check path. Well, more generally what we need is a better match algorithm in make_relative_path. After a few moment's thought I propose: * Determine the common prefix of the compiled-in target_path and bin_path (for typical cases this would be "/usr" or "/usr/local"; worst case is that the common prefix is just "/"). Call everything to the right of the common prefix the "tail" of these paths. The currently expected scenario is that the tails are "share" and "bin", but there might be more than one directory level in them. * Try to match the tail of the bin_path to the end of the actual binary location (my_exec_path without the executable's name). * If match, take everything to the left of the match in my_exec_path, and append the tail of target_path to produce the result. * If no match, use target_path as-is, same as now. I think this would get right all of the cases the current code gets right, and more generally would work when we need to substitute N levels of directory names instead of just one. It may still be a few bricks shy of a load, however. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] localization problem (and solution)
Tom Lane said: > Andreas Seltenreich <[EMAIL PROTECTED]> writes: >> I'm afraid having LC_ALL in the environment at this time would still >> do the wrong thing on setlocale(LC_ALL, ""); since a LC_ALL >> environment variable overrides the other categories. > > Doh, of course, I was misremembering the precedence. So we need > LANG=C > LC_ALL unset (probably LANGUAGE too, for glibc) > others as stated > We need to test any solution carefully on Windows, which deals with locales very differently from *nix, and where we still have some known locale issues (see recent discussion). I wonder if the complained of behaviour is triggered by our recent changes to support utf8 in pl/perl? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] localization problem (and solution)
Andreas Seltenreich <[EMAIL PROTECTED]> writes: > I'm afraid having LC_ALL in the environment at this time would still > do the wrong thing on setlocale(LC_ALL, ""); since a LC_ALL > environment variable overrides the other categories. Doh, of course, I was misremembering the precedence. So we need LANG=C LC_ALL unset (probably LANGUAGE too, for glibc) others as stated regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Windows Installer Bug (Probably)
I am writing to you all just to inform you of a bug(probably), that I came across while trying to install PostgreSQL 8.1.0-2 and 8.1.1-1 on WinXP with SP2, using the PostgreSQL installer. I had done a windows clean install and then installed straight away PostgreSQL 8.0.4. It was the only software installed on windows and worked perfectly. After a day or two, I noticed that the PostgreSQL 8.1.0-2 was released. I decided two uninstall PostgreSQL 8.0.4 and install the new 8.1.0-2 version. So I did, using the following unistall procedure: 1. I unistalled PostgreSQL 8.0.4 form the control panel in the usual manner that all windows application are uninstalled. 2. I used the Computer Management program of the Administrative Tools of windows and deleted the PostgreSQL user that had been created during the installation of PostgreSQL 8.0.4. 3. Finally, I removed all garbage left by the uninstaller in the registry. 4. I restarted windows. I have to stress that I did not have any other software installed on windows at the moment and I had the Windows firewall service disabled. So I tried to install the new version 8.1.0-2 but got an error. I tried at least 15 times and I always got the same error. The error was manifesting itself during the installation as follows: During the "Activating procedural languages" installation part, I get: "Failed to connect to the database. Procedural languages files are installed, but are not activated in any databases." When I press OK it continues and I get: "server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request." When I press OK again, it continues and I get: "Failed to connect to the 'template1' database. Contrib files are installed, but are not activated in any databases." Pressing OK for a final time gives me this: "could not send startup packet: Connection reset by peer (0x2746/10054)" ** I uninstalled the 8.1.0-2 PostgreSQL which was not working and I tried to install again the 8.0.4, as some people in the novice and Admin mailing lists suggested. The 8.0.4 installer worked fine and I had PostgreSQL 8.0.4 up and running in no time. Then I uninstalled 8.0.4 and tried to install 8.1.0-2 for a last time. The same errors appeared once again. * If that is not a bizzare behaviour of the installer, what is? * The same problem appeared when some people I know tried to install PostgreSQL 8.1.0-2 as well. But they did not have the time nor the inclination to make this known to the public by posting to the mailing lists. I have no feedback of the same people trying the 8.1.1-1 version. I believe they have not tried it yet. Now that PostgreSQL 8.1.1-1 is out, I decided to test my luck again. I repeated the whole procedure and I got exactly the same erros that manifested when trying to install 8.1.0-2. I uninstalled and I tried to install 8.0.4. That went ok again and I did not have any problems. All the problems appear whenever I try to install any version after the 8.0.4. As someone suggested, these errors indicate a proper install where the server crashes after install. That is exactly what happens. Another strange thing is that when the first error prompt appears, if I look for any log files, I can not find any. Maybe that is because that error appears after the installation of the core engine. I dont know. But! That bizzare behaviour does not manifest itself when using the installers of versions of PostgreSQL prior to 8.1.x. For all versions of PostgreSQL that I have successfully installed so far (8.0.4 and all others prior to that), I used the folder "C:\Program Files\Postgresql\" for the PostgrerSQL files and the folder "D:\DBASES\" for the database files. I tried the default folders as well but it did not make any difference. At any unistallation I delete the folder D:\DBASES manually. All other folders are deleted automatically by the unistaller. Can anybody please help? It is very annoying not to be able to use the DB. http://www.freemail.gr - äùñåÜí õðçñåóßá çëåêôñïíéêïý ôá÷õäñïìåßïõ. http://www.freemail.gr - free email service for the Greek-speaking. ---(end of broadcast)--- TIP 6: explain analyze is your friend