Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith [EMAIL PROTECTED] writes: On Thu, 4 Dec 2008, Gregory Stark wrote: My point was more that you could have a data warehouse on a non-dedicated machine, you could have a web server on a non-dedicated machine, or you could have a mixed server on a non-dedicated machine. I should just finish the documentation, where there will be a big disclaimer saying THESE SETTINGS ASSUME A SERVER DEDICATED TO POSTGRESQL! That's the context here. Why, after you follow my tuning instructions, you're lucky if the server will run anything but the database afterwards. So you're getting rid of the desktop mode altogether? That's more drastic than I was suggesting. I was suggesting that you want to separate desktop into a separate option form the workload list. What users are getting now is kind of like asking users Would you like a small, medium, large, or diet Coke? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
Hi, On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao [EMAIL PROTECTED] wrote: Do we need to worry about periodic renegotiation of keys in be-secure.c? What is keys you mean? See the notes in that file for explanation. Thanks! I would check it. The key is used only when we use SSL for the connection of replication. As far as I examined, secure_write() renegotiates the key if needed. Since walsender calls secure_write() when sending the WAL to the standby, the key is renegotiated periodically. So, I think that we don't need to worry about the obsolescence of the key. Am I missing something? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Greg Smith napsal(a): On Wed, 3 Dec 2008, Zdenek Kotala wrote: It works fine for 8.3-8.4 too, but I'm working on cleanup and fixing bugs. I hope that I will send updated version to community today. That would be great. It didn't feel like you were quite done with it yet. I'll be glad to help test it out, just didn't want to jump into that if it was known to still have issues that were being worked on. Please let us know what the remaining bugs you know about are at that point, I really don't want this part of things to get ignored just because the page format stuff is the harder part. It is more workaround or temporary solution. This approach is easy but it has lot of limitation. Problem with toast tables is one, but biggest problem is with dropped columns. And maybe there will be more issues. Problem with dump is that you lost a internal data. Can you be a bit more specific about what the problems with TOAST and dropped columns are? If those are covered in your presentation or came up already and I missed it, just point me that way; I'm still working my way through parts of this and don't expect to ever have it all in my head like you do at this point. Obviously this approach is going to be somewhat traumatic even if perfectly executed because of things like losing table statistics. The TOAST problem is already addressed and script should handle it correctly. But I don't like it much, because it is kind of magic. Dropped column is another story. Heikki pointed me this issue in Prato and current published version of script does not handle it. Problem is that dropped columns are only mark as a deleted and data are still stored in tuples. Catalog contains related information about position and length, but when you perform dump and restore, this information is lost and columns are shifted ... I already added to check for dropped column and now I'm going to test how upgrade works with visibility map. I'll send this version when I finish tests. snip I personally prefer to have special mode (like boostrap) which converts data from old catalog to new format. That's a perfectly fine idea I would like to see too. But if we have to write such a thing from scratch right now, I'm afraid that may be too late to implement and still ship the next release on schedule. And if such bootstrap code is needed, we sure need to make sure the prototype it's going to be built on is solid ASAP. That's what I want to help you look into if you can catch me up a bit here. I agree. This is a starter for 8.3 - 8.4, but we need more robust solution in the future. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] V2 of PITR performance improvement for 8.4
On Wed, 2008-12-03 at 14:22 +0900, Koichi Suzuki wrote: There's clearly a huge gain using prefetch, when we have full_page_writes = off. But that does make me think: Why do we need prefetch at all if we use full page writes? There's nothing to prefetch if we can keep it in cache. Agreed. This is why I proposed prefetch optional through GUC. So I'm wondering if we only need prefetch because we're using lesslog? If we integrated lesslog better into the new replication would we be able to forget about doing the prefetch altogether? In the case of lesslog, almost all the FPW is replaced with corresponding incremental log and recovery takes longer. Prefetch dramatically improve this, as you will see in the above result.To improve recovery time with FPW=off or FPW=on and lesslog=yes, we need prefetch. It does sound like it is needed, yes. But if you look at the architecture of synchronous replication in 8.4 then I don't think it makes sense any more. It would be very useful for the architecture we had in 8.3, but that time has gone. If we have FPW=on on primary then we will stream WAL with FPW to standby. There seems little point removing it *after* it has been sent, then putting it back again before we recover, especially when it causes a drop in performance that then needs to be fixed (by this patch). pg_lesslog allowed us to write FPW to disk, yet send WAL without FPW. So if we find a way of streaming WAL without FPW then this patch makes sense, but not until then. So far many people have argued in favour of using FPW=on, which was the whole point of pg_lesslog. Are we now saying that we would run the primary with FPW=off? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
On Thu, 2008-12-04 at 17:57 +0900, Fujii Masao wrote: On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao [EMAIL PROTECTED] wrote: Do we need to worry about periodic renegotiation of keys in be-secure.c? What is keys you mean? See the notes in that file for explanation. Thanks! I would check it. The key is used only when we use SSL for the connection of replication. As far as I examined, secure_write() renegotiates the key if needed. Since walsender calls secure_write() when sending the WAL to the standby, the key is renegotiated periodically. So, I think that we don't need to worry about the obsolescence of the key. Understood. Is the periodic renegotiation of keys something that would interfere with the performance or robustness of replication? Is the delay likely to effect sync rep? I'm just checking we've thought about it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
On Thu, 2008-12-04 at 16:10 +0900, Fujii Masao wrote: * Diagram on p.2 has two Archives. We have just one (yes) No, we need archive in both the primary and standby. The primary needs archive because a base backup is required when starting the standby. Meanwhile, the standby needs archive for cooperating with pg_standby. If the directory where pg_standby checks is the same as the directory where walreceiver writes the WAL, the halfway WAL file might be restored by pg_standby, and continuous recovery would fail. So, we have to separate the directories, and I assigned pg_xlog and archive to them. Another idea; walreceiver writes the WAL to the file with temporary name, and rename it to the formal name when it fills. So, pg_standby doesn't restore a halfway WAL file. But it's more difficult to perform the failover because the unrenamed WAL file remains. WAL sending is either via archiver or via streaming. We must switch cleanly from one mode to the other and not half-way through a WAL file. When WAL sending is about to begin, issue xlog switch. Then tell archiver to shutdown once it has got to the last file. All files after that point are streamed. So there need be no conflict in filename. We must avoid having two archives, because people will configure this incorrectly. * If we have synchronous_commit = off do we ignore synchronous_replication = on (yes) No, we can configure them independently. synchronous_commit covers only local writing of the WAL. If synch_*commit* should cover both local writing and replication, I'd like to add new GUC which covers only local writing (synchronous_local_write?). The only sensible settings are synchronous_commit = on, synchronous_replication = on synchronous_commit = on, synchronous_replication = off synchronous_commit = off, synchronous_replication = off This doesn't make any sense: (does it??) synchronous_commit = off, synchronous_replication = on Do we definitely need the archiver to move the files written by walreceiver to archive and then move them back out again? Yes, it's because of cooperating with pg_standby. It seems very easy to make this happen the way we want. We could make pg_standby look into pg_xlog also, for example. I was expecting you to have walreceiver and startup share an end of WAL address via shared memory, so that startup never tries to read past end. That way we would be able to begin reading a WAL file *before* it was filled. Waiting until a file fills means we still have to have archive_timeout set to ensure we switch regularly. We need the existing mechanisms for the start of replication (base backup etc..) but we don't need them after that point. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
On Thu, 4 Dec 2008, Zdenek Kotala wrote: The TOAST problem is already addressed and script should handle it correctly. But I don't like it much, because it is kind of magic. I just read through the whole toast by chunk-end thread again and it does seem pretty complicated. What is the magic part you're still not happy with? I'll send this version when I finish tests. You really should feel free to forward these things over as soon as you've got something working, even if you're still running your own tests. With all due respect to how much you've done here, the sooner we can get more people working with and on something closer to candidate code the better. I've have started a couple of days ago but couldn't find anything but the old script. If some parts have comments like this is an awful check for dropped columns that probably doesn't even work yet, that's OK. We need to get other people helping out with this besides you. Problem is that dropped columns are only mark as a deleted and data are still stored in tuples. Catalog contains related information about position and length, but when you perform dump and restore, this information is lost and columns are shifted ... Here's a good example; that seems a perfect problem for somebody else to work on. I understand it now well enough to float ideas without even needing to see your code. Stop worring about it, I'll grab responsibility for making sure it gets done by someone. So, for everyone else who isn't Zdenek: when columns are dropped, pg_attribute.attisdropped turns true and atttypid goes to 0. pg_dump skips over them, and even if it didn't pg_restore doesn't know how to put them back. I can think of a couple of hacks to work around this, and one of them might even work: 1) Create a dummy type that exists only to flag these during conversion. Re-add all the deleted columns by turning off attisdropped and flag them with that type. Dump. Restore. Re-delete the columns. My first pass through poking holes in this idea wonders how the dump will go crazy if it finds rows that were created after the column was dropped, that therefore have no value for it. 2) Query the database to find all these deleted columns and store the information we need about them, save that into some text files (similary to how relids are handled by the script right now). After the schema restore, read that list in, iterating over the missing ones. For each column that was gone, increment attnum for everything above that position to renumber a place for it. Put a dummy column entry back in that's already marked as deleted. 3) Wander back into pre-upgrade land by putting together something that wanders through every table updating any row that contains data for a dropped column. Since dropping columns isn't really common in giant data warehouses, something that had to wander over all the tuples related to a table that has lost a column should only need to consider a pretty small subset of the database. You might even make it off-line without getting too many yelps from the giant DW crowd, seems like it would be easy to write something to estimate the amount of work needed in advance of doing it even (before you take the system down, run a check utility that says The server currently has 65213 rows of data for tables with deleted columns). Who wants to show off how much more they know about this than me by saying what's right or wrong with these various ideas? If we care about the fact that columns never go away and are using (1) or (2), could also consider adding some additional meta-data to 8.4 such that something like vacuum can flag when a column no longer exists in any part of the data. All deleted columns move from 8.3 to 8.4, but one day the 8.5 upgrade could finally blow them away. There's already plenty of per-table catalog data being proposed to push into 8.4 for making future upgrades easier, this seems like a possible candidate for something to make space for there. As I just came to appreciate the problem I'm not sure about that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs head initdb hangs on unixware
On Wed, 3 Dec 2008, Heikki Linnakangas wrote: Date: Wed, 03 Dec 2008 20:29:01 +0200 From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Zdenek Kotala [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] cvs head initdb hangs on unixware [EMAIL PROTECTED] wrote: On Tue, 2 Dec 2008, Heikki Linnakangas wrote: Date: Tue, 02 Dec 2008 20:47:19 +0200 From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Zdenek Kotala [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] cvs head initdb hangs on unixware [EMAIL PROTECTED] wrote: Suivi de pile correspondant à p1, Programme postmaster *[0] fsm_rebuild_page( présumé: 0xbd9731a0, 0, 0xbd9731a0) [0x81e6a97] [1] fsm_search_avail( présumé: 0x2, 0x6, 0x1) [0x81e68d9] [2] fsm_set_and_search(0x84b2250, 0, 0, 0x2e, 0x5, 0x6, 0x2e, 0x8047416, 0xb4) [0x81e6385] [3] RecordAndGetPageWithFreeSpace(0x84b2250, 0x2e, 0xa0, 0xb4) [0x81e5a00] [4] RelationGetBufferForTuple( présumé: 0x84b2250, 0xb4, 0) [0x8099b59] [5] heap_insert(0x84b2250, 0x853a338, 0, 0, 0) [0x8097042] [6] simple_heap_insert( présumé: 0x84b2250, 0x853a338, 0x853a310) [0x8097297] [7] InsertOneTuple( présumé: 0xb80, 0x84057b0, 0x8452fb8) [0x80cb210] [8] boot_yyparse( présumé: 0x, 0x3, 0x8047ab8) [0x80c822b] [9] BootstrapModeMain( présumé: 0x66, 0x8454600, 0x4) [0x80ca233] [10] AuxiliaryProcessMain(0x4, 0x8047ab4) [0x80cab3b] [11] main(0x4, 0x8047ab4, 0x8047ac8) [0x8177dce] [12] _start() [0x807ff96] seems interesting! We've had problems already with unixware optimizer, hope this one is fixable! Looking at fsm_rebuild_page, I wonder if the compiler is treating int as an unsigned integer? That would cause an infinite loop. No, a simple printf of nodeno shows it starting at 4096 all the way down to 0, starting back at 4096... Hmm, it's probably looping in fsm_search_avail then. In a fresh cluster, there shouldn't be any broken FSM pages that need rebuilding. You're right! I'd like to see what the FSM page in question looks like. Could you try to run initdb with -d -n options? I bet you'll get an infinite number of lines like: DEBUG: fixing corrupt FSM block 1, relation 123/456/789 right again! DEBUG: fixing corrupt FSM block 2, relation 1663/1/1255 Could you zip up the FSM file of that relation (a file called e.g 789_fsm), and send it over? Or the whole data directory, it shouldn't be that big. you get both. BTW, this is an optimizer problem, not anything wrong with the code, but I'd hate to have a -g compiled postmaster in prod :) best regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) 1255_fsm Description: fsm db.tgz Description: data dir -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Greg Smith [EMAIL PROTECTED] writes: Here's a good example; that seems a perfect problem for somebody else to work on. I understand it now well enough to float ideas without even needing to see your code. Stop worring about it, I'll grab responsibility for making sure it gets done by someone. So, for everyone else who isn't Zdenek: when columns are dropped, pg_attribute.attisdropped turns true and atttypid goes to 0. pg_dump skips over them, and even if it didn't pg_restore doesn't know how to put them back. I can think of a couple of hacks to work around this, and one of them might even work: 1) Create a dummy type that exists only to flag these during conversion. Re-add all the deleted columns by turning off attisdropped and flag them with that type. Dump. Restore. Re-delete the columns. My first pass through poking holes in this idea wonders how the dump will go crazy if it finds rows that were created after the column was dropped, that therefore have no value for it. No, those records would work fine, they will have the column set NULL. But in any case it doesn't matter, you don't need to dump out the data at all -- that's kind of the whole point of the exercise after all :) Who wants to show off how much more they know about this than me by saying what's right or wrong with these various ideas? *blush* :) They all seem functional ideas. But it seems to me they're all ideas that would be appropriate if this was a pgfoundry add-on for existing releases. But if this is an integrated feature targeting future releases we have more flexibility and there are more integrated approaches possible. How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders. Something like: CREATE TABLE foo ( col1 integer, NULL COLUMN, col2 integer ); If we care about the fact that columns never go away and are using (1) or (2), could also consider adding some additional meta-data to 8.4 such that something like vacuum can flag when a column no longer exists in any part of the data. All deleted columns move from 8.3 to 8.4, but one day the 8.5 upgrade could finally blow them away. There's already plenty of per-table catalog data being proposed to push into 8.4 for making future upgrades easier, this seems like a possible candidate for something to make space for there. As I just came to appreciate the problem I'm not sure about that. Hm, that's an interesting idea but I think it would work differently. If the column is dropped but there are tuples where the column is present then vacuum could squeeze the column out and set the null bit on each tuple instead. But that would involve a lot of I/O so it wouldn't be something we would want to do on a regular vacuum. Actually removing the attribute is downright hard. You would have to have the table locked, and squeeze the null bitmap -- and if you crash in the middle your data will be toast. I don't see much reason to worry about dropping the attribute though. The only cases where it matters are if you're near MaxAttrNum (1600 columns IIRC) or if it's the only null column (and in a table with more than 8 columns). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs head initdb hangs on unixware
[EMAIL PROTECTED] wrote: On Wed, 3 Dec 2008, Heikki Linnakangas wrote: Could you zip up the FSM file of that relation (a file called e.g 789_fsm), and send it over? Or the whole data directory, it shouldn't be that big. you get both. Thanks. Hmm, the FSM pages are full of zeros, as I would expect for a just-created relation. fsm_search_avail should've returned quickly at the top of the function in that case. Can you put a extra printf or something at the top of the function, to print all the arguments? And the value of fsmpage-fp_nodes[0]. BTW, this is an optimizer problem, not anything wrong with the code, but I'd hate to have a -g compiled postmaster in prod :) Yes, so it seems, although I wouldn't be surprised if it turns out to be a bug in the new FSM code either.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
Simon Riggs wrote: On Thu, 2008-12-04 at 17:57 +0900, Fujii Masao wrote: On Wed, Dec 3, 2008 at 3:38 PM, Fujii Masao [EMAIL PROTECTED] wrote: Do we need to worry about periodic renegotiation of keys in be-secure.c? What is keys you mean? See the notes in that file for explanation. Thanks! I would check it. The key is used only when we use SSL for the connection of replication. As far as I examined, secure_write() renegotiates the key if needed. Since walsender calls secure_write() when sending the WAL to the standby, the key is renegotiated periodically. So, I think that we don't need to worry about the obsolescence of the key. Understood. Is the periodic renegotiation of keys something that would interfere with the performance or robustness of replication? Is the delay likely to effect sync rep? I'm just checking we've thought about it. It will certainly add an extra piece of delay. But if you are worried about performance for it, you are likely not running SSL. Plus, if you don't renegotiate the key, you gamble with security. If it does have a negative effect on the robustness of the replication, we should just recommend against using it - or refuse to use - not disable renegotiation. /Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
On Thu, 2008-12-04 at 12:41 +0100, Magnus Hagander wrote: Understood. Is the periodic renegotiation of keys something that would interfere with the performance or robustness of replication? Is the delay likely to effect sync rep? I'm just checking we've thought about it. It will certainly add an extra piece of delay. But if you are worried about performance for it, you are likely not running SSL. Plus, if you don't renegotiate the key, you gamble with security. If it does have a negative effect on the robustness of the replication, we should just recommend against using it - or refuse to use - not disable renegotiation. I didn't mean to imply renegotiation might optional. I just wanted to check whether there is anything to worry about as a result of it, there may not be. *If* it took a long time, I would not want sync commits to wait for it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Gregory Stark napsal(a): How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders. Something like: CREATE TABLE foo ( col1 integer, NULL COLUMN, col2 integer ); You need to know a size of the attribute for fetchattr function. Zdenke -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] snapshot leak and core dump with serializable transactions
On Thu, Dec 4, 2008 at 2:25 AM, Alvaro Herrera [EMAIL PROTECTED]wrote: Yeah, that was plenty silly. Updated patch attached. Looks good me to, except for this warning: snapmgr.c: In function 'RegisterSnapshot': snapmgr.c:356: warning: unused variable 'snap' Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] cvs head initdb hangs on unixware
On Thu, 4 Dec 2008, Heikki Linnakangas wrote: Date: Thu, 04 Dec 2008 13:19:15 +0200 From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Zdenek Kotala [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] cvs head initdb hangs on unixware [EMAIL PROTECTED] wrote: On Wed, 3 Dec 2008, Heikki Linnakangas wrote: Could you zip up the FSM file of that relation (a file called e.g 789_fsm), and send it over? Or the whole data directory, it shouldn't be that big. you get both. Thanks. Hmm, the FSM pages are full of zeros, as I would expect for a just-created relation. fsm_search_avail should've returned quickly at the top of the function in that case. Can you put a extra printf or something at the top of the function, to print all the arguments? And the value of fsmpage-fp_nodes[0]. BTW, this is an optimizer problem, not anything wrong with the code, but I'd hate to have a -g compiled postmaster in prod :) Yes, so it seems, although I wouldn't be surprised if it turns out to be a bug in the new FSM code either.. As you can see in attached initdb.log, it seems fsm_search_avail is called repeatedly and args are sort of looping... -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery)Running in noclean mode. Mistakes will not be cleaned up. The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. The default database encoding has accordingly been set to SQL_ASCII. The default text search configuration will be set to english. creating directory /home/postgres/pgsql/src/test/regress/./tmp_check/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 32MB creating configuration files ... ok creating template1 database in /home/postgres/pgsql/src/test/regress/./tmp_check/data/base/1 ... entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering fsm_search_avail, minvalue = 6, advancenext = t, exclusive_lock_held =t,fsmpage-fp_nodes[0] = 4 entering fsm_search_avail, minvalue = 6, advancenext = f, exclusive_lock_held =f,fsmpage-fp_nodes[0] = 0 entering
[HACKERS] Assertion failure in new outer/semi/anti join code
The following query causes an assertion failure on CVS head: SELECT * FROM (SELECT 1 AS i) AS a WHERE NOT EXISTS (SELECT 1 WHERE 1 i); TRAP: FailedAssertion(!(!bms_is_empty(min_righthand)), File: initsplan.c, Line: 685) Looks like it's assuming there's at least one relation on each side of the join. Incidentally originally triggered with a VALUES clause but I think by the point the code runs that distinction is long gone. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Optimizing DISTINCT with LIMIT
As far as I have understood the following query SELECT DISTINCT foo FROM bar LIMIT baz is done by first sorting the input and then traversing the sorted data, ensuring uniqueness of output and stopping when the LIMIT threshold is reached. Furthermore, a part of the sort procedure is to traverse input at least one time. Now, if the input is large but the LIMIT threshold is small, this sorting step may increase the query time unnecessarily so here is a suggestion for optimization: If the input is sufficiently large and the LIMIT threshold sufficiently small, maintain the DISTINCT output by hashning while traversing the input and stop when the LIMIT threshold is reached. No sorting required and *at* *most* one read of input. Use case: Websites that needs to present small samples of huge queries fast. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Zdenek Kotala [EMAIL PROTECTED] writes: Gregory Stark napsal(a): How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders. Something like: CREATE TABLE foo ( col1 integer, NULL COLUMN, col2 integer ); You need to know a size of the attribute for fetchattr function. True. and the alignment. I guess those would have to be in the syntax as well since we don't even know what type those columns were previously. The type might not even exist any more. This is an internal syntax so I don't see any reason to bother making new keywords just to pretty up the syntax. I don't see a problem with just doing something like NULL COLUMN (2,1) The only constraint it seems to me is that it should be an unlikely string for someone to come up with accidentally from a simple syntax error. That's why I originally suggested two reserved keywords. But even NULL(2,1) seems like it would be fine. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pgsql: Properly unregister OpenSSL callbacks when libpq is done with
Bruce Momjian wrote: Kris Jurka wrote: Magnus Hagander wrote: Log Message: --- Properly unregister OpenSSL callbacks when libpq is done with it's connection. This is required for applications that unload the libpq library (such as PHP) in which case we'd otherwise have pointers to these functions when they no longer exist. Breaks the build with --enable-thread-safety and --with-openssl because of this typo. Thanks, applied. That fix is wrong. The comment clearly says the code *shouldn't* free the lockarray, so the proper fix is to remove those two lines. I have applied a patch that does this. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
tmp [EMAIL PROTECTED] writes: If the input is sufficiently large and the LIMIT threshold sufficiently small, maintain the DISTINCT output by hashning while traversing the input and stop when the LIMIT threshold is reached. No sorting required and *at* *most* one read of input. You mean like this? postgres=# explain select distinct x from i limit 5; QUERY PLAN --- Limit (cost=54.50..54.51 rows=1 width=304) - HashAggregate (cost=54.50..54.51 rows=1 width=304) - Seq Scan on i (cost=0.00..52.00 rows=1000 width=304) (3 rows) This will be in the upcoming 8.4 release. Versions since about 7.4 or so have been capable of producing this plan but not for DISTINCT, only for the equivalent GROUP BY query: postgres=# explain select x from i group by x limit 5; -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
Gregory Stark wrote: tmp [EMAIL PROTECTED] writes: If the input is sufficiently large and the LIMIT threshold sufficiently small, maintain the DISTINCT output by hashning while traversing the input and stop when the LIMIT threshold is reached. No sorting required and *at* *most* one read of input. You mean like this? postgres=# explain select distinct x from i limit 5; QUERY PLAN --- Limit (cost=54.50..54.51 rows=1 width=304) - HashAggregate (cost=54.50..54.51 rows=1 width=304) - Seq Scan on i (cost=0.00..52.00 rows=1000 width=304) (3 rows) Does that know to stop scanning as soon as it has seen 5 distinct values? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
Heikki Linnakangas [EMAIL PROTECTED] writes: Gregory Stark wrote: Does that know to stop scanning as soon as it has seen 5 distinct values? Uhm, hm. Apparently not :( postgres=# create or replace function v(integer) returns integer as $$begin raise notice 'called %', $1; return $1; end$$ language plpgsql volatile; CREATE FUNCTION postgres=# select distinct v(i) from generate_series(1,10) as a(i) limit 3; NOTICE: 0: called 1 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 2 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 3 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 4 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 5 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 6 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 7 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 8 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 9 LOCATION: exec_stmt_raise, pl_exec.c:2542 NOTICE: 0: called 10 LOCATION: exec_stmt_raise, pl_exec.c:2542 v --- 5 4 6 (3 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
Heikki Linnakangas [EMAIL PROTECTED] writes: Gregory Stark wrote: You mean like this? postgres=# explain select distinct x from i limit 5; QUERY PLAN --- Limit (cost=54.50..54.51 rows=1 width=304) - HashAggregate (cost=54.50..54.51 rows=1 width=304) - Seq Scan on i (cost=0.00..52.00 rows=1000 width=304) (3 rows) Does that know to stop scanning as soon as it has seen 5 distinct values? In principle, if there are no aggregate functions, then nodeAgg could return a row immediately upon making any new entry into the hash table. Whether it's worth the code uglification is debatable ... I think it would require a third major pathway through nodeAgg. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] snapshot leak and core dump with serializable transactions
Pavan Deolasee escribió: On Thu, Dec 4, 2008 at 2:25 AM, Alvaro Herrera [EMAIL PROTECTED]wrote: Yeah, that was plenty silly. Updated patch attached. Looks good me to, except for this warning: Applied. Many thanks for the exhaustive testing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
Hello. I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a column type for another table (dt): CREATE TABLE ct (id INTEGER); CREATE TABLE dt (id INTEGER, c ct); INSERT INTO dt VALUES(1, '(666)'); SELECT * FROM dt; -- (1, '(666)') ALTER TABLE ct ADD COLUMN n INTEGER; SELECT * FROM dt; -- (1, '(666,)') You see, '(666,)' means that the new field is added successfully. But, if I declare ct as a COMPOSITE type (not a table), it is not permitted to ALTER this type (Postgres says that there are dependensies on ct). Why?
[HACKERS] Can't start postmaster on -HEAD
This is a fresh snapshot -- is also happened on Nov 28th snapshot, too: -bash-3.2$ pg_ctl start server starting -bash-3.2$ LOG: authentication option not in name=value format: sameuser CONTEXT: line 70 of configuration file /var/lib/pgsql/data/pg_hba.conf FATAL: could not load pg_hba.conf Line 70 is: local all all ident sameuser (Ran initdb via RPM init script btw). Am I missing something? -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Simple postgresql.conf wizard
Gregory Stark [EMAIL PROTECTED] wrote: That sounds like it would be an interesting query to analyze in more detail. Is there any chance to could run the complete graph and get a chart of analyze times for all statistics values from 1..1000 ? And log the explain plans to a file so we can look for at what statistics targets the plan changed? Or if the data is public I would be interested in looking at doing it if you want to send it to me. There are some very big tables in that query which contain some confidential data. It would be hard do a lot of runs at high default_statistics_target values because the database analyze time goes so high. If I pick out which tables are used by the query, I might be able to put a script together which loops through analyze of those tables with different targets and capturing run time. There are two problems -- finding the time to set this up, and finding server time windows where other things wouldn't be distorting the results. If you could help with setting up the test script, that would go a long way toward solving the first problem. I think I could reserve a smaller multi-CPU machine with identical data but slower CPUs to run the test. I'll attach the query and plan. You'll note that the query looks a little odd, especially all the (1=1) tests. This is because the application allows users to plug in a variety of selection criteria, and any that aren't used are stubbed out that way. I picked one that was not too atypical for the 300,000 runs per day. I intentionally didn't clean up the white space, but left it just as it was emitted by our framework, in case any of that affected parse/plan time. If you need schema info not obvious from the plan, let me know. -Kevin name-search.sql Description: Binary data QUERY PLAN Unique (cost=473.48..473.57 rows=2 width=173) - Sort (cost=473.48..473.49 rows=2 width=173) Sort Key: caseNo, filingDate, countyName, statusCodeDescr, nameF, nameM, nameL, suffix, dob, caption, countyNo, caseType, isSeal, isPartySeal, lastModified, searchName, isPetitionerSeal - Append (cost=0.00..473.47 rows=2 width=173) - Subquery Scan *SELECT* 1 (cost=0.00..235.74 rows=1 width=169) - Nested Loop (cost=0.00..235.73 rows=1 width=169) - Nested Loop (cost=0.00..126.19 rows=1 width=163) - Nested Loop Left Join (cost=0.00..124.31 rows=1 width=153) - Nested Loop Left Join (cost=0.00..120.71 rows=1 width=153) - Nested Loop Left Join (cost=0.00..117.82 rows=1 width=145) Filter:
[HACKERS] [patch] pg_upgrade script for 8.3-8.4
Hi all,
I attached pg_upgrade.sh script patch which works now for 8.3-8.4. It is
contrib module in contrib/pg_upgrade directory. Just make/make install and it works.
There are two changes from previous 8.1-8.2.
1) pg_largeobject is also upgraded
2) added check for dropped column
And now how to run a test. At first DO NOT test it on production database :-).
Please, do binary copy and start test on binary copy. Binary copy is important
because it contains a lot of updated, dead tuples and other interesting things.
Script is easy to use. You need only setup access to old and new binaries and
old and new data directory. I use following script:
#!/bin/bash
export PG_OLD_DATADIR=/zfs_test/postgres_83/data_83
export PG_OLD_BINDIR=/usr/postgres/8.3/bin
export PG_NEW_DATADIR=/zfs_test/postgres_83/data_84_upg
export PG_NEW_BASEDIR=/var/tmp/pg84_upg/
ksh ${PG_NEW_BASEDIR}/bin/pg_upgrade.sh -m
you can use also switches - try pg_upgrade.sh --help
The script contains some magic to handle following issues.
1) Keep relfileid of toast file same. It is important because toast pointer
contains relfileid. Currently script creates fake files with same number to
protect postgresql to create new relation with this refileid. It works but by my
opinion it is not much robust. I suggest to use following syntax:
create table foo (id int) with (relfileid=16544, reltoastid=11655,
reltoastidx=16543)
pg_dump(all) will be extended to dump this information on a request.
2) problem with dropped columns. PostgreSQL do not remove column physically from
the disk. It only marks that column as deleted and the column is ignored in the
future. But pg_dump dumps only valid column. There is idea from greggreg to
extend create table syntax with padding column:
CREATE TABLE foo (
col1 integer,
NULL COLUMN(2,0),
col2 integer
);
3) tablespace and database oid mapping. It is similar with relations. Another
problem with tablespace location is that CREATE TABLESPACE checks if directory
is empty and it fails when it contains any file/directory. Unfortunately, it is
no much good for upgrade because usually tablespace is mountpoint and any
copy/move outside a mountpoint is not wanted.
Suggested sugar syntax is:
CREATE DATABASE foobar WITH ID=17012;
CREATE TABLESPACE bar LOCATION '/dev/null/' ID=15543 IGNORECONTENT;
4) script is written in ksh. It has several problems. First is that it does not
work on win, second is that it needs extra magic to escape any stupid object
names. Bruce has suggested to rewrite it to PERL. It is maybe good idea but I'm
not really PERL guru - any volunteers?
By the way why we accept whole ASCII in name datatype (including 0-31)?
Comments, thoughts?
Thanks Zdenek
pgu.patch.gz
Description: GNU Zip compressed data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Kevin Grittner [EMAIL PROTECTED] writes: There are some very big tables in that query which contain some confidential data. oh well. I'll attach the query and plan. You'll note that the query looks a little odd, especially all the (1=1) tests. That is interesting. I seem to recall Tom is resistant to trying to optimize such queries but actually I've written lots of queries like that myself so I find them interesting. I'll look at the query and see if I can write a similar one using dbt3. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can't start postmaster on -HEAD
On Thursday 04 December 2008 17:19:28 Devrim GÜNDÜZ wrote: This is a fresh snapshot -- is also happened on Nov 28th snapshot, too: -bash-3.2$ pg_ctl start server starting -bash-3.2$ LOG: authentication option not in name=value format: sameuser CONTEXT: line 70 of configuration file /var/lib/pgsql/data/pg_hba.conf FATAL: could not load pg_hba.conf Line 70 is: local all all ident sameuser (Ran initdb via RPM init script btw). Am I missing something? The hba file format has changed. Your simplest fix would be to remove the sameuser. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can't start postmaster on -HEAD
On Thu, 2008-12-04 at 19:20 +0200, Peter Eisentraut wrote: The hba file format has changed. Your simplest fix would be to remove the sameuser. This is a file created by initdb -- so is it a bug? -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Can't start postmaster on -HEAD
Devrim GÜNDÜZ wrote: On Thu, 2008-12-04 at 19:20 +0200, Peter Eisentraut wrote: The hba file format has changed. Your simplest fix would be to remove the sameuser. This is a file created by initdb -- so is it a bug? Do the RPM initscript by any chance pass something like '-A ident sameuser' to initdb? If so, that's where the fix needs to go. Initdb defaults to trust... //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can't start postmaster on -HEAD
Devrim GÜNDÜZ wrote: On Thu, 2008-12-04 at 19:20 +0200, Peter Eisentraut wrote: The hba file format has changed. Your simplest fix would be to remove the sameuser. This is a file created by initdb -- so is it a bug? If it were a bug in our sources the buildfarm would be broken, but it isn't. Does your package patch the default pg_hba.conf, or initdb? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: default values for function parameters
It's committed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Gregory Stark [EMAIL PROTECTED] wrote:
And log the explain plans to a
file so we can look for at what statistics targets the plan changed?
Well, I can give you explain analyze output for
default_statistics_target 10 and 50, for whatever that's worth.
Unfortunately I blew my save from the first run with target 50, but it
ran much faster than the first run of target 10 (attached), I think it
was about 2.8 seconds. That may or may not be due to the right pages
being cached by coincidence. The machine with the target 50 run was
serving the web app at the time, so there was significant other load,
while the other was idle except as a replicaton target at the time of
the run.
-Kevin
Unique (cost=457.72..457.81 rows=2 width=171) (actual
time=94927.370..94932.613 rows=2388 loops=1)
- Sort (cost=457.72..457.73 rows=2 width=171) (actual
time=94927.366..94928.548 rows=2396 loops=1)
Sort Key: caseNo, filingDate, countyName, statusCodeDescr,
nameF, nameM, nameL, suffix, dob, caption, countyNo, caseType,
isSeal, isPartySeal, lastModified, searchName, isPetitionerSeal
- Append (cost=0.00..457.71 rows=2 width=171) (actual
time=108.847..94913.855 rows=2396 loops=1)
- Subquery Scan *SELECT* 1 (cost=0.00..227.86 rows=1
width=169) (actual time=108.846..93393.253 rows=2315 loops=1)
- Nested Loop (cost=0.00..227.85 rows=1 width=169)
(actual time=108.843..93383.339 rows=2315 loops=1)
- Nested Loop (cost=0.00..122.30 rows=1
width=163) (actual time=97.389..93174.181 rows=2315 loops=1)
- Nested Loop Left Join (cost=0.00..120.42
rows=1 width=153) (actual time=86.182..93144.680 rows=2315 loops=1)
- Nested Loop Left Join
(cost=0.00..116.82 rows=1 width=153) (actual time=75.841..73181.110 rows=2315
loops=1)
- Nested Loop Left Join
(cost=0.00..113.88 rows=1 width=145) (actual time=60.527..52107.799 rows=2315
loops=1)
Filter:
(((WPCT.profileName IS NOT NULL) OR (((C.caseType)::text = ANY
(('{PA,JD}'::character varying[])::text[])) AND (NOT C.isConfidential)))
AND (((WPCT.profileName)::text 'PUBLIC'::text) OR
((C.caseType)::text 'FA'::text) OR ((C.wcisClsCode)::text
'40501'::text)))
- Nested Loop
(cost=0.00..113.56 rows=1 width=146) (actual time=53.104..52045.586 rows=2601
loops=1)
Join Filter:
(P.partyType)::text = ANY (('{JV,CH}'::character varying[])::text[]))
AND ((C.caseType)::text = 'ZZ'::text)) OR ((P.partyType)::text ALL
(('{JV,CH}'::character varying[])::text[]))) AND (((C.caseType)::text
ALL (('{CF,CI,CM,CT,FO,TR}'::character varying[])::text[])) OR
((P.partyType)::text = 'DE'::text)) AND C.caseType)::text = ANY
(('{JA,JC,JG,JM,JO,JV,JI,TP}'::character varying[])::text[])) AND
((P.partyType)::text = ANY (('{CH,JV}'::character varying[])::text[]))) OR
(((C.caseType)::text ALL (('{JA,JC,JG,JM,JO,JV,JI,TP}'::character
varying[])::text[])) AND ((P.partyType)::text ALL (('{CH,JV}'::character
varying[])::text[] AND (((P.partyType)::text ALL
(('{PE,PL,JP}'::character varying[])::text[])) OR C.filingDate)::date
'2008-11-01'::date) OR ((C.wcisClsCode)::text '30709'::text)) AND
(((C.caseType)::text ALL (('{CV,FA}'::character varying[])::text[])) OR
((C.wcisClsCode)::text '30711'::text) OR (NOT (subplan))
- Index Scan using
Party_SearchName on Party P (cost=0.00..3.21 rows=1 width=81) (actual
time=6.878..159.418 rows=4097 loops=1)
Index Cond:
(((searchName)::text = 'HILL,J'::character varying) AND
((searchName)::text 'HILL,K'::character varying))
Filter: ((NOT
isSeal) AND ((searchName)::text ~~ 'HILL,J%'::text))
- Index Scan using
Case_pkey on Case C (cost=0.00..5.01 rows=1 width=87) (actual
time=12.607..12.625 rows=1 loops=4097)
Index Cond:
(((C.countyNo)::smallint = (P.countyNo)::smallint) AND
((C.caseNo)::text = (P.caseNo)::text))
Filter:
((isExpunge true) AND (NOT (subplan)))
SubPlan
- Index Scan
using HiddenCase_pkey on HiddenCase HCA (cost=0.00..2.14 rows=1 width=0)
(actual time=4.487..4.487 rows=0 loops=4097)
Index
Cond: (((countyNo)::smallint = ($0)::smallint) AND ((caseNo)::text =
($1)::text))
Re: [HACKERS] WIP: default values for function parameters
2008/12/4 Peter Eisentraut [EMAIL PROTECTED]: It's committed. great, thank you Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith wrote: I'm not the sort to be too concerned myself that the guy who thinks he's running a DW on a system with 64MB of RAM might get bad settings, but it's a fair criticism to point that out as a problem. In defense of thinking about very small configurations, I've seen many cases where an enterprise-software salesperson's laptop is running a demo - either in a small virtual machine in the laptop, or on an overloaded windows box.Even though the customer might end up running with 64GB, the sales demo's more likely to be 64MB. In some ways, I'd be more interested in seeing automation of those than the large production systems. Large production systems are likely to have an administrator who's paid to read the documentation and learn how to configure the database. OTOH there tends to be less DBA time available to tune the smaller demo instances that comego as sales people upgrade their laptops; so improved automation would be much appreciated there. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 2008-12-04 at 10:20 -0800, Ron Mayer wrote: Greg Smith wrote: I'm not the sort to be too concerned myself that the guy who thinks he's running a DW on a system with 64MB of RAM might get bad settings, but it's a fair criticism to point that out as a problem. In defense of thinking about very small configurations, I've seen many cases where an enterprise-software salesperson's laptop is running a demo - either in a small virtual machine in the laptop, or on an overloaded windows box.Even though the customer might end up running with 64GB, the sales demo's more likely to be 64MB. Although I get your point, that is a job for sqllite not postgresql. PostgreSQL is not a end all be all solution and it is definitely not designed to be embedded which is essentially what you are suggesting with that kind of configuration. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Well that's a bit if hyperbole. There's a gulf of difference between an embedded use case where it should fit within an acceptable footprint for a desktop app component of maybe a megabyte or so of ram and disk - if we're generous and saying it should run comfortably without having to spec out special server hardware for a demo. That said 64mb of ram seems like hyperbole too. My NSLU2 has 32mb... greg On 4 Dec 2008, at 06:28 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Thu, 2008-12-04 at 10:20 -0800, Ron Mayer wrote: Greg Smith wrote: I'm not the sort to be too concerned myself that the guy who thinks he's running a DW on a system with 64MB of RAM might get bad settings, but it's a fair criticism to point that out as a problem. In defense of thinking about very small configurations, I've seen many cases where an enterprise-software salesperson's laptop is running a demo - either in a small virtual machine in the laptop, or on an overloaded windows box.Even though the customer might end up running with 64GB, the sales demo's more likely to be 64MB. Although I get your point, that is a job for sqllite not postgresql. PostgreSQL is not a end all be all solution and it is definitely not designed to be embedded which is essentially what you are suggesting with that kind of configuration. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
In defense of thinking about very small configurations, I've seen many cases where an enterprise-software salesperson's laptop is running a demo - either in a small virtual machine in the laptop, or on an overloaded windows box.Even though the customer might end up running with 64GB, the sales demo's more likely to be 64MB. Although I get your point, that is a job for sqllite not postgresql. PostgreSQL is not a end all be all solution and it is definitely not designed to be embedded which is essentially what you are suggesting with that kind of configuration. It's unlikely that someone would want to write a demo version of the software that runs on a completely different database than the production one... it's also totally unnecessary because PostgreSQL runs great on small systems. In fact, it runs great on small systems with NO TUNING AT ALL. That's exactly why a wizard is needed to set values for systems that aren't small. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can't start postmaster on -HEAD
On Thu, 2008-12-04 at 18:38 +0100, Magnus Hagander wrote: Do the RPM initscript by any chance pass something like '-A ident sameuser' to initdb? If so, that's where the fix needs to go. Initdb defaults to trust... Good catch. So, AFAICS running initdb with -A ident will do the trick. BTW, should I worry about thi the krb5 message below? $ psql -U postgres psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found FATAL: Ident authentication failed for user postgres Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Simple postgresql.conf wizard
Joshua D. Drake wrote: On Thu, 2008-12-04 at 10:20 -0800, Ron Mayer wrote: Greg Smith wrote: I'm not the sort to be too concerned myself that the guy who thinks he's running a DW on a system with 64MB of RAM might get bad settings, but it's a fair criticism to point that out as a problem. In defense of thinking about very small configurations, I've seen many cases where an enterprise-software salesperson's laptop is running a demo - either in a small virtual machine in the laptop, or on an overloaded windows box.Even though the customer might end up running with 64GB, the sales demo's more likely to be 64MB. Although I get your point, that is a job for sqllite not postgresql. PostgreSQL is not a end all be all solution and it is definitely not designed to be embedded which is essentially what you are suggesting with that kind of configuration. But these sales people are selling a postgres based product. It'd be both much less convincing to demo a different application stack; as well as not a very productive use of the developer's time. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 2008-12-04 at 10:55 -0800, Ron Mayer wrote: Joshua D. Drake wrote: Although I get your point, that is a job for sqllite not postgresql. PostgreSQL is not a end all be all solution and it is definitely not designed to be embedded which is essentially what you are suggesting with that kind of configuration. But these sales people are selling a postgres based product. It'd be both much less convincing to demo a different application stack; as well as not a very productive use of the developer's time. Fair enough, then make sure you are demoing on a platform that can handle PostgreSQL :) Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
In principle, if there are no aggregate functions, then nodeAgg could return a row immediately upon making any new entry into the hash table. Whether it's worth the code uglification is debatable ... I think it would require a third major pathway through nodeAgg. Regarding whether it's worth the effort: In each of my three past jobs (all using postgresql) I have met several queries that would fetch a small subset of a large - even huge - input. I think that types of queries are relatively common out there, but if they are executed for e.g. a web-client it is simply a no-go with the current late LIMIT evaluation. Also, it is my impression that many people use LIMIT to minimize the evaluation time of sub queries from which the outer query only needs a small subset of the sub query output. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] pg_upgrade script for 8.3-8.4
On Thu, Dec 04, 2008 at 05:57:01PM +0100, Zdenek Kotala wrote: Hi all, I attached pg_upgrade.sh script patch which works now for 8.3-8.4. It is contrib module in contrib/pg_upgrade directory. Just make/make install and it works. Kudos! 4) script is written in ksh. It has several problems. First is that it does not work on win, second is that it needs extra magic to escape any stupid object names. Bruce has suggested to rewrite it to PERL. It is maybe good idea but I'm not really PERL guru - any volunteers? I'll give it a whirl today :) Cheers, David (oh, and it's Perl ;) -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Joshua D. Drake [EMAIL PROTECTED] wrote: Fair enough, then make sure you are demoing on a platform that can handle PostgreSQL :) There are a lot of good reasons for people to be running an instance of PostgreSQL on a small machine, running it on a machine with other software, or running many clusters of PostgreSQL on a single machine. It may not be possible for this tool to generate useful values for all of these situations, but it seems to me that should be viewed as a limitation of the tool, rather than some moral failing on the part of the people with those needs. Let's cover what we can, document the limitations, and avoid any user-hostile tones -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
tmp [EMAIL PROTECTED] writes: Regarding whether it's worth the effort: In each of my three past jobs (all using postgresql) I have met several queries that would fetch a small subset of a large - even huge - input. I think that types of queries are relatively common out there, but if they are executed for e.g. a web-client it is simply a no-go with the current late LIMIT evaluation. Also, it is my impression that many people use LIMIT to minimize the evaluation time of sub queries from which the outer query only needs a small subset of the sub query output. I've seen lots of queries which only pull a subset of the results too -- but it's always a specific subset. So that means using ORDER BY or a WHERE clause to control it. In this example the subset returned is completely arbitrary. That's a much finer slice of queries. I would tend to think it's worth it myself. I can see cases where the subset selected doesn't really matter -- for instance if you're only testing whether there are at least a certain number of distinct values. Or if you're using up some inventory and it's not important what order you use them up only that you fetch some candidate inventory and process them. But I can also see Tom's reluctance. It's a fair increase in the amount of code to maintain in that file for a pretty narrow use case. On the other hand it looks like it would be all in that file. The planner wouldn't have to do anything special to set it up which is nice. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [patch] pg_upgrade script for 8.3-8.4
Zdenek Kotala napsal(a): Hi all, I attached pg_upgrade.sh script patch which works now for 8.3-8.4. It is contrib module in contrib/pg_upgrade directory. Just make/make install and it works. I forget to mention that default datetime format is different now. Please, use same datetime format for 8.3 and 8.4. I will add check. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 2008-12-04 at 14:05 -0600, Kevin Grittner wrote: Joshua D. Drake [EMAIL PROTECTED] wrote: Fair enough, then make sure you are demoing on a platform that can handle PostgreSQL :) There are a lot of good reasons for people to be running an instance of PostgreSQL on a small machine, running it on a machine with other software, or running many clusters of PostgreSQL on a single machine. It may not be possible for this tool to generate useful values for all of these situations, but it seems to me that should be viewed as a limitation of the tool, rather than some moral failing on the part of the people with those needs. Let's cover what we can, document the limitations, and avoid any user-hostile tones I didn't say don't run on a small machine :) I said make sure you run on one that is up for the job. There is a difference. Joshua D. Drake -Kevin -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 4 Dec 2008, Ron Mayer wrote: OTOH there tends to be less DBA time available to tune the smaller demo instances that comego as sales people upgrade their laptops; so improved automation would be much appreciated there. I have a TODO list for things that might be interesting to add to a V2.0 version of this tool. I just added an item to there for extending the tuning model usefully into working on systems with smaller amounts of RAM. I'm not opposed to the idea, just don't have any background doing that and I'm trying to stay focused on the more common big-machine problems for the first release. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1202
2. EXPLAIN ANALYZE VERBOSE shows buffer statistics in 'actual' section. I do not get the point of VERBOSE. As far as I understand, explain analyze (without verbose) will anyway add overhead for calculation of gets/hits/cpu. Why discard that information in non verbose mode? Just to make the investigation more complex? Write-counters are not included because I think they are not so useful. Never say never. I guess they (or just one counter for accumulated writes) could be useful for monitoring operations that spill to the disk. For instance, PostgreSQL does not show the amount of temp used for the join. buffer_gets;/* # of buffer hits */ buffer_hits;/* # of buffer gets */ buffile_reads; /* # of buffile reads */ I guess it makes sense expanding buffile reads into buffer file reads or just file reads Here is an sample output. We'd better to add a linebreak before the 'actual' section because the line is too wide to display. I wish there was a way to get the results of explain into some table. I wish it was the default output format. That would make life of pgAdmin easier, and improve readability even in psql. Do not you think there is something wrong with having cost=... rows=... loops=... in each and every row? ResetBufferUsage() is save the current counters in global variables as baseline and buffer statistics are measured in difference from them because the counters are used by struct Instrumentation. That would definitely work well for Instrumentation (it will not notice resetBufferUsage any more), however that will not isolate the guys who do the reset. I am afraid the idea of having api for reset is broken and it makes sense removing that function. However it looks like it is out of scope of this patch. Regards, Vladimir Sitnikov
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith [EMAIL PROTECTED] wrote: On Thu, 4 Dec 2008, Ron Mayer wrote: OTOH there tends to be less DBA time available to tune the smaller demo instances that comego as sales people upgrade their laptops; so improved automation would be much appreciated there. I have a TODO list for things that might be interesting to add to a V2.0 version of this tool. I just added an item to there for extending the tuning model usefully into working on systems with smaller amounts of RAM. I'm not opposed to the idea, just don't have any background doing that and I'm trying to stay focused on the more common big-machine problems for the first release. I think there needs to be some easy way to choose an option which yields a configuration similar to what we've had in recent production releases -- something that will start up and allow minimal testing on even a small machine. It also occurred to me that if initdb is generating its initial configuration with this, some special handling might be needed for the make check runs. It isn't unusual to want to do a build and check it on a production server. If the generated configuration used in regression tests is assuming it owns the machine there could be a problem. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
On Thu, 4 Dec 2008, Gregory Stark wrote: They all seem functional ideas. But it seems to me they're all ideas that would be appropriate if this was a pgfoundry add-on for existing releases. I was mainly trying to target things that would be achievable within the context of the existing shell script. I think that we need such a script that does 100% of the job and can be tested ASAP. If it's possible to slice the worst of the warts off later, great, but don't drop focus from getting a potential candidate release done first. How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders...This is an internal syntax so I don't see any reason to bother making new keywords just to pretty up the syntax. I don't see a problem with just doing something like NULL COLUMN (2,1) It's a little bit ugly, but given the important of in-place upgrade I would think this is a reasonable hack to consider; two questions: -Is there anyone whose clean code sensibilities are really opposed to adding such a syntax into the 8.4 codebase? -If nobody has a beef about it, is this something you could draft a patch for? I'm going to be busy with the upgrade script stuff and don't know much about extending in this area anyway. Actually removing the attribute is downright hard. You would have to have the table locked, and squeeze the null bitmap -- and if you crash in the middle your data will be toast. Not being familiar with the code, my assumption was that it would be possible to push all the tuples involved off to another page as if they'd been updated, with WAL logging and everything, similarly to the ideas that keep getting kicked around for creating extra space for header expansion. Almost the same code really, just with the target of moving everything that references the dead column rather than moving just enough to create the space needed. Actually doing the upgrade on the page itself does seem quite perilous. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 4 Dec 2008, Kevin Grittner wrote: I think there needs to be some easy way to choose an option which yields a configuration similar to what we've had in recent production releases -- something that will start up and allow minimal testing on even a small machine. But that's the goal of what comes out of initdb already; I'm missing how that is something this script would even get involved in. Is your suggestion to add support for a minimal target that takes a tuned-up configuration file and returns it to that state, or did you have something else in mind? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1202
Vladimir Sitnikov [EMAIL PROTECTED] writes: I wish there was a way to get the results of explain into some table. I wish it was the default output format. That would make life of pgAdmin easier, and improve readability even in psql. Do not you think there is something wrong with having cost=... rows=... loops=... in each and every row? A number of people have suggesting we switch to XML. An alternative would be to build up a tuplestore of data and then send that to the client in a separate result set. That's kind of nice because it would give us a way to send both the real results and the explain results. And at least we already have an api for accessing result sets. Oracle's approach is to have the explain command stuff the results into a table. That has advantages for tools, especially if you want to be able to look at plans generated by other sessions. But it's pretty awkward for the simple case. I'm sure there are dozens of ways to skin this cat. Anyone have any more? We probably just have to pick one and run with it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith [EMAIL PROTECTED] wrote: On Thu, 4 Dec 2008, Kevin Grittner wrote: I think there needs to be some easy way to choose an option which yields a configuration similar to what we've had in recent production releases -- something that will start up and allow minimal testing on even a small machine. But that's the goal of what comes out of initdb already; I'm missing how that is something this script would even get involved in. Is your suggestion to add support for a minimal target that takes a tuned-up configuration file and returns it to that state, or did you have something else in mind? Perhaps I misunderstood some earlier post -- I had gotten the impression that initdb was going to use this utility for the initial postgresql.conf file. If that's not happening, then you can ignore my last post as noise, with my apologies. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Not being familiar with the code, my assumption was that it would be possible to push all the tuples involved off to another page as if they'd been updated, with WAL logging and everything, similarly to the ideas that keep getting kicked around for creating extra space for header expansion. Almost the same code really, just with the target of moving everything that references the dead column rather than moving just enough to create the space needed. Actually doing the upgrade on the page itself does seem quite perilous. For in-place upgrade, you can tell which pages have been converted and which have not by looking at the page header, so you can put a switch into the code to handle each version appropriately. I don't think that would be possible in this case without purpose-built infrastructure. It might be possible to lock out writers only and rewrite the table in a new file though, disk space permitting. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [postgis-devel] CLUSTER in 8.3 on GIST indexes break
Mark Cave-Ayland wrote: On Thu, 2008-12-04 at 13:51 -0800, Kevin Neufeld wrote: Wow, that's bad. I just updated to PostgreSQL 8.3.5 from 8.3.3 and I now get the same thing. test=# create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 1); SELECT test=# create index tmp_geom_idx on tmp using gist (the_geom); CREATE INDEX test=# analyze tmp; ANALYZE test=# select count(*) from tmp; count --- 1 (1 row) test=# cluster tmp using tmp_geom_idx; CLUSTER test=# analyze tmp; ANALYZE test=# select count(*) from tmp; count --- 0 (1 row) test=# select version(); version --- PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) (1 row) test=# select postgis_full_version(); postgis_full_version --- POSTGIS=1.4.0SVN GEOS=3.1.0-CAPI-1.5.0 PROJ=Rel. 4.4.9, 29 Oct 2004 (procs from 1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 need upgrade) (1 row) -- Kevin Yuck. If you can definitely confirm that this works on 8.3.3 but not 8.3.5 then it's probably work a post on -hackers :( ATB, Mark. Confirmed. It seems something changed in GIST from 8.3.3 to 8.3.5 -- 8.3.3 CLUSTER on GIST index works fine. test=# create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 1); SELECT test=# create index tmp_geom_idx on tmp using gist (the_geom); CREATE INDEX test=# analyze tmp; ANALYZE test=# select count(*) from tmp; count --- 1 (1 row) test=# cluster tmp using tmp_geom_idx; CLUSTER test=# analyze tmp; ANALYZE test=# select count(*) from tmp; count --- 1 (1 row) test=# select version(); version --- PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) (1 row) test=# select postgis_full_version(); postgis_full_version --- POSTGIS=1.4.0SVN GEOS=3.1.0-CAPI-1.5.0 PROJ=Rel. 4.4.9, 29 Oct 2004 (procs from 1.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 need upgrade) (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
Greg Smith [EMAIL PROTECTED] writes: On Thu, 4 Dec 2008, Gregory Stark wrote: They all seem functional ideas. But it seems to me they're all ideas that would be appropriate if this was a pgfoundry add-on for existing releases. I was mainly trying to target things that would be achievable within the context of the existing shell script. I think that we need such a script that does 100% of the job and can be tested ASAP. If it's possible to slice the worst of the warts off later, great, but don't drop focus from getting a potential candidate release done first. I suggested it because I thought it would be easier and less messy though. How about adding a special syntax for CREATE TABLE which indicates to include a dropped column in that position? Then pg_dump could have a -X option to include those columns as placeholders...This is an internal syntax so I don't see any reason to bother making new keywords just to pretty up the syntax. I don't see a problem with just doing something like NULL COLUMN (2,1) It's a little bit ugly, but given the important of in-place upgrade I would think this is a reasonable hack to consider; two questions: Well it's ugly but it seems to me that it would be a whole lot more ugly to have a whole pile of code which tries to adjust the table definitions to insert dropped columns after the fact. -Is there anyone whose clean code sensibilities are really opposed to adding such a syntax into the 8.4 codebase? Incidentally I got this wrong in my previous email. If we're aiming at 8.4-8.5 as the first in-place update then we actually don't need this in 8.4 at all. The recommended path is always to use the *new* pg_dump to dump the old database even for regular updates and there would be no problem making that mandatory for an in-place update. So as long as the 8.5 pg_dump knows how to dump this syntax and the 8.5 create parser knows what to do with it then that would be sufficient. -If nobody has a beef about it, is this something you could draft a patch for? I'm going to be busy with the upgrade script stuff and don't know much about extending in this area anyway. It doesn't sound hard off the top of my head. CREATE TABLE is a bit tricky though, I'll check to make sure it works. Actually removing the attribute is downright hard. You would have to have the table locked, and squeeze the null bitmap -- and if you crash in the middle your data will be toast. Not being familiar with the code, my assumption was that it would be possible to push all the tuples involved off to another page as if they'd been updated, with WAL logging and everything, similarly to the ideas that keep getting kicked around for creating extra space for header expansion. Almost the same code really, just with the target of moving everything that references the dead column rather than moving just enough to create the space needed. Actually doing the upgrade on the page itself does seem quite perilous. I'm sorry, I think I misunderstood the original idea, what you're talking about makes a lot more sense now. You want to save the space of the dead column by replacing it with NULL, not remove it from the table definition. That should be possible to do in vacuum or some other operation that has the vacuum lock without locking the table or introducing new tuples. Whatever does it does need the tuple descriptor which Vacuum normally doesn't need though. The page conversion time might be a good time since it'll need to deform all the tuples and re-form them anyways. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1202
Vladimir Sitnikov [EMAIL PROTECTED] writes: I wish there was a way to get the results of explain into some table. I wish it was the default output format. That would make life of pgAdmin easier, and improve readability even in psql. Do not you think there is something wrong with having cost=... rows=... loops=... in each and every row? A number of people have suggesting we switch to XML. I do not see much benefit of XML: * XML is not human-readable * Plain old result set is even easier to process since it is the main PostgreSQL interface at this point The only benefit of XML I could imagine is it could provide a nicer markup for sort/hash/etc nodes. It is not that nice to have a column sort method that would be empty nearly for all the rows. At the same time it looks fine to have a column with xml inside for any additional information execution node wants provide (like topN-allrows sort / number of batches in hash join or whatever) An alternative would be to build up a tuplestore of data and then send that to the client in a separate result set. That's kind of nice because it would give us a way to send both the real results and the explain results. And at least we already have an api for accessing result sets. Sounds good. As for me, current output of explain is not very easy to read: it suits well only for find timings for particular node workflow only (I mean, the source is a particular node, the result is timings/rows/buffers/etc). However from my point of view, when it comes to query tuning the main workflow is find node by suspicious timings. If all the relevant data were displayed in the same column it would be easier to read. Consider all the row counts in the very first column. Oracle's approach is to have the explain command stuff the results into a table. That has advantages for tools, especially if you want to be able to look at plans generated by other sessions. I do not believe that workflow makes sense. I have never ever thought of it. External table makes sense if you have several output formats (say, create a formatting function for psql and let pgAdmin format the plan on its own) Regards, Vladimir Sitnikov
Re: [HACKERS] Simple postgresql.conf wizard
Greg Smith wrote: On Thu, 4 Dec 2008, Ron Mayer wrote: OTOH there tends to be less DBA time available to tune the smaller demo instances that comego as sales people upgrade their laptops; so improved automation would be much appreciated there. I have a TODO list for things that might be interesting to add to a V2.0 version of this tool. I just added an item to there for extending the tuning model usefully into working on systems with smaller amounts of RAM. I'm not opposed to the idea, just don't have any background doing that and I'm trying to stay focused on the more common big-machine problems for the first release. We *have* a configuration for small amounts of RAM; it's our current default configuration. However, I take the point that the workstation calculations should work down to 128MB of system RAM. I'll check. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, Dec 4, 2008 at 5:11 PM, Greg Smith [EMAIL PROTECTED] wrote: On Thu, 4 Dec 2008, Kevin Grittner wrote: I think there needs to be some easy way to choose an option which yields a configuration similar to what we've had in recent production releases -- something that will start up and allow minimal testing on even a small machine. But that's the goal of what comes out of initdb already; I'm missing how that is something this script would even get involved in. Is your suggestion to add support for a minimal target that takes a tuned-up configuration file and returns it to that state, or did you have something else in mind? I humbly suggest that the memory-related settings output by the tool don't need to match what initdb outputs. But the values of checkpoint_segments, constraint_exclusion, and default_statistics_target probably should, given a small mixed-mode database. You've probably all figured out by now that I, personally, in my own opinion, think that default_statistics_target = 10 is just fine for such a database, but if a decision is made to change that number, so be it. Just let's please change it both places, rather than letting contrib/pgtune be a backdoor to get around not liking what initdb does. And similarly with the other parameters... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] V2 of PITR performance improvement for 8.4
Hi, On Thu, Dec 4, 2008 at 6:11 PM, Simon Riggs [EMAIL PROTECTED] wrote: On Wed, 2008-12-03 at 14:22 +0900, Koichi Suzuki wrote: There's clearly a huge gain using prefetch, when we have full_page_writes = off. But that does make me think: Why do we need prefetch at all if we use full page writes? There's nothing to prefetch if we can keep it in cache. Agreed. This is why I proposed prefetch optional through GUC. So I'm wondering if we only need prefetch because we're using lesslog? If we integrated lesslog better into the new replication would we be able to forget about doing the prefetch altogether? In the case of lesslog, almost all the FPW is replaced with corresponding incremental log and recovery takes longer. Prefetch dramatically improve this, as you will see in the above result.To improve recovery time with FPW=off or FPW=on and lesslog=yes, we need prefetch. It does sound like it is needed, yes. But if you look at the architecture of synchronous replication in 8.4 then I don't think it makes sense any more. It would be very useful for the architecture we had in 8.3, but that time has gone. Agreed. I also think that lesslog is for archiving in single node rather than replication between multiple nodes. Of course, it's very useful for the user who doesn't use replication.. etc. So if we find a way of streaming WAL without FPW then this patch makes sense, but not until then. So far many people have argued in favour of using FPW=on, which was the whole point of pg_lesslog. Are we now saying that we would run the primary with FPW=off? If we always recover a database from a base backup, the primary can run with FPW=off. Since we might need a fresh backup when making the failed server catch up with the current primary, such restriction (always recovery from a backup) might not matter. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
KaiGai Kohei wrote: If you have anything to comment for the patches, could you disclose it? It is not necessary to be a comprehensive one. Don't hesitate to submit. I looked over the patch and was wondering why you chose to have a configure option to disable row-level ACLs. There are no explicit reasons. I thought it was natural, as if we can build Linux kernel without any enhanced security features (SELinux, SMACK and so on). I don't oppose to elimination of --disable-row-acl options, however, it is not clear for me whether it should be unavoidable selection in the future, or not. Look at the existing configure options; we don't remove features via configure unless it is for some platform-specific reason. Please remove the configure option and make it always enabled. The way it should work is that the feature is always enabled, and some SQL-level commands should throw an appropriate error if SE-Linux is enabled in the build. I assume that could just be always enabled. It is not always enabled. When we build it with SE-PostgreSQL feature, rest of enhanced security features (includes the row-level ACL) are disabled automatically, as we discussed before. Oh. Is that because we use SE-Linux row-level security when SE-PostgreSQL is enabled? I guess that makes sense. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 4 Dec 2008, Robert Haas wrote: Just let's please change it both places, rather than letting contrib/pgtune be a backdoor to get around not liking what initdb does. And similarly with the other parameters... Someone running pgtune has specifically asked for their database to be tuned for performance; someone running initdb has not. It's not a backdoor, the defaults for a tuned small system and what comes out of initdb have completely different priorities. The linking of the two that keeps happening in this thread makes no sense to me, and frankly I consider the whole topic an off-topic distraction. I never had any intention of making changes to the basic configuration that comes out of initdb, the burden of proof for making a change there is far higher than I feel justified in clearing. The last time I got an initdb setting changed I had days worth of focused test data to present with the suggestion. If I take a poll of half a dozen experienced PostgreSQL administrators with performance tuning background (which is basically where the pgtune settings are coming from) and I hear the same story about a setting from most of them, that's good enough for me to justify a settings change for this tool; the whole idea is to pool expert opinion and try to distill it into code. But that's not good enough for changing that setting for everybody who installs the database. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep: First Thoughts on Code
Hi, On Thu, Dec 4, 2008 at 6:29 PM, Simon Riggs [EMAIL PROTECTED] wrote: The only sensible settings are synchronous_commit = on, synchronous_replication = on synchronous_commit = on, synchronous_replication = off synchronous_commit = off, synchronous_replication = off This doesn't make any sense: (does it??) synchronous_commit = off, synchronous_replication = on If the standby replies before writing the WAL, that strategy can improve the performance with moderate reliability, and sounds sensible. IIRC, MySQL Cluster might use that strategy. I was expecting you to have walreceiver and startup share an end of WAL address via shared memory, so that startup never tries to read past end. That way we would be able to begin reading a WAL file *before* it was filled. Waiting until a file fills means we still have to have archive_timeout set to ensure we switch regularly. You mean that not pg_standby but startup process waits for the next WAL available? If so, I agree with you in the future. That is, I just think that this is next TODO because there are many problems which we should resolve carefully to achieve it. But, if it's essential for 8.4, I will tackle it. What is your opinion? I'd like to clear up the goal for 8.4. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple postgresql.conf wizard
On Thu, 2008-12-04 at 21:51 -0500, Greg Smith wrote: On Thu, 4 Dec 2008, Robert Haas wrote: Just let's please change it both places, rather than letting contrib/pgtune be a backdoor to get around not liking what initdb does. And similarly with the other parameters... Someone running pgtune has specifically asked for their database to be tuned for performance; someone running initdb has not. It's not a backdoor, Right. the defaults for a tuned small system and what comes out of initdb have completely different priorities. IMO the priority of initdb is, Get the damn thing running The linking of the two that keeps happening in this thread makes no sense to me, and frankly I consider the whole topic an off-topic distraction. Agreed. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
I don't oppose to elimination of --disable-row-acl options, however, it is not clear for me whether it should be unavoidable selection in the future, or not. Look at the existing configure options; we don't remove features via configure unless it is for some platform-specific reason. Please remove the configure option and make it always enabled. OK, I'll update it in the next patch set. I assume that could just be always enabled. It is not always enabled. When we build it with SE-PostgreSQL feature, rest of enhanced security features (includes the row-level ACL) are disabled automatically, as we discussed before. Oh. Is that because we use SE-Linux row-level security when SE-PostgreSQL is enabled? I guess that makes sense. Yes, when SE-PostgreSQL is enabled, it provides row-level security, and the per-tuple security field is used to show its security context. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
KaiGai Kohei wrote: I don't oppose to elimination of --disable-row-acl options, however, it is not clear for me whether it should be unavoidable selection in the future, or not. Look at the existing configure options; we don't remove features via configure unless it is for some platform-specific reason. Please remove the configure option and make it always enabled. OK, I'll update it in the next patch set. Good. I assume the SQL-row security patch is not testable alone with out the rest of the patches, right? I assume that could just be always enabled. It is not always enabled. When we build it with SE-PostgreSQL feature, rest of enhanced security features (includes the row-level ACL) are disabled automatically, as we discussed before. Oh. Is that because we use SE-Linux row-level security when SE-PostgreSQL is enabled? I guess that makes sense. Yes, when SE-PostgreSQL is enabled, it provides row-level security, and the per-tuple security field is used to show its security context. Yes, that seems fine. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
Bruce Momjian wrote: KaiGai Kohei wrote: I don't oppose to elimination of --disable-row-acl options, however, it is not clear for me whether it should be unavoidable selection in the future, or not. Look at the existing configure options; we don't remove features via configure unless it is for some platform-specific reason. Please remove the configure option and make it always enabled. OK, I'll update it in the next patch set. Good. I assume the SQL-row security patch is not testable alone with out the rest of the patches, right? The minimum requirements are the 1st and 2nd patches. The first provides security hooks to PostgreSQL server program, and the other provides ones to pg_dump command. The 3rd, 4th and 5th are not necessary for the test purpose. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] In-place upgrade: catalog side
On Wed, 3 Dec 2008, Bruce Momjian wrote: As the author of the original shell script, which was in /contrib/pg_upgrade, I think the code has grown to the point where it should be reimplemented in something like Perl. Ah, there's the common ancestor I couldn't find. Sheesh, you learn Perl last month, and already you're a zealot. That was fast. As I see it the priorities for this part are: 1) Finish the shell-based pg_upgrade. The latest one we just got from Zdenek looks almost there, just have to sort out the dropped column bits. Start testing that in parallel with the below to figure out if there's anything that was missed. 2) Re-evaluate what's in there vs. what's already implemented in the C-based pg_migrator to determine the effort needed to upgrade that to fully functional. 3) Figure out who is available to do the expected work on schedule. 4) Determine what language they're going to do it in and whether the task is: a) Re-implementing the script in a more portable and powerful scripting language like Perl or Python b) Adding the additional features needed to complete pg_migrator c) Writing an implementation into core via some bootstrap process You suggested (a), I was the latest in a series of people to suggest (b), and Zdenek suggested (c). They all have different trade-offs in terms of development time and expected quality of the resulting tool. At this point we'll be lucky to get anything done, and I think we may have to settle for whichever of the above options seems most likely to finish based on the skills of the person(s) doing the job. I think (c) may be out just because there will be increasing pressure for a final code freeze on anything in core, so that beta testing can begin on Jan 1. Whereas something that's going to end up in contrib like either the final pg_upgrade or an improved pg_migrator might get cut a bit more slack for starting beta less polished than the core code. (Insert retort from Tom about how that's not the way it's done here) Additionally, it may be the case that putting the appropriate hooks in to support 8.4-8.5 upgrades that have been suggested (dedicated free space management, catalog support, etc.) is the only thing that ships on time, and that the 8.4 announcement just mentions a community tool for in-place upgrades from 8.3-8.4 is in currently in beta and can be downloaded from pgforge. That retreat position goes away if you've commited to putting the whole thing in core. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] portability of designated initializers
Alvaro Herrera escreveu: I've already modified your patch a bit ... please send your updated patch so I can merge it into mine. However, my changes were also relatively minor. Since Tom wants it to be entirely rewritten then maybe merging minor fixes to it is a waste of time ... Since Alvaro is involved in finishing another patches, I improved the first patch based on discussion. What did I do? (i) change from relOptXXX arrays to relOpts as Tom pointed out in [1]; (ii) fix some memory alloc/free problems; (iii) add some code to uniform the output of boolean parameters (output will be true/false); (iv) change the *_base_thresh names to *_threshold. I know the names are not appropriated but postgresql.conf use similar ones; (v) rollback the RelationGetXXX changes as Tom pointed out in [2]; (vi) fillfactor is stored as a separate variable in RelationData; (vi) include documentation. I did some small benchmarks to figure out if storing the parameters as a bytea (rd_options) has any performance penalty; i don't see any significant difference but my laptop is not a good parameter. Comments? PS I don't include regression tests but if you think it's important to exercise that code path, I will elaborate some tests. [1] http://archives.postgresql.org/pgsql-hackers/2008-11/msg01494.php [2] http://archives.postgresql.org/pgsql-hackers/2008-11/msg01500.php -- Euler Taveira de Oliveira http://www.timbira.com/ reloptv2.diff.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Thu, Nov 27, 2008 at 6:46 PM, Gregory Stark [EMAIL PROTECTED] wrote: ANALYZE with default_statistics_target set to 10 takes 13 s. With 100, 92 s. With 1000, 289 s. That is interesting. It would also be interesting to total up the time it takes to run EXPLAIN (without ANALYZE) for a large number of queries. OK, I did this. I actually tried 10 .. 100 in increments of 10 and then 100 ... 1000 in increments of 50, for 7 different queries of varying complexity (but all generally similar, including all of them having LIMIT 100 as is typical for this database). I planned each query 100 times with each default_statistics_target. The results were somewhat underwhelming. The query which was by far the most complex, slowest, and most expensive to plan took 2% longer to plan with default_statistics_target = 1000 vs. default_statistics_target = 10 (284 ms vs. 279 ms). The average of the remaining 6 queries was a 12% increase in planning time (17.42 ms vs. 15.47 ms). The ANALYZE times as a function of default_statistics_target were: 10 13.030161 20 22.523386 30 32.38686 40 42.200557 50 51.280172 60 60.510998 70 69.319333 80 77.942732 90 85.96144 100 93.235432 150 120.251883 200 131.297581 250 142.410084 300 152.763004 350 164.222845 400 175.989113 450 186.762032 500 199.075595 550 210.241334 600 224.366766 650 233.036997 700 240.685552 750 249.516471 800 259.522957 850 268.19841 900 277.227745 950 290.639858 1000297.099143 I'm attaching the test harness in case anyone wants to try this out with their own queries (disclaimer: this is pretty quick and dirty - it expects the input to be in files called q1.txt through q7.txt and you have to edit the code to change that, or, uh, anything else). Obviously these queries aren't very interesting in terms of d_s_t; maybe someone has some where it makes more of a difference. ...Robert explain_benchmark.pl Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default statistics target testing (was: Simple postgresql.conf wizard)
On Fri, 5 Dec 2008, Robert Haas wrote: OK, I did this. I actually tried 10 .. 100 in increments of 10 and then 100 ... 1000 in increments of 50, for 7 different queries of varying complexity Great bit of research. Was this against CVS HEAD or an 8.3 database? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] visibility maps and heap_prune
ISTM that the PD_ALL_VISIBLE flag and the visibility map bit can be set at the end of pruning operation if we know that there are only tuples visible to all transactions left in the page. The way pruning is done, I think it would be straight forward to get this information. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Sync Rep: First Thoughts on Code
Hello, On Fri, Dec 5, 2008 at 12:09 PM, Fujii Masao [EMAIL PROTECTED] wrote: I was expecting you to have walreceiver and startup share an end of WAL address via shared memory, so that startup never tries to read past end. That way we would be able to begin reading a WAL file *before* it was filled. Waiting until a file fills means we still have to have archive_timeout set to ensure we switch regularly. You mean that not pg_standby but startup process waits for the next WAL available? If so, I agree with you in the future. That is, I just think that this is next TODO because there are many problems which we should resolve carefully to achieve it. But, if it's essential for 8.4, I will tackle it. What is your opinion? I'd like to clear up the goal for 8.4. Umm.. on second thought, this feature (continuous recovery without pg_standby) seems to be essential for 8.4. So, I will try it. Development plan: - Share the end of WAL address via shared memory --- Done! - Change ReadRecord() to wait for the next WAL *record* available. - Change ReadRecord() to restore the WAL from archive by using pg_standby before reaching the replication starting position, then read the half-streaming WAL from pg_xlog. - Add new trigger for promoting the standby to the primary. As the trigger, when fast shudown (SIGINT) is requested during recovery, the standby would recover the WAL up to end and become the primary. What system call does walreceiver have to call against the WAL before startup process reads it? Probably we need to call write(2), and don't need fsync(2) in Linux. How about other platform? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] visibility maps
/* * We don't need to lock the page, as we're only looking at a single bit. */ result = (map[mapByte] (1 mapBit)) ? true : false; Isn't this a dangerous assumption to make ? I am not so sure that even a bit can be read atomically on all platforms. I think the only caller of visibilitymap_test() is VACUUM which can live with a false information. But if this is indeed a problem, should we either fix this or have explicit comments there ? BTW, my apologies for random comments. I haven't followed the discussion well, neither done a full review. So these things might have been discussed before. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] visibility maps
/* * Size of the bitmap on each visibility map page, in bytes. There's no * extra headers, so the whole page minus except for the standard page header * is used for the bitmap. */ #define MAPSIZE (BLCKSZ - SizeOfPageHeaderData) ISTM that we should MAXALIGN the SizeOfPageHeaderData to compute MAPSIZE. PageGetContents() works that way and I believe that's the right thing to do. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] In-place upgrade: catalog side
On Thu, 4 Dec 2008, Gregory Stark wrote: Incidentally I got this wrong in my previous email. If we're aiming at 8.4-8.5 as the first in-place update then we actually don't need this in 8.4 at all. I don't know about everybody else, but I haven't give up on putting together something that works for 8.3. That's why I'm still trying to chase down an approach, even if it's not as elegant as would be possible for 8.4-8.5. It doesn't sound hard off the top of my head. CREATE TABLE is a bit tricky though, I'll check to make sure it works. Support for NULL bits in CREATE TABLE might be a helpful trick to have available for this at some point. I'm not sure if it's actually worth doing after the rest of your comments though; see below. I'm sorry, I think I misunderstood the original idea, what you're talking about makes a lot more sense now. You want to save the space of the dead column by replacing it with NULL, not remove it from the table definition. Not so much to save the space, it's more about breaking its need for the soon to be removed pg_attribute that used to lead to the dropped column. If you think it's possible to slip that change into the page conversion task, that's probably the ideal way to deal with this. If 8.4 has the appropriate catalog support to finally execute the full page upgrade vision Zdenek and everybody else has mapped out, it would make me feel better about something that just hacked around this problem crudely for 8.3-8.4. Knowing that a future 8.5 update could finally blow away the bogus dropped columns makes leaving them in there for this round not as bad, and it would avoid needing to mess with the whole pg_dump/CREATE TABLE with NULL bit. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
