src/test/regress/expected/autostats.out

-- start_matchsubs
-- m/^LOG:  In mode on_change, command INSERT.* modifying 10 tuples caused Auto-ANALYZE./
-- s/\(dboid,tableoid\)=\(\d+,\d+\)/\(dboid,tableoid\)=\(XXXXX,XXXXX\)/
-- m/LOG:  Auto-stats did not issue ANALYZE on tableoid \d+ since the user does not have table-owner level permissions./
-- s/tableoid \d+/tableoid XXXXX/
-- end_matchsubs
-- start_matchignore
-- m/^LOG: .*Feature not supported: Queries on master-only tables./
-- m/^LOG: .*Falling back to Postgres-based planner because GPORCA does not support the following feature: Queries on master-only tables./
-- m/^LOG:.*ERROR,"PG exception raised"/
-- end_matchignore
set gp_autostats_mode=on_change;
set gp_autostats_on_change_threshold=9;
set log_autostats=on;
set client_min_messages=log;
reset optimizer_trace_fallback;
LOG:  statement: reset optimizer_trace_fallback;
drop table if exists autostats_test;
LOG:  statement: drop table if exists autostats_test;
NOTICE:  table "autostats_test" does not exist, skipping
create table autostats_test (a INTEGER);
LOG:  statement: create table autostats_test (a INTEGER);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
drop user if exists autostats_nonowner;
LOG:  statement: drop user if exists autostats_nonowner;
NOTICE:  role "autostats_nonowner" does not exist, skipping
create user autostats_nonowner;
LOG:  statement: create user autostats_nonowner;
NOTICE:  resource queue required -- using default resource queue "pg_default"
-- Make sure rel_tuples starts at zero
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        -1
(1 row)

-- Try it with gp_autostats_allow_nonowner GUC enabled, but as a non-owner
-- without INSERT permission.  Should fail with permission denied, without
-- triggering autostats collection
set gp_autostats_allow_nonowner=on;
LOG:  statement: set gp_autostats_allow_nonowner=on;
set role=autostats_nonowner;
LOG:  statement: set role=autostats_nonowner;
insert into autostats_test select generate_series(1, 10);
LOG:  statement: insert into autostats_test select generate_series(1, 10);
LOG:  An exception was encountered during the execution of statement: insert into autostats_test select generate_series(1, 10);
ERROR:  permission denied for table autostats_test
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        -1
(1 row)

reset role;
LOG:  statement: reset role;
-- Try it with GUC enabled, after granting INSERT, stats should update to 10
grant insert on table autostats_test to autostats_nonowner;
LOG:  statement: grant insert on table autostats_test to autostats_nonowner;
set role=autostats_nonowner;
LOG:  statement: set role=autostats_nonowner;
insert into autostats_test select generate_series(11, 20);
LOG:  statement: insert into autostats_test select generate_series(11, 20);
LOG:  In mode on_change, command INSERT on (dboid,tableoid)=(XXXXX,XXXXX) modifying 10 tuples caused Auto-ANALYZE.
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        10
(1 row)

-- Try running analyze manually as nonowner, should fail
set role=autostats_nonowner;
LOG:  statement: set role=autostats_nonowner;
analyze autostats_test;
LOG:  statement: analyze autostats_test;
WARNING:  skipping "autostats_test" --- only table or database owner can analyze it
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        10
(1 row)

-- Try to disable allow_nonowner GUC as ordinary user, should fail
set gp_autostats_allow_nonowner=off;
LOG:  statement: set gp_autostats_allow_nonowner=off;
LOG:  An exception was encountered during the execution of statement: set gp_autostats_allow_nonowner=off;
ERROR:  permission denied to set parameter "gp_autostats_allow_nonowner"
show gp_autostats_allow_nonowner;
LOG:  statement: show gp_autostats_allow_nonowner;
 gp_autostats_allow_nonowner 
-----------------------------
 on
(1 row)

-- GUC should still be enabled, stats should update to 20
insert into autostats_test select generate_series(21, 30);
LOG:  statement: insert into autostats_test select generate_series(21, 30);
LOG:  In mode on_change, command INSERT on (dboid,tableoid)=(XXXXX,XXXXX) modifying 10 tuples caused Auto-ANALYZE.
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        20
(1 row)

reset role;
LOG:  statement: reset role;
-- Change allow_nonowner GUC as admin, should work
set gp_autostats_allow_nonowner=off;
LOG:  statement: set gp_autostats_allow_nonowner=off;
show gp_autostats_allow_nonowner;
LOG:  statement: show gp_autostats_allow_nonowner;
 gp_autostats_allow_nonowner 
-----------------------------
 off
(1 row)

-- GUC should be disabled, stats should not update
set role=autostats_nonowner;
LOG:  statement: set role=autostats_nonowner;
insert into autostats_test select generate_series(31, 40);
LOG:  statement: insert into autostats_test select generate_series(31, 40);
LOG:  In mode on_change, command INSERT on (dboid,tableoid)=(XXXXX,XXXXX) modifying 10 tuples caused Auto-ANALYZE.
LOG:  Auto-stats did not issue ANALYZE on tableoid XXXXX since the user does not have table-owner level permissions.
select relname, reltuples from pg_class where relname='autostats_test';
LOG:  statement: select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        20
(1 row)

reset role;
LOG:  statement: reset role;
-- Try to enable allow_nonowner GUC as ordinary user, should fail
-- GUC should still be disabled, stats should update from 20 to 40
insert into autostats_test select generate_series(21, 30);
LOG:  statement: insert into autostats_test select generate_series(21, 30);
LOG:  In mode on_change, command INSERT on (dboid,tableoid)=(XXXXX,XXXXX) modifying 10 tuples caused Auto-ANALYZE.
reset client_min_messages;
LOG:  statement: reset client_min_messages;
select relname, reltuples from pg_class where relname='autostats_test';
    relname     | reltuples 
----------------+-----------
 autostats_test |        40
(1 row)

reset role;
-- After 4 successful inserts, final row count should also be 40
select COUNT(*) from autostats_test;
 count 
-------
    40
(1 row)

drop table if exists autostats_test;
drop user autostats_nonowner;
-- test inFunction
-- udf
create function test_auto_stats_in_function(sql text, load_data boolean, check_relname text) returns void as
$$
declare
  ntuples int;
begin
  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=none;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=on_no_stats;
  set gp_autostats_mode_in_functions=none;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=on_no_stats;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=on_no_stats;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=%', ntuples;
  set gp_autostats_mode_in_functions=on_change;
  set gp_autostats_on_change_threshold=0;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';
end;
$$
language plpgsql;
select test_auto_stats_in_function('copy t_test_auto_stats_in_function from program ''echo 1''',
                                   false, 't_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=-1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
 test_auto_stats_in_function 
-----------------------------
 
(1 row)

select test_auto_stats_in_function('create table tmp_test_auto_stats_in_function as select * from t_test_auto_stats_in_function distributed randomly',
                                   true, 'tmp_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=<NULL>
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=1
 test_auto_stats_in_function 
-----------------------------
 
(1 row)

select test_auto_stats_in_function('delete from t_test_auto_stats_in_function',
                                   true, 't_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=0
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
 test_auto_stats_in_function 
-----------------------------
 
(1 row)

drop function test_auto_stats_in_function(text, boolean, text);
-- procedure
create procedure test_auto_stats_in_function(sql text, load_data boolean, check_relname text) as
$$
declare
  ntuples int;
begin
  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=none;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=on_no_stats;
  set gp_autostats_mode_in_functions=none;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=on_no_stats;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';

  execute 'create table t_test_auto_stats_in_function(a int) distributed randomly';
  set gp_autostats_mode=none;
  set gp_autostats_mode_in_functions=on_no_stats;
  if load_data then execute 'insert into t_test_auto_stats_in_function values (1)'; end if;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=%', ntuples;
  set gp_autostats_mode_in_functions=on_change;
  set gp_autostats_on_change_threshold=0;
  execute sql;
  select reltuples into ntuples from pg_class where relname = check_relname;
  raise info 'gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=%', ntuples;
  execute 'drop table t_test_auto_stats_in_function';
  execute 'drop table if exists tmp_test_auto_stats_in_function';
end;
$$
language plpgsql;
call test_auto_stats_in_function('copy t_test_auto_stats_in_function from program ''echo 1''',
                                   false, 't_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=-1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
call test_auto_stats_in_function('create table tmp_test_auto_stats_in_function as select * from t_test_auto_stats_in_function distributed randomly',
                                   true, 'tmp_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=<NULL>
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=1
call test_auto_stats_in_function('delete from t_test_auto_stats_in_function',
                                   true, 't_test_auto_stats_in_function');
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=on_no_stats, gp_autostats_mode_in_functions=none, ntuples=-1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_no_stats, ntuples=1
INFO:  gp_autostats_mode=none, gp_autostats_mode_in_functions=on_change, ntuples=0
NOTICE:  table "tmp_test_auto_stats_in_function" does not exist, skipping
drop procedure test_auto_stats_in_function(text, boolean, text);
create table t_test_auto_stats_in_function(a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
set gp_autostats_mode_in_functions = none;
set gp_autostats_mode = on_no_stats;
copy t_test_auto_stats_in_function from program 'echo 1';
select reltuples from pg_class where relname = 't_test_auto_stats_in_function';
 reltuples 
-----------
         1
(1 row)

drop table t_test_auto_stats_in_function;
create table t_test_auto_stats_in_function(a int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
copy t_test_auto_stats_in_function from program 'echo 1';
set gp_autostats_mode_in_functions = none;
set gp_autostats_mode = on_no_stats;
create table tmp_test_auto_stats_in_function as select * from t_test_auto_stats_in_function distributed randomly;
select reltuples from pg_class where relname = 't_test_auto_stats_in_function';
 reltuples 
-----------
         1
(1 row)

drop table t_test_auto_stats_in_function;
drop table tmp_test_auto_stats_in_function;
-- reset GUCs
reset gp_autostats_mode;
reset gp_autostats_mode_in_functions;
reset gp_autostats_on_change_threshold;
reset log_autostats;
reset gp_autostats_allow_nonowner;