--
-- citus_update_table_statistics.sql
--
-- Test citus_update_table_statistics function on both
-- hash and append distributed tables
-- This function updates shardlength, shardminvalue and shardmaxvalue
--
SET citus.next_shard_id TO 981000;
SET citus.next_placement_id TO 982000;
SET citus.shard_count TO 8;
SET citus.shard_replication_factor TO 2;
-- test with a hash-distributed table
-- here we update only shardlength, not shardminvalue and shardmaxvalue
CREATE TABLE test_table_statistics_hash (id int);
SELECT create_distributed_table('test_table_statistics_hash', 'id');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

-- populate table
INSERT INTO test_table_statistics_hash SELECT i FROM generate_series(0, 10000)i;
-- originally shardlength (size of the shard) is zero
SELECT
	ds.logicalrelid::regclass::text AS tablename,
	ds.shardid AS shardid,
    dsp.placementid AS placementid,
	shard_name(ds.logicalrelid, ds.shardid) AS shardname,
    ds.shardminvalue AS shardminvalue,
    ds.shardmaxvalue AS shardmaxvalue
FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid)
WHERE ds.logicalrelid::regclass::text in ('test_table_statistics_hash') AND dsp.shardlength = 0
ORDER BY 2, 3;
         tablename          | shardid | placementid |             shardname             | shardminvalue | shardmaxvalue
---------------------------------------------------------------------
 test_table_statistics_hash |  981000 |      982000 | test_table_statistics_hash_981000 | -2147483648   | -1610612737
 test_table_statistics_hash |  981000 |      982001 | test_table_statistics_hash_981000 | -2147483648   | -1610612737
 test_table_statistics_hash |  981001 |      982002 | test_table_statistics_hash_981001 | -1610612736   | -1073741825
 test_table_statistics_hash |  981001 |      982003 | test_table_statistics_hash_981001 | -1610612736   | -1073741825
 test_table_statistics_hash |  981002 |      982004 | test_table_statistics_hash_981002 | -1073741824   | -536870913
 test_table_statistics_hash |  981002 |      982005 | test_table_statistics_hash_981002 | -1073741824   | -536870913
 test_table_statistics_hash |  981003 |      982006 | test_table_statistics_hash_981003 | -536870912    | -1
 test_table_statistics_hash |  981003 |      982007 | test_table_statistics_hash_981003 | -536870912    | -1
 test_table_statistics_hash |  981004 |      982008 | test_table_statistics_hash_981004 | 0             | 536870911
 test_table_statistics_hash |  981004 |      982009 | test_table_statistics_hash_981004 | 0             | 536870911
 test_table_statistics_hash |  981005 |      982010 | test_table_statistics_hash_981005 | 536870912     | 1073741823
 test_table_statistics_hash |  981005 |      982011 | test_table_statistics_hash_981005 | 536870912     | 1073741823
 test_table_statistics_hash |  981006 |      982012 | test_table_statistics_hash_981006 | 1073741824    | 1610612735
 test_table_statistics_hash |  981006 |      982013 | test_table_statistics_hash_981006 | 1073741824    | 1610612735
 test_table_statistics_hash |  981007 |      982014 | test_table_statistics_hash_981007 | 1610612736    | 2147483647
 test_table_statistics_hash |  981007 |      982015 | test_table_statistics_hash_981007 | 1610612736    | 2147483647
(16 rows)

-- setting this to on in order to verify that we use a distributed transaction id
-- to run the size queries from different connections
-- this is going to help detect deadlocks
SET citus.log_remote_commands TO ON;
-- setting this to sequential in order to have a deterministic order
-- in the output of citus.log_remote_commands
SET citus.multi_shard_modify_mode TO sequential;
-- update table statistics and then check that shardlength has changed
-- but shardminvalue and shardmaxvalue stay the same because this is
-- a hash distributed table
SELECT citus_update_table_statistics('test_table_statistics_hash');
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT 0 AS shard_id, '' AS table_name LIMIT 0
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT shard_id, pg_total_relation_size(table_name) FROM (VALUES (981000, 'public.test_table_statistics_hash_981000'), (981001, 'public.test_table_statistics_hash_981001'), (981002, 'public.test_table_statistics_hash_981002'), (981003, 'public.test_table_statistics_hash_981003'), (981004, 'public.test_table_statistics_hash_981004'), (981005, 'public.test_table_statistics_hash_981005'), (981006, 'public.test_table_statistics_hash_981006'), (981007, 'public.test_table_statistics_hash_981007')) t(shard_id, table_name) WHERE to_regclass(table_name) IS NOT NULL
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT shard_id, pg_total_relation_size(table_name) FROM (VALUES (981000, 'public.test_table_statistics_hash_981000'), (981001, 'public.test_table_statistics_hash_981001'), (981002, 'public.test_table_statistics_hash_981002'), (981003, 'public.test_table_statistics_hash_981003'), (981004, 'public.test_table_statistics_hash_981004'), (981005, 'public.test_table_statistics_hash_981005'), (981006, 'public.test_table_statistics_hash_981006'), (981007, 'public.test_table_statistics_hash_981007')) t(shard_id, table_name) WHERE to_regclass(table_name) IS NOT NULL
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
 citus_update_table_statistics
---------------------------------------------------------------------

(1 row)

RESET citus.log_remote_commands;
RESET citus.multi_shard_modify_mode;
SELECT
	ds.logicalrelid::regclass::text AS tablename,
	ds.shardid AS shardid,
    dsp.placementid AS placementid,
	shard_name(ds.logicalrelid, ds.shardid) AS shardname,
    ds.shardminvalue as shardminvalue,
    ds.shardmaxvalue as shardmaxvalue
FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid)
WHERE ds.logicalrelid::regclass::text in ('test_table_statistics_hash') AND dsp.shardlength > 0
ORDER BY 2, 3;
         tablename          | shardid | placementid |             shardname             | shardminvalue | shardmaxvalue
---------------------------------------------------------------------
 test_table_statistics_hash |  981000 |      982000 | test_table_statistics_hash_981000 | -2147483648   | -1610612737
 test_table_statistics_hash |  981000 |      982001 | test_table_statistics_hash_981000 | -2147483648   | -1610612737
 test_table_statistics_hash |  981001 |      982002 | test_table_statistics_hash_981001 | -1610612736   | -1073741825
 test_table_statistics_hash |  981001 |      982003 | test_table_statistics_hash_981001 | -1610612736   | -1073741825
 test_table_statistics_hash |  981002 |      982004 | test_table_statistics_hash_981002 | -1073741824   | -536870913
 test_table_statistics_hash |  981002 |      982005 | test_table_statistics_hash_981002 | -1073741824   | -536870913
 test_table_statistics_hash |  981003 |      982006 | test_table_statistics_hash_981003 | -536870912    | -1
 test_table_statistics_hash |  981003 |      982007 | test_table_statistics_hash_981003 | -536870912    | -1
 test_table_statistics_hash |  981004 |      982008 | test_table_statistics_hash_981004 | 0             | 536870911
 test_table_statistics_hash |  981004 |      982009 | test_table_statistics_hash_981004 | 0             | 536870911
 test_table_statistics_hash |  981005 |      982010 | test_table_statistics_hash_981005 | 536870912     | 1073741823
 test_table_statistics_hash |  981005 |      982011 | test_table_statistics_hash_981005 | 536870912     | 1073741823
 test_table_statistics_hash |  981006 |      982012 | test_table_statistics_hash_981006 | 1073741824    | 1610612735
 test_table_statistics_hash |  981006 |      982013 | test_table_statistics_hash_981006 | 1073741824    | 1610612735
 test_table_statistics_hash |  981007 |      982014 | test_table_statistics_hash_981007 | 1610612736    | 2147483647
 test_table_statistics_hash |  981007 |      982015 | test_table_statistics_hash_981007 | 1610612736    | 2147483647
(16 rows)

-- check with an append-distributed table
-- here we update shardlength, shardminvalue and shardmaxvalue
CREATE TABLE test_table_statistics_append (id int);
SELECT create_distributed_table('test_table_statistics_append', 'id', 'append');
 create_distributed_table
---------------------------------------------------------------------

(1 row)

SELECT master_create_empty_shard('test_table_statistics_append') AS shardid1 \gset
SELECT master_create_empty_shard('test_table_statistics_append') AS shardid2 \gset
COPY test_table_statistics_append FROM PROGRAM 'echo 0 && echo 1 && echo 2 && echo 3' WITH (format 'csv', append_to_shard :shardid1);
COPY test_table_statistics_append FROM PROGRAM 'echo 4 && echo 5 && echo 6 && echo 7' WITH (format 'csv', append_to_shard :shardid2);
-- shardminvalue and shardmaxvalue are NULL
SELECT
	ds.logicalrelid::regclass::text AS tablename,
	ds.shardid AS shardid,
    dsp.placementid AS placementid,
	shard_name(ds.logicalrelid, ds.shardid) AS shardname,
    ds.shardminvalue as shardminvalue,
    ds.shardmaxvalue as shardmaxvalue
FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid)
WHERE ds.logicalrelid::regclass::text in ('test_table_statistics_append')
ORDER BY 2, 3;
          tablename           | shardid | placementid |              shardname              | shardminvalue | shardmaxvalue
---------------------------------------------------------------------
 test_table_statistics_append |  981008 |      982016 | test_table_statistics_append_981008 |               |
 test_table_statistics_append |  981008 |      982017 | test_table_statistics_append_981008 |               |
 test_table_statistics_append |  981009 |      982018 | test_table_statistics_append_981009 |               |
 test_table_statistics_append |  981009 |      982019 | test_table_statistics_append_981009 |               |
(4 rows)

-- delete some data to change shardminvalues of a shards
DELETE FROM test_table_statistics_append WHERE id = 0 OR id = 4;
SET citus.log_remote_commands TO ON;
SET citus.multi_shard_modify_mode TO sequential;
-- update table statistics and then check that shardminvalue has changed
-- shardlength (shardsize) is still 8192 since there is very few data
SELECT citus_update_table_statistics('test_table_statistics_append');
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT 0 AS shard_id, '' AS table_name LIMIT 0
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT shard_id, pg_total_relation_size(table_name) FROM (VALUES (981008, 'public.test_table_statistics_append_981008'), (981009, 'public.test_table_statistics_append_981009')) t(shard_id, table_name) WHERE to_regclass(table_name) IS NOT NULL
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT assign_distributed_transaction_id(xx, xx, 'xxxxxxx');
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing SELECT shard_id, pg_total_relation_size(table_name) FROM (VALUES (981008, 'public.test_table_statistics_append_981008'), (981009, 'public.test_table_statistics_append_981009')) t(shard_id, table_name) WHERE to_regclass(table_name) IS NOT NULL
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
NOTICE:  issuing COMMIT
DETAIL:  on server postgres@localhost:xxxxx connectionId: xxxxxxx
 citus_update_table_statistics
---------------------------------------------------------------------

(1 row)

RESET citus.log_remote_commands;
RESET citus.multi_shard_modify_mode;
SELECT
	ds.logicalrelid::regclass::text AS tablename,
	ds.shardid AS shardid,
    dsp.placementid AS placementid,
	shard_name(ds.logicalrelid, ds.shardid) AS shardname,
    ds.shardminvalue as shardminvalue,
    ds.shardmaxvalue as shardmaxvalue
FROM pg_dist_shard ds JOIN pg_dist_shard_placement dsp USING (shardid)
WHERE ds.logicalrelid::regclass::text in ('test_table_statistics_append')
ORDER BY 2, 3;
          tablename           | shardid | placementid |              shardname              | shardminvalue | shardmaxvalue
---------------------------------------------------------------------
 test_table_statistics_append |  981008 |      982016 | test_table_statistics_append_981008 |               |
 test_table_statistics_append |  981008 |      982017 | test_table_statistics_append_981008 |               |
 test_table_statistics_append |  981009 |      982018 | test_table_statistics_append_981009 |               |
 test_table_statistics_append |  981009 |      982019 | test_table_statistics_append_981009 |               |
(4 rows)

DROP TABLE test_table_statistics_hash, test_table_statistics_append;
ALTER SYSTEM RESET citus.shard_count;
ALTER SYSTEM RESET citus.shard_replication_factor;
