Re: [HACKERS] Improve lseek scalability v3
On Fri, Sep 16, 2011 at 07:27:33PM +0200, Andres Freund wrote: many tuples does the table have. Those statistics are only updated every now and then though. So it uses those old stats to check how many tuples are normally stored on a page and then uses that to extrapolate the number of tuples from the current nr of pages (which is computed by lseek(SEEK_END) over the 1GB segements of a table). I am not sure how interested you are on the relevant postgres internals? For such tables, can't Postgres track the size of the file internally? I'm assuming it's keeping file descriptors open on the tables it manages, in which case when it writes to a file to extend it, the internally stored size could be updated. Not making a syscall at all would scale far better than even a modified lseek() will perform. Granted, I've not looked at the Postgres code at all. -ben -- 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] Range Types - typo + NULL string constructor
hello sorry for late assign to discussion. I don't think so using NULL instead INF is a good idea. Regards Pavel Stehule 2011/9/19 Jeff Davis pg...@j-davis.com: On Sun, 2011-09-18 at 18:08 +0200, Erik Rijkers wrote: Below are 2 changes. The first change is an elog saying 'lower' instead of 'upper'. Done, thank you. New patch attached. Changes: * documentation fixes * added document for pg_range catalog * cleaned up errors, increased error checking * improved pg_dump TODO: * Support casts and typmod. - This requires adding a RangeCoerceExpr, or possibly overloading ArrayCoerceExpr somehow. This is likely to require a lot of boilerplate code and a fairly large diff. * Cache lookups better to avoid unnecessary SearchSysCache calls. * I need to find a clean way to get the operator class name in pg_dump. Rangetypes as it stands uses NULL to indicate INF or -INF: select int4range(2, NULL); int4range [ 2, INF ) (1 row) but refuses to accept it in the string-form: select '[ 2 , NULL )'::int4range; ERROR: NULL range boundaries are not supported LINE 1: select '[ 2 , NULL )'::int4range; I think this might require more opinions. There is a trade-off here between convenience and confusion: accepting NULL is convenient in the constructors, because it avoids the need to have extra constructors just for unbounded ranges; but could lead to confusion between NULL and INF (which are not the same). In the string form, it doesn't add any convenience to accept NULL; but as you point out, it seems inconsistent without it. Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Cross-compile of 3rd-party extensions fails
Hi, I am trying to cross-compile PostgreSQL 9.0.4 and a few 3rd-party external modules using Mingw32-w64 on Fedora 15 for 64-bit Windows. 64-bit Wine can be used to run the 64-bit pg_config.exe under Linux, so the USE_PGXS=1 machinery works. However, mingw64-make fails with the following error: [zozo@localhost postgis-1.5.3]$ mingw64-make make -C liblwgeom make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make -C postgis make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' /home/zozo/.wine/dosdevices/z:/usr/x86_64-w64-mingw32/sys-root/mingw/lib/postgresql/pgxs/src/makefiles/../../src/Makefile.port:63: *** target pattern contains no `%'. Stop. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' make: *** [postgis] Error 2 Line 63 in Makefile.port (src/makefiles/Makefile.win32 in this case) is this: 63: win32ver.rc: $(top_srcdir)/src/port/win32ver.rc 64:sed -e 's;FILEDESC;$(PGFILEDESC);' -e 's;VFT_APP;$(PGFTYPE);' -e 's;_ICO_;$(PGICOSTR);' -e 's;\(VERSION.*\),0 *$$;\1,'`date '+%y%j' | sed 's/^0*//'`';' $ $@ 65: 66: win32ver.o: win32ver.rc 67: $(WINDRES) -i $ -o $@ --include-dir=$(top_builddir)/src/include --include-dir=$(srcdir) Do we actually need win32ver.rc for the external MODULES/MODULE_big/PROGRAM? win32ver.rc is not actually installed from the PostgreSQL source tree. Commenting out the above lines in the installed Makefile.port allows make to continue without errors. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] /proc/self/oom_adj is deprecated in newer Linux kernels
On sön, 2011-09-18 at 12:21 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On fre, 2011-09-16 at 10:57 -0400, Tom Lane wrote: So it looks like it behooves us to cater for oom_score_adj in the future. The simplest, least risky change that I can think of is to copy-and-paste the relevant #ifdef code block in fork_process.c. If we do that, then it would be up to the packager whether to #define LINUX_OOM_ADJ or LINUX_OOM_SCORE_ADJ or both depending on the behavior he wants. There are lots of reasons why that won't work: backports, forward ports, derivatives, custom kernels, distribution upgrades, virtual hosting. [ shrug... ] These are all hypothetical reasons. A packager who foresaw needing that could just turn on both write attempts, or for that matter patch the code to do whatever else he saw fit. In practice, we've not had requests for anything significantly smarter than what is there. But having said that, it wouldn't be very hard to arrange things so that if you did have both symbols defined, the code would only attempt to write oom_adj if it had failed to write oom_score_adj; which is about as close as you're likely to get to a kernel version test for this. Why is this feature not a run-time configuration variable or at least a configure option? It's awfully well hidden now. I doubt a lot of people are using this even though they might wish to. -- 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] Cross-compile of 3rd-party extensions fails
On mån, 2011-09-19 at 10:16 +0200, Boszormenyi Zoltan wrote: [zozo@localhost postgis-1.5.3]$ mingw64-make make -C liblwgeom make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make -C postgis make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' /home/zozo/.wine/dosdevices/z:/usr/x86_64-w64-mingw32/sys-root/mingw/lib/postgresql/pgxs/src/makefiles/../../src/Makefile.port:63: *** target pattern contains no `%'. Stop. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' make: *** [postgis] Error 2 Having a colon in a file name is not going to work with make. -- 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] Cross-compile of 3rd-party extensions fails
On Mon, Sep 19, 2011 at 11:27 AM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2011-09-19 at 10:16 +0200, Boszormenyi Zoltan wrote: [zozo@localhost postgis-1.5.3]$ mingw64-make make -C liblwgeom make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make -C postgis make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' /home/zozo/.wine/dosdevices/z:/usr/x86_64-w64-mingw32/sys-root/mingw/lib/postgresql/pgxs/src/makefiles/../../src/Makefile.port:63: *** target pattern contains no `%'. Stop. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' make: *** [postgis] Error 2 Having a colon in a file name is not going to work with make. I guess there is 'make' instead of '$(MAKE)' in Makefile somewhere? -- marko -- 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] Cross-compile of 3rd-party extensions fails
2011-09-19 10:27 keltezéssel, Peter Eisentraut írta: On mån, 2011-09-19 at 10:16 +0200, Boszormenyi Zoltan wrote: [zozo@localhost postgis-1.5.3]$ mingw64-make make -C liblwgeom make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/liblwgeom' make -C postgis make[1]: Entering directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' /home/zozo/.wine/dosdevices/z:/usr/x86_64-w64-mingw32/sys-root/mingw/lib/postgresql/pgxs/src/makefiles/../../src/Makefile.port:63: *** target pattern contains no `%'. Stop. make[1]: Leaving directory `/home/zozo/Schönig-számlák/w64/nsis/9.0/postgis-1.5.3/postgis' make: *** [postgis] Error 2 Having a colon in a file name is not going to work with make. Actually, it does work nicely. make has problems with a colon in the body of a target name (IIRC, it's invalid, anyway but may be accepted if escaped), but it doesn't complain if the filesystem give it filenames with colons in them. What it really complains about it the win32ver.rc: target in the Makefile.port. For cross-compiling postGIS, turns out that I only need the attached patches applied in /usr/x86_64-w64-mingw32/sys-root/mingw/lib/postgresql/pgxs/src on the PostgreSQL side along with similar OPTION = $(winepath -u `$PG_CONFIG --option` | dos2unix) treatments in the configure.ac where needed. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ --- Makefile.global.old 2011-09-15 10:27:20.0 +0200 +++ Makefile.global 2011-09-19 10:39:53.723918117 +0200 @@ -125,16 +125,16 @@ PG_CONFIG = pg_config endif -bindir := $(shell $(PG_CONFIG) --bindir) -datadir := $(shell $(PG_CONFIG) --sharedir) -sysconfdir := $(shell $(PG_CONFIG) --sysconfdir) -libdir := $(shell $(PG_CONFIG) --libdir) -pkglibdir := $(shell $(PG_CONFIG) --pkglibdir) -includedir := $(shell $(PG_CONFIG) --includedir) -pkgincludedir := $(shell $(PG_CONFIG) --pkgincludedir) -mandir := $(shell $(PG_CONFIG) --mandir) -docdir := $(shell $(PG_CONFIG) --docdir) -localedir := $(shell $(PG_CONFIG) --localedir) +bindir := $(shell winepath -u `$(PG_CONFIG) --bindir` | dos2unix) +datadir := $(shell winepath -u `$(PG_CONFIG) --sharedir` | dos2unix) +sysconfdir := $(shell winepath -u `$(PG_CONFIG) --sysconfdir` | dos2unix) +libdir := $(shell winepath -u `$(PG_CONFIG) --libdir` | dos2unix) +pkglibdir := $(shell winepath -u `$(PG_CONFIG) --pkglibdir` | dos2unix) +includedir := $(shell winepath -u `$(PG_CONFIG) --includedir` | dos2unix) +pkgincludedir := $(shell winepath -u `$(PG_CONFIG) --pkgincludedir` | dos2unix) +mandir := $(shell winepath -u `$(PG_CONFIG) --mandir` | dos2unix) +docdir := $(shell winepath -u `$(PG_CONFIG) --docdir` | dos2unix) +localedir := $(shell winepath -u `$(PG_CONFIG) --localedir` | dos2unix) endif # PGXS --- Makefile.port.old 2011-09-15 10:27:20.0 +0200 +++ Makefile.port 2011-09-19 10:12:32.247300770 +0200 @@ -60,11 +60,11 @@ PGICOSTR = $(subst /,\/,IDI_ICON ICON \$(top_builddir)/src/port/$(PGAPPICON).ico\) endif -win32ver.rc: $(top_srcdir)/src/port/win32ver.rc - sed -e 's;FILEDESC;$(PGFILEDESC);' -e 's;VFT_APP;$(PGFTYPE);' -e 's;_ICO_;$(PGICOSTR);' -e 's;\(VERSION.*\),0 *$$;\1,'`date '+%y%j' | sed 's/^0*//'`';' $ $@ - -win32ver.o: win32ver.rc - $(WINDRES) -i $ -o $@ --include-dir=$(top_builddir)/src/include --include-dir=$(srcdir) +#win32ver.rc: $(top_srcdir)/src/port/win32ver.rc +# sed -e 's;FILEDESC;$(PGFILEDESC);' -e 's;VFT_APP;$(PGFTYPE);' -e 's;_ICO_;$(PGICOSTR);' -e 's;\(VERSION.*\),0 *$$;\1,'`date '+%y%j' | sed 's/^0*//'`';' $ $@ +# +#win32ver.o: win32ver.rc +# $(WINDRES) -i $ -o $@ --include-dir=$(top_builddir)/src/include --include-dir=$(srcdir) # Rule for building a shared library from a single .o file %.dll: %.o -- 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] Adding CORRESPONDING to Set Operations
Is it feasible to implement the CORRESPONDING [BY (expr_list)] statement in set operations by the following changes: i) In analyze.c:transformSetOperationStmt after parsing left and right queries as subnodes to a set operation tree, a) CORRESPONDING: Find matching column targets from both statements, eliminate unmatching targets and proceed. b) CORRESPONDING BY (expr_list): Verify expr_list columns exist in both select statements. Eliminate unmatched column names to expr_list and proceed. ii) Instead of elimination set TargetEntry-resjunk = true for unwanted output columns. Thank you for your attention, Any comments are welcome. Kerem KAT On Sun, Sep 18, 2011 at 12:39, Kerem Kat kerem...@gmail.com wrote: Hello, I am new to postgresql code, I would like to start implementing easyish TODO items. I have read most of the development guidelines, faqs, articles by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers). The item I would like to implement is adding CORRESPONDING [BY (col1[,col2,...]])] to INTERSECT and EXCEPT operators. Can anyone comment on how much effort this item needs? regards, kerem kat.
[HACKERS] CUDA Sorting
Hello everyone, I'm implementing a CUDA based sorting on PostgreSQL, and I believe it can improve the ORDER BY statement performance in 4 to 10 times. I already have a generic CUDA sort that performs around 10 times faster than std qsort. I also managed to load CUDA into pgsql. Since I'm new to pgsql development, I replaced the code of pgsql qsort_arg to get used with the way postgres does the sort. The problem is that I can't use the qsort_arg_comparator comparator function on GPU, I need to implement my own. I didn't find out how to access the sorting key value data of the tuples on the Tuplesortstate or SortTuple structures. This part looks complicated because it seems the state holds the pointer for the scanner(?), but I didn't managed to access the values directly. Can anyone tell me how this works? Cheers, Vítor -- 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 September 2011 13:11, Vitor Reus vitor.r...@gmail.com wrote: Hello everyone, I'm implementing a CUDA based sorting on PostgreSQL, and I believe it can improve the ORDER BY statement performance in 4 to 10 times. I already have a generic CUDA sort that performs around 10 times faster than std qsort. I also managed to load CUDA into pgsql. Since I'm new to pgsql development, I replaced the code of pgsql qsort_arg to get used with the way postgres does the sort. The problem is that I can't use the qsort_arg_comparator comparator function on GPU, I need to implement my own. I didn't find out how to access the sorting key value data of the tuples on the Tuplesortstate or SortTuple structures. This part looks complicated because it seems the state holds the pointer for the scanner(?), but I didn't managed to access the values directly. Can anyone tell me how this works? I can't help with explaining the inner workings of sorting code, but just a note that CUDA is a proprietary framework from nVidia and confines its use to nVidia GPUs only. You'd probably be better off investing in the OpenCL standard which is processor-agnostic. Work has already been done in this area by Tim Child with pgOpenCL, although doesn't appear to be available yet. It might be worth engaging with him to see if there are commonalities to what you're both trying to achieve. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Improve lseek scalability v3
* Benjamin LaHaise (b...@kvack.org) wrote: For such tables, can't Postgres track the size of the file internally? I'm assuming it's keeping file descriptors open on the tables it manages, in which case when it writes to a file to extend it, the internally stored size could be updated. Not making a syscall at all would scale far better than even a modified lseek() will perform. We'd have to have it in shared memory and have a lock around it, it wouldn't be cheap at all. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Improve lseek scalability v3
On Mon, Sep 19, 2011 at 8:31 AM, Stephen Frost sfr...@snowman.net wrote: * Benjamin LaHaise (b...@kvack.org) wrote: For such tables, can't Postgres track the size of the file internally? I'm assuming it's keeping file descriptors open on the tables it manages, in which case when it writes to a file to extend it, the internally stored size could be updated. Not making a syscall at all would scale far better than even a modified lseek() will perform. We'd have to have it in shared memory and have a lock around it, it wouldn't be cheap at all. In theory, we could implement a lock-free cache. But I still think it would be better to see this fixed on the kernel side. If we had some evidence that all of those lseek() calls were a performance problem even when the i_mutex is not seriously contended, then that would be a good argument for doing this in user-space, but I haven't seen any such evidence. On the other hand, the numbers I posted show that when i_mutex IS contended, it can cause a throughput regression of up to 90%. That seems worth fixing. If it turns out that lseek() is too expensive even in the uncontended case or with the i_mutex contention removed (or if the Linux community is unwilling to accept the proposed fix), then we can (and should) look at further optimizing it within PostgreSQL. My guess, though, is that an unlocked lseek will be fast enough that we won't need to worry about installing our own caching infrastructure (or at least, there will be plenty of more significant performance problems to hunt down first). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis pg...@j-davis.com wrote: select '[ 2 , NULL )'::int4range; ERROR: NULL range boundaries are not supported LINE 1: select '[ 2 , NULL )'::int4range; I think this might require more opinions. There is a trade-off here between convenience and confusion: accepting NULL is convenient in the constructors, because it avoids the need to have extra constructors just for unbounded ranges; but could lead to confusion between NULL and INF (which are not the same). I agree with this line of reasoning. I think we will be making pain for ourselves if we need to invent a bunch more constructors just to have a way of indicating an unbounded range, but OTOH I don't see any compelling reason why the type input function needs to accept N-U-L-L. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 September 2011 14:32, Vitor Reus vitor.r...@gmail.com wrote: 2011/9/19 Thom Brown t...@linux.com: On 19 September 2011 13:11, Vitor Reus vitor.r...@gmail.com wrote: Hello everyone, I'm implementing a CUDA based sorting on PostgreSQL, and I believe it can improve the ORDER BY statement performance in 4 to 10 times. I already have a generic CUDA sort that performs around 10 times faster than std qsort. I also managed to load CUDA into pgsql. Since I'm new to pgsql development, I replaced the code of pgsql qsort_arg to get used with the way postgres does the sort. The problem is that I can't use the qsort_arg_comparator comparator function on GPU, I need to implement my own. I didn't find out how to access the sorting key value data of the tuples on the Tuplesortstate or SortTuple structures. This part looks complicated because it seems the state holds the pointer for the scanner(?), but I didn't managed to access the values directly. Can anyone tell me how this works? I can't help with explaining the inner workings of sorting code, but just a note that CUDA is a proprietary framework from nVidia and confines its use to nVidia GPUs only. You'd probably be better off investing in the OpenCL standard which is processor-agnostic. Work has already been done in this area by Tim Child with pgOpenCL, although doesn't appear to be available yet. It might be worth engaging with him to see if there are commonalities to what you're both trying to achieve. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company Hi Thom Brown, thank you very much for your reply. I am aware that CUDA is a proprietary framework, but since the high level CUDA API is easier than OpenCL, it will be faster to implement and test. Also, CUDA can be translated to OpenCL in a straightforward way, since the low level CUDA API generated code is really similar to OpenCL. I'll try engaging with Tim Child, but it seems that his work is to create GPU support for specific SQL, like procedural SQL statements with CUDA extensions, did I understand it right? And my focus is to unlock the GPU power without the user being aware of this. Please use Reply To All in your responses so the mailing list is included. Is your aim to have this committed into core PostgreSQL, or just for your own version? If it's the former, I don't anticipate any enthusiasm from the hacker community. But you're right, Tim Child's work is aimed at procedural acceleration rather than speeding up core functionality (from what I gather anyway). -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Is there really no interest in SQL Standard?
+1 for a closed mailing list. It's a bit annoying to have to do such a thing, but it's not like we haven't got other closed lists for appropriate purposes. I guess the real question is, exactly what will be the requirements for joining? Well, one requirement would be agreeing not to share anything discussed in public without a vote of the entire group. Annoying, but that's how confidential drafts go. FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Also, Suzanne indicated that summaries of what features were being discussed could be posted in public, even if details could not be. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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 Mon, Sep 19, 2011 at 1:11 PM, Vitor Reus vitor.r...@gmail.com wrote: Since I'm new to pgsql development, I replaced the code of pgsql qsort_arg to get used with the way postgres does the sort. The problem is that I can't use the qsort_arg_comparator comparator function on GPU, I need to implement my own. I didn't find out how to access the sorting key value data of the tuples on the Tuplesortstate or SortTuple structures. This part looks complicated because it seems the state holds the pointer for the scanner(?), but I didn't managed to access the values directly. Can anyone tell me how this works? This is something I've been curious about for a while. The biggest difficulty is that Postgres has a user-extensible type system and calls user provided functions to do things like comparisons. Postgres only supports comparison sorts and does so by calling the user function for the data type being sorted. These user defined function is looked up earlier in the query parsing and analysis phase and stored in Tuplesortstate-scanKeys which is an array of structures that hold information about the ordering required. In there there's a pointer to the function, a set of flags (such as NULLS FIRST/LAST), the text collation needed and the collation. I assume you're going to have to have tuplesort.c recognize if all the comparators are one of a small set of standard comparators that you can implement on the GPU such as integer and floating point comparison. In which case you could call a specialized qsort which implements that comparator inlined instead of calling the standard function. That might actually be a useful optimization to do anyways since it may well be much faster even without the GPU. So that would probably be a good place to start. But the barrier to get over here might be relatively high. In order to tolerate that amount of duplicated code and special cases there would have to be benchmarks showing it's significantly faster and helps real-world user queries. It would also have to be pretty cleanly implemented so that it doesn't impose a lot of extra overhead every time this code needs to be changed -- for example when adding collations it would have been unfortunate to have to add it to half a dozen specializations of tuplesort (though frankly I don't think that would have made that much of a dent in the happiness of the people who worked on collations). All that said my personal opinion is that this can be done cleanly and would be more than worth the benefit even without the GPU -- sorting integers and floating point numbers is a very common case and Peter Geoghan recently showed our qsort could be about twice as fast if it could inline the comparisons. 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. -- greg -- 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] /proc/self/oom_adj is deprecated in newer Linux kernels
Peter Eisentraut pete...@gmx.net writes: On sön, 2011-09-18 at 12:21 -0400, Tom Lane wrote: But having said that, it wouldn't be very hard to arrange things so that if you did have both symbols defined, the code would only attempt to write oom_adj if it had failed to write oom_score_adj; which is about as close as you're likely to get to a kernel version test for this. Why is this feature not a run-time configuration variable or at least a configure option? It's awfully well hidden now. I doubt a lot of people are using this even though they might wish to. See the thread in which the feature was designed originally: http://archives.postgresql.org/pgsql-hackers/2010-01/msg00170.php The key point is that to get useful behavior, you need cooperation between both a root-privileged startup script and the PG executable. That tends to throw the problem into the domain of packagers, more than end users, and definitely puts a big crimp in the idea that run-time configuration of just half of the behavior could be helpful. So far, no Linux packagers have complained that the design is inadequate (a position that I also hold when wearing my red fedora) so I do not feel a need to complicate it further. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A little pg_dump patch
Hi, I'm Enrico, I wrote a little pg_dump patch, I have introduced a new option to have a database dump without comments, no 'COMMENT ON' are written on the dump if my new option is selected. If this little piece of code can interest somebody, I'll be happy to share it with the community, Regards, Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 Gtalk sscott...@gmail.com -- 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 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. I've never seen a PostgreSQL server capable of running CUDA, and I don't expect that to change. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Generating a query that never returns
I have a need to test timeouts in JDBC, is there a query that is guaranteed not to return ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- 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 September 2011 15:36, Greg Smith g...@2ndquadrant.com 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. I've never seen a PostgreSQL server capable of running CUDA, and I don't expect that to change. But couldn't that also be seen as a chicken/egg situation? No-one buys GPUs for database servers because the database won't make use of it, but databases don't implement GPU functionality since database servers don't tend to have GPUs. It's more likely the latter of those two reasonings would have to be the first to budge. But nVidia does produce a non-graphics-oriented GPGPU line called Tesla dedicated to such processing. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 Mon, Sep 19, 2011 at 3:36 PM, Greg Smith g...@2ndquadrant.com wrote: 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 Of course that could change if adding a GPU would help Postgres... I would expect it to help mostly for data warehouse batch query type systems, especially ones with very large i/o subsystems that can saturate the memory bus with sequential i/o. Run your large batch queries twice as fast by adding a $400 part to your $40,000 server might be a pretty compelling sales pitch :) That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I expect someone has implemented heaps for CUDA/OpenCL already though. -- greg -- 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] PATCH: regular logging of checkpoint progress
On 09/05/2011 07:52 PM, Tomas Vondra wrote: If your logging criteria for the write phase was display a message any time more than 30 seconds have passed since last seeing one, that would give you only a few lines of output in a boring, normal checkpoint--certainly less than the 9 in-progress samples you're outputting now, at 10% intervals. But in the pathological situations where writes are super slow, your log data would become correspondingly denser, which is exactly what you want in that situation. I still am not sure what should be a reasonable value or how to determine it. What happens when the checkpoint_timeout is increased, there's more shared_buffers etc.? What about using (checkpoint_timeout/10) for the time-based checkpoints and 30s for the other checkpoints? That may work fine. Maybe implement it like that, and see if the amount of logging detail is reasonable in a couple of test scenarios. I think combining the two makes the most sense: log when=30 seconds have passed since the last message, and there's been=10% more progress made. (Maybe do the progress check before the time one, to cut down on Is this is a good idea? The case when the timeout expires and not much data was written is interesting, and this would not log it. But OTOH this would nicely solve the issue with time-based checkpoints and a fixed threshold. One thing I am trying to avoid here is needing to check the system clock after every buffer write. I also consider it useful to put an upper bound on how many of these messages will appear even in the verbose mode. This deals with both those problems. Yes, there is a potential problem with this idea. Let's say checkpoint writes degrade to where they take an hour. In that case, you won't see the first progress report until 6 minutes (10%) have gone by with this implementation. I don't see a good way to resolve that without violating one of the other priorities I listed above though. You'll have to poll the system clock constantly and will end up creating a lot of log entries if you don't do a check against the % progress first. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Is there really no interest in SQL Standard?
On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. Joe -- 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] Generating a query that never returns
Dave Cramer p...@fastcrypt.com writes: I have a need to test timeouts in JDBC, is there a query that is guaranteed not to return ? You could just do an unconstrained join between several large tables. Or select pg_sleep(largevalue), depending on whether you'd like the backend to be spitting data at you or not, regards, tom lane -- 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] A little pg_dump patch
On 09/19/2011 10:34 AM, Enrico Pirozzi wrote: Hi, I'm Enrico, I wrote a little pg_dump patch, I have introduced a new option to have a database dump without comments, no 'COMMENT ON' are written on the dump if my new option is selected. If this little piece of code can interest somebody, I'll be happy to share it with the community, We had a recent discussion about filtering items in pg_dump / pg_restore. My personal feeling is that we should provide filtering of table data ion pg_dump, and pre-data and post-data sections in both, all of which we know there are good and widespread use cases for, but beyond that users should filter using pg_dump -F c and pg_restore --use-list. If we try to build in every kind of filter any user might want we'll just become horribly bloated. What's the use case for not dumping comments? At first glance it seems a very odd thing to do. cheers andrew -- 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] Generating a query that never returns
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
I have a need to test timeouts in JDBC, is there a query that is
guaranteed not to return ?
Not *never*, but close enough:
select pg_sleep();
Or if you want to be strict:
CREATE FUNCTION noreturn()
RETURNS VOID
LANGUAGE plperl
AS $$ while (1) { select (undef,undef,undef,0.1) } $$;
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201109191104
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAk53WvYACgkQvJuQZxSWSsiItACg+BXmjoR9ecJWuU/AOka+/CBX
rAcAoOQi0MhHk0cWp2aFc87yvZOyY5T1
=wnlW
-END PGP SIGNATURE-
--
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 September 2011 15:54, Greg Stark st...@mit.edu wrote: On Mon, Sep 19, 2011 at 3:36 PM, Greg Smith g...@2ndquadrant.com wrote: 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 Of course that could change if adding a GPU would help Postgres... I would expect it to help mostly for data warehouse batch query type systems, especially ones with very large i/o subsystems that can saturate the memory bus with sequential i/o. Run your large batch queries twice as fast by adding a $400 part to your $40,000 server might be a pretty compelling sales pitch :) That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I expect someone has implemented heaps for CUDA/OpenCL already though. I seem to recall a paper on such a thing by Carnegie Mellon University. Can't remember where I saw it though. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Generating a query that never returns
On Sep19, 2011, at 16:48 , Dave Cramer wrote: I have a need to test timeouts in JDBC, is there a query that is guaranteed not to return ? WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT * FROM infinite) SELECT * FROM infinite If you declare a cursor for this statement, it will return infinitely many rows (all containing the value 1). If stick a ORDER BY value clause at the end of the statement, then the first FETCH from the cursor will hang (since it'll attempt to materialize the infinitely many rows returns by the cursor). My first try, BTW, was WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT 1) SELECT * FROM infinite but that returns only two rows. I'd have expected it to returns an infinite stream of 1s as well, since the iteration part of the recursive CTE never returns zero rows. The behaviour I get is what I'd have expected if I had written UNION instead of UNION ALL. Am I missing something, or is that a genuine bug? Just FYI, this question should probably have gone to -general, not -hackers. best regards, Florian Pflug -- 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 September 2011 16:10, Thom Brown t...@linux.com wrote: On 19 September 2011 15:54, Greg Stark st...@mit.edu wrote: On Mon, Sep 19, 2011 at 3:36 PM, Greg Smith g...@2ndquadrant.com wrote: 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 Of course that could change if adding a GPU would help Postgres... I would expect it to help mostly for data warehouse batch query type systems, especially ones with very large i/o subsystems that can saturate the memory bus with sequential i/o. Run your large batch queries twice as fast by adding a $400 part to your $40,000 server might be a pretty compelling sales pitch :) That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I expect someone has implemented heaps for CUDA/OpenCL already though. I seem to recall a paper on such a thing by Carnegie Mellon University. Can't remember where I saw it though. Found it! http://www.cs.cmu.edu/afs/cs.cmu.edu/Web/People/ngm/15-823/project/Final.pdf -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Is there really no interest in SQL Standard?
On 19.09.2011 15:50, Josh Berkus wrote: +1 for a closed mailing list. It's a bit annoying to have to do such a thing, but it's not like we haven't got other closed lists for appropriate purposes. I guess the real question is, exactly what will be the requirements for joining? Well, one requirement would be agreeing not to share anything discussed in public without a vote of the entire group. Annoying, but that's how confidential drafts go. Exactly. Honestly, I don't expect that it will get a big group. It is very dry stuff. FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. +1 - But all suggestions to change it got rejected. Also, Suzanne indicated that summaries of what features were being discussed could be posted in public, even if details could not be. I looked into it - I fear it will get too gibberish. You sometimes just need the details. Also - just forwarding it - is much easier and less time intensive for me. Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com -- 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] PATCH: regular logging of checkpoint progress
Greg Smith g...@2ndquadrant.com writes: One thing I am trying to avoid here is needing to check the system clock after every buffer write. On machines where gettimeofday is slow (and last I heard there were still lots of them), any such thing would be a disaster performance-wise. I'm still afraid to add more gettimeofday's into the query parse/plan/execute code path, even though it would greatly ease the problem of figuring out whether re-planning is worthwhile. regards, tom lane -- 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
Greg Stark st...@mit.edu 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 ... FWIW, I tend to believe a variant of what Greg Stark said upthread: there would surely be some win from reducing the impedance mismatch for comparison functions. In concrete terms, there would be no reason to have tuplesort.c's myFunctionCall2Coll, and maybe not inlineApplySortFunction either, if the datatype-specific comparison functions had APIs that were closer to what sorting wants rather than following the general SQL-callable-function API. And those functions cost a *lot* more than a one-instruction comparison does. But it's very much more of a stretch to believe that inlining per se is going to do much for us, and even more of a stretch to believe that getting a separate processor involved is going to be a win. regards, tom lane -- 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] A little pg_dump patch
What's the use case for not dumping comments? At first glance it seems a very odd thing to do. cheers andrew I wrote this little patch, becuse my customer doesn't want to have comments on the production db. It's not my choice Regards, Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 Gtalk sscott...@gmail.com -- 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] Range Types - typo + NULL string constructor
On Sep19, 2011, at 15:33 , Robert Haas wrote: On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis pg...@j-davis.com wrote: select '[ 2 , NULL )'::int4range; ERROR: NULL range boundaries are not supported LINE 1: select '[ 2 , NULL )'::int4range; I think this might require more opinions. There is a trade-off here between convenience and confusion: accepting NULL is convenient in the constructors, because it avoids the need to have extra constructors just for unbounded ranges; but could lead to confusion between NULL and INF (which are not the same). I agree with this line of reasoning. I think we will be making pain for ourselves if we need to invent a bunch more constructors just to have a way of indicating an unbounded range, but OTOH I don't see any compelling reason why the type input function needs to accept N-U-L-L. The one reason I can see in favour of supporting N-U-L-L there is compatibility with arrays. I've recently had the questionable pleasure of writing PHP functions to parse and emit our textual representations of arrays, records, dates and timestamps. After that experience, I feel that the number of similar-yet-slightly-different textual input output format for non-primitive types is already excessive, and any further additions should be modeled after some existing ones. (And BTW, why in heavens sake, is date and time input and output asymmetric for some DateStyle settings? Asymmetric like in you need to send one format, but get back another...) best regards, Florian Pflug -- 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] A little pg_dump patch
On 09/19/2011 11:23 AM, Enrico Pirozzi wrote: What's the use case for not dumping comments? At first glance it seems a very odd thing to do. I wrote this little patch, becuse my customer doesn't want to have comments on the production db. It's not my choice Then use pg_restore --use-list to filter them out, and you won't need a single line of C code to achieve your goal. The --use-list option gives you very fine grained control over what gets restoresd and what does not. Patching pg_dump like this seems like the wrong way to go. cheers andrew -- 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] A little pg_dump patch
Patching pg_dump like this seems like the wrong way to go. Ok ;) Thank you very much Enrico -- That's one small step for man; one giant leap for mankind -- 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] A little pg_dump patch
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I wrote this little patch, becuse my customer doesn't want to have comments on the production db. It's not my choice Then use pg_restore --use-list to filter them out, and you won't need a Or just strip them out after the fact with a little bit of SQL, e.g. DELETE FROM pg_description WHERE objoid 5; (test first, your system may vary and 5 may not work) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201109191143 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk53YzsACgkQvJuQZxSWSsgA0wCguiURJI4Hneu7Dn+vBtcEOLsR /OcAn35ujJCWhf2tM3a1J2yek0uddF1X =wc3x -END PGP SIGNATURE- -- 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] Range Types - typo + NULL string constructor
On Mon, 2011-09-19 at 17:23 +0200, Florian Pflug wrote: The one reason I can see in favour of supporting N-U-L-L there is compatibility with arrays. But arrays actually do store and produce NULLs; ranges don't. I've recently had the questionable pleasure of writing PHP functions to parse and emit our textual representations of arrays, records, dates and timestamps. After that experience, I feel that the number of similar-yet-slightly-different textual input output format for non-primitive types is already excessive, and any further additions should be modeled after some existing ones. I'm not clear on how accepting NULL would really save effort. With ranges, the brackets have an actual meaning (inclusivity), and empty ranges have no brackets at all. So I don't think it's going to be easy to write one function to parse everything. What about binary formats? Regards, Jeff Davis -- 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
2011/9/19 Thom Brown t...@linux.com Is your aim to have this committed into core PostgreSQL, or just for your own version? If it's the former, I don't anticipate any enthusiasm from the hacker community. This is a research thesis and I'm not confident to commit it on the core just by myself. I will, however, release the source, and I believe it will open the way to future work be committed on core PostgreSQL. 2011/9/19 Greg Stark st...@mit.edu Of course that could change if adding a GPU would help Postgres... I would expect it to help mostly for data warehouse batch query type systems, especially ones with very large i/o subsystems that can saturate the memory bus with sequential i/o. Run your large batch queries twice as fast by adding a $400 part to your $40,000 server might be a pretty compelling sales pitch :) My focus is also energy proportionality. If you add a GPU, you will increase the power consumption in about 2 times, but perhaps could increse the efficiency much more. That said, to help in the case I described you would have to implement the tapesort algorithm on the GPU as well. I expect someone has implemented heaps for CUDA/OpenCL already though. For now, I'm planning to implement just the in-memory sort, for simplicity and to see if it would give a real performance gain. 2011/9/19 Greg Stark st...@mit.edu: In which case you could call a specialized qsort which implements that comparator inlined instead of calling the standard function. Actually I'm now trying to make a custom comparator for integers, but I didn't had great progress. If this works, I'll port it to GPU and start working with the next comparators, such as float, then strings, in a incremental way. 2011/9/19 Thom Brown t...@linux.com: Found it! http://www.cs.cmu.edu/afs/cs.cmu.edu/Web/People/ngm/15-823/project/Final.pdf This is a really great work, and I'm basing mine on it. But it's implemented using OpenGL (yes, not OpenCL), and therefore has a lot of limitations. I also tried to contact naju but didn't get any answer. Vítor Uwe Reus -- 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] A little pg_dump patch
Ok, but with this hack I need 2 databases, one with comments and another without comments. I prefer to have only one db with comments and choice to have a dump with comments or without comments. regards, Enrico 2011/9/19 Greg Sabino Mullane g...@turnstep.com: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I wrote this little patch, becuse my customer doesn't want to have comments on the production db. It's not my choice Then use pg_restore --use-list to filter them out, and you won't need a Or just strip them out after the fact with a little bit of SQL, e.g. DELETE FROM pg_description WHERE objoid 5; (test first, your system may vary and 5 may not work) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201109191143 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk53YzsACgkQvJuQZxSWSsgA0wCguiURJI4Hneu7Dn+vBtcEOLsR /OcAn35ujJCWhf2tM3a1J2yek0uddF1X =wc3x -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- That's one small step for man; one giant leap for mankind -- 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] Generating a query that never returns
On Mon, Sep 19, 2011 at 05:12:15PM +0200, Florian Pflug wrote: On Sep19, 2011, at 16:48 , Dave Cramer wrote: I have a need to test timeouts in JDBC, is there a query that is guaranteed not to return ? WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT * FROM infinite) SELECT * FROM infinite If you declare a cursor for this statement, it will return infinitely many rows (all containing the value 1). If stick a ORDER BY value clause at the end of the statement, then the first FETCH from the cursor will hang (since it'll attempt to materialize the infinitely many rows returns by the cursor). My first try, BTW, was WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT 1) SELECT * FROM infinite but that returns only two rows. I'd have expected it to returns an infinite stream of 1s as well, since the iteration part of the recursive CTE never returns zero rows. The behaviour I get is what I'd have expected if I had written UNION instead of UNION ALL. Am I missing something, or is that a genuine bug? That's actually the correct behavior. In order to get a recursion (or iteration, whichever way you want to look at it), you need to refer to the CTE on the right side of the UNION [ALL] (or the INTERSECT [ALL] per the SQL standard). Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Range Types - typo + NULL string constructor
Robert Haas robertmh...@gmail.com wrote: Jeff Davis pg...@j-davis.com wrote: select '[ 2 , NULL )'::int4range; ERROR: NULL range boundaries are not supported LINE 1: select '[ 2 , NULL )'::int4range; I think this might require more opinions. There is a trade-off here between convenience and confusion: accepting NULL is convenient in the constructors, because it avoids the need to have extra constructors just for unbounded ranges; but could lead to confusion between NULL and INF (which are not the same). I agree with this line of reasoning. I think we will be making pain for ourselves if we need to invent a bunch more constructors just to have a way of indicating an unbounded range, but OTOH I don't see any compelling reason why the type input function needs to accept N-U-L-L. FWIW, the existing semantics of NULL include not only UNKNOWN but also NOT APPLICABLE. It seems fairly natural to think of a range as being unbounded if the boundary limit is not applicable. On a practical level, our shop is already effectively doing this. We have several tables where part of the primary key is effective date and there is a null capable expiration date -- with a NULL meaning that no expiration date has been set. It would be nice to be able to have a generated column function which used these two dates to build a range for exclusion constraints and such. -Kevin -- 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] Generating a query that never returns
On Sep19, 2011, at 17:59 , David Fetter wrote: On Mon, Sep 19, 2011 at 05:12:15PM +0200, Florian Pflug wrote: My first try, BTW, was WITH RECURSIVE infinite(value) AS (SELECT 1 UNION ALL SELECT 1) SELECT * FROM infinite but that returns only two rows. I'd have expected it to returns an infinite stream of 1s as well, since the iteration part of the recursive CTE never returns zero rows. The behaviour I get is what I'd have expected if I had written UNION instead of UNION ALL. Am I missing something, or is that a genuine bug? That's actually the correct behavior. In order to get a recursion (or iteration, whichever way you want to look at it), you need to refer to the CTE on the right side of the UNION [ALL] (or the INTERSECT [ALL] per the SQL standard). Interesting. Thanks for the explanation! best regards, Florian Pflug -- 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] Is there really no interest in SQL Standard?
On Mon, Sep 19, 2011 at 10:58:49AM -0400, Joe Abbate wrote: On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. That's probably not a bad idea. The down side is that it'll be the work of decades, not years, to get this thing going. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 Mon, Sep 19, 2011 at 7:11 AM, Vitor Reus vitor.r...@gmail.com wrote: Hello everyone, I'm implementing a CUDA based sorting on PostgreSQL, and I believe it can improve the ORDER BY statement performance in 4 to 10 times. I already have a generic CUDA sort that performs around 10 times faster than std qsort. I also managed to load CUDA into pgsql. NVIDIA cards are not that good as ATI cards. ATI cards are much faster with integer operations, and should be ideal for sorting transaction ids or sort of similar numbers (unless you are going to sort prices stored as float, which ATI still beats NVIDIA but not by that much) Another problem you have to deal with is PCI Express speed. Transfer is very slow compared to RAM. You will have to put more GPUs to match the performance and this will increase solution cost. There was a sorting algorithm for 4 CPU cores that was beating sort on a GTX 285 (I don't have the link, sorry), but CPUs are not that bad with sorting like you think. AMD is already working with embedding GPUs into the motherboard, if I am not mistaken there are already some of them on the market available for purchase. Anyone who uses a tiny embedded ATI for sorting problems with integers will outperform your NVIDIA based PCI-Express connected GPU with CUDA, because basically your algorithm will waste a lot of time transfering data to GPU and getting it back. But if you use embedded ATI GPU , you can also use SSE registers on each CPU core to add more performance to your algorithm. It is not going to be a very hardware compatible solution but if you want good speed/cost, this should be the best solution. I recommend doing some bandwidth benchmark test before you start coding. Regards Nulik -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- == The power of zero is infinite -- 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 Mon, Sep 19, 2011 at 10:36 AM, Greg Smith g...@2ndquadrant.com wrote: 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. Note that Amazon's EC2 offerings include a configuration with a pair of GPUs. Whether or not this continues has a certain chicken and egg aspect to it... - I'm glad that Amazon is selling such a configuration, as it does give folks the option of trying it out. - Presumably, it will only continue on their product list if customers do more than merely trying it out. I think I'd be shocked if PostgreSQL offered much support for such a configuration in the next year; despite there being some work ongoing, drawing the functionality into core would require Core decisions that I'd be surprised to see so quickly. Unfortunately, that may be slow enough progress that PostgreSQL won't be contributing to the would-be success of the technology. If this kind of GPU usage fails to attract much interest, then it's probably a good thing that we're not committed to it. But if other uses lead to it taking off, then we'll doubtless get a lot of noise on lists about a year from now to the effect Why don't you have this in core yet? Not 3773t enough!?!? Having a bit of progress taking place now would probably be good timing, in case it *does* take off... -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] Range Types - typo + NULL string constructor
On Mon, Sep 19, 2011 at 11:23 AM, Florian Pflug f...@phlo.org wrote: On Sep19, 2011, at 15:33 , Robert Haas wrote: On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis pg...@j-davis.com wrote: select '[ 2 , NULL )'::int4range; ERROR: NULL range boundaries are not supported LINE 1: select '[ 2 , NULL )'::int4range; I think this might require more opinions. There is a trade-off here between convenience and confusion: accepting NULL is convenient in the constructors, because it avoids the need to have extra constructors just for unbounded ranges; but could lead to confusion between NULL and INF (which are not the same). I agree with this line of reasoning. I think we will be making pain for ourselves if we need to invent a bunch more constructors just to have a way of indicating an unbounded range, but OTOH I don't see any compelling reason why the type input function needs to accept N-U-L-L. The one reason I can see in favour of supporting N-U-L-L there is compatibility with arrays. I've recently had the questionable pleasure of writing PHP functions to parse and emit our textual representations of arrays, records, dates and timestamps. After that experience, I feel that the number of similar-yet-slightly-different textual input output format for non-primitive types is already excessive, and any further additions should be modeled after some existing ones. Well, I'm not violently opposed to accepting NULL to mean an unbounded range. The semantics of no bound at all and an unknown bound (i.e. NULL) are pretty close, as Kevin also points out downthread. But I think the way Jeff actually did it is OK, too. What I really care about is that we don't talk ourselves into needing a zillion constructor functions. Making things work with a single constructor function seems to me to simplify life quite a bit, and allowing there seems essential for that. (I am also vaguely wondering what happens if if you have a text range is (nubile, null) ambiguous?) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On Sep19, 2011, at 17:46 , Jeff Davis wrote:
On Mon, 2011-09-19 at 17:23 +0200, Florian Pflug wrote:
The one reason I can see in favour of supporting N-U-L-L there is
compatibility with arrays.
But arrays actually do store and produce NULLs; ranges don't.
Hm, yeah, granted. But OTOH, clients will very likely use NULL, null, nil
or something similar as a bound to represent unbounded ranges. And those
will probably already be mapped to SQL's NULL. So in practice, people will
think of unbounded ranges having the (lower or upper) bound NULL I think.
I've recently had the questionable pleasure
of writing PHP functions to parse and emit our textual representations of
arrays, records, dates and timestamps. After that experience, I feel that
the number of similar-yet-slightly-different textual input output format
for non-primitive types is already excessive, and any further additions
should be modeled after some existing ones.
I'm not clear on how accepting NULL would really save effort. With
ranges, the brackets have an actual meaning (inclusivity), and empty
ranges have no brackets at all. So I don't think it's going to be easy
to write one function to parse everything.
No, but more similar the format are the easier it gets to at least factor
the hairy parts of such a parser into a common subroutine. Assume that we
don't support NULL as an alias for INF. What would then be the result of
'[A,NULL)'::textrange?
Presumably, it'd be the same as textrange('A','NULL','[)'). Which think
is a bit surprising, since '[A,NULL]'::text[] produces ARRAY['A',NULL],
*NOT* ARRAY['A','NULL'].
BTW, we currently represent infinity for floating point values as
'Infinity', not 'INF'. Shouldn't we do the same for ranges, i.e. make
int4range(0,NULL,'[)')::text
return
'[0,Infinity)'?
best regards,
Florian Pflug
--
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] Is there really no interest in SQL Standard?
On 09/19/2011 12:20 PM, David Fetter wrote: On Mon, Sep 19, 2011 at 10:58:49AM -0400, Joe Abbate wrote: On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. That's probably not a bad idea. The down side is that it'll be the work of decades, not years, to get this thing going. Frankly, whether or not it might be desirable, it strikes me as probably simply tilting at windmills. cheers andrew -- 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] Is there really no interest in SQL Standard?
On Mon, Sep 19, 2011 at 12:20 PM, David Fetter da...@fetter.org wrote: On Mon, Sep 19, 2011 at 10:58:49AM -0400, Joe Abbate wrote: On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. That's probably not a bad idea. The down side is that it'll be the work of decades, not years, to get this thing going. Actually, I think it *is* a bad idea, as it would require construction from whole cloth of kinds of mostly political infrastructure that we don't have, as a community and aren't necessarily notably competent to construct. The nearest sort of thing that *could* conceivably be sensible would be to participate in UnQL http://www.unqlspec.org/display/UnQL/Home. That's early enough in its process that it's likely somewhat guidable, and, with the popularity of NoSQL, being at the ground breaking of a common query language to access that would likely be useful to us. If we wanted to start a new standards process, I imagine it would best involve embracing truly relational, stepping back to PostQUEL, and promoting a standard based on something off more in that direction. As much as that might sound like a terrible idea, trying to take over SQL by forking it strikes me as a much *worse* idea. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] Range Types - typo + NULL string constructor
On Mon, 2011-09-19 at 11:00 -0500, Kevin Grittner wrote: On a practical level, our shop is already effectively doing this. We have several tables where part of the primary key is effective date and there is a null capable expiration date -- with a NULL meaning that no expiration date has been set. It would be nice to be able to have a generated column function which used these two dates to build a range for exclusion constraints and such. Agreed, that's a good convenience argument for accepting NULL boundaries in the constructors. Underneath though, we don't use NULL semantics (because they don't make sense for ranges -- in fact, avoiding the need to constantly special-case NULLs is one of the reasons to use range types). So, we want to avoid confusion where possible. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Review for EXPLAIN and nfiltered
Here is my review for EXPLAIN and nfiltered (http://archives.postgresql.org/message-id/4e6e9e83.7070...@2ndquadrant.com) - Is the patch in context diff format? It's in git diff format - Does it apply cleanly to the current git master? Yes - Does it include reasonable tests, necessary doc patches, etc? I think so. Comments in execnodes.h are not updated with new fields (ss_qualnremoved and others). They are commented directly in the definition. Very minor, comments should just be moved around. I have the same problem as explained in the original mail finding an appropriate place in the documentation. It could be added on EXPLAIN's page, but that would mean changing the example statement for one with a filter. - Does the patch actually implement that? Yes - Do we want that? I'd like to have that. It makes it easier to determine if an index is selective enough. Some people might prefer having it through an option of EXPLAIN (such as EXPLAIN (filters on) ), I don't really know. - Do we already have it? No. - Does it follow SQL spec, or the community-agreed behavior? Nothing about this in the SQL spec, obviously, and I didn't see anyone disagreeing with the proposal. - Does it include pg_dump support (if applicable)? Not applicable - Are there dangers? Not that I think of - Have all the bases been covered? Yes, I think so. - Does the feature work as advertised? Yes - Are there corner cases the author has failed to consider? I didn't find any - Are there any assertion failures or crashes? No - Does the patch slow down simple tests? No - If it claims to improve performance, does it? Not applicable - Does it slow down other things? No - Does it follow the project coding guidelines? Yes - Are there portability issues? No - Will it work on Windows/BSD etc? Yes - Are the comments sufficient and accurate? Yes, except for the minor inconsistancy in execnodes.h - Does it do what it says, correctly? Yes - Does it produce compiler warnings? No - Can you make it crash? No - Is everything done in a way that fits together coherently with other features/modules? Yes - Are there interdependencies that can cause problems? Not that I can see -- 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] Is there really no interest in SQL Standard?
On 09/19/2011 12:40 PM, Christopher Browne wrote: On Mon, Sep 19, 2011 at 12:20 PM, David Fetter da...@fetter.org wrote: Actually, I think it *is* a bad idea, as it would require construction from whole cloth of kinds of mostly political infrastructure that we don't have, as a community and aren't necessarily notably competent to construct. The nearest sort of thing that *could* conceivably be sensible would be to participate in UnQL http://www.unqlspec.org/display/UnQL/Home. That's early enough in its process that it's likely somewhat guidable, and, with the popularity of NoSQL, being at the ground breaking of a common query language to access that would likely be useful to us. If we wanted to start a new standards process, I imagine it would best involve embracing truly relational, stepping back to PostQUEL, and promoting a standard based on something off more in that direction. If I were looking for something truly relational I wouldn't go towards JSON or NoSQL, I'd go with something like Dee (http://www.quicksort.co.uk/ ) which IIRC were interested in building a PostgreSQL inteface. As much as that might sound like a terrible idea, trying to take over SQL by forking it strikes me as a much *worse* idea. My intention was not to take over anything. I only think it may be useful to discuss SQL features, informally or otherwise, with other open source competitors such as SQLite, MySQL (brethren), Firebird, etc., and Josh, having been close to the MySQL camp (even physically, from what I recall :-) is possibly well suited to start that discussion. Joe -- 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] Review for EXPLAIN and nfiltered
On Mon, Sep 19, 2011 at 12:51 PM, Marc Cousin cousinm...@gmail.com wrote: Here is my review for EXPLAIN and nfiltered (http://archives.postgresql.org/message-id/4e6e9e83.7070...@2ndquadrant.com) Please add this review to the CommitFest app here: https://commitfest.postgresql.org/action/patch_view?id=638 ...and also update the patch status as appropriate. Sounds like Ready for Committer would be the way to go in this case, since your review found only trivial issues. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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
* Thom Brown (t...@linux.com) wrote: But nVidia does produce a non-graphics-oriented GPGPU line called Tesla dedicated to such processing. Just as a side-note, I've got a couple Tesla's that aren't doing terribly much at the moment and they're in a Linux 'server'-type box from Penguin computing. I could certainly install PG on it and run some tests- if someone's written the code and provides the tests. I agree that it'd be interesting to do, but I share Lord Stark's feelings about the challenges and lack of potential gain- it's a very small set of queries that would benefit from this. You need to be working with enough data to make the cost of tranferring it all over to the GPU worthwhile, just for starters.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PATCH: regular logging of checkpoint progress
Tom Lane t...@sss.pgh.pa.us writes: On machines where gettimeofday is slow (and last I heard there were still lots of them), any such thing would be a disaster performance-wise. I'm still afraid to add more gettimeofday's into the query parse/plan/execute code path, even though it would greatly ease the problem of figuring out whether re-planning is worthwhile. Excuse my ignorance here, but is SIGALARM less of a problem? Then you could ask the system for an alarm next second and count the alarms rather than poll the clock. We don't need high precision in both those cases I guess. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Is there really no interest in SQL Standard?
Christopher Browne cbbro...@gmail.com writes: The nearest sort of thing that *could* conceivably be sensible would be to participate in UnQL http://www.unqlspec.org/display/UnQL/Home. That's early enough in its process that it's likely somewhat guidable, and, with the popularity of NoSQL, being at the ground breaking of a common query language to access that would likely be useful to us. Quite franckly, the thing that SQL was meant to provide is the ability for non programmers to grok and use the language by themselves. I'm yet to see that happen anywhere, all I see is developers and DBA that learn yet another programming language, which has a lot of strengths and its share of weaknesses too. My feeling here is that if we want to offer something else than our current SQL syntax support to the NoSQL people, we should expose the PostgreSQL system as a programming facility. Build a kind of a more classic programming language that would use our engine inside, etc. Here's and example of such a system, with some lisp and prolog interfaces on top of transactional data access semantics. http://software-lab.de/doc/ref.html#dbase IOW, I don't believe in another SQL standard, we're good enough at pushing the current one (wCTE being the last incantation of that, but all the custom types and extensibility are there too, building a kind of a generic or polymorphic type system, with custom operator support, etc). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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 09/19/2011 10:53 AM, Thom Brown wrote: But couldn't that also be seen as a chicken/egg situation? The chicken/egg problem here is a bit deeper than just no one offers GPUs because no one wants them on server systems. One of the reasons there aren't more GPUs in typical database server configurations is that you're already filling up some number of the full size slots, and correspondingly the bandwidth available to cards, with disk controllers. It doesn't help that many server class motherboards don't even have a x16 PCI-e slot on them, which is what most GPUs as delivered on regular consumer video cards are optimized for. But nVidia does produce a non-graphics-oriented GPGPU line called Tesla dedicated to such processing. Tesla units start at around $1500 USD, which is a nice budget to spend on either more RAM (to allow higher work_mem), faster storage to store temporary files onto, or a faster CPU to chew through all sorts of tasks more quickly. The Tesla units are easy to justify if you have a serious GPU-oriented application. The good bang for the buck point with CPU sorting for PostgreSQL is probably going to be a $50-$100 video card instead. For example, the card Vitor is seeing good results on costs around $60. (That's also a system with fairly slow RAM, though; it will be interesting to see if the gain holds up on newer systems.) -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Review for EXPLAIN and nfiltered
2011/9/19 Robert Haas robertmh...@gmail.com: On Mon, Sep 19, 2011 at 12:51 PM, Marc Cousin cousinm...@gmail.com wrote: Here is my review for EXPLAIN and nfiltered (http://archives.postgresql.org/message-id/4e6e9e83.7070...@2ndquadrant.com) Please add this review to the CommitFest app here: https://commitfest.postgresql.org/action/patch_view?id=638 ...and also update the patch status as appropriate. Sounds like Ready for Committer would be the way to go in this case, since your review found only trivial issues. Sorry, I wanted to, but I didn't get the email id fast enough, and had to go. It's done now. -- 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] Review for EXPLAIN and nfiltered
On Mon, Sep 19, 2011 at 3:10 PM, Marc Cousin cousinm...@gmail.com wrote: 2011/9/19 Robert Haas robertmh...@gmail.com: On Mon, Sep 19, 2011 at 12:51 PM, Marc Cousin cousinm...@gmail.com wrote: Here is my review for EXPLAIN and nfiltered (http://archives.postgresql.org/message-id/4e6e9e83.7070...@2ndquadrant.com) Please add this review to the CommitFest app here: https://commitfest.postgresql.org/action/patch_view?id=638 ...and also update the patch status as appropriate. Sounds like Ready for Committer would be the way to go in this case, since your review found only trivial issues. Sorry, I wanted to, but I didn't get the email id fast enough, and had to go. It's done now. No problem, didn't mean to nag. Thanks! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 Sep 19, 2011, at 5:16 PM, Tom Lane wrote: Greg Stark st...@mit.edu 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. btw, there is a handful of interesting talks / lectures about GPU programming provided by the university of chicago (just cannot find the link atm). regards, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- 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
2011/9/19 Greg Smith g...@2ndquadrant.com: On 09/19/2011 10:53 AM, Thom Brown wrote: But couldn't that also be seen as a chicken/egg situation? The chicken/egg problem here is a bit deeper than just no one offers GPUs because no one wants them on server systems. One of the reasons there aren't more GPUs in typical database server configurations is that you're already filling up some number of the full size slots, and correspondingly the bandwidth available to cards, with disk controllers. It doesn't help that many server class motherboards don't even have a x16 PCI-e slot on them, which is what most GPUs as delivered on regular consumer video cards are optimized for. Sandy bridge and ivy bridge intel series are CPU/GPU. I don't know how using the GPU affect the CPU part but it might be interesting to explore... -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] Grouping Sets
On Sun, Sep 18, 2011 at 02:08:01PM -0500, David Rinaldi wrote: I tried to apply the Grouping Sets Patch to 8.4, but received several Hunks failed messages, does anyone know if the failing hunks can be applied manually? Or what version they were applied to specifically? Your best bet is probably to get the code from approximately the date of the patch. As far as I know it hasn't been touched in a while, and didn't work well back when it was being actively developed. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com pgpeMKakIJ2SX.pgp Description: PGP signature
Re: [HACKERS] Is there really no interest in SQL Standard?
On 09/19/2011 10:58 AM, Joe Abbate wrote: Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. Not spending as much time sitting in meetings and fighting with other vendors is one of the competitive advantages PostgreSQL development has vs. the big guys. There needs to be a pretty serious problem with your process before adding bureaucracy to it is anything but a backwards move. And standardization tends to attract lots of paperwork. Last thing you want to be competing with a big company on is doing that sort of big company work. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Is there really no interest in SQL Standard?
2011/9/19 Greg Smith g...@2ndquadrant.com: On 09/19/2011 10:58 AM, Joe Abbate wrote: Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. Not spending as much time sitting in meetings and fighting with other vendors is one of the competitive advantages PostgreSQL development has vs. the big guys. There needs to be a pretty serious problem with your process before adding bureaucracy to it is anything but a backwards move. And standardization tends to attract lots of paperwork. Last thing you want to be competing with a big company on is doing that sort of big company work. +1 :) Pavel -- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Is there really no interest in SQL Standard?
Hi Greg, On 09/19/2011 04:44 PM, Greg Smith wrote: Not spending as much time sitting in meetings and fighting with other vendors is one of the competitive advantages PostgreSQL development has vs. the big guys. There needs to be a pretty serious problem with your process before adding bureaucracy to it is anything but a backwards move. And standardization tends to attract lots of paperwork. Last thing you want to be competing with a big company on is doing that sort of big company work. You have a point there. However, open source standardization doesn't have to be patterned after closed source efforts. OTOH it's hard to predict what form it should take. Perhaps it's simply a matter of cross-pollination, i.e., the kind of interaction with MySQL groups that occurred over the last year (I realize it wasn't just or even primarily SQL-related). Joe -- 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] Is there really no interest in SQL Standard?
Folks, Can we move the discussion about hypothetical new standards groups over to -advocacy? This is getting a bit off-topic for -hackers. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] File not found error on creating collation
Hi, I don't find the following error message very helpful: =# create collation sr_SP (LOCALE ='sr_SB.utf8'); ERROR: could not create locale sr_SB.utf8: No such file or directory It's correct in that it shouldn't be able to create the locale since it's not installed, but what file can't it find? What is the user supposed to do with this message? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Is there really no interest in SQL Standard?
On Monday, September 19, 2011 9:20 AM, David Fetter da...@fetter.org wrote: On Mon, Sep 19, 2011 at 10:58:49AM -0400, Joe Abbate wrote: On 09/19/2011 09:50 AM, Josh Berkus wrote: FWIW, the fact that the drafts *are* confidential is symptomatic of everything which is wrong with the ISO. Maybe it's time for an open source SQL standard, one not controlled by the big guys and their IP claims. That's probably not a bad idea. The down side is that it'll be the work of decades, not years, to get this thing going. If anyone wants to start on something like this, I think it could start as a rigorous review of PostgreSQL semantics. On Monday, September 19, 2011 4:44 PM, Greg Smith g...@2ndquadrant.com wrote: Not spending as much time sitting in meetings and fighting with other vendors is one of the competitive advantages PostgreSQL development has vs. the big guys. There needs to be a pretty serious problem with your process before adding bureaucracy to it is anything but a backwards move. And standardization tends to attract lots of paperwork... Perhaps focusing only on PostgreSQL semantics and edge cases is also where the effort should stop. I'm not offering to do this. I think this work would only be really valuable if it significantly improved the already excellent documentation and regression tests -- ie, provides direct user value. Best, Clark -- 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] File not found error on creating collation
On Tue, Sep 20, 2011 at 03:24, Thom Brown t...@linux.com wrote: ERROR: could not create locale sr_SB.utf8: No such file or directory It's correct in that it shouldn't be able to create the locale since it's not installed, but what file can't it find? What is the user supposed to do with this message? Unfortunately locales are OS-specific so there's not much universal advice that Postgres could give. However, I guess it does make sense addressing this in documentation. On Linux you'd need to edit the /etc/locale.gen file and then run 'locale-gen' as root. Regards, Marti -- 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] Grouping Sets
Paul,
I was able to apply the patch to 9.0.4 and so far looks good. My Oracle
results match. Nice.
But, when trying to calculate some percentages and control some rounding,
the results are coming back as null for some reason. I have tried casting,
to_char, etc to try to get them to show up..no love ensued. I was wondering
if you have any idea what could by happening. I have attached some test
results based on the grouping sets wiki. One of the examples is just using
group by, as a sanity check. Any ideas or help would be much appreciated.
CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
sales real, cost real );
insert into cars2 values('skoda', 'czech rep.', 1, 8000);
insert into cars2 values('skoda', 'germany', 5000, 6000);
insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
insert into cars2 values('bmw', 'germany', 18000, 15000);
insert into cars2 values('opel', 'czech rep.', 7000, 5000);
insert into cars2 values('opel', 'germany', 7000, 5000);
--grouping sets test--
select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by rollup(name, place);
nameplace sales costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep. 600040000.6667 (null) (null)
skoda germany 500060001.2 (null) (null)
opelczech rep. 700050000.7143 (null) (null)
opelgermany 700050000.7143 (null) (null)
skoda czech rep. 1 80000.8 (null) (null)
bmw germany 18000 15000 0.8333 (null) (null)
bmw (null) 24000 19000 0.7917 (null) (null)
skoda (null) 15000 14000 0.9333 (null) (null)
opel(null) 14000 1 0.7143 (null) (null)
(null) (null) 53000 43000 0.8113 (null) (null)
--group by sanity test--
select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by name, place;
nameplace sales costcost_sales_ratiocost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep. 600040000.6667 66.6667 67
skoda germany 500060001.2 120 120
opelczech rep. 700050000.7143 71.4286 71
opelgermany 700050000.7143 71.4286 71
skoda czech rep. 1 80000.8 80 80
bmw germany 18000 15000 0.8333 83. 83
Thanks
--
Regards
David
-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
Sent: Sunday, September 18, 2011 2:34 PM
To: David Rinaldi
Subject: Re: [HACKERS] Grouping Sets
Hello
A last patch should be applied on 8.4 or 9.0 - should to try it. I
worked with developer version.
http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php
Regards
Pavel Stehule
2011/9/18 David Rinaldi edwbro...@gmail.com:
Hi,
I tried to apply the Grouping Sets Patch to 8.4, but received several
Hunks
failed messages, does anyone know if the failing hunks can be applied
manually? Or what version they were applied to specifically?
--
Regards
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Inlining comparators as a performance optimisation
Recent discussions on the threads Double sorting split patch and
CUDA sorting raised the possibility that there could be significant
performance optimisation low-hanging fruit picked by having the
executor treat integers and floats as a special case during sorting,
avoiding going to the trouble of calling a comparator using the
built-in SQL function machinery, and taking advantage of inlining of
the comparator, which has been shown to have a considerable
performance advantage (at least compared to a general purpose c stdlib
qsort(), that takes a function pointer as its comparator, much like
tuplesort).
I've hacked together a sloppy POC implementation in a hurry
(basically, some code is shifted around) , which is attached - I felt
that it would be useful to determine if the community feels that this
is a worth-while undertaking in advance of a business trip that I'm
leaving on tomorrow lasting until Friday, during which I will be
mostly unavailable. The patch breaks the Postgres sorting executor
node (at least when it quicksorts) for any type other than int4. I
apologise for how rough the patch is, but the code itself isn't
important right now - the idea is. I anticipate that the value
state-datumType or something similar will be set in a near future
revision, so that tuplesort_performsort will know which type-specific
optimisation it can use for the type, while falling back on the
existing generic qsort_arg + qsort_arg_comparator, and sorting won't
actually be broken.
I've been doing some preliminary testing using the dell store 2 sample
database. I increase work_mem to '50MB', to ensure that a quicksort
will be performed for sorting (otherwise, I'm using the
postgresql.conf that initdb gave me). The query is:
explain analyze select * from orderlines order by prod_id;
Once the cache has been warmed, explain analyze very consistently
reports a runtime of 123ms for this query on master/HEAD, which varies
+/- 1 ms, with a few outliers of maybe +/- 2ms. However, when I apply
this patch, that goes down to 107ms +/- 1ms at -O0. I think that
that's a pretty good start. Funnily enough, the difference/advantage
vanishes at -O2 (I'm guessing that the higher optimisation level of
GCC 4.5 hyper-corrects away the inlining, but I don't have time to
check that right now).
I imagine the version that I actually submit for patch review will
have a macro-based infrastructure for inlining the sorting of various
built-in types, initially integers and floats. It will most likely
have some other optimisations - I haven't even used a profiler yet.
This performance patch differs from most in that it's difficult in
principle to imagine a performance regression occurring.
Thoughts?
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 3505236..c5ac708 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -1224,6 +1224,285 @@ puttuple_common(Tuplesortstate *state, SortTuple *tuple)
}
}
+inline int
+compare_int4( Datum first, Datum second)
+{
+ int32 a_is = DatumGetInt32(first);
+ int32 b_is = DatumGetInt32(second);
+
+ if (a_is b_is)
+ return 1;
+ else if (a_is == b_is)
+ return 0;
+ else
+ return -1;
+}
+
+
+/*
+ * Inline-able copy of FunctionCall2Coll() to save some cycles in sorting.
+ */
+static inline Datum
+myFunctionCall2Coll(FmgrInfo *flinfo, Oid collation, Datum arg1, Datum arg2)
+{
+ FunctionCallInfoData fcinfo;
+ Datum result;
+
+ InitFunctionCallInfoData(fcinfo, flinfo, 2, collation, NULL, NULL);
+
+ fcinfo.arg[0] = arg1;
+ fcinfo.arg[1] = arg2;
+ fcinfo.argnull[0] = false;
+ fcinfo.argnull[1] = false;
+
+ result = FunctionCallInvoke(fcinfo);
+
+ /* Check for null result, since caller is clearly not expecting one */
+ if (fcinfo.isnull)
+ elog(ERROR, function %u returned NULL, fcinfo.flinfo-fn_oid);
+
+ return result;
+}
+/*
+ * Apply a sort function (by now converted to fmgr lookup form)
+ * and return a 3-way comparison result. This takes care of handling
+ * reverse-sort and NULLs-ordering properly. We assume that DESC and
+ * NULLS_FIRST options are encoded in sk_flags the same way btree does it.
+ */
+static inline int32
+inlineApplySortFunction(FmgrInfo *sortFunction, int sk_flags, Oid collation,
+ Datum datum1, bool isNull1,
+ Datum datum2, bool isNull2)
+{
+ int32 compare;
+
+ if (isNull1)
+ {
+ if (isNull2)
+ compare = 0; /* NULL = NULL */
+ else if (sk_flags SK_BT_NULLS_FIRST)
+ compare = -1; /* NULL NOT_NULL */
+ else
+ compare = 1; /* NULL NOT_NULL */
+ }
+ else if (isNull2)
+ {
+ if (sk_flags SK_BT_NULLS_FIRST)
+ compare = 1; /* NOT_NULL NULL */
+ else
+ compare = -1; /* NOT_NULL NULL */
+ }
+ else
+ {
+ compare = compare_int4(datum1, datum2);
+
+ if (sk_flags SK_BT_DESC)
+ compare = -compare;
+ }
+
+ return compare;
+}
+
+
+
+inline int
Re: [HACKERS] File not found error on creating collation
Marti Raudsepp ma...@juffo.org writes: On Tue, Sep 20, 2011 at 03:24, Thom Brown t...@linux.com wrote: ERROR: Â could not create locale sr_SB.utf8: No such file or directory It's correct in that it shouldn't be able to create the locale since it's not installed, but what file can't it find? Â What is the user supposed to do with this message? Unfortunately locales are OS-specific so there's not much universal advice that Postgres could give. The reason it's doing that is that newlocale() is returning ENOENT, which is what it's supposed to do according to the standard: ERRORS The newlocale() function shall fail if: [ENOMEM] There is not enough memory available to create the locale object or load the locale data. [EINVAL] The category_mask contains a bit that does not correspond to a valid category. [ENOENT] For any of the categories in category_mask, the locale data is not available. The generic meaning of ENOENT is file not found, and at some level this probably *is* a file-not-found situation, but Postgres has no way to know which file is missing. We could possibly add a HINT suggesting that the locale isn't installed, but I don't see that we could offer any useful generic advice about how to install it. I'm also worried about how to phrase the hint to cover some other obvious possibilities, like you fat-fingered the locale name. regards, tom lane -- 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] Inlining comparators as a performance optimisation
Peter Geoghegan pe...@2ndquadrant.com writes: Once the cache has been warmed, explain analyze very consistently reports a runtime of 123ms for this query on master/HEAD, which varies +/- 1 ms, with a few outliers of maybe +/- 2ms. However, when I apply this patch, that goes down to 107ms +/- 1ms at -O0. I think that that's a pretty good start. Funnily enough, the difference/advantage vanishes at -O2 (I'm guessing that the higher optimisation level of GCC 4.5 hyper-corrects away the inlining, but I don't have time to check that right now). Considering that -O2 is our standard optimization level, that observation seems to translate to this patch will be useless in practice. I think you had better investigate that aspect in some detail before spending more effort. This performance patch differs from most in that it's difficult in principle to imagine a performance regression occurring. Really? N copies of the same code could lead to performance loss just due to code bloat (ie, less of a query's inner loops fitting in CPU cache). Not to mention the clear regression in maintainability. So I'm disinclined to consider this sort of change without a significantly bigger win than you're suggesting above (no, I don't even consider the -O0 number attractive, let alone what you're finding at -O2). regards, tom lane -- 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] Is there really no interest in SQL Standard?
FYI, one of the main goals of the Muldis D language is to be an open source SQL standard. It is intended to satisfy both relational and NoSQL folks, and predates UnQL significantly. Muldis D has always been published openly and is comprehensive enough to cover anything that SQL does, and anyone is welcome to improve it. Moreover, this standard has built-in resilience against embrace, extend and extinguish by including explicit versioning with authorities (Perl 6 inspired that feature), so that if anyone forks the language, it is possible for the different versions to be easily distinguishable and non-conflicting, and in a more benign respect it is designed to be extensible so DBMSs can be free to evolve under it, adding unique features, while not causing compatibility conflicts with other DBMSs in the process. Note that I have fallen behind in specifying a number of intended significant design improvements/simplifications to the spec proper, though much of this is hashed out in the laundry list TODO_DRAFT file in github. -- Darren Duncan Joe Abbate wrote: On 09/19/2011 12:40 PM, Christopher Browne wrote: On Mon, Sep 19, 2011 at 12:20 PM, David Fetter da...@fetter.org wrote: Actually, I think it *is* a bad idea, as it would require construction from whole cloth of kinds of mostly political infrastructure that we don't have, as a community and aren't necessarily notably competent to construct. The nearest sort of thing that *could* conceivably be sensible would be to participate in UnQL http://www.unqlspec.org/display/UnQL/Home. That's early enough in its process that it's likely somewhat guidable, and, with the popularity of NoSQL, being at the ground breaking of a common query language to access that would likely be useful to us. If we wanted to start a new standards process, I imagine it would best involve embracing truly relational, stepping back to PostQUEL, and promoting a standard based on something off more in that direction. If I were looking for something truly relational I wouldn't go towards JSON or NoSQL, I'd go with something like Dee (http://www.quicksort.co.uk/ ) which IIRC were interested in building a PostgreSQL inteface. As much as that might sound like a terrible idea, trying to take over SQL by forking it strikes me as a much *worse* idea. My intention was not to take over anything. I only think it may be useful to discuss SQL features, informally or otherwise, with other open source competitors such as SQLite, MySQL (brethren), Firebird, etc., and Josh, having been close to the MySQL camp (even physically, from what I recall :-) is possibly well suited to start that discussion. Joe -- 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] File not found error on creating collation
On Mon, Sep 19, 2011 at 10:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: On Tue, Sep 20, 2011 at 03:24, Thom Brown t...@linux.com wrote: ERROR: could not create locale sr_SB.utf8: No such file or directory It's correct in that it shouldn't be able to create the locale since it's not installed, but what file can't it find? What is the user supposed to do with this message? Unfortunately locales are OS-specific so there's not much universal advice that Postgres could give. The reason it's doing that is that newlocale() is returning ENOENT, which is what it's supposed to do according to the standard: ERRORS The newlocale() function shall fail if: [ENOMEM] There is not enough memory available to create the locale object or load the locale data. [EINVAL] The category_mask contains a bit that does not correspond to a valid category. [ENOENT] For any of the categories in category_mask, the locale data is not available. The generic meaning of ENOENT is file not found, and at some level this probably *is* a file-not-found situation, but Postgres has no way to know which file is missing. We could possibly add a HINT suggesting that the locale isn't installed, but I don't see that we could offer any useful generic advice about how to install it. I'm also worried about how to phrase the hint to cover some other obvious possibilities, like you fat-fingered the locale name. Maybe something like this? HINT: The operating system was unable to find any locale data for the locale name you specified. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Grouping Sets
Hello
2011/9/20 David Rinaldi edwbro...@gmail.com:
Paul,
I was able to apply the patch to 9.0.4 and so far looks good. My Oracle
results match. Nice.
But, when trying to calculate some percentages and control some rounding,
the results are coming back as null for some reason. I have tried casting,
to_char, etc to try to get them to show up..no love ensued. I was wondering
if you have any idea what could by happening. I have attached some test
results based on the grouping sets wiki. One of the examples is just using
group by, as a sanity check. Any ideas or help would be much appreciated.
sorry, I have not any useful idea. This work was a concept and it is
probable, so there will be some corner issues :(.
This feature needs more love and some more significant changes in
planner and executor.
Regards
Pavel
CREATE TABLE cars2 (name CHARACTER VARYING , place CHARACTER VARYING ,
sales real, cost real );
insert into cars2 values('skoda', 'czech rep.', 1, 8000);
insert into cars2 values('skoda', 'germany', 5000, 6000);
insert into cars2 values('bmw', 'czech rep.', 6000, 4000);
insert into cars2 values('bmw', 'germany', 18000, 15000);
insert into cars2 values('opel', 'czech rep.', 7000, 5000);
insert into cars2 values('opel', 'germany', 7000, 5000);
--grouping sets test--
select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by rollup(name, place);
name place sales cost cost_sales_ratio cost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep. 6000 4000 0.6667 (null) (null)
skoda germany 5000 6000 1.2 (null) (null)
opel czech rep. 7000 5000 0.7143 (null) (null)
opel germany 7000 5000 0.7143 (null) (null)
skoda czech rep. 1 8000 0.8 (null) (null)
bmw germany 18000 15000 0.8333 (null) (null)
bmw (null) 24000 19000 0.7917 (null) (null)
skoda (null) 15000 14000 0.9333 (null) (null)
opel (null) 14000 1 0.7143 (null) (null)
(null) (null) 53000 43000 0.8113 (null) (null)
--group by sanity test--
select name, place,
sum(sales) as sales,
sum(cost) as cost,
sum(cost) / sum(sales) as cost_sales_ratio,
(sum(cost) / sum(sales)) * 100 as cost_sales_ratio_per,
round(((sum(cost) / sum(sales)) * 100),0) as cost_sales_ratio_per_rnd
from cars2 group by name, place;
name place sales cost cost_sales_ratio cost_sales_ratio_per
cost_sales_ratio_per_rnd
bmw czech rep. 6000 4000 0.6667 66.6667 67
skoda germany 5000 6000 1.2 120 120
opel czech rep. 7000 5000 0.7143 71.4286 71
opel germany 7000 5000 0.7143 71.4286 71
skoda czech rep. 1 8000 0.8 80 80
bmw germany 18000 15000 0.8333 83. 83
Thanks
--
Regards
David
-Original Message-
From: Pavel Stehule [mailto:pavel.steh...@gmail.com]
Sent: Sunday, September 18, 2011 2:34 PM
To: David Rinaldi
Subject: Re: [HACKERS] Grouping Sets
Hello
A last patch should be applied on 8.4 or 9.0 - should to try it. I
worked with developer version.
http://archives.postgresql.org/pgsql-hackers/2010-08/msg00647.php
Regards
Pavel Stehule
2011/9/18 David Rinaldi edwbro...@gmail.com:
Hi,
I tried to apply the Grouping Sets Patch to 8.4, but received several
Hunks
failed messages, does anyone know if the failing hunks can be applied
manually? Or what version they were applied to specifically?
--
Regards
David
--
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] File not found error on creating collation
Robert Haas robertmh...@gmail.com writes: On Mon, Sep 19, 2011 at 10:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could possibly add a HINT suggesting that the locale isn't installed, but I don't see that we could offer any useful generic advice about how to install it. I'm also worried about how to phrase the hint to cover some other obvious possibilities, like you fat-fingered the locale name. Maybe something like this? HINT: The operating system was unable to find any locale data for the locale name you specified. Hmm, that's not bad. We could probably even call it errdetail, since it's not so much a hint as explaining what the SUS spec states that the ENOENT error code means here. In the nitpick department, s/was unable to/could not/ per our usual message style guidelines. Otherwise seems good. regards, tom lane -- 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] Improve lseek scalability v3
On Mon, Sep 19, 2011 at 08:31:00AM -0400, Stephen Frost wrote: * Benjamin LaHaise (b...@kvack.org) wrote: For such tables, can't Postgres track the size of the file internally? I'm assuming it's keeping file descriptors open on the tables it manages, in which case when it writes to a file to extend it, the internally stored size could be updated. Not making a syscall at all would scale far better than even a modified lseek() will perform. We'd have to have it in shared memory and have a lock around it, it wouldn't be cheap at all. Yep, that makes perfect sense. After all, the kernel does basically the same thing to maintain this information; why should we have userspace duplicating the same infrastructure? I must admit, I'd never heard of this usage of lseek to get the current size of a file before; I'd assumed everybody used fstat. Given this legitimate reason for a high-frequency calling of lseek, I withdraw my earlier objection to the patch series. -- Matthew Wilcox Intel Open Source Technology Centre Bill, look, we understand that you're interested in selling us this operating system, but compare it to ours. We can't possibly take such a retrograde step. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Use new oom_score_adj without a new compile-time constant
This is one way to prevent the kernel warning message without having to
introduce a new constant. Scale the old oom_adj-style value the same way
the kernel internally does it and use that instead if oom_score_adj is
available for writing.
Signed-off-by: Dan McGee d...@archlinux.org
---
This addresses some of the concerns raised on the ML and will at least keep
those of us running modern kernels happy.
Alternatively one could switch the symbol used to be the new style and have the
old one computed; however this is a pain for legacy vs. current versions.
src/backend/postmaster/fork_process.c | 22 +-
1 files changed, 21 insertions(+), 1 deletions(-)
diff --git a/src/backend/postmaster/fork_process.c
b/src/backend/postmaster/fork_process.c
index b2fe9a1..3cded54 100644
--- a/src/backend/postmaster/fork_process.c
+++ b/src/backend/postmaster/fork_process.c
@@ -81,16 +81,36 @@ fork_process(void)
* Use open() not stdio, to ensure we control the open
flags. Some
* Linux security environments reject anything but
O_WRONLY.
*/
- int fd = open(/proc/self/oom_adj,
O_WRONLY, 0);
+ int fd =
open(/proc/self/oom_score_adj, O_WRONLY, 0);
/* We ignore all errors */
if (fd = 0)
{
charbuf[16];
+ int oom_score_adj;
+ /*
+* The compile-time value is the old style
oom_adj;
+* scale it the same way the kernel does to
+* convert to the new style oom_score_adj. This
+* should become a constant at compile time.
+* Valid values range from -17 (never kill) to
+* 15; no attempt of validation is done.
+*/
+ oom_score_adj = LINUX_OOM_ADJ * 1000 / 17;
snprintf(buf, sizeof(buf), %d\n,
LINUX_OOM_ADJ);
(void) write(fd, buf, strlen(buf));
close(fd);
+ } else if (errno == EEXIST) {
+ int fd = open(/proc/self/oom_adj,
O_WRONLY, 0);
+ if (fd = 0)
+ {
+ charbuf[16];
+
+ snprintf(buf, sizeof(buf), %d\n,
LINUX_OOM_ADJ);
+ (void) write(fd, buf, strlen(buf));
+ close(fd);
+ }
}
}
#endif /* LINUX_OOM_ADJ */
--
1.7.6.1
--
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] [PATCH] Use new oom_score_adj without a new compile-time constant
On Mon, Sep 19, 2011 at 3:11 PM, Dan McGee d...@archlinux.org wrote:
This is one way to prevent the kernel warning message without having to
introduce a new constant. Scale the old oom_adj-style value the same way
the kernel internally does it and use that instead if oom_score_adj is
available for writing.
Signed-off-by: Dan McGee d...@archlinux.org
---
This addresses some of the concerns raised on the ML and will at least keep
those of us running modern kernels happy.
Alternatively one could switch the symbol used to be the new style and have
the
old one computed; however this is a pain for legacy vs. current versions.
src/backend/postmaster/fork_process.c | 22 +-
1 files changed, 21 insertions(+), 1 deletions(-)
diff --git a/src/backend/postmaster/fork_process.c
b/src/backend/postmaster/fork_process.c
index b2fe9a1..3cded54 100644
--- a/src/backend/postmaster/fork_process.c
+++ b/src/backend/postmaster/fork_process.c
@@ -81,16 +81,36 @@ fork_process(void)
* Use open() not stdio, to ensure we control the open
flags. Some
* Linux security environments reject anything but
O_WRONLY.
*/
- int fd =
open(/proc/self/oom_adj, O_WRONLY, 0);
+ int fd =
open(/proc/self/oom_score_adj, O_WRONLY, 0);
/* We ignore all errors */
if (fd = 0)
{
char buf[16];
+ int oom_score_adj;
+ /*
+ * The compile-time value is the old style
oom_adj;
+ * scale it the same way the kernel does to
+ * convert to the new style oom_score_adj.
This
+ * should become a constant at compile time.
+ * Valid values range from -17 (never kill) to
+ * 15; no attempt of validation is done.
+ */
+ oom_score_adj = LINUX_OOM_ADJ * 1000 / 17;
snprintf(buf, sizeof(buf), %d\n,
LINUX_OOM_ADJ);
Of course it would help to actually use the computed value here; this should be:
snprintf(buf, sizeof(buf), %d\n,
oom_score_adj);
(void) write(fd, buf, strlen(buf));
close(fd);
+ } else if (errno == EEXIST) {
+ int fd =
open(/proc/self/oom_adj, O_WRONLY, 0);
+ if (fd = 0)
+ {
+ char buf[16];
+
+ snprintf(buf, sizeof(buf), %d\n,
LINUX_OOM_ADJ);
+ (void) write(fd, buf, strlen(buf));
+ close(fd);
+ }
}
}
#endif /* LINUX_OOM_ADJ */
--
1.7.6.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Back-branch releases upcoming this week
As has been mentioned a couple times, we're well overdue for updates of the back branches. Seems like time to get that done, so we'll be wrapping 8.2.x and up this Thursday for release Monday the 26th. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
