bllewell
commented
1 year ago On the use of "ysql_suppress_unsupported_error=true"
All of the tests that are described in these comments:
- issue-6514.zip — Bllewell summary
- issue-6514-2.zip — Bllewell summary
- issue-6514-3.zip — Bllewell summary
and that are implemented in the attached .zip files were run after starting my YB cluster with this YSQL configuration parameter setting:
ysql_suppress_unsupported_error=trueThe default for this parameter is false. And when the default setting is used, every 0A000 occurrence, that in my tests is a warning and that I suppress by setting _client_minmessages to error, is a genuine error—meaning that any statement that causes 0A000 rolls back and has no effect.
issue-6514.zip — Bllewell summary
NOTE: the list that starts this report is wrong. Here are the statements that don't draw a warning (when _client_minmessages is set to warning):
fetch from cur;
fetch next from cur;
fetch forward from cur;
fetch :N from cur;
fetch forward :N from cur;where :N is an integer that's greater than zero.
This issue needs some testcases. In particular, it doesn't mention limitations in PL/pgSQL cursor functionality that stem from the underlying SQL functionality limitations.
I just tested everything using YB-2.17.0.0. My tests are attached.
It actually turns out that every operation that's listed above as "not yet supported" runs quietly without error and produces the expected result as long as you do this:
set client_min_messages = error;This suggests that, with another round of careful testing, this warning, and others like it for other variants of fetch, can simply be removed:
0A000: FETCH BACKWARD not supported yetSimilarly, move works fine when warnings are suppressed:
set client_min_messages = error;
create function s.f(n in int)
returns int
set search_path = pg_catalog, pg_temp
language plpgsql
as $body$
declare
cur refcursor := 'cur';
vv int;
begin
open cur for select v from s.t order by v;
move absolute n in cur;
fetch cur into vv;
return vv;
end;
$body$;
select s.f(4);Notice that, when _client_minmessages is warning, even create function when the body has a move statement draws the warning:
0A000: MOVE not supported yetYou get it again at runtime. But you also get the right result.
Create the testcase objects
The following shows the testcase concept. However, the real testcase (attached) uses a fine-grained control over _client_minmessages to set it to error only when this is needed.
Connect as an ordinary user to a database upon which it has the create privilege. First create and populate a suitable test table.
-- Notice that the entire testcase runs with this setting.
set client_min_messages = error;
drop schema if exists s cascade;
create schema s;
create table s.t(k serial primary key, v int not null);
insert into s.t(v) select g.v from generate_series(5, 25) as g(v);Next, create a procedure that declares a session-duration cursor. (There's no explicit PL/pgSQL statements that allow this. But dynamic SQL manages it without a problem.) The advantage of using a session-duration cursor for these tests is simply that you can create it once and forget about it without having to do everything within an explicitly transaction. (An an explicitly transaction makes ad hoc queries tedious because even a trivial typo breaks the txn so that you have to start again from scratch.)
create procedure s.declare_cur()
set search_path = pg_catalog, pg_temp
security definer
language plpgsql
as $body$
begin
begin
execute 'close cur;';
exception
when invalid_cursor_name then null;
end;
execute
'declare cur scroll cursor with hold '||
'for select v::text from s.t where v between 11 and 20 order by v;';
end;Next, create a function that encapsulates the various fetch operations, using (according to the lang formal argument) either raw SQL (via the execute statement) or the dedicated PL/pgSQL fetch statement variants.
create function s.fetched_row(lang in text, kind in text, cur in refcursor)
returns text
set search_path = pg_catalog, pg_temp
security definer
language plpgsql
as $body$
declare
v text;
begin
case lang
when 'sql' then
execute format('fetch %s from cur', kind) into v;
when 'plpgsql' then
case kind
when 'first' then
fetch first from cur into v;
when 'next' then
fetch next from cur into v;
when 'absolute 5' then
fetch absolute 5 from cur into v;
when 'relative 3' then
fetch relative 3 from cur into v;
when 'relative 0' then
fetch relative 0 from cur into v;
when 'relative -5' then
fetch relative -5 from cur into v;
when 'last' then
fetch last from cur into v;
when 'prior' then
fetch prior from cur into v;
end case;
end case;
return case
when v is null then null
else rpad(kind, 13)||': '||v
end;
end;
$body$;Next, create a function that encapsulates the various PL/pgSQL move statement variants, matching the variants one-to-one with the fetch variants.
create function s.move_report(kind in text, cur in refcursor)
returns text
set search_path = pg_catalog, pg_temp
security definer
language plpgsql
as $body$
begin
case kind
when 'first' then
move first in cur;
when 'next' then
move next in cur;
when 'absolute 5' then
move absolute 5 in cur;
when 'relative 3' then
move relative 3 in cur;
when 'relative 0' then
move relative 0 in cur;
when 'relative -5' then
move relative -5 in cur;
when 'last' then
move last in cur;
when 'prior' then
move prior in cur;
end case;
return kind;
end;
$body$;Finally, create a driver table function that exercises all the variants according to its lang formal argument. When lang is sql, it exercises all of the SQL fetch variants but, of course, there's no "move". When lang is plpgsql, it exercises all of the PL/pgSQL fetch variants and all of the PL/pgSQL move variants.
create function s.cur_rows_sql(lang in text, cur in refcursor)
returns table(z text)
set search_path = pg_catalog, pg_temp
security definer
language plpgsql
as $body$
declare
v text;
kind_first constant text not null := 'first';
kind_next constant text not null := 'next';
kind_abs_5 constant text not null := 'absolute 5';
kind_rel_3 constant text not null := 'relative 3';
kind_rel_0 constant text not null := 'relative 0';
kind_rel_neg_5 constant text not null := 'relative -5';
kind_last constant text not null := 'last';
kind_prior constant text not null := 'prior';
begin
z := s.fetched_row(lang, kind_first, cur); return next;
z := '-----'; return next;
loop
z := s.fetched_row(lang, kind_next, cur);
exit when z is null;
/* */ return next;
end loop;
z := '-----'; return next;
z := s.fetched_row(lang, kind_abs_5, cur); return next;
z := s.fetched_row(lang, kind_rel_3, cur); return next;
z := s.fetched_row(lang, kind_rel_0, cur); return next;
z := s.fetched_row(lang, kind_rel_0, cur); return next;
z := s.fetched_row(lang, kind_last, cur); return next;
z := s.fetched_row(lang, kind_rel_neg_5, cur); return next;
z := '-----'; return next;
loop
z := s.fetched_row(lang, kind_prior, cur);
exit when z is null;
/* */ return next;
end loop;
if lang = 'plpgsql' then
z := '----- "move" tests (plpgsql only)'; return next;
begin
z := s.move_report(kind_first, cur); return next;
z := s.fetched_row(lang, kind_next, cur); return next;
z := s.move_report(kind_abs_5, cur); return next;
z := s.fetched_row(lang, kind_next, cur); return next;
z := s.move_report(kind_rel_3, cur); return next;
z := s.fetched_row(lang, kind_next, cur); return next;
z := s.move_report(kind_last, cur); return next;
z := s.move_report(kind_rel_neg_5, cur); return next;
z := s.fetched_row(lang, kind_next, cur); return next;
end;
end if;
end;
$body$;Run the testcase
Run it first in YB and then in vanilla PG. Spool the output (using the "\o" meta-command), using suitable names like yb.txt and pg.txt, so that you can diff them.
First, create the session-duration cursor and execute the few SQL fetch variants that get many rows each. (These cannot be used from PL/pgSQL.)
call s.declare_cur();
fetch 3 from cur;
fetch forward 2 from cur;
fetch forward all from cur;
fetch backward 4 from cur;
fetch backward all from cur;Now invoke the _s.cur_rowssql() encapsulation using both its sql mode and its plpgsql mode:
select z from s.cur_rows_sql('sql', 'cur');
select z from s.cur_rows_sql('plpgsql', 'cur');Diffing the two spool files shows that they are identical.
The attached testcase
This is presented as a single file, issue-6514.sql inside the attached issue-6514.zip. It's totally self-contained and you can simply start it at the ysqlsh or the psql prompt—with one caveat: it starts with this:
drop schema if exists s cascade;So, of course, you'll have to improvise a bit if the database that you connect to happens already to have a schema called s that's owned by a role other than the one as which you connect. Notice that the script uses this device to spool to a file that reflects whether you run the test in YB or PG:
select (version() like '%YB%')::text as is_yb
\gset
\if :is_yb
\o yb.txt
\else
\o pg.txt
\endifThe attached issue-6514.zip also contains the spool files yb.txt and pg.txt that were generated just before making the .zip.
Here, the code decorates each output line with an asterisk if it required warnings to be suppressed. This means that diff will indeed shown differences because, of course, you don't get any warnings in PG and so there's no need to suppress them. However, it's very easy to read over these asterisk as see that the actual returned values are identical in PB and PG.
Jira Link: DB-4854 The following FETCH options are not yet supported.