Re: [GENERAL] SQL question - problem with INTERSECT
If I remove the "GROUP BY messages.msgid ...", then the result will be messages whose subject contains either 'Hello' or 'There' in the subject, but not necessarily both. I want messages which have both 'Hello' and 'There' in the subject, and both 'Jim' and 'Jones' in the author. (For example, if I needed all of 'Hello', 'There', and 'Now' in the subject, my first HAVING clause would use a count of 3, while the second HAVING clause would still use a count of 2, for both 'Jim' and 'Jones'.) So I cannot remove either having clause without changing the meaning. What I would really like to know is why INTERSECT does not allow this. If I understand that, maybe I can figure out how to get what I need. -Original Message- From: Igor Roboul [EMAIL PROTECTED] To: PGSQL-General [EMAIL PROTECTED] Date: Wednesday, November 01, 2000 12:03 AM Subject: Re: [GENERAL] SQL question - problem with INTERSECT On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote: "(SELECT messages.msgid FROM messages, subject_index WHERE ((subject_index.word='Hello' or subject_index.word='There') and (subject_index.msgid = messages.msgid)) GROUP BY messages.msgid HAVING count(messages.msgid)=2) INTERSECT (SELECT messages.msgid FROM messages, author_index WHERE ((author_index.word='Jim' or author_index.word='Jones') and (author_index.msgid = messages.msgid)) GROUP BY messages.msgid HAVING count(messages.msgid)=2);" Try removing first "GROUP BY messages.msgid HAVING count(messages.msgid)=2)" -- Igor Roboul, Unix System Administrator Programmer @ sanatorium "Raduga", Sochi, Russia http://www.brainbench.com/transcript.jsp?pid=304744
Re: [GENERAL] Re: JDBC Performance
I did try out Peter's new JDBC driver on two very simple query-only applications. It was about 15% slower on one of the apps and 10% faster on the other, compared with jdbc7.0-1.2.jar. It worked correctly on both tests. Neither is particularly representative, but the fact that one's faster an one's slower is surprising. I have not had the time to dig into why the discrepancy. - Keith -Original Message- From: Peter Mount [EMAIL PROTECTED] To: Gunnar R|nning [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; PGSQL-General [EMAIL PROTECTED] Date: Monday, October 09, 2000 6:55 AM Subject: Re: [GENERAL] Re: JDBC Performance On 9 Oct 2000, Gunnar R|nning wrote: Bruce Momjian [EMAIL PROTECTED] writes: Applied, and new files added. Scares me ;-) I just wanted to get some feedback and testers for this code before actually having it applied. I can also imagine Peter might have some comments on structure etc. since he got a lot better overview of the JDBC code as whole. There's going to be some structure changes happening, mainly with the conversion/optimisation work Gunnar's working on, but also with the Scrollable ResultSet and regression testing stuff I'm working on at the moment. Well did anyone review the changes or test them out ? The application I'm testing is hardly representative for all applications, so there may still be some bugs left in there. Which is why I need to get the regression stuff sorted out as soon as I can. Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Re: [GENERAL] Re: JDBC Performance
I did try out Gunnar's new JDBC driver on two very simple query-only applications. It was about 15% slower on one of the apps and 10% faster on the other, compared with jdbc7.0-1.2.jar. It worked correctly on both tests. Neither is particularly representative, but the fact that one's faster an one's slower is surprising. I have not had the time to dig into why the discrepancy. - Keith -Original Message- From: Peter Mount [EMAIL PROTECTED] To: Gunnar R|nning [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; PGSQL-General [EMAIL PROTECTED] Date: Monday, October 09, 2000 6:55 AM Subject: Re: [GENERAL] Re: JDBC Performance On 9 Oct 2000, Gunnar R|nning wrote: Bruce Momjian [EMAIL PROTECTED] writes: Applied, and new files added. Scares me ;-) I just wanted to get some feedback and testers for this code before actually having it applied. I can also imagine Peter might have some comments on structure etc. since he got a lot better overview of the JDBC code as whole. There's going to be some structure changes happening, mainly with the conversion/optimisation work Gunnar's working on, but also with the Scrollable ResultSet and regression testing stuff I'm working on at the moment. Well did anyone review the changes or test them out ? The application I'm testing is hardly representative for all applications, so there may still be some bugs left in there. Which is why I need to get the regression stuff sorted out as soon as I can. Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Re: [GENERAL] Re: JDBC Performance
Where can I get the source for the JDBC driver? -Original Message- From: Peter Mount [EMAIL PROTECTED] To: Gunnar R|nning [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]; PostgreSQL general mailing list [EMAIL PROTECTED]; Keith L. Musser [EMAIL PROTECTED] Date: Tuesday, October 03, 2000 4:57 AM Subject: Re: [GENERAL] Re: JDBC Performance On 2 Oct 2000, Gunnar R|nning wrote: Peter Mount [EMAIL PROTECTED] writes: I'm now off work for the next two weeks (off sick that is), so I'll have some more time now to get the driver up to date. I'm finishing off the outstanding stuff now, so this should be in CVS today (finally ;-) ) I hope you don't check in the patch I supplied before I get the time to fix the issue with metadata ? Not into CVS, but I have in my code base. Don't worry, I'm working on the regression stuff, so it'll get tested thoroughly before I check it in. -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
Re: [GENERAL] Re: JDBC Performance
Gunnar,
Your new JDBC driver (postgresql.jar, 29-Sept-2000, 14:47, 187K) caused
the following error.
Using these tables...
CREATE TABLE servers ( pid INT4 PRIMARY KEY, tableid INT2, host
TEXT, port INT4);
CREATE TABLE classes ( tableid INT2, classname TEXT, tablename
TEXT);
CREATE TABLE persistent ( pid INT4 PRIMARY KEY, tableid INT2);
CREATE TABLE test ( pid INT4 PRIMARY KEY, tableid INT2, my_string
TEXT, my_long INT8, my_double FLOAT8, ref INT8);
CREATE TABLE pids ( next_lpid INT4);
CREATE TABLE test2 ( pid INT4 PRIMARY KEY, tableid INT2, one INT4,
two INT2, three INT2, name TEXT, four FLOAT4, five FLOAT8, six INT8);
I run this select statement...
SELECT host, port FROM Servers WHERE PID=1;
Bad Integer int4
at org.postgresql.jdbc2.ResultSet.getInt(ResultSet.java:261)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:748)
at org.postgresql.jdbc2.ResultSet.getObject(ResultSet.java:789)
at com.idisys.odb.ODBManager.loadMain(ODBManager.java:655)
at com.idisys.odb.ODBManager.load(ODBManager.java:584)
at com.idisys.odb.ODBManager.getObject(ODBManager.java:790)
at com.idisys.odb.ODBManager.getServer(ODBManager.java:814)
at com.idisys.odb.Reference.getServer(Reference.java:27)
at com.idisys.odb.Reference.getURL(Reference.java:39)
at com.idisys.odb.Test.test(Test.java:319)
at com.idisys.odb.Test.main(Test.java:124)
- Keith
-Original Message-
From: Gunnar R|nning [EMAIL PROTECTED]
To: Peter Mount [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]; PostgreSQL general mailing list
[EMAIL PROTECTED]; Keith L. Musser [EMAIL PROTECTED]
Date: Friday, September 29, 2000 9:08 AM
Subject: Re: [GENERAL] Re: JDBC Performance
Peter Mount [EMAIL PROTECTED] writes:
Email them to me, as the modifications will break when I commit my
changes
(delayed due to stress related illness), and there's a lot of changes
in
there. I'm about to resume work in a few minutes.
Okay, I wrapped up the modifications now. I'm appending the patch
against
the current CVS. You can also find the patch and a precompiled version
of
the driver at :
http://www.candleweb.no/~gunnar/projects/pgsql/
The interesting part is the replacement of new byte[] with an
allocByte()
method called that uses a pool of different byte arrays. I first tried
using the JDK 1.2 datastructures to implement the pooling, but they had
too
much overhead so I created a couple of simple and dirty implementations
instead.
I also added ReceiveString() methods that can take byte[] array as
parameter. All the ReceiveString methods in Connection now uses one
shared
byte array instead of forcing ReceiveString to allocate a new one on
each
call.
Comments and test results from others are very welcome.
Maybe I will look into doing the custom char conversion this weekend,
as
the default implementation provided by Sun appears to be the current
bottleneck. As Tim Kientzle wrote in another mail, this implementation
is
instatiating a new converter object every time you do a conversion.
This is
is also pointed out has a bottleneck by OptimizeIT.
Regards,
Gunnar
? postgresql.jar
? lazy_result.diff
? bytecache.diff
? org/postgresql/DriverClass.java
Index: org/postgresql/Connection.java
===
RCS file:
/home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Co
nnection.java,v
retrieving revision 1.6
diff -c -r1.6 Connection.java
*** org/postgresql/Connection.java 2000/09/12 05:09:54 1.6
--- org/postgresql/Connection.java 2000/09/29 12:54:12
***
*** 81,86
--- 81,91
// The PID an cancellation key we get from the backend process
public int pid;
public int ckey;
+
+ // This receive_sbuf should be used by the different methods
+ // that call pg_stream.ReceiveString() in this Connection, so
+ // so we avoid uneccesary new allocations.
+ byte receive_sbuf[] = new byte[8192];
/**
* This is called by Class.forName() from within
org.postgresql.Driver
***
*** 165,171
// "User authentication failed"
//
throw new SQLException(pg_stream.ReceiveString
!(4096, getEncoding()));
case 'R':
// Get the type of request
--- 170,176
// "User authentication failed"
//
throw new SQLException(pg_stream.ReceiveString
!(receive_sbuf, 4096,
getEncoding()));
case 'R':
// Get the type of request
***
*** 236,242
case 'E':
case 'N':
throw new SQLException(pg_stream.ReceiveString
! (4096, getEncoding()));
default:
throw new PSQLException("postgresql.con.setup");
}
--- 241,247
case 'E':
case 'N':
throw new SQLExc
[GENERAL] Command names
I know all of you are accustomed to the command line interface for pgsql. (Of course, some of you created it!) However, I'd be interested if anyone else feels the way I do I would prefer to have a consistent set of names for the commands. For example, I propose the following: Instead of this use this. createdb pg_createdb createuser pg_createuser destroydb pg_destroydb initdb pg_initdb initlocation pg_initlocation pgaccess pg_accessgui pgadmin pg_admin pg_dump pg_dump pg_dumpall pg_dumpall postgres pg_singlepostmaster pg_master psql pg_access vacuumdb pg_vacuumdb ??? pg_help (list all these commands) Not that it is terribly difficult the way it is. But I simply think that a consistent set of names would help new users, and help to prevent conflicts with other commands in the PATH. Any comments? - Keith Keith L. MusserIntegrated Dynamics, Inc.812-371-email: [EMAIL PROTECTED]
[GENERAL] RE: JDBC Performance
The performance of my client Java app using JDBC to access PGSL is very finicky. Sometimes it's fast, and sometimes it's slow, depending on how much memory I'm allocating in my program. This appears to be an issue with the JVM I'm using on Linux. Performance is very consistent using HotSpot JDK1.3 on Windows NT. What is the best JVM to use on Linux? Does anybody have experience using the new HotSpot for Linux? Keith L. Musser Integrated Dynamics, Inc. 812-371- email: [EMAIL PROTECTED]
