[HACKERS] bitfield and gcc
I wonder if somewhere in Postgres source "we" are relying on the GCC "correct behaviour" regarding the read-modify-write of bitfield in structures. Take a read at this https://lwn.net/Articles/478657/ sorry if this was already mentioned. Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 19/09/2011 21:41, PostgreSQL - Hans-Jürgen Schönig wrote: On Sep 19, 2011, at 5:16 PM, Tom Lane wrote: Greg Stark writes: That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I think the real problem would be that we are seldom sorting just the key values. If you have to push the tuples through the GPU too, your savings are going to go up in smoke pretty quickly … i would argument along a similar line. to make GPU code fast it has to be pretty much tailored to do exactly one thing - otherwise you have no chance to get anywhere close to card-bandwith. if you look at "two similar" GPU codes which seem to do the same thing you might easily see that one is 10 times faster than the other - for bloody reason such as memory alignment, memory transaction size or whatever. this opens a bit of a problem: PostgreSQL sorting is so generic and so flexible that i would be really surprised if somebody could come up with a solution which really comes close to what the GPU can do. it would definitely be interesting to see a prototype, however. Thrust Nvidia library provides the same sorting flexibility as postgres does. // generate 32M random numbers on the host thrust::host_vector h_vec(32 << 20); thrust::generate(h_vec.begin(), h_vec.end(), rand); // transfer data to the device thrust::device_vector d_vec = h_vec; // sort data on the device (846M keys per second on GeForce GTX 480) thrust::sort(d_vec.begin(), d_vec.end()); // transfer data back to host thrust::copy(d_vec.begin(), d_vec.end(), h_vec.begin()); as you can see the type to be ordered is template, and the thrust::sort have also a version in where it takes the comparator to use. So compared with pg_qsort thrust::sort gives you the same flexibility. http://docs.thrust.googlecode.com/hg/group__sorting.html Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 19/09/2011 16:36, Greg Smith wrote: On 09/19/2011 10:12 AM, Greg Stark wrote: With the GPU I'm curious to see how well it handles multiple processes contending for resources, it might be a flashy feature that gets lots of attention but might not really be very useful in practice. But it would be very interesting to see. The main problem here is that the sort of hardware commonly used for production database servers doesn't have any serious enough GPU to support CUDA/OpenCL available. The very clear trend now is that all systems other than gaming ones ship with motherboard graphics chipsets more than powerful enough for any task but that. I just checked the 5 most popular configurations of server I see my customers deploy PostgreSQL onto (a mix of Dell and HP units), and you don't get a serious GPU from any of them. Intel's next generation Ivy Bridge chipset, expected for the spring of 2012, is going to add support for OpenCL to the built-in motherboard GPU. We may eventually see that trickle into the server hardware side of things too. The trend is to have server capable of running CUDA providing GPU via external hardware (PCI Express interface with PCI Express switches), look for example at PowerEdge C410x PCIe Expansion Chassis from DELL. I did some experimenst timing the sort done with CUDA and the sort done with pg_qsort: CUDA pg_qsort 33Milion integers: ~ 900 ms, ~ 6000 ms 1Milion integers:~ 21 ms, ~ 162 ms 100k integers: ~ 2 ms, ~ 13 ms CUDA time has already in the copy operations (host->device, device->host). As GPU I was using a C2050, and the CPU doing the pg_qsort was a Intel(R) Xeon(R) CPU X5650 @ 2.67GHz Copy operations and kernel runs (the sort for instance) can run in parallel, so while you are sorting a batch of data, you can copy the next batch in parallel. As you can see the boost is not negligible. Next Nvidia hardware (Keplero family) is PCI Express 3 ready, so expect in the near future the "bottle neck" of the device->host->device copies to have less impact. I strongly believe there is space to provide modern database engine of a way to offload sorts to GPU. > I've never seen a PostgreSQL server capable of running CUDA, and I > don't expect that to change. That sounds like: "I think there is a world market for maybe five computers." - IBM Chairman Thomas Watson, 1943 Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 12/02/2012 13:13, Oleg Bartunov wrote: I'm wondering if CUDA will win in geomentry operations, for example, tesing point <@ complex_polygon I'm not sure if the algorithm you mentioned can be implemented in terms of vector algebra, blas, etc. It's plenty of geometry operations implemented in CUDA out there, my field of CUDA application is not this one so I'm not that much in it. However I can point you to official NVIDIA npp library that provides vector algebra algorithms, and some geometry algorithms as well. http://developer.download.nvidia.com/compute/DevZone/docs/html/CUDALibraries/doc/NPP_Library.pdf (take a look at around page 620). Regards Gaetano Mendola Oleg On Sun, 12 Feb 2012, Gaetano Mendola wrote: On 19/09/2011 16:36, Greg Smith wrote: On 09/19/2011 10:12 AM, Greg Stark wrote: With the GPU I'm curious to see how well it handles multiple processes contending for resources, it might be a flashy feature that gets lots of attention but might not really be very useful in practice. But it would be very interesting to see. The main problem here is that the sort of hardware commonly used for production database servers doesn't have any serious enough GPU to support CUDA/OpenCL available. The very clear trend now is that all systems other than gaming ones ship with motherboard graphics chipsets more than powerful enough for any task but that. I just checked the 5 most popular configurations of server I see my customers deploy PostgreSQL onto (a mix of Dell and HP units), and you don't get a serious GPU from any of them. Intel's next generation Ivy Bridge chipset, expected for the spring of 2012, is going to add support for OpenCL to the built-in motherboard GPU. We may eventually see that trickle into the server hardware side of things too. The trend is to have server capable of running CUDA providing GPU via external hardware (PCI Express interface with PCI Express switches), look for example at PowerEdge C410x PCIe Expansion Chassis from DELL. I did some experimenst timing the sort done with CUDA and the sort done with pg_qsort: CUDA pg_qsort 33Milion integers: ~ 900 ms, ~ 6000 ms 1Milion integers: ~ 21 ms, ~ 162 ms 100k integers: ~ 2 ms, ~ 13 ms CUDA time has already in the copy operations (host->device, device->host). As GPU I was using a C2050, and the CPU doing the pg_qsort was a Intel(R) Xeon(R) CPU X5650 @ 2.67GHz Copy operations and kernel runs (the sort for instance) can run in parallel, so while you are sorting a batch of data, you can copy the next batch in parallel. As you can see the boost is not negligible. Next Nvidia hardware (Keplero family) is PCI Express 3 ready, so expect in the near future the "bottle neck" of the device->host->device copies to have less impact. I strongly believe there is space to provide modern database engine of a way to offload sorts to GPU. I've never seen a PostgreSQL server capable of running CUDA, and I don't expect that to change. That sounds like: "I think there is a world market for maybe five computers." - IBM Chairman Thomas Watson, 1943 Regards Gaetano Mendola Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On Feb 13, 2012 11:39 a.m., "Kohei KaiGai" wrote: > > 2012/2/13 Greg Smith : > > On 02/11/2012 08:14 PM, Gaetano Mendola wrote: > >> > >> The trend is to have server capable of running CUDA providing GPU via > >> external hardware (PCI Express interface with PCI Express switches), look > >> for example at PowerEdge C410x PCIe Expansion Chassis from DELL. > > > > > > The C410X adds 16 PCIe slots to a server, housed inside a separate 3U > > enclosure. That's a completely sensible purchase if your goal is to build a > > computing cluster, where a lot of work is handed off to a set of GPUs. I > > think that's even less likely to be a cost-effective option for a database > > server. Adding a single dedicated GPU installed in a server to accelerate > > sorting is something that might be justifiable, based on your benchmarks. > > This is a much more expensive option than that though. Details at > > http://www.dell.com/us/enterprise/p/poweredge-c410x/pd for anyone who wants > > to see just how big this external box is. > > > > > >> I did some experimenst timing the sort done with CUDA and the sort done > >> with pg_qsort: > >> CUDA pg_qsort > >> 33Milion integers: ~ 900 ms, ~ 6000 ms > >> 1Milion integers:~ 21 ms, ~ 162 ms > >> 100k integers: ~ 2 ms, ~ 13 ms > >> CUDA time has already in the copy operations (host->device, device->host). > >> As GPU I was using a C2050, and the CPU doing the pg_qsort was a Intel(R) > >> Xeon(R) CPU X5650 @ 2.67GHz > > > > > > That's really interesting, and the X5650 is by no means a slow CPU. So this > > benchmark is providing a lot of CPU power yet still seeing over a 6X speedup > > in sort times. It sounds like the PCI Express bus has gotten fast enough > > that the time to hand data over and get it back again can easily be > > justified for medium to large sized sorts. > > > > It would be helpful to take this patch and confirm whether it scales when > > using in parallel. Easiest way to do that would be to use the pgbench "-f" > > feature, which allows running an arbitrary number of some query at once. > > Seeing whether this acceleration continued to hold as the number of clients > > increases is a useful data point. > > > > Is it possible for you to break down where the time is being spent? For > > example, how much of this time is consumed in the GPU itself, compared to > > time spent transferring data between CPU and GPU? I'm also curious where > > the bottleneck is at with this approach. If it's the speed of the PCI-E bus > > for smaller data sets, adding more GPUs may never be practical. If the bus > > can handle quite a few of these at once before it saturates, it might be > > possible to overload a single GPU. That seems like it would be really hard > > to reach for database sorting though; I can't really defend justify my gut > > feel for that being true though. > > > > > >> > I've never seen a PostgreSQL server capable of running CUDA, and I > >> > don't expect that to change. > >> > >> That sounds like: > >> > >> "I think there is a world market for maybe five computers." > >> - IBM Chairman Thomas Watson, 1943 > > > > > > Yes, and "640K will be enough for everyone", ha ha. (Having said the 640K > > thing is flat out denied by Gates, BTW, and no one has come up with proof > > otherwise). > > > > I think you've made an interesting case for this sort of acceleration now > > being useful for systems doing what's typically considered a data warehouse > > task. I regularly see servers waiting for far more than 13M integers to > > sort. And I am seeing a clear trend toward providing more PCI-E slots in > > servers now. Dell's R810 is the most popular single server model my > > customers have deployed in the last year, and it has 5 X8 slots in it. It's > > rare all 5 of those are filled. As long as a dedicated GPU works fine when > > dropped to X8 speeds, I know a fair number of systems where one of those > > could be added now. > > > > There's another data point in your favor I didn't notice before your last > > e-mail. Amazon has a "Cluster GPU Quadruple Extra Large" node type that > > runs with NVIDIA Tesla hardware. That means the installed base of people > > who could consider CUDA is higher than I expected. To demonstrate how much &g
Re: [HACKERS] CUDA Sorting
On Feb 13, 2012 7:49 p.m., "Greg Stark" wrote: > > I don't think we should be looking at either CUDA or OpenCL directly. > We should be looking for a generic library that can target either and > is well maintained and actively developed. Any GPU code we write > ourselves would rapidly be overtaken by changes in the hardware and > innovations in parallel algorithms. If we find a library that provides > a sorting api and adapt our code to use it then we'll get the benefits > of any new hardware feature as the library adds support for them. To sort integer I used the Thrust Nvidia library.
Re: [HACKERS] CUDA Sorting
On 13/02/2012 08:26, Greg Smith wrote: On 02/11/2012 08:14 PM, Gaetano Mendola wrote: The trend is to have server capable of running CUDA providing GPU via external hardware (PCI Express interface with PCI Express switches), look for example at PowerEdge C410x PCIe Expansion Chassis from DELL. The C410X adds 16 PCIe slots to a server, housed inside a separate 3U enclosure. That's a completely sensible purchase if your goal is to build a computing cluster, where a lot of work is handed off to a set of GPUs. I think that's even less likely to be a cost-effective option for a database server. Adding a single dedicated GPU installed in a server to accelerate sorting is something that might be justifiable, based on your benchmarks. This is a much more expensive option than that though. Details at http://www.dell.com/us/enterprise/p/poweredge-c410x/pd for anyone who wants to see just how big this external box is. I did some experimenst timing the sort done with CUDA and the sort done with pg_qsort: CUDA pg_qsort 33Milion integers: ~ 900 ms, ~ 6000 ms 1Milion integers: ~ 21 ms, ~ 162 ms 100k integers: ~ 2 ms, ~ 13 ms CUDA time has already in the copy operations (host->device, device->host). As GPU I was using a C2050, and the CPU doing the pg_qsort was a Intel(R) Xeon(R) CPU X5650 @ 2.67GHz That's really interesting, and the X5650 is by no means a slow CPU. So this benchmark is providing a lot of CPU power yet still seeing over a 6X speedup in sort times. It sounds like the PCI Express bus has gotten fast enough that the time to hand data over and get it back again can easily be justified for medium to large sized sorts. It would be helpful to take this patch and confirm whether it scales when using in parallel. Easiest way to do that would be to use the pgbench "-f" feature, which allows running an arbitrary number of some query at once. Seeing whether this acceleration continued to hold as the number of clients increases is a useful data point. Is it possible for you to break down where the time is being spent? For example, how much of this time is consumed in the GPU itself, compared to time spent transferring data between CPU and GPU? I'm also curious where the bottleneck is at with this approach. If it's the speed of the PCI-E bus for smaller data sets, adding more GPUs may never be practical. If the bus can handle quite a few of these at once before it saturates, it might be possible to overload a single GPU. That seems like it would be really hard to reach for database sorting though; I can't really defend justify my gut feel for that being true though. There you go (times are in ms): Size H->D SORT D->H TOTAL 64 0.209824 0.479392 0.013856 0.703072 128 0.098144 0.41744 0.01312 0.528704 256 0.096832 0.420352 0.013696 0.53088 512 0.097568 0.3952 0.014464 0.507232 1024 0.09872 0.396608 0.014624 0.509952 2048 0.101344 0.56224 0.016896 0.68048 4096 0.106176 0.562976 0.02016 0.689312 8192 0.116512 0.571264 0.02672 0.714496 163840.136096 0.587584 0.040192 0.763872 327680.179296 0.658112 0.066304 0.903712 655360.212352 0.84816 0.118016 1.178528 131072 0.317056 1.1465 0.22784 1.691396 262144 0.529376 1.82237 0.42512 2.776866 524288 0.724032 2.39834 0.64576 3.768132 1048576 1.11162 3.51978 1.12176 5.75316 2097152 1.95939 5.93434 2.06992 9.96365 4194304 3.76192 10.6011 4.10614 18.46916 8388608 7.16845 19.9245 7.93741 35.03036 16777216 13.8693 38.7413 15.4073 68.0179 33554432 27.3017 75.6418 30.6646 133.6081 67108864 54.2171 151.192 60.327 265.7361 pg_sort 64 0.01 128 0.01 256 0.021000 512 0.128000 1024 0.092000 2048 0.196000 4096 0.415000 8192 0.883000 163841.881000 327683.96 655368.432000 131072 17.951000 262144 37.14 524288 78.32 1048576163.276000 2097152339.118000 4194304693.223000 8388608 1423.142000 16777216 2891.218000 33554432 5910.851000 67108864 11980.93 As you can notice the times with CUDA are lower than the timing I have reported on my previous post because the server was doing something else in mean while, I have repeated those benchmarks with server completely unused. And this is the boost as in pg_sort/cuda : 64 0.0142232943 128 0.018914175 256 0.039556962 512 0.2070058671 1024 0.1804091365 2048 0.2880319774 4096 0.6078524674 8192 1.2372357578 163842.4637635625 327684.4106972133 655367.1742037525 131072 10.5090706139 262144 13.3719091955 524288 20.5834084369 1048576 28.2516043357 2097152 33.9618513296 4194304 37.5247168794 8388608 40.5135716561 16777216 42.4743633661 33554432 44.2394809896 67108864 45.1499777411 > I've never seen a PostgreSQL server capable of running CUDA, and I > don't expect
Re: [HACKERS] CUDA Sorting
On 13/02/2012 19:48, Greg Stark wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be overtaken by changes in the hardware and innovations in parallel algorithms. If we find a library that provides a sorting api and adapt our code to use it then we'll get the benefits of any new hardware feature as the library adds support for them. I think one option is to make the sort function pluggable with a shared library/dll. I see several benefits from this: - It could be in the interest of the hardware vendor to provide the most powerful sort implementation (I'm sure for example that TBB sort implementation is faster that pg_sort) - It can permit people to "play" with it without being deep involved in pg development and stuffs. - It can relieve the postgres core group the choose about the right language/tool/implementation to use. - Also for people not willing (or not able for the matter) to upgrade postgres engine to change instead the sort function upon an hardware upgrade. Of course if this happens postgres engine has to make some sort of sanity check (that the function for example actually sorts) before to "thrust" the plugged sort. The engine can even have multiple sort implementation available and use the most proficient one (imagine some sorts acts better on a certain range value or on certain element size). Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 13/02/2012 19:48, Greg Stark wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be overtaken by changes in the hardware and innovations in parallel algorithms. If we find a library that provides a sorting api and adapt our code to use it then we'll get the benefits of any new hardware feature as the library adds support for them. I think one option is to make the sort function plugable with a shared library/dll. I see several benefits from this: - It could be in the interest of the hardware vendor to provide the most powerful sort implementation (I'm sure for example that TBB sort implementation is faster that pg_sort) - It can permit people to "play" with it without being deep involved in pg development and stuffs. - It can relieve the postgres core group the choose about the right language/tool/implementation to use. - Also for people not willing (or not able for the matter) to upgrade postgres engine to change instead the sort function upon an hardware upgrade. Of course if this happens postgres engine has to make some sort of sanity check (that the function for example actually sorts) before to "thrust" the plugged sort. The engine can even have multiple sort implementation available and use the most proficient one (imagine some sorts acts better on a certain range value or on certain element size). Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 15/02/2012 23:11, Peter Geoghegan wrote: On 15 February 2012 20:00, Gaetano Mendola wrote: On 13/02/2012 19:48, Greg Stark wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be overtaken by changes in the hardware and innovations in parallel algorithms. If we find a library that provides a sorting api and adapt our code to use it then we'll get the benefits of any new hardware feature as the library adds support for them. I think one option is to make the sort function pluggable with a shared library/dll. I see several benefits from this: - It could be in the interest of the hardware vendor to provide the most powerful sort implementation (I'm sure for example that TBB sort implementation is faster that pg_sort) - It can permit people to "play" with it without being deep involved in pg development and stuffs. Sorry, but I find it really hard to believe that the non-availability of pluggable sorting is what's holding people back here. Some vanguard needs to go and prove the idea by building a rough prototype before we can even really comment on what an API should look like. For example, I am given to understand that GPUs generally sort using radix sort - resolving the impedance mismatch that prevents someone from using a non-comparison based sort sure sounds like a lot of work for an entirely speculative reward. AFAIK thrust library uses the radix sort if the keys you are sorting are POD data comparable with a "<" operator otherwise it does the comparison based sort using the operator provided. http://docs.thrust.googlecode.com/hg/modules.html I'm not saying that the non-availability of pluggable sort completely holds people back, I'm saying that it will simplify the process now and int the future, of course that's my opinion. Someone who cannot understand tuplesort, which is not all that complicated, has no business trying to build GPU sorting into Postgres. That sounds a bit harsh. I'm one of those indeed, I haven't look in the details not having enough time for it. At work we do GPU computing (not the sort type stuff) and given the fact I'm a Postgres enthusiast I asked my self: "my server is able to sort around 500 milions integer per seconds, if postgres was able to do that as well it would be very nice". What I have to say? Sorry for my thoughts. I had a patch committed a few hours ago that almost included the capability of assigning an alternative sorting function, but only one with the exact same signature as my variant of qsort_arg. pg_qsort isn't used to sort tuples at all, by the way. Then I did look in the wrong direction. Thank you for point that out. Threading building blocks is not going to form the basis of any novel sorting implementation, because comparators in general are not thread safe, and it isn't available on all the platforms we support, and because of how longjmp interacts with C++ stack unwinding and so on and so on. Now, you could introduce some kind of parallelism into sorting integers and floats, but that's an awful lot of work for a marginal reward. The TBB was just example that did come in my mind. What do you mean with you could introduce some kind of parallelism? As far as I know any algorithm using the divide and conquer can be parallelized. Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CUDA Sorting
On 15/02/2012 23:11, Peter Geoghegan wrote: On 15 February 2012 20:00, Gaetano Mendola wrote: On 13/02/2012 19:48, Greg Stark wrote: I don't think we should be looking at either CUDA or OpenCL directly. We should be looking for a generic library that can target either and is well maintained and actively developed. Any GPU code we write ourselves would rapidly be overtaken by changes in the hardware and innovations in parallel algorithms. If we find a library that provides a sorting api and adapt our code to use it then we'll get the benefits of any new hardware feature as the library adds support for them. I think one option is to make the sort function pluggable with a shared library/dll. I see several benefits from this: - It could be in the interest of the hardware vendor to provide the most powerful sort implementation (I'm sure for example that TBB sort implementation is faster that pg_sort) - It can permit people to "play" with it without being deep involved in pg development and stuffs. Sorry, but I find it really hard to believe that the non-availability of pluggable sorting is what's holding people back here. Some vanguard needs to go and prove the idea by building a rough prototype before we can even really comment on what an API should look like. For example, I am given to understand that GPUs generally sort using radix sort - resolving the impedance mismatch that prevents someone from using a non-comparison based sort sure sounds like a lot of work for an entirely speculative reward. AFAIK thrust library uses the radix sort if the keys you are sorting are POD data comparable with a "<" operator otherwise it does the comparison based sort using the operator provided. http://docs.thrust.googlecode.com/hg/modules.html I'm not saying that the non-availability of pluggable sort completely holds people back, I'm saying that it will simplify the process now and int the future, of course that's my opinion. Someone who cannot understand tuplesort, which is not all that complicated, has no business trying to build GPU sorting into Postgres. That sounds a bit harsh. I'm one of those indeed, I haven't look in the details not having enough time for it. At work we do GPU computing (not the sort type stuff) and given the fact I'm a Postgres enthusiast I asked my self: "my server is able to sort around 500 milions integer per seconds, if postgres was able to do that as well it would be very nice". What I have to say? Sorry for my thoughts. I had a patch committed a few hours ago that almost included the capability of assigning an alternative sorting function, but only one with the exact same signature as my variant of qsort_arg. pg_qsort isn't used to sort tuples at all, by the way. Then I did look in the wrong direction. Thank you for point that out. Threading building blocks is not going to form the basis of any novel sorting implementation, because comparators in general are not thread safe, and it isn't available on all the platforms we support, and because of how longjmp interacts with C++ stack unwinding and so on and so on. Now, you could introduce some kind of parallelism into sorting integers and floats, but that's an awful lot of work for a marginal reward. The TBB was just example that did come in my mind. What do you mean with you could introduce some kind of parallelism? As far as I know any algorithm using the divide and conquer can be parallelized. Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] elog and MemoryContextSwitchTo
Hi all,
Is the following code well formed?
oldContext = MemoryContextSwitchTo(newContext);
if (something_bad) {
elog(ERROR, ...);
}
...
MemoryContextSwitchTo(oldContext);
or do I have to ripristinate the oldContext before to issue the elog ?
Regards
Gaetano Mendola
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] C++ compiler
I have read Peter Eisentraut blog entry about "Moving to C++", I full agree with him about what he wrote. Is there any interest or work in progress in making the entire Postgresql code base compilable by a C++ compiler? Regards Gaetano Mendola -- cpp-today.blogspot.com
[HACKERS] Suspicious check (src/backend/access/gin/gindatapage.c)
At line 650 I can read: if ((leaf->lsize - segsize) - (leaf->lsize - segsize) < BLCKSZ / 4) break; I believe one of the two should be leaf->rsize -- cpp-today.blogspot.com
[HACKERS] pg_tables bug?
I'm playing around with tablespace (postgresq 9.4) and I found out what I believe is a bug in pg_tables. Basically if you create a database in a table space X and then you create a table on the database the table is created correctly on the tablespace X ( I did a check on the filesystem) however if you do a select on pg_tables the column tablespace for that table is empty and even worst if you dump the DB there is no reporting about the the database or table being on that tablespace. Even \d doesn't report that the table is in the tablespace X. Regards
Re: [HACKERS] pg_tables bug?
>From documentation about "CREATE DATABASE name WITH TABLESAPCE = tablespace_name": tablespace_name The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database's tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information. I'm sure that my tables are created in the name space but those are not reported either in pg_tables, either in pg_dump or by \d. Look as this: kalman@kalman-VirtualBox:~$ mkdir tablespace_XXX kalman@kalman-VirtualBox:~$ sudo chown postgres.postgres tablespace_XXX kalman@kalman-VirtualBox:~$ psql template1 psql (9.4.5) Type "help" for help. template1=# create tablespace XXX LOCATION '/home/kalman/tablespace_XXX'; CREATE TABLESPACE template1=# create database db_test with tablespace = XXX; CREATE DATABASE template1=# \q kalman@kalman-VirtualBox:~$ psql db_test psql (9.4.5) Type "help" for help. db_test=# create table t_test ( a integer, b numeric); CREATE TABLE db_test=# \d+ t_test Table "public.t_test" Column | Type | Modifiers | Storage | Stats target | Description +-+---+-+--+- a | integer | | plain | | b | numeric | | main| | db_test=# select * from pg_tables where tablename = 't_test'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers +---++++--+- public | t_test| kalman || f | f| f (1 row) db_test=# select oid from pg_database where datname = 'db_test'; oid --- 80335 db_test=# select relfilenode from pg_class where relname = 't_test'; relfilenode - 80336 (1 row) Unfortunately contrary to what postgres is showing me the table test is in /home/kalman/tablespace_: root@kalman-VirtualBox:~# file /home/kalman/tablespace_XXX/PG_9.4_201409291/80335/80336 /home/kalman/tablespace_XXX/PG_9.4_201409291/80335/80336: empty as you can see the CREATE DATABASE documentation is honored but the system is failing to give me the right tablespace location for that table. Regards On Thu, 17 Dec 2015 at 15:36 Tom Lane wrote: > Gaetano Mendola writes: > > I'm playing around with tablespace (postgresq 9.4) and I found out what I > > believe is a bug in pg_tables. > > Basically if you create a database in a table space X and then you > create a > > table on the database the table is created correctly on the tablespace X > ( > > I did a check on the filesystem) however if you do a select on pg_tables > > the column tablespace for that table is empty and even worst if you dump > > the DB there is no reporting about the the database or table being on > that > > tablespace. > > Even \d doesn't report that the table is in the tablespace X. > > An empty entry in that column means that the table is in the default > tablespace for the database. Which it sounds like is what you have > here. I think it's operating as designed, though you might quibble > with the decision that showing default tablespaces explicitly would > have been clutter. > > regards, tom lane >
Re: [HACKERS] pg_tables bug?
On Thu, 17 Dec 2015 at 15:36 Tom Lane wrote: > Gaetano Mendola writes: > > I'm playing around with tablespace (postgresq 9.4) and I found out what I > > believe is a bug in pg_tables. > > Basically if you create a database in a table space X and then you > create a > > table on the database the table is created correctly on the tablespace X > ( > > I did a check on the filesystem) however if you do a select on pg_tables > > the column tablespace for that table is empty and even worst if you dump > > the DB there is no reporting about the the database or table being on > that > > tablespace. > > Even \d doesn't report that the table is in the tablespace X. > > An empty entry in that column means that the table is in the default > tablespace for the database. Which it sounds like is what you have > here. I think it's operating as designed, though you might quibble > with the decision that showing default tablespaces explicitly would > have been clutter. > Now it's clear thank you.
Re: [HACKERS] pg_tables bug?
On Sat, Dec 19, 2015, 01:50 Andrew Dunstan wrote: > > > > > On 12/18/2015 05:18 PM, Gaetano Mendola wrote: > > From documentation about "CREATE DATABASE name WITH TABLESAPCE = > > tablespace_name": > > > > tablespace_name > > The name of the tablespace that will be associated with the new > > database, or DEFAULT to > > use the template database's tablespace. This tablespace will be the > > default tablespace used > > for objects created in this database. See CREATE TABLESPACE for more > > information. > > > > I'm sure that my tables are created in the name space but those are > > not reported either in > > pg_tables, either in pg_dump or by \d. > > 1. Please don't top-post on the PostgreSQL lists. See > <http://idallen.com/topposting.html> > > 2. The system is working as designed and as documented - see the > comments in the docs on pg_tables. If nothing is shown for the table's > tablespace then it will be in the default tablespace for the database. > That's what you're seeing. You appear to be assuming incorrectly that it > means that the table will be in the system's default tablespace. > I did a reply using a not correctly setup client sorry for it. I'm not new to this list. I understood now how it works. Having many database and many tablespace is a nightmare this way. I will make my own view to fix the annoyance. >
[HACKERS] GPU and Database
May be someone of you is interested in this ADBIS workshop on GPUs in Databases http://gid2012.cs.put.poznan.pl/ Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hackers newsgroup hacked ?
Reading this mailing list via newsgroup (news.postgresql.org port 119) I can see that last "legitimate" message is from 29 August since then only "RUSSIAN" posts are present. Regards Gaetano Mendola -- cpp-today.blogspot.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Possible pointer dereference
I'm playing with a static analyzer and it's giving out some real error analyzing postgresql code base like the following one src/backend/access/transam/commit_ts.c return *ts != 0 // line 321 but a few line up (line 315) ts is checked for null, so either is not needed to check for null or *ts can lead to a null pointer dereference. Same happens a few line later lines 333 and 339 Regards Gaetano
[HACKERS] useless assignment pointer argument
Hi, in the following spots: src/backend/commands/explain.c:1692 src/backend/commands/explain.c:1874 src/backend/commands/explain.c:1986 there is the following assignment: ancestors = list_delete_first(ancestors); but it has no effect at all being that a function parameter and not used anymore after the assignment itself.
Re: [HACKERS] Possible pointer dereference
While at it the assert(cnfa != NULL && cnfa->nstates != 0); at src/backend/regex/rege_dfa.c:282 is issued too late indeed at line 278 and 279 cnfa was already dereferenced. Same for assert(t != NULL) in src/backend/regex/regexec.c:821 is issued way too late. On Thu, 28 May 2015 at 15:59 Tom Lane wrote: > Robert Haas writes: > > On Wed, May 27, 2015 at 8:57 PM, Haribabu Kommi > > wrote: > >> By correcting the following way will solve the problem. > >> return ts ? (*ts != 0) : false; instead of retun *ts != 0; > >> Attached a patch for it. > > > If the only caller always passes a valid pointer, there's no point in > > adding this check. We have many functions in our source base that > > assume that the caller will pass a valid pointer, and changing them > > all would make the code bigger, harder to read, and possibly slower, > > without any real benefit. > > Well, we should either install something like Haribabu's patch, or else > remove the existing tests in the function that allow "ts" to be NULL. > And the function's API contract comment needs to be clarified in either > case; the real bug here is lack of a specification. > > I don't particularly have an opinion on whether it's valuable to allow > this function to be called without receiving a timestamp back. Perhaps > the authors of the patch can comment on that. > > regards, tom lane >
Re: [HACKERS] useless assignment pointer argument
If the compiler is good the assignment is elided indeed, that's not what I meant to point out. On Thu, 28 May 2015 at 22:17 Andres Freund wrote: > On 2015-05-28 20:14:33 +, Gaetano Mendola wrote: > > src/backend/commands/explain.c:1692 > > src/backend/commands/explain.c:1874 > > src/backend/commands/explain.c:1986 > > > > there is the following assignment: > > > >ancestors = list_delete_first(ancestors); > > > > but it has no effect at all being that a function parameter and not used > > anymore after the assignment itself. > > So? It costs little to nothing, and it'll make it much less likely that > a stale version of 'ancestors' is used when the code is expanded. > > Greetings, > > Andres Freund >
[HACKERS] Stats collector on rampage (8.2.3)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
it seems that the stats collector on my box is using more CPU than
it did in the past.
This is what I'm observing:
CPU usage for the stat process: 25% flat
$ psql -c "select version()"
version
-
---
PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-3)
(1 row)
$ strace -tt -p 10773
[...]
09:47:37.867655 write(3,
"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
09:47:37.867738 write(3,
"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 823) = 823
09:47:37.867820 close(3)= 0
09:47:37.867862 munmap(0xb7ced000, 4096) = 0
09:47:37.867906 rename("global/pgstat.tmp", "global/pgstat.stat") = 0
09:47:37.868188 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868245 recv(7,
"\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347"..., 1000, 0) = 976
09:47:37.868317 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 500}},
NULL) = 0
09:47:37.868372 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868428 recv(7,
"\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0g\n\0"..., 1000, 0) = 976
09:47:37.868501 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868559 recv(7,
"\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\0\0\0\0i\n\0"..., 1000, 0) = 976
09:47:37.868629 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868687 recv(7,
"\1\0\0\0\4\3\0\0\rg\0\0\v\0\0\0\0\0\0\0\0\0\0\0^\n\0\0"..., 1000, 0) = 772
09:47:37.868757 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868815 recv(7,
"\1\0\0\0\240\0\0\0\0\0\0\0\2\0\0\0\0\0\0\0\0\0\0\0\354"..., 1000, 0) = 160
09:47:37.868886 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.868944 recv(7,
"\1\0\0\0\240\0\0\0\rg\0\0\2\0\0\0\1\0\0\0\0\0\0\0<\n\0"..., 1000, 0) = 160
09:47:37.869012 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869070 recv(7,
"\1\0\0\0l\1\0\0\0\0\0\0\5\0\0\0\0\0\0\0\0\0\0\0\354\4\0"..., 1000, 0) = 364
09:47:37.869141 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869198 recv(7,
"\1\0\0\0\300\2\0\0\rg\0\0\n\0\0\0\1\0\0\0\0\0\0\0007\n"..., 1000, 0) = 704
09:47:37.869267 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869329 recv(7,
"\1\0\0\0(\1\0\0\rg\0\0\4\0\0\0\1\0\0\0\0\0\0\0/\n\0\0\0"..., 1000, 0) = 296
09:47:37.869398 poll([{fd=7, events=POLLIN|POLLERR, revents=POLLIN}], 1, 2000)
= 1
09:47:37.869456 recv(7,
"\1\0\0\0\320\3\0\0\rg\0\0\16\0\0\0\0\0\0\0\1\0\0\0\347"..., 1000, 0) = 976
09:47:37.869524 --- SIGALRM (Alarm clock) @ 0 (0) ---
09:47:37.869575 sigreturn() = ? (mask now [])
09:47:37.869659 getppid() = 10768
09:47:37.869702 open("global/pgstat.tmp", O_WRONLY|O_CREAT|O_TRUNC|O_LARGEFILE,
0666) = 3
09:47:37.869775 fstat64(3, {st_mode=S_IFREG|0600, st_size=0, ...}) = 0
09:47:37.869871 mmap2(NULL, 4096, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0xb7ced000
09:47:37.869928 write(3,
"\226\274\245\1D\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
09:47:37.870252 write(3,
";\n\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
[...]
and doing the statistics on the system calls:
$ time strace -c -p 10773
Process 10773 attached - interrupt to quit
Process 10773 detached
% time seconds usecs/call callserrors syscall
- -- --- --- - -
62.704.980721 16307851 write
25.722.043299 156 13058 3039 poll
9.610.763046 248 3078 rename
0.640.050992 17 3079 open
0.490.038819 4 10019 recv
0.260.020469 7 3078 munmap
0.130.010344 3 3078 close
0.120.009425 3 3079 mmap2
0.110.008353 3 3079 setitimer
0.090.007114 2 3079 3039 sigreturn
0.070.005923 2 3079 fstat64
0.060.004734 2 3079 getppid
- -- --- --- - -
100.007.943239358636 6078 total
real0m16.313s
user0m1.428s
sys 0m3.802s
so instead of 32 or such rename it did the rename 3K times.
To solve the problem is it possible to kill that process? (will it be
respawned?)
Regards
Gaetano M
Re: [HACKERS] Stats collector on rampage (8.2.3)
hubert depesz lubaczewski wrote: > > On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: >> >> it seems that the stats collector on my box is using more CPU than >> >> it did in the past. > > > > it's well known bug, and it was fixed in 8.2.4: > > http://www.postgresql.org/docs/current/interactive/release-8-2-4.html > > ... > > Prevent the statistics collector from writing to disk too frequently > > (Tom) > > ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. Regards Gaetano Mendola ---(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] Stats collector on rampage (8.2.3)
hubert depesz lubaczewski wrote: > On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: >> it seems that the stats collector on my box is using more CPU than >> it did in the past. > > it's well known bug, and it was fixed in 8.2.4: > http://www.postgresql.org/docs/current/interactive/release-8-2-4.html > ... > Prevent the statistics collector from writing to disk too frequently > (Tom) > ... I saw that, upgrading the DB at this very moment is not doable, killing that process will the postmaster respawn another one? BTW I discover that it was triggered by the time change due the daylight saving. Regards Gaetano Mendola ---(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] Stats collector on rampage (8.2.3)
Andrew Dunstan wrote: > > > Gaetano Mendola wrote: >> hubert depesz lubaczewski wrote: >> >>>> On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: >>>> >>>>>> it seems that the stats collector on my box is using more CPU than >>>>>> it did in the past. >>>>>> >>>> it's well known bug, and it was fixed in 8.2.4: >>>> http://www.postgresql.org/docs/current/interactive/release-8-2-4.html >>>> ... >>>> Prevent the statistics collector from writing to disk too frequently >>>> (Tom) >>>> ... >>>> >> >> I saw that, upgrading the DB at this very moment is not doable, killing >> that process will the postmaster respawn another one? BTW I discover that >> it was triggered by the time change due the daylight saving. >> >> >> > > you do realize that this upgrade wouldn't require a dump/restore, don't > you? It would be close to instantaneous. Sure I do, for me it's even easier than that, I use DRBD so I can just shut down one node upgrade it and then upgrade the other one; however upgrade the DB means for me "touch" an entire satellite trasmission platform, I have to schedule a platform maintenance for that... Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stats collector on rampage (8.2.3)
Magnus Hagander wrote: > Gaetano Mendola wrote: >> hubert depesz lubaczewski wrote: >>> On Mon, Oct 29, 2007 at 09:52:24AM +0100, Gaetano Mendola wrote: >>>> it seems that the stats collector on my box is using more CPU than >>>> it did in the past. >>> it's well known bug, and it was fixed in 8.2.4: >>> http://www.postgresql.org/docs/current/interactive/release-8-2-4.html >>> ... >>> Prevent the statistics collector from writing to disk too frequently >>> (Tom) >>> ... >> I saw that, upgrading the DB at this very moment is not doable, killing >> that process will the postmaster respawn another one? BTW I discover that >> it was triggered by the time change due the daylight saving. > > IIRC, it will. You need to change postgresql.conf and disable the stats > collector. If you do that, it won't be started. > > Shouldn't be trigged by DST. The high cpu usage started at that time, may be that is another problem of some applications stuck on it. I will investigate further. Gaetano ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm using 8.2.6 and I'm observing a trange behaviour using offset and limits. This are the two queries that are puzzling me: explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 ; QUERY PLAN - Limit (cost=175044.75..175071.04 rows=10518 width=90) -> Sort (cost=175044.75..175071.04 rows=10518 width=90) Sort Key: c.nctr, c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) Hash Cond: (c.id = dt.card_id) -> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90) Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0) Index Cond: (ecp = 18) -> Hash (cost=22743.45..22743.45 rows=171593 width=8) -> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) Recheck Cond: (_from <= 1550) Filter: (_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0) Index Cond: (_from <= 1550) explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN - Limit (cost=0.00..2125.12 rows=5 width=90) -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) Filter: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) using the limit I have an execution time of minutes vs a some seconds. What am I missing here ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHov3I7UpzwH2SGd4RApR+AJ0dG/+0MoB3PMD1kRgQt0BisHwQBACgzVwC BN/SBWrvVxVE9eBLK0C1Pnw= =9Ucp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn van Oosterhout wrote: > On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hi all, >> I'm using 8.2.6 and I'm observing a trange behaviour using >> offset and limits. > > Please post EXPLAIN ANALYZE output so we can see what's actually taking > the time. The analyze is still running (I launched it 30 mins ago), I'll post it as soon I have it. Disabling the nested_loop ( set enable_nestloop = false ) the query with the limit has now the same execution time without the limit. I don't get why a limit is going to change the query plan and most of all decreasing the performances. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHowXA7UpzwH2SGd4RAomqAJ409579Jk7d5FYWf92PjOYDRxWNIQCggg1w 1WJcVmn2g1MASBGh9OtCQ0Q= =h2Z6 -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] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory Stark wrote: > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > >> I don't get why a limit is going to change the query plan and most of all >> decreasing >> the performances. > > Until we see the explain analyze it won't be clear what exactly is going on. > But in theory a LIMIT can definitely change the plan because the planner knows > it won't need to generate all the rows to satisfy the LIMIT. > > In the plans you gave note that the plan for the unlimited query has a Sort so > it has to produce all the records every time. The second query produces the > records in order so if the LIMIT is satisfied quickly then it can save a lot > of work. > > It's evidently guessing wrong about the limit being satisfied early. The > non-indexed restrictions might be pruning out a lot more records than the > planner expects. Or possibly the table is just full of dead records. > Here the analyze result: explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN --- Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1) -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1) -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual time=3399892.632..3399896.773 rows=50 loops=1) Filter: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) Total runtime: 3399960.277 ms explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 ; QUERY PLAN - Limit (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.138..2435.633 rows=3298 loops=1) -> Sort (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.134..2428.812 rows=3298 loops=1) Sort Key: c.nctr, c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) (actual time=797.540..2382.900 rows=3298 loops=1) Hash Cond: (c.id = dt.card_id) -> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90) (actual time=70.212..1507.429 rows=97883 loops=1) Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0) (actual time=53.340..53.340 rows=97883 loops=1) Index Cond: (ecp = 18) -> Hash (cost=22743.45..22743.45 rows=171593 width=8) (actual time=726.597..726.597 rows=89277 loops=1) -> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) (actual time=86.181..593.275 rows=89277 loops=1) Recheck Cond: (_from <= 1550) Filter: (_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0) (actual time=80.863..80.863 rows=201177 loops=1) Index Cond: (_from <= 1550) Total runtime: 2440.396 ms Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHoytQ7UpzwH2SGd4RAujPAKDkM53sirwNFa7jH/Q3R2y1/QAcKQCgn9VH pUSwTkR3c963BoCbNwG+W6Y= =s7Vr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Greg Stark" <[EMAIL PROTECTED]> writes: >>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 >>> rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) >>> Index Cond: (dt.card_id = c.id) >>> Filter: ((_to >= 1500) AND (_from <= 1550)) >>> Total runtime: 3399960.277 ms > >> Also, are 1500 and 1550 user-supplied parameters or are they part of a small >> set of possible values? You could consider having a partial index on >> "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have >> to match exactly as long as they include all the records the query needs. > > That side of the join isn't where the problem is, though. > > If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel > would probably fix the performance issue very nicely. > As always you are right, creating the index "ivan" btree (ecp, nctr, nctn, ncts, rvel) that query with the limit responds now in the blink of an eye: > explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, > pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN - --- Limit (cost=0.00..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 loops=1) -> Nested Loop (cost=0.00..778392.80 rows=10518 width=90) (actual time=0.099..0.594 rows=5 loops=1) -> Index Scan using ivan on t_oa_2_00_card c (cost=0.00..235770.34 rows=101872 width=90) (actual time=0.024..0.134 rows=50 loops=1) Index Cond: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=50) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) Total runtime: 0.700 ms (8 rows) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHo1SB7UpzwH2SGd4RAhTeAJ0WL49jjUgCWSrNopV/8L+rbOLaEgCfTDlh crAHZYxxTYz6VqTDggqW7x0= =dKey -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Base64 decode/encode performance
Hi, I have been experimenting with some base64 encoding/decoding implementation. I find out that the one at http://code.google.com/p/stringencoders is the best obtaining a 1.3 speedup vs the postgres one. Do you think is worth to submit a patch that replaces the postgres base64 implementation with this one? Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] /etc/init.d/postgresql status error
I have just installed the rpm found at:
http://www.postgresql.org/ftp/binary/v8.2.7/linux/rpms/redhat/rhel-4-i386/
and the status option generates an error:
$ /etc/init.d/postgresql status
pidof: invalid options on command line!
pidof: invalid options on command line!
-p is stopped
I was able to fix it in the following mode:
status)
# status -p /var/run/postmaster.${PGPORT}.pid
status postmaster
script_result=$?
;;
Commented the original line and replaced with the one just below.
Regards
Gaetano Mendola
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] new field content lost
Hi all, since long time I have implemented a materialized view, today I had to add a new field and I faced the following (I believe) bug. The bug can be replicated on a 8.2.7 -- SETUP create table test (a integer, b integer); create table test_trigger (a integer); CREATE OR REPLACE FUNCTION trigger_test() RETURNS TRIGGER AS' DECLARE BEGIN update test set b = b*10 where a = NEW.a; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE CONSTRAINT TRIGGER trigger_test AFTER INSERT OR UPDATE ON test_trigger DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE trigger_test(); insert into test values (1,1),(2,2),(3,3); insert into test_trigger values (1),(2),(3); -- FROM CONNECTION A update test_trigger set a=1 where a=1; -- FROM CONNECTION B alter table test add column c integer; update test set c = 15; select * from test; -- FROM CONNECTION A update test_trigger set a=2 where a=2; --FROM CONNECTION B select * from test; you can see that the value c=15 for a=2 has been nullified Regards Gaetano -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new field content lost
Tom Lane wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: >> since long time I have implemented a materialized view, today I had to add a >> new field and I faced the following (I believe) bug. >> The bug can be replicated on a 8.2.7 > > Cached plan for the function's UPDATE. Should work okay in 8.3 ... > It does. Regards Gaetano Mendola -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Not valid dump [8.2.9, 8.3.1]
Hi all, we have faced lately dumps not valid, the bug can be replicated using a 8.2.9 or a 8.3.1 server. These are the steps to create the database that will generate a not valid dump: --- CREATE TABLE t_public ( a integer ); CREATE OR REPLACE FUNCTION sp_public ( ) RETURNS INTEGER AS' BEGIN PERFORM * FROM t_public LIMIT 1; RETURN 0; END; ' LANGUAGE 'plpgsql' IMMUTABLE; CREATE SCHEMA my_schema; CREATE TABLE my_schema.table_ref(x integer primary key); CREATE TABLE my_schema.table_test(x integer references my_schema.table_ref (x)); CREATE INDEX idx ON my_schema.table_test (x) WHERE x = sp_public(); Briefly: in the public schema we have a function that uses a table. In another schema we have a table with a foreign key to another table, and an partial index that uses the function in the public schema. The function is immutable because in our case the table being used inside the function is a lookup table (readonly). When the dump is restored the index idx is created but the foreign key is not. This is the error we obtain during the restore: psql:test.dump:143: ERROR: relation "t_public" does not exist CONTEXT: SQL statement "SELECT * FROM t_public LIMIT 1" PL/pgSQL function "sp_public" line 2 at perform SQL statement "SELECT fk."x" FROM ONLY "my_schema"."table_test" fk LEFT OUTER JOIN ONLY "my_schema"."table_ref" pk ON (pk."x"=fk."x") WHERE pk."x" IS NULL AND (fk."x" IS NOT NULL)" Regards -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not valid dump [8.2.9, 8.3.1]
On Fri, Jun 20, 2008 at 4:37 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: > > we have faced lately dumps not valid, the bug can be replicated using a > 8.2.9 or > > a 8.3.1 server. > > > These are the steps to create the database that will generate a not valid > dump: > > This is a bug in your function: it will not work if the search path > doesn't contain the public schema. You'd be best advised to make it > qualify the reference to t_public explicitly. Yes, that's the way we are fixing it. Still I have a bitter taste being able to create a working database instance that doesn't generate a valid dump. (Of course you realize that referencing any table at all in an > "immutable" function is probably a mortal sin...) > Yes Tom I know, in our case that table is a lookup table, noone update, delete, insert data in it, so from my point of view it is like I have declared a static array inside the function declaration. -- cpp-today.blogspot.com
[HACKERS] 7.4beta compile warning
Hi all, I had these during the compilation: gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/include -c -o execScan.o execScan.c execScan.c: In function `tlist_matches_tupdesc': execScan.c:199: warning: unused variable `att_tup' In file included from preproc.y:6278: pgc.c: In function `yylex': pgc.c:1386: warning: label `find_rule' defined but not used /usr/include/ctype.h: At top level: pgc.c:3367: warning: `yy_flex_realloc' defined but not used Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] consistency check on SPI tuple count failed
I forgot to say to do a: select bar() at the end! Gaetano ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Farewell
Retired? May be is better: Hall of fame. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] consistency check on SPI tuple count failed
Hi all, the following code was working properly under Postgres 7.3.X I'm now running my regression test with Postgres 7.4beta1 and I'm having the error in subj. CREATE TABLE test ( a integer, b integer ); INSERT INTO test VALUES ( 1 ); CREATE OR REPLACE FUNCTION foo(INTEGER) RETURNS INTEGER AS' BEGIN RETURN $1 + 1; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION bar() RETURNS INTEGER AS' DECLARE my_ret RECORD; BEGIN FOR my_ret IN SELECT foo(a) AS ret FROM test LOOP IF my_ret.ret = 3 THEN RETURN -1; END IF; END LOOP; RETURN 0; END; ' LANGUAGE 'plpgsql'; Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SetVariable
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > I see other strdup() calls that don't check on a return. Should we deal > with those too? Well strdup obtain the memory for the new string using a malloc and normally is a good habit check the return value of a malloc. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SetVariable
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > Gaetano Mendola wrote: > > "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > > > I see other strdup() calls that don't check on a return. Should we deal > > > with those too? > > > > Well strdup obtain the memory for the new string using a malloc > > and normally is a good habit check the return value of a malloc. > > Right. My point is that we have lots of other strdup's in the code. > Should we fix those too? Seems we should be consistent. Of course yes, consider also that inside SetVariable the check is performed but too late, after that the old value was loose for ever. Keep also the suggestion of Tom Late about the xstrdup. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SetVariable
"Tom Lane" <[EMAIL PROTECTED]> wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I see other strdup() calls that don't check on a return. Should we deal > > with those too? > > strdup -> xstrdup if you're concerned. May be is a good idea avoid the future use: #define strdup STRDUP_DEPRECATED_USE_INSTEAD_XSTRDUP I don't like the other solution: redefine the strdup in function of xstrdup. Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: 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] SetVariable
"Tom Lane" <[EMAIL PROTECTED]> wrote: > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > "Tom Lane" <[EMAIL PROTECTED]> wrote: > >> strdup -> xstrdup if you're concerned. > > > May be is a good idea avoid the future use: > > #define strdup STRDUP_DEPRECATED_USE_INSTEAD_XSTRDUP > > Not a good idea --- there are places that want to check for strdup > failure and do something different than exit(1). That's true. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SetVariable
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > Mendola Gaetano wrote: > > "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > > > Gaetano Mendola wrote: > > > > "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > > > > > I see other strdup() calls that don't check on a return. Should we > > deal > > > > > with those too? > > > > > > > > Well strdup obtain the memory for the new string using a malloc > > > > and normally is a good habit check the return value of a malloc. > > > > > > Right. My point is that we have lots of other strdup's in the code. > > > Should we fix those too? Seems we should be consistent. > > > > Shall I post the patch for SetVariable ? > > > > I know that this change is not so important but I want to try > > on my skin the cycle > > seen somethink wrong => send patch => be applyed > > Sure. > > It would be good if you would evaluate all the strdups, find the ones > that don't check properly, and submit a big patch of all those. The fix > can be to call xstrdup, or to check the return code. I will. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Unixware Patch (Was: Re: Beta2 Tag'd and Bundled ...)
"Peter Eisentraut" <[EMAIL PROTECTED]> wrote: > Reentrancy is (usually) a property of the interface (hence *_r functions > with differing interfaces), thread-safety is a feature of the > implementation; May I not agree with this definition ? Reentrancy is a property of the implemention that assure that the code can be executed simultaneously by concurrent program. Thread safety instead involve concept like critical section and the ability to force the non execution simultaneously of part of the code. I agree anyway that are orthogonal concept. Regards Gaetano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_id and pg_encoding
"Andreas Pflug" <[EMAIL PROTECTED]> wrote: > Bruce Momjian wrote: > > >>We'll also need to decide the Windows equivalent of the 'don't run as > >>root' rule - or even if we want to enforce it at all, given that it > >>appears to be very common practice on Windows to run all services as a > >>user with Administrator privileges. > >> > >> > > > >I assume we will relax that for Win32. I don't think non-Administrators > >have the same isolation on Win32 as non-root users have on Unix. > > > > > While it's best practice for *ix to work as non-root, many windows users > will be administrator-equivalent. The "Local System account" commonly > used to run services is even more privileged than the local admin. So > the restriction to non-admins won't make too much sense. Work as non-root is a good practice for windows user too, I'll not bet for the future that on windows all users will be "super user"; you can choose to start a service like a non super user too, I'd like to mantain the same policy on windows too. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] mcxt.c
"Tom Lane" <[EMAIL PROTECTED]> wrote:
> "Mendola Gaetano" <[EMAIL PROTECTED]> writes:
> > A test for null string is missing here:
>
> > MemoryContextStrdup(MemoryContext context, const char *string)
> > {
> > char *nstr;
> > -
> > - if ( !string )
> > - {
> > - elog(ERROR, "MemoryContextStrdup called with a NULL pointer");
> > - return NULL;
> > - }
>
> This seems inappropriate to me. Are you going to suggest that every
> routine that takes a pointer parameter needs to explicitly test for
> null? We could bloat the code a great deal that way, and slow it down,
> without gaining anything at all in debuggability (IMHO anyway).
Of course I'm not suggesting this, what I'm suggesting is put an
assert( ) if the test can slow down the performances and an "if ( ) "
in places that are not going to touch the performances.
I think that is reasonable.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] constraint modification on todo list
"Jeroen Ruigrok/asmodai" <[EMAIL PROTECTED]> wrote: > Because what I can imagine, and please correct me if I miss something in > my thought pattern, you have a small gap between dropping a constraint > and adding the new one allowing the possibility of missing checks. I think, someone correct me if I'm wrong, you can do it inside a transaction, so no time window without constraint. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] constraint modification on todo list
"Tom Lane" <[EMAIL PROTECTED]> wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > I assume MODIFY would allow you to alter the constraint without > > re-checking all the rows, as would be required by DROP/ADD. However, if > > you are modifying the constraint, wouldn't we have to recheck all the > > rows anyway. > > Yeah. Rod's point about altering foreign key action settings is a good > one, but other than that I don't see a whole lot of scope for shortcuts. The only shortcuts that come to my mind is when you enlarge ( or relax ) a constraint, and I think that is not so easy detect it. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] mcxt.c
"Andrew Dunstan" <[EMAIL PROTECTED]> wrote: > The particular assertion that was proposed doesn't strike me as terribly > useful - It should be checked at the point of call rather than inside > pstrdup, I should have thought. Are you going to trust the client of that function ? Here the question is not if insert a check/assert there but write a general rule if insert and where check/assert Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Maximum table size
On Tue, 9 Sep 2003 14:25:19 -0400 (EDT), [EMAIL PROTECTED] (Bruce Momjian) wrote: >Tatsuo Ishii wrote: >> > Tom Lane wrote: >> > > Bruce Momjian <[EMAIL PROTECTED]> writes: >> > > > Is our maximum table size limited by the maximum block number? >> > > >> > > Certainly. >> > > >> > > > Is the 16TB number a hold-over from when we weren't sure block number >> > > > was unsigned, though now we are pretty sure it is handled as unsigned >> > > > consistenly? >> > > >> > > It's a holdover. As to how certain we are that all the >> > > signed-vs-unsigned bugs are fixed, who have you heard from running a >> > > greater-than-16Tb table? And how often have they done CLUSTER, REINDEX, >> > > or even VACUUM FULL on it? AFAIK we have zero field experience to >> > > justify promising that it works. >> > > >> > > We can surely fix any such bugs that get reported, but we haven't got >> > > any infrastructure that would find or prevent 'em. >> > >> > I guess the big question is what do we report as the maximum table size? >> > Do we report 32TB and fix any bug that happen over 16TB? >> >> That seems right direction for me. I see no reason why 16TB is more >> reliable number than 32TB, since nobody has ever tried to build 16TB >> tables. > >Agreed. I think the question is how large does the design support, >rather than how large have we tested. (In fact, the check for using >block numbers as unsigned was removed from the FAQ when I reviewed the >code.) > >I know Tom is concerned because we haven't tested it, but I don't think >anyone has tested 16TB either, nor our 1600-column limit. Well, made some tests with 1600 shall not be so difficult and I'll not bet that nobody reached this limit > >Also, I think people look at these numbers to determine if PostgreSQL >can handle their data needs 5-10 years down the road. I don't agree that people are looking at PostgreSQL fot handle 5-10 years old, what I think ( is anyway my opinion ) is that people are looking at postgres in order to avoid more expensive tools like ORACLE, SYBASE, INFORMIX, and have a low TCO >In fact, if you increase the page size, you can quadruple most of the >existing limits. This is already mentioned in the FAQ: > > The maximum table size and maximum number of columns can > be increased if the default block size is increased to 32k. > >I have updated the FAQ to say 32TB, and of course, larger page sizes >could make this 128TB. Why this ? just because bigger is better? I agree with Tom Lane, is better underpromise than overpromise. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Maximum table size
""Dann Corbit"" <[EMAIL PROTECTED]> wrote: > /* > ** This will generate a 28 megabyte SQL script. > ** 1600 table definitions will be created for tables > ** with from 1 to 1600 columns. > */ That's easy, now you shall do real query, real vacuum, real reindex on it Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] mcxt.c
"Tom Lane" <[EMAIL PROTECTED]> wrote: > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > "Tom Lane" <[EMAIL PROTECTED]> wrote: > >> This seems inappropriate to me. Are you going to suggest that every > >> routine that takes a pointer parameter needs to explicitly test for > >> null? > > > Of course I'm not suggesting this, what I'm suggesting is put an > > assert( ) if the test can slow down the performances and an "if ( ) " > > in places that are not going to touch the performances. > > I see no value at all in an assert. The code will dump core just fine > with or without an assert ... Right but an assert can display information about the file and line number without debug the application, without mention that reading the code with the assert is clear what are the precondictions for a call function. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem with function permission test in a view
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > Someone asked me a question about view and function permissions. I > > > assumed all object access done by a view would be based on the > > > permissions on the view, and not the permissions of the objects. > > > > Table references are checked according to the owner of the view, but use > > in a view does not change the execution context for function or operator > > calls. This is how it's always been done. > > > > > Is this a bug? > > > > Changing it would be a major definitional change (and a pretty major > > implementation change too). It might be better, but please don't > > pre-judge the issue by labeling it a bug. > > Well, it sure sounds like a bug. What logic is there that table access > use the view permissions, but not function access? Could we just use > SECURITY DEFINER for function calls in views? I already had this problem, look here: http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1 and I had no reply :-( Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Problem with function permission test in a view
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > Someone asked me a question about view and function permissions. I > > > assumed all object access done by a view would be based on the > > > permissions on the view, and not the permissions of the objects. > > > > Table references are checked according to the owner of the view, but use > > in a view does not change the execution context for function or operator > > calls. This is how it's always been done. > > > > > Is this a bug? > > > > Changing it would be a major definitional change (and a pretty major > > implementation change too). It might be better, but please don't > > pre-judge the issue by labeling it a bug. > > Well, it sure sounds like a bug. What logic is there that table access > use the view permissions, but not function access? Could we just use > SECURITY DEFINER for function calls in views? I already had this problem, look here: http://groups.google.it/groups?q=postgres+security+definer+gaetano+mendola&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=b711hu%241g25%241%40news.hub.org&rnum=1 and I had no reply :-( Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] constraint modification on todo list
"Jeroen Ruigrok/asmodai" <[EMAIL PROTECTED]> wrote: > -On [20030911 15:43], Tom Lane ([EMAIL PROTECTED]) wrote: > >We can't ALTER a table that's already in use when the first ALTER > >starts, either --- its attempt to exclusive-lock the table will fail. > >But once you get the exclusive lock, you can (in Postgres) perform > >a series of operations without fear that subsequently-started > >transactions will be able to see the incompletely changed state of the > >table. Evidently Oracle can't handle that. That's why they need to > >invent combination operations like MODIFY CONSTRAINT. > > As my colleague says: > > it is indeed a lazy choice, but super safe and that's the goal. Does your colleague know the Aesops's Fables: "The fox and the Grapes" ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] observations about temporary tables and schemas
Tom Lane wrote: I think we have two choices: disallow foreign-key references from temp tables to permanent tables, or take out the optimization of storing temp table pages in private memory. (That would leave the whole "local buffer manager" module as dead code, I think.) I'm kinda leaning towards the first; does anyone feel that it's a valuable feature to keep? My vote is for remove this feature. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] 7.4beta2 vs 7.3.3
class_default (cost=0.00..1.43 rows=1 width=4) (actual time=0.07..0.08 rows=1 loops=1) Filter: (id_provider = 39) -> Index Scan using idx_user_data_class on user_data (cost=0.00..315.87 rows=386 width=8) (actual time=0.11..0.54 rows=43 loops=1) Index Cond: (user_data.id_class = "outer".id_class) Total runtime: 2986.33 msec (11 rows) How you can see with 7.4 the two queries ( the original with the join and with the IN nested ) are performing at the same way. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.4beta2 vs 7.3.3
Tom Lane wrote: Hm, it sure looks to be exactly the same plan. The performance difference seems to be just that the seqscans are faster. I surmise that in the 7.3 database you had a lot of dead rows, or at least a lot of free space. Possibly you need to vacuum more often to keep down the amount of junk in the tables. The two databases were created from scratch and the first operation on it ( after a vacuum analyze ) was just that query. I will try to pump up the statistic target. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4beta2 vs 7.3.3
Gaetano Mendola wrote: Tom Lane wrote: Hm, it sure looks to be exactly the same plan. The performance difference seems to be just that the seqscans are faster. I surmise that in the 7.3 database you had a lot of dead rows, or at least a lot of free space. Possibly you need to vacuum more often to keep down the amount of junk in the tables. The two databases were created from scratch and the first operation on it ( after a vacuum analyze ) was just that query. I will try to pump up the statistic target. The default_statistics_target was already 1000 for postgres7.4beta2 and after setting this value to 1000 for postgres7.3.3 I had the same explain with the same expected row ( almost 400 ) and the almost the same time execution. Another piece: The query SELECT ud.* FROM user_data ud, class_default cd WHERE ud.id_class = cd.id_class AND cd.id_provider = 39; ( I removed the huge table ) take just few ms to be executed. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 7.4beta2 vs 7.3.3
Tom Lane wrote: Gaetano Mendola <[EMAIL PROTECTED]> writes: Tom Lane wrote: Hm, it sure looks to be exactly the same plan. The performance difference seems to be just that the seqscans are faster. I surmise that in the 7.3 database you had a lot of dead rows, or at least a lot of free space. Possibly you need to vacuum more often to keep down the amount of junk in the tables. The two databases were created from scratch and the first operation on it ( after a vacuum analyze ) was just that query. Y'know, I'd love to think that 7.4 is 2x faster than 7.3 on seqscanning, but I just can't believe that. We didn't do anything that could account for such a speedup. So there's got to be some inconsistency in your test conditions. The machine is the same, the disk too, the main values in the configuration file are the same ( I put the confs in attachment ). I was alo able to remove the useless column on that tables, and I put the dump here: http://212.198.37.110 The select take long: Postgres7.3.3: average 4000 ms Postgres7.4b2: average 2600 ms you can experiment your self with the dump that I gave you Regards Gaetano Mendola tcpip_socket = true max_connections = 128 shared_buffers = 13000 # I'm running two postgres server max_fsm_relations = 1000# min 10, fsm is free space map, ~40 bytes max_fsm_pages = 200 max_locks_per_transaction = 64 # min 10 wal_buffers = 2000 sort_mem = 32768 vacuum_mem = 32768 default_statistics_target = 1000 server_min_messages = debug2 log_connections = true log_pid = true log_statement = true log_duration = true log_timestamp = true syslog = 2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' stats_start_collector = true stats_reset_on_server_start = true stats_command_string = true stats_row_level = true stats_block_level = true LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8' tcpip_socket = true max_connections = 128 shared_buffers = 13000 sort_mem = 32768 vacuum_mem = 32768 max_fsm_pages = 200 max_fsm_relations = 1000 wal_buffers = 2000 default_statistics_target = 1000 syslog = 2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_messages = debug2 log_connections = true log_duration = true log_pid = true log_statement = true log_timestamp = true log_hostname = false log_source_port = true stats_start_collector = true stats_command_string = true stats_block_level = true stats_row_level = true stats_reset_on_server_start = true lc_messages = 'en_US.UTF-8' # locale for system error message strings lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4beta2 vs 7.3.3
Tom Lane wrote: Gaetano Mendola <[EMAIL PROTECTED]> writes: The select take long: Postgres7.3.3: average 4000 ms Postgres7.4b2: average 2600 ms you can experiment your self with the dump that I gave you Hm. I tried to duplicate your results. I'm getting about 5400 msec versus 4200 msec, which is a nice version-to-version improvement but not as large as you're seeing. (I have --enable-cassert on, though, and that may be cancelling some of the percentage gain.) May be, I have the --enable-cassert off. What about the wrong row expected ? Anyway if the rows expected are 400 ( instead of 43 ) why not an index scan, with 400 rows on 150 seems a good choise do an index scan, isn't it ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL not ACID compliant?
Joshua D. Drake wrote: I need a hug. *HUG* ---(end of broadcast)--- TIP 3: 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] 7.4beta2 vs 7.3.3
Tom Lane wrote: Your idea of reducing id_provider to id_class using a separate query seems like a good one to me --- that will allow the planner to generate different plans depending on which id_class value is involved. However is not a natural way to approch the problem; Am I wrong ? Gaetano ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Error message cleanup
Peter Eisentraut wrote: "transaction block" vs. "BEGIN/END transaction block" -> Both are used, I think the first one is better. I vote for the second one. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Improving REINDEX for system indexes (long)
Hiroshi Inoue wrote: > instead. Because it was impossible to make REINDEX transaction-safe > then, such flag was needed to suppress inconsistency as less > as possible. This mean that the actual REINDEX is not transaction-safe ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Improving REINDEX for system indexes (long)
Hiroshi Inoue wrote: Gaetano Mendola [mailto:[EMAIL PROTECTED] wrote: Hiroshi Inoue wrote: instead. Because it was impossible to make REINDEX transaction-safe then, such flag was needed to suppress inconsistency as less as possible. This mean that the actual REINDEX is not transaction-safe ? No. It was not transaction-safe long time ago. Anyway I think there is a nasty bug somewhere, see my last posts about "duplication primary key". Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is this a commit problem?
[EMAIL PROTECTED] wrote: 2. SELECT d_next_o_id INTO current_o_id FROM district WHERE d_w_id = 1 AND d_id = 8 3. UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = 1 AND d_id = 8 I don't know exactly what you are trying to do but usualy in cases like this, in order to avoid further problem in the commit phase you ought to do a SELECT FOR UPDATE instead. Regards Gaeatano Mendola ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Calculated view fields (8.1 != 8.2)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm observing this behavior in 8.2: kalman=# create table test ( a integer, b integer ); CREATE TABLE kalman=# kalman=# CREATE OR REPLACE FUNCTION sp_test ( INTEGER ) kalman-# RETURNS INTEGER AS' kalman'# DECLARE kalman'#a_id_contractALIAS FOR $1; kalman'# BEGIN kalman'#RAISE NOTICE ''here''; kalman'# kalman'#return 3; kalman'# END; kalman'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION kalman=# kalman=# kalman=# CREATE OR REPLACE VIEW v_test AS kalman-# SELECT kalman-# a AS a, kalman-# sp_test(b) AS b kalman-# FROM kalman-# test c kalman-# ; CREATE VIEW kalman=# kalman=# insert into test values (2,3); INSERT 0 1 kalman=# kalman=# select * from v_test; NOTICE: here a | b - ---+--- 2 | 3 (1 row) kalman=# select a from v_test; NOTICE: here a - --- 2 (1 row) In version 8.1 the function sp_test is not evaluated in case of "select a from v_test". If I mark the function as STABLE or IMMUTABLE then even with version 8.2 the function is not evaluated. Is this the intended behavior? I didn't see something about it in the release note. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF7Y1D7UpzwH2SGd4RAv//AJ0dcDPyYIndVMs7pEhzXjVNwKqdLQCeJQnL oaZVL2JgS/J9lPf+B80+FuY= =qaCE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Tom Lane wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: >> [ 8.2 evaluates volatile functions in the targetlist of a view ] >> If I mark the function as STABLE or IMMUTABLE then even with version >> 8.2 the function is not evaluated. Is this the intended behavior? > > Yes; people complained that we needed to be more careful about the > number of times volatile functions get evaluated. I suspect that functions are evaluated also for record discarded due to joins. Is that the case? Like: SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4; If ta is a view with some calculated fields are the function on ta evaluated only for record matching the filters or in some case ( like a full scan on ta ) also for the records discarded due to the join? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gaetano Mendola wrote: > Tom Lane wrote: >> Gaetano Mendola <[EMAIL PROTECTED]> writes: >>> [ 8.2 evaluates volatile functions in the targetlist of a view ] >>> If I mark the function as STABLE or IMMUTABLE then even with version >>> 8.2 the function is not evaluated. Is this the intended behavior? >> Yes; people complained that we needed to be more careful about the >> number of times volatile functions get evaluated. > > I suspect that functions are evaluated also for record discarded due to > joins. Is that the case? > > Like: > > SELECT * FROM ta JOIN tb USING (id) where tb.foo = 4; > > If ta is a view with some calculated fields are the function on ta > evaluated only for record matching the filters or in some case ( > like a full scan on ta ) also for the records discarded due to the join? > I did a check on a 8.2 and I can confirm my suspects: kalman=# create table ta ( a integer, b integer ); CREATE TABLE kalman=# create table tb ( b integer, c integer ); CREATE TABLE kalman=# kalman=# CREATE OR REPLACE FUNCTION sp_test ( INTEGER ) kalman-# RETURNS INTEGER AS' kalman'# DECLARE kalman'# a_idALIAS FOR $1; kalman'# BEGIN kalman'# RAISE NOTICE ''here''; kalman'# kalman'# return 3; kalman'# END; kalman'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION kalman=# kalman=# CREATE OR REPLACE VIEW v_ta AS kalman-#SELECT kalman-# sp_test(a) AS a, kalman-# b AS b kalman-#FROM kalman-# ta c kalman-# ; CREATE VIEW kalman=# kalman=# insert into ta values (2,3); INSERT 0 1 kalman=# insert into ta values (3,4); INSERT 0 1 kalman=# insert into tb values (4,5); INSERT 0 1 kalman=# kalman=# select * from v_ta join tb using (b) where c = 5; NOTICE: here NOTICE: here b | a | c - ---+---+--- 4 | 3 | 5 (1 row) Is really this what we want? I did a migration 8.0.x => 8.2.3 and I had on first hour of service up lot of queries "blocked" due to this, consider in my case I have on v_ta milions of records and usually that join extracts 1 row. Is there a way to set till I don't check all my huge schema to disable this behaviour? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF8TAc7UpzwH2SGd4RAgajAKCvIxLH9JSBk4gxSbuaq4WE2y7v2wCfbnRa jWDV3hlEq/Loye6G+E2S9Ew= =LR5T -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Martijn van Oosterhout wrote: > On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote: >> Is really this what we want? I did a migration 8.0.x => 8.2.3 and I had on >> first hour of service up >> lot of queries "blocked" due to this, consider in my case I have on v_ta >> milions of records and usually >> that join extracts 1 row. Is there a way to set till I don't check all my >> huge schema to disable this >> behaviour? > > Most people figured it was a improvment. It's configured per function > now, which wasn't the case before. I dont't think there was ever any > discussion about having a global switch. Well it's not an improvement in term of performances but a performance degradation in the best case and in the worst can be devastating: create table ta ( a integer, b integer ); CREATE TABLE create table tb ( b integer, c integer ); CREATE TABLE CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER ) RETURNS INTEGER AS' DECLARE a_idALIAS FOR $1; BEGIN DELETE FROM ta where a = a_id; return 0; END; ' LANGUAGE 'plpgsql'; CREATE FUNCTION CREATE OR REPLACE VIEW v_ta AS SELECT sp_delete_selected_row(a) AS a, b AS b FROM ta ; CREATE VIEW insert into ta values (2,3); INSERT 0 1 insert into ta values (3,4); INSERT 0 1 insert into tb values (4,5); INSERT 0 1 select * from v_ta join tb using (b) where c = 5; b | a | c ---+---+--- 4 | 0 | 5 (1 row) select * from ta; a | b ---+--- (0 rows) All rows are gone instead of the only one extracted from that query. IMHO is a undesired side effect. In my case I destroyed my application statistics on how many time a certain row was extracted. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Florian G. Pflug wrote:
> Gaetano Mendola wrote:
>> Martijn van Oosterhout wrote:
>>> On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote:
>>>> Is really this what we want? I did a migration 8.0.x => 8.2.3 and I
>>>> had on first hour of service up
>>>> lot of queries "blocked" due to this, consider in my case I have on
>>>> v_ta milions of records and usually
>>>> that join extracts 1 row. Is there a way to set till I don't check
>>>> all my huge schema to disable this
>>>> behaviour?
>>> Most people figured it was a improvment. It's configured per function
>>> now, which wasn't the case before. I dont't think there was ever any
>>> discussion about having a global switch.
>>
>> Well it's not an improvement in term of performances but a performance
>> degradation in the best case and
>> in the worst can be devastating:
>>
>> create table ta ( a integer, b integer );
>> CREATE TABLE
>> create table tb ( b integer, c integer );
>> CREATE TABLE
>>
>> CREATE OR REPLACE FUNCTION sp_delete_selected_row ( INTEGER )
>> RETURNS INTEGER AS'
>> DECLARE
>>a_idALIAS FOR $1;
>> BEGIN
>>DELETE FROM ta where a = a_id;
>>return 0;
>> END;
>> ' LANGUAGE 'plpgsql';
>> CREATE FUNCTION
>>
>> CREATE OR REPLACE VIEW v_ta AS
>> SELECT
>> sp_delete_selected_row(a) AS a,
>> b AS b
>> FROM
>> ta
>> ;
>> CREATE VIEW
>>
>> insert into ta values (2,3);
>> INSERT 0 1
>> insert into ta values (3,4);
>> INSERT 0 1
>> insert into tb values (4,5);
>> INSERT 0 1
>>
>> select * from v_ta join tb using (b) where c = 5;
>>
>> b | a | c
>> ---+---+---
>> 4 | 0 | 5
>> (1 row)
>>
>> select * from ta;
>> a | b
>> ---+---
>> (0 rows)
>>
>>
>> All rows are gone instead of the only one extracted from that query.
>> IMHO is a undesired side effect.
>> In my case I destroyed my application statistics on how many time a
>> certain row was extracted.
>
> This is insane. Whoever creates a view like that on a production system
> should *immediatly* be carried away from his keyboard, to prevent
> further damage. Imagine someone using "View Data" on this view in
> pgadmin.. I don't wanna be near him when he clicks "Refresh", and
> suddenly all data is gone...
>
> Maybe calling volatile functions in selects and views should be
> forbidden entirely, except for volatile functions in the top-level
> select clause,
> to support things like "select ..., nextval('seq') from ...".
>
> But it's probably not worth the effort - there will always be creative
> ways to shoot yourself into your foot.
I full agree with this, that was just an extreme example of an hidden undesired
call. In my framework I don't have by "coding rule" any function with side
effects
applied at view fields, however I have some functions not marked correctly as
STABLE ( mea culpa ) that degraded the performances until I realized what was
going
on; I'm in the opinion that is not sane call a function not marked as
stable/immutable
for discarded column (I can in some way accept this ) and most of all on
discarded
rows.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
Florian G. Pflug wrote: > Martijn van Oosterhout wrote: >> On Fri, Mar 09, 2007 at 10:59:56AM +0100, Gaetano Mendola wrote: >>> Is really this what we want? I did a migration 8.0.x => 8.2.3 and I >>> had on first hour of service up >>> lot of queries "blocked" due to this, consider in my case I have on >>> v_ta milions of records and usually >>> that join extracts 1 row. Is there a way to set till I don't check >>> all my huge schema to disable this >>> behaviour? >> >> Most people figured it was a improvment. It's configured per function >> now, which wasn't the case before. I dont't think there was ever any >> discussion about having a global switch. > > If your function is already marked immutable or stable, then nothing > changes for you. If you *did* call volatile functions inside your > select, then you now get > consistens behaviour. Since you don't want your function to be evaluated > in all > cases, I assume that it shouldn't be marked volatile in the first place. Well some function are volatile and can not be marked as stable. We develop our applications layering the modules, we didn't have any reason to forbid as "coding rule" to put function call on view in low level layers. After all views are there also to build up your schema layering the info. I can immagine a case when a lower module exports a view to upper layer stating the interface as list of fields: first_name, last_name, with an *hidden* field that is a function call that updates the statistics on how many time a given record was selected, then this technique can not be used anymore starting with 8.2.x. The above is not my case but it can be a possible scenario (I admit not a sane one ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Calculated view fields (8.1 != 8.2)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: >> I can immagine a case when a lower module exports a view to upper layer >> stating >> the interface as list of fields: > >> first_name, last_name, > >> with an *hidden* field that is a function call that updates the statistics on >> how many time a given record was selected, then this technique can not be >> used >> anymore starting with 8.2.x. > > You're living in a dream world if you think that works reliably in *any* > version of Postgres. But for starters, what is your definition of > "selected" --- pulled from the physical table? Accumulated into an > aggregate? Delivered as a recognizable row to the client? Delivered N > times to the client due to joining N times to some other table? Well that was a not good example, I don't have any problem in mark from now on all my function as stable/immutable (the one I use on views) but still I believe is source of bad performance evaluate a function on rows discarded and at same time this break the principle of least surprise. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF9U8R7UpzwH2SGd4RAhoGAKDSpUSQ3lGEdIdFWLwQjxoZXUAS1ACdGtht TZg9BKScbzGO0MzpHy0Gr80= =auwk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Backend crash in 8.2.3 with plpgsql function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > SM <[EMAIL PROTECTED]> writes: >> I got a backend crash in Postgresql 8.2.3 with the plpgsql function. >> The following statement in psql causes a signal 11: >> psql=# create function testpl() returns void as 'begin return; >> end;'language 'plpgsql'; > > Worksforme ... For me too. $ psql Welcome to psql 8.2.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit kalman=# create function testpl() returns void as 'begin return; end;'language 'plpgsql'; CREATE FUNCTION kalman=# select version(); version - PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-51) (1 row) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF+V2/7UpzwH2SGd4RAk29AJ44FZFMnsFHJV+uOcQZpuD0cGN/YACgjxjY 4lVP/g+/PLs2+RfOFtpBJtE= =/Vae -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] UPDATE using sub selects
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NikhilS wrote: > I have tried some update-subselect variations and they seem to work. For > example the case in the src/test/regress/sql/update.sql, which used to > fail till now, seems to work: > > UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = > 'foo') > WHERE a = 10; What's the expected result if the tuple from subselect is more than 1? I expect no update at all in case of void result set, is this the case ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF+Vx47UpzwH2SGd4RAvyVAKCGK5pC9B/lmrNjAFPGXhm5ialwSwCglM2n DxrxWyvJASX5WSF9B8cAMas= =AoVF -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Lock table in non-volatile functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm observing that is not allowed to LOCK a table in a STABLE/IMMUTABLE function but at same time is allowed a SELECT FOR UPDATE. Is that normal? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF+lsm7UpzwH2SGd4RAjloAJ4j/AOdJhGMRnvM/TKVpKHPwesAOACeO4mT OQhSwR1of3xS7HSSvtjGiQc= =nFFM -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] Lock table in non-volatile functions
Tom Lane wrote: Gaetano Mendola <[EMAIL PROTECTED]> writes: I'm observing that is not allowed to LOCK a table in a STABLE/IMMUTABLE function but at same time is allowed a SELECT FOR UPDATE. Really? AFAICS, CommandIsReadOnly() will reject SELECT FOR UPDATE too. kalman=# select version(); version PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-51) (1 row) kalman=# CREATE TABLE test( a integer ); CREATE TABLE kalman=# INSERT INTO test VALUES ( 1 ); INSERT 0 1 kalman=# CREATE OR REPLACE FUNCTION sp_test() kalman-# RETURNS INTEGER AS $body$ kalman$# DECLARE kalman$# my_integer integer; kalman$# my_port_set RECORD; kalman$# BEGIN kalman$# FOR my_port_set IN kalman$# SELECT a kalman$# FROM test kalman$# FOR UPDATE kalman$# LOOP kalman$# my_integer = 0; kalman$# END LOOP; kalman$# RETURN 0; kalman$# END; kalman$# $body$ language 'plpgsql' kalman-# STABLE; CREATE FUNCTION kalman=# select sp_test(); sp_test - 0 (1 row) BTW why forbid the lock in a non volatile function or (if you fix this) the SELECT FOR UPDATE ? Regards Gaetano Mendola ---(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] checking on buildfarm member thrush
Tom Lane wrote: > Could you get a gdb stack trace from that crash? If the buildfarm > run is under a suitable ulimit, it should be leaving a core file > in the test PGDATA directory. Unfortunately the core size for the user pgfarm is 0: $ulimit -c 0 However I did a configure, make and make check on regression test and I got two cores, may be I do something wrong but I'm not able to get any information. Configured with: ./configure --prefix=/home/pgfarm/HEAD/inst --enable-cassert --enable-debug --enable-nls --enable-integer-datetimes --with-perl --with-python --with-tcl $ gdb tmp_check/install/home/pgfarm/HEAD/inst/bin/postgres ./tmp_check/data/core.6516 GNU gdb Red Hat Linux (5.3.90-0.20030710.41rh) Copyright 2003 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "i386-redhat-linux-gnu"...Using host libthread_db library "/lib/tls/libthread_db.so.1". warning: core file may not match specified executable file. Core was generated by `postgres: pgfarm regression [local] COMMENT '. Program terminated with signal 11, Segmentation fault. Reading symbols from /lib/libcrypt.so.1...done. Loaded symbols for /lib/libcrypt.so.1 Reading symbols from /lib/libdl.so.2...done. Loaded symbols for /lib/libdl.so.2 Reading symbols from /lib/tls/libm.so.6...done. Loaded symbols for /lib/tls/libm.so.6 Reading symbols from /lib/tls/libc.so.6...done. Loaded symbols for /lib/tls/libc.so.6 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /lib/libnss_files.so.2...done. Loaded symbols for /lib/libnss_files.so.2 #0 0x0005 in ?? () (gdb) bt #0 0x0005 in ?? () #1 0x4000 in ?? () anything else I can do ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] One vacuum full is not enough.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hannu Krosing wrote: > Ühel kenal päeval (teisipäev, 8. märts 2005, 00:52+0100), kirjutas > Gaetano Mendola: > >>Hi all, >>running a 7.4.5 it happen to me with another table >>where a single vacuum full was not freeing enough pages, >>here the verbose vacuum full, as you can see only at >>the end: truncated 8504 to 621 pages. >> >>I use pg_autovacuum and it's not enough. I'll schedule >>again a nightly vacuum full. > > > You may have too few fsm pages, so new inserts/updates don't use all the > pages freed by vacuums. > Is not this the case: max_fsm_pages | 200 max_fsm_relations | 1000 and when I was doing the vacuum full these settings were above the real needs. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCMA++7UpzwH2SGd4RAi0hAJwLBpSWlDTQoAWglK8Dg/IoY3fb8QCfTjKU wxDSc2VG7B5pRPfCfQqxRtk= =Ce+9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] A bad plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: > Gaetano, > > >>Hi all, >>running a 7.4.5 engine, I'm facing this bad plan: > > > Please take this to the PGSQL-PERFORMANCE mailing list; that's what that list > exists for. > > Or IRC, where I know your are sometimes. But not -Hackers. Sorry, I was convinced to have sent this email to performances ( as I do usually ). Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCLXih7UpzwH2SGd4RApCYAKCS/1qPYFs7GABfpwAO0c51kg+daQCg/J66 vwv2Z92GtFvOwKFwa8jC838= =BlCp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] keepalive
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, this weekend our DB server crashed ( 7.4.x ) and the DB engine was relocated on another server. So far so well. Unfortunatelly most of our clients are still on a "recv" trying to recv data from the old DB engine instance: # netstat -anp | grep ESTABLISHED | grep 54497 tcp0 0 193.251.135.46:5449710.10.50.47:5432ESTABLISHED 27331/psql # ps -eafwww | grep 27331 apache 27331 27327 0 May13 ?00:00:00 psql -t -c SELECT is_authorized_flag FROM v_ts_quota WHERE login='*' -h x.y.z.w yyy as you can see this connection is there since 3 days now. Digging on it I see these sockets are not using the keepalive options: netstat -anop | grep ESTABLISHED | grep 54497 tcp0 0 193.251.135.46:5449710.10.50.47:5432ESTABLISHED 27331/psql off (0.00/0/0) normaly on other connections I see: tcp0 0 127.0.0.1:199 127.0.0.1:32784 ESTABLISHED 1255/snmpd keepalive (7102.52/0/0) The same happens with JDBC connections ( the keepalive missing ). There is a reason for this or a way to instruct psql to use the keepalive option ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCiJNF7UpzwH2SGd4RAh8aAJ0WOBEzzjYf1gj1OaFGsFBE9mr4hgCfUhna D1F420Pa94lvrA04xA73tiE= =muzn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SO_KEEPALIVE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Oliver Jowett wrote: >>>> If you're unlucky, the server could go down while you're blocked waiting >>>> for a query response.. >>>> That is exactly what happens to us, and you have to be not so unlucky for that happen if the engine have ~100 query at time. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCi04C7UpzwH2SGd4RArvMAKDUJEefpsH2CX9E6wjg2j5DcV3JSwCgr/XB BlTc3y4vE9GjyUl6eypcN00= =h/Gg -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: 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] SO_KEEPALIVE
Oliver Jowett wrote: > If you're unlucky, the server could go down while you're blocked waiting > for a query response.. > That is exactly what happens to us, and you have to be not so unlucky for that happen if the engine have ~100 query at time. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] SO_KEEPALIVE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I understood that noone will add that option to pglib, is it correct ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCkOh27UpzwH2SGd4RAuh1AJ92B4qZiYZmd40+v4QwmCJyM7z0ggCbBSon g75HfsDdfdSRuIVXEqFe6+g= =I+X/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 8.0.x RPM issues
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm trying to install 8.0.3 on a RH9.0. I do have few problems: 1) They do not exist for 9.0 2) Using the 8.0.2 rpm I get: # rpm -Uvh --test *.rpm warning: postgresql-8.0.2-1PGDG.i686.rpm: V3 DSA signature: NOKEY, key ID 748f7d0e error: Failed dependencies: libpq.so.3 is needed by postgresql-contrib-8.0.2-1PGDG libecpg.so.3 is needed by postgresql-libs-8.0.2-1PGDG libpq.so.3 is needed by postgresql-libs-8.0.2-1PGDG libpq.so.3 is needed by (installed) gnucash-backend-postgres-1.8.1-3 libpq.so.3 is needed by (installed) libdbi-dbd-pgsql-0.6.5-5 libpq.so.3 is needed by (installed) mod_auth_pgsql-0.9.12-14 libpq.so.3 is needed by (installed) perl-DBD-Pg-1.21-2 libpq.so.3 is needed by (installed) php-pgsql-4.2.2-17 libpq.so.3 is needed by (installed) qt-PostgreSQL-3.1.1-6 libpq.so.3 is needed by (installed) xemacs-21.4.12-6 libpq.so.3 is needed by (installed) postgresql-tcl-7.4.2-1PGDG Suggested resolutions: postgresql-libs-7.3.2-3.i386.rpm 3) 8.0.2 SRPMS for RH9.0 are not there as well. If you provide me a way to get the SRPMS I can create the rpms and give them to you Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCkx4W7UpzwH2SGd4RAp4uAKCdobiXlhZkc6TzersTA295ne0GggCgreVU ftk/gM7gvdMSoRkKhSb2Il4= =63o7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 8.0.x RPM issues
Dave Cramer wrote: > Check the archives, this has already been discussed. > Devrim is posting a compat rpm shortly. Thx. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] linux and anotime mount option
Do you know if postgres made assumption on the access time time stamp for the files on his own file sistem ? If not I'm wondering if mount a partition with the option "anotime" can improve the disk i/o performance. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] idle in transaction with JDBC interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm facing a problem with the unfamous: "idle in transaction" problem. I'm using the JDBC driver. Mainly the problem is that the JDBC interface doesn't provide the method begin() for a transaction, of course this is not a JDBC postgres interface problem. Let me explain what happen using the JDBC interface Client Side|Server Side - --- 1) Open a connection|Connection accepted ~| <- Connection Idle 2) set autocommit false |begin; ~| <- Idle in transaction 3) select now();|select now(); ~| <- Idle in transaction 4) commit; |commit; begin; ~| <- Idle in transaction 5) select now();|select now(); ~| <- Idle in transaction 6) rollback;|rollback; begin; ~| <- Idle in transaction as you can easily understand there is no window time larger enough with a connection idle, I thin that the JDBC behaviour ( with the server I mean ) is not really correct. This is what I think it's better: Client Side|Server Side - --- 1) Open a connection|Connection accepted ~| <- Connection Idle 2) set autocommit false | ~| <- Connection Idle 3) select now();|begin; select now(); ~| <- Idle in transaction 4) commit; |commit; ~| <- Connection Idle 5) select now();|begin; select now(); ~| <- Idle in transaction 6) select now();|select now(); ~| <- Idle in transaction 7) rollback;|rollback; ~| <- Connection Idle AS you can see the JDBC driver must do a begin only before the first statement. Am I missing something ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAdTxl7UpzwH2SGd4RAkPOAJwNgUsfkMpd9m5R4que7PxuFnrZvgCePbI9 hdCLD4fAI6vRnr224e9r0lk= =gEQe -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.5 beta version]
Jürgen Cappel wrote: Point 1 I completely agree on: byte order, alignment, padding, etc. is different for each platform and data cannot directly be exchanged. Point 2: who really needs C++ ?? We use it, a multi path TCP router written in C++ and behind there is a Postgresql... Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Invalid pg_hba.conf => Postgres crash
Hi all, running postgres 7.4.1 2 different behavior: - Inserting the following invalid line on pg_hba.conf host allall127.0.0.1trust sameuser # HANG the future connections will hang leaving a postgres process running - Inserting the following line instead will crash postgres host all 127.0.0.1 trust sameuser# CRASH #psql kalman psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and in the log: LOG: server process (PID 17656) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2004-04-24 12:27:37 CEST LOG: checkpoint record is at 0/4877FD38 LOG: redo record is at 0/4877FD38; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 195700; next OID: 5718523 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/4877FD78 LOG: redo is not required LOG: database system is ready Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Invalid pg_hba.conf => Postgres crash
Tom Lane wrote: I was able to reproduce the crash but not the hang. Would you see if the attached patch fixes both symptoms on your machine? Yes, problem gone. I discover however that what I was believing an hang was in reality just a delayed crash. Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] invalid memory alloc request size 0
Hi all, strange errors with a empty table: kalman=# create table test(); CREATE TABLE kalman=# select * from test t union select * from test t1; ERROR: invalid memory alloc request size 0 I think this have to return: -- (0 rows) Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_begintypend
Hi all, loading a function written in C I get this error: #create function email_in( cstring ) returns email AS 'emaildt.so', 'email_in' LANGUAGE C STRICT; NOTICE: type "email" is not yet defined DETAIL: Creating a shell type definition. ERROR: could not load library "emaildt.so": emaildt.so: undefined symbol: pg_begintypsend I'm compiling my files in this way: gcc -c -g -fPIC -o "email.o" -I /usr/include/pgsql/server "email.c" gcc -g -shared -fPIC -o "emaildt.so" email.o which library I have to link in order to have the symbol pg_begintypsend ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] tinyint
Hi all, someone of you sent me an example of a new data type called tinyint. I seen that is using a well directory structure just for compile a tinyint.c. Is there any "empty" structure that I have to use in order to delivery compile my email.c or I can just use a single line command: gcc email.c -I. -I /usr/include/pgsql/server I seen also that a library called libpgext.so is created. Shall I do the same ? Are guide line to use out there? Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
