Haris,
This is not possible on the client. The protocol that clients use to
talk to the server (the front end/back end protocol (FE/BE)) only allows
one method for getting the results of a sql statement. It is only
possible via the FE/BE protocol to get the entire result. Which is why
the code works the way it does. The work Nic is doing works around this
limitation by issuing multiple sql statements (declare cursor, fetch
etc.) but it is still the case that the entire result of each statement
must be read before any additional calls can be made.
What you are pointing out here is the C interface on the server, which
is not limited by the capabilities of the FE/BE protocol. But I still
beleive that even using the server side C interface, cursors are limited
to a single transaction, thus your original problem still exists.
thanks,
--Barry
Haris Peco wrote:
Hello Barry,
What think You about next idea
I have study C interface and it is easy solve my problem without cursor.
JDBC load complete qyery result in method QueryExecutor.execute
I think that method QueryExecutor.execute work like now except for query
(select) - for select this method should do something like PQExec method
in C (this is compatible with ResultSet in JDBC specification)
When we call ResultSet.next (or like method) we should be initialize
fetchSize rows and load rows with method like C method PQgetvalue -
consecutive call ResultSet.next should scroll row window (length for window is
fetchSize)
and if need call new fetchSize rows with method like PQgetvalue
'OutOfmemory' error for large table is not because Postgresql do so than
JDBC QueryExecutor.execute method load all rows.
If this work in C without cursor then no reason that work in Java
I have tried more queries with large table in C and it work fine without
cursors.
regards
Haris Peco
On Monday 18 November 2002 05:54 pm, Barry Lind wrote:
Haris,
I understand your problem. But unless the database supports cursors
that span transactions, I don't see any solution for you, other than to
issue multiple sql statements to mimic cross transaction queries in your
application.
--Barry
Haris Peco wrote:
On Monday 18 November 2002 05:14 pm, Barry Lind wrote:
Nic,
Here are my thoughts on this topic.
1) Since the server doesn't support cursors across transactions, I don't
think the driver should either. In fact in jdbc3 the DatabaseMetaData
object has a supportsResultSetHoldability() method that explicitly lets
the driver tell the application what is does/doesn't support in this
area.
I think running multiple sql statements to mimic this behavior is a very
bad idea. Since the select statements will run at different times they
will return different data (since they will pick up commited changes
between runs), and if you don't include an order by the results are
completely unpredictable. If someone wants this very unpredictable
behavior they can issue the multiple statements themselves.
2) I think the use of cursors should be optional. In fact since most
queries don't need them since most queries return a small number of rows
, I think the use of cursors needs to be turned on. I think there
should be two ways to do this: the first is by setting the fetchSize()
and the second would be a jdbc url parameter.
3) I think the transaction characteristics of the current patch are just
fine and conform to the jdbc specification. The code should
automatically close the resultset when a commit occurs. One thing that
will be confusing is that noncursor based result sets will work accross
commits, but cursor based ones won't. But I think that is reasonable.
My problem :
master-detail
I select one from many rows master with cursor (big table and only this
is possible) - In detail I do change and commit (or rollback)
My select is lost.
How can I do that ?
Nic Ferrier wrote:
Message-ID: <87fztyexea.fsf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
Lines: 24
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
--text follows this line--
Haris Peco <snpe@xxxxxxxxxx> writes:
I have tried.
DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't
work
What error do you get?
Prepared command don't work, but my greatest problem are trasnaction
I hope that cursor in 7.4 will be out of a transaction
I don't think there's much I can do about the cursor problem.
Can Yoy yet another :
set driver's flag btw jdbc:pgsql:...?cursor=yes
for use cursor or old way
What does everyone else think? Is a system doing a different query
each time worth looking into?
Nic
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
