Cursor Problems

Hi,

  I have the following situation:

  I AM USING THE C LANGUAGE INTERFACE (LIBPQ) but I
have also tried the situation described below from
within psql and got the same results.

  I am using PostgreSQL version 6.4.

- I Have to keep a cursor for a select * from <table1>
open most of the time and sometimes I have to insert
into or update <table1>
- Other users may also open a cursor for a select *
from <table1> and may also insert into or update
<table1> concurrently
- Is there a way to do that using cursors?

  In my experiments this is what happened:

User 1
------

1 - BEGIN TRANSACTION;
2 - DECLARE c1 CURSOR FOR SELECT * FROM table1;
3 - INSERT INTO table1 VALUES (...);

At this time User 2 connects to the same Database and
does:

User 2
------
1 - BEGIN TRANSACTION;
2 - DECLARE c1 CURSOR FOR SELECT * FROM table1;

-----> At this time the statement does not complete
and stalls the psql shell until User 1 issues a COMMIT
TRANSACTION;

Is there a way to either return an error code and not
keep User 2 waiting undefinitely for User 1 to issue a
COMMIT TRANSACTION or complete the DECLARE statement
with only the tuples that already exist in the table?

Where can I find more info about this sort of
operation ?

    Thanks a lot!

Marcos Castro
email: mbdecastro@yahoo.com



__________________________________________________
Do You Yahoo!?
Yahoo! Photos -- now, 100 FREE prints!
http://photos.yahoo.com