Re: [HACKERS] Spoofing as the postmaster
Greg Smith wrote: On Sat, 29 Dec 2007, Joshua D. Drake wrote: http://code.google.com/p/sepgsql/ ??? Getting that to work required some obtrusive changes to the source code, which they've only done to 8.2.4. Even that doesn't seem to be production-quality and it's not clear how that will make its way into newer versions yet. Sorry for my late responding. I don't argue your opinion about its quality issue. We indeed need more feedbacks and improvements from widespread viewpoints. The current status of SE-PostgreSQL is a bit incorrect. The latest one is sepostgresql-8.2.5-1.66.fc9, based on 8.2.5. See, http://download.fedora.redhat.com/pub/fedora/linux/development/ Currently, we are paying efforts to port SE-PostgreSQL features into 8.3.x based PostgreSQL. (It is based on 8.3beta based PostgreSQL in correct.) The job here is to work on the SELinux policies for PostgreSQL. You can't just re-use whatever work has gone into the SE-PostgreSQL ones, because those presume you're using their modified server instead of the regular one. Yes, SE-PostgreSQL requires to stop the regular one when it works. We cannot use both of them at the same time. However, the default security policy is designed as if it works like regular one without any special SELinux configuration. If you can find out any bug or unclear behavior, I want you to report it. I started collecting notes and writing a PostgreSQL/SELinux how-to aimed at RHEL 5.0+ but I'm not doing work in that area anymore. I'm interested in this effort. Could you tell me the URL? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spoofing as the postmaster
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, 29 Dec 2007 14:40:29 -0500 (EST) Greg Smith [EMAIL PROTECTED] wrote: On Sat, 29 Dec 2007, Joshua D. Drake wrote: http://code.google.com/p/sepgsql/ ??? Getting that to work required some obtrusive changes to the source code, which they've only done to 8.2.4. Even that doesn't seem to be production-quality and it's not clear how that will make its way into newer versions yet. they've has the potential to be we... As I recall the individual made a reasonable effort to introduce the work that he was doing to the community. http://archives.postgresql.org/pgsql-hackers/2007-03/msg00271.php http://archives.postgresql.org/pgsql-hackers/2007-04/msg00664.php If my memory is correct, the alpha implementation was announced after the feature freeze date of 8.3. # Sorry for my lacking of understanding for PostgreSQL development processes. Therefore, Tom suggested this kind of discussion should be restarted after the release of 8.3. I also agreed it. But unless there's somebody else with a burning need to work on this area I doubt that will happen--there's nothing about SELinux that anybody does just for fun. Ya think? :P I recognize that this SE PGSQL has the potential to be a portability nightmare (as it only works on linux) but it certainly has potential to give us a leg up on a lot of work. Yes, it works only on Linux. I added --enable-selinux build option into the configure script. It prevent to enable SE-PostgreSQL feature on any other plathomes. Anyway, not saying its good code but I did read the docs and it sure looks cool. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Spoofing as the postmaster
Greg Smith wrote: On Sat, 29 Dec 2007, Joshua D. Drake wrote: they've has the potential to be we... As I recall the individual made a reasonable effort to introduce the work that he was doing to the community. After a bit of hindsight research, I think SE-PostgreSQL suffered from two timing problems combined with a cultural misperception. The first timing issue was that those messages went out just as the 8.3 feature freeze was going on. I know I looked at their stuff for a bit at that point, remembered I had patches to work on, and that was it at that point. Yes, it was lack of my understanding of PostgreSQL development process. The second problem is that just after the first message to the list came out, RedHat released RHEL 5.0, which did a major reworking of SELinux that everyone could for production systems immediately. I know all my SELinux time at that point immediately switched to working through the major improvements RHEL5 made rather than thinking about their project. The most of SELinux features on RHEL5.0 are based on Fedora core 6. It does not contain any SE-PostgreSQL support. We have to wait for next major release of RHEL to apply SE-PostgreSQL features on production system. If you can try out it on non-production system, Fedora 8 is the most recommendable environment. The cultural problem is that their deliverable was a series of RPM packages (for Fedora 7, ack). They also have a nice set of user documentation. But you can't send a message to this hackers list asking for feedback and hand that over as your reference. People here want code. When I wander through the threads that died, I think this message shows the mismatch best: http://archives.postgresql.org/pgsql-hackers/2007-04/msg00722.php Hmm... I'll send it as a patch to discuss this feature. Please wait for we can port it into the latest postgresql tree. (Maybe, it is nonsense to discuss 8.2.x based patches.) When Tom throws out an objection that a part of the design looks sketchy, the only good way to respond is to throw the code out and let him take a look. I never saw the SE-PostgreSQL group even showing diffs of what they did; making it easy to get a fat context diff (with a bit more context than usual) would have done wonders for their project. You're not going to get help from this community if people have to install a source RPM and do their own diff just to figure out what was changed from the base. Thanks for your indications. -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM FULL out of memory
As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). That ought to leave more than enough space for other processes in the system. Again, the system fails on the VACUUM with the following error (identical to the error we had when maintenance_work_mem was very low. INFO: vacuuming pg_catalog.pg_largeobject ERROR: out of memory DETAIL: Failed on request of size 536870912 I've now also tested a 64-bit setup with 16 GB RAM, with 2 GB maintenance_work_mem; this time on PostgreSQL 8.2.5. INFO: vacuuming pg_catalog.pg_largeobject ERROR: invalid memory alloc request size 1073741824 It strikes me as somewhat worrying that VACUUM FULL ANALYZE has so much trouble with a large table. Granted - 730 million rows is a good deal - but it's really not that much for a large database. I'd expect an operation on such a table to take time, of course, but not to consistently crash out of memory. Any suggestions as to what we can otherwise try to isolate the problem? Regards, Michael Akinde Database Architect, met.no Michael Akinde wrote: [Synopsis: VACUUM FULL ANALYZE goes out of memory on a very large pg_catalog.pg_largeobject table.] Simon Riggs wrote: Can you run ANALYZE and then VACUUM VERBOSE, both on just pg_largeobject, please? It will be useful to know whether they succeed ANALYZE: INFO: analyzing pg_catalog.pg_largeobject INFO: pg_largeobject: scanned 3000 of 116049431 pages, containing 18883 live rows and 409 dead rows; 3000 rows in sample, 730453802 estimated total rows VACUUM VERBOSE: INFO: vacuuming pg_catalog.pg_largeobject INFO: scanned index pg_largeobject_loid_pn_index to remove 106756133 row versions DETAIL: CPU 38.88s/303.43u sec elapsed 2574.24 sec. INFO: pg_largeobject: removed 106756133 row versions in 13190323 pages DETAIL: CPU 259.42s/113.20u sec elapsed 14017.17 sec. INFO: index pg_largeobject_loid_pn_index now contains 706303560 row versions in 2674471 pages DETAIL: 103960219 index row versions were removed. 356977 index pages have been deleted, 77870 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.02 sec. INFO: pg_largeobject: found 17489832 removable, 706303560 nonremovable row versions in 116049431 pages DETAIL: 0 dead row versions cannot be removed yet. There were 36000670 unused item pointers. 64493445 pages contain useful free space. 0 pages are entirely empty. CPU 1605.42s/1107.48u sec elapsed 133032.02 sec. WARNING: relation pg_catalog.pg_largeobject contains more than max_fsm_pages pages with useful free space HINT: Consider using VACUUM FULL on this relation or increasing the configuration parameter max_fsm_pages. VACUUM (This took some 36+ Hours. It will be interesting to see what happens when we add another 20 years worth of data to the 13 years already in the DB). ANALYZE: INFO: analyzing pg_catalog.pg_largeobject INFO: pg_largeobject: scanned 3000 of 116049431 pages, containing 17830 live rows and 0 dead rows; 3000 rows in sample, 689720452 estimated total rows I will lower the SharedMem and MaintenanceWorkMem settings as suggested in earlier posts before leaving for home this evening, and then let it run a VACUUM FULL ANALYZE. I remain dubious though - as mentioned, the first test I did had quite low settings for this, and we still had the memory crash. No reason not to try it though. Over Christmas, we will be moving this over on a 64-bit kernel and 16 GB, so after that we'll be able to test on the database with 1GB maintenance memory as well. Regards, Michael A. Database Architect, met.no ---(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 begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:[EMAIL PROTECTED] tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard ---(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] Dynamic Partitioning using Segment Visibility Maps
On Wed, 2008-01-02 at 17:56 +, Simon Riggs wrote: Like it? Very cool :-) One additional thought: what about a kind of segment fill factor ? Meaning: each segment has some free space reserved for future updates/inserts of records in the same range of it's partitioning constraint. And when inserting/updating you put the new record into the corresponding segment... just like a very coarse clustering. Then you could vacuum the segments separately to keep the free space not running out. For active segments you would then fix the partitioning constraint range once the fill factor is reached, to allow for keeping it's constraint even when heavily updating (heavily vacuuming it too as response to that), and create a new segment for the unbounded range for new inserts... this would work fine for tables where the constraint is based on ever increasing keys and accidental inserts in old ranges (which do happen occasionally in real life). When the change rate of old segments get down, the segments could be reorganized to have a smaller fill factor, so that you still allow for accidental updates but keep space usage efficient. This would be some similar action as a clustering, but hopefully not blocking (which might be a hard thing to do)... and later again you could mark some of the really old things as read only and put them in special segments with no wasted space. One problem would be when the segment's free space runs out, so you must put records from the same constraint range in multiple segments - but that could still work, you just would have multiple segments covering the same range, but if the segment fill factor is chosen properly it should not be the case... you could normally maintain a set of non-overlapping segments in terms of the partitioning constraint. Cheers, Csaba. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Joshua D. Drake wrote: SELECT oid::regclass FROM pg_class WHERE reltoastrelid='pg_toast.pg_toast_49013869'::regclass; oid | pg_temp_24.tmp_isp_blk_chk The hack to get this cleaned up was to connect about 2 dozen times (to get to slot 24) with psql via different sessions and create temp tables. Once we hit slot 24, the probably instantly went away and the database returned to normal state. Ah -- interesting. This is a known issue, but we haven't found a solution yet. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Hi Csaba, Csaba Nagy wrote: One additional thought: what about a kind of segment fill factor ? Meaning: each segment has some free space reserved for future updates/inserts of records in the same range of it's partitioning constraint. And when inserting/updating you put the new record into the corresponding segment... just like a very coarse clustering. Hm.. yeah. That way, a few writes to a read optimized segment could be accepted, without having to drop the optimization immediately. And the other way around: generally prevent having to drop the optimization by forcing tuples to be written to a segment with matching min/max tuples. Although, that's not exactly trivial, I think. However, for tables which don't fit the use case of SE, people certainly don't want such a fill factor to bloat their tables. Regards Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Tuning Postgresql on Windows XP Pro 32 bit
We are running the binary distribution, version 8.2.5-1, installed on Windows XP Pro 32 bit with SP2. We typically run postgres on linux, but have a need to run it under windows as well. Our typical admin tuning for postgresql.conf doesn't seem to be as applicable for windows. Doug On Sun, 2008-01-06 at 18:23 +0500, Usama Dar wrote: On Jan 3, 2008 8:57 PM, Doug Knight [EMAIL PROTECTED] wrote: All, Is there a place where I can find information about tuning postgresql running on a Windows XP Pro 32 bit system? I installed using the binary installer. I am seeing a high page fault delta and total page faults for one of the postgresql processes. Any help would be great. Which version of postgres? the process you are seeing this for is a user process? -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
On Mon, 2008-01-07 at 13:59 +0100, Markus Schiltknecht wrote: However, for tables which don't fit the use case of SE, people certainly don't want such a fill factor to bloat their tables. Sure, but it could be configurable and should only be enabled if the table is marked as partitioned on some condition... I think it would be a bad idea anyway if the DB would start partitioning on some arbitrary criteria based on analyzing he table, so the DBA should be the one to decide on what criteria to partition. In particular it could be a bad idea on occasions to partition on the clustering criteria for tables which were clustered once. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
On Mon, 2008-01-07 at 14:20 +0100, Markus Schiltknecht wrote: Why is that? AFAIUI, Segment Exclusion combines perfectly well with clustering. Or even better, with an upcoming feature to maintain clustered ordering. Where do you see disadvantages such an optimization for sequential scans? Well, as I understood it, this would be some kind of special case of clustering, where the cluster key is expected to be ever increasing in time and new rows would not be randomly distributed over the complete possible range. In theory you could also have each segment in turn be clustered on some other criteria than the partitioning criteria so indexed access could also be better on the main selection criteria which could be different than the partitioning criteria. All this is of course just speculations - but I guess that's what you expected too in this discussion :-) Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Hi, Csaba Nagy wrote: Sure, but it could be configurable and should only be enabled if the table is marked as partitioned on some condition... As I'm regarding SE as an optimization, I disagree here.. As all optimizations, SE should conceptually be reasonably close to cost-less when unneeded. I think it would be a bad idea anyway if the DB would start partitioning on some arbitrary criteria based on analyzing he table, so the DBA should be the one to decide on what criteria to partition. I absolutely agree for real partitioning, which targets manageability of table partitions. In particular it could be a bad idea on occasions to partition on the clustering criteria for tables which were clustered once. Why is that? AFAIUI, Segment Exclusion combines perfectly well with clustering. Or even better, with an upcoming feature to maintain clustered ordering. Where do you see disadvantages such an optimization for sequential scans? Regards Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate
Tom Lane wrote: Sokolov Yura [EMAIL PROTECTED] writes: create or replace function add_group(grp anyarray, ad anyelement, size int4) returns anyarray language plpgsql ... create aggregate build_group(anyelement, int4) ( SFUNC= add_group, STYPE = anyarray ); ERROR: argument declared anyarray is not an array but type anyarray After chewing on this for awhile, it seems to me that pg_aggregate.c is using enforce_generic_type_consistency() in a rather fundamentally different way than it's being used anywhere else. [snip] I think we could make enforce_generic_type_consistency() clearer by adding an additional argument bool allow_poly which specifies whether polymorphic actual argument and result types are allowed. [snip] lookup_agg_function() should always invoke enforce_generic_type_consistency(), with this argument true. This sounds like a reasonable plan to me. Although this problem really goes quite far back, I think it's probably not interesting to back-patch further than 8.2, because AFAICS the interesting cases involve aggregates with more than one argument. I agree, especially since this is the first time anyone has complained. Did you want me to work on this? I could probably put some time into it this coming weekend. Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM FULL out of memory
On Jan 7, 2008 2:40 PM, Michael Akinde [EMAIL PROTECTED] wrote: As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). My Apologies if my question seems redundant and something you have already discussed with list members, but why do you need to do a VACUUM FULL? have you not vacuumed for a while? or some special requirement which requires very aggressive space re-claim? Vacuum Full is also known to cause some index bloat at times as well. most systems i know run regular vacuums and had never required to run a vacuum full. -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
Re: [HACKERS] OUTER JOIN performance regression remains in 8.3beta4
On Sun, Jan 6, 2008 at 7:20 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: There was a serious performance regression in OUTER JOIN planning going from 8.2.4 to 8.2.5. I know Tom came up with some patches to mitigate the issues in 8.2.5, but my testing shows that problems remain in 8.3beta4. Please try the attached proposed patch. It seems to fix my simplified test case, but I'm not sure if there are any additional considerations involved in your real queries. Applied and built cleanly. Check found no errors. Startup clean. Query returns expected rows. Plan looks good. Thanks! -Kevin Sort (cost=1789.74..1789.75 rows=5 width=226) (actual time=308.768..308.772 rows=4 loops=1) Sort Key: CH.chargeNo, CH.chargeSeqNo Sort Method: quicksort Memory: 18kB - Hash Left Join (cost=1643.49..1789.68 rows=5 width=226) (actual time=308.630..308.723 rows=4 loops=1) Hash Cond: ((CH.sevClsCode)::bpchar = (S.sevClsCode)::bpchar) - Hash Left Join (cost=1641.95..1788.07 rows=5 width=195) (actual time=308.522..308.601 rows=4 loops=1) Hash Cond: ((CH.modSevClsCode)::bpchar = (M.sevClsCode)::bpchar) - Hash Left Join (cost=1640.41..1786.50 rows=5 width=164) (actual time=308.397..308.466 rows=4 loops=1) Hash Cond: ((CH.pleaCode)::bpchar = (PC.pleaCode)::bpchar) - Hash Left Join (cost=1639.19..1785.23 rows=5 width=128) (actual time=308.312..308.369 rows=4 loops=1) Hash Cond: (((CHST.countyNo)::smallint = (CTHE.countyNo)::smallint) AND ((CHST.eventType)::bpchar = (CTHE.eventType)::bpchar) AND ((CHST.caseType)::bpchar = (CTHE.caseType)::bpchar)) - Nested Loop Left Join (cost=0.00..116.14 rows=5 width=107) (actual time=0.049..0.093 rows=4 loops=1) - Index Scan using Charge_pkey on Charge CH (cost=0.00..12.01 rows=5 width=94) (actual time=0.037..0.047 rows=4 loops=1) Index Cond: (((countyNo)::smallint = 53) AND ((caseNo)::bpchar = '2007CM003476'::bpchar)) - Index Scan using CaseHist_pkey on CaseHist CHST (cost=0.00..20.79 rows=3 width=32) (actual time=0.002..0.002 rows=0 loops=4) Index Cond: (((CHST.countyNo)::smallint = 53) AND ((CHST.caseNo)::bpchar = '2007CM003476'::bpchar) AND ((CHST.histSeqNo)::smallint = (CH.reopHistSeqNo)::smallint)) - Hash (cost=1360.64..1360.64 rows=15917 width=98) (actual time=308.227..308.227 rows=15917 loops=1) - Subquery Scan CTHE (cost=148.89..1360.64 rows=15917 width=98) (actual time=10.499..263.746 rows=15917 loops=1) - Merge Left Join (cost=148.89..1201.47 rows=15917 width=77) (actual time=10.497..225.505 rows=15917 loops=1) Merge Cond: (((b.caseType)::bpchar = (d.caseType)::bpchar) AND ((b.eventType)::bpchar = (d.eventType)::bpchar)) Join Filter: ((d.countyNo)::smallint = (c.countyNo)::smallint) - Nested Loop (cost=2.90..953.87 rows=15917 width=67) (actual time=0.071..150.104 rows=15917 loops=1) - Index Scan using CaseTypeHistEventB_pkey on CaseTypeHistEventB b (cost=0.00..632.63 rows=15917 width=65) (actual time=0.029..30.370 rows=15917 loops=1) - Materialize (cost=2.90..2.91 rows=1 width=2) (actual time=0.001..0.002 rows=1 loops=15917) - Seq Scan on ControlRecord c (cost=0.00..2.90 rows=1 width=2) (actual time=0.029..0.049 rows=1 loops=1) Filter: ((countyNo)::smallint = 53) - Sort (cost=145.99..151.14 rows=2060 width=15) (actual time=10.416..12.879 rows=2060 loops=1) Sort Key: d.caseType, d.eventType Sort Method: quicksort Memory: 145kB - Seq Scan on CaseTypeHistEventD d (cost=0.00..32.60 rows=2060 width=15) (actual time=0.023..3.177 rows=2060 loops=1) - Hash (cost=1.10..1.10 rows=10 width=41) (actual time=0.048..0.048 rows=10 loops=1) - Seq Scan on PleaCode PC (cost=0.00..1.10 rows=10 width=41) (actual time=0.008..0.024 rows=10 loops=1) - Hash (cost=1.24..1.24 rows=24 width=34) (actual time=0.106..0.106 rows=24 loops=1) - Seq Scan on SevClsCode M (cost=0.00..1.24 rows=24 width=34) (actual time=0.008..0.044
Re: [HACKERS] Tuning Postgresql on Windows XP Pro 32 bit
Doug Knight wrote: We are running the binary distribution, version 8.2.5-1, installed on Windows XP Pro 32 bit with SP2. We typically run postgres on linux, but have a need to run it under windows as well. Our typical admin tuning for postgresql.conf doesn't seem to be as applicable for windows. So what have you tuned so far? what are your current postgresql settings that you have modified? What are your system specs for Hardware, RAM , CPU etc? -- Usama Munir Dar http://www.linkedin.com/in/usamadar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] VACUUM FULL out of memory
Hi, The system we are building is intended to be utilized in a number of different applications, so the testing we are doing is primarily directed at stressing the system by running it through its paces and uncovering any weaknesses. I prefer to find as many problems as possible now, rather than in production. ;-) For the current application set I'm testing, I expect we won't need to do much VACUUMing, as it will be a fairly static dataset only used for querying (once all the data is loaded). I know that we will be running some databases with some pretty rapid throughput (100 GB/day), but if VACUUM will do (as I expect), then we'll probably just stick to that. I don't have time to do any testing on that until next month, though. I do find it odd, however, that pgsql recommends using a VACUUM FULL (as a result of running the VACUUM). Especially if, as it seems, VACUUM FULL doesn't work for tables beyond a certain size. Assuming we have not set up something completely wrongly, this seems like a bug. If this is the wrong mailing list to be posting this, then please let me know. Regards, Michael Akinde Database Architect, Met.no Usama Dar wrote: On Jan 7, 2008 2:40 PM, Michael Akinde [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). My Apologies if my question seems redundant and something you have already discussed with list members, but why do you need to do a VACUUM FULL? have you not vacuumed for a while? or some special requirement which requires very aggressive space re-claim? Vacuum Full is also known to cause some index bloat at times as well. most systems i know run regular vacuums and had never required to run a vacuum full. -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:[EMAIL PROTECTED] tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
On Sat, Jan 05, 2008 at 08:02:41PM +0100, Markus Schiltknecht wrote: Well, management of relations is easy enough, known to the DBA and most importantly: it already exists. Having to set up something which is *not* tied to a relation complicates things just because it's an additional concept. But we're already dealing with some complicated concepts. There isn't anything that will prevent current-style partitioning strategies from continuing to work in the face of Simon's proposal. But let me see if I can outline the sort of cases where I see real value in what he's outlined. There is a tendency in data systems to gather all manner of data that, in retrospect, _might_ turn out to be be valuable; but which, at the time, is not really valuable at all. Moreover, the value later on might be relatively low: if you can learn something much later from that data, and do so easily, then it will be worth doing. But if the work involved passes some threshold (say 1/2 a day), it's suddenly not worth it any more. It's simple economics: below a certain cost, the data is valuable. Above a certain cost, you simply shouldn't keep the data in the first place, because the cost of using it is higher than any value you'll likely be able to extract. Simon's proposal changes the calculations you have to do. If keeping some data online longer does not impose administrative or operational overhead (you have it marked read only, so there's no I/O for vacuum; you don't need to do anything to get the data marked read only; c.), then all it costs is a little more disk, which is relatively cheap these days. More importantly, if the longer-term effect of this strategy is to make it possible to move such data offline _without imposing a big cost_ when moving it back online, then the value is potentially very high. Without even trying, I can think of a dozen examples in the past 5 years where I could have used that sort of functionality. Because the cost of data retrieval was high enough, we had to decide that the question wasn't worth answering. Some of those answers might have been quite valuable indeed to the Internet community, to be frank; but because I had to pay the cost without getting much direct benefit, it just wasn't worth the effort. The thing about Simon's proposal that is beguiling is that it is aimed at a very common use pattern. The potential for automatic management under such a use pattern makes it seem to me to be worth exploring in some detail. Agreed. I'd say that's why the DBA needs to be able to define the split point between partitions: only he knows the meaning of the data. I think this is only partly true. A casual glance at the -general list will reveal all manner of false assumptions on the parts of administrators about how their data is structured. My experience is that, given that the computer has way more information about the data than I do, it is more likely to make the right choice. To the extent it doesn't do so, that's a problem in the planning (or whatever) algorithms, and it ought to be fixed there. A ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] VACUUM FULL out of memory
On Mon, Jan 07, 2008 at 10:40:23AM +0100, Michael Akinde wrote: As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). That ought to leave more than enough space for other processes in the system. Again, the system fails on the VACUUM with the following error (identical to the error we had when maintenance_work_mem was very low. INFO: vacuuming pg_catalog.pg_largeobject ERROR: out of memory DETAIL: Failed on request of size 536870912 Something is using up the memory on the machine, or (I'll bet this is more likely) your user (postgres? Whatever's running the postmaster) has a ulimit on its ability to allocate memory on the machine. It strikes me as somewhat worrying that VACUUM FULL ANALYZE has so much trouble with a large table. Granted - 730 million rows is a good deal - No, it's not really that big. I've never seen a problem like this. If it were the 8.3 beta, I'd be worried; but I'm inclined to suggest you look at the OS settings first given your set up. Note that you should almost never use VACUUM FULL unless you've really messed things up. I understand from the thread that you're just testing things out right now. But VACUUM FULL is not something you should _ever_ need in production, if you've set things up correctly. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] OUTER JOIN performance regression remains in 8.3beta4
On Fri, Jan 4, 2008 at 6:46 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: 8.2 (at least at branch tip, can't say for sure about earlier dot-releases) 8.2.4 and 8.2.5 both behave this way. f2 | f3 | f1 ++ 53 || 53 (1 row) which I claim is the wrong answer. I agree that it is the wrong answer. the first question for you is whether it is intentional that your query joins CTHE to CHST rather than to CH. CTHE.eventType has to match to CHST; there is not an eventType column in CH. The other values could refer to either; I would expect that to be logically equivalent (unless I'm missing something). -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Index trouble with 8.3b4
Gregory Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] writes:
I didn't have any luck reproducing either of these behaviors --- maybe
it's data-dependent. Can you extract a test case?
I haven't been able to reproduce this either but I produced an entirely
different problem:
postgres=# create index concurrently dg5 on doc using gin
(to_tsvector('english',d));
ERROR: deadlock detected
DETAIL: Process 7076 waits for ShareLock on unrecognized locktag type 5;
blocked by process 10497.
Process 10497 waits for ShareUpdateExclusiveLock on relation 24656 of
database 11511; blocked by process 7076.
Further poking around shows that the unrecognized locktag is because
lmgr.c:DescribeLockTag was never taught about virtual xids. Ie something like
this (untested):
--- lmgr.c 04 Jan 2008 15:12:37 + 1.95
+++ lmgr.c 07 Jan 2008 15:54:49 +
@@ -739,6 +739,12 @@
tag-locktag_field2,
tag-locktag_field1);
break;
+ case LOCKTAG_VIRTUALTRANSACTION:
+ appendStringInfo(buf,
+_(virtual transaction
%d/%u),
+tag-locktag_field1,
+tag-locktag_field2);
+ break;
case LOCKTAG_TRANSACTION:
appendStringInfo(buf,
_(transaction %u),
The pid it's waiting on is long since gone but looks like it was probably an
autovacuum process. I have a vague recollection that you had rigged CREATE
INDEX CONCURRENTLY to ignore vacuum processes when checking for conflicting
processes. Since any such process will be blocked on our session-level
ShareUpdateExclusiveLock it will always cause a deadlock and we would rather
it just hang out and wait until our index build is finished.
On the other hand we can't just ignore all vacuums because someone could issue
a manual vacuum inside a transaction (I think?). But this is a general problem
with all the places where we check if another transaction is just running
vacuum, such as checking for globalxmin. We should only be ignoring
transactions which were started just to execute a vacuum.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 7 Jan 2008 09:18:24 -0300 Alvaro Herrera [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: SELECT oid::regclass FROM pg_class WHERE reltoastrelid='pg_toast.pg_toast_49013869'::regclass; oid | pg_temp_24.tmp_isp_blk_chk The hack to get this cleaned up was to connect about 2 dozen times (to get to slot 24) with psql via different sessions and create temp tables. Once we hit slot 24, the probably instantly went away and the database returned to normal state. Ah -- interesting. This is a known issue, but we haven't found a solution yet. Is there bug number? Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHglltATb/zqfZUUQRAk19AJ9GywJ7ohqGZa4jrRYtufgbwCacowCgrgml 00egslWmlrI0MK2sJjyc63I= =Y4Ok -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Index trouble with 8.3b4
Gregory Stark [EMAIL PROTECTED] writes: On the other hand we can't just ignore all vacuums because someone could issue a manual vacuum inside a transaction (I think?). Doh, ignore this. sigh. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(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] Bug: Unreferenced temp tables disables vacuum to update xid
Joshua D. Drake wrote: SELECT oid::regclass FROM pg_class WHERE reltoastrelid='pg_toast.pg_toast_49013869'::regclass; oid | pg_temp_24.tmp_isp_blk_chk The hack to get this cleaned up was to connect about 2 dozen times (to get to slot 24) with psql via different sessions and create temp tables. Once we hit slot 24, the probably instantly went away and the database returned to normal state. Ah -- interesting. This is a known issue, but we haven't found a solution yet. Is there bug number? I assume it is this TODO item: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php but am confused how the fix worked. Have all of these backends been active for 1 billion transactions? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 7 Jan 2008 11:58:29 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Ah -- interesting. This is a known issue, but we haven't found a solution yet. Is there bug number? I assume it is this TODO item: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php but am confused how the fix worked. Have all of these backends been active for 1 billion transactions? Well it certainly appears that the TODO item is related. However there are a couple of differences. 1. I had to manually vacuum because we had already hid xidStoplimit. 2. Postgres has been restarted multiple times and it made zero difference. E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent when it happens. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHgl0CATb/zqfZUUQRAtcnAKChLV9E4p7klYXRnVoEWKGsM+xv2QCgjmKB JrBjOrL9i/4RcwXKMNk+z5I= =6Gdf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] VACUUM FULL out of memory
On Mon, 07 Jan 2008 10:57:53 -0500, Andrew Sullivan wrote: Note that you should almost never use VACUUM FULL unless you've really messed things up. I understand from the thread that you're just testing things out right now. But VACUUM FULL is not something you should _ever_ need in production, if you've set things up correctly. Then why does it exist? Is it a historical leftover? If it is only needed for emergency, should it not have a different name? Just curious.. Holger ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] VACUUM FULL out of memory
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 07 Jan 2008 17:33:53 +0100 Holger Hoffstaette [EMAIL PROTECTED] wrote: On Mon, 07 Jan 2008 10:57:53 -0500, Andrew Sullivan wrote: Note that you should almost never use VACUUM FULL unless you've really messed things up. I understand from the thread that you're just testing things out right now. But VACUUM FULL is not something you should _ever_ need in production, if you've set things up correctly. Then why does it exist? Is it a historical leftover? If it is only needed for emergency, should it not have a different name? Just curious.. There are times when it is required, usually when people don't configure normal vacuum/autovacuum correctly. Sincerely, Joshua D. Drake Holger ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHgmEbATb/zqfZUUQRAoyXAJ9GB4lXGr6UsEMpdT4KDvtIkRv+ywCdEMQV T07KuT+OUWcrr9NEX+blSuQ= =rDNL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Hi, Andrew Sullivan wrote: On Sat, Jan 05, 2008 at 08:02:41PM +0100, Markus Schiltknecht wrote: Well, management of relations is easy enough, known to the DBA and most importantly: it already exists. Having to set up something which is *not* tied to a relation complicates things just because it's an additional concept. But we're already dealing with some complicated concepts. Possibly, yes, but that's by far no reason to introduce even more complicated concepts... Does anything speak against letting the DBA handle partitions as relations? There isn't anything that will prevent current-style partitioning strategies from continuing to work in the face of Simon's proposal. Agreed. Nor will Simon's proposal completely replace them. Without even trying, I can think of a dozen examples in the past 5 years where I could have used that sort of functionality. Because the cost of data retrieval was high enough, we had to decide that the question wasn't worth answering. Some of those answers might have been quite valuable indeed to the Internet community, to be frank; but because I had to pay the cost without getting much direct benefit, it just wasn't worth the effort. Sure, there's value in Simon's proposal. But it has pretty strict requirements. IMO, it's pretty hard to say, if it would have helped at all for your cases. Any of them still available to check? Remember the requirements: no single tuple in the segment may be significantly out of the average bounds. Otherwise, the min/max gets pretty useless and the segment can never be excluded. As said before, combination with CLUSTERing might help, yes. But if you need to maintain CLUSTERed ordering, aren't there better ways? For example, you could use binary searching on the relation directly, much like with indices, instead of sequentially scanning on the CLUSTERed relation. That would even give us some sort of indices with visibility. Agreed. I'd say that's why the DBA needs to be able to define the split point between partitions: only he knows the meaning of the data. I think this is only partly true. A casual glance at the -general list will reveal all manner of false assumptions on the parts of administrators about how their data is structured. My experience is that, given that the computer has way more information about the data than I do, it is more likely to make the right choice. To the extent it doesn't do so, that's a problem in the planning (or whatever) algorithms, and it ought to be fixed there. Well, Postgres doesn't automatically create indices, for a counter example. With regard to partitioning over multiple table spaces, I think the DBA definitely has more information available, than the computer. A DBA (hopefully) knows future plans and emergency strategies for the storage system, for example. Lacking such information, the database system will have a pretty hard time taking a good decision on how to partition between table spaces, IMO. Regards Markus ---(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] Bug: Unreferenced temp tables disables vacuum to update xid
Joshua D. Drake [EMAIL PROTECTED] writes: On Mon, 7 Jan 2008 11:58:29 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: I assume it is this TODO item: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php but am confused how the fix worked. Have all of these backends been active for 1 billion transactions? Well it certainly appears that the TODO item is related. However there are a couple of differences. Yeah, it seems there are two bugs here. 1) temporary tables prevent frozen-xid advancement and 2) if a process dies at the wrong moment it's possible to temporary tables. Either one alone is pretty minor but I guess the combination is lethal. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Gregory Stark [EMAIL PROTECTED] writes: Joshua D. Drake [EMAIL PROTECTED] writes: On Mon, 7 Jan 2008 11:58:29 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: I assume it is this TODO item: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php but am confused how the fix worked. Have all of these backends been active for 1 billion transactions? Well it certainly appears that the TODO item is related. However there are a couple of differences. Yeah, it seems there are two bugs here. 1) temporary tables prevent frozen-xid advancement and 2) if a process dies at the wrong moment it's possible to temporary tables. Either one alone is pretty minor but I guess the combination is lethal. oops, 2) if a process dies at the wrong moment it's possible to *leak* temporary tables -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
On Monday 07 January 2008 09:10:24 Joshua D. Drake wrote: On Mon, 7 Jan 2008 11:58:29 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Ah -- interesting. This is a known issue, but we haven't found a solution yet. Is there bug number? I assume it is this TODO item: o Prevent long-lived temporary tables from causing frozen-xid advancement starvation The problem is that autovacuum cannot vacuum them to set frozen xids; only the session that created them can do that. http://archives.postgresql.org/pgsql-general/2007-06/msg01645.php but am confused how the fix worked. Have all of these backends been active for 1 billion transactions? Well it certainly appears that the TODO item is related. However there are a couple of differences. 1. I had to manually vacuum because we had already hid xidStoplimit. 2. Postgres has been restarted multiple times and it made zero difference. E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent when it happens. After a fresh start of postgres, there should be no temp tables, so would a work around to this at least be at postmaster start to (for a lack of a better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in interactive mode? Doing this would at least have allowedthe manual vacuum to do what it needed and not have caused confusion on the part of the user? Also it would have greatly reduced the total time to resolution, and not requiring hacking the backend to get there. Sincerely, Joshua D. Drake -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 7 Jan 2008 10:37:18 -0800 Darcy Buskermolen [EMAIL PROTECTED] wrote: 1. I had to manually vacuum because we had already hid xidStoplimit. 2. Postgres has been restarted multiple times and it made zero difference. E.g; PostgreSQL isn't cleaning up after itself and it isn't apparent when it happens. After a fresh start of postgres, there should be no temp tables, so would a work around to this at least be at postmaster start to (for a lack of a better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in interactive mode? Doing this would at least have allowedthe manual vacuum to do what it needed and not have caused confusion on the part of the user? Also it would have greatly reduced the total time to resolution, and not requiring hacking the backend to get there. +1 Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHgnQaATb/zqfZUUQRAt1qAJ4hzeNG8fzA4l5y/luNrg3eGOz5QQCfcvtZ xMuLPQSEbvG+AYfTRkEyLD0= =+Lkk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote: Does anything speak against letting the DBA handle partitions as relations? Yes: it doesn't solve the problem I have, which is that I don't want to have to manage a whole bunch of tables. I want one table, and I want to be able to say, That section is closed. Sure, there's value in Simon's proposal. But it has pretty strict requirements. IMO, it's pretty hard to say, if it would have helped at all for your cases. Any of them still available to check? No, but one of your worries doesn't bother me: Remember the requirements: no single tuple in the segment may be significantly out of the average bounds. Otherwise, the min/max gets pretty useless and the segment can never be excluded. The segment can never be excluded in a search on that key, yes. But consider the likely cases we're looking at: WHERE some_date = '1999-01-01' AND some_date '2001-01-01'; WHERE sequence_field BETWEEN 3000 AND 30; c. These are the two obvious cases: you're searching for data in a given date range or for primary (sometimes artificial) identifiers in a range, and the source data increases (almost) monotonically. You have to do this now anyway, because there's _some_ basis on which you're partitioning your data; but today, you do this with a lot of fooling around with views and nasty triggers that push data into the right table, assuming someone doesn't screw it up. need to maintain CLUSTERed ordering, aren't there better ways? For example, you could use binary searching on the relation directly, much like with indices, instead of sequentially scanning on the CLUSTERed relation. That would even give us some sort of indices with visibility. I think this is a nice idea too :) Well, Postgres doesn't automatically create indices, for a counter example. Yes, and it has no data-use analyser tools that automatically suggest indexes, either. That's the sort of thing people coming from other (err, Other ;-) products complain about, in fact. definitely has more information available, than the computer. A DBA (hopefully) knows future plans and emergency strategies for the storage system, for example. Perhaps my jaundice comes from too much time spent in operational trenches, but while good DBAs have some ideas about that, large numbers of them are harried and overwhelmed just by the piles of work they already have. Nevertheless, while what you say is true, I'm not sure what it has to do with the present case. I don't think the current proposal is to address partitioning across table spaces. It's to do with the way certain segments of a table are interpreted by the system. It's undoubtedly true that this strategy is of questionable utility for many kinds of use of PostgreSQL. But it seems to offer very significant advantages for one use-pattern that is very common. That said, I am not trying to argue it should be adopted without poking at its weaknesses. I just think it unfair to ask the proposal to address problems it's not really aimed at. A ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] ERROR: translation failed from server encoding to wchar_t
I am using tsearch2 with pgsql 8.2.5 and get the following error when calling to_tsvector : translation failed from server encoding to wchar_t My database is UTF8 encoded and the data sent to to_tsvector comes from a bytea column converted to text with encode(COLUMN, 'escape'). In 8.1 with tsearch2 it worked perfectly ... Thanks for you help, ilan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] VACUUM FULL out of memory
Michael Akinde [EMAIL PROTECTED] writes: As suggested, I tested a VACUUM FULL ANALYZE with 128MB shared_buffers and 512 MB reserved for maintenance_work_mem (on a 32 bit machine with 4 GB RAM). That ought to leave more than enough space for other processes in the system. Again, the system fails on the VACUUM with the following error (identical to the error we had when maintenance_work_mem was very low. INFO: vacuuming pg_catalog.pg_largeobject ERROR: out of memory DETAIL: Failed on request of size 536870912 Are you sure this is a VACUUM FULL, and not a plain VACUUM? I suspect that it's the latter, and the reason it's failing is that you are running the postmaster under a ulimit that is less than 512MB (or at least not enough more to allow an allocation of that size). 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] ERROR: translation failed from server encoding to wchar_t
http://pastebin.ca/845670
This url provides a testcase ...
fisrt pass :
= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
ERROR: translation failed from server encoding to wchar_t
second pass :
= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
UPDATE 1
The to_tsvector seems to accept the row at random ...
On Jan 7, 9:16 pm, [EMAIL PROTECTED] wrote:
I am using tsearch2 with pgsql 8.2.5 and get the following error when
calling to_tsvector :
translation failed from server encoding to wchar_t
My database is UTF8 encoded and the data sent to to_tsvector comes
from a bytea column converted to text with
encode(COLUMN, 'escape').
In 8.1 with tsearch2 it worked perfectly ...
Thanks for you help,
ilan
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Darcy Buskermolen [EMAIL PROTECTED] writes: After a fresh start of postgres, there should be no temp tables, so would a work around to this at least be at postmaster start to (for a lack of a better pseudo code ) DROP SCHEMA pg_temp* CASCADE; before coming up in interactive mode? The argument against this is the same as not wiping out apparently-unreferenced regular tables: automatically destroying the evidence after a crash is someday going to bite you. Admittedly, this argument is a bit weaker for temp tables than it is for regular tables, but that only goes to the question of whether the data is valuable on its own terms, not whether it might be valuable for crash analysis. The real question that Josh's report brings up to me is why the heck was there an orphaned temp table? Especially if it was only a toast table and not the linked regular temp table? Something happened there that should not have. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] ERROR: translation failed from server encoding to wchar_t
Found something interesting with this testcase.
update fails after SELECT query.
Can anyone confirm this ???
dbname= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
UPDATE 1
dbname= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
UPDATE 1
dbname= select * from dbmail_messageblks where messageblk_idnr =
12949;
messageblk_idnr | physmessage_id
|
messageblk
| blocksize | is_header |
idxfti
-+
+--
+---+---
+-
12949 | 6319 | l'\351quipe de Casinos-park a bien
re\347u votre messsage. \012\012Vous aurez une r\351ponse d\350s que
l'un de nos responsables aura pris connaissance de votre envoi.
\012\012cordialement\012\012l'\351quipe de casinos-park.
\012\012\012\012The team of Casinos-park received
your messsage.\012\012You will have an answer as soon as one of our
persons in charge takes note of your sending. \012\012Best regards
\012\012The team of casinos-park.\012 | 398 | 0 | '=':3
'e':5 'h':11 'i':2,10 'k':12 'l':1 'o':7 'p':9,16 'r':8 's':6 't':13
'u':15 'y':14 'rk':4
(1 row)
dbname= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
ERROR: translation failed from server encoding to wchar_t
dbname= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
UPDATE 1
On Jan 7, 10:21 pm, [EMAIL PROTECTED] wrote:
http://pastebin.ca/845670
This url provides a testcase ...
fisrt pass :
= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
ERROR: translation failed from server encoding to wchar_t
second pass :
= UPDATE dbmail_messageblks SET idxFTI=to_tsvector('simple',
encode(messageblk, 'escape')) where messageblk_idnr = 12949;
UPDATE 1
The to_tsvector seems to accept the row at random ...
On Jan 7, 9:16 pm, [EMAIL PROTECTED] wrote:
I am using tsearch2 with pgsql 8.2.5 and get the following error when
calling to_tsvector :
translation failed from server encoding to wchar_t
My database is UTF8 encoded and the data sent to to_tsvector comes
from a bytea column converted to text with
encode(COLUMN, 'escape').
In 8.1 with tsearch2 it worked perfectly ...
Thanks for you help,
ilan
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Andrew Sullivan wrote: On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote: ...the requirements: no single tuple in the segment may be significantly out of the average bounds. Otherwise, the min/max gets pretty useless and the segment can never be excluded. The segment can never be excluded in a search on that key, yes. But consider the likely cases we're looking at: ...you're searching for data in a given date range or for primary (sometimes artificial) identifiers in a range, and the source data increases (almost) monotonically. It seems to me the idea discussed elsewhere in the thread[1] about configurable segment sizes would make this limitation much less problematic for some types of data. Some of my biggest tables are clustered by zip-code; and are insert mostly. Common queries are where state_provence='TX' or where city='Dallas'. While I doubt I have enough data to fill a 1GB segment for any but the largest cities; I certainly have runs of many consecutive blocks - since clustering by zip tends to cluster cities as well. Even though the table's not monotonically increasing or decreasing, like values for cities and states are clustered together. Is my understanding right that these Segment Visibility Maps could help this case as well? [1] http://archives.postgresql.org/pgsql-hackers/2008-01/msg00065.php ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate
Joe Conway [EMAIL PROTECTED] writes: Did you want me to work on this? I could probably put some time into it this coming weekend. I'll try to get to it before that --- if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, so it'd be nice to have this dealt with sooner than that. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Hi, On Mon, 2008-01-07 at 17:12 -0500, Tom Lane wrote: if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, Please give the packagers and other people one more week break between two releases. We all had a busy weekend. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
On 2008-01-07, Tom Lane [EMAIL PROTECTED] wrote: The real question that Josh's report brings up to me is why the heck was there an orphaned temp table? Especially if it was only a toast table and not the linked regular temp table? Something happened there that should not have. The regular table was there too, but the regular table's relfrozenxid was apparently recent, only the toast table's was old: pg_toast_49013869 | 2146491285 [...] SELECT oid::regclass FROM pg_class WHERE reltoastrelid='pg_toast.pg_toast_49013869'::regclass; oid | pg_temp_24.tmp_isp_blk_chk The regular table had not shown up on a query of age(relfrozenxid) WHERE relkind='r' but the toast table showed up on a similar query with WHERE relkind='t'. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implementing Sorting Refinements
You'll get better response if you don't hijack threads. :) Also,
there's nothing in here that describes what the benefits of this
change are.
On Jan 1, 2008, at 2:09 PM, Manolo _ wrote:
Hi to all.
This mail is aimed at asking some suggestion to face PostgreSQL
(PG) development to implement a refinement to PG source code. I'll
briefly summarize the idea of the 2-Way Replacement
Selection (2WRS) refinement, just in case. If you already remember
what 2WRS is, you can please jump directly to the IMPLEMENTATION
ISSUES part of this mail.
2WRS.
Refinement of the Replacement Selection (RS) technique currently
used by PG in src/backend/utils/sort/tuplesort.c .
The 2WRS uses two heaps instead of just one in order to create the
current (logical) run. Here there are some fundamental points of
the 2WRS technique:
- 'qsort' the initial unsorted 'memtuples' array
- divide the 'memtuples' array into two parts and each of those
will be managed as a heap
- one of the heaps will arrange its elements in ascending order,
while the other heap in descending order
- each heap will spill its current root in its corresponding run
(i.e.: we have a run per each of those two heaps), so we are
actually creating 2 physical current runs
- those 2 current physical runs could theoretically be merged
into the same logical run, actually we can make 'mergesort' think
they do belong to the same physical run. That reduces the number of
comparisons 'mergesort' has to do at each merge step (that means
less seek delay time on mass storage). We can also think the
average length of logical runs produced by 2WRS will probably be
greater than the average length produced by RS (again less seek
delay time: the longer each run the less number of final runs
produced, that means the less number of comparisons at each
'mergesort' step).
IMPLEMENTATION ISSUES.
Where to place those heaps?
1) I think that both heaps could be arranged on the same
'memtuples' array. That allows easily subsequent resizing those
heaps according to their effective use or according to some
heuristic, without reallocating memory.
How to arrange those heaps?
2a) It's convenient to arrange those heaps root to root. That is
arranging those heaps with their roots toward the center of
'memtuples' (in a way we can say they lay face to face, or root
to root as said before) while their leaves lay towards the extreme
indexes of the 'memtuples' array (that is the last leaf of one heap
will lay at index 0, the last leaf of the other heap laying at
index memtupsize-1. This arrangement prevents overlapping elements
between those physical runs associated to the same current logical
run.
PRO: once we qsort memtuples and divide it into 2 parts we already
get those two heaps, no need to build them.
CONTRA: ???
2b) As in 2a) but arranging heaps leaf to leaf, that is their
roots will lay at the extreme indexes of 'memtuples' while their
leaves towards the center of the 'memtuples' array.
Or even start building heaps as soon as we get initial elements,
instead of qsort the whole 'memtuples' array.
Any PRO/CONTRA compared to 2a)???
Current run numbers
I think I should duplicate the 'int currentRun' variable in the
Tuplesortstate struct. I'll replace it with a 'int currentRunUP'
and 'int currentRunDOWN' variables in order to distinguish those
two physical runs associated to those 2 heaps. In this case I will
give a run number (max{currentRunUP,currentRunDOWN} + 1) to
elements not belonging to the current logical run. I suppose no
need to touch 'long availMem' nor 'long allowedMem' variables nor
any others.
Heap functions
I will duplicate all the heap management functions in order to
adapt them to the kind of heap they should be applied to (for
example, the tuplesort_heap_siftup function should be replaced with
tuplesort_heap_siftupUP and tuplesort_heap_siftupDOWN functions).
Merge Plan
This technique would use a sort of merge plan to instruct
mergesort on how to use those physical runs. Actually mergesort
should consider at first odd runs before pair runs. That is,
for example, mergesort should start merging runs with run number
1,3,5,7,... and when run number X terminates start considering run
number X+1. Obviously that doesn't need any merge plan, but I
remember someone else (as Simon Riggs) was interested in sorting
improvements so it could be a good thing to know if I should
consider any conventions or paramethers in order to possibly create
that merge plan.
DEVELOPMENT CONTEXT
I preferred to use the last stable release at the moment, that is
8.2.
Any comment/suggestion/advice ?
Thanks for your attention and for your time.
Regards, Manolo.
_
Express yourself instantly with MSN Messenger! Download today it's
FREE!
Re: [HACKERS] Implementing Sorting Refinements
Well, sorry for hijacking... ummm how did I do that?
Anyway I'll thank you for giving a sign of life when I was almost loosing
my hopes to get any kind of answer from -hackers.
I suppose the lack of answers was due to the way I wrote my mail. At that
moment I supposed that at least someone reminded the 2WRS technique and
possible benefits described into previous posts.
I think I was wrong, so I'll write it once again hoping meanwhile to get
some suggestions on: HOWTO write a mail to which -hackers will give an
answer :) hehehehe
Thanks for your attention.
Manolo.
--
From: Decibel! [EMAIL PROTECTED]
Sent: Tuesday, January 08, 2008 12:34 AM
To: Manolo _ [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Implementing Sorting Refinements
You'll get better response if you don't hijack threads. :) Also, there's
nothing in here that describes what the benefits of this change are.
On Jan 1, 2008, at 2:09 PM, Manolo _ wrote:
Hi to all.
This mail is aimed at asking some suggestion to face PostgreSQL (PG)
development to implement a refinement to PG source code. I'll briefly
summarize the idea of the 2-Way Replacement Selection (2WRS)
refinement, just in case. If you already remember what 2WRS is, you can
please jump directly to the IMPLEMENTATION ISSUES part of this mail.
2WRS.
Refinement of the Replacement Selection (RS) technique currently used by
PG in src/backend/utils/sort/tuplesort.c .
The 2WRS uses two heaps instead of just one in order to create the
current (logical) run. Here there are some fundamental points of the
2WRS technique:
- 'qsort' the initial unsorted 'memtuples' array
- divide the 'memtuples' array into two parts and each of those will be
managed as a heap
- one of the heaps will arrange its elements in ascending order, while
the other heap in descending order
- each heap will spill its current root in its corresponding run (i.e.:
we have a run per each of those two heaps), so we are actually creating
2 physical current runs
- those 2 current physical runs could theoretically be merged into the
same logical run, actually we can make 'mergesort' think they do belong
to the same physical run. That reduces the number of comparisons
'mergesort' has to do at each merge step (that means less seek delay
time on mass storage). We can also think the average length of logical
runs produced by 2WRS will probably be greater than the average length
produced by RS (again less seek delay time: the longer each run the less
number of final runs produced, that means the less number of comparisons
at each 'mergesort' step).
IMPLEMENTATION ISSUES.
Where to place those heaps?
1) I think that both heaps could be arranged on the same 'memtuples'
array. That allows easily subsequent resizing those heaps according to
their effective use or according to some heuristic, without reallocating
memory.
How to arrange those heaps?
2a) It's convenient to arrange those heaps root to root. That is
arranging those heaps with their roots toward the center of 'memtuples'
(in a way we can say they lay face to face, or root to root as said
before) while their leaves lay towards the extreme indexes of the
'memtuples' array (that is the last leaf of one heap will lay at index
0, the last leaf of the other heap laying at index memtupsize-1. This
arrangement prevents overlapping elements between those physical runs
associated to the same current logical run.
PRO: once we qsort memtuples and divide it into 2 parts we already get
those two heaps, no need to build them.
CONTRA: ???
2b) As in 2a) but arranging heaps leaf to leaf, that is their roots
will lay at the extreme indexes of 'memtuples' while their leaves
towards the center of the 'memtuples' array.
Or even start building heaps as soon as we get initial elements, instead
of qsort the whole 'memtuples' array.
Any PRO/CONTRA compared to 2a)???
Current run numbers
I think I should duplicate the 'int currentRun' variable in the
Tuplesortstate struct. I'll replace it with a 'int currentRunUP' and
'int currentRunDOWN' variables in order to distinguish those two
physical runs associated to those 2 heaps. In this case I will give a
run number (max{currentRunUP,currentRunDOWN} + 1) to elements not
belonging to the current logical run. I suppose no need to touch 'long
availMem' nor 'long allowedMem' variables nor any others.
Heap functions
I will duplicate all the heap management functions in order to adapt
them to the kind of heap they should be applied to (for example, the
tuplesort_heap_siftup function should be replaced with
tuplesort_heap_siftupUP and tuplesort_heap_siftupDOWN functions).
Merge Plan
This technique would use a sort of merge plan to instruct mergesort on
how to use those physical runs. Actually mergesort should consider at
first odd runs before pair runs. That is, for example,
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Devrim G?ND?Z wrote: Hi, On Mon, 2008-01-07 at 17:12 -0500, Tom Lane wrote: if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, Please give the packagers and other people one more week break between two releases. We all had a busy weekend. We have thousands of people waiting for the 8.3 release. It seems odd to hold up 8.3 because the packagers are too busy. Perhaps we need more packagers or release the source and have the binary packages ready later. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Hi, Andrew Sullivan wrote: On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote: Does anything speak against letting the DBA handle partitions as relations? Yes: it doesn't solve the problem I have, which is that I don't want to have to manage a whole bunch of tables. I want one table, and I want to be able to say, That section is closed. That's a fair requirement. Wanting to manage partitions manually is another fair requirement, IMO. They can well coexist. Remember the requirements: no single tuple in the segment may be significantly out of the average bounds. Otherwise, the min/max gets pretty useless and the segment can never be excluded. The segment can never be excluded in a search on that key, yes. But consider the likely cases we're looking at: Uh, which key are you talking about? AFAIU Simon's proposal, he suggests maintaining min/max values for all columns of the table. WHERE some_date = '1999-01-01' AND some_date '2001-01-01'; Yeah, and if only *one* tuple in the 1G segment has: some_date = '1998-12-31' OR some_date = '2001-01-01' Segment Exclusion can't exclude that segment. That's all I'm saying. That said, I am not trying to argue it should be adopted without poking at its weaknesses. I just think it unfair to ask the proposal to address problems it's not really aimed at. Huh? I'm certainly not the one asking for it. Quite the opposite, I'm warning from over-estimating the use of SE. In his proposal, Simon was explicitly comparing to declarative partitioning, pointing out lots of advantages and implicitly stating that SE could cover most, if not all needs of what's commonly understand by partitioning. That's where I disagree. But certainly, SE and SVM has some merit for it's use case. And I'm looking forward to test patches ;-) Regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 7 Jan 2008 19:06:27 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Devrim GÜNDÜZ wrote: Hi, On Mon, 2008-01-07 at 17:12 -0500, Tom Lane wrote: if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, Please give the packagers and other people one more week break between two releases. We all had a busy weekend. We have thousands of people waiting for the 8.3 release. It seems odd to hold up 8.3 because the packagers are too busy. Perhaps we need more packagers or release the source and have the binary packages ready later. Perhaps we need to allow the release candidate to percolate and be tested a bit before we rush to release. If I recall, it is release when it is done not release because lots of people are waiting for it. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHgsEEATb/zqfZUUQRAkpVAJ9aThdQjyyCc9B7mv4hGDZbJygUpwCfcTLi qGyVYutwo6cL8UwfWf38c0A= =T8Pb -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Hi, On Mon, 2008-01-07 at 19:06 -0500, Bruce Momjian wrote: We have thousands of people waiting for the 8.3 release. Really? How did you get this statistic? ...and IIRC, our motto is Release when it is ready, not release because thousands ppl are waiting for it. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
On Monday 07 January 2008 16:06:27 Bruce Momjian wrote: Devrim GÜNDÜZ wrote: Hi, On Mon, 2008-01-07 at 17:12 -0500, Tom Lane wrote: if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, Please give the packagers and other people one more week break between two releases. We all had a busy weekend. We have thousands of people waiting for the 8.3 release. It seems odd to hold up 8.3 because the packagers are too busy. Perhaps we need more packagers or release the source and have the binary packages ready later. Also to be fair I've seen no real announcement of RC1, probabbly because it's been bundled in with security releases for the other branched.. can we maybe do an RC1 announcement, let people actually test the RC before we push a gold ? -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Andrew - Supernews [EMAIL PROTECTED] writes: On 2008-01-07, Tom Lane [EMAIL PROTECTED] wrote: The real question that Josh's report brings up to me is why the heck was there an orphaned temp table? Especially if it was only a toast table and not the linked regular temp table? Something happened there that should not have. The regular table was there too, but the regular table's relfrozenxid was apparently recent, only the toast table's was old: Hmm, that's even more odd, since AFAICS vacuum will always vacuum a toast table immediately after vacuuming the parent. I wonder whether we have a bug somewhere that allows a toast table's relfrozenxid to get initially set to something substantially different from the parent's. (BTW, if the parent table *was* there then Josh hardly needed any fancy jujitsu to clear the problem -- drop table pg_temp_24.tmp_isp_blk_chk as a superuser should've worked. I wouldn't try this if the originating backend were still around, but if it's not then there's not going to be anything all that special about the temp table.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Hi, On Mon, 2008-01-07 at 19:06 -0500, Bruce Momjian wrote: It seems odd to hold up 8.3 because the packagers are too busy. ... and read my e-mail again. I wrote: Please give the packagers and other people one more week break... I did not say just the packagers. You give almost no time to people to test the release itself -- we are already behind our schedule. One week more would not hurt, IMHO. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Implementing Sorting Refinements
On Jan 8, 2008 1:04 AM, [EMAIL PROTECTED] wrote: Well, sorry for hijacking... ummm how did I do that? Anyway I'll thank you for giving a sign of life when I was almost loosing my hopes to get any kind of answer from -hackers. Don't forget that we're just a few days/weeks of 8.3 release so the attention is a bit focused on it at the moment (and I'm not speaking of the security releases of today). Don't feel disappointed about the lack of attention you're suffering at the moment, just post your proposal again after 8.3 release, explain what you plan to do and why and perhaps you'll have the time to write a mock-up and get some numbers to prove your point before that. That could help too. Keep up the good work. Regards, -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Joshua D. Drake wrote: Hi, On Mon, 2008-01-07 at 17:12 -0500, Tom Lane wrote: if no serious bugs come up this week, core is thinking of wrapping 8.3.0 at the end of the week, Please give the packagers and other people one more week break between two releases. We all had a busy weekend. We have thousands of people waiting for the 8.3 release. It seems odd to hold up 8.3 because the packagers are too busy. Perhaps we need more packagers or release the source and have the binary packages ready later. Perhaps we need to allow the release candidate to percolate and be tested a bit before we rush to release. If I recall, it is release when it is done not release because lots of people are waiting for it. Right, but you are mixing source code not ready with packagers not ready. They are two different things. I am not saying we are ready to release but if we determine the source code is ready I would like to avoid more delay because the packagers aren't ready. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Devrim G?ND?Z wrote: -- Start of PGP signed section. Hi, On Mon, 2008-01-07 at 19:06 -0500, Bruce Momjian wrote: It seems odd to hold up 8.3 because the packagers are too busy. ... and read my e-mail again. I wrote: Please give the packagers and other people one more week break... I did not say just the packagers. You give almost no time to people to test the release itself -- we are already behind our schedule. One week more would not hurt, IMHO. If we need more time for testing, fine, but I am not in favor of delaying to give packagers more break time. I think I was clear in that. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Tom Lane wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2008-01-07, Tom Lane [EMAIL PROTECTED] wrote: The real question that Josh's report brings up to me is why the heck was there an orphaned temp table? Especially if it was only a toast table and not the linked regular temp table? Something happened there that should not have. The regular table was there too, but the regular table's relfrozenxid was apparently recent, only the toast table's was old: Hmm, that's even more odd, since AFAICS vacuum will always vacuum a toast table immediately after vacuuming the parent. I wonder whether we have a bug somewhere that allows a toast table's relfrozenxid to get initially set to something substantially different from the parent's. Hmm ... that would be strange. Off-the-cuff idea: we introduced code to advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of ALTER TABLE. Perhaps the problem is that we're neglecting to update it for the toast table there. AFAIR I analyzed the cases and they were all handled, but perhaps I forgot something. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: 8.3.0 release schedule (Was:Re: [HACKERS] [BUGS] BUG #3852: Could not create complex aggregate)
Bruce Momjian [EMAIL PROTECTED] writes: I am not saying we are ready to release but if we determine the source code is ready I would like to avoid more delay because the packagers aren't ready. It may be moot anyway. Now that I've caught up on today's email, this thread looks like we have some nasty GIST/GIN bugs: http://archives.postgresql.org/pgsql-general/2008-01/msg00176.php I don't think we can even consider releasing before we've got to the bottom of those reports. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Dynamic Partitioning using Segment Visibility Maps
Andrew Sullivan [EMAIL PROTECTED] writes: On Mon, Jan 07, 2008 at 07:16:35PM +0100, Markus Schiltknecht wrote: Does anything speak against letting the DBA handle partitions as relations? Yes: it doesn't solve the problem I have, which is that I don't want to have to manage a whole bunch of tables. I want one table, and I want to be able to say, That section is closed. That's not your problem, that's the solution you're looking for. You're assuming a particular solution in your problem statement. I posit that the whole point of partitioning is to create an object which serves to represent the semantically meaningful chunks of data. The reason this is useful is precisely because it serves as a short-hand for the DBA describe the data and how it will be used. I think Simon's proposal loses the very feature that makes partitioning useful. The DBA doesn't have a thing to describe, he has to define what parts of the table he's describing for every operation. And if you define a whole new object to name these things I think you'll end up with something that looks a lot like tables. I also don't understand how this proposal deals with the more common use case of unloading and loading data. Normally in partitioned tables we build the data in a side table until the data is all correct then load it as a partition. If you treat it as a lower-level object then I don't see that working. The layout of the new table won't often match the layout of the target partitioned table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(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] ERROR: translation failed from server encoding to wchar_t
[EMAIL PROTECTED] writes: I am using tsearch2 with pgsql 8.2.5 and get the following error when calling to_tsvector : translation failed from server encoding to wchar_t My database is UTF8 encoded and the data sent to to_tsvector comes from a bytea column converted to text with encode(COLUMN, 'escape'). Two likely theories: 1. Your database encoding is UTF-8, but your locale (LC_CTYPE) assumes some other encoding. 2. The encode() is yielding something that isn't valid UTF-8. PG 8.3 contains checks that should complain about both of these scenarios, but IIRC 8.2 does not. 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] Bug: Unreferenced temp tables disables vacuum to update xid
Alvaro Herrera wrote: Tom Lane wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2008-01-07, Tom Lane [EMAIL PROTECTED] wrote: The real question that Josh's report brings up to me is why the heck was there an orphaned temp table? Especially if it was only a toast table and not the linked regular temp table? Something happened there that should not have. The regular table was there too, but the regular table's relfrozenxid was apparently recent, only the toast table's was old: Hmm, that's even more odd, since AFAICS vacuum will always vacuum a toast table immediately after vacuuming the parent. I wonder whether we have a bug somewhere that allows a toast table's relfrozenxid to get initially set to something substantially different from the parent's. Hmm ... that would be strange. Off-the-cuff idea: we introduced code to advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of ALTER TABLE. Perhaps the problem is that we're neglecting to update it for the toast table there. AFAIR I analyzed the cases and they were all handled, but perhaps I forgot something. Just to throw another variable into the mix. This machine was a PITR slave that was pushed into production about two weeks ago. Joshua D. Drake ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Bug: Unreferenced temp tables disables vacuum to update xid
Joshua D. Drake wrote: Alvaro Herrera wrote: Hmm ... that would be strange. Off-the-cuff idea: we introduced code to advance relfrozenxid in CLUSTER, TRUNCATE and table-rewriting forms of ALTER TABLE. Perhaps the problem is that we're neglecting to update it for the toast table there. AFAIR I analyzed the cases and they were all handled, but perhaps I forgot something. Just to throw another variable into the mix. This machine was a PITR slave that was pushed into production about two weeks ago. Ah, right, I bet we have a smoking gun here. -- 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] Dynamic Partitioning using Segment Visibility Maps
Gregory Stark wrote: I also don't understand how this proposal deals with the more common use case of unloading and loading data. Normally in partitioned tables we build the data in a side table until the data is all correct then load it as a partition. If you treat it as a lower-level object then I don't see that working. The layout of the new table won't often match the layout of the target partitioned table. +1 In addition, a similar use case is archival of old partitions as they are not longer (commonly) accessed - perhaps to a different tablespace (or even to backup media). I don't see how dynamic in-table partitioning handles this, and I think it would highly desirable to be able to do these things! best wishes Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementing Sorting Refinements
On Tue, 08 Jan 2008, [EMAIL PROTECTED] wrote: Well, sorry for hijacking... ummm how did I do that? You replied to a post instead of creating a new, unrelated e-mail. It is different. Just try to use threaded mode of your e-mail client and you'll get the idea. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
