[YSQL] Support FETCH NEXT, FETCH FORWARD <count>, FETCH <count> variants on cursors

[kmuthukk]

We already support (after the work done in #3286), the basic DECLARE, FETCH, CLOSE operations.

Note that the default FETCH <cursor_name>, which is effectively FETCH FORWARD 1 <cursor_name> is supported.

But other "forward" variants which are pretty similar (either syntax sugar, or forward with a count variant), such as these are not yet supported in YugabyteDB.

• FETCH NEXT cursor;
• FETCH 10 cursor;
• FETCH FORWARD 10 cursor;
  [See: https://www.postgresql.org/docs/9.1/sql-fetch.html]

When using python psycopg2 drivers, with named cursors and the fetchone() or fetchmany() API -- the driver uses underneath the covers the FETCH FORWARD <n> syntax. Therefore, this prevents the cursor feature from being usable in Python right now

Test Case:

Find below two test cases (one ysqlsh based, and another python base) to illustrate the problem.

Created a sample table like (specifics don't matter):

CREATE TABLE IF NOT EXISTS items (id integer, color text, PRIMARY KEY(id))

Here's a ysqlsh based test case:

yugabyte=# begin;
BEGIN
yugabyte=# DECLARE foobar CURSOR FOR Select * from ITEMS;
DECLARE CURSOR
yugabyte=# FETCH foobar;
 id  | color
-----+-------
 854 | green
(1 row)

yugabyte=# FETCH foobar;
  id  | color
------+-------
 1862 | green
(1 row)

yugabyte=# FETCH NEXT foobar;
ERROR:  FETCH NEXT not supported yet
LINE 1: FETCH NEXT foobar;
              ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/6514. Click '+' on the description to raise its priority
yugabyte=# FETCH FORWARD 1 foobar;
ERROR:  FETCH FORWARD not supported yet
LINE 1: FETCH FORWARD 1 foobar;
              ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/6514. Click '+' on the description to raise its priority

yugabyte=# fetch 1 foobar;
ERROR:  FETCH + OR - not supported yet
             ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/6514. Click '+' on the description to raise its priority

Python Test case:

import psycopg2
from threading import Thread,Semaphore
import random

# Test Params:
num_items=2500
percentage_red=5  # about 5% rows are red and remaining are green.

cluster_ip="localhost"
connect_string="host={} dbname=yugabyte user=yugabyte port=5433".format(cluster_ip)

def create_table():
  conn = psycopg2.connect(connect_string)
  conn.set_session(autocommit=True)
  cur = conn.cursor()
  cur.execute("""DROP TABLE IF EXISTS items""");
  print("Dropped (if exists): items table")
  print("====================")
  cur.execute("""
        CREATE TABLE IF NOT EXISTS items (
           id     integer,
           color  text,
           PRIMARY KEY(id)
        )
    """)
  print("Created items table.")
  cur.close()
  conn.close()

def init_rows():
  conn = psycopg2.connect(connect_string)
  conn.set_session(autocommit=True)
  cur = conn.cursor();
  print("Populating rows")

  for idx in range(num_items):
    if (random.randint(1, 100) <= percentage_red):
      color = 'red'
    else:
      color = 'green'
    cur.execute("""INSERT INTO items(id, color) VALUES (%s, %s)""", (idx, color))
  print("====================")
  cur.close()
  conn.close()

def scan_rows():
  scan_conn = psycopg2.connect(connect_string)
  delete_conn = psycopg2.connect(connect_string)

  txn_cur = scan_conn.cursor();
  txn_cur.execute("""BEGIN ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE""")

  scan_cur = scan_conn.cursor(name='fetch_large_result')
  scan_cur.execute("SELECT id, color FROM items")
  rows = scan_cur.fetchmany(500)
  row_cnt = 0
  while (rows):
    print("next batch..")
    for r in rows:
      row_cnt += 1
    rows = scan_cur.fetchmany(500)

  print("Total matching rows: {}".format(row_cnt))

  txn_cur.close()
  scan_cur.close()
  scan_conn.close()

# Main
create_table()
init_rows()
scan_rows();

Observed Error:

$ python ~/notes/ysql_named_cursor.py
Dropped (if exists): items table
====================
Created items table.
Populating rows
====================
Traceback (most recent call last):
  File "/home/centos/notes/ysql_named_cursor.py", line 72, in <module>
    scan_rows();
  File "/home/centos/notes/ysql_named_cursor.py", line 55, in scan_rows
    rows = scan_cur.fetchmany(500)
psycopg2.errors.FeatureNotSupported: FETCH FORWARD not supported yet
LINE 1: FETCH FORWARD 500 FROM "fetch_large_result"
              ^
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/6514. Click '+' on the description to raise its priority

[m-iancu]

Fixed by 5f96f45.