[HACKERS] Problem with mailing list?
Is everything ok with postgres mail server? I have problem to send mail to hackers list and pgadmin-hacker as well. If somebody is on cc, he receives mail correctly, but it does not appear in the list. Any suggestion? Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] An Idea for planner hints
On Tue, Aug 22, 2006 at 11:56:17AM -0700, Mark Dilger wrote: I proposed something like this quite a bit up-thread. I was hoping we could have a mode in which the system would run the second, third, fourth, ... best plans rather than just the best looking one, and then determine from actual runtime statistics which was best. (The proposal also included the ability to output the best plan and read that in at a later time in lieu of a SQL query, but that part of it can be ignored if you like.) The posting didn't generate much response, so I'm not sure what people thought of it. The only major problem I see is getting the planner to keep track of alternate plans. I don't know the internals of it very well, but I think the genetic query optimizer doesn't have a concept of runner-up #1, runner-up #2, etc., which it would need to have. I think the biggest issue is that you'd have to account for varying load on the box. If we assume that the database is the only thing running on the box, we might be able to do that by looking at things like how much IO traffic we generated (though of course OS caching will screw with that). Actually, that's another issue... any plans run after the first one will show up as being artificially fast, since there will be a lot of extra cached data. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Enum proposal / design
On Thu, Aug 17, 2006 at 08:02:32PM -0400, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Jim C. Nasby wrote: If there was a mechanism to obtain field widths from the catalog there would be no need to store the field width in each tuple. This would be useful for other types as well (UUID and ENUM, for example). I don't think there is concensus on adding that. Well, it's pie-in-the-sky at the moment because we have no credible design for doing it. Whether any given proposal would get accepted would depend on what its downsides were. Do we (or should we) have a TODO section for blue sky research ideas? I'd not object to putting an item like this in such a section. But for most of the TODO items we have a reasonably clear idea of what we're talking about, so this doesn't seem to belong in with the rest. Blue sky ideas just don't seem natural on the TODO list. Some people wanted to use a wiki, and maybe it would be good for that. I think it would be good to have something, so that people are occasionally reminded about these things. That's a good way to help shake ideas out. Something else to consider is that anything is doable, given enough effort, which is an argument for just putting it on the TODO. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert Treat Sent: 23 August 2006 04:16 To: pgsql-hackers@postgresql.org Cc: Peter Eisentraut; Tom Lane Subject: Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors) I've always been quite amazed how much email some of the folks here manage to process... I suppose I could just chalk it up to a pine vs. gui thing, but I suspect there are some other tricks people have to make emails more manageable (anyone combine all pg mail to one folder?) More or less - one for -www, webmaster and slaves stuff, and another for -odbc, -hackers, -patches, -committers, -perform, -general and so on. I do keep additional ones for FG and -core though. Everything is auto-filtered at our Exchange server so it's organised as I like whether I pick it up on PDA, webmail, PC or Mac. Regards, Dave. ---(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] Problem with mailing list?
Is everything ok with postgres mail server? I have problem to send mail to hackers list and pgadmin-hacker as well. If somebody is on cc, he receives mail correctly, but it does not appear in the list. Any suggestion? This problem first occurred when I sign into pgadmin-hacker list. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Hi, Robert Treat [EMAIL PROTECTED] writes: On Tuesday 22 August 2006 16:10, Tom Lane wrote: As I see it, we've effectively got a patch that was rejected once, and Bruce wants to apply it anyway because no replacement has been forthcoming. Well, unless someone is going to commit to doing it the other way, it seems the guy who actually codes something offers a better solution than handwaving... people have also had plenty of time to come up with a replacement if that's what they really wanted. The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. regards, tom lane I am willing to get it up to shape and support both COPY (select) TO and COPY view TO, the second is rewritten as SELECT * FROM view. In fact, I already started. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgstattuple extension for indexes
On Fri, Aug 18, 2006 at 09:15:59AM +0900, Satoshi Nagayasu wrote: ITAGAKI Takahiro wrote: But the method has the above problem. So I suggest to use whether the right link points to the next adjacent page or not. if (opaque-btpo_next != P_NONE opaque-btpo_next != blkno + 1) stat-fragments++; Well, in that way, following two conditions, [1] [x] [2] [y] [3] and [3] [x] [2] [y] [1] will be calculated as same fragmentation ratio(100%), I can't agree with that, because both will generate different costs while index scan in the real world (I don't care about page splitting algorithm now). What about just reporting the correlation of pages in the index, as well as fragmentation? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Problem with mailing list?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zdenek Kotala Sent: 23 August 2006 08:07 To: Josh Berkus; Bruce Momjian; pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: [HACKERS] Problem with mailing list? Is everything ok with postgres mail server? I have problem to send mail to hackers list and pgadmin-hacker as well. If somebody is on cc, he receives mail correctly, but it does not appear in the list. Any suggestion? This problem first occurred when I sign into pgadmin-hacker list. The problem with your recent pgadmin-hackers mail is that it was too large (~150KB iirc) thus was automatically dropped by the list server. BTW, infrastructure related mails such as this should probably go to pgsql-www or webmaster, and me or scrappy if required. In particular Bruce Josh are certainly the wrong people! Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Autovacuum on by default?
On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: If there's a bunch of activity on a table but stats are reset before a vacuum is run on it and then a vacuum is run, the user will still be left thinking that the table needs to be vacuumed. Except that autovac *won't* vacuum it if the stats have been reset. So I'm not seeing that there's really a problem in practice. IIRC the stats also include info about regular (manual) vacuums, so the above scenario still applies. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum on by default?
On Wed, Aug 23, 2006 at 01:45:43PM +0900, ITAGAKI Takahiro wrote: Jim C. Nasby [EMAIL PROTECTED] wrote: And +1 on Rod's suggestion to make it more aggressive. I always drop the scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem unreasonable), and typically drop the thresholds to 200 and 100 (though again, lower is probably warrented). The default fillfactors for index btree is 90%. So if we want to avoid spliting of the leaf pages, vacuum scale factors should be less than 0.1 in cases where tuples are only updated randomly. I think threshoulds should be less than PCTFREEs(=1-fillfactors) except ever-increasing tables. Very good point, though at least for indexes the new code that tries to reclaim space on a page before splitting it will help. Doesn't help for the heap, though. So maybe the default should be 0.08? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Problem with mailing list?
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zdenek Kotala Sent: 23 August 2006 08:07 To: Josh Berkus; Bruce Momjian; pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: [HACKERS] Problem with mailing list? Is everything ok with postgres mail server? I have problem to send mail to hackers list and pgadmin-hacker as well. If somebody is on cc, he receives mail correctly, but it does not appear in the list. Any suggestion? This problem first occurred when I sign into pgadmin-hacker list. The problem with your recent pgadmin-hackers mail is that it was too large (~150KB iirc) thus was automatically dropped by the list server. I had similar problem with pgsql-hackers list yesterday. I sent 45kB to the list. I expect that It was rejected too by this rule? Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with mailing list?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 23 August 2006 08:56 To: Dave Page Cc: pgsql-hackers@postgresql.org; [EMAIL PROTECTED] Subject: Re: [HACKERS] Problem with mailing list? I had similar problem with pgsql-hackers list yesterday. I sent 45kB to the list. I expect that It was rejected too by this rule? Iirc, the limit is 40KB. It's much higher on -patches though if that helps. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] news server does not respond
Christopher Browne wrote: Yeah, and you can't complain when you're cut off... :-) yeah, known problem... I used gmane to track the list, but... Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tricky bugs in concurrent index build
Ühel kenal päeval, T, 2006-08-22 kell 16:48, kirjutas Tom Lane: Joshua D. Drake [EMAIL PROTECTED] writes: It's fairly clear that we could support concurrent builds of nonunique indexes, but is that enough of a use-case to justify it? I believe there would be. Most PostgreSQL users I run into, develop in production, which means being able to add an index they forgot when doing query analysis. True, unique constraints are usually something you should get right to start with. But it'll be annoying if we can do everything BUT that :-( Maybe we could find a way to build a non-unique index first and then convert it to a unique one later, in yet another pass ? -- 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] [PATCHES] COPY view
--On Dienstag, August 22, 2006 23:12:21 -0400 Tom Lane [EMAIL PROTECTED] wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. What are these open issues for the updatable views patch you are seeing exactly? I'm currently trying to update this patch based on alvaros comments in the code and i see the INSERT...RETURNING stuff as the only big hurd at the moment (however, i haven't looked at this closer, but saw your and Jaime's comments on this...). It would be nice if we could summarize all open things so everybody who is able to work on this gets a complete overview. -- Thanks Bernd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tricky bugs in concurrent index build
Ühel kenal päeval, K, 2006-08-23 kell 11:05, kirjutas Hannu Krosing: Ühel kenal päeval, T, 2006-08-22 kell 16:48, kirjutas Tom Lane: Joshua D. Drake [EMAIL PROTECTED] writes: It's fairly clear that we could support concurrent builds of nonunique indexes, but is that enough of a use-case to justify it? I believe there would be. Most PostgreSQL users I run into, develop in production, which means being able to add an index they forgot when doing query analysis. True, unique constraints are usually something you should get right to start with. But it'll be annoying if we can do everything BUT that :-( Maybe we could find a way to build a non-unique index first and then convert it to a unique one later, in yet another pass ? Or even add ALTER INDEX myindex ADD/DROP UNIQUE; command -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] seahorse again failing
It may be a good idea to put a elog(LOG) with the error code in the failure path of AllocateFile. That seems like a plan to me. I had been thinking of making win32error.c itself log the conversions, but that would not provide any context information. AllocateFile could log the file name along with the code, which should be enough info to associate a particular log entry with the actual failure. Note you should probably save and restore errno around the elog call, just to be safe. Could someone with access to Windows code and test this? All this seems good and sensible. I am just a little suspicious of seahorse, though, as it is running on a Xen VM. I wonder if we should add a VM column to the buildfarm machine specs. Definitly. If nothing else, it should at least be listed in the platform identificagtion. AFAIK, Snake is also a VM, and Daves other box as well... But on VMWare (or was it Virtual Server?) and not Xen, but still. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] seahorse again failing
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Hagander Sent: 23 August 2006 09:25 To: Andrew Dunstan; Tom Lane Cc: Alvaro Herrera; Stefan Kaltenbrunner; PostgreSQL-development Subject: Re: [HACKERS] seahorse again failing Definitly. If nothing else, it should at least be listed in the platform identificagtion. AFAIK, Snake is also a VM, and Daves other box as well... But on VMWare (or was it Virtual Server?) and not Xen, but still. No, Snake is real. Bandicoot is a VMWare Server VM running on Snake though. /D ---(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] pg_upgrade: What is changed?
I'm working on pg_upgrade concept. I try to determine what is changed from 8.1 to 8.2. There is list of important areas for upgrade and suggested action. 1) BKI - catalog. There are a lot of changes. See attached file. a) There is new table pg_shdescription action: create b) There is new record in some tables. action: insert question: Should be any OID conflict during insertion? c) Some records are changed action: ??? d) Some record are removed action: keep it question : Is it safe to keep for example record in the pg_proc? 2) GUC - postgresql.conf a) New variable. action: nothing b) Some changes (default, context) action: nothing c) backslash_quote is removed action: removed from postgresql.conf There is list of GUC changes: ssl (assign hook) array_nulls (new) escape_string_warning (default false-true, context PGC_INTERNAL-PGC_USERSET) allow_system_table_mods (new) ignore_system_indexes (new) post_auth_delay (new) join_collapse_limit (description) backslash_quote (removed) search_path (default $user,public - \$user\,public) 3) pg_hba.conf, pg_ident.conf no changes 4) Disk layout is same - version 3 5) Tuples question: Does have data types some disk representation? Does have tupleheader same structure? 6) Indexes question: Any changes on disk representation? Should be possible remove all index before upgrade and recreate them on the new version instead upgrade index structure? 7) Change PG_VERSION files 8) WAL/XLOG Question: Should be deleted? 9) ... Any other ideas? Zdenek bki.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tricky bugs in concurrent index build
Is it not possible to brute force this adding an AM method to insert without the uniqueness check? Hm. Actually there already is a feature of aminsert to allow suppressing the unique check, but I'm not sure whether using it for RECENTLY_DEAD tuples helps. Seems like we have to wait to see whether DELETE_IN_PROGRESS deleters commit in any case. Um, but if we wait for the DELETE_IN_PROGRESS tuple, after the wait we can add it eighter with or without the unique check (depending on commit/abort). Then at least we don't need to wait in a 3rd pass for readers ? Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_upgrade: What is changed?
On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote: snip 1) BKI - catalog. c) Some records are changed action: ??? They just need to be changed. In principle the datalog needs to be updated so it looks like a database initdb'd with the new version. 5) Tuples question: Does have data types some disk representation? Does have tupleheader same structure? I think only the inet/cidr types changed format this release. Ways to handle that have been discussed: 1. Have server contain code for old versions under old OID. This was mentioned as a possibility. 2. ALTER TYPE ALTER TYPE from old to new type, possibly using text as intermediary. 3. Fiddle with bits on disk (not sure if this is even possible) 6) Indexes question: Any changes on disk representation? Should be possible remove all index before upgrade and recreate them on the new version instead upgrade index structure? Maybe, maybe not. Why risk it? Just REINDEX the whole database afterwards. 8) WAL/XLOG Question: Should be deleted? I imagine you should probably force a checkpoint and then wipe the wal records. The WAL isn't going to be able to cover some of the stuff done during the upgrade, so it'd be useless after anyway. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] COPY view
On Tue, Aug 22, 2006 at 01:11:22PM -0400, Andrew Dunstan wrote: There's nothing hidden (unless it's also hidden from me ;-) ) I take it that when you talk about we did this you are referring to the patch from Karel Zak. Hans has been original author of COPY VIEW idea and I've wrote it for his customer (yes, it was sponsored work). Karel -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Leaving... (was: Re: [HACKERS] [PATCHES] COPY view)
Hi all, seriously... I don't have time to work on PostgreSQL. It's time to say that I'm leaving this project. So, if you found some my broken code or whatever in PostgreSQL you should go and fix it. It's community-driven project. It's about collaboration -- don't ask why should I help -- go and help! It was nice time and really big experience, but in the world is more projects and many of them need more help than already stable (do you remember PostgreSQL 6.5? :-) and very reliable PostgreSQL. Good bye! Karel On Tue, Aug 22, 2006 at 11:12:21PM -0400, Tom Lane wrote: The patch submitter has neither provided an updated patch nor defended his original submission as being the right thing. If he doesn't take it seriously enough to have done any followup, why should the rest of us? -- Karel Zak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Tricky bugs in concurrent index build
Tom Lane [EMAIL PROTECTED] writes: What I think we can do about this is to include DELETE_IN_PROGRESS tuples in the set of candidate tuples to insert in the second pass. During the merge step that verifies whether the tuple is already in the index, if we find that it's not, then we must wait for the deleter to commit or roll back. If the deleter commits then we ignore the tuple. If the deleter rolls back then we have to insert the tuple in the index. (I think we have to actually take a FOR UPDATE or possibly FOR SHARE lock on the tuple while we do this, else we have race conditions against someone else starting a new deletion attempt on the tuple.) Hm, my first thought was to just try to get a lock on the record which would inherently wait until the deleter commits or aborts. But then wouldn't we have deadlock risks? If we come across these records in a different order from someone else (possibly even the deleter) who also wants to lock them? Or would it be safe to lock and release them one by one so we only every hold one lock at a time? I'm also pondering whether it might be worth saving up all the DELETE_IN_PROGRESS tuples in a second tuplesort and processing them all in a third phase. That seems like it would reduce the amount of waiting that might be involved. The fear I have though is that this third phase could become quite large. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication
Ühel kenal päeval, E, 2006-08-21 kell 21:46, kirjutas Fujii Masao: Stefan Kaltenbrunner wrote: It is however async replication so you can loose data commited on the master but not yet replicated to the slaves in case you loose the master completely. Yes, here is an insufficient point of Slony-I, i think. Most systems will not permit the committed data to be lost, so use is limited. But any sync _replication_ system will have severe impact on performance. My guess is that for a full sync replication, going from 1 server to 2 will actually lower performance andsome small gains would be possible only starting from 3rd server. This has nothing to do with postgreSQL, but is just due to latencies of memory, disk and network and the need to do remote locking. My quess is based on using standard 100Gb ethernet. It may be possible to do better on some more advanced interconnects like myrinet. -- 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] Enum proposal / design
Jim C. Nasby [EMAIL PROTECTED] writes: I think it would be good to have something, so that people are occasionally reminded about these things. That's a good way to help shake ideas out. I think the only reason there aren't more outrageous dreamworld ideas in the TODO is that people came along and did a lot of them. 3-phase-commit, nested-transactions, PITR, etc. were all at some point pretty pie in the sky. At some level there's not much point in keeping a TODO of ideas we know how to do, most of those ideas just get done. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Where is hstore?
AFAIR the authors have never proposed it for inclusion. We'll be glad if hstore will be in main tarball. As I remember, when we suggest (may be, in private exchange of letters) to include it, somebody says that hstore breaks relational in db. Lastest version is located at http://www.sigaev.ru/cvsweb/cvsweb.cgi/hstore/ Note, in this year there are a several questions about inclusion in mail lists: http://archives.postgresql.org/pgsql-hackers/2006-05/msg00079.php http://archives.postgresql.org/pgsql-hackers/2006-07/msg00727.php http://archives.postgresql.org/pgsql-hackers/2006-08/msg01412.php And several questions in private mail... Now hstore is stable module. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Replication
Ühel kenal päeval, E, 2006-08-21 kell 15:00, kirjutas D'Arcy J.M. Cain: On Mon, 21 Aug 2006 14:46:05 -0400 Gregory Maxwell [EMAIL PROTECTED] wrote: On 8/21/06, Alvaro Herrera [EMAIL PROTECTED] wrote: But the confirmation that needs to come is that the WAL changes have been applied (fsync'ed), so the performance will be terrible. So bad, that I don't think anyone will want to use such a replication system ... Okay. I give up... Why is waiting for fsync on a fast local network which takes 15us to send a message (infiniband is cheap..) an unimaginable delay when we tolerate a local 8ms fsync delay on systems without writeback cache? OK, that solves your problem. How about my problem where replication has to happen on servers in three countries on two continents and thousands of updates a second have to happen in less that 10ms? For this scenario you are far better off with partitioning than replication. That is if your data is partitionable. But geographically distributed data often is. This is the critical issue with replication - one size does not fit all. Syncronous replication, in particular, fits almost no one. My experience is that any replication needs to be based on your business rules which will vary widely. -- 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 6: explain analyze is your friend
Re: [HACKERS] Question about (lazy) vacuum
Ühel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark: With all this code to handle ignoring vacuum transactions in calculating the global xmin it just occurred to me to wonder: Does lazy vacuum need a transaction at all? It doesn't do the tuple moving tricks with xvac that vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't it just use GetTopTransactionId instead of its own xid? Would this cause any problems? When I asked the same question, I was told that a lot of core functionality vacuum uses needs to be in transaction. I guess bad things can happen, if some other backend ends a transaction you claim to be in. And it is not so much about what ends up in tables, but about what other backends think. BTW, I think that CONCURRENT CREATE INDEX should be modified to use long transactions which actually build the index and are ignored by vacuum and short ones which write data to system tables and are not ignored. That way we have one less obstacle for keeping high-update tables in shape. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] Replication
Hannu Krosing wrote: But any sync _replication_ system will have severe impact on performance. My guess is that for a full sync replication, going from 1 server to 2 will actually lower performance andsome small gains would be possible only starting from 3rd server. Only testing will show concrete results, but for sure such a general answer does not make much sense. It all depends very much on the type of your load. Especially the read/write ratio is very important. Another important factor is the amount of conflicting transactions. My quess is based on using standard 100Gb ethernet. It may be possible to do better on some more advanced interconnects like myrinet. 100Gb ethernet already sounds very advanced... ;-) Regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication
Ühel kenal päeval, K, 2006-08-23 kell 13:09, kirjutas Markus Schiltknecht: Hannu Krosing wrote: But any sync _replication_ system will have severe impact on performance. My guess is that for a full sync replication, going from 1 server to 2 will actually lower performance andsome small gains would be possible only starting from 3rd server. Only testing will show concrete results, but for sure such a general answer does not make much sense. It all depends very much on the type of your load. Especially the read/write ratio is very important. True. But if you have very few writes, then there seems no reason to do sync anyway. Another important factor is the amount of conflicting transactions. That too, but just the need to do *any* locking on all nodes will significantly slow down sync replication My quess is based on using standard 100Gb ethernet. It may be possible to do better on some more advanced interconnects like myrinet. 100Gb ethernet already sounds very advanced... ;-) Yeah, I meant some mix of 100Mb and 1Gb ;) Regards Markus -- 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 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] Replication
Hannu Krosing wrote: But if you have very few writes, then there seems no reason to do sync anyway. I think there is one: high-availability. A standby-server which can continue if your primary fails. Of course sync is only needed if you absolutely cannot effort loosing any committed transaction. Another important factor is the amount of conflicting transactions. That too, but just the need to do *any* locking on all nodes will significantly slow down sync replication If you implement sync replication with locking, yes. But there are better ways: the Postgres-R approach does not do network locking, but aborts conflicting transactions just before committing. That results in much less network traffic (one GCS-message per writing-transaction). Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tricky bugs in concurrent index build
Tom Lane [EMAIL PROTECTED] writes: In the past, the only way we could see HEAPTUPLE_INSERT_IN_PROGRESS or HEAPTUPLE_DELETE_IN_PROGRESS was for tuples created/deleted by our own transaction, and so the actions taken by IndexBuildHeapScan are to include in the index in both cases, but exclude DELETE_IN_PROGRESS tuples from the uniqueness check. This does not work for a concurrent build, though, because if the in-progress delete is from another transaction, it could roll back after we look. In that case we have an entry that is in the index and has escaped the uniqueness check. If it conflicts with another tuple also entered into the index in the first pass, we'll never notice that. I think we can solve this by having IndexBuildHeapScan not index DELETE_IN_PROGRESS tuples if it's doing a concurrent build. The problem of old transactions trying to use the index does not exist, because we'll wait 'em out before marking the index valid, so we need not worry about preserving validity for old snapshots. And if the deletion does in fact roll back, we'll insert the tuple during the second pass, and catch any uniqueness violation at that point. Actually that's a bit of a pain. This function is called from the AM functions so it doesn't have any access to whether it's doing a concurrent build or not. I would have to stuff a flag in the indexInfo or change the AM api. The API is pretty bizarre around this. The core calls the AM to build the index, the AM calls back this function in core to do the scan, then this function calls back into the AM to handle the inserts. It seems like it would be simpler to leave the core in charge the whole time. It would call an AM method to initialize state, then call an AM method for each tuple that should be indexed, and lastly call a finalize method. Also, I think there may be another problem here with INSERT_IN_PROGRESS. I'm currently testing unique index builds while pgbench is running and I'm consistently getting unique index violations from phase 1. I think what's happening is that insert that haven't committed yet (and hence ought to be invisible to us) are hitting unique constraint violations against older versions that are still alive to us. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Where is hstore?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, Aug 23, 2006 at 01:39:34PM +0400, Teodor Sigaev wrote: AFAIR the authors have never proposed it for inclusion. We'll be glad if hstore will be in main tarball. FWIW, I'd be glad too. As I remember, when we suggest (may be, in private exchange of letters) to include it, somebody says that hstore breaks relational in db. This might be true, and I'm definitely not the one to judge that, but then... geometrical data types do the same, right? But it is way cool... imho the coolest application of gist (this combination of bitmap hash and gist). Lastest version is located at http://www.sigaev.ru/cvsweb/cvsweb.cgi/hstore/ Note, in this year there are a several questions about inclusion in mail lists: http://archives.postgresql.org/pgsql-hackers/2006-05/msg00079.php http://archives.postgresql.org/pgsql-hackers/2006-07/msg00727.php http://archives.postgresql.org/pgsql-hackers/2006-08/msg01412.php And several questions in private mail... Now hstore is stable module. I'm planning to try my hands on using it as an rdf triple store. Thanks for your good work - -- tomas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFE7D/bBcgs9XrR2kYRAic0AJ4hmwrvnX6zNirPqJL7ygD2wLkAIgCeLsA2 xXlELrI45f12/TQG3Xrn2sA= =fiG6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tricky bugs in concurrent index build
[Sorry for the duplicate -- I accidentally sent the previous before I was finished editing it] Tom Lane [EMAIL PROTECTED] writes: I think we can solve this by having IndexBuildHeapScan not index DELETE_IN_PROGRESS tuples if it's doing a concurrent build. The problem of old transactions trying to use the index does not exist, because we'll wait 'em out before marking the index valid, so we need not worry about preserving validity for old snapshots. And if the deletion does in fact roll back, we'll insert the tuple during the second pass, and catch any uniqueness violation at that point. Actually that's a bit of a pain. This function is called from the AM functions so it doesn't have any access to whether it's doing a concurrent build or not. I would have to stuff a flag in the indexInfo or change the AM api. The API is pretty bizarre around this. The core calls the AM to build the index, the AM calls back this function in core to do the scan, then this function calls back into the AM to handle the inserts. It seems like it would be simpler to leave the core in charge the whole time. It would call an AM method to initialize state, then call an AM method for each tuple that should be indexed, and lastly call a finalize method. Also, I think there may be another problem here with INSERT_IN_PROGRESS. I'm currently testing unique index builds while pgbench is running and I'm consistently getting unique index violations from phase 1. I think what's happening is that insert that haven't committed yet (and hence ought to be invisible to us) are hitting unique constraint violations against older versions that are still alive to us. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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_upgrade: What is changed?
Martijn van Oosterhout wrote: On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote: snip 1) BKI - catalog. c) Some records are changed action: ??? They just need to be changed. In principle the datalog needs to be updated so it looks like a database initdb'd with the new version. Yes I agree but The question is if some OID collision should appears for example collision between user function created in the 8.1 and build-in function in 8.2. There are some changes like this: ! insert OID = 1838 ( numeric_variance 11 10 12 f f t f i 1 1700 1231 _null_ _null_ _null_ numeric_variance - _null_ ) ! insert OID = 1838 ( numeric_var_samp 11 10 12 f f t f i 1 1700 1231 _null_ _null_ _null_ numeric_var_samp - _null_ ) Is only renaming or is it different proc? 5) Tuples question: Does have data types some disk representation? Does have tupleheader same structure? I think only the inet/cidr types changed format this release. Ways to handle that have been discussed: 1. Have server contain code for old versions under old OID. This was mentioned as a possibility. 2. ALTER TYPE ALTER TYPE from old to new type, possibly using text as intermediary. 3. Fiddle with bits on disk (not sure if this is even possible) It looks like that name is same but data representation is different. I'm not sure if possible use same data type names with different OID. I think there is unique index. We can rename this type to inet_old, but some application should confused. I quickly looked in network.c and It looks that data size is same (4 or 16). Option 3 is possible in this case and I should be easy to implement it. Does anybody know what exactly has been changed? 6) Indexes question: Any changes on disk representation? Should be possible remove all index before upgrade and recreate them on the new version instead upgrade index structure? Maybe, maybe not. Why risk it? Just REINDEX the whole database afterwards. Will be possible run REINDEX database with damaged/old index data structure? For example on inet/cidr index? Zdenek ---(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] [PATCHES] Use of backslash in tsearch2
Patch isn't full, simple test (values are took from regression.diffs): and try dump table and restore: ERROR: syntax error CONTEXT: COPY tt, line 5, column tq: '1 ''2' Attached cumulative patch fixes problem, but I have some doubts, is it really needed? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ttt.gz Description: Unix tar archive ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Question about (lazy) vacuum
Hannu Krosing [EMAIL PROTECTED] writes: When I asked the same question, I was told that a lot of core functionality vacuum uses needs to be in transaction. I guess bad things can happen, if some other backend ends a transaction you claim to be in. And it is not so much about what ends up in tables, but about what other backends think. Well the only way other backends would find out about vacuum's xid is via tables or via the PGProc table. But they aren't going to care about vacuum's transaction status in PGProc unless they're vacuum and then we're going out of our way to make sure it doesn't care. Vacuum doesn't take any record locks so the xid isn't necessary for that. Vacuum does WAL log some entries via log_heap_clean but I'm unclear whether that requires a transaction, I don't see it being used anywhere. BTW, I think that CONCURRENT CREATE INDEX should be modified to use long transactions which actually build the index and are ignored by vacuum and short ones which write data to system tables and are not ignored. That way we have one less obstacle for keeping high-update tables in shape. Hm, that might be worth thinking about. Note that it locks out vacuum from running on the table it's running on so it would only help in allowing other tables to be vacuumed effectively. If we ever get per-table xmin then it would be entirely unnecessary. -- Gregory Stark 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] [PATCHES] COPY view
Bernd Helmle [EMAIL PROTECTED] writes: What are these open issues for the updatable views patch you are seeing exactly? Didn't Alvaro list a bunch of issues when he put the patch back up for comment? I have not looked at it myself yet. i see the INSERT...RETURNING stuff as the only big hurd at the moment That's not the fault of the updatable-views patch, but it definitely is something we need to put some time into :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tricky bugs in concurrent index build
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, K, 2006-08-23 kell 11:05, kirjutas Hannu Krosing: Maybe we could find a way to build a non-unique index first and then convert it to a unique one later, in yet another pass ? Or even add ALTER INDEX myindex ADD/DROP UNIQUE; command That would be great. But note that it suffers from precisely the same problem. If you come across a couple of records with the same key and one of them is DELETE_IN_PROGRESS then you'll have to wait until you can acquire a sharelock on it before you can determine if there's a constraint violation. Hmmm. Or is that true. The problem may be somewhat easier since at least you can be sure every tuple in the heap is in the index. So if you see a DELETE_IN_PROGRESS either it *was* a constraint violation prior to the delete and failing is reasonable or it's an update in which case maybe it's possible to detect that they're part of the same chain? (Actually there is another corner case. a transaction that inserts a value, then deletes it in the same transaction then inserts that same value again. Now you have a INSERT_IN_PROGRESS and a DELETE_IN_PROGRESS that conflict but should be allowed since they come from the same transaction. Hopefully the ALTER INDEX command would be able to determine they come from the same transaction.) In the case of concurrent index builds that's not really safe since you don't have the other tuples you're conflicting with together at the same time and even if you did you may or may not have a complete set of them. Tom's right. This stuff is tricky. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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] Tricky bugs in concurrent index build
Greg Stark [EMAIL PROTECTED] writes: But then wouldn't we have deadlock risks? If we come across these records in a different order from someone else (possibly even the deleter) who also wants to lock them? Or would it be safe to lock and release them one by one so we only every hold one lock at a time? AFAICS we could release the lock as soon as we've inserted the index entry. (Whether there is any infrastructure to do that is another question...) I'm also pondering whether it might be worth saving up all the DELETE_IN_PROGRESS tuples in a second tuplesort and processing them all in a third phase. That seems like it would reduce the amount of waiting that might be involved. The fear I have though is that this third phase could become quite large. Actually --- a tuple that is live when we do the second pass scan could well be DELETE_IN_PROGRESS (or even RECENTLY_DEAD) by the time we do the merge and discover that it hasn't got an index entry. So offhand I'm thinking that we *must* take a tuple lock on *every* tuple we insert in stage two. Ugh. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Queries joining views
Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: I'm confused too. Would it be possible for you to send me a dump of your database? Attached is a cleaned out database, the full schema is included, but only the relevant tables contain any data. Thanks. After digging through it a bit, I understand what's happening, but I'm not seeing any simple fix. The problem is basically that you've got create or replace view mm_product as SELECT ... FROM mm_product_table JOIN mm_object mmop USING (number); and then the problem query has WHERE mm_product.number = insrel.snumber which causes the planner to conclude that mm_product_table.number, mm_object.number, and mm_insrel_table.snumber are all basically interchangeable. In particular it ends up performing the join between mm_product_table.number and mm_object.number as though mm_product_table.number were being joined to mm_insrel_table.snumber. It's even worse, I guess, as the mm_insrel view joins mm_insrel_table with mm_object again. So basically the query performs a self-join on mm_object with a detour through mm_insrel_table and mm_product_table... Which is fine, except that it's thinking that the statistics for mm_object.number are applicable in this context, and they're completely misleading. After the join to mm_insrel_table, the statistics of the variable are really like mm_insrel_table.number --- in particular the fraction of the table that has to be visited is much larger than it would've been for mm_object as a whole. I don't entirely understand what you're saying here. Mm_object is always larger than any other table in the database, as every table joins with (different) records in it to determine it's otype and owner. So I don't understand how a fraction of any of those tables could be larger than mm_object as a whole... In fact, originally the schema used inheritance; every table inherited (directly or indirectly) from mm_object. As this resulted in unions, which caused much more performance problems than the current view-approach, I implemented the current approach. In fact, this approach was lent from what MMBase uses for the MSSQL layer. Well, as I implemented the way the views are defined, there is room for changes in that area. Suggestions are welcome. This is a problem we've understood in a generic form for awhile: a join or selection might change the statistics of a variable, and so the info stored in the catalogs ought to be modified somehow to predict what will happen at upper join levels. We've not seen it in this particular form before, though. I'm not sure if there's a whole lot you can do about it in the near term other than refactor your schema to avoid having different tables joining to different subranges of mm_object.number. (You don't necessarily have to get rid of mm_object --- just try assigning its keys from a serial, or something, so that there's no correlation to the ranges of keys in other tables.) Unfortunately the number key is required to correlate to the number keys in other tables. That's the whole point of that table. It's also already generated from a sequence... I am looking at a view options at the moment: 1.) Cluster mm_object on an index over otype - I'm not sure how that would influence the statistics; if it doesn't then this wouldn't change much. 2.) Change mm_object into a view over the tables that now join with it. I'll have to devise some way to get the otype and owner columns into the other tables. 3.) An extension to option 2; Creating seperate tables, only containing the relevant sections from mm_object, combining them into a view-version of mm_object. Like this: CREATE TABLE mm_product_object ( number integer PRIMARY KEY, otype integer, owner text ); CREATE TABLE mm_insrel_object ( number integer PRIMARY KEY, otype integer, owner text ); (I recall seeing an inheritance-like statement that makes copies of table definitions - seems useful in this case) CREATE OR REPLACE VIEW mm_object AS SELECT * FROM mm_product_object UNION ALL SELECT * FROM mm_insrel_object; It remains to be seen that MMBase can handle mm_object being a view, but (if not) it probably will work if it's an updatable view. I'm leaning to option 3, but if option 1 helps it saves me a lot of trouble. We might be able to do something about actually solving the statistical problem in 8.3, but I fear it's too late to think about it for 8.2. Well, I had hoped for a suitable workaround, and I believe I may have a few options now. Waiting for the next PostgreSQL release never really was an option for us (deadline is somewhere next week). So it doesn't really matter to us that there won't be a solution until 8.3, or maybe even later. Thanks for the help so far, glad to be able to point out an actual problem. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F:
Re: [HACKERS] Tricky bugs in concurrent index build
Greg Stark [EMAIL PROTECTED] writes: It seems like it would be simpler to leave the core in charge the whole time. It would call an AM method to initialize state, then call an AM method for each tuple that should be indexed, and lastly call a finalize method. [ shrug... ] I'm uninterested in refactoring the AM API right now. We've got enough stuff to deal with before beta, not to mention an uncommitted bitmap AM patch that it would certainly break. Also, I think there may be another problem here with INSERT_IN_PROGRESS. I'm currently testing unique index builds while pgbench is running and I'm consistently getting unique index violations from phase 1. I think what's happening is that insert that haven't committed yet (and hence ought to be invisible to us) are hitting unique constraint violations against older versions that are still alive to us. Hmm ... it's certainly highly likely that we could pick up multiple versions of a row during pass 1, but the uniqueness checker should notice that some versions are dead? Oooh, no there's a problem: the tuple we are inserting could become dead in the interval between when we pick it up and when we put it into the index. So we could try to put multiple versions of the same row into the uniqueness check. Right at the moment, unique index builds with this mechanism are looking unfixably broken :-(. Anyone see any chance at all of making them work? Maybe we should just cut our losses and go with the nonunique case only. That one is pretty easy: just stuff everything in the index ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Queries joining views
Alban Hertroys [EMAIL PROTECTED] writes: Mm_object is always larger than any other table in the database, as every table joins with (different) records in it to determine it's otype and owner. So I don't understand how a fraction of any of those tables could be larger than mm_object as a whole... No, I said a larger fraction, not a larger absolute number of tuples. The problem is that because mm_product contains only very small values of number, a mergejoin looks like a great way to join it to mm_object: only the first 5% of mm_object will need to be scanned. The bug consists in applying that 5% number to mm_insrel, for which it's not correct. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
--On Mittwoch, August 23, 2006 08:24:55 -0400 Tom Lane [EMAIL PROTECTED] wrote: What are these open issues for the updatable views patch you are seeing exactly? Didn't Alvaro list a bunch of issues when he put the patch back up for comment? I have not looked at it myself yet. Indeed he did and this helps a lot to clean up some parts of the code (oh, thanks to him for reviewing this, i think i forgot that :( ). I thought you were refering to some specific showstoppers i've missed. -- Thanks Bernd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tricky bugs in concurrent index build
Greg Stark [EMAIL PROTECTED] writes: Hmmm. Or is that true. The problem may be somewhat easier since at least you can be sure every tuple in the heap is in the index. So if you see a DELETE_IN_PROGRESS either it *was* a constraint violation prior to the delete and failing is reasonable or it's an update in which case maybe it's possible to detect that they're part of the same chain? Unless we are willing to lock every single tuple while we insert it, this seems unfixable to me. Without a lock, the tuple could become DELETE_IN_PROGRESS immediately after we look at it. Actually it's worse than that. We could examine a tuple, see that it's good, include it in the uniqueness check. Then someone updates the tuple and puts the new version near the end of the table. By the time we reach that version, it could be committed good. There is absolutely no way that we could notice an issue without applying extremely expensive tests to *every* apparently-good tuple. [ thinks for a bit... ] At least, it seems hopeless if we use SnapshotNow. Does it help if we use a real snapshot? I'm thinking pass 1 inserts exactly those tuples that are good according to a snap taken at its beginning, and then pass 2 considers only tuples that are good according to a snap taken at *its* beginning. But having consumed no caffeine yet this morning, I'm not sure I can spot any flaws that might exist in this idea. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Question about (lazy) vacuum
Ühel kenal päeval, K, 2006-08-23 kell 08:11, kirjutas Greg Stark: Hannu Krosing [EMAIL PROTECTED] writes: BTW, I think that CONCURRENT CREATE INDEX should be modified to use long transactions which actually build the index and are ignored by vacuum and short ones which write data to system tables and are not ignored. That way we have one less obstacle for keeping high-update tables in shape. Hm, that might be worth thinking about. Note that it locks out vacuum from running on the table it's running on so it would only help in allowing other tables to be vacuumed effectively. Exactly. The whole point of one vacuum not blocking others is to make sure, that a vacuum on a huge table would not disable vacuuming and thereby reusing rows of much much smaller tables. Locking out vacuum on the table itself is ok, as the runtimes of vacuum and concurrent create index on a same table are in the same order. -- 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] [PATCHES] COPY view
Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. So when will you send in a revised patch? cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: Hi, Tom Lane wrote: At the moment, with the online-index and updatable-views patches both pretty seriously broken, and no sign that the bitmap-index people are awake at all, I might take it on myself to fix this one instead of those others. But is that what I should be spending my time on in the waning days of the 8.2 freeze cycle? Speak now or hold your peace. Personally, I would say that this is less important than updatable views but more than online indexes. If it could be fixed just for the view case in a day or so then I think it's worth it. cheers andrew It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. So when will you send in a revised patch? cheers andrew Soon. :-) Best regards, Zoltán Böszörményi ---(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] [PATCHES] COPY view
Böszörményi Zoltán wrote: It seems I was able to get it working for both the VIEW and SELECT cases. I still have one issue, the reference to the select is left open and it complains on closing the transaction. But basically works. Cool, thanks. Send the patch and we can look it over to see what you're missing. It may be something simple like shutting down the executor node or something. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] seahorse again failing
Tom Lane wrote: It would be interesting to know the actual underlying Windows error code --- I see that win32error.c maps several different codes to EACCES. It may be a good idea to put a elog(LOG) with the error code in the failure path of AllocateFile. That seems like a plan to me. I had been thinking of making win32error.c itself log the conversions, but that would not provide any context information. AllocateFile could log the file name along with the code, which should be enough info to associate a particular log entry with the actual failure. Note you should probably save and restore errno around the elog call, just to be safe. Could someone with access to Windows code and test this? Do you mean something as simple as this? compiles, passes regression tests, logs this on startup of a fresh cluster: LOG: win32 open error on 'global/pgstat.stat': 2 (very simple - it's a file-not-found, which is expected..) //Magnus allocatefile.patch Description: allocatefile.patch ---(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] Tricky bugs in concurrent index build
Ühel kenal päeval, K, 2006-08-23 kell 09:01, kirjutas Tom Lane: Greg Stark [EMAIL PROTECTED] writes: Hmmm. Or is that true. The problem may be somewhat easier since at least you can be sure every tuple in the heap is in the index. So if you see a DELETE_IN_PROGRESS either it *was* a constraint violation prior to the delete and failing is reasonable or it's an update in which case maybe it's possible to detect that they're part of the same chain? Unless we are willing to lock every single tuple while we insert it, this seems unfixable to me. Without a lock, the tuple could become DELETE_IN_PROGRESS immediately after we look at it. Actually it's worse than that. We could examine a tuple, see that it's good, include it in the uniqueness check. Then someone updates the tuple and puts the new version near the end of the table. By the time we reach that version, it could be committed good. Perhaps we should scan the index in index order, and set the unique flag per index page. That would a) help us avoid going to heap unless there are multiple entries for some value and b) enable us, once all index pages containing pointers to some possibly duplicate value are checked, to release locks on those tuples. -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_upgrade: What is changed?
Zdenek Kotala wrote: Martijn van Oosterhout wrote: On Wed, Aug 23, 2006 at 10:26:19AM +0200, Zdenek Kotala wrote: snip 1) BKI - catalog. c) Some records are changed action: ??? They just need to be changed. In principle the datalog needs to be updated so it looks like a database initdb'd with the new version. Yes I agree but The question is if some OID collision should appears for example collision between user function created in the 8.1 and build-in function in 8.2. ! insert OID = 1838 ( numeric_variance 11 10 12 f f t f i 1 1700 1231 _null_ _null_ _null_ numeric_variance - _null_ ) Those OIDs cannot be used for user types, because the OID counter skips then when wrapping around. See GetNewObjectId. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Question about (lazy) vacuum
Gregory Stark wrote: With all this code to handle ignoring vacuum transactions in calculating the global xmin it just occurred to me to wonder: Does lazy vacuum need a transaction at all? It doesn't do the tuple moving tricks with xvac that vacuum full does so does lazy vacuum's xid ever make it into tables? Couldn't it just use GetTopTransactionId instead of its own xid? Would this cause any problems? Two points: 1. it needs to get locks 2. GetTopTransactionId returns its own Xid anyway ... The point of getting locks is that it needs the lock manager to be able to release the lock if the transaction rolls back. (The lock object itself does not hold anything about the transaction itself AFAIR.) -- 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] Replication
On Wed, 23 Aug 2006 12:42:11 +0300 Hannu Krosing [EMAIL PROTECTED] wrote: OK, that solves your problem. How about my problem where replication has to happen on servers in three countries on two continents and thousands of updates a second have to happen in less that 10ms? For this scenario you are far better off with partitioning than replication. That is if your data is partitionable. But geographically distributed data often is. I agree but in this case it wasn't. -- D'Arcy J.M. Cain darcy@druid.net | 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Böszörményi Zoltán wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
B?sz?rm?nyi Zolt?n wrote: B?sz?rm?nyi Zolt?n wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. I think Alvaro is saying we need it in a few days, not longer. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Contrib module to examine client certificate
Am Dienstag, 22. August 2006 02:52 schrieb Bruce Momjian: This seems like a nice /contrib module. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. I have cleaned up this patch a little. I have changed all the function signatures from varchar to text, fixed up the formatting and packaging a little, and renamed it to just sslinfo. Note to the author: Whitespace is free. Use it. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ sslinfo.diff.bz2 Description: BZip2 compressed data ---(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] [PATCHES] COPY view
Böszörményi Zoltán wrote: B?sz?rm?nyi Zolt?n wrote: So when will you send in a revised patch? Soon. :-) No, don't send it soon. We're in feature freeze already (and have been for three weeks). You need to send it now. I have to test it some more but I will send it. I think Alvaro is saying we need it in a few days, not longer. Of course. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: * The schema now uses foreign keys to more accurately reflect a finacial DDL Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. * The history table now has a primary key that uses a serial Ditto. Again, part of the point :) * The respective balance columns have been increased to int8 to deal with larger values Ditto. This was done because you can easily break pgbench without the increase in data type. pgbench -c 850 -t 1000 pgbench gave a stream of errors like this before ending: Client 18 aborted in state 8: ERROR: integer out of range Client 429 aborted in state 8: ERROR: integer out of range Client 168 aborted in state 8: ERROR: integer out of range PG error log showed: 2006-08-22 15:45:19 PDT-[local]STATEMENT: UPDATE branches SET bbalance = bbalance + 4209228 WHERE bid = 679; 2006-08-22 15:45:19 PDT-[local]ERROR: integer out of range * Initalization will be done in a new schema/namespace, pgbench will exit if this schema/namespace exists OK, maybe that doesn't matter. Yeah I did it just so we wouldn't stomp on somebody on accident. * The new DDL should allow both Mammoth Replicator and Slony to be tested using pgbench (at least basic replication) Erm ... exactly why couldn't you do that before? history was missing a primary key. It could be done before. I just removed a step in getting it to work. pgbench doesn't have all that many things to recommend it, but what it does have is that it's been a stable testbed across quite a few PG releases. Arbitrarily whacking around the tested functionality will destroy that continuity. Well to be fair, I wasn't doing it arbitrarily. I had a specific purpose which was to have it use a schema that would be closer to a production schema, without breaking existing behavior. This patch does that :) I fell into this trap before myself ... I have a local copy of pgbench that produces TPS numbers quite a lot better than the standard pgbench, against exactly the same server. What's wrong with that picture? Well I think we all agree that some of the behavior of pgbench has been weird. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Tricky bugs in concurrent index build
stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: [ thinks for a bit... ] At least, it seems hopeless if we use SnapshotNow. Does it help if we use a real snapshot? I'm thinking pass 1 inserts exactly those tuples that are good according to a snap taken at its beginning, and then pass 2 considers only tuples that are good according to a snap taken at *its* beginning. But having consumed no caffeine yet this morning, I'm not sure I can spot any flaws that might exist in this idea. What about tuples that are inserted and committed in the window between the two phases. Ie, they're RECENTLY_DEAD but not in phase2's snapshot. We'd put them in the index but skip uniqueness check. Or do you mean we use SatisfiesVacuum to determine what to insert but SatisfiesSnapshot to determine whether to check uniqueness? Right. The problems seem to all stem from the risk of trying to unique-check more than one version of a tuple, and using a snap would stop that. We need to think through all the cases though and be sure they all work. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication
Ühel kenal päeval, K, 2006-08-23 kell 13:36, kirjutas Markus Schiltknecht: Hannu Krosing wrote: But if you have very few writes, then there seems no reason to do sync anyway. I think there is one: high-availability. A standby-server which can continue if your primary fails. Of course sync is only needed if you absolutely cannot effort loosing any committed transaction. Yes. I guess that for that an enchanced WAL-shipping would be ok. Probably one where you require both sync to local disk and copy to remote (without waiting for sync) to have happened in order to COMMIT/ROLLBACK successfully. Another important factor is the amount of conflicting transactions. That too, but just the need to do *any* locking on all nodes will significantly slow down sync replication If you implement sync replication with locking, yes. But there are better ways: the Postgres-R approach does not do network locking, but aborts conflicting transactions just before committing. That results in much less network traffic (one GCS-message per writing-transaction). but it still needs to do at least one network roundtrip + any needed testing on all nodes + WAL sync on all nodes before it can COMMIT, no? And I'm afraid that GCS serialisation will need more than one roundtrip or risk being out-of-date. I'm not saying that Postgres-R (or any other sync replication) is not doable or even useful. I just can't see right away, how it can scale very well for any significant write load. -- 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 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Some minor changes to pgbench
Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). 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] [PATCHES] Some minor changes to pgbench
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Tom Lane wrote: Addition of foreign key checking will certainly impact performance significantly. That is kind of the point. Without foreign keys it is a flawed test because you wouldn't be running in production without them and thus you can't bench without them. pgbench is not about reality, though. If we can't rely on it to give consistent results across versions then I don't think it's useful at all. There are many other benchmarks you can run that do speak to reality (eg OSDL's work). Would it be worthwhile to add a switch so that the foreign key test is only used if they use the switch in conjunction with a -i? Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Question about (lazy) vacuum
Hannu Krosing [EMAIL PROTECTED] writes: Ãhel kenal päeval, K, 2006-08-23 kell 05:23, kirjutas Gregory Stark: global xmin it just occurred to me to wonder: Does lazy vacuum need a transaction at all? When I asked the same question, I was told that a lot of core functionality vacuum uses needs to be in transaction. Locks for instance; you certainly need a lock on the table. In general a whole lot of the backend functionality is bound up in the transaction start/stop mechanisms, and quite a bit of rearchitecting would be needed to do very much outside a transaction. Doesn't really seem worth it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tricky bugs in concurrent index build
Tom Lane [EMAIL PROTECTED] writes: Or do you mean we use SatisfiesVacuum to determine what to insert but SatisfiesSnapshot to determine whether to check uniqueness? Right. The problems seem to all stem from the risk of trying to unique-check more than one version of a tuple, and using a snap would stop that. We need to think through all the cases though and be sure they all work. What happens if someone inserts a record that we miss, but it gets deleted by the same phase 2 starts. So it's not visible to phase 2 but conflicts with some other record we find. I suppose that's ok since the delete would have to have comitted for that to happen. It just means that having a unique constraint doesn't guarantee uniqueness if your transaction started before the index was finished being built. Or what if there's an insert that occurs before phase 2 starts and hasn't committed yet. There's a conflicting record in the heap that's missing in the index. I guess the build would have to block when it finds the missing record until the new insert either commits or aborts just like inserts do when a user inserts a potential conflict. Would I have to handle that myself or does index_insert handle that automatically? -- Gregory Stark 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] [PATCHES] COPY view
Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] An Idea for planner hints
Jim C. Nasby wrote: On Tue, Aug 22, 2006 at 11:56:17AM -0700, Mark Dilger wrote: I proposed something like this quite a bit up-thread. I was hoping we could have a mode in which the system would run the second, third, fourth, ... best plans rather than just the best looking one, and then determine from actual runtime statistics which was best. (The proposal also included the ability to output the best plan and read that in at a later time in lieu of a SQL query, but that part of it can be ignored if you like.) The posting didn't generate much response, so I'm not sure what people thought of it. The only major problem I see is getting the planner to keep track of alternate plans. I don't know the internals of it very well, but I think the genetic query optimizer doesn't have a concept of runner-up #1, runner-up #2, etc., which it would need to have. I think the biggest issue is that you'd have to account for varying load on the box. If we assume that the database is the only thing running on the box, we might be able to do that by looking at things like how much IO traffic we generated (though of course OS caching will screw with that). Actually, that's another issue... any plans run after the first one will show up as being artificially fast, since there will be a lot of extra cached data. Yes, caching issues prevent you from using wall-clock time. We could instrument the code to count the number of rows vs. the number predicted for each internal join, from which new cost estimates could be generated. Perhaps you can check my reasoning for me: I'm imagining a query which computes AxBxCxD, where A, B, C, and D are actual tables. I'm also imagining that the planner always chooses AxB first, then joins on C, then joins on D. (It does so because the single-table statistics suggest this as the best course of action.) It might be that AxD is a really small metatable, much smaller than would be estimated from the statistics for A independent of the statistics for D, but AxB is pretty much what you would expect given the independent statistics for A and B. So we need some way for the system to stumble upon that fact. If we only ever calculate cross-join statistics for plans that the system chooses, we will only discover that AxB is about the size we expected it to be. So, if the actual size of AxB is nearly equal to the estimated size of AxB, the system will continue to choose the same plan in future queries, totally ignorant of the advantages of doing AxD first. That last paragraph is my reasoning for suggesting that the system have a mode in which it runs the runner-up #1, runner-up #2, etc sorts of plans. Such a mode could force it down alternate paths where it might pick up interesting statistics that it wouldn't find otherwise. This idea could be changed somewhat. Rather than running the other plans, we could just extract from them which alternate joins they include, and consider also calculating those join statistics. mark ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Tricky bugs in concurrent index build
stark [EMAIL PROTECTED] writes: What happens if someone inserts a record that we miss, but it gets deleted by the same phase 2 starts. So it's not visible to phase 2 but conflicts with some other record we find. I suppose that's ok since the delete would have to have comitted for that to happen. I think that's OK, but the whole idea of using an MVCC snap in phase 2 doesn't work on closer inspection. The problem is still the same one that you need to take (at least) share lock on each tuple you insert into the index. Telling aminsert to check uniqueness implicitly assumes the new tuple is live, and without any lock on the tuple you can't promise that. So there's a significant risk of falsely declaring a uniqueness failure due to someone else's perfectly innocent UPDATE. Using an MVCC snap would actually make this worse, by widening the window for that UPDATE to happen. Even though we're hoping not to have to process too many tuples here, having to row-lock each one sounds pretty awful. Remember that that involves writing a WAL record these days. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Replication
Hi, Hannu Krosing wrote: but it still needs to do at least one network roundtrip + any needed testing on all nodes + WAL sync on all nodes before it can COMMIT, no? No. It only needs the 'roundtrip' in the sense that a transaction sends out its writeset and has to wait for the GCS to have it serialized (i.e. the GCS sends the message back to the sender node). Then all nodes do the testing and WAL sync independently. (As Neil recently pointed out in [1] this opens a small risk for data loss in the case all nodes crash.) And I'm afraid that GCS serialisation will need more than one roundtrip or risk being out-of-date. The spread people did some tests on 20 pentium machines connected via 100mbit ethernet. In [2] they state that it takes between 1.7 to 2.8 ms (depending on the message size) to 'serialize' a message within this group of 20 nodes. I'm not saying that Postgres-R (or any other sync replication) is not doable or even useful. I just can't see right away, how it can scale very well for any significant write load. Sure, sync replication won't solve everybody's problems. But out of all the sync replication algorithms, Postgres-R is my clear favorite. ;-) Regards Markus [1]: http://pgfoundry.org/pipermail/postgres-r-general/2006-August/01.html [2]: The Spread Toolkit: Architecture and Performance by Yair Amir, Claudiu Danilov, Michal Miskin-Amir, John Schultz, Jonathan Stanton http://www.cnds.jhu.edu/pub/papers/cnds-2004-1.pdf ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tricky bugs in concurrent index build
Tom Lane [EMAIL PROTECTED] writes: I think that's OK, but the whole idea of using an MVCC snap in phase 2 doesn't work on closer inspection. The problem is still the same one that you need to take (at least) share lock on each tuple you insert into the index. Telling aminsert to check uniqueness implicitly assumes the new tuple is live, and without any lock on the tuple you can't promise that. No wait. It's still live according to my snapshot. How could it be possible for a single snapshot to see two different versions of the same tuple as live? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Replication
On Wed, 2006-08-23 at 13:36 +0200, Markus Schiltknecht wrote: Hannu Krosing wrote: But if you have very few writes, then there seems no reason to do sync anyway. I think there is one: high-availability. A standby-server which can continue if your primary fails. Of course sync is only needed if you absolutely cannot effort loosing any committed transaction. I disagree about high-availability. In fact, I would say that sync replication is trading availability and performance for synchronization (which is a valid tradeoff, but costly). If you have an async system, all nodes must go down for the system to go down. If you have a sync system, if any node goes down the system goes down. If you plan on doing failover, consider this: what if it's not obvious which system is still up? What if the network route between the two systems goes down (or just becomes too slow to replicate over), but clients can still connect to both servers? Then you have two systems that both think that the other system went down, and both start accepting transactions. Now you no longer have replication at all. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
To cut the Gordon knot I'm going to suggest we use: \set CURSOR_FETCH fetch_count and \g and ; are modified such that when they see this variable set to fetch_count 0 and the buffer is a select they would use the modified fetch/output code. Does this sound reasonable to everyone? OK with me, but maybe call the variable FETCH_COUNT, to avoid the presupposition that the implementation uses a cursor. As I mentioned before, I expect we'll someday rework it to not use that. regards, tom lane Ok, sounds good. I'm travelling this week, but can send an updated patch during the weekend. Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Tricky bugs in concurrent index build
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: I think that's OK, but the whole idea of using an MVCC snap in phase 2 doesn't work on closer inspection. The problem is still the same one that you need to take (at least) share lock on each tuple you insert into the index. Telling aminsert to check uniqueness implicitly assumes the new tuple is live, and without any lock on the tuple you can't promise that. No wait. It's still live according to my snapshot. How could it be possible for a single snapshot to see two different versions of the same tuple as live? The problem case is that we take a tuple and try to insert it into the index. Meanwhile someone else updates the tuple, and they're faster than us so they get the new version into the index first. Now our aminsert sees a conflicting index entry, and as soon as it commits good aminsert will raise a uniqueness error. There's no backoff for oh, the tuple I'm inserting stopped being live while I was inserting it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
[ cc list trimmed to something reasonable ] Zoltan Boszormenyi [EMAIL PROTECTED] writes: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. This patch seems to be adding a tremendous amount of duplicated code in copy.c. Why? Also, moving checks for the relation case out of copy.c and into analyze.c is inappropriate. The checks you have moved there are actually wrong because you have no lock on the relation at the time you are checking. You could perhaps take a lock at analyze time, but frankly I see no reason for this patch to be messing with the relation case at all. As for the UNION problem, try passing the query to transformStmt rather than prejudging where transformStmt will send it. Compare for instance the analyze.c code for ExplainStmt. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi wrote: The exact same code was there, e.g. parse and rewrite SELECT * FROM view just not in analyze.c. I will try without it, though. And it was wrong as well. (The code was there on the COPY-view patch, not on the official code). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] [PATCHES] COPY view
Tom Lane wrote: frankly I see no reason for this patch to be messing with the relation case at all. Quite apart from anything else, if it's done that way nothing that currently works gets broken. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] COPY view
Tom Lane írta: [ cc list trimmed to something reasonable ] Zoltan Boszormenyi [EMAIL PROTECTED] writes: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. This patch seems to be adding a tremendous amount of duplicated code in copy.c. Why? 1. to minimize the already huge code impact on the relation case. 2. the checks done for the SELECT case is not exactly the same as for the realation case. 3. the relation case is managed by passing around a Relation pointer, e.g. CopyGetAttnums. This simply not appropriate for the SELECT case. I will try to clean it up as much as I can, though. How about the callback solution for the SELECT case that was copied from the original? Should I consider open-coding in copy.c what ExecutorRun() does to avoid the callback? Also, moving checks for the relation case out of copy.c and into analyze.c is inappropriate. The checks you have moved there are actually wrong because you have no lock on the relation at the time you are checking. You could perhaps take a lock at analyze time, but frankly I see no reason for this patch to be messing with the relation case at all. OK, I will put the checks back where they were. As for the UNION problem, try passing the query to transformStmt rather than prejudging where transformStmt will send it. Compare for instance the analyze.c code for ExplainStmt. Thanks. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Win32 max connections bug (causing crashes)
On 8/18/06, Magnus Hagander [EMAIL PROTECTED] wrote: i doubled all my heap settings and was able to roughly double the - c on pgbench from ~158 (stock) to ~330 (modified). so this is definately the problem. If you try decreasing max_files_per_process to a significantly lower value (say, try 100 instead of 1000), does the number of processes you can run change noticeably? (I don't have a box around ATM that I can try to reproduce on. Will try to set up a VM for it soon.) per Magnus's request, I set my machine to 25 max_files (the minimum) and saw no appreciable gain in the number of connections requred to make it crash (I tested at 400). The first time I ran it I almost hosed my machine...it was doing all kinds of irrational beeping and all the windows were flickering and blinking. It did not do this following the max_file reduction, although I have no desire to run this test again on my development box ;) merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi wrote: 1. to minimize the already huge code impact on the relation case. 2. the checks done for the SELECT case is not exactly the same as for the realation case. So put them in CopyToRelation. But the ones that apply to both, leave in CopyTo. 3. the relation case is managed by passing around a Relation pointer, e.g. CopyGetAttnums. This simply not appropriate for the SELECT case. Which is why you should leave the relation case alone and only add the different case. The relation case is already known to be good. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] COPY view
Alvaro Herrera [EMAIL PROTECTED] writes: Which is why you should leave the relation case alone and only add the different case. The relation case is already known to be good. Well, a certain amount of refactoring of the code is inevitable unless we want a lot of code duplication. But I don't see a reason to do anything more to the relation-case code path than push some chunks of it into subroutines. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi [EMAIL PROTECTED] writes: How about the callback solution for the SELECT case that was copied from the original? Should I consider open-coding in copy.c what ExecutorRun() does to avoid the callback? Adding a DestReceiver type is a good solution ... although that static variable is not. Instead define a DestReceiver extension struct that can carry the CopyState pointer for you. You could also consider putting the copy-from-view-specific state fields into DestReceiver instead of CopyState, though this is a bit asymmetric with the relation case so maybe it's not really cleaner. BTW, lose the tuple_to_values function --- it's an extremely bad reimplementation of heap_deform_tuple. copy_dest_printtup also seems coded without regard for the TupleTableSlot access API (read printtup() to see what to do instead). And what's the point of factoring out the heap_getnext loop as CopyRelationTo? It's not like that lets you share any more code. The inside of the loop, ie what you've called CopyValuesTo, is the sharable part. 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] pgsql-patches reply-to (was Re: [PATCHES] selecting large result sets in psql using cursors)
On Tue, Aug 22, 2006 at 23:15:59 -0400, Robert Treat [EMAIL PROTECTED] wrote: On Thursday 17 August 2006 11:55, Peter Eisentraut wrote: I'm curious, do you combine any other lists like that? I've played around with that idea (for example, I used to combine webmaster emails, pgsql-www, and -slaves emails but the slaves traffic was too high so I had to split it back out). As someone subscribed to a good dozen pg lists, I've always been quite amazed how much email some of the folks here manage to process... I suppose I could just chalk it up to a pine vs. gui thing, but I suspect there are some other tricks people have to make emails more manageable (anyone combine all pg mail to one folder?) I do, but it is a lot of email and if I miss a few days it takes a while to catch up again. At some point I will probably do some smarter filtering, but I don't want to spend the effort to figure that out right now. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] COPY view
Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] [PATCHES] COPY view
Hi, Bruce Momjian írta: Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. BTW, my first name is Zoltán. Best regards, Zoltán Böszörményi pgsql-copyselect.patch.gz Description: Unix tar archive ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY view
Zoltan Boszormenyi írta: Hi, Bruce Momjian írta: Alvaro Herrera wrote: Bruce Momjian wrote: Andrew Dunstan wrote: Bruce Momjian wrote: I think Alvaro is saying we need it in a few days, not longer. I thought he was saying today ;-) He actually said now, but I don't think we need it immediately, especially if he is still working on it. We are at least 1-2 weeks away from having all open patches applied. Yes, I'm saying today so that we can all look at it and point obvious mistakes now, not in 2 weeks from now. Release early, release often. If the patch contains a mistake and we find out in 2 weeks, are we going to fix it? No, we are going to reject it. OK, I understand. B?sz?rm?nyi, post now so we can see where you are, but keep working and send it to us again when you are done. No sense in not posting your working version. OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I meant that UNION selects, subselects don't work yet. BTW, my first name is Zoltán. Best regards, Zoltán Böszörményi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] [PATCHES] COPY view
Zoltan Boszormenyi wrote: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I'm not sure I agree with the approach of creating a fake SELECT * FROM foo in analyze.c in the relation case and passing it back to the parser to create a Query node. That's not there in the original code and you shouldn't need it. Just let the case where COPY gets a relation continue to handle it as it does today, and add a separate case for the SELECT. That doesn't help you with the UNION stuff though. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] [PATCHES] COPY view
Alvaro Herrera írta: Zoltan Boszormenyi wrote: OK, here's my current version. The reference leak is fixed. But as my testcase shows, it only works for single selects currently. The parser accepts it but COPY doesn't produce the expected output. Please, suggest a solution. I'm not sure I agree with the approach of creating a fake SELECT * FROM foo in analyze.c in the relation case and passing it back to the parser to create a Query node. That's not there in the original code and you shouldn't need it. Just let the case where COPY gets a relation continue to handle it as it does today, and add a separate case for the SELECT. The exact same code was there, e.g. parse and rewrite SELECT * FROM view just not in analyze.c. I will try without it, though. That doesn't help you with the UNION stuff though. :-( ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgsql-patches reply-to (was Re: [PATCHES] selecting large result sets in psql using cursors)
Bruno Wolff III wrote: On Tue, Aug 22, 2006 at 23:15:59 -0400, Robert Treat [EMAIL PROTECTED] wrote: On Thursday 17 August 2006 11:55, Peter Eisentraut wrote: I'm curious, do you combine any other lists like that? I've played around with that idea (for example, I used to combine webmaster emails, pgsql-www, and -slaves emails but the slaves traffic was too high so I had to split it back out). As someone subscribed to a good dozen pg lists, I've always been quite amazed how much email some of the folks here manage to process... I suppose I could just chalk it up to a pine vs. gui thing, but I suspect there are some other tricks people have to make emails more manageable (anyone combine all pg mail to one folder?) I do, but it is a lot of email and if I miss a few days it takes a while to catch up again. At some point I will probably do some smarter filtering, but I don't want to spend the effort to figure that out right now. I was at some point doing the smarter filtering, i.e. each list to its own folder, but eventually found out that it's better to combine the whole thing, which is what I do now. I also managed to figure out that it's better to put stuff that doesn't pass through the list, but has a Cc: some-list header, in the same folder; that way, duplicates (of which I do get a few) are easier to handle. (You can choose to remove dupes by telling Majordomo not to send you mails that have you on Cc:, but I've found that I lose some people's emails due to my own spam filtering.) I have on my TODO to have procmail throw away an email that it already delivered (e.g. by comparing Message-Id's), so if someone has a solution to that I'd like to know. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql-patches reply-to (was Re: [PATCHES] selecting large result sets in psql using cursors)
On Wed, Aug 23, 2006 at 15:03:24 -0400, Alvaro Herrera [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: I do, but it is a lot of email and if I miss a few days it takes a while to catch up again. At some point I will probably do some smarter filtering, but I don't want to spend the effort to figure that out right now. I was at some point doing the smarter filtering, i.e. each list to its own folder, but eventually found out that it's better to combine the whole thing, which is what I do now. I also managed to figure out that it's better to put stuff that doesn't pass through the list, but has a Cc: some-list header, in the same folder; that way, duplicates (of which I do get a few) are easier to handle. (You can choose to remove dupes by telling Majordomo not to send you mails that have you on Cc:, but I've found that I lose some people's emails due to my own spam filtering.) I have on my TODO to have procmail throw away an email that it already delivered (e.g. by comparing Message-Id's), so if someone has a solution to that I'd like to know. I don't have cc's removed because that still sometimes gets me faster replies, but I do have get only one message when a message is posted to several lists set. I use mutt to read mail and maildrop to do filtering. I think for me smarter filtering would be to split the lists into to or three groups. There are lists I see a fair number of interesting messages on, lists I can often answer questions on, and other postgres lists. When I fall behind, doing a D.* on the other postgres lists is something I should do more than I currently am. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
[EMAIL PROTECTED] wrote: To cut the Gordon knot I'm going to suggest we use: ITYM Gordian - see http://en.wikipedia.org/wiki/Gordian_Knot cheers andrew ;-) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pgsql-patches reply-to (was Re: [PATCHES] selecting large result sets in psql using cursors)
Alvaro Herrera wrote: I have on my TODO to have procmail throw away an email that it already delivered (e.g. by comparing Message-Id's), so if someone has a solution to that I'd like to know. :0 Wh: msgid.lock | formail -D 65536 $HOME/.msgid.cache I don't use the eliminatecc feature either, for the known reasons, but the above works without fail. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ISBN/ISSN/ISMN/EAN13 module
Michael wrote: Idohopethatyouralgorithmforgenerating13digitsfrom10hasbeen validatedwithisbn.org,sinceallthecheckdigitswillchange.I believeitiscrucialforpostgresqltogenerateisbncodesinboth10 and13digits Indeed now that see the module it's finally close to be accepted as an official module, I'm verifying my algorithm against the one in the ISBN User Manual and all the current information I can get my hands into (I first created the module a couple years ago,and there wasn't as much information back then.) The module internally keeps all the numbers in EAN13 mode, and it only converts the numbers to ISBN when required or needed by the selected data type. andinthetwoseparatewaysrequiredofpublisherson theirbarcodes,withjustthe10digitinput.Thisisjustaspecial casefortheoverallclassificationsystem,sotheinitial3digitcode forbookpublishersshouldnotbehardwired. I got lost in what you said here, could you please be more specific? what is that about the initial 3 digit code you're mentioning? do you mean "the two separate ways", you meanboth ISBN and ISBN13as in: ISBN-13: 978-1-873671-00-9 ISBN-10: 1-873671-00-8 ? Shortly, I'll be further improving the performance and doing some tweaking in other areas, as well as updating the ISBN hyphenation ranges and probably adding UPC support.I'm still not sure if to include hyphenations in the EAN13 codes as it seems it's not part of the standard, thus 0036000291452 and 9781873671009 would be to examples of how the EAN13 numbers would show without hyphenation Thanks for your concerns, Kronuz. "Foolsrushinwherefoolshavebeenbefore"-UnknownGet the new Windows Live Messenger! Try it!
Re: [HACKERS] Problem with mailing list?
On Wed, Aug 23, 2006 at 08:47:03 +0200, Zdenek Kotala [EMAIL PROTECTED] wrote: Is everything ok with postgres mail server? I have problem to send mail to hackers list and pgadmin-hacker as well. If somebody is on cc, he receives mail correctly, but it does not appear in the list. Any suggestion? Well this message went through. I think there is an option to not see your own posts. It also could just be that mail is slow getting through to you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] ISBN/ISSN/ISMN/EAN13 module
Jeremy Kronuz wrote: Michael wrote: I do hope that your algorithm for generating 13 digits from 10 has been validated with isbn.org, since all the check digits will change. I believe it is crucial for postgresql to generate isbn codes in both 10 and 13 digits Indeed now that see the module it's finally close to be accepted as an official module, I'm verifying my algorithm against the one in the ISBN User Manual and all the current information I can get my hands into (I first created the module a couple years ago, and there wasn't as much information back then.) The module internally keeps all the numbers in EAN13 mode, and it only converts the numbers to ISBN when required or needed by the selected data type. and in the two separate ways required of publishers on their bar codes, with just the 10 digit input. This is just a special case for the overall classification system, so the initial 3 digit code for book publishers should not be hard wired. I got lost in what you said here, could you please be more specific? what is that about the initial 3 digit code you're mentioning? do you mean the two separate ways, you mean both ISBN and ISBN13 as in: ISBN-13: 978-1-873671-00-9 ISBN-10: 1-873671-00-8 ? Shortly, I'll be further improving the performance and doing some tweaking in other areas, as well as updating the ISBN hyphenation ranges and probably adding UPC support. I'm still not sure if to include hyphenations in the EAN13 codes as it seems it's not part of the standard, thus 0036000291452 and 9781873671009 would be to examples of how the EAN13 numbers would show without hyphenation Thanks for your concerns, Kronuz. Fools rush in where fools have been before - Unknown Get the new Windows Live Messenger! Try it! http://get.live.com/messenger/overview the 978 is the worldwide classification for book publishers. There is a complete schedule of 3 digit prefixes for all other types of products in the world, and it syncs withbthe NAIAC -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.5/425 - Release Date: 8/22/2006 ---(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