Re: [HACKERS] xlogdump
Heikki Linnakangas wrote: Gregory Stark wrote: There's an xlogdump project on pgfoundry. However it suffers from perennial bitrot as it has to maintain its own table of xlog record types and code to decode each xlog record type. ... I think this module should be rewritten to depend more closely on the Postgres source files. What I'm doing now is making an SRF in the style of the pageinspect module which will read an arbitrary wal file and generate records directly. This has a big disadvantage compared to the original approach, namely that you need a functioning Postgres instance of the same version to dissect wal records. But it also has a big advantage, namely that it will always be in sync. It will just use the same RmgrTable to find the rm_name and call the rm_desc method to decode the record. The result might not be quite as or dense as the rm_desc function is meant for debugging messages. We could address that sometime with a new method if we wanted to. Would it still be possible to compile it as a stand-alone program, using the backend source files? It would be a hack, we just went through some effort to clean up references to server private header files from ecpg and initdb, but it feels a lot nicer to use as a standalone program than requiring a running postgres instance. How much infrastructure would you need to call rm_name and rm_desc from a standalone program? palloc and friends, I presume, What else? Do you actually need palloc and friends, or just something named palloc? We already have some stuff in src/port that deals with using palloc calls in routines used in frontend programs... //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] xlogdump
--On Freitag, November 02, 2007 10:54:45 + Gregory Stark [EMAIL PROTECTED] wrote: I think this module should be rewritten to depend more closely on the Postgres source files. What I'm doing now is making an SRF in the style of the pageinspect module which will read an arbitrary wal file and generate records directly. Isn't that already partially implemented in xlogviewer, hosted on the same project: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/xlogviewer/xlogviewer/ The last time i've used it it had some problems with exhausted memory, but it implements a SRF returning WAL records from a given log segment file. -- Thanks Bernd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: If the proposal is implemented BEGIN savepoint s1; some DML operations get current inventory2 = select ... if current inventory2 is fixed size current inventory1 = select .. as of savepoint s1; END Do you see the difference? Yes, a completely non-standard and somewhat unusual feature. What I don't see is why you're wasting time pushing this frankly obscure idea for time-travel, only within a transaction. Why not just go all out and suggest re-adding time-travel completely. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Intel x64 vs AMD x64 pgdata
Jeff Trout [EMAIL PROTECTED] writes: I'm migrating from some opterons to some xeons (E5345) both are running x86_64. At first I figured I'd need to dump load my data, which will be painful. But on a whim I made a test db on the opteron, copied it over (tar) and it fired up and worked fine on the xeon. Seeing the success of this, I took our PITR backup and restored it properly, and everything seems to be functioning correctly from my tests. Sure, those are the same architecture as far as PG is concerned. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] minimal update
For some time I have been working on removing some inefficiencies from a large DW-type app. This app does a large daily batch update, and this is what is the major bottleneck. One of the things I have been doing is to remove unnecessary updates (which are particualrly expensive in our index-rich setting). Several times now I have wished that there was a switch on the UPDATE command that said do minimal instead of maximal updating. i.e., don't update records with identical replacements. At the moment I have to write things like: update tname set foo = bar ... where foo is null or foo bar ... This becomes more than tedious when the update might be setting thirty or forty fields, and I have to write such tests for each of them. It would be so much nicer to be able to write something like: update tname minimally set foo = bar ... Is this an insane idea, or would it be possible, practical and useful? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Clarification about HOT
On Fri, Nov 02, 2007 at 06:12:37PM +0530, Gokulakannan Somasundaram wrote: I am especially interested in the case of continuing the HOT chain across pages. When we are actually reclaiming space, we should check the snapshot and reclaim it. If it is HOT updated, we will leave the top most tuple and take the rest. So then the top most tuple will remain always and any index scan now has to make read two heap pages to reach the target entry.Is this the only reason, it was left out? It's not that simple. At any point in time there may be dozens of active snapshots, each of which might see a different tuple in the chain. So to clear any tuple you have to wait until all active snapshots are gone. You will almost never be able to reduce the chain to just one. As for your original question and jumping across pages, why stop at one. Why not chain HOT tuples down 100 pages? Because then it gets very expensive. Not to mention the locking considerations. Better keep it simple. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [HACKERS] Clarification about HOT
Gokulakannan Somasundaram wrote: I understand that if you have to Vacuum a tuple, it has to satisfy the necessary snapshot requirements. i will never be able to reduce the chain to just one, because the there is always a indirection at the top of HOT. I understood this. My question was is it the only reason for the decision to stop HOT across pages. Another reason is that it avoids the whole problem of updating multiple pages atomically, without deadlocks. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Clarification about HOT
On 11/2/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Fri, Nov 02, 2007 at 06:12:37PM +0530, Gokulakannan Somasundaram wrote: I am especially interested in the case of continuing the HOT chain across pages. When we are actually reclaiming space, we should check the snapshot and reclaim it. If it is HOT updated, we will leave the top most tuple and take the rest. So then the top most tuple will remain always and any index scan now has to make read two heap pages to reach the target entry.Isthis the only reason, it was left out? It's not that simple. At any point in time there may be dozens of active snapshots, each of which might see a different tuple in the chain. So to clear any tuple you have to wait until all active snapshots are gone. You will almost never be able to reduce the chain to just one. I understand that if you have to Vacuum a tuple, it has to satisfy the necessary snapshot requirements. i will never be able to reduce the chain to just one, because the there is always a indirection at the top of HOT. I understood this. My question was is it the only reason for the decision to stop HOT across pages. As for your original question and jumping across pages, why stop at one. Why not chain HOT tuples down 100 pages? Because then it gets very expensive. Not to mention the locking considerations. Better keep it simple. If you have to jump 100 pages, then you have 100 versions of the same tuple, which are not to be Vacuumed. That's a heavily updated tuple indeed. Then you will have 100 index tuples and you will anyway visit all those versions in a normal index scan. The question is whether you want to visit it through the HOT chain/ through the index entries. If you visit it through HOT chain, indexes can be in reduced size. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
[HACKERS] Clarification about HOT
I went through the README on HOT. That was really a nice and cool feature. Hats off to the person who thought about it. I have a couple of doubts about it. a) In the README, there is a statement like this. In principle we could continue a HOT chain across pages, but this would destroy the desired property of being able to reclaim space with just page-local manipulations. Anyway, we don't want to have to chase through multiple heap pages to get from an index entry to the desired tuple, so it seems better to create a new index entry for the new tuple. I am especially interested in the case of continuing the HOT chain across pages. When we are actually reclaiming space, we should check the snapshot and reclaim it. If it is HOT updated, we will leave the top most tuple and take the rest. So then the top most tuple will remain always and any index scan now has to make read two heap pages to reach the target entry.Is this the only reason, it was left out? -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] xlogdump
Gregory Stark wrote: There's an xlogdump project on pgfoundry. However it suffers from perennial bitrot as it has to maintain its own table of xlog record types and code to decode each xlog record type. ... I think this module should be rewritten to depend more closely on the Postgres source files. What I'm doing now is making an SRF in the style of the pageinspect module which will read an arbitrary wal file and generate records directly. This has a big disadvantage compared to the original approach, namely that you need a functioning Postgres instance of the same version to dissect wal records. But it also has a big advantage, namely that it will always be in sync. It will just use the same RmgrTable to find the rm_name and call the rm_desc method to decode the record. The result might not be quite as or dense as the rm_desc function is meant for debugging messages. We could address that sometime with a new method if we wanted to. Would it still be possible to compile it as a stand-alone program, using the backend source files? It would be a hack, we just went through some effort to clean up references to server private header files from ecpg and initdb, but it feels a lot nicer to use as a standalone program than requiring a running postgres instance. How much infrastructure would you need to call rm_name and rm_desc from a standalone program? palloc and friends, I presume, What else? I'm thinking of actually dropping it directly into the pageinspect contrib module. It's not quite an exact fit but it doesn't seem to deserve it's own contrib module and it's likely to suffer the same bitrot problem if it lives in pgfoundry. I'd vote for pgfoundry or a new contrib module. It shouldn't suffer from bitrot as easily as what's there now. That was the whole point of switching over to the new approach, right? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] xlogdump
There's an xlogdump project on pgfoundry. However it suffers from perennial bitrot as it has to maintain its own table of xlog record types and code to decode each xlog record type. Earlier I modified xlogdump to generate a CSV loadable data set so I could do some basic analysis and see what types of operations are generating the most wal traffic. But I found it had bitrotted and needed some attention to bring it up to date. Again now I wanted to repeat that analysis to measure the effect HOT has had on WAL traffic. And again now I find it has bitrotted, not least because of HOT of course... I think this module should be rewritten to depend more closely on the Postgres source files. What I'm doing now is making an SRF in the style of the pageinspect module which will read an arbitrary wal file and generate records directly. This has a big disadvantage compared to the original approach, namely that you need a functioning Postgres instance of the same version to dissect wal records. But it also has a big advantage, namely that it will always be in sync. It will just use the same RmgrTable to find the rm_name and call the rm_desc method to decode the record. The result might not be quite as or dense as the rm_desc function is meant for debugging messages. We could address that sometime with a new method if we wanted to. I'm thinking of actually dropping it directly into the pageinspect contrib module. It's not quite an exact fit but it doesn't seem to deserve it's own contrib module and it's likely to suffer the same bitrot problem if it lives in pgfoundry. Incidentally I would like to call xlog.c:RecordIsValid() which is currently a static function. Any objection to exporting it? It doesn't depend on any external xlog.c state. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Clarification about HOT
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: Another reason is that it avoids the whole problem of updating multiple pages atomically, without deadlocks. Thanks Heikki. I am still not getting what you said. In the case of HOT, you need to update the top pointer to point to some other tuple in some other page. That's one update. what's the other one? There are several problems, two that come to mind are: 1) How do you make the dead top pointer redirect to the first live pointer in the chain? Currently we store the item number of the first live tuple in the line pointer. You would have to keep the tuple around, though you could truncate it to just the tuple header. 2) When vacuuming how do you find the head of the chain when you're looking at a dead tuple? There's no space in the tuple header to store the head of the chain. Besides you want to vacuum scanning sequentially, not randomly. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] minimal update
David Fetter [EMAIL PROTECTED] writes: On Fri, Nov 02, 2007 at 11:49:38AM -0400, Andrew Dunstan wrote: At the moment I have to write things like: update tname set foo = bar ... where foo is null or foo bar One way I've done this is make RULEs which basically drop non-updating UPDATEs on the floor. A BEFORE UPDATE trigger would be better, and probably hardly more expensive than a wired-in facility (especially if you were willing to write it in C). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Intel x64 vs AMD x64 pgdata
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 2 Nov 2007 08:40:46 -0400 Jeff Trout [EMAIL PROTECTED] wrote: Hey folks, Asking here since it may deal with clever things such as alignment and or binary reps. I'm migrating from some opterons to some xeons (E5345) both are running x86_64. At first I figured I'd need to dump load my data, which will be painful. But on a whim I made a test db on the opteron, copied it over (tar) and it fired up and worked fine on the xeon. Seeing the success of this, I took our PITR backup and restored it properly, and everything seems to be functioning correctly from my tests. x86_64 is x86_64, regardless of intel or amd. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHK08WATb/zqfZUUQRAtRcAKCoh0UqQJCfCbRltsfdqgaoPpa/SACePsRQ t3/gNGb9/Lus6JaiVj3yhuA= =6vs/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Hans-Juergen Schoenig [EMAIL PROTECTED] wrote: I think Simon Riggs is already working on that idea. This one is fairly easy to implement. I think these are some of the features only a time-stamp based database can implement. I think database standards were formed during the time, when the data consistency was provided with Lock based mechanisms. And moreover i have already committed on the indexes with snapshot and i am still waiting for its approval from hackers. If that does go through, then i need to work on the reverse mapping hash tables, which is really a long task. So i may not be able to take up time-travel now. if i remember my last talk with Simon correctly the idea is to have timetravel across transactions. having this feature inside a transaction will not make it into CVS as it is basically of no practical use. I am just reminding my days of working with Oracle. The Flashback feature was allowed only for DBAs, and they never let the developers access that feature, unless there is a strong reason for it. It was more thought of as a feature for recovery and they never let deveopers use that in the application. Also it was designed as a optional feature. If its switched off, it cannot be used. If someone comes up with the time travel feature across transactions and if it is designed as non-optional feature and if it happens to be a feature, which DBA can let the developers use freely, then this feature should be rolled back. The feature i am talking about is very simple and it won't even add 100 lines of code into the Postgres source code base. i would suggest to put some effort into making it work across transactions. just saving the snapshot is not enough here - there are a couple of other things which have to be taken into consideration (transaction wraparound, etc.) When i think about it, Timetravel always look big for me and i don't have the bandwidth to take that up. if you want to work on timetravel my team and i can provide some assistance as we wanted to help in this area anyway. Thanks. Please send me your findings. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Clarification about HOT
Gokulakannan Somasundaram wrote: Thanks Heikki. I am still not getting what you said. In the case of HOT, you need to update the top pointer to point to some other tuple in some other page. That's one update. what's the other one? say currently the top of heap chain points to (2,3) . Imagine we are making the HOT chain through the pages. there might be a situation it should start pointing to (4,5) after the tuple at (2,3) gets ready to be Vacuumed. We should just lock the page where the top of HOT chain resides and update it to point to (4,5). What else we should do atomically? Imagine one more update, and we end up with a HOT chain like this: (2,3) - (4,5) - (6,7) Where (2,3) is a redirecting line pointer, (4,5) is a tuple that can be vacuumed, and (6,7) is the a live tuple. When vacuuming (4,5), the redirecting line pointer (2,3) needs to be updated at the same time. The chain could be even longer, requiring either locking and modifying even more pages atomically, or doing the pruning in steps which leads to more WAL traffic among other things. It could be done, we already have to deal with locking two pages simultaneously in heap_update, but it's pretty darn complex. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] minimal update
Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Fri, Nov 02, 2007 at 11:49:38AM -0400, Andrew Dunstan wrote: At the moment I have to write things like: update tname set foo = bar ... where foo is null or foo bar One way I've done this is make RULEs which basically drop non-updating UPDATEs on the floor. A BEFORE UPDATE trigger would be better, and probably hardly more expensive than a wired-in facility (especially if you were willing to write it in C). Yes. I also prefer the trigger idea to a rule because triggers are easy to enable and disable. It's still a lot of work for what must be a common want, though. Could it be done generically? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Test lab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, The test lab is finally starting to come to fruition. We (the community) have been donated hardware via MyYearbook and Hi5. It is my understanding that we may also have some coming from HP. We are currently setting up a Trac for management and publishing of results etc... I have also spoken with Mark Wong and he is going to be helping with DBT and such. The first machine we are going to have up and have ready access to is a HP DL 585. It has 8 cores (Opteron), 32GB of ram and 28 spindles over 4 channels. My question is -hackers, is who wants first bite and what do they want :) Sincerely, Joshua D. Drake P.S. It is RHEL 5. - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHK2F6ATb/zqfZUUQRAm3UAJ0cZ+ypAWE2uFIDhwMm1Ih5iqPb4ACgnoxc kZmdZ7FrwdWldNZ8gC+CfC4= =Oyas -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] minimal update
On Fri, Nov 02, 2007 at 11:49:38AM -0400, Andrew Dunstan wrote: For some time I have been working on removing some inefficiencies from a large DW-type app. This app does a large daily batch update, and this is what is the major bottleneck. One of the things I have been doing is to remove unnecessary updates (which are particualrly expensive in our index-rich setting). Several times now I have wished that there was a switch on the UPDATE command that said do minimal instead of maximal updating. i.e., don't update records with identical replacements. At the moment I have to write things like: update tname set foo = bar ... where foo is null or foo bar ... One way I've done this is make RULEs which basically drop non-updating UPDATEs on the floor. CREATE RULE foo_drop_empty_updates AS ON UPDATE TO foo WHERE ROW(OLD.*)::foo IS NOT DISTINCT FROM ROW(NEW.*)::foo DO INSTEAD NOTHING; It's pretty easy to automate rule creation, but since Postgres doesn't have DDL triggers, it's also a bit of a foot gun. By the way, the above has what I think of as an infelicity in 8.2.5, namely that you need non-obvious contortions to get it to work. I'm thinking OLD IS NOT DISTINCT FROM NEW should Just Work(TM). This becomes more than tedious when the update might be setting thirty or forty fields, and I have to write such tests for each of them. It would be so much nicer to be able to write something like: update tname minimally set foo = bar ... Is this an insane idea, or would it be possible, practical and useful? I don't know about the sanity, but I've done it a couple of places :) Cheers, David. -- 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Clarification about HOT
On Fri, Nov 02, 2007 at 10:27:27PM +0530, Gokulakannan Somasundaram wrote: say currently the top of heap chain points to (2,3) . Imagine we are making the HOT chain through the pages. there might be a situation it should start pointing to (4,5) after the tuple at (2,3) gets ready to be Vacuumed. We should just lock the page where the top of HOT chain resides and update it to point to (4,5). What else we should do atomically? You have to lock (4,5) also to make sure it's still there after you update. Maybe just at that moment another vacuum saw that (4,5) can also be cleaned, you have to lock all the pages to make sure the change is atomic... As soon as you have to lock more than one page, deadlocks become a problem. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [HACKERS] Clarification about HOT
On 11/2/07, Heikki Linnakangas [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram wrote: I understand that if you have to Vacuum a tuple, it has to satisfy the necessary snapshot requirements. i will never be able to reduce the chain to just one, because the there is always a indirection at the top of HOT. I understood this. My question was is it the only reason for the decision to stop HOT across pages. Another reason is that it avoids the whole problem of updating multiple pages atomically, without deadlocks. Thanks Heikki. I am still not getting what you said. In the case of HOT, you need to update the top pointer to point to some other tuple in some other page. That's one update. what's the other one? say currently the top of heap chain points to (2,3) . Imagine we are making the HOT chain through the pages. there might be a situation it should start pointing to (4,5) after the tuple at (2,3) gets ready to be Vacuumed. We should just lock the page where the top of HOT chain resides and update it to point to (4,5). What else we should do atomically? -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Fri, Nov 02, 2007 at 02:43:44PM +0530, Gokulakannan Somasundaram wrote: Hi, I would like to propose an additional feature for Postgres to enable time-travelling inside a transaction. snip This would reduce the requirement for Serializable transactions in some cases and adds one more feature under the umbrella of postgres. I'm still confused. Why on earth would you want to run a query against an old snapshot? This is a interesting question. But if situation demands, you have to. Suppose i need to make a decision and in some cases, i may need the data as of old snapshot. Currently that can be done by caching the old data, irrespective of whether you need it/not at the later time. If we have this feature, we can put that extra load on the database selectively. Current Scenario eg: BEGIN current inventory1 = select .. some DML operations get current inventory2 = select.. if current inventory2 is fixed size make some decision to purchase based on current inventory 1. END If the proposal is implemented BEGIN savepoint s1; some DML operations get current inventory2 = select ... if current inventory2 is fixed size current inventory1 = select .. as of savepoint s1; END Do you see the difference? If you want a stable view, I'd think of opening a cursor or using a temporary table. And where does serializable come into this? You can use a stable view / temporary table, if you are going to fire a same query again, not for x different queries against y different tables. it is not advisable to take the whole database as a backup in the form of temporary table, since we are going to run two queries as of the same snapshot. Sometimes people decide on serializable transactions, since they need most of the select queries inside the transactions have to get fired as of the same snapshot and they have very few DMLs. i have run into such a situation personally. In those kind of situations, this extra flexibility helps. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFHKuu9IB7bNG8LQkwRAkQUAJwP9ShvfqxzHpTU2XHxRy5/TpVtVQCgik7k cwDUu99nfIpydxlQHihnKXE= =4lO3 -END PGP SIGNATURE- -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On Fri, Nov 02, 2007 at 02:43:44PM +0530, Gokulakannan Somasundaram wrote: Hi, I would like to propose an additional feature for Postgres to enable time-travelling inside a transaction. snip This would reduce the requirement for Serializable transactions in some cases and adds one more feature under the umbrella of postgres. I'm still confused. Why on earth would you want to run a query against an old snapshot? If you want a stable view, I'd think of opening a cursor or using a temporary table. And where does serializable come into this? Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
[HACKERS] Proposal: Select ... AS OF Savepoint
Hi, I would like to propose an additional feature for Postgres to enable time-travelling inside a transaction. I went through the source code and i found Savepoint is already saving the necessary information. But currently it doesn't store the CommandId. This change, if implemented would save the command id into savepoint state, increase the command id counter for every savepoint and finally we can issue a command like (One more global variable to store the current state) Select ... as of savepoint savepoint-name. This would reduce the requirement for Serializable transactions in some cases and adds one more feature under the umbrella of postgres. -- Thanks, Gokul. CertoSQL Project, Allied Solution Groups. (www.alliedgroups.com)
Re: [HACKERS] minimal update
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: A BEFORE UPDATE trigger would be better, and probably hardly more expensive than a wired-in facility (especially if you were willing to write it in C). Yes. I also prefer the trigger idea to a rule because triggers are easy to enable and disable. It's still a lot of work for what must be a common want, though. Could it be done generically? Well, you could write the trigger in C and it'd work for any table. I think it could be as simple as a memcmp of the tuples' data areas, since we now require padding bytes to be 0 ... Ah. Good. Thanks, that's the piece I was missing. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Jonah H. Harris [EMAIL PROTECTED] wrote: On 11/2/07, Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: If the proposal is implemented BEGIN savepoint s1; some DML operations get current inventory2 = select ... if current inventory2 is fixed size current inventory1 = select .. as of savepoint s1; END Do you see the difference? Yes, a completely non-standard and somewhat unusual feature. What I don't see is why you're wasting time pushing this frankly obscure idea for time-travel, only within a transaction. Why not just go all out and suggest re-adding time-travel completely. I think Simon Riggs is already working on that idea. This one is fairly easy to implement. I think these are some of the features only a time-stamp based database can implement. I think database standards were formed during the time, when the data consistency was provided with Lock based mechanisms. And moreover i have already committed on the indexes with snapshot and i am still waiting for its approval from hackers. If that does go through, then i need to work on the reverse mapping hash tables, which is really a long task. So i may not be able to take up time-travel now. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
I think Simon Riggs is already working on that idea. This one is fairly easy to implement. I think these are some of the features only a time-stamp based database can implement. I think database standards were formed during the time, when the data consistency was provided with Lock based mechanisms. And moreover i have already committed on the indexes with snapshot and i am still waiting for its approval from hackers. If that does go through, then i need to work on the reverse mapping hash tables, which is really a long task. So i may not be able to take up time-travel now. if i remember my last talk with Simon correctly the idea is to have timetravel across transactions. having this feature inside a transaction will not make it into CVS as it is basically of no practical use. i would suggest to put some effort into making it work across transactions. just saving the snapshot is not enough here - there are a couple of other things which have to be taken into consideration (transaction wraparound, etc.) if you want to work on timetravel my team and i can provide some assistance as we wanted to help in this area anyway. best regards, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at
[HACKERS] should I worry?
Hi hackers, I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation perso_objets already exists ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages_dest already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages_exp already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation type_evt already exists ERROR: trigger unnamed for relation objet_generique already exists ERROR: trigger unnamed for relation objet_generique already exists ERROR: trigger unnamed for relation objets_caracs already exists ERROR: trigger unnamed for relation competences already exists ERROR: trigger unnamed for relation race_comp already exists I don't remember having those with beta1 and don't know what to look at. I've tried both 8.2.5 and 8.3beta2 pg_dump. I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Unrelated, who should I contact to get my password of jaguar in build_farm, this animal has been set up to test with -DCLOBBER_CACHE_ALWAYS and I never received the password. Maybe pgfbuildfarm.org web should include a button to resend account to the owner. Just my 0.01$... 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) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] beta2, I can't set UTF-8 for czech locales on win
On 1 nov, 19:30, [EMAIL PROTECTED] (Tom Lane) wrote: Pavel Stehule [EMAIL PROTECTED] writes: When I try manually rebuild cluster I had second problem: C:\PostgreSQL\bininitdb -D ../data The program postgres isneededbyinitdbbutwas not found in the same directory as C:\PostgreSQL\bin/initdb. Check your installation. Do you have the same problem previously reported that postgres -V doesn't work? If so, maybe taking out libxml2 will help? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Exactly same problem !! I use postgres V 8.2.4 on windows vista. Have you found a response since ? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: On 11/2/07, Tom Lane [EMAIL PROTECTED] wrote: [ splorfff... ] The grammar support alone will cost ten times that. But i guess(I may be wrong), you may be wrong about the grammar support part. Well, a crude estimate is that SELECT ... AS OF Savepoint would take about the same amount of grammar and nodetree infrastructure as SELECT ... FOR UPDATE does. Try grepping for all the code that deals with LockingClause (the raw-grammar representation of FOR UPDATE) and RowMarkClause (the transformed representation). Hits on the list fields SelectStmt.lockingClause and Query.rowMarks would be relevant too (code touching these lists wouldn't necessarily mention the list member node type anyplace). 1000 lines may be an overestimate, but it's probably nearer the mark than 100 is. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Asynchronous commit documentation gap
The documentation doesn't really tell how to disable synchronous commits for a single commit. I believe the correct command is SET LOCAL synchronous_commit TO OFF; just before the COMMIT statement. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
On Fri, 2 Nov 2007, Heikki Linnakangas wrote: Date: Fri, 02 Nov 2007 18:11:14 + From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? Yes it was empty, Sorry, I can't post the schema, it belongs to one of my customer... All triggers in the schema are named. So I assume they are triggers for foreign keys. It's hard to tell if all foreign keys have been created after restore is complete... I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Depends on how much activity there is. If there's a lot of dirty buffers, a lot of written buffers and long write time is perfectly normal. Thanks for the explanation. 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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Depends on how much activity there is. If there's a lot of dirty buffers, a lot of written buffers and long write time is perfectly normal. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Intel x64 vs AMD x64 pgdata
Hey folks, Asking here since it may deal with clever things such as alignment and or binary reps. I'm migrating from some opterons to some xeons (E5345) both are running x86_64. At first I figured I'd need to dump load my data, which will be painful. But on a whim I made a test db on the opteron, copied it over (tar) and it fired up and worked fine on the xeon. Seeing the success of this, I took our PITR backup and restored it properly, and everything seems to be functioning correctly from my tests. Is there anything I should be weary of doing this? I'd figure any alignment or other issues would have bitten me in my testing or PG would simply refuse to start. btw, the version in question is 8.2.5 thanks guys! -- Jeff Trout [EMAIL PROTECTED] http://www.dellsmartexitin.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
Gokulakannan Somasundaram [EMAIL PROTECTED] writes: The feature i am talking about is very simple and it won't even add 100 lines of code into the Postgres source code base. [ splorfff... ] The grammar support alone will cost ten times that. You should probably reflect on the fact that not one single person besides you thinks this is a good idea. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Tom Lane [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: The feature i am talking about is very simple and it won't even add 100 lines of code into the Postgres source code base. [ splorfff... ] The grammar support alone will cost ten times that. You should probably reflect on the fact that not one single person besides you thinks this is a good idea. regards, tom lane Tom, If you have made this comment, when i requested for the comment, i would have dropped this idea there itself. :). But please let me know your comments on why you feel this is useless. But i guess(I may be wrong), you may be wrong about the grammar support part. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com)
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 2 Nov 2007 22:33:16 +0530 Gokulakannan Somasundaram [EMAIL PROTECTED] wrote: Tom, If you have made this comment, when i requested for the comment, i would have dropped this idea there itself. :). But please let me know your comments on why you feel this is useless. But i guess(I may be wrong), you may be wrong about the grammar support part. Wow, you are new here aren't you? I assure you, Tom is the last person that is going to be wrong about grammar support. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHK1jTATb/zqfZUUQRAtoHAKCd2F35SPaHBsfS4JaanYkvBa/V2QCeP5x3 swyEo4Xm3h7wxxW1FhoSUgA= =VLSb -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Machine available for community use
Just a follow-up to note that Red Hat has graciously donated a 1 year RHEL subscription and myYearbook is paying Command Prompt to setup the RHEL box for community use. We've not worked out a scheduling methodology, or how to best organize the use of said hardware, but I know that Tom and others are interested. Does anyone have a scheduling solution for things like this to make sure people aren't stepping on each others toes processor/ram/disk wise? Also, what should the policies be for making sure that people can use the box for what they need to use the box for? Should people clean up after themselves data usage wise after their scheduled time? Should people only be able to run PostgreSQL in the context of their own user? Do we have experience with such setups in the past? What has worked well and what hasn't? Gavin On 7/25/07, Gavin M. Roy [EMAIL PROTECTED] wrote: Recently I've been involved in or overheard discussions about SMP scalability at both the PA PgSQL get together and in some list traffic. myYearbook.com would ike to make one of our previous production machines available to established PgSQL Hackers who don't have access to this level of hardware for testing, benchmarking and development to work at improving SMP scalability and related projects. The machine is a HP 585 G1, 8 Core AMD, 32GB RAM with one 400GB 14 Spindle DAS Array dedicated to community use. I've attached a text file with dmesg and /proc/cpuinfo output. I'm working on how this will be setup and am open to suggestions on how to structure access. I'm currently in the process of having Gentoo linux reinstalled on the box since that is what I am most comfortable administering from a security perspective. If this will be a blocker for developers who would actually work on it, please let me know. If you're interested in access, my only requirement is that you're a current PgSQL Hacker with a proven track-record of committing patches to the community. This is a resource we could be using for something else, and I'd like to see the community get direct benefit from it as opposed to it being a play sandbox for people who want to tinker. Please let me know thoughts, concerns or suggestions. Gavin M. Roy CTO myYearbook.com [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Test lab
Joshua D. Drake [EMAIL PROTECTED] writes: My question is -hackers, is who wants first bite and what do they want :) Something I'd like to have back real soon is the daily DBT run against CVS HEAD that Mark Wong was doing at OSDL. Maybe we don't need a particularly enormous machine for that, but comparable runs day after day are real nice for noting when patches had unexpected performance impacts... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: Select ... AS OF Savepoint
On 11/2/07, Tom Lane [EMAIL PROTECTED] wrote: Gokulakannan Somasundaram [EMAIL PROTECTED] writes: The feature i am talking about is very simple and it won't even add 100 lines of code into the Postgres source code base. [ splorfff... ] The grammar support alone will cost ten times that. You should probably reflect on the fact that not one single person besides you thinks this is a good idea. Well said. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] beta2, I can't set UTF-8 for czech locales on win
On 02/11/2007, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On 1 nov, 19:30, [EMAIL PROTECTED] (Tom Lane) wrote: Pavel Stehule [EMAIL PROTECTED] writes: When I try manually rebuild cluster I had second problem: C:\PostgreSQL\bininitdb -D ../data The program postgres isneededbyinitdbbutwas not found in the same directory as C:\PostgreSQL\bin/initdb. Check your installation. Do you have the same problem previously reported that postgres -V doesn't work? If so, maybe taking out libxml2 will help? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Exactly same problem !! I use postgres V 8.2.4 on windows vista. Have you found a response since ? use runas and run initdb as user postgres Regards Pavel Stehule ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Machine available for community use
Gavin M. Roy [EMAIL PROTECTED] writes: Just a follow-up to note that Red Hat has graciously donated a 1 year RHEL subscription and myYearbook is paying Command Prompt to setup the RHEL box for community use. Sorry that Red Hat was so slow about that :-( [ various interesting questions snipped ] Should people only be able to run PostgreSQL in the context of their own user? Do we have experience with such setups in the past? What has worked well and what hasn't? Yeah, I'd vote for people just building private PG installations in their own home directories. I am not aware of any performance-testing reason why we'd want a shared installation, and given that people are likely to be testing many different code variants, a shared installation would be a management nightmare. Also, with personal installations, nobody need have root privileges, which just seems like a real good idea. I don't have any special insights about the other management issues you mentioned, but I'm sure someone does ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] beta2, I can't set UTF-8 for czech locales on win
Pavel Stehule [EMAIL PROTECTED] writes: On 02/11/2007, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Exactly same problem !! I use postgres V 8.2.4 on windows vista. Have you found a response since ? use runas and run initdb as user postgres Doesn't sound like that will fix it. The root check doesn't happen until after we check for -V (look in backend/main/main.c). Whatever libxml2 is doing to break things must be something that affects the earlier steps in main() ... and there aren't that many. Maybe it's a locale thing? Anyway I think someone has to reproduce this under a debugger on Windows to find out where it's failing. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote: On Fri, 2 Nov 2007, Heikki Linnakangas wrote: Date: Fri, 02 Nov 2007 18:11:14 + From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? Yes it was empty, Sorry, I can't post the schema, it belongs to one of my customer... Maybe you could reduce it to a small self-contained test case, with table names changed to protect the innocent? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: All triggers in the schema are named. So I assume they are triggers for foreign keys. No, foreign-key triggers always have names too, and they don't look like that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace in PG that supplies unnamed as a default name for a trigger, either. So there's something weird about your schema, and we really need to see a test case ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
Hi, Le Friday 02 November 2007 21:08:24 Tom Lane, vous avez écrit : No, foreign-key triggers always have names too, and they don't look like that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace in PG that supplies unnamed as a default name for a trigger, either. So there's something weird about your schema, and we really need to see a test case ... I've had some restore problems with unnamed triggers on a 8.1 database. It contained some de-activated triggers dating from pre-7.3 era, and I finally managed to clean out the schema with the adddepend contrib utility. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/adddepend/Attic/ After running this, I had no more problems related to unnamed triggers, but I can't remember the specifics of the errors I had. For adddepend to run, some data were to be removed, too (disabled constraint triggers made possible to insert them at some point in the past). Sorry for the imprecision of the post, hope this helps, -- dim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Continue [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello, You seem not to have understood my recommendation to use a callback function. This patch might work nicely for SQL functions but there will be no good way to use it for plpgsql, or probably any other PL function language. If we're going to change the parser API then I'd like to have a more general solution. Perhaps I did not look well enough, but is there any callback mechanism like the error_context_stack etc... in the parser? ( If not, I guess one has to be created :) ) Thank you. Gevik. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Machine available for community use
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 02 Nov 2007 15:37:17 -0400 Tom Lane [EMAIL PROTECTED] wrote: Gavin M. Roy [EMAIL PROTECTED] writes: Just a follow-up to note that Red Hat has graciously donated a 1 year RHEL subscription and myYearbook is paying Command Prompt to setup the RHEL box for community use. Sorry that Red Hat was so slow about that :-( [ various interesting questions snipped ] Should people only be able to run PostgreSQL in the context of their own user? Do we have experience with such setups in the past? What has worked well and what hasn't? Yeah, I'd vote for people just building private PG installations in their own home directories. I am not aware of any performance-testing reason why we'd want a shared installation, and given that people are likely to be testing many different code variants, a shared The only caveat here is that our thinking was that the actual arrays would be able to be re-provisioned all the time. E.g; test with RAID 10 with x stripe size, Software RAID 6, what is the real difference between 28 spindles with RAID 5 versus 10? installation would be a management nightmare. Also, with personal installations, nobody need have root privileges, which just seems like a real good idea. No question. Joshua D. Drake I don't have any special insights about the other management issues you mentioned, but I'm sure someone does ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHK412ATb/zqfZUUQRAg4eAJ0YubwkLQ3mU0st5jPhUnC6dWrqeACeMjQe TFxunw+efuh3XNtMv+whKBI= =RzC/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Machine available for community use
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 02 Nov 2007 17:11:30 -0400 Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Yeah, I'd vote for people just building private PG installations in their own home directories. I am not aware of any performance-testing reason why we'd want a shared installation, and given that people are likely to be testing many different code variants, a shared The only caveat here is that our thinking was that the actual arrays would be able to be re-provisioned all the time. E.g; test with RAID 10 with x stripe size, Software RAID 6, what is the real difference between 28 spindles with RAID 5 versus 10? Well, we need some workspace that won't go away when that happens. Right which is on the internal devices. I'd suggest that the OS and people's home directories be mounted on a permanent partition with plenty of space for source code, say a few tens of GB, and then there be a farm of data workspace that's understood to be transient and can be reconfigured as needed for tests like that. Agreed. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHK5LwATb/zqfZUUQRApBQAJ9Gp+fpgOnA6ZONpdQl43giMcetZwCggv2Q 8A9FfkeP6VsQptWl1J8W4n8= =nX1C -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Machine available for community use
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: Yeah, I'd vote for people just building private PG installations in their own home directories. I am not aware of any performance-testing reason why we'd want a shared installation, and given that people are likely to be testing many different code variants, a shared The only caveat here is that our thinking was that the actual arrays would be able to be re-provisioned all the time. E.g; test with RAID 10 with x stripe size, Software RAID 6, what is the real difference between 28 spindles with RAID 5 versus 10? Well, we need some workspace that won't go away when that happens. I'd suggest that the OS and people's home directories be mounted on a permanent partition with plenty of space for source code, say a few tens of GB, and then there be a farm of data workspace that's understood to be transient and can be reconfigured as needed for tests like that. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg 8.3beta 2 restore db with autovacuum report
andy wrote: with autovacuum enabled with default settings, cramd.sql is 154M: [EMAIL PROTECTED]:/pub/back$ time pg_restore -Fc -C -d postgres cramd.sql real3m43.687s [...] Now I dropdb and disable autovacuum, restart pg: [EMAIL PROTECTED]:/pub/back$ time ( pg_restore -Fc -C -d postgres cramd.sql; vacuumdb -z cramd ) real3m47.229s user0m9.933s sys 0m0.744s Sweet, about the same amount of time. Thanks. I find it strange that it takes 3 minutes to restore a 150 MB database ... do you have many indexes? Even though the restore times are very similar, I find it a bit disturbing that the CREATE INDEX is shown to be waiting. Was it just bad luck that the ps output shows it that way, or does it really wait for long? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] AutoVacuum Behaviour Question
Jeff Amiel wrote: Bruce Momjian wrote: No, it isn't. Please add a TODO item about it: * Prevent long-lived temp tables from causing frozen-Xid advancement starvation Can somebody explain this one to me? because of our auditing technique, we have many LONG lived temp tables.(one per pooled connection)...so as long as the pool isn't disturbed, these temp tables can exist for a long time (weeksmonths?) Hmm. The problem is that the system can't advance the frozen Xid for a database when there are temp tables that live for long periods of time. Autovacuum can't vacuum those tables; if the app vacuums them itself then there's no problem, but you can only vacuum them in the same session that creates it. The problem with a frozen Xid (datfrozenxid) that doesn't advance is of Xid-wraparound nature. The system eventually shuts itself down to prevent data loss, so if those temp tables live a really long life, you could be subject to that. (The immediate symptom is that pg_clog segments do not get recycled, which is not serious because it's just wasted disk space, and it's not a lot). (previous thread about our use of temp tables and autovacuum/xid issues) http://archives.postgresql.org/pgsql-general/2007-01/msg00690.php http://archives.postgresql.org/pgsql-general/2007-01/msg00691.php Sorry, I'm offline ATM and can't check those. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Cuando miro a alguien, más me atrae cómo cambia que quién es (J. Binoche) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] beta2, I can't set UTF-8 for czech locales on win
Pavel Stehule wrote: On 01/11/2007, Tom Lane [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: When I try manually rebuild cluster I had second problem: C:\PostgreSQL\bininitdb -D ../data The program postgres is needed by initdb but was not found in the same directory as C:\PostgreSQL\bin/initdb. Check your installation. Do you have the same problem previously reported that postgres -V doesn't work? If so, maybe taking out libxml2 will help? No, it is different problem. It was my beginner mistake :(. I run initdb as Administrator. It needs maybe some hint message. With runas initdb works. IMHO we should check for an Administrator user and reject it explicitely. The error message is way too obscure. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Test lab
On Fri, 02 Nov 2007 15:20:27 -0400 Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: My question is -hackers, is who wants first bite and what do they want :) Something I'd like to have back real soon is the daily DBT run against CVS HEAD that Mark Wong was doing at OSDL. Maybe we don't need a particularly enormous machine for that, but comparable runs day after day are real nice for noting when patches had unexpected performance impacts... I expect the processors in this system to be faster than what I was using but this system does have about a third of the number of spindles I had previously. In my spare time I am trying to complete a TPC-E implementation (dbt5) to the current spec revision and it is supposed to have significantly less disk requirements than the TPC-C derivative (dbt2) I was using in the past. If we believe TPC-E achieved all its goals, I think it would be appropriate to start using that as soon as the kit is ready. Anyway want to help with the kit? :) It's the C stored functions that need to be revised. Regards, Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg 8.3beta 2 restore db with autovacuum report
Alvaro, On 11/2/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Even though the restore times are very similar, I find it a bit disturbing that the CREATE INDEX is shown to be waiting. Was it just bad luck that the ps output shows it that way, or does it really wait for long? I did the test again with the reference database I used a month ago. My previous figures with 8.3devel of October 1st were: - autovacuum off: 14m39 - autovacuum on, delay 20: 51m37 With 8.3devel of today, I have: - autovacuum on, delay 20: 15m26 I can see (CREATE INDEX|ALTER TABLE) waiting from time to time in my watch -n 1 but it disappears within 1 or 2 seconds so what Simon and you did seems to work as expected AFAICS. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg 8.3beta 2 restore db with autovacuum report
Alvaro Herrera wrote: andy wrote: with autovacuum enabled with default settings, cramd.sql is 154M: [EMAIL PROTECTED]:/pub/back$ time pg_restore -Fc -C -d postgres cramd.sql real3m43.687s [...] Now I dropdb and disable autovacuum, restart pg: [EMAIL PROTECTED]:/pub/back$ time ( pg_restore -Fc -C -d postgres cramd.sql; vacuumdb -z cramd ) real3m47.229s user0m9.933s sys 0m0.744s Sweet, about the same amount of time. Thanks. I find it strange that it takes 3 minutes to restore a 150 MB database ... do you have many indexes? Even though the restore times are very similar, I find it a bit disturbing that the CREATE INDEX is shown to be waiting. Was it just bad luck that the ps output shows it that way, or does it really wait for long? There are about 800 tables, each has one index. Most tables (75%) are very small, the rest have, maybe 50K rows. I had to run the ps several times to catch it waiting. It didnt seem to wait too long. It was run on my laptop, which may not have the best io times in the world (and it only has 512 Meg ram). -Andy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
