Re: [HACKERS] HOT for PostgreSQL 8.3
Hannu Krosing <[EMAIL PROTECTED]> writes: > How about adding a new 2-byte field to header for in-page c_tid poiner > for HOT ? We just finished sweating blood to get the tuple header size down to 23 bytes from 27 (which saves 8 bytes not 4 if MAXALIGN=8). We are not going to blow that again on HOT. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Foreign keys for non-default datatypes, redux
I wrote: >> * Add an oid[] column to pg_constraint that stores the equality operator >> OIDs for a foreign-key constraint, in the same column order as conkey[] >> and confkey[]. It turns out this isn't sufficient: ri_Check_Pk_Match() wants to generate PK = PK checks, and the PK = FK operator isn't the right one for that. The information I suggested adding to pg_constraint isn't enough to let it find out which operator is the right one. We could handle this in a couple of ways: 1. Add yet another column with PK=PK operator OIDs to pg_constraint. 2. Add a column with the underlying PK index's OID to pg_constraint, and expect ri_Check_Pk_Match to dredge the info from that. This is probably possible, but not exactly trivial because of which-column-is-which considerations. 3. Leave pg_constraint alone and expect ri_Check_Pk_Match to look in pg_depend to find out the underlying PK index, then proceed as in #2. >From an efficiency standpoint #1 seems the best, and yet it seems a bit ugly. Not that the others aren't. Comments, other ideas? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] select from sequences
"Jaime Casanova" <[EMAIL PROTECTED]> writes: > and why is this a feature? i mean, what real use it has? To find out the parameters of an existing sequence. Remove it and you'll break pg_dump, to say nothing of any other application. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
Marc Munro <[EMAIL PROTECTED]> writes: > Consider a table C containing 2 child records C1 and C2, of parent P. > If transaction T1 updates C1 and C2, the locking order of the the > records will be C1, P, C2. Another transaction, T2, that attempts to > update only C2, will lock the records in order C2, P. > The locks on C2 and P are taken in different orders by the two > transactions, leading to the possibility of deadlock. But the lock on P is shared, hence no deadlock. 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] Ooops ... seems we need a re-release pronto
"D'Arcy J.M. Cain" writes: > Well, that is covered in the system that I took that from. The full > description is; > 1. Identify a bug or missing feature. > 2. Write the test that proves the bug or missing feature. > 3. Run the test to prove that it fails. > 4. Code until the test passes and then stop. > 5. Run the regression test to make sure you didn't break something. > This is taken from the principles of extreme programming. The above is all fine as a development methodology. The question is whether such tests are strictly a short-term development aid, or need to be memorialized in a fashion that will cause every other developer to re-execute them every time that developer needs to test his own work, for the indefinite future. I tend to think there are not that many tests that really deserve that status. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] select from sequences
On 2/11/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Jaime Casanova" <[EMAIL PROTECTED]> writes: > just for curiosity, why the code doesn't throw an error when using > sequences in the from_list of a select? That's a feature, not a bug. and why is this a feature? i mean, what real use it has? -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] select from sequences
"Jaime Casanova" <[EMAIL PROTECTED]> writes: > just for curiosity, why the code doesn't throw an error when using > sequences in the from_list of a select? That's a feature, not a bug. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] select from sequences
Hi, just for curiosity, why the code doesn't throw an error when using sequences in the from_list of a select? allow selects from sequences confuses many people about the correct way of using sequences -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HOT for PostgreSQL 8.3
On 2/12/07, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: Hannu Krosing wrote: > Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: >> Hannu Krosing <[EMAIL PROTECTED]> writes: >>> What if we would just reuse the root tuple directly instead of turning >>> it into a stub ? >>> This would create a cycle of ctid pointers, which changes the lookup >>> process from 'follow ctid chaint until the end' to 'follow the tid chain >>> until you reach the start'. >> How do you know which one is newest? > > By xmin,cmin of course . > >> What happens when you have to put a newer version off-page for lack of space? > > Then this scheme won't work. Couldn't the ctid of the latest tuple point to the off-page tuple as usual? It could. But then you may lose reference for older version(s). We can do the whole page scans to locate older versions, but that might prove too costly It might be acceptable, if it was only stored on those tuples that are (HOT) updated. But it's not clear to me what you're proposing to do with the field, anyway, Which tuples would have it, and what would it point to? My guess what Hannu is suggesting is to have a circular chain of HOT-updated tuples in a page. The index can point to any tuple in the chain. When update goes off-page or is a COLD update, t_ctid points to the newer version as usual. So a tuple which is COLD updated would need two pointers, one which points to the oldest version in the circular on-page chain and other which points to the new version. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] Documentation on WAL/buffer cache/checkpoint internals
I have a project I'm working on that involves a deep look at WAL and checkpoint performance; a patch related to this topic will be ready to release shortly. The company I'm working for was kind enough to allow me to release the documentation I wrote for them while trying to understand the code involved, and I just posted the first public version of that to my web site: http://www.westnet.com/~gsmith/content/postgresql/ As most of the hacker-level documentation I've read about the PostgreSQL codebase was aimed more at query and data type related work, this topic seemed liked it could use some more coverage. I apologize for the crappy Word->HTML conversion; wanted to get a draft out rather than delaying for reformatting work first. My agenda here includes: 1) Would love to get feedback and corrections. I know there's errors in there but can't think of anyone but this group who could tell me what they are. 2) Does anyone else have documentation on this topic they might contribute now that I've started a framework for it, or documentation suggestions for what else should be convered here? I can't be the only one who's looked into this topic, but I didn't find much information on it out there. 3) I'm open to suggestions for where to put this information at in a form more integrated into the community. That's part of why I didn't sweat the formatting yet, I'm expecting at least one more format conversion before I'm done. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Sun, 2007-11-02 at 12:21 -0600, Jim C. Nasby wrote: > On Thu, Feb 08, 2007 at 08:47:42AM -0800, Marc Munro wrote: > > One of the causes of deadlocks in Postgres is that its referential > > integrity triggers can take locks in inconsistent orders. Generally a > > child record will be locked before its parent, but not in all cases. > > Where would PostgreSQL lock the parent before the child? AFAIK the > behavior should be consistent... Consider a table C containing 2 child records C1 and C2, of parent P. If transaction T1 updates C1 and C2, the locking order of the the records will be C1, P, C2. Another transaction, T2, that attempts to update only C2, will lock the records in order C2, P. The locks on C2 and P are taken in different orders by the two transactions, leading to the possibility of deadlock. __ Marc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Ooops ... seems we need a re-release pronto
On Sun, 11 Feb 2007 12:30:45 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > "D'Arcy J.M. Cain" writes: > > How about a rule that says no new ode without a test? > > We've got way too many tests like that already, ie, a bunch of > mostly-redundant functional tests of isolated new features. > Most of the code I worry about there isn't any simple way to > test from the SQL level --- the fact that a query gives the > right answer doesn't prove it went through a particular part > of the planner, for example. Well, that is covered in the system that I took that from. The full description is; 1. Identify a bug or missing feature. 2. Write the test that proves the bug or missing feature. 3. Run the test to prove that it fails. 4. Code until the test passes and then stop. 5. Run the regression test to make sure you didn't break something. Step 3. is the critical one from the point of view of your concern. Having a test that can't fail is worse than no test. This is taken from the principles of extreme programming. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] HOT for PostgreSQL 8.3
Hannu Krosing wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: Hannu Krosing <[EMAIL PROTECTED]> writes: What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers, which changes the lookup process from 'follow ctid chaint until the end' to 'follow the tid chain until you reach the start'. How do you know which one is newest? By xmin,cmin of course . What happens when you have to put a newer version off-page for lack of space? Then this scheme won't work. Couldn't the ctid of the latest tuple point to the off-page tuple as usual? As long as the index points to any tuple in the update chain, an index scan can even scan the whole page to find all the earlier tuples in the chain. The latest version can always be found by following the c_tid pointers, so that would only be needed to find an older version of the row. How about adding a new 2-byte field to header for in-page c_tid poiner for HOT ? It grows header from 26 to 28 bytes, but for MAXALIGN=4 the space usage would stay the same. Actually the tuple header is now 23 bytes, thanks to combo (aka phantom) cids. Adding a new 2-byte field would take it over 24 bytes again, making it either 28 or 32 bytes depending on MAXALIGN. It might be acceptable, if it was only stored on those tuples that are (HOT) updated. But it's not clear to me what you're proposing to do with the field, anyway, Which tuples would have it, and what would it point to? -- 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
Re: [HACKERS] PostgreSQL and OpenLdap
Sorry All, I go to see another list. Cris. On 2/11/07, Peter Eisentraut <[EMAIL PROTECTED]> wrote: Cristiano Panvel wrote: > This is my first post in the list. This list is for discussing the development of PostgreSQL. Please take your usage questions to a different list. http://www.postgresql.org/community/lists/ -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] PostgreSQL and OpenLdap
Cristiano Panvel wrote: > This is my first post in the list. This list is for discussing the development of PostgreSQL. Please take your usage questions to a different list. http://www.postgresql.org/community/lists/ -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] PostgreSQL and OpenLdap
Hi Friends, This is my first post in the list. I am not obtaining authentication my users of PostgreSQL in OpenLdap. 1) PostgreSQL was compiled with the support to ldap in a FreeBSD System. "option --with-ldap for configure". 2) The user also exists in the base of the OpenLdap 3) Already I also added the line of configuration in "pg_hba.conf" and in the "pg_service.conf" $ tail /etc/pg_service.conf ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br $ cat /usr/local/pgsql/data/pg_hba.conf local all all trust hostall all 10.193.4.0/24 md5 DITI/CBMSC. ldap://ldap.cb.sc.gov.br/dc=cb,dc=sc,dc=gov,dc=br 4) To create the usuary I make thus # su - pgsql % createuser scott After the made configurations, I try to effect login and is error of password. remote# psql postgresql -h server -U scott -W psql: FATAL: password authentication failed for user "scott" It will be that somebody could help me, "I am making something incorrect !!!" http://www.postgresql.org/docs/current/interactive/libpq-ldap.html Thanks Cris. ---(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] HOT for PostgreSQL 8.3
Pavan Deolasee wrote: On 2/11/07, Hannu Krosing <[EMAIL PROTECTED]> wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > What if we would just reuse the root tuple directly instead of turning > > it into a stub ? > > This would create a cycle of ctid pointers, which changes the lookup > > process from 'follow ctid chaint until the end' to 'follow the tid chain > > until you reach the start'. > > How do you know which one is newest? By xmin,cmin of course . This sounds interesting. But we might have trouble supporting HOT-update when tuple length changes. May be we can release the space consumed by the dead root tuple and have fresh allocation if the tuple length increases. I don't see a problem with length changes. We're free to rearrange data on the page whenever we can acquire the vacuum lock. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy -> strlcpy (not complete)
Peter Eisentraut wrote: > [EMAIL PROTECTED] wrote: > > I think we had this discussion already. strncpy() copies N bytes, > > whereas strlcpy() copies only as many bytes as necessary. For short > > strings with larger buffers, strlcpy() wins. It's understood that > > in many cases in PostgreSQL, the expectation is for short strings, > > and it is not required for the later bytes to be '\0'. > > You may also speculate that strncpy() is more optimized in some C > libraries than strlcpy(). However, the changed cases are all > uninteresting in terms of performance or fall under the short strings > in long buffers case. > > The remaining uses of StrNCpy() are either inner loops which need to be > investigated, or it's not clear whether the zero-filling of strncpy() > is required, or it's in a library were the libpgport linkages needs to > be added. > > The main idea here is to get this programming style out because it's > become clear that people are very confused about how to use some of the > other functions correctly. Sorry, I was confusing this with MemSet. Thanks for the clarification. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] wishlist items ..
Pavel Stehule wrote: Postponed The following features are unlikely to make it into 8.3 at this point: * SQL/PSM support per SQL:2003 (Pavel) * Clustered/replication solutions SQL/PSM is available (without resignal stmt and extended diagnostic stmt). Some people work on doc translation to english. experience I waiting for real using and real experience with it. ok thanks .. updated on the wishlist .. I also got feedback from Gavin, which is also incorporated already. regards, Lukas please, can you add this link http://postgresql.interweb.cz/index.php/SQL/PSM Pavel _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] wishlist items ..
Pavel Stehule wrote: Postponed The following features are unlikely to make it into 8.3 at this point: * SQL/PSM support per SQL:2003 (Pavel) * Clustered/replication solutions SQL/PSM is available (without resignal stmt and extended diagnostic stmt). Some people work on doc translation to english. experience I waiting for real using and real experience with it. ok thanks .. updated on the wishlist .. I also got feedback from Gavin, which is also incorporated already. regards, Lukas ---(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] HOT for PostgreSQL 8.3
On 2/11/07, Hannu Krosing <[EMAIL PROTECTED]> wrote: Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > What if we would just reuse the root tuple directly instead of turning > > it into a stub ? > > This would create a cycle of ctid pointers, which changes the lookup > > process from 'follow ctid chaint until the end' to 'follow the tid chain > > until you reach the start'. > > How do you know which one is newest? By xmin,cmin of course . This sounds interesting. But we might have trouble supporting HOT-update when tuple length changes. May be we can release the space consumed by the dead root tuple and have fresh allocation if the tuple length increases. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Reducing likelihood of deadlocks (was referential Integrity and SHARE locks)
On Thu, Feb 08, 2007 at 08:47:42AM -0800, Marc Munro wrote: > One of the causes of deadlocks in Postgres is that its referential > integrity triggers can take locks in inconsistent orders. Generally a > child record will be locked before its parent, but not in all cases. Where would PostgreSQL lock the parent before the child? AFAIK the behavior should be consistent... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] HOT for PostgreSQL 8.3
Ühel kenal päeval, P, 2007-02-11 kell 12:35, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > What if we would just reuse the root tuple directly instead of turning > > it into a stub ? > > This would create a cycle of ctid pointers, which changes the lookup > > process from 'follow ctid chaint until the end' to 'follow the tid chain > > until you reach the start'. > > How do you know which one is newest? By xmin,cmin of course . > What happens when you have to put a newer version off-page for lack of space? Then this scheme won't work. How about adding a new 2-byte field to header for in-page c_tid poiner for HOT ? It grows header from 26 to 28 bytes, but for MAXALIGN=4 the space usage would stay the same. > regards, tom lane -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] HOT for PostgreSQL 8.3
Hannu Krosing <[EMAIL PROTECTED]> writes: > What if we would just reuse the root tuple directly instead of turning > it into a stub ? > This would create a cycle of ctid pointers, which changes the lookup > process from 'follow ctid chaint until the end' to 'follow the tid chain > until you reach the start'. How do you know which one is newest? What happens when you have to put a newer version off-page for lack of space? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Ooops ... seems we need a re-release pronto
"D'Arcy J.M. Cain" writes: > How about a rule that says no new ode without a test? We've got way too many tests like that already, ie, a bunch of mostly-redundant functional tests of isolated new features. Most of the code I worry about there isn't any simple way to test from the SQL level --- the fact that a query gives the right answer doesn't prove it went through a particular part of the planner, for example. I think we need some intelligent test design, not tests thrown in to meet a rule. regards, tom lane ---(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] Acclerating INSERT/UPDATE using UPS
Tom and all, I apologize destroying the thread information with this topic. Unfortunately my basic smtp server does not work now and I am writing all the responses via gmail tonight... Tom, Thanks for teaching me about the development assumption of PostgreSQL. The assumption and my direction are different since the assumption considers only disk drive as persistent device while my assumption is battery-supplied memory. Honestly speaking, I am not sure whether there is a person who accepts my assumption (i.e. battery-supplied memory as persistent device) or not. And I am not sure whether my approach can be integrated into PostgreSQL since some accept and some reject. So, anyway I will write a patch and submit. Then I leave to this community the decision of accept/reject. If someone has interest on Sigres, please download it from http://sourceforge.jp/projects/sigres/ and try it. I will continue to accelerate Sigres more anyway under my assumption. Since I believe "time always wins in transaction processing" as Jim Gray told last June in ACM SIGMOD 2006 keynote talk, I wish UPS will be reliable or nice non volatile memories such as MRAM will appear in the near future. Finally, this discussion was really beneficial for me. I would like to say thank you for everyone who gave me great information. Thank you all ! Best Regards, -- Hideyuki Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Tom Lane wrote: I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. Absolutely agreed there. Just to be clear: I believe our current assumptions can be stated as "Postgres will not lose data if the kernel and disk drive do not lose data that they have acknowledged as being successfully fsync'd." This is independent of any questions about Postgres bugs or measures that we take to limit the impact of our bugs --- it's about what our extent of responsibility is. I think that Hideyuki-san is proposing a different contract for data integrity, and I want to understand what that contract is and why someone would want it. regards, tom lane ---(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] Acclerating INSERT/UPDATE using UPS
Tom, Thanks for your comments ! Let me answer to your beneficial mail. Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; The motivation of my work is accelerating INSERT/UPDATE without losing data integrity guarantees. In the first place, I thought *IF* a battery supplied memory can be considered as a persistent device, Sigres method works. However, my assumption seems not to be accepted unfortunately. Without sending my first email, I could not know your informative comments. Thank you ! there are plenty of ways to do that already :-(. Could you please let me know or give me some pointers for the important information ? For ubiquitous computing researchers or sensor data processing researchers, that is a great concern. Since INSERT/UPDATE is too heavy, several universities have developed stream processing engines (SPE) which do not store any data. Also I have already published more than 4 international conference papers in this topic. Moreover, I have been developing a brand-new DBMS from scratch in my research project. I am all ears ! Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. In the first place, I thought Sigres method works *IF* UPSs are reliable. This was my assumption. However, I am in the process of understanding that unfortunately UPSs may not be reliable usually through this discussion :-( On the other hand, however, there not negligible people who hopes to dramatically accelerate INSERT/UPDATE such as financial companies or sensor data processing research groups such as our team. For example, In our team, the default postgresql is too slow and thus sensor data insertions delays over an hour. The purpose of our team is detecting real-world events in real-time, but it does not work because of bad performance. In such a case, Sigres is useful if I can believe UPS. . I am not confident whether my explanation is clear or not. If unclear, sorry. Best Regards, -- Hideyuki Tom Lane wrote: Gene <[EMAIL PROTECTED]> writes: ... just my two cents. on a side note, would putting the wal on a tmpfs partition give you something similar? Indeed, I'm wondering why one needs to hack the Postgres core to throw away data integrity guarantees; there are plenty of ways to do that already :-(. Hideyuki-san has not explained exactly what integrity assumptions he wants to make or not make. I'm surely willing to listen to supporting a different set of assumptions than we currently use, but I'd like to see a clear explanation of what assumptions are being made and why they represent a useful case. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Andrew, Your experience is really informative. Thanks for giving me such a really precious information. Since I am a researcher, I rarely faces on real troubles. Andrew, I agree with you. UPS is not reliable all the time. On the other hand, however, disks or raids are also not reliable all the time. Moreover, on the earth, there is NO device which completely assures durability of the ACID transaction property. Even if disks and power supplies are perfect, the system would be destroyed by an earthquake (thus, disaster recovery techniques are recently popular). Thus I think the problem is not in a device. It is in the management of system administrators. (However, this is just my opinion and it is not objective one required by large questionnaire). On the other hand, speed is required. My first motivation of this work is a friend of mine who is working at a financial firm. He strongly required performance of UPDATE/INSERT on the database servers in his company (the database was a major commercial product). I think in such a rich environment, strong power management facilities may be expected though this is just my one thought). In summary, I think the problem lies in system management rather than device itself. Regards, -- Hideyuki J. Andrew Rogers wrote: On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote: The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. Ask me about the time a year ago that I had a 24x7 database, with two power supplies connected to two UPSes on independent mains circuits, dropped dead because one UPS was overloaded (more than one server connected to it, apparently too much) and the other UPS was simply dead (undetected zombie UPS), when a catastrophic power failure killed both of the generator backed mains circuits. I wasn't pleased, but it happened nonetheless. A UPS is not a 100% guarantee of anything. They fail more often than they should. No amount of paranoia guarantees uptime. That said, I see plenty of use for loosening restrictions on databases where the contents do not matter and a little loss is acceptable. Cheers, J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
Magnus Hagander wrote: > On Tue, Jan 30, 2007 at 11:45:24AM -0500, Tom Lane wrote: >> Magnus Hagander <[EMAIL PROTECTED]> writes: >>> But I guess maybe the added check has to be not just (!syslogger_started) >>> but (!syslogger_started && is_postmaster)? >> That would at least get you out of the problem of having to transmit the >> syslogger_started flag to the backends... > > Here's a patch that does just this. Since nobody complained and it passed all my tests, I've gone ahead and applied this patch to head and back-patched to 8.1 and 8.2. //Magnus ---(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] Acclerating INSERT/UPDATE using UPS
Christopher,
Thanks for your advices !
OK, I will submit a patch as soon as possible.
In the patch, Sigres will be activated by postgresql.conf.
Best Regards,
-- Hideyuki
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED]
(Hideyuki Kawashima) wrote:
> I appreciate your great suggestion!
> It is great honor for me if Sigres will be merged to PostgreSQL.
> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> incorporating Sigres into PostgreSQL would be easy.
You should consider submitting a patch for this against CVS HEAD.
And actually, I'd think it a better idea to define a GUC variable and
use that to control whether Sigres is active or not.
At the more sophisticated end of the spectrum, you might set things up
so that it could be activated/deactivated at runtime by a superuser.
At the less sophisticated end, it might need to be configured in
postgresql.conf...
-- output = ("cbbrowne" "@" "acm.org") http://linuxfinances.info/info/
If you've done six impossible things this morning, why not round it
off with breakfast at Milliways, the Restaurant at the End of the
Universe? ---(end of
broadcast)--- TIP 3: Have you checked our
extensive FAQ? http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher, Thanks for your comments and I am sorry for my bad response because of my business. In the first place, criticisms are welcome since without criticisms and discussions, I cannot improve my idea. Christopher, thanks to your comments, I understood why this community does not incline to accept Sigres approach. Reason 1: Sigres method may make PostgreSQL less robust. Reason 2: There is a not-inconsiderable risk that people will fail to read warnings Let me write my opinions for the above. Opinion for Reason 1: Right. Apparently Sigres method makes PostgreSQL less robust. Thus I propose to use Sigres method as an option like fsync=off. In my understanding, fsync=off option is ambiguous. I think unfortunately it is unclear when exactly the option should be used. Even if one uses UPS, the option is dangerous because the final issue_xlog_sync called by XLogWrite called by CreateCheckPoint called by ShutdownXLOG called by bgWriter process at postgres shutdown is not executed. (Sorry for the exact but wired descriptions). Without using UPS, the option never be used. On the other hand, *IF* UPS is reliable, Sigres assures synchronization of wal records to a disk because Sigres executes issue_xlog_fsync at each XLogFileClose() and at shutdown. That is, the question would be expressed as "Can an administrator have a reliable UPS system ?" If an administrator is confident on his/her UPS system, the administrator may select Sigres method. In summary, fsync=off is ambiguous option, and Sigres can be used *IF* an administrator is confident on his/her UPS system. Opinion for Reason 2: Right. I think it is easy to avoid the risk. Sigres should not be used in default such as fsync=off. If my opinion lacks in common sense, I am sorry. Regards, -- Hideyuki Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick & informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. One may try to mitigate that problem by supporting the memory device with multiple power supplies *and* multiple UPSes. But there is a not-inconsiderable risk that people will fail to read warnings, deploy databases in a way that leaves them exposed to total loss, and then lay blame at this community's feet. I'm sure you can understand why the community might resist that... ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] XML export
Tino Wildenhain wrote: > > > > linda > > 19 > > (0.9,6.1) > > 100 > > ^ > > > How would you express null in the values above? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] XML export
Peter Eisentraut schrieb:
The issue of XML export has been discussed a few times throughout
history. Right now you've got the HTML output in psql. A few
people have proposed "real" XML output formats in psql or elsewhere.
I dug out some old code today that implements what SQL/XML has to say
on the matter and fitted the code to work with the current XML support
in the backend.
Below are examples of what it can do. I'm thinking about hosting this
on PgFoundry, but if the crowd thinks this should be somewhere else,
short of the moon, let me know.
regression=# select table_to_xml('select * from emp');
table_to_xml
---
sharon
25
(15,12)
1000
sam
...
linda
19
(0.9,6.1)
100
(1 row)
As a use case of sorts, I've got an XSLT stylesheet that can convert
this to HTML tables.
regression=# select table_to_xmlschema('select * from emp');
table_to_xmlschema
-
(1 row)
I also have a table function which can convert both of these back into
an table, so that would be XML import. But that doesn't work quite yet.
How would you express null in the values above?
Regards
Tino
---(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] Ooops ... seems we need a re-release pronto
On Sat, 10 Feb 2007 10:36:56 +0100 Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > I'm not concerned so much about the runtime as the development and > > maintenance effort... > > Shouldn't we at least add the one or two exemplary statements that > failed so we have some sort of coverage of the problem? How about a rule that says no new ode without a test? That's one of the ways that extreme programming is applied to new projects. Basically what that means is that bugs continue to be found but we never see the same bug twice because the regression test catches those. Of course, while you are creating one test you can always add a few related ones. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] XML export
Pavel Stehule wrote: > I thought about some special function. But why not? COPY is perfect > for this task. I don't understand what you are asking for. Please show an example. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] wishlist items ..
Postponed The following features are unlikely to make it into 8.3 at this point: * SQL/PSM support per SQL:2003 (Pavel) * Clustered/replication solutions SQL/PSM is available (without resignal stmt and extended diagnostic stmt). Some people work on doc translation to english. experience I waiting for real using and real experience with it. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] XML export
Pavel Stehule wrote: > If you integrate xml_export to core, you don't need string argument, > which isn't too handy, but you can use COPY stmt aparat. I don't > speak about enhancing stmt COPY. Then what do you speak about? I thought about some special function. But why not? COPY is perfect for this task. Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] [Fwd: [COMMITTERS] pgsql: Fix for early log messages during postmaster startup getting lost]
Hi, It is very good to see Magnus among committers. Congrats Magnus! Cheers, Devrim Forwarded Message > From: Magnus Hagander <[EMAIL PROTECTED]> > To: pgsql-committers@postgresql.org > Subject: [COMMITTERS] pgsql: Fix for early log messages during > postmaster startup getting lost > Date: Sun, 11 Feb 2007 07:59:26 -0400 (AST) > > Log Message: > --- > Fix for early log messages during postmaster startup getting lost when > running as a service on Win32. > > Per report from Harald Armin Massa. > > Modified Files: > -- > pgsql/src/backend/postmaster: > postmaster.c (r1.519 -> r1.520) > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/postmaster.c.diff?r1=1.519&r2=1.520) > pgsql/src/backend/utils/error: > elog.c (r1.181 -> r1.182) > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/error/elog.c.diff?r1=1.181&r2=1.182) > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[HACKERS] wishlist items ..
Aloha, I just wanted to bring up the wishlist todo items: http://developer.postgresql.org/index.php/Todo:WishlistFor83 All items have a name behind them aside from "Better handling of partitioning". Does anyone feel responsible for handling that one? Anyways I would appreciate it if people who's name is behind each of the items to either mail this list or me directly to let me know if they are still handling this item for 8.3 or not. I will then make sure to update the list accordingly. The applies if an item is missing. For convenience here is the current state: The following items are on the table for 8.3: * On disk bitmap index (Gavin) * WITH RECURSIVE hierarchical queries (Gregory Stark) * Better handling of partitioning * SQL:2003 windowing queries (Gavin) * Improvements to autovac (Alvaro Herrera) * SQL/XML support per SQL:2003 (Peter) * Notification payload messages (Andrew Dunstan) * Fix permissions properly on custom GUC vars (Andrew Dunstan) * Create a mechanism for plperl to load modules safely (Andrew Dunstan) * Plan invalidation (Tom, Heikki) * Tsearch2 in core (Teodor Sigaev, Oleg Bartunov) * Grouped Index Tuples (Heikki) * Hot Updates (Pavan) * Reuse of index tuples (Simon Riggs) * ENUM (Tom Dunstan) * GENERATED/IDENTITY patches (Zoltan Boszormenyi) * Updatable VIEWs (Bernd) Postponed The following features are unlikely to make it into 8.3 at this point: * SQL/PSM support per SQL:2003 (Pavel) * Clustered/replication solutions Completed * Operator family rewrite (Tom) regards, Lukas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Ooops ... seems we need a re-release pronto
On Sat, Feb 10, 2007 at 12:20:35PM -0500, Tom Lane wrote: > I do > agree with adding a test when you think it is likely to be able to catch > a whole class of errors, or even a specific error if it seems especially > likely to recur, but right now I'm not seeing how we do that here. Well, currently the regression tests only make a handful of functional indexes, and never insert any data into any of them. So arguably there's a benefit to just adding a handful of inserts and updates somewhere to test these. That a whole area of code not currently tested. In my memory I remember a site that displayed the code coverage of the regression tests, but I can't find it now. Does anybody know? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] [COMMITTERS] pgsql: StrNCpy -> strlcpy (not complete)
[EMAIL PROTECTED] wrote: > I think we had this discussion already. strncpy() copies N bytes, > whereas strlcpy() copies only as many bytes as necessary. For short > strings with larger buffers, strlcpy() wins. It's understood that > in many cases in PostgreSQL, the expectation is for short strings, > and it is not required for the later bytes to be '\0'. You may also speculate that strncpy() is more optimized in some C libraries than strlcpy(). However, the changed cases are all uninteresting in terms of performance or fall under the short strings in long buffers case. The remaining uses of StrNCpy() are either inner loops which need to be investigated, or it's not clear whether the zero-filling of strncpy() is required, or it's in a library were the libpgport linkages needs to be added. The main idea here is to get this programming style out because it's become clear that people are very confused about how to use some of the other functions correctly. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] XML export
Pavel Stehule wrote: > If you integrate xml_export to core, you don't need string argument, > which isn't too handy, but you can use COPY stmt aparat. I don't > speak about enhancing stmt COPY. Then what do you speak about? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] tsearch2: enable non ascii stop words with C locale
Hi,
Currently tsearch2 does not accept non ascii stop words if locale is
C. Included patches should fix the problem. Patches against PostgreSQL
8.2.3.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
*** wordparser/parser.c~2007-01-16 00:16:11.0 +0900
--- wordparser/parser.c 2007-02-10 18:04:59.0 +0900
***
*** 246,251
--- 246,266
static int
p_islatin(TParser * prs)
{
+ if (prs->usewide)
+ {
+ if (lc_ctype_is_c())
+ {
+ unsigned int c = *(unsigned int*)(prs->wstr +
prs->state->poschar);
+
+ /*
+* any non-ascii symbol with multibyte encoding
+* with C-locale is a latin character
+*/
+ if ( c > 0x7f )
+ return 1;
+ }
+ }
+
return (p_isalpha(prs) && p_isascii(prs)) ? 1 : 0;
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] HOT for PostgreSQL 8.3
Ühel kenal päeval, K, 2007-02-07 kell 17:38, kirjutas Simon Riggs: > When we try to UPDATE a tuple and the new tuple version doesn't fit on > the block, we get the BufferCleanupLock if possible and then perform a > single-block VACUUM. Any tuple that is both HEAP_DEAD & > HEAP_ONLY_TUPLE > can be removed completely. This is possible by changing the t_ctid > field > so that it points at the first visible-to-someone tuple in the chain, > so > it points "over" the previous HOT tuples. The root tuple is also dead > - > it cannot be removed completely, so it is replaced it with "just a > TupleHeader", which is referred to as a TupleStub. (Credit to Itagaki > for this concept). What if we would just reuse the root tuple directly instead of turning it into a stub ? This would create a cycle of ctid pointers, which changes the lookup process from 'follow ctid chaint until the end' to 'follow the tid chain until you reach the start'. It also allows one to chill tuples for which all other tuples except the root are dead by just flipping the HOT flag bit (or juts making the ctid chain ptr to point to self.) > The single-block VACUUM would alter *all* tuple chains on the block, > not just the one for the current tuple being UPDATEd. > > This technique means that a tuple never changes its CTID, so > everything that currently uses CTID can continue normally. SeqScan > would also work identically to the way it works today. > > It also means that we can't easily remove the root tuple, even if it > is now just a TupleStub (unless the whole chain is also removable > because of DELETE). Removing the root tuple will require a VACUUM > *FULL*. Even so, this option is still space-neutral in the worst-case, > in comparison with inserting index tuples. if you can afford changing xmin and xmax, you can actually do it in 2 steps - first do an "update" that moves the tuple to hot chain root position, and then, after the original is dead for all backends, just mark it as not hot. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.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
Re: [HACKERS] XML export
Hello If you integrate xml_export to core, you don't need string argument, which isn't too handy, but you can use COPY stmt aparat. I don't speak about enhancing stmt COPY. Regards Pavel Stehule p.s. it's can be great if xmloutput will be independent on datestyle root=# set datestyle TO German ; SET root=# select xmlelement(name bbb, current_date); xmlelement --- 11.02.2007 _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
