Re: [HACKERS] PostgreSQL Anniversary Summit, Call for Contributions
On Sat, 11 Mar 2006, Oleg Bartunov wrote: It's still not easy to come from Russia to Canada. I have to convince officer in canadian embassy that 1) I have enough money for living in Canada 2) I don't want to immigrate 3) I'm a loyal citizen Invitation from conference commitee could help me to get an official letter from my institute to embassy (1,2). But we still have 3) I should get references for all members of my family from our police department that we're not criminals :) There is no united database, so I should get references from all places I live ! This is awful and I'm about to give up, even if I'd be able to afford tickets. Yowch! I know it must have improved somewhat since (doesn't it?), post-Cold War Russia was well painted in the media as 'corrupt and heavy crime rate' side of things (news rarely reports the good, since the bad is what sells) ... and none of *that* helps make, *at least* 2 in the above any easier ;( Oleg On Sat, 11 Mar 2006, Christopher Browne wrote: Quoth [EMAIL PROTECTED] (Marc G. Fournier): Just curious, but how do ppl come to Canada as tourists from other countries? I don't imagine they need to be invited by a Canadian, do they? Well, the invitation thing doesn't apply at all to people from North America or Western Europe; it's generally just applicable to those coming from Africa, Asia, Eastern Europe, and South America. Much of those regions represent developing countries, where the notion of middle class is emerging as opposed to being a mature thing. Absent of middle class, you generally have the other two ends, namely lower-lower class, who can't conceivably afford to be tourists, and upper class, who can certainly arrange invitations (if not diplomatic status :-) !). In my lifetime, the world has quite changed. Thirty years ago, the only way Russians would be coming to Canada would be under pretty strict scrutiny of the apparatus of the former Soviet Union, which would definitely elicit suspicion. Either you'd be of governmental/diplomatic status, an athlete/performer, or, well, quite likely you're an undeclared spy... It's quite an enormous change for relatively ordinary people (well, if they're working on PostgreSQL, they've got to be at least a little extraordinary! ;-)) to be just visiting from such places. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] Hanging queries on dual CPU windows
If so, we could perhaps recode that part using a Mutex instead of a critical section - since it's not a performance critical path, the difference shouldn't be large. If I code up a patch for that, can you re-apply SP1 and test it? Or is this a production system you can't really touch? I can do whatever the hell I want with it, so if you could cook up a patch that would be great. As a BTW: I reinstalled SP1 and turned stats collection off. That also seems to work, but is not really a solution since we want to use autovacuuming. Ok, I've coded up a patch that changes the code to use a mutex instead. Patch attached. You can get a precompiled postgres.exe at http://www.hagander.net/download/postgres.exe_mutex.zip. You need to copy this file to postmaster.exe as well - they are supposed to be identical. It's based off a snapshot of 8.1-stable. Looking a my system while testing this it still loooked like it was hanging on that plac ein the code, even though I saw no problems. So I'm not convinced we can actually trust the stacktrace from the non-default threads. So I don't think this patch will actually work :-( But it's worth a try. (Oh, and I moved the thread over to -hackers, seems more correct at this time) //Magnus mutex.patch Description: mutex.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
[HACKERS] ERROR: record type has not been registered on CVS head
While trying to help somebody on IRC with slow queries against information_schema i stumbled across the following EXPLAIN buglet (much reduced from the original one and does not make a lot of sense therefore): foo=# explain SELECT * FROM information_schema.constraint_column_usage JOIN information_schema.key_column_usage ON key_column_usage.constraint_name = constraint_column_usage.constraint_name; ERROR: record type has not been registered Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for updatable views
Bernd Helmle [EMAIL PROTECTED] Hi folks, The supported syntax is CREATE VIEW foo AS SELECT ... [WITH [LOCAL | CASCADED] CHECK OPTION]; The LOCAL and CASCADED keywords are optional when a CHECK OPTION is specified, the default is CASCADED (this syntax creates a shift/reduce conflict in the grammar file i don't know how to fix). Maybe you can fix it like UNIONJOIN. See parser.c. But Tom said he want to remove the support for UNION JOIN and save the overhead: http://archives.postgresql.org/pgsql-hackers/2006-03/msg00344.php Regards, William ZHANG ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] DB2-style INS/UPD/DEL RETURNING
I was talking with Jonathan Gennick about the INS/UPD/DEL RETURNING stuff, and he recommended looking into the way DB2 handles similar functionality. After looking into it a bit, it's more inline with what Tom's suggestion was regarding a query from the operation rather than returning the values in the manner currently required. Here's DB2's syntax... does anyone have any familiarity with it?Simply put, it's sort-of like:SELECT * FROM (FINAL | NEW | OLD) TABLE (INSERT | UPDATE | DELETE)I'd like to hear from anyone that's used it to see if it really is better... logically it seems nicer, but I've never used it. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [HACKERS] [PERFORM] Hanging queries on dual CPU windows
On Sunday 12 March 2006 09:40, Magnus Hagander wrote: If so, we could perhaps recode that part using a Mutex instead of a critical section - since it's not a performance critical path, the difference shouldn't be large. If I code up a patch for that, can you re-apply SP1 and test it? Or is this a production system you can't really touch? I can do whatever the hell I want with it, so if you could cook up a patch that would be great. As a BTW: I reinstalled SP1 and turned stats collection off. That also seems to work, but is not really a solution since we want to use autovacuuming. Ok, I've coded up a patch that changes the code to use a mutex instead. Patch attached. You can get a precompiled postgres.exe at http://www.hagander.net/download/postgres.exe_mutex.zip. You need to copy this file to postmaster.exe as well - they are supposed to be identical. It's based off a snapshot of 8.1-stable. Looking a my system while testing this it still loooked like it was hanging on that plac ein the code, even though I saw no problems. So I'm not convinced we can actually trust the stacktrace from the non-default threads. So I don't think this patch will actually work :-( But it's worth a try. (Oh, and I moved the thread over to -hackers, seems more correct at this time) Thanks Magnus, I'll try tomorrow. Will let you know ASAP (8:30 EST I guess :). If this doesn't work, how do we progress? //Magnus jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Anniversary Summit, Call for
Oleg, Invitation from conference commitee could help me to get an official letter from my institute to embassy (1,2). But we still have 3) I should get references for all members of my family from our police department that we're not criminals :) There is no united database, so I should get references from all places I live ! This is awful and I'm about to give up, even if I'd be able to afford tickets. Can we take this to an appropriate place to deal with it, rather than the -Hackers list? Like -advocacy? Of course we, the conference organizers will do what we can to help you come over to the conference. I take it that you need that invitation letter sooner rather than later? --Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 752-2500 for law firms, small businessesfax 752-2387 and non-profit organizations. San Francisco ---(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: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? I would have thought so - unless there are not enough pages left in the FSM... pg_freespacemap is reporting on what gets into the FSM - so provided I haven't put a bug in there somewhere (!) - we need to look at how VACUUM reports free space to the FSM cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? No, because (a) pgbench vacuums at the start of the run not the end, and (b) vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tom Lane wrote: Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). Ah - that what I was seeing! Thanks. I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. Ok - I did wonder about 2 views, but was unsure if the per-relation stuff was interesting. Given that it looks like it is interesting, I'll see about getting a second view going. Cheers Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii wrote: BTW, I noticed difference of outputs from pg_freespacemap and pgstattuple. I ran pgbench and inspected accounts table by using these tools. pg_freespacemap: sum of bytes: 250712 pgstattuple: free_space: 354880 Shouldn't they be identical? No, because (a) pgbench vacuums at the start of the run not the end, I ran VACUUM after pbench run and still got the differece. and (b) vacuum/fsm disregard pages with uselessly small amounts of free space (less than the average tuple size, IIRC). That sounds strange to me. Each record of accounts tables is actually exactly same, i.e fixed size. So it should be possible that UPDATE reuses any free spaces made by previous UPDATE. If FSM neglects those free spaces because they are uselessly small, then the unrecycled pages are getting grow even if they are regulary VACUUMed, no? I do notice a rather serious shortcoming of pg_freespacemap in its current incarnation, which is that it *only* shows you the per-page free space data, and not any of the information that would let you determine what the FSM is doing to filter the raw data. The per-relation avgRequest and lastPageCount fields would be interesting for instance. Perhaps there should be a second view with one row per relation to carry the appropriate data. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] About Buffer Flushing Function
Íõ±¦±ø [EMAIL PROTECTED] wrote --Data Buffer Flush:Only flush the dirty database data(items) into the disk not including the log buffer. In most cases, if you just flush the dirty database pages into disk without related xlog records, you are violating WAL. A possible consequence is data corruption if any system error happens. Regards, Qingqing ---(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: [PATCHES] [HACKERS] pg_freespacemap question
Tatsuo Ishii [EMAIL PROTECTED] writes: That sounds strange to me. Each record of accounts tables is actually exactly same, i.e fixed size. So it should be possible that UPDATE reuses any free spaces made by previous UPDATE. If FSM neglects those free spaces because they are uselessly small, then the unrecycled pages are getting grow even if they are regulary VACUUMed, no? The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] pg_freespacemap question
The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] pg_freespacemap question
Christopher Kings-Lynne wrote: The point here is that if tuples require 50 bytes, and there are 20 bytes free on a page, pgstattuple counts 20 free bytes while FSM ignores the page. Recording that space in the FSM will not improve matters, it'll just risk pushing out FSM records for pages that do have useful amounts of free space. Maybe an overloaded pgstattuple function that allows you to request FSM behavior? That's a nice idea - could also do equivalently by adding an extra column usable_free_space or some such, and calculating this using FSM logic. Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Proposal for updatable views
On Fri, 2006-03-10 at 11:21 +0100, Bernd Helmle wrote: Please find attached a patch that implements SQL92-compatible updatable views. I'm currently reviewing this. Comments later... Please note that the patch isn't complete yet Do you have a list of known TODO items? -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PERFORM] Hanging queries on dual CPU windows
Magnus Hagander [EMAIL PROTECTED] wrote Ok, I've coded up a patch that changes the code to use a mutex instead. Are we asserting the problem is caused by the spinlock random wake-up order? I am not sure why this would fix the problem. If my memory serves, a critical section might be a problem if one process aborts unexpected while it is inside. Other waiting processes can never have a chance to enter it (also have no chance to handle SIGQUIT) -- so this patch may solve this. There is another suspect in http://www.devisser-siderius.com/stack1.jpg, i.e., process 3 does shmctl. I once filed a server core dump bug in win32 of reporting WSAEWOULDBLOCK. (http://archives.postgresql.org/pgsql-bugs/2006-02/msg00185.php). AFAICS, it is actually an mistranslated EINTR. There seems some relation between these issues, but I didn't come up with a complete theory of it. Regards, Qingqing ---(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
