Re: [HACKERS] Interactive docs idea
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Stark Sent: 14 April 2005 04:54 To: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Interactive docs idea Alvaro Herrera [EMAIL PROTECTED] writes: I think it's an interesting idea to mail the comments to pgsql-docs, but *please don't* start emulating the PHP behavior regarding comments (leaving the relevant ones forever) :-( I think the PHP manuals are very low quality because of the information in comments which really belongs in the main text body (which is crappy in PHP's manual anyway IMHO.) It seems that's not much of a danger -- the interactive Postgres documentation hardly gets any comments at all in the first place. It would be a big improvement if there were some way to encourage many more comments. We can get from 2 - 10 a day I would guess. They get mailed to a closed list for moderation. Regards, Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Interactive docs idea
Christopher Kings-Lynne wrote: It seems that's not much of a danger -- the interactive Postgres documentation hardly gets any comments at all in the first place. It would be a big improvement if there were some way to encourage many more comments. Only link to the version with comments. No thankyou. I prefer mine straight. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Constant time insertion into highly non-unique indexes
Recent discussions on PERFORM have made me look into some aspects of B-tree index code, especially with regard to bulk loading high volumes of data. I now have cause for concern about the way that Btree index code currently works when inserting large volumes of data into a table with non-unique indexes, which is most tables with 1 index. _bt_insertonpg contains an algorithm to locate a page to insert into. Since the index is unique, if there are many rows in the table then there could be potentially a large number of possible insertion points. line 404 onwards says * If we will need to split the page to put the item here, * check whether we can put the tuple somewhere to the right, * instead. Keep scanning right until we * (a) find a page with enough free space, * (b) reach the last page where the tuple can legally go, or * (c) get tired of searching. * (c) is not flippant; it is important because if there are many * pages' worth of equal keys, it's better to split one of the early * pages than to scan all the way to the end of the run of equal keys * on every insert. We implement get tired as a random choice, * since stopping after scanning a fixed number of pages wouldn't work * well (we'd never reach the right-hand side of previously split * pages). Currently the probability of moving right is set at 0.99, * which may seem too high to change the behavior much, but it does an * excellent job of preventing O(N^2) behavior with many equal keys. from v1.62 The end result of this is that the first N records fill the first block, then the next N records search the first block, start to fill the next block. When that is full we move to the next, etc. So as we add rows index insertion time increases. The probability we move right is high, so we soon grow to the situation where we move right a large number of times to find an insertion point. Once a page has been split, all new inserts must make there way across the same pages to the new insertion point. After many rows had been inserted, the steady state will be that all blocks at the start of that index value will be full apart from the current insertion point. With a current probability of moving right of 0.99, the likelihood that the current insertion point is more than 20 blocks away from the start of the index value is 82%!! With a large index, many of these would not be in cache, so I/O is going to show close to O(N^2) behaviour for at least the first 10-20 pages until the randomness of the algorithm kicks in. Any algorithm to find an insertion point has a number of factors to trade-off: a) insertion cost b) packing density c) ordering of index to heap data However you cut it, you can only fit so many records in a block, so when averaged out the number of page splits cannot be less than a constant wherever you choose to split, assuming we have constant length index keys (for now). Current algorithm attempts to locally minimise a) and minimise b). The current algorithm is greedy because it tries to avoid page splitting by searching for a space created by somebody else, rather than performing the action itself. The end result is that everybody pays a much higher cost overall. When loading large number of rows, a greedy algorithm makes no sense at all, since you are only competing with yourself. A non-greedy algorithm would set the probability of moving right = 0, minimising insertion cost since we would always try to insert the index entry into the first block and if no space, then split. If we reduce the probability of moving right this reduces insertion time considerably, though with the problem that we would allow the index to increase further in size, eventually ending up at twice as large since we split each page in two. This would waste disk space, but also waste RAM since most index pages would be half full, as well as doubling the time taken for a full index scan. That effect prevents me from suggesting the very simple change of reducing the probability of moving right to 90%, which would dramatically improves the insertion time behaviour. Reducing the probability of moving right should only be done in conjunction with making an imbalanced split so that the right hand index page would be almost full. The combined approach would offer: a) insertion cost very low b) packing density very high and as a minor addition... c) excellent ordering of index - heap (when heap is ordered) Changing the algorithm for all cases would probably be a bad thing, since not all indexes are highly non-unique and the way it works now is tried and tested. An observation: If we ever scan 3 or more index blocks looking for an insertion point we know that the middle block only has rows for this index value. Once we know that we have a block entirely dedicated to an index value, we can safely take another strategy to locate the insertion point. Proposed algorithm would then be * If we will need to split the page to put
Re: [HACKERS] Constant time insertion into highly non-unique indexes
Simon Riggs [EMAIL PROTECTED] writes: Recent discussions on PERFORM have made me look into some aspects of B-tree index code, especially with regard to bulk loading high volumes of data. Have you read the archived discussions that led up to the current algorithm? I don't think it's nearly as bad as you believe. In particular, I think you missed the point that the move-or-split decision is *random* and is therefore made differently by each inserter. Therefore the probability that the earlier pages get split rises rapidly as more and more insertions are made --- and it only takes one split decision to take the pressure off. It's entirely possible that the 0.99 figure needs some fine tuning. IIRC, the experiments we did to choose that number were done with pretty simple test indexes --- probably just int4. Thinking about the behavior, it seems plausible that the figure needs to drop as the number of entries per page drops ... but we have not tested that. In an int4 index on Intel-ish hardware (MAXALIGN 4), you can fit about 500 entries per page. So consider a case where the first 2 pages for a given value are full and the third is half full. To fill the third completely will require 250 insertions, by which time there is a very good chance (more than 90% if I did the math right) that someone will have decided to split rather than move right at the second page. After that the second page fills, and then we are back to the original state (because the new third page will be half full). So I claim that in fact the behavior *is* constant time: most insertions will succeed on either the second or third page, indefinitely. However, obviously if there are only a few values per page, you would get much worse behavior. (OTOH, the wider the index values, the lower the probability of exact duplicates anyway, I'd think, so we may be wasting our time to worry about the behavior there.) regards, tom lane ---(end of broadcast)--- TIP 3: 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] Constant time insertion into highly non-unique
On Thu, 2005-04-14 at 10:35 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Recent discussions on PERFORM have made me look into some aspects of B-tree index code, especially with regard to bulk loading high volumes of data. Have you read the archived discussions that led up to the current algorithm? I don't think it's nearly as bad as you believe. In particular, I think you missed the point that the move-or-split decision is *random* and is therefore made differently by each inserter. Therefore the probability that the earlier pages get split rises rapidly as more and more insertions are made --- and it only takes one split decision to take the pressure off. Yes, and did the math too. The cumulative probability of having to search more than 20 blocks before you split is 82%. ..36% chance of searching more than 100. It's entirely possible that the 0.99 figure needs some fine tuning. That would be a simple approach, though has downsides as discussed. IIRC, the experiments we did to choose that number were done with pretty simple test indexes --- probably just int4. Thinking about the behavior, it seems plausible that the figure needs to drop as the number of entries per page drops ... but we have not tested that. In an int4 index on Intel-ish hardware (MAXALIGN 4), you can fit about 500 entries per page. So consider a case where the first 2 pages for a given value are full and the third is half full. To fill the third completely will require 250 insertions, by which time there is a very good chance (more than 90% if I did the math right) that someone will have decided to split rather than move right at the second page. After that the second page fills, and then we are back to the original state (because the new third page will be half full). So I claim that in fact the behavior *is* constant time: most insertions will succeed on either the second or third page, indefinitely. However, obviously if there are only a few values per page, you would get much worse behavior. (OTOH, the wider the index values, the lower the probability of exact duplicates anyway, I'd think, so we may be wasting our time to worry about the behavior there.) For once, I beg to note that the above maths is not correct, because the algorithm doesn't work exactly that way. The move right only occurs when the page is full, so the chance of moving right is not 0.99^250, but 0.99, since the previous 249 inserts would not cause a page split. The probability does not drop away as you suggest and the operation is not constant time as a result. IMHO the performance figures show this to be true. Yes, with 500 entries per page, it would take 1500 rows/per index value before the proposed new algorithm makes *any* difference at all. Since people often build indexes on columns that have skewed distributions, the time taken for MFVs is of particular concern in this regard. In a million+ row table we are are *very* likely to find such numbers of rows/index value even amongst infrequently occurring values and still find the index has useful selectivity. My viewpoint is, as ever, towards large and high performance databases. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 3: 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] Constant time insertion into highly non-unique indexes
Simon Riggs [EMAIL PROTECTED] writes: The move right only occurs when the page is full, so the chance of moving right is not 0.99^250, but 0.99, since the previous 249 inserts would not cause a page split. Sure, but given that we have a full page, the probability that 250 successive insertions *all* decide to move right rather than split that page is 0.99^250. And it only takes one decision to split to maintain the constant-time behavior. So I still think your analysis is incorrect. IMHO the performance figures show this to be true. *What* performance figures? You have shown none. We did do performance testing of this algorithm when we adopted it, and it worked fine --- though as I say, I don't think we tested with any very wide keys. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Constant time insertion into highly non-unique
On Thu, 2005-04-14 at 11:15 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: The move right only occurs when the page is full, so the chance of moving right is not 0.99^250, but 0.99, since the previous 249 inserts would not cause a page split. Sure, but given that we have a full page, the probability that 250 successive insertions *all* decide to move right rather than split that page is 0.99^250. And it only takes one decision to split to maintain the constant-time behavior. So I still think your analysis is incorrect. OK... point accepted. Darn, but also thank goodness it performs. P(N) 0.999 for W byte keys, at... N W Mean blocks read/insert 3 4 bytes 1.1 5 8 bytes 1.4 11 16 bytes2.1 22 32 bytes3.6 43 64 bytes6.7 83 128 bytes 12.5 lots256 bytes 23 IMHO the performance figures show this to be true. *What* performance figures? The figures shown on PERFORM recently, with graphs. We still have a performance issue with insertion rate for large indexes. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Interactive docs idea
On Thu, 2005-04-14 at 03:56, Dave Page wrote:
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Greg Stark
Sent: 14 April 2005 04:54
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Interactive docs idea
Alvaro Herrera [EMAIL PROTECTED] writes:
I think it's an interesting idea to mail the comments to
pgsql-docs, but
*please don't* start emulating the PHP behavior regarding comments
(leaving the relevant ones forever) :-( I think the PHP
manuals are
very low quality because of the information in comments which really
belongs in the main text body (which is crappy in PHP's
manual anyway
IMHO.)
It seems that's not much of a danger -- the interactive
Postgres documentation
hardly gets any comments at all in the first place. It would be a big
improvement if there were some way to encourage many more comments.
We can get from 2 - 10 a day I would guess. They get mailed to a closed
list for moderation.
It's not so much closed as just separate and this was mainly because the
folks on -docs and the folks on -www didn't want all the traffic.
FWIW the PHP docs do actually integrate their comments into the docs,
although this seems to have slowed down much more over the recent years.
(I know they used to do it though, cause several comments I put in 5+
years ago have been integrated into the mainline docs).
On the PostgreSQL front, Tom has in the past gone through comments
around release time and integrated in the relevant changes; I've also
submitted a patch or two based on suggestions that have come across
since we got the new system in place. If you're interested in moderating
the comments and have time to write patches for new suggestions I'm sure
most people would be happy to have you on board.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: 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] Constant time insertion into highly non-unique indexes
Just to check if I was nuts or not, I made up a test case: create table foo (f1 text); create index fooi on foo(f1); then truncate foo; copy foo from stdin; 99 98 97 ... one million rows ... 02 01 00 \. versus truncate foo; copy foo from stdin; xx xx xx ... one million rows ... xx xx xx \. The first of these should of course force a btree split on the first page each time it splits, while the second will involve the probabilistic moveright on each split. But the files will be exactly the same size. [EMAIL PROTECTED] ~]$ time psql -f zdecr10 test TRUNCATE TABLE real1m41.681s user0m1.424s sys 0m0.957s [EMAIL PROTECTED] ~]$ time psql -f zsame10 test TRUNCATE TABLE real1m40.927s user0m1.409s sys 0m0.896s [EMAIL PROTECTED] ~]$ And it just happens that I had this server built with profiling enabled, so I was able to look at the stats for each run, and I see that _bt_compare is actually called slightly *fewer* times in the all-identical-data case. zdecr10: 2954536 _bt_moveright cycle 3 [57] 20938485 _bt_binsrch cycle 3 [48] 0.050.186491/3006701 _bt_insertonpg cycle 2 [16] [33] 4.9 11.730.00 23899512 _bt_compare cycle 3 [33] 21944768 FunctionCall2 cycle 3 [24] zsame10: 2935922 _bt_moveright cycle 3 [62] 17156948 _bt_binsrch cycle 3 [54] 3.45 11.09 465429/3072793 _bt_insertonpg cycle 2 [16] [31] 5.0 11.560.00 20558299 _bt_compare cycle 3 [31] 18622167 FunctionCall2 cycle 3 [24] So the theory does work, at least for small index entries. Currently repeating with wider ones ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Interactive docs idea
On Thu, Apr 14, 2005 at 11:54:56AM -0400, Robert Treat wrote: On the PostgreSQL front, Tom has in the past gone through comments around release time and integrated in the relevant changes; I've also submitted a patch or two based on suggestions that have come across since we got the new system in place. If you're interested in moderating the comments and have time to write patches for new suggestions I'm sure most people would be happy to have you on board. So what list is this? -- Alvaro Herrera ([EMAIL PROTECTED]) Java is clearly an example of a money oriented programming (A. Stepanov) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Constant time insertion into highly non-unique indexes
I wrote: So the theory does work, at least for small index entries. Currently repeating with wider ones ... I tried the same test with the row width extended to 100 characters and then 500 characters. The runtime and number of _bt_compare calls is still about the same for the all-different-key and all-same-key cases. I'm a bit surprised at that --- with only a dozen index entries per page, you'd expect a lot more moverights --- but I sure do not see any evidence here that there's anything broken about our handling of equal keys. The top profile entries with 500-character keys are decreasing keys: % cumulative self self total time seconds secondscalls Ks/call Ks/call name 33.43440.48 440.48 2163283 0.00 0.00 XLogInsert 13.45617.65 177.17 50104 0.00 0.00 CopyGetData 7.74719.64 101.99 50103 0.00 0.00 pq_copymsgbytes 6.72808.1888.55 101 0.00 0.00 CopyReadLine 4.03861.2453.05 50104 0.00 0.00 CopyGetChar 3.82911.5550.31 100 0.00 0.00 CopyReadAttribute 2.71947.2935.74 23116181 0.00 0.00 LWLockAcquire 2.57981.1133.81 11462122 0.00 0.00 hash_search 2.16 1009.5328.43 23345281 0.00 0.00 LWLockRelease 1.72 1032.1822.64 31306616 0.00 0.00 _bt_compare 1.42 1050.9418.76 8779022 0.00 0.00 PinBuffer 1.08 1065.2214.28 7452454 0.00 0.00 _bt_moveright 1.06 1079.1713.95 100 0.00 0.00 textin 0.98 1092.0612.88 11462142 0.00 0.00 hash_any equal keys: % cumulative self self total time seconds secondscalls Ks/call Ks/call name 25.21326.87 326.87 2083931 0.00 0.00 XLogInsert 13.59503.09 176.22 50104 0.00 0.00 CopyGetData 7.96606.32 103.23 50103 0.00 0.00 pq_copymsgbytes 6.97696.6390.31 101 0.00 0.00 CopyReadLine 4.06749.2852.65 35592024 0.00 0.00 LWLockAcquire 3.97800.7351.45 50104 0.00 0.00 CopyGetChar 3.73849.1048.37 100 0.00 0.00 CopyReadAttribute 3.40893.1344.04 17223947 0.00 0.00 hash_search 3.33936.3743.23 35736377 0.00 0.00 LWLockRelease 2.34966.7630.40 1083913 0.00 0.00 _bt_insertonpg 2.29996.4929.72 15477642 0.00 0.00 PinBuffer 1.98 1022.1325.64 32383797 0.00 0.00 _bt_compare 1.45 1040.9818.86 15628296 0.00 0.00 UnpinBuffer 1.40 1059.1118.12 33256782 0.00 0.00 LockBuffer 1.28 1075.6916.58 17223967 0.00 0.00 hash_any 1.19 1091.1215.43 6832956 0.00 0.00 _bt_moveright 1.06 1104.8913.77 100 0.00 0.00 textin 0.82 1115.5210.63 15628296 0.00 0.00 ReadBuffer regards, tom lane ---(end of broadcast)--- TIP 3: 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] Constant time insertion into highly non-unique
On Thu, 2005-04-14 at 12:10 -0400, Tom Lane wrote: The first of these should of course force a btree split on the first page each time it splits, while the second will involve the probabilistic moveright on each split. But the files will be exactly the same size. [EMAIL PROTECTED] ~]$ time psql -f zdecr10 test TRUNCATE TABLE real1m41.681s user0m1.424s sys 0m0.957s [EMAIL PROTECTED] ~]$ time psql -f zsame10 test TRUNCATE TABLE real1m40.927s user0m1.409s sys 0m0.896s [EMAIL PROTECTED] ~]$ I think thats conclusive. So the theory does work, at least for small index entries. Currently repeating with wider ones ... I think we should adjust the probability for longer item sizes - many identifiers can be 32 bytes and there are many people with a non-unique URL column for example. An average of over 2 blocks/insert at 16 bytes is still one too many for my liking, though I do understand the need for the randomness. I'd suggest a move right probability of 97% (divide by 16) for itemsz 16 bytes and 94% (divide by 32) when itemsz = 128 Though I think functional indexes are the way to go there. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Interactive docs idea
Dave Page dpage@vale-housing.co.uk writes: We can get from 2 - 10 a day I would guess. They get mailed to a closed list for moderation. Uhm, then where are they? The comments in the PHP docs, while they contain a lot of garbage also contain a lot of helpful tips and warnings. There's hardly any in the Postgres docs. I think the idea of moderating the comments is inherently flawed. You can either have the deliberate, planned documentation without the comments, or you can have the wild-west style comments system, but trying to have it both ways is impossible. It just leads to the current situation where the comments are moribund. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interactive docs idea
On Thu, Apr 14, 2005 at 01:39:11PM -0400, Greg Stark wrote: I think the idea of moderating the comments is inherently flawed. You can either have the deliberate, planned documentation without the comments, or you can have the wild-west style comments system, but trying to have it both ways is impossible. It just leads to the current situation where the comments are moribund. What do you mean, moribund? What happens is that at each release Tom gets the comments and integrate whatever of value into the main text body. The rest are deleted. That's the way it should be IMHO. -- Alvaro Herrera ([EMAIL PROTECTED]) I dream about dreams about dreams, sang the nightingale under the pale moon (Sandman) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Interactive docs idea
The comments in the PHP docs, while they contain a lot of garbage also contain a lot of helpful tips and warnings. There's hardly any in the Postgres docs. If it were I, we would start a wiki that was linked from the docs but not have the docs themselves have the comments. I think the idea of moderating the comments is inherently flawed. If more people were intelligent and reasonable human beings you would be correct. I have seen the comments we get and a lot are complete garbage. You can either have the deliberate, planned documentation without the comments, or you can have the wild-west style comments system, but trying to have it both ways is impossible. It just leads to the current situation where the comments are moribund. See my comment about a wiki :) Also shouldn't this all be on pgsql-www? Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interactive docs idea
On Thu, Apr 14, 2005 at 03:01:10PM -0400, Greg Stark wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Thu, Apr 14, 2005 at 01:39:11PM -0400, Greg Stark wrote: I think the idea of moderating the comments is inherently flawed. You can either have the deliberate, planned documentation without the comments, or you can have the wild-west style comments system, but trying to have it both ways is impossible. It just leads to the current situation where the comments are moribund. What do you mean, moribund? What happens is that at each release Tom gets the comments and integrate whatever of value into the main text body. The rest are deleted. So there's no comments saying here's a useful function written using this function or watch out for this common bug or if what you want to do is this you might want to check out this other function or any of the thousands of similar comments in the PHP docs. You are right, there aren't. But to me that's not a bad thing. I'd find PHP's manual much better if the main text body really covered the subject instead of only showing a couple of examples, and leaving part of the matter to the comments (Even to editor's notes in the comments!) Instead you get one good example that's worthy of being included in the documentation and nothing else. There's also a problem that people are less likely to put comments in if they don't see any existing comments. I have agree with you on this last assertion. -- Alvaro Herrera ([EMAIL PROTECTED]) Postgres is bloatware by design: it was built to house PhD theses. (Joey Hellerstein, SIGMOD annual conference 2002) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interactive docs idea
Alvaro Herrera [EMAIL PROTECTED] writes: So there's no comments saying here's a useful function written using this function or watch out for this common bug or if what you want to do is this you might want to check out this other function or any of the thousands of similar comments in the PHP docs. You are right, there aren't. But to me that's not a bad thing. I'd find PHP's manual much better if the main text body really covered the subject instead of only showing a couple of examples, and leaving part of the matter to the comments (Even to editor's notes in the comments!) I think this is a false dichotomy. Nobody's arguing that we should let the main body of the documentation rot in favour of the comments. There's no reason we can't have more comments and still have nicer authoritative documentation than the PHP folks :) I really see the comments serving a separate purpose from the main body. The main body should be the manual -- an authoritative reference. The comments should be more like this mailing list only organized. How many times have you seen a question on pgsql-general and thought gee that would be answered if only the poster searched the archives? Well the comments on PHP serve basically as an organized repository of such previous discuss. Instead of being in a single archive to search through they're attached directly to the relevant piece of the documentation. Certainly comments that amount to bug reports about the documentation can be addressed by fixing the documentation (and the comment can then be removed). Likewise comments that suggest additions can be moved into the main body of the documentation. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] NetBSD mac68k crashing on union regression test
=?ISO-8859-1?Q?R=E9mi_Zara?= [EMAIL PROTECTED] writes: Well, I've re-run the checks several times after a clean make and it does not crash anymore. So the patch seems to help ! Thought it might ;-) Please consider applying it. Done. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] OUT parameters in PL/Java
Thomas Hallgren [EMAIL PROTECTED] writes: Tom Lane wrote: Hmm. I think this is not your bug. Is the call coming from evaluate_function in clauses.c? We need to either prevent that from pre-evaluating a function returning RECORD, or fix it so it can pass the expected tuple descriptor ... probably the former :-( I committed a patch to prevent that problem. I've found another problem that might be related. The same example as above but this time I use returns setof record. Using the CVS head I now get: thhal=# select * from javatest.recordExample(3, 4) as (foo int, bar int, baz timestamptz); ERROR: record type has not been registered this happens before the java_call_handler is called. Here's the stacktrace: #0 lookup_rowtype_tupdesc_noerror (type_id=2249, typmod=-1, noError=0 '\0') at typcache.c:425 #1 0x081f435b in lookup_rowtype_tupdesc (type_id=2249, typmod=-1) at typcache.c:390 #2 0x0812081a in ExecMakeTableFunctionResult (funcexpr=0x9c4e288, econtext=0x9c4ded0, expectedDesc=0x9c4e068, returnDesc=0x9c52200) at execQual.c:1298 Looking at the code, it appears that the java call handler *has* been called once, and what it returned was a tuple that didn't carry any type identification. This is probably because you didn't call BlessTupleDesc. nodeFunctionscan.c formerly did that, and I suppose it should keep doing it for backwards compatibility. I put back the call... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Multicolumn hash tables space complexity.
Hi, I hope its not off topic. I have an algorithm to implement where it needs to hold the tuples (complete tuples - all columns) either in a b+tree or a hashtable. I am concerned about the space such an index will require. What is the difference in percentages from the size of all the data not indexed vs. holding it in a b+tree or a hashtable. also what is the difference when those indices are half-full? e.g. I understand a b+tree half full in the worse case can take space as if it was full. I am guessing it's the same with hashtables. I understand that the HASH indice in postgresql does not support a multicolumn. What does it take to upgrade it to do that? Regards, tzahi. WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
