Re: [HACKERS] Netflix Prize data
Mark Woodward wrote: I tried to cluster the data along a particular index but had to cancel it after 3 hours. If the data is in random order, it's faster to do SELECT * INTO foo_sorted FROM foo ORDER BY bar then CREATE INDEX, than to run CLUSTER. That's because CLUSTER does a full index scan of the table, which is slower than a seqscan + sort if the table is not already clustered. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] workaround for buggy strtod is not necessary
Bruce Momjian napsal(a): OK, great information, updated comment is: /* * Many versions of Solaris have broken strtod() --- see bug #4751182. * This has been fixed in current versions of Solaris: * * http://sunsolve.sun.com/search/document.do?assetkey=1-21-108993-62-1searchclause=108993-62 * http://sunsolve.sun.com/search/document.do?assetkey=1-21-112874-34-1searchclause=112874-34 * * However, many people might not have patched versions, so * still use our own fix for the buggy version. */ Excellent, thanks Bruce Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] PL Caching in TopTransactionContext
Hi, I've been planning to implement a caching mechanism for non-volatile (and non-SRF) procedures in PL/scheme, that'll return pre-computed values in case of a procedure signature overlap[1]. First, I decided to use fcinfo-flinfo-fn_mcxt context for the storage. But then I learnt it'll survive as long as FmgrInfo will do, which means that (I think) it can only be useful for subsequent calls in the current query context. (Like when returning from a SRF.) Right? (An explanation for the possible use cases of this memory context will be appreciated.) And after reading backend/utils/mmgr/README, I think TopTransactionContext is the way for me to go. Actually, I can use CurTransactionContext too, but why should I make a redundant computation of a non-volatile procedure that might be probably computed before in the TopTransactionContext. (Yeah, same is true for TopMemoryContext too, but IMHO that's not something a PL handler should mess with.) OTOH, this[TopTransactionContext] context is NOT cleared immediately upon error; its contents will survive until the transaction block is exited by COMMIT/ROLLBACK. note in the README makes me consider again using CurTransactionContext instead. I wanted to consult this decision to the developers. Is TopTransactionContext the way for me to go? Should I prefer considering a totally different implementation? And one last question: After a successful establishment of this mechanism in PL/scheme, I can try to port it to PL/pgSQL. Will developers welcome such a patch? Regards. ---(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.2 beta blockers
Lukas Kahwe Smith wrote: Peter Eisentraut wrote: Am Montag, 18. September 2006 09:20 schrieb Lukas Kahwe Smith: This just reminds me, are there plans to take into account multibyte server encodings inside the client quote function? Huh? Ah, I just checked the libpq docs and there seems to be a PQescapeStringConn. Not sure when this was added, I think PHP does not yet use it. I will investigate this and will make sure its used in favor of the deprecated old PQescapeString function. This will be fixed as of PHP 5.2.0 regards, Lukas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PL/pgSQL Todo, better information in errcontext from plpgsql
Hi, Tom, Tom Lane wrote: I miss some info in context: function's oid, function's argumenst and schema. Maybe: 199292 function public.foo(int, int, int) language plpgsql statement return line 10 Putting the OID there is a seriously awful idea, not least because it would make it impossible to have stable regression-test outputs. I'm not really convinced that we need more than the function name. I also think that the OID will hurt here, but schema and argument types may be useful in some corner cases. And what two variants of errcontext drived by GUC? First current (compatible) and second enhanced (with oid, params, maybe all possible debug values) and possible machine's readable. This enhanced variant can be compatible and shared in all environments. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgindent has been run
On Wed, Oct 04, 2006 at 04:41:44PM -0400, Bruce Momjian wrote: That will prevent it from being changed by pgindent in the future. Thanks. 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 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] PL Caching in TopTransactionContext
[EMAIL PROTECTED] writes: I've been planning to implement a caching mechanism for non-volatile (and non-SRF) procedures in PL/scheme, that'll return pre-computed values in case of a procedure signature overlap[1]. This has generally been considered a bad idea even if implemented for all function types. It seems even less sane to implement it on a retail PL-by-PL basis. And after reading backend/utils/mmgr/README, I think TopTransactionContext is the way for me to go. You realize that that goes away at the end of each transaction? 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] PL/pgSQL Todo, better information in errcontext from plpgsql
Pavel Stehule [EMAIL PROTECTED] writes: And what two variants of errcontext drived by GUC? First current (compatible) and second enhanced (with oid, params, maybe all possible debug values) and possible machine's readable. This enhanced variant can be compatible and shared in all environments. [ shrug... ] The complaints I've heard about the errcontext mechanism are that it's too verbose already. I can't see a good use-case for the above, and I do know that a lot of people wouldn't consider it an enhancement at all. I suspect the problems you wish to solve would be better addressed by using the plpgsql debugger that will be available with 8.2. It sounds to me like you are wishing for a debugger stack trace, and if you need one of those you probably need other debugger facilities too. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL Todo, better information in errcontext from plpgsql
Pavel Stehule [EMAIL PROTECTED] writes: And what two variants of errcontext drived by GUC? First current (compatible) and second enhanced (with oid, params, maybe all possible debug values) and possible machine's readable. This enhanced variant can be compatible and shared in all environments. [ shrug... ] The complaints I've heard about the errcontext mechanism are that it's too verbose already. I can't see a good use-case for the above, and I do know that a lot of people wouldn't consider it an enhancement at all. I suspect the problems you wish to solve would be better addressed by using the plpgsql debugger that will be available with 8.2. It sounds to me like you are wishing for a debugger stack trace, and if you need one of those you probably need other debugger facilities too. plpgsql debugger's plugin can be solution. But it's big gun for me and has little bit overhead. We have really large plpgsql library, where we very often use overloading of functions, and I finding way for usefull error handling, and I need collect all possible information about stack track. Current format of stack track isn't really usefull. Only name, line and statement are less. With func oid I can get all other info later, without it, I need estimate which functions are in stack track. By example, for me aren't important language, in 99% is plpgsql. I wouldn't use debugger in production datatabase. Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Query Failed, out of memory
I am using the netflix database: Table public.ratings Column | Type | Modifiers +--+--- item | integer | client | integer | day| smallint | rating | smallint | The query was executed as: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. The postgresql log file complained about a broken pipe. Question, is this a bug in psql? It took over 4 hours of run time before the crash. dmesg: Free pages: 13192kB (112kB HighMem) Active:124664 inactive:124330 dirty:0 writeback:0 unstable:0 free:3298 slab:2188 mapped:248080 pagetables:1939 DMA free:12160kB min:16kB low:32kB high:48kB active:0kB inactive:0kB present:16384kB pages_scanned:12602 all_unreclaimable? yes protections[]: 0 0 0 Normal free:920kB min:928kB low:1856kB high:2784kB active:438608kB inactive:437656kB present:901120kB pages_scanned:978318 all_unreclaimable? yes protections[]: 0 0 0 HighMem free:112kB min:128kB low:256kB high:384kB active:60176kB inactive:59536kB present:131008kB pages_scanned:134673 all_unreclaimable? yes protections[]: 0 0 0 DMA: 6*4kB 3*8kB 3*16kB 3*32kB 3*64kB 2*128kB 1*256kB 0*512kB 1*1024kB 1*2048kB 2*4096kB = 12160kB Normal: 0*4kB 1*8kB 7*16kB 1*32kB 0*64kB 0*128kB 1*256kB 1*512kB 0*1024kB 0*2048kB 0*4096kB = 920kB HighMem: 0*4kB 0*8kB 1*16kB 1*32kB 1*64kB 0*128kB 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 112kB Swap cache: add 548633, delete 548633, find 11883/13748, race 0+0 0 bounce buffer pages Free swap:0kB 262128 pages of RAM 32752 pages of HIGHMEM 3593 reserved pages 608 pages shared 0 pages swap cached Out of Memory: Killed process 9143 (psql). ---(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] Query Failed, out of memory
On Thu, Oct 05, 2006 at 11:56:43AM -0400, Mark Woodward wrote: The query was executed as: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. The postgresql log file complained about a broken pipe. Question, is this a bug in psql? It took over 4 hours of run time before the crash. Well, psql tried to store the entire resultset in memory at once, and failed. I'm not sure how many records you were trying to display, but try to estimate how much memory that would take to store... What were you trying to do? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Query Failed, out of memory
On Oct 5, 2006, at 11:56 , Mark Woodward wrote: I am using the netflix database: Table public.ratings Column | Type | Modifiers +--+--- item | integer | client | integer | day| smallint | rating | smallint | The query was executed as: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. The postgresql log file complained about a broken pipe. Question, is this a bug in psql? It took over 4 hours of run time before the crash. The OOM killer kills a process based on some heuristics but it is just a guess. For reliable behavior, configure Linux to 1) disable the OOM killer 2) stop overcommitting memory. Then, you should be able to get the results you require. -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query Failed, out of memory
Mark Woodward [EMAIL PROTECTED] writes: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. Not too surprising. Question, is this a bug in psql? It's really a libpq design issue: since libpq provides random access to a PGresult, and has no mechanism for handling failures after returning the PGresult to the client, it has to slurp the whole query result into memory first. FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query Failed, out of memory
Mark Woodward [EMAIL PROTECTED] writes: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. Not too surprising. Question, is this a bug in psql? It's really a libpq design issue: since libpq provides random access to a PGresult, and has no mechanism for handling failures after returning the PGresult to the client, it has to slurp the whole query result into memory first. FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. That's sort of what I was afraid off. I am trying to get 100 million records into a text file in a specific order. Sigh, I have to write a quick program to use a cursor. :-( ---(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] Query Failed, out of memory
On Thu, Oct 05, 2006 at 11:56:43AM -0400, Mark Woodward wrote: The query was executed as: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. The postgresql log file complained about a broken pipe. Question, is this a bug in psql? It took over 4 hours of run time before the crash. Well, psql tried to store the entire resultset in memory at once, and failed. I'm not sure how many records you were trying to display, but try to estimate how much memory that would take to store... What were you trying to do? It's the stupid NetFlix prize thing, I need to dump out the data in a specific order. This is just *one* such query I want to try. I guess, like I told Tom, I have to write a small program that uses a cursor. :-( ---(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] Query Failed, out of memory
FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. That's sort of what I was afraid off. I am trying to get 100 million records into a text file in a specific order. Sigh, I have to write a quick program to use a cursor. :-( Why don't you try the psql client from 8.2beta1 then? This way you don't have to write the program yourself and you're helping out with beta testing as well :-) See FETCH_COUNT in http://developer.postgresql.org/pgdocs/postgres/app-psql.html Bye, Chris. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Query Failed, out of memory
Tom Lane wrote: Mark Woodward [EMAIL PROTECTED] writes: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. but COPY doesn't guarantee any order. BTW, I just this morning discovered the hard way that our linux boxes didn't have strict memory allocation turned on, and then went and set it. I'd advise Mark to do the same, if he hasn't already. 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
[HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Graham Davis [EMAIL PROTECTED] writes:
The documentation for to_char states that:
|to_char(interval)| formats HH and HH12 as hours in a single day, while
HH24 can output hours exceeding a single day, e.g. 24.
However I can not get it to work with time intervals that span more than
1 day.
Well, it does in fact print intervals exceeding 24 hours:
regression=# select to_char('48 hours'::interval, 'HH24:MI:SS');
to_char
--
48:00:00
(1 row)
However, '48 hours' and '2 days' are not the same thing. The problem
with the case you give is really that timestamp_mi applies justify_hours
to its result --- that is,
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to the
subtraction result for themselves. Not sure what the fallout would be,
though.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Query Failed, out of memory
FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. That's sort of what I was afraid off. I am trying to get 100 million records into a text file in a specific order. Sigh, I have to write a quick program to use a cursor. :-( Why don't you try the psql client from 8.2beta1 then? This way you don't have to write the program yourself and you're helping out with beta testing as well :-) See FETCH_COUNT in http://developer.postgresql.org/pgdocs/postgres/app-psql.html Well, maybe next time, it only took about 10 minutes to write. It is a simple program. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Query Failed, out of memory
Create table as select ... Order by ... Copy to ... - Luke Msg is shrt cuz m on ma treo -Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 12:51 PM Eastern Standard Time To: Tom Lane Cc: Mark Woodward; pgsql-hackers@postgresql.org Subject:Re: [HACKERS] Query Failed, out of memory Tom Lane wrote: Mark Woodward [EMAIL PROTECTED] writes: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. but COPY doesn't guarantee any order. BTW, I just this morning discovered the hard way that our linux boxes didn't have strict memory allocation turned on, and then went and set it. I'd advise Mark to do the same, if he hasn't already. 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query Failed, out of memory
Tom Lane wrote: Mark Woodward [EMAIL PROTECTED] writes: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt FWIW, there's a feature in CVS HEAD to instruct psql to try to use a cursor to break up huge query results like this. For the moment I'd suggest using COPY instead. but COPY doesn't guarantee any order. BTW, I just this morning discovered the hard way that our linux boxes didn't have strict memory allocation turned on, and then went and set it. I'd advise Mark to do the same, if he hasn't already. Yea, I've been toying with the idea of that setting lately, I can't for the life of me understand why it isn't the default behavior. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Win XP SP2 SMP locking (8.1.4)
Hi there, I'm looking into strange locking, which happens on WinXP SP2 SMP machine running 8.1.4 with stats_row_level=on. This is the only combination (# of cpu and stats_row_level) which has problem - SMP + stats_row_level. The same test runs fine with one cpu (restarted machine with /numproc=1) disregarding to stats_row_level option. Customer's application loads data into database and sometimes process stopped, no cpu, no io activity. PgAdmin shows current query is 'COMMIT'. I tried to attach gdb to postgres and client processes, but backtrace looks useless (see below). Running vacuum analyze of this database in separate process cause loading process to continue ! Weird. It's interesting, that there is no problem with 8.2beta1 in all combinations ! Any idea what changes from 8.1.4 to 8.2beta1 could affect the problem ? postgres.exe: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x019effd0 in ?? () #6 0xf784e548 in ?? () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x19f application: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x0196ffd0 in ?? () #6 0x7c97c0d8 in ntdll!NtAccessCheckByTypeResultListAndAuditAlarm () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x197 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Win XP SP2 SMP locking (8.1.4)
It's interesting, that there is no problem with 8.2beta1 in all combinations ! Any idea what changes from 8.1.4 to 8.2beta1 could affect the problem ? What do you mean locking? Do you mean the postgresql process locks up? E.g; can you still connect to PostgreSQL from another connection? If not is there an error? Joshua D. Drake postgres.exe: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x019effd0 in ?? () #6 0xf784e548 in ?? () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x19f application: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x0196ffd0 in ?? () #6 0x7c97c0d8 in ntdll!NtAccessCheckByTypeResultListAndAuditAlarm () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x197 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win XP SP2 SMP locking (8.1.4)
On Thu, 5 Oct 2006, Joshua D. Drake wrote: It's interesting, that there is no problem with 8.2beta1 in all combinations ! Any idea what changes from 8.1.4 to 8.2beta1 could affect the problem ? What do you mean locking? Do you mean the postgresql process locks up? E.g; can you still connect to PostgreSQL from another connection? If not is there an error? It looks like application is waiting something from postgresql, but postgresql thinks it did the job. vacuum analyze gets things moving. I could connect to PostgreSQL from another connection, for example pgAdmin still works with this database. Joshua D. Drake postgres.exe: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x019effd0 in ?? () #6 0xf784e548 in ?? () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x19f application: (gdb) bt #0 0x7c901231 in ntdll!DbgUiConnectToDbg () from C:\WINDOWS\system32\ntdll.dll #1 0x7c9507a8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #2 0x0005 in ?? () #3 0x0004 in ?? () #4 0x0001 in ?? () #5 0x0196ffd0 in ?? () #6 0x7c97c0d8 in ntdll!NtAccessCheckByTypeResultListAndAuditAlarm () #7 0x in ?? () #8 0x7c90ee18 in strchr () from C:\WINDOWS\system32\ntdll.dll #9 0x7c9507c8 in ntdll!KiIntSystemCall () from C:\WINDOWS\system32\ntdll.dll #10 0x in ?? () from #11 0x in ?? () from #12 0x in ?? () from #13 0x in ?? () from (gdb) Cannot access memory at address 0x197 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Win XP SP2 SMP locking (8.1.4)
Hi there, I'm looking into strange locking, which happens on WinXP SP2 SMP machine running 8.1.4 with stats_row_level=on. This is the only combination (# of cpu and stats_row_level) which has problem - SMP + stats_row_level. The same test runs fine with one cpu (restarted machine with /numproc=1) disregarding to stats_row_level option. Customer's application loads data into database and sometimes process stopped, no cpu, no io activity. PgAdmin shows current query is 'COMMIT'. I tried to attach gdb to postgres and client processes, but backtrace looks useless (see below). Running vacuum analyze of this database in separate process cause loading process to continue ! Weird. It's interesting, that there is no problem with 8.2beta1 in all combinations ! Any idea what changes from 8.1.4 to 8.2beta1 could affect the problem ? There is a new implementations of semaphores in 8.2. That could possibly be it. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Query Failed, out of memory
MW == Mark Woodward [EMAIL PROTECTED] writes: MW Yea, I've been toying with the idea of that setting lately, I MW can't for the life of me understand why it isn't the default MW behavior. Lots of programs handle malloc() failures very badly. Including daemons. Often it's better in practice to just keep going a little longer and see if you can squeeze by -- and then perhaps kill the memory hog, rather than some daemon getting a NULL and crashing. /Benny ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Query Failed, out of memory
On Thu, 2006-10-05 at 12:52 -0400, Luke Lonergan wrote: Create table as select ... Order by ... Copy to ... Or in 8.2, COPY TO (SELECT ... ORDER BY) (My, that's a neat feature.) -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Query Failed, out of memory
:-D Is that in the release notes? - Luke Msg is shrt cuz m on ma treo -Original Message- From: Neil Conway [mailto:[EMAIL PROTECTED] Sent: Thursday, October 05, 2006 02:35 PM Eastern Standard Time To: Luke Lonergan Cc: Andrew Dunstan; Tom Lane; Mark Woodward; pgsql-hackers@postgresql.org Subject:Re: [HACKERS] Query Failed, out of memory On Thu, 2006-10-05 at 12:52 -0400, Luke Lonergan wrote: Create table as select ... Order by ... Copy to ... Or in 8.2, COPY TO (SELECT ... ORDER BY) (My, that's a neat feature.) -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Query Failed, out of memory
On Thu, 2006-10-05 at 14:53 -0400, Luke Lonergan wrote: Is that in the release notes? Yes: Allow COPY to dump a SELECT query (Zoltan Boszormenyi, Karel Zak) -Neil ---(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] Storing MemoryContext Pointers
Hi, When I allocate a new memory context via oldmcxt = AllocSetContextCreate(TopMemoryContext, ...) persistent_mcxt = CurrentMemoryContext; How can I store the persistent_mcxt in a persistent place that I'll be able to reach it in my next getting invoked? Is that possible? If not, how can I reach my previously created persistent data in my next invocation? Regards. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query Failed, out of memory
On Thu, 2006-10-05 at 14:53 -0400, Luke Lonergan wrote: Is that in the release notes? Yes: Allow COPY to dump a SELECT query (Zoltan Boszormenyi, Karel Zak) I remember this discussion, it is cool when great features get added. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Storing MemoryContext Pointers
Volkan YAZICI [EMAIL PROTECTED] writes: When I allocate a new memory context via oldmcxt = AllocSetContextCreate(TopMemoryContext, ...) persistent_mcxt = CurrentMemoryContext; ITYM persistent_mcxt = AllocSetContextCreate(TopMemoryContext, ...) because the other doesn't do what you think... How can I store the persistent_mcxt in a persistent place that I'll be able to reach it in my next getting invoked? Make it a static variable. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Upgrading a database dump/restore
Not to cause any arguments, but this is sort a standard discussion that gets brought up periodically and I was wondering if there has been any softening of the attitudes against an in place upgrade, or movement to not having to dump and restore for upgrades. I am aware that this is a difficult problem and I understand that if there is a radical restructuring of the database then a dump/restore is justified, but wouldn't it be a laudable goal to *not* require this with each new release? Can't we use some release as a standard who's binary format shall not be changed. I know the arguments about predicting the future, and all, but standards and stability are important too. I'm not saying it should never ever change or never ever require a dump/restore, but make it, as policy, difficult to get past the group and the norm not to require d/r. The issue is that as disks get bigger and bigger, databases get bigger and bigger, and this process becomes more and more onerous. If you haven't noticed, data transmission speeds are not accelerating at the rate disk space is growing. I am currently building a project that will have a huge number of records, 1/2tb of data. I can't see how I would ever be able to upgrade PostgreSQL on this system. ---(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] Upgrading a database dump/restore
Mark Woodward wrote: I am currently building a project that will have a huge number of records, 1/2tb of data. I can't see how I would ever be able to upgrade PostgreSQL on this system. Slony will help you upgrade (and downgrade, for that matter) with no downtime at all, pretty much. Of course, you do need double the resources You other suggestion of setting the on disk format in high viscosity jello, if not in concrete, seems doomed to failure. Cool features that you and other people want occasionally rely on format changes. Of course, you don't have to upgrade every release. Many people (including me) don't. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] continuing daily testing of dbt2 against postgresql
Hi everyone, After over a year of problems (old site http://developer.osdl.org/markw/postgrescvs/) I have resumed producing daily results of dbt-2 against PostgreSQL CVS code with results here: http://dbt.osdl.org/dbt2.html The only really new thing is better described stats on the i/o activity per tablespace. I'm generating iostat plots for devices per tablespace. I'm going to track results at two scale factors, one where the system is not quite overloaded and one where the system is overloaded. I'll have dbt3 results shortly, I hope. Let me know if there are any questions. Regards, Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upgrading a database dump/restore
Mark Woodward wrote: I am currently building a project that will have a huge number of records, 1/2tb of data. I can't see how I would ever be able to upgrade PostgreSQL on this system. Slony will help you upgrade (and downgrade, for that matter) with no downtime at all, pretty much. Of course, you do need double the resources You other suggestion of setting the on disk format in high viscosity jello, if not in concrete, seems doomed to failure. Cool features that you and other people want occasionally rely on format changes. I disagree with the all or nothing attitude, I'm generally a pragmatist. It is unreasonable to expect that things will never change, by the same token, never attempting to standardize or enforce some level of stability is equally unreasonable. From an enterprise DB perspective, a d/r of a database is a HUGE process and one that isn't taken lightly. I just think that an amount of restraint in this area would pay off well. Of course, you don't have to upgrade every release. Many people (including me) don't. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upgrading a database dump/restore
On Oct 5, 2006, at 15:46 , Mark Woodward wrote: Not to cause any arguments, but this is sort a standard discussion that gets brought up periodically and I was wondering if there has been any softening of the attitudes against an in place upgrade, or movement to not having to dump and restore for upgrades. I am aware that this is a difficult problem and I understand that if there is a radical restructuring of the database then a dump/restore is justified, but wouldn't it be a laudable goal to *not* require this with each new release? Can't we use some release as a standard who's binary format shall not be changed. I know the arguments about predicting the future, and all, but standards and stability are important too. I'm not saying it should never ever change or never ever require a dump/restore, but make it, as policy, difficult to get past the group and the norm not to require d/r. The issue is that as disks get bigger and bigger, databases get bigger and bigger, and this process becomes more and more onerous. If you haven't noticed, data transmission speeds are not accelerating at the rate disk space is growing. I am currently building a project that will have a huge number of records, 1/2tb of data. I can't see how I would ever be able to upgrade PostgreSQL on this system. Indeed. The main issue for me is that the dumping and replication setups require at least 2x the space of one db. That's 2x the hardware which equals 2x $$$. If there were some tool which modified the storage while postgres is down, that would save lots of people lots of money. -M ---(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] Upgrading a database dump/restore
Indeed. The main issue for me is that the dumping and replication setups require at least 2x the space of one db. That's 2x the hardware which equals 2x $$$. If there were some tool which modified the storage while postgres is down, that would save lots of people lots of money. Its time and money. Stoping a database and staring with new software is a lot faster than dumping the data out (disallowing updates or inserts) and restoring the data can take hours or days *and* twice the hardware. ---(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] Upgrading a database dump/restore
On Thu, Oct 05, 2006 at 04:39:22PM -0400, Mark Woodward wrote: Indeed. The main issue for me is that the dumping and replication setups require at least 2x the space of one db. That's 2x the hardware which equals 2x $$$. If there were some tool which modified the storage while postgres is down, that would save lots of people lots of money. Its time and money. Stoping a database and staring with new software is a lot faster than dumping the data out (disallowing updates or inserts) and restoring the data can take hours or days *and* twice the hardware. In that case there should be people willing to fund the development. There have been a few people (even in the last few weeks) who say they're looking into it, perhaps they need a helping hand? Someone got as far as handling catalog updates I beleive, Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] broken dead code in pg_lzcompress.h
I came across the following obviously corrupt macro in pg_lzcompress.h: #define PGLZ_IS_COMPRESSED(_lzdata) ((_lzdata)-varsize != \ e (_lzdata)-rawsize + e \ sizeof(PGLZ_Header)) The reason we have not noticed its brokenness is that it's only used in macro pglz_decomp_init, which is used nowhere. In fact there seems to be quite a lot of dead code in this header. I'm inclined to remove everything that's not referenced, rather than try to fix it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] broken dead code in pg_lzcompress.h
On 10/5/2006 5:04 PM, Tom Lane wrote: I came across the following obviously corrupt macro in pg_lzcompress.h: #define PGLZ_IS_COMPRESSED(_lzdata) ((_lzdata)-varsize != \ e (_lzdata)-rawsize +e \ sizeof(PGLZ_Header)) The reason we have not noticed its brokenness is that it's only used in macro pglz_decomp_init, which is used nowhere. In fact there seems to be quite a lot of dead code in this header. I'm inclined to remove everything that's not referenced, rather than try to fix it. That's quite possible that there is a fair amount of dead code in there because it was originally the header for the lztext datatype, which if memory served only existed quietly in one release to support slightly larger rewrite rules before it was replaced with TOAST. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 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] broken dead code in pg_lzcompress.h
Jan Wieck [EMAIL PROTECTED] writes: That's quite possible that there is a fair amount of dead code in there because it was originally the header for the lztext datatype, which if memory served only existed quietly in one release to support slightly larger rewrite rules before it was replaced with TOAST. Ah, I was wondering how so much of it came to be dead code, but that explains that. I will pull out whatever's not referenced anymore --- it looks like there are a couple of subroutines, not just macros, that we don't need. 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] pg_dump exclusion switches and functions/types
Testing out the new pg_dump exclusion switches I've found that excluding a table means that no functions or types will be dumped. Excluding one table shouldn't exclude these objects. My real use case for this functionality is that I have a database that has 99% of its space used by one big table and I'd like to be able to dump the rest of the database quickly. If I lose function/type information it's useless. Kris Jurka ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump exclusion switches and functions/types
Kris Jurka [EMAIL PROTECTED] writes: Testing out the new pg_dump exclusion switches I've found that excluding a table means that no functions or types will be dumped. Excluding one table shouldn't exclude these objects. I tend to agree ... will see if I can make it happen. (I never did get around to reviewing that patch, anyway ...) One issue is what to do with procedural languages and large objects, which don't have any associated schema. If we treat them as being outside all schemas, we'd have semantics like this: dump the PLs and blobs unless one or more --schema switches appeared. Is that OK? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upgrading a database dump/restore
Mark Woodward [EMAIL PROTECTED] writes: Not to cause any arguments, but this is sort a standard discussion that gets brought up periodically and I was wondering if there has been any softening of the attitudes against an in place upgrade, or movement to not having to dump and restore for upgrades. Whenever someone actually writes a pg_upgrade, we'll institute a policy to restrict changes it can't handle. But until we have a credible upgrade tool it's pointless to make any such restriction. (Credible means able to handle system catalog restructurings, IMHO --- without that, you'd not have any improvement over the current rules for minor releases.) 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] continuing daily testing of dbt2 against postgresql
Mark Wong [EMAIL PROTECTED] writes: After over a year of problems (old site http://developer.osdl.org/markw/postgrescvs/) I have resumed producing daily results of dbt-2 against PostgreSQL CVS code with results here: http://dbt.osdl.org/dbt2.html This is good to hear! I am curious where we are now compared to where we were a year ago ... do you still have the old data, and is the test setup still comparable? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On Oct 6, 2006, at 1:50 , Tom Lane wrote: I'm tempted to propose that we remove the justify_hours call, and tell anyone who really wants the old results to apply justify_hours() to the subtraction result for themselves. Not sure what the fallout would be, though. I'm tempted to support such a proposal. Is this something that we'd want to do for 8.2? There are some interval range checking fixes I'm working on for 8.3. Perhaps this could be rolled into that as well? Then again, range checking and behavior are two separate things. Considering how late it is in the cycle, perhaps the change in behavior should come in 8.3. Michael Glaesemann grzm seespotcode net ---(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] Upgrading a database dump/restore
Well, there is a TODO item ( somewhere only we know ...). Administration * Allow major upgrades without dump/reload, perhaps using pg_upgrade http://momjian.postgresql.org/cgi-bin/pgtodo?pg_upgrade pg_upgrade resists itself to be born, but that discussion seems to seed *certain* fundamentals for a future upgrade tool. It reached pgfoundry, at least the name :) g.- On 10/5/06, Tom Lane [EMAIL PROTECTED] wrote: Mark Woodward [EMAIL PROTECTED] writes: Not to cause any arguments, but this is sort a standard discussion that gets brought up periodically and I was wondering if there has been any softening of the attitudes against an in place upgrade, or movement to not having to dump and restore for upgrades. Whenever someone actually writes a pg_upgrade, we'll institute a policy to restrict changes it can't handle. But until we have a credible upgrade tool it's pointless to make any such restriction. (Credible means able to handle system catalog restructurings, IMHO --- without that, you'd not have any improvement over the current rules for minor releases.) 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 -- Guido Barosio --- http://www.globant.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
Michael Glaesemann [EMAIL PROTECTED] writes: Considering how late it is in the cycle, perhaps the change in behavior should come in 8.3. Yeah, there's not really enough time to think through the consequences now. I'd like to experiment with it for 8.3 though. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.2beta1 failure on IRIX
On Wed, 4 Oct 2006, Tom Lane wrote:
I've applied the patch and it seems to fix the problems.
8.2beta1 + the patch passes all of the regression tests on the IRIX box
(accept the expected difference with the geometry test).
I've applied the attached patch which merges ideas from your version and
John Jorgensen's. Please check it.
regards, tom lane
*** src/backend/utils/adt/float.c.orig Tue Oct 3 23:16:36 2006
--- src/backend/utils/adt/float.c Wed Oct 4 21:21:17 2006
***
*** 328,333
--- 328,359
}
#endif /* HAVE_BUGGY_SOLARIS_STRTOD */
+ #ifdef HAVE_BUGGY_IRIX_STRTOD
+ /*
+* In some IRIX versions, strtod() recognizes only inf, so if the
+* input is infinity we have to skip over inity. Also, it may
+* return positive infinity for -inf.
+*/
+ if (isinf(val))
+ {
+ if (pg_strncasecmp(num, Infinity, 8) == 0)
+ {
+ val = get_float4_infinity();
+ endptr = num + 8;
+ }
+ else if (pg_strncasecmp(num, -Infinity, 9) == 0)
+ {
+ val = -get_float4_infinity();
+ endptr = num + 9;
+ }
+ else if (pg_strncasecmp(num, -inf, 4) == 0)
+ {
+ val = -get_float4_infinity();
+ endptr = num + 4;
+ }
+ }
+ #endif /* HAVE_BUGGY_IRIX_STRTOD */
+
/* skip trailing whitespace */
while (*endptr != '\0' isspace((unsigned char) *endptr))
endptr++;
***
*** 494,499
--- 520,551
endptr--;
}
#endif /* HAVE_BUGGY_SOLARIS_STRTOD */
+
+ #ifdef HAVE_BUGGY_IRIX_STRTOD
+ /*
+* In some IRIX versions, strtod() recognizes only inf, so if the
+* input is infinity we have to skip over inity. Also, it may
+* return positive infinity for -inf.
+*/
+ if (isinf(val))
+ {
+ if (pg_strncasecmp(num, Infinity, 8) == 0)
+ {
+ val = get_float8_infinity();
+ endptr = num + 8;
+ }
+ else if (pg_strncasecmp(num, -Infinity, 9) == 0)
+ {
+ val = -get_float8_infinity();
+ endptr = num + 9;
+ }
+ else if (pg_strncasecmp(num, -inf, 4) == 0)
+ {
+ val = -get_float8_infinity();
+ endptr = num + 4;
+ }
+ }
+ #endif /* HAVE_BUGGY_IRIX_STRTOD */
/* skip trailing whitespace */
while (*endptr != '\0' isspace((unsigned char) *endptr))
*** src/include/port/irix.h.origFri Mar 10 23:38:38 2006
--- src/include/port/irix.h Wed Oct 4 21:20:50 2006
***
*** 1
--- 1,7
/* $PostgreSQL: pgsql/src/include/port/irix.h,v 1.3 2006/03/11 04:38:38
momjian Exp $ */
+
+ /*
+ * IRIX 6.5.26f and 6.5.22f (at least) have a strtod() that accepts
+ * infinity, but leaves endptr pointing to inity.
+ */
+ #define HAVE_BUGGY_IRIX_STRTOD
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] timestamp subtraction (was Re: [SQL] formatting intervals with to_char)
On Oct 5, 2006, at 11:50 AM, Tom Lane wrote:
regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01
09:30:41'::timestamp);
?column?
--
14 days 14:28:19
(1 row)
should be reporting '350:28:19' instead.
This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/
seconds
to months/days/seconds. But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.
I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to
the
subtraction result for themselves. Not sure what the fallout would
be,
though.
I suspect there's applications out there that are relying on that
being nicely formated for display purposes.
I agree it should be removed, but we might need a form of backwards
compatibility for a version or two...
--
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] PL/pgSQL Todo, better information in errcontext from plpgsql
On Oct 5, 2006, at 9:30 AM, Pavel Stehule wrote: With func oid I can get all other info later, without it, I need estimate which functions are in stack track. Why do you need the OID to know exactly what function something is? What's wrong with schema.function(args)? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- 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] timetz storage vs timestamptz
On Oct 3, 2006, at 5:32 PM, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: Why is it timestamptz can store a date and time to 1 microsecond in 8 bytes but a timetz needs 12 to store just the time to 1 microsecond? It's tracking the timezone explicitly ... something that timestamptz really ought to do too. Wow, the docs are totally unclear on that. I believe that explains bug 2661. Yes, it would be nice to store the timezone in timestamptz or an equivalent, but there's also a use for the current behavior. In many cases, you don't care what the original timezone was; you just want to make sure that everything is getting stored in UTC (and then converted to your local timezone on the way back out). I'm thinking time[stamp], time[stamp]tz (which should do what timetz does), and time[stamp]utc (doing what timestamptz does). In the meantime I'll try and clarify the docs on this. -- 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
Re: [HACKERS] Another idea for dealing with cmin/cmax
On Oct 3, 2006, at 2:23 PM, Gregory Stark wrote: If the space set aside for these transaction ids is full when you're inserting i suppose you could just go back to the FSM for another page. But I don't see any way out when you're deleting. You have to mark xmax one way or another and if there's no space left in the footer and you only have 4 bits in the tuple what are you going to do? As an aside doing vacuum freeze more aggressively might reduce the pressure on these ITL slots. But I don't see any way to guarantee a slot is available for xmax when deleting. We would need some sort of scheme where the space for transaction ids is able to grow but we're already growing from both ends of the page. We would either have to interleave transaction ids with line pointers or store them on another special page somewhere. Well, worst-case you could just re-do the whole page if you need to expand the list of transaction slots; I don't think that's a huge deal. What did have me baffled was how to deal with xmax though, since (as you mentioned), you can end up in a situation where you can't delete a tuple because there's no more room on the page for another xmax. But I just thought of a way around that which might be better than a separate store for transaction info: allow for moving a tuple off the current page by placing a link to it's new location, similar to how ctid works. We probably wouldn't want to try and cram that into the item list, but I think we should be able to create a special version of a tuple header (AddressForwardingHeader) that simply states the tuple has moved to this new ctid; go there. Of course, anytime you have to follow that link you're going to pay a penalty, but I think this should only be needed when trying to delete a tuple on a page that's basically full. Theoretically, there shouldn't be too many people trying to hit that deleted tuple, but to further reduce the number of people hitting it, we could include the visibility info (or a pointer to it) in the AddressForwardingHeader. -- 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] Pie-in-sky dreaming about reworking tuple layout entirely
On Oct 3, 2006, at 4:06 PM, Merlin Moncure wrote: On 10/3/06, Gregory Stark [EMAIL PROTECTED] wrote: I can't shake the feeling that merely tweaking the way our varlenas work with a shortvarlena or with compressed varlena headers is missing the real source of our headaches. It seems very strange to me to be trying to step through a tuple with length bits at the head of every field. It's a lot of work spent dealing with a terribly inconvenient format when we can pick the format to be whatever we like. one advantage of the current system is that columns with nulls do not require any storage. so you can alter table add column for free on a really big table. ISTM that your approch would require moving all the static fields in if you added a static field regardless, right? I'm thinking that for Greg's ideas to be workable, we'll need to divorce the on-disk format from what was specified in CREATE TABLE, specifically so we can do things like put all the fixed-width stuff in front of the variable-width stuff (of course we could also further optimize placement beyond that). IIRC, the show-stopper for doing that is how to deal with DDL changes. While we could try and get cute about that, there is a brute- force method that would work without a doubt: store some kind of catalog version number in each tuple (or maybe just in each page, since you could theoretically convert an entire page to a different format without too big a penalty while you've already got it in memory. There are some caveats to this... there will be some limit on how many DDL changes you can make until you run out of version numbers. Worst-case, we could provide a command that would run through the entire table, ensuring that everything is up to the current version. Of course, we'd want some way to throttle that, but I don't think that'd be terribly difficult. One nice thing is that you shouldn't need to mess with any visibility info when you run this, so it should be able to just do everything in-place. BTW, it seems like what we're really looking at between this and discussion of visibility changes, etc. is essentially re-designing the entire storage layout (for better or for worse). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] timestamptz alias
On Oct 4, 2006, at 10:52 AM, Markus Schaber wrote: Andrew Dunstan wrote: It's not only about documenting the pure existence of the aliases (which was already documented in the table on the datatype TOC page), it's also about telling the user which of the names are the ones to avoid, and the reasons to do so. *blink* Why do any need to be avoided? What you use is a matter of taste, and your organisation's coding standards. From a purely technical POV I don't see any reason to avoid using either the canonical type names or the various aliases. At least compatibility with the SQL standard, as well as with other Databases might be a reason. It would be nice to denote types/aliases that are and aren't ANSI. A number are marked in the docs, but it would be good to add the info to that summary table. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Query Failed, out of memory
On Oct 5, 2006, at 11:15 AM, Mark Woodward wrote: On Thu, Oct 05, 2006 at 11:56:43AM -0400, Mark Woodward wrote: The query was executed as: psql -p 5435 -U pgsql -t -A -c select client, item, rating, day from ratings order by client netflix netflix.txt My question, it looks like the kernel killed psql, and not postmaster. The postgresql log file complained about a broken pipe. Question, is this a bug in psql? It took over 4 hours of run time before the crash. Well, psql tried to store the entire resultset in memory at once, and failed. I'm not sure how many records you were trying to display, but try to estimate how much memory that would take to store... What were you trying to do? It's the stupid NetFlix prize thing, I need to dump out the data in a specific order. This is just *one* such query I want to try. I guess, like I told Tom, I have to write a small program that uses a cursor. :-( IIRC, 8.2 adds the ability to at least copy from a view, if not a raw SELECT, so you should probably do that instead. Plus it'd be good to bang on 8.2 with that data set. :) You'll also likely get better performance. -- 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
