Re: [BUGS] BUG #6335: Weird planner decision with exists (a join b) condition
maxim.bo...@gmail.com writes: > EXPLAIN analyze select * > from applicant_adv_subscription aas > where > aas.user_id in (5112699,7995496) > and exists ( > SELECT * from resume > join resume_view_history using (resume_id) > where > resume.user_id = aas.user_id > ); I'm hoping to fix this type of case with the "generalized inner indexscan" work that I've been nattering about for a year or two now. What you need to make this fast, given that resume and resume_view_history are both large, is to push the current value of aas.user_id down into the table scan of resume --- and because the join and semijoin can't be reordered, that's not possible with the planner's current simpleminded idea of what an inner indexscan can be. The other example you show manages to luck out and get a good plan due to transitive propagation of equality conditions, but that's a narrow special case. Any other form of constraint whatsoever on aas is going to end up with the crummy plan where the whole lower join gets computed. 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 #5578: postrgesql database crash
We have a similar issue with postgres 9.0.1 Any ideas? I dint see any updates in the 9.1 release notes.. Has this issue been addressed? Regards, Pratik Chirania Hewlett-Packard -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-5578-postrgesql-database-crash-tp2259469p5077853.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- 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 #5578: postrgesql database crash
We have a similar issue with postgres 9.0.1 Any ideas? I dint see any updates in the 9.1 release notes.. Has this issue been addressed? Regards, Pratik Chirania -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-5578-postrgesql-database-crash-tp2259469p5077868.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- 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 #6338: Startup Script Ubuntu 10.04.3 LTS
The following bug has been logged on the website: Bug reference: 6338 Logged by: Gabriel Martínez Email address: sopo...@greencore.co.cr PostgreSQL version: 8.4.9 Operating system: Ubuntu 10.04.3 LTS Description: After the installation of postgresql (sudo apt-get install postgresql) isn't possible to start the service (sudo /etc/init.d/postgresql-8.4 start), simply doesn't happen anything and there is nothing in the logs. Searching in Internet, I found this: http://ubuntuforums.org/showpost.php?p=5663743&postcount=9 So I changed the startup script and was possible to start the service. Has anyone else reported this issue?. -- 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 #6337: Startup Script
The following bug has been logged on the website: Bug reference: 6337 Logged by: Gabriel Martínez Email address: gabriel.marti...@greencore.co.cr PostgreSQL version: 8.4.9 Operating system: Ubuntu 10.04.3 LTS Description: After the installation of postgresql (sudo apt-get install postgresql) isn't possible to start the service (sudo /etc/init.d/postgresql-8.4 start), simply doesn't happen anything and there is nothing in the logs. Searching in Internet, I found this: http://ubuntuforums.org/showpost.php?p=5663743&postcount=9 So I changed the startup script and was possible to start the service. Has anyone else reported this issue?. -- 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 #6339: Startup Script Ubuntu 10.04.3 LTS
The following bug has been logged on the website: Bug reference: 6339 Logged by: Gabriel Martínez Email address: gabriel.marti...@greencore.co.cr PostgreSQL version: 8.4.9 Operating system: Ubuntu 10.04.3 LTS Description: After the installation of postgresql (sudo apt-get install postgresql) isn't possible to start the service (sudo /etc/init.d/postgresql-8.4 start), simply doesn't happen anything and there is nothing in the logs. Searching in Internet, I found this: http://ubuntuforums.org/showpost.php?p=5663743&postcount=9 So I changed the startup script and was possible to start the service. Has anyone else reported this issue?. -- 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 #6340: to_timestamp() miscalculates
The following bug has been logged on the website:
Bug reference: 6340
Logged by: Hans-Henrik Fuxelius
Email address: hfuxel...@gmail.com
PostgreSQL version: 9.1.1
Operating system: Ubuntu 11.10
Description:
test to run: select to_timestamp('2011,03,27,02,11,11',
',MM,DD,HH24,MI,SS')
and the run: select to_timestamp('2011,03,27,03,11,11',
',MM,DD,HH24,MI,SS')
It miscalculates 02 hour to 03 hour by some reason
--
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 #6340: to_timestamp() miscalculates
hfuxel...@gmail.com writes:
> test to run: select to_timestamp('2011,03,27,02,11,11',
> ',MM,DD,HH24,MI,SS')
> and the run: select to_timestamp('2011,03,27,03,11,11',
> ',MM,DD,HH24,MI,SS')
> It miscalculates 02 hour to 03 hour by some reason
If that date is a DST forward transition date in your timezone, then
this behavior is neither surprising nor a bug.
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 #6340: to_timestamp() miscalculates
wrote:
> test to run: select to_timestamp('2011,03,27,02,11,11',
> ',MM,DD,HH24,MI,SS')
>
> and the run: select to_timestamp('2011,03,27,03,11,11',
> ',MM,DD,HH24,MI,SS')
>
> It miscalculates 02 hour to 03 hour by some reason
I don't know what time zone you're in, or whether daylight saving
time kicks in on that date. *Is* there a 02:11:11 on that date, or
is that when time "springs forward" by an hour in the spring for your
locale?
-Kevin
--
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 #6337: Startup Script
Excerpts from gabriel.martinez's message of jue dic 15 12:02:53 -0300 2011: > The following bug has been logged on the website: > > Bug reference: 6337 > Logged by: Gabriel Martínez > Email address: gabriel.marti...@greencore.co.cr > PostgreSQL version: 8.4.9 > Operating system: Ubuntu 10.04.3 LTS > Description: > > After the installation of postgresql (sudo apt-get install postgresql) isn't > possible to start the service (sudo /etc/init.d/postgresql-8.4 start), > simply doesn't happen anything and there is nothing in the logs. I see no such file in my /etc/init.d. Mine is called simply "postgresql" (this is Debian, not Ubuntu, but the official packages come from the same guy in both cases). Are you sure you're using the right script? What repo does the package come from? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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 #6337: Startup Script
Hello Gabriel, gabriel.marti...@greencore.co.cr [2011-12-15 15:02 +]: > After the installation of postgresql (sudo apt-get install postgresql) isn't > possible to start the service (sudo /etc/init.d/postgresql-8.4 start), > simply doesn't happen anything and there is nothing in the logs. I suppose the package installation did not create a default "8.4/main" cluster. I guess "pg_lsclusters" is empty? Please create one with "sudo pg_createcluster 8.4 main --start". (See /usr/share/doc/postgresql-common/README.Debian.gz) This happens if there is an already existing configuration for 8.4, or the package was installed before. The default cluster only gets created if this is more or less a "clean" install. Martin -- Martin Pitt| http://www.piware.de Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org) -- 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 #6340: to_timestamp() miscalculates
Thanx a lot for your promt respons :) You are absolutely right, its the
daylight saving that date and hour, I had it as an key in my GPS data and was
very confused!!!
Again - Thanx
/Hans-Henrik
On Dec 15, 2011, at 5:20 PM, Kevin Grittner wrote:
> wrote:
>
>> test to run: select to_timestamp('2011,03,27,02,11,11',
>> ',MM,DD,HH24,MI,SS')
>>
>> and the run: select to_timestamp('2011,03,27,03,11,11',
>> ',MM,DD,HH24,MI,SS')
>>
>> It miscalculates 02 hour to 03 hour by some reason
>
> I don't know what time zone you're in, or whether daylight saving
> time kicks in on that date. *Is* there a 02:11:11 on that date, or
> is that when time "springs forward" by an hour in the spring for your
> locale?
>
> -Kevin
--
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 #6341: Packaging - virtual provides "postgres" without version
The following bug has been logged on the website: Bug reference: 6341 Logged by: Nelson Marques Email address: nelson-m-marq...@ext.ptinovacao.pt PostgreSQL version: 8.4.10 Operating system: Red Hat Enterprise Linux 5 Description: Hi all, Currently your binary distribution of PostgreSQL for Enterprise Linux has a virtual provides for 'postgresql' which has no version associated with it (there's good and bad things depending on usage regarding this methodology). Is there a special reason for having a virtual provides without versioning? For my specific usage, have the "postgresql" virtual provides with a version associated (the same of the original package) would be of great value, and this situation might end up in forcing me to repackage the whole thing. Would upstream consider during next updates to make this virtual provides associated with the version of the main package? (I don't mind doing the necessary changes on the spec file, testing and even help maintaining it for upstream). NM -- 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 #6335: Weird planner decision with exists (a join b) condition
On Thu, Dec 15, 2011 at 7:11 PM, Tom Lane wrote: > maxim.bo...@gmail.com writes: > > EXPLAIN analyze select * > > from applicant_adv_subscription aas > > where > > aas.user_id in (5112699,7995496) > > and exists ( > > SELECT * from resume > > join resume_view_history using (resume_id) > > where > > resume.user_id = aas.user_id > > ); > > I'm hoping to fix this type of case with the "generalized inner > indexscan" work that I've been nattering about for a year or two now. > What you need to make this fast, given that resume and > resume_view_history are both large, is to push the current value of > aas.user_id down into the table scan of resume --- and because the join > and semijoin can't be reordered, that's not possible with the planner's > current simpleminded idea of what an inner indexscan can be. > > The other example you show manages to luck out and get a good plan due > to transitive propagation of equality conditions, but that's a narrow > special case. Any other form of constraint whatsoever on aas is going > to end up with the crummy plan where the whole lower join gets computed. > >regards, tom lane > Thank you very much for information. Rewriting the query did the trick and resolved performance issues. Do you plan create "generalized inner indexscan" mechanics for 9.2 version? -- Maxim Boguk
[BUGS] user names & non-ASCII
Hi All, please help. I Have PostgreSQL server 8.4.9 on Linux, database utf-8 and Client app on Windows (VC++ and libpq.dll). I need to use user account with non-ASCII and PQconnectdb() with options="client_encoding=WIN1250" doesn't work. SET CLIENT_ENCODING TO 'WIN1250' after PQconnectdb is too late because connection fails. Best regards, Pavel Holec
Re: [BUGS] user names & non-ASCII
Hello this is not bug you can try use a system variable PGCLIENTENCODING bash-4.2$ PGCLIENTENCODING='latin2' psql postgres psql (9.1.1) Type "help" for help. postgres=# show client_encoding ; client_encoding - LATIN2 (1 row) other way is connect with "only ascii" user name, changing encoding and changin current role SET role TO specialcharsrole; Regards Pavel Stehule 2011/12/16 Pavel Holec : > Hi All, please help. > > I Have PostgreSQL server 8.4.9 on Linux, database utf-8 and Client app on > Windows (VC++ and libpq.dll). > I need to use user account with non-ASCII and PQconnectdb() with > options="client_encoding=WIN1250" doesn't work. > SET CLIENT_ENCODING TO 'WIN1250' after PQconnectdb is too late because > connection fails. > > Best regards, > > Pavel Holec -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] user names & non-ASCII
Hi All, please help. I Have PostgreSQL server 8.4.9 on Linux, database utf-8 and Client app on Windows (VC++ and libpq.dll). I need to use user account with non-ASCII and PQconnectdb() with options="client_encoding=WIN1250" doesn't work. SET CLIENT_ENCODING TO 'WIN1250' after PQconnectdb is too late because connection fails. Best regards, Pavel Holec
[BUGS] user names & non-ASCII
Hi All, please help. I Have PostgreSQL server 8.4.9 on Linux, database utf-8 and Client app on Windows (VC++ and libpq.dll). I need to use user account with non-ASCII and PQconnectdb() with options="client_encoding=WIN1250" doesn't work. SET CLIENT_ENCODING TO 'WIN1250' after PQconnectdb is too late because connection fails. Best regards, Pavel Holec
[BUGS] BUG #6342: libpq blocks forever in "poll" function
The following bug has been logged on the website:
Bug reference: 6342
Logged by: Andrea Grassi
Email address: andreagra...@sogeasoft.com
PostgreSQL version: 8.4.8
Operating system: SUSE SLES 10 SP4 64 BIT
Description:
Hi,
I have a big and strange problem. Sometimes, libpq remains blocked in “poll”
function even if the server has already answered to the query. If I attach
to the process using kdbg I found this stack:
__kernel_vsyscall()
poll() from /lib/libc.so.6
pqSocketCheck() from /home/pg/pgsql/lib-32/libpq.so.5
pqWaitTimed() from /home/pg/pgsql/lib-32/libpq.so.5
pqWait() from /home/pg/pgsql/lib-32/libpq.so.5
PQgetResult() from /home/pg/pgsql/lib-32/libpq.so.5
PQexecFinish() from /home/pg/pgsql/lib-32/libpq.so.5
…
To simplify the context and to reproduce the bug, I wrote a test program
(that I attach below) that uses only libpq interface (no other strange
libraries) to read my database at localhost.
It loop on a table of 64000 rows and for each row it reads another table.
Generally it take 1 minute to work. I put this program in a loop, so once it
finishes, it restarts.
Usually it works fine but sometimes (without any rule) it blocks. It blocks
always (with the stack above) executing PQexec function (“CLOSE CURSOR xx”
or “FETCH ALL IN xx”).
If I press “continue” on kdbg after attaching the process, the programs
continue its execution and exit with success.
Here the specifics of the platform (a SLES 10 SP4 64-bit WITHOUT any
VMWARE)
Server
HP DL 580 G7
4 CPU INTEL XEON X7550
64 GB RAM
8 HD 600GB SAS DP 6G 2,5” RAID 1 e RAID5
S.O.
SUSE SLES 10 SP4 64 BIT
Kernel
Linux linuxspanesi 2.6.16.60-0.85.1-smp #1 SMP Thu Mar 17 11:45:06 UTC 2011
x86_64 x86_64 x86_64 GNU/Linux
Server Postgres
8.4.8 - 64-bit
Libpq
8.4.8 – 32-bit
I try to recompile libpq in
- debug mode
- on a 64-bit machine with –m32 option
- on a 32-bit machine
- setting HAVE_POLL to false at line 1053 in fe-misc.c to force to execute
the other branch of “#ifdef/else” using the function “select()” instead of
“poll()”
but none fixes the bug. I had the same stack as above, except for the last
case in which I had “___newselect_nocancel()” instead of “poll()”.
If I check the state of the connection using the “netstat” command I get
this output:
tcp 24 0127.0.0.1:49007127.0.0.1:5432
ESTABLISHED17415/pq_example.e
where the second field (recv-Q) is always blocked to a non-zero value.
It seems as the server has already answered but the libpq or poll function
don’t realize it.
Consider that the machine is very good and very fast.
It seems that the answer of the server arrives before the libpq starts
waiting for it (calling poll). Could be ?
I try to install a VMware this the same version of Linux and same version of
the kernel on a machine much less powerful: my program works fine and never
blocks.
Here below the code of the example program:
/*
* testlibpq.c
*
* Test the C version of libpq, the PostgreSQL frontend library.
*/
#include
#include
#include
#include "libpq-fe.h"
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
int i,
j;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and
using
* environment variables or defaults for all other connection
parameters.
*/
/* Make a connection to the database */
#ifdef CASE1
conn = PQsetdbLogin( getenv("SQLSERVER"), // pghost
0, // pgport
0, // pgoptions
0, // pgtty
"OSA", // dbName
0, // login
0// pwd
);
#else
conn = PQconnectdb("dbname = OSA");
#endif
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexec (conn, "SET datestyle='ISO'");
switch (PQresultStatus (res))
{
case PGRES_BAD_RESPONSE:
case PGRES_NONFATAL_ERROR:
case PGRES_FATAL_ERROR:
fprintf(stderr, "SET DATESTYLE command failed: %s",
PQresultErrorMessage(res));
break;
}
PQclear(res);
/*
* Our test case here involves using a cursor, for which we must be
inside
* a transaction block. We could do the whole thing with a single
