Scrolling/Updating Cursors

On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote:

My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everything I can think of and cannot figure it out myself.

The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rows and see those changes?

Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provide it if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would do the trick but it still seems to see no changes (inserts/updates/deletes).

Effectively I want roughly this sequence of events to work.

CREATE TABLE test (val text);

BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ

DECLARE c1 CURSOR FOR SELECT * FROM test;

FETCH ABSOLUTE 1; # Returns No Row

INSERT INTO test VALUES (‘1');
INSERT INTO test VALUES (‘2');
INSERT INTO test VALUES (‘3');

FETCH ABSOLUTE 1; # Returns “1”
FETCH NEXT: # Returns “2"
DELETE FROM test WHERE CURRENT OF c1; # Deletes “2”

FETCH ABSOLUTE 1; # Returns “1”
FETCH NEXT; # Returns “3”
UPDATE test SET val=‘4' WHERE CURRENT OF c1;
FETCH RELATIVE 0; # Returns “4”

COMMIT;

Thanks!

-kw

Should I take silence to mean this cannot be done? Or should I send to -hackers?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Why not both? I keep on searching even when I post up a question.

On 11/22/2013 2:27 PM, Kevin Wooten wrote:

On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote:

My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everything I can think of and cannot figure it out myself.

The basic question is� Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rows and see those changes?

Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I�d like to provide it if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would do the trick but it still seems to see no changes (inserts/updates/deletes).

Effectively I want roughly this sequence of events to work.

CREATE TABLE test (val text);

BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ

DECLARE c1 CURSOR FOR SELECT * FROM test;

FETCH ABSOLUTE 1; # Returns No Row

INSERT INTO test VALUES (�1');
INSERT INTO test VALUES (�2');
INSERT INTO test VALUES (�3');

FETCH ABSOLUTE 1; # Returns �1�
FETCH NEXT: # Returns �2"
DELETE FROM test WHERE CURRENT OF c1; # Deletes �2�

FETCH ABSOLUTE 1; # Returns �1�
FETCH NEXT; # Returns �3�
UPDATE test SET val=�4' WHERE CURRENT OF c1;
FETCH RELATIVE 0; # Returns �4�

COMMIT;

Thanks!

-kw

Should I take silence to mean this cannot be done? Or should I send to -hackers?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Nov 22, 2013, at 2:32 PM, John Meyer <johnmeyer@pueblocomputing.com> wrote:

Why not both? I keep on searching even when I post up a question.

Just attempting to be polite and not cross post to too much. I have pretty much exhausted my own search and now am relying on the kindness and knowledge of others ;)

On 11/22/2013 2:27 PM, Kevin Wooten wrote:

On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote:

My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everything I can think of and cannot figure it out myself.

The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rows and see those changes?

Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provide it if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would do the trick but it still seems to see no changes (inserts/updates/deletes).

Effectively I want roughly this sequence of events to work.

CREATE TABLE test (val text);

BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ

DECLARE c1 CURSOR FOR SELECT * FROM test;

FETCH ABSOLUTE 1; # Returns No Row

INSERT INTO test VALUES (‘1');
INSERT INTO test VALUES (‘2');
INSERT INTO test VALUES (‘3');

FETCH ABSOLUTE 1; # Returns “1”
FETCH NEXT: # Returns “2"
DELETE FROM test WHERE CURRENT OF c1; # Deletes “2”

FETCH ABSOLUTE 1; # Returns “1”
FETCH NEXT; # Returns “3”
UPDATE test SET val=‘4' WHERE CURRENT OF c1;
FETCH RELATIVE 0; # Returns “4”

COMMIT;

Thanks!

-kw

Should I take silence to mean this cannot be done? Or should I send to -hackers?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general