[BUGS] Re: BUG #6483: Rows being evaluated, although being outside the LIMIT / OFFSET boundaries
On 02/23/2012 12:05 AM, Marti Raudsepp wrote: > On Wed, Feb 22, 2012 at 23:40, Tom Lane wrote: >> Marti Raudsepp writes: >>> According to this model, evaluating SELECT clause fields for *all* >>> found rows is done in step 5, whereas LIMIT/OFFSET are only applied >>> later at step 9. So we're already bending the rules here (in general >>> we don't do such optimizations around volatile functions). The worst >>> thing is that it's inconsistent -- the LIMIT gets applied when >>> computing the SELECT list, but OFFSET doesn't. >> >> On what grounds do you say that? LIMIT and OFFSET are practically the >> same thing internally, and are certainly applied in the same way. > > The difference is that the SELECT fields for the first OFFSET rows are > *evaluated*, but aren't simply returned to the client. But beyond > LIMIT, query evaluation terminates entirely -- the rest of the SELECT > clause rows aren't evaluated. > > AFAICT, the model in the documentation suggests that the SELECT fields > are evaluated for all matching rows in indeterminate order, before > ORDER BY is applied and before the result set is sliced by > OFFSET/LIMIT. Indeed, that's probably the main issue - it is not behaving symmetrically, i.e. fetching the first two rows has one effect (and performance impact), while fetching the last two - completely different. In my case, I am making something like an "ON SELECT" rule, triggering some actions once the rows are read (and sent to the client) from a SELECT statement. The thing is that "read" and "sent to the client" appear to be two different things in that case. While I will certainly use a subquery for it, as proposed by Marti (since real cursors are not an option in my stateless web environment), I do believe that at least the documentation should be more clear concerning cases like that (if the behaviour stays that way). Regards, -- Kouber Saparev -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6483: Rows being evaluated, although being outside the LIMIT / OFFSET boundaries
Marti Raudsepp writes: > AFAICT, the model in the documentation suggests that the SELECT fields > are evaluated for all matching rows in indeterminate order, before > ORDER BY is applied and before the result set is sliced by > OFFSET/LIMIT. That is in fact the case if you have a query plan that involves a Sort node followed by Limit. We have some optimizations that avoid the need for an explicit sort, but it would be pretty hard to write a specification for exactly when unretrieved rows will not be evaluated. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6485: Primary index key not updated uniformly
Ramanujam writes: > On Thu, Feb 23, 2012 at 3:22 PM, Tom Lane wrote: >> This is intentional --- we gave up updating index column names awhile >> ago. > Is pg_constraint a credible place to retrieve primary key information from? I'd try the information_schema views first. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6486: configure - unable to cross-compile postgresql with openssl using Fedora MinGW
The following bug has been logged on the website: Bug reference: 6486 Logged by: Tomasz Ostrowski Email address: tomet...@batory.org.pl PostgreSQL version: 9.1.2 Operating system: Fedora Linux 16 Description: Fedora MinGW is a project which allows for easy cross-compiling of Windows binaries and libraries on Fedora Linux - much easier than with native MinGW on Windows - just use "./configure --host=i686-pc-mingw32 && make" and you're done. I'm using it to easily compile "libpq" and "libpqxx" for a C++ project. I can compile PostgreSQL just fine using this but without OpenSSL support. When I try to compile with OpenSSL on Windows Postgres expects to link with libeay32.dll and libssleay32.dll, which aren't there, but ignores that there are dll's for standard "crypto" and "ssl" libraries available. To reproduce: 1. Download Fedora LiveCD iso from http://fedoraproject.org/ 2. Run it in a virtual machine - for example: qemu-kvm -m 2048 -cdrom Fedora-16-i686-Live-Desktop.iso You can also burn it to a CD-R or install it to a USB Thumb-drive using "livecd-iso-to-disk" script included in this image, and start a physical computer with it, but a virtual machine would be easier and faster. 3. Download postgresql-9.1.2.tar.bz2 to /tmp/ 4. Run using "System Tools\Terminal": su -c 'yum -y install mingw32-gcc mingw32-openssl gcc' cd /tmp/ tar xf postgresql-9.1.2.tar.bz2 cd postgresql-9.1.2 ./configure --with-openssl --host=i686-pc-mingw32 Result: (...) checking for CRYPTO_new_ex_data in -leay32... no configure: error: library 'eay32' is required for OpenSSL I've attached a proposed patch for configure.in - it uses AC_SEARCH_LIBS instead of AC_CHECK_LIB on Windows. With this patch and after autoreconf (I needed also to remove "autoconf 2.63 is required" check from configure.in): ./configure --with-openssl --host=i686-pc-mingw32 && make completed successfully and a working binary and libpq.dll using libcrypto-10.dll and libssl-10.dll was created. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh diff -urNP postgresql-9.1.2.orig/configure.in postgresql-9.1.2/configure.in --- postgresql-9.1.2.orig/configure.in 2011-12-01 22:47:20.0 +0100 +++ postgresql-9.1.2/configure.in 2012-02-23 19:24:43.708343882 +0100 @@ -945,8 +945,8 @@ AC_CHECK_LIB(crypto, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'crypto' is required for OpenSSL])]) AC_CHECK_LIB(ssl,SSL_library_init, [], [AC_MSG_ERROR([library 'ssl' is required for OpenSSL])]) else - AC_CHECK_LIB(eay32, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'eay32' is required for OpenSSL])]) - AC_CHECK_LIB(ssleay32,SSL_library_init, [], [AC_MSG_ERROR([library 'ssleay32' is required for OpenSSL])]) + AC_CHECK_LIB(CRYPTO_new_ex_data, crypto eay32, [], [AC_MSG_ERROR([library 'crypto' or 'eay32' is required for OpenSSL])]) + AC_CHECK_LIB(SSL_library_init, ssl ssleay32, [], [AC_MSG_ERROR([library 'ssl' or 'ssleay32' is required for OpenSSL])]) fi fi -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6486: configure - unable to cross-compile postgresql with openssl using Fedora MinGW
On 2012-02-23 19:51, tomet...@batory.org.pl wrote: I've attached a proposed patch for configure.in The bug reporting form has word-wrapped my patch - I'm attaching a correct version. 4. Run using "System Tools\Terminal": su -c 'yum -y install mingw32-gcc mingw32-openssl gcc' Sorry - make this: su -c 'yum -y install mingw32-gcc mingw32-openssl' "gcc" isn't needed - much less to download. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh diff -urNP postgresql-9.1.2.orig/configure.in postgresql-9.1.2/configure.in --- postgresql-9.1.2.orig/configure.in 2011-12-01 22:47:20.0 +0100 +++ postgresql-9.1.2/configure.in 2012-02-23 19:24:43.708343882 +0100 @@ -945,8 +945,8 @@ AC_CHECK_LIB(crypto, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'crypto' is required for OpenSSL])]) AC_CHECK_LIB(ssl,SSL_library_init, [], [AC_MSG_ERROR([library 'ssl' is required for OpenSSL])]) else - AC_CHECK_LIB(eay32, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'eay32' is required for OpenSSL])]) - AC_CHECK_LIB(ssleay32,SSL_library_init, [], [AC_MSG_ERROR([library 'ssleay32' is required for OpenSSL])]) + AC_CHECK_LIB(CRYPTO_new_ex_data, crypto eay32, [], [AC_MSG_ERROR([library 'crypto' or 'eay32' is required for OpenSSL])]) + AC_CHECK_LIB(SSL_library_init, ssl ssleay32, [], [AC_MSG_ERROR([library 'ssl' or 'ssleay32' is required for OpenSSL])]) fi fi -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6486: configure - unable to cross-compile postgresql with openssl using Fedora MinGW
Tomasz Ostrowski writes: > On 2012-02-23 19:51, tomet...@batory.org.pl wrote: >> I've attached a proposed patch for configure.in > The bug reporting form has word-wrapped my patch - I'm attaching a > correct version. Hmmm ... I'd be happier with this patch if it reversed the order of the library probes (ie, try the nonstandard names first), as then it would obviously not break any configurations that worked before. Would that be OK from your point of view, or not? Also, the syntax looks wrong --- shouldn't the new calls be AC_SEARCH_LIBS? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6486: configure - unable to cross-compile postgresql with openssl using Fedora MinGW
On 2012-02-23 20:28, Tom Lane wrote: Hmmm ... I'd be happier with this patch if it reversed the order of the library probes (ie, try the nonstandard names first), as then it would obviously not break any configurations that worked before. Would that be OK from your point of view, or not? It will be OK I guess. Also, the syntax looks wrong --- shouldn't the new calls be AC_SEARCH_LIBS? Of course - my bad. I had to recreate a patch because I needed to return back this autoconf version check and haven't retested it. I'm attaching corrected and this time tested version. Sorry. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh diff -urNP postgresql-9.1.2.orig/configure.in postgresql-9.1.2/configure.in --- postgresql-9.1.2.orig/configure.in 2011-12-01 22:47:20.0 +0100 +++ postgresql-9.1.2/configure.in 2012-02-23 20:34:00.765244884 +0100 @@ -945,8 +945,8 @@ AC_CHECK_LIB(crypto, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'crypto' is required for OpenSSL])]) AC_CHECK_LIB(ssl,SSL_library_init, [], [AC_MSG_ERROR([library 'ssl' is required for OpenSSL])]) else - AC_CHECK_LIB(eay32, CRYPTO_new_ex_data, [], [AC_MSG_ERROR([library 'eay32' is required for OpenSSL])]) - AC_CHECK_LIB(ssleay32,SSL_library_init, [], [AC_MSG_ERROR([library 'ssleay32' is required for OpenSSL])]) + AC_SEARCH_LIBS(CRYPTO_new_ex_data, eay32 crypto, [], [AC_MSG_ERROR([library 'eay32' or 'crypto' is required for OpenSSL])]) + AC_SEARCH_LIBS(SSL_library_init, ssleay32 ssl, [], [AC_MSG_ERROR([library 'ssleay32' or 'ssl' is required for OpenSSL])]) fi fi -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6486: configure - unable to cross-compile postgresql with openssl using Fedora MinGW
Tomasz Ostrowski writes: >> Also, the syntax looks wrong --- shouldn't the new calls be >> AC_SEARCH_LIBS? > Of course - my bad. I had to recreate a patch because I needed to return > back this autoconf version check and haven't retested it. I'm attaching > corrected and this time tested version. Sorry. OK, applied to HEAD and 9.1, will be in next week's 9.1.3 release (you just made it under the wire for that ...) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
