--
-- Test the TRUNCATE TABLE command for columnar tables.
--
-- print whether we're using version > 10 to make version-specific tests clear
SHOW server_version \gset
SELECT substring(:'server_version', '\d+')::int > 10 AS version_above_ten;
 version_above_ten
---------------------------------------------------------------------
 t
(1 row)

-- CREATE a columnar table, fill with some data --
CREATE TABLE columnar_truncate_test (a int, b int) USING columnar;
CREATE TABLE columnar_truncate_test_second (a int, b int) USING columnar;
-- COMPRESSED
CREATE TABLE columnar_truncate_test_compressed (a int, b int) USING columnar;
CREATE TABLE columnar_truncate_test_regular (a int, b int);
SELECT count(distinct storage_id) AS columnar_data_files_before_truncate FROM columnar.stripe \gset
INSERT INTO columnar_truncate_test select a, a from generate_series(1, 10) a;
set columnar.compression = 'pglz';
INSERT INTO columnar_truncate_test_compressed select a, a from generate_series(1, 10) a;
INSERT INTO columnar_truncate_test_compressed select a, a from generate_series(1, 10) a;
set columnar.compression to default;
-- query rows
SELECT * FROM columnar_truncate_test;
 a  | b
---------------------------------------------------------------------
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)

SELECT * FROM chunk_group_consistency;
 consistent
---------------------------------------------------------------------
 t
(1 row)

TRUNCATE TABLE columnar_truncate_test;
SELECT * FROM chunk_group_consistency;
 consistent
---------------------------------------------------------------------
 t
(1 row)

SELECT * FROM columnar_truncate_test;
 a | b
---------------------------------------------------------------------
(0 rows)

SELECT COUNT(*) from columnar_truncate_test;
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT count(*) FROM columnar_truncate_test_compressed;
 count
---------------------------------------------------------------------
    20
(1 row)

TRUNCATE TABLE columnar_truncate_test_compressed;
SELECT count(*) FROM columnar_truncate_test_compressed;
 count
---------------------------------------------------------------------
     0
(1 row)

SELECT pg_relation_size('columnar_truncate_test_compressed');
 pg_relation_size
---------------------------------------------------------------------
                0
(1 row)

INSERT INTO columnar_truncate_test select a, a from generate_series(1, 10) a;
INSERT INTO columnar_truncate_test_regular select a, a from generate_series(10, 20) a;
INSERT INTO columnar_truncate_test_second select a, a from generate_series(20, 30) a;
SELECT * from columnar_truncate_test;
 a  | b
---------------------------------------------------------------------
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)

SELECT * from columnar_truncate_test_second;
 a  | b
---------------------------------------------------------------------
 20 | 20
 21 | 21
 22 | 22
 23 | 23
 24 | 24
 25 | 25
 26 | 26
 27 | 27
 28 | 28
 29 | 29
 30 | 30
(11 rows)

SELECT * from columnar_truncate_test_regular;
 a  | b
---------------------------------------------------------------------
 10 | 10
 11 | 11
 12 | 12
 13 | 13
 14 | 14
 15 | 15
 16 | 16
 17 | 17
 18 | 18
 19 | 19
 20 | 20
(11 rows)

SELECT * FROM chunk_group_consistency;
 consistent
---------------------------------------------------------------------
 t
(1 row)

-- make sure multi truncate works
-- notice that the same table might be repeated
TRUNCATE TABLE columnar_truncate_test,
			   columnar_truncate_test_regular,
			   columnar_truncate_test_second,
   			   columnar_truncate_test;
SELECT * FROM chunk_group_consistency;
 consistent
---------------------------------------------------------------------
 t
(1 row)

SELECT * from columnar_truncate_test;
 a | b
---------------------------------------------------------------------
(0 rows)

SELECT * from columnar_truncate_test_second;
 a | b
---------------------------------------------------------------------
(0 rows)

SELECT * from columnar_truncate_test_regular;
 a | b
---------------------------------------------------------------------
(0 rows)

-- test if truncate on empty table works
TRUNCATE TABLE columnar_truncate_test;
SELECT * from columnar_truncate_test;
 a | b
---------------------------------------------------------------------
(0 rows)

-- make sure TRUNATE deletes metadata for old relfilenode
SELECT :columnar_data_files_before_truncate - count(distinct storage_id) FROM columnar.stripe;
 ?column?
---------------------------------------------------------------------
        0
(1 row)

-- test if truncation in the same transaction that created the table works properly
BEGIN;
CREATE TABLE columnar_same_transaction_truncate(a int) USING columnar;
INSERT INTO columnar_same_transaction_truncate SELECT * FROM generate_series(1, 100);
TRUNCATE columnar_same_transaction_truncate;
INSERT INTO columnar_same_transaction_truncate SELECT * FROM generate_series(20, 23);
COMMIT;
-- should output "1" for the newly created relation
SELECT count(distinct storage_id) - :columnar_data_files_before_truncate FROM columnar.stripe;
 ?column?
---------------------------------------------------------------------
        1
(1 row)

SELECT * FROM columnar_same_transaction_truncate;
 a
---------------------------------------------------------------------
 20
 21
 22
 23
(4 rows)

DROP TABLE columnar_same_transaction_truncate;
-- test if a cached truncate from a pl/pgsql function works
CREATE FUNCTION columnar_truncate_test_regular_func() RETURNS void AS $$
BEGIN
	INSERT INTO columnar_truncate_test_regular select a, a from generate_series(1, 10) a;
	TRUNCATE TABLE columnar_truncate_test_regular;
END;$$
LANGUAGE plpgsql;
SELECT columnar_truncate_test_regular_func();
 columnar_truncate_test_regular_func
---------------------------------------------------------------------

(1 row)

-- the cached plans are used stating from the second call
SELECT columnar_truncate_test_regular_func();
 columnar_truncate_test_regular_func
---------------------------------------------------------------------

(1 row)

DROP FUNCTION columnar_truncate_test_regular_func();
DROP TABLE columnar_truncate_test, columnar_truncate_test_second;
DROP TABLE columnar_truncate_test_regular;
DROP TABLE columnar_truncate_test_compressed;
-- test truncate with schema
CREATE SCHEMA truncate_schema;
-- COMPRESSED
CREATE TABLE truncate_schema.truncate_tbl (id int) USING columnar;
set columnar.compression = 'pglz';
INSERT INTO truncate_schema.truncate_tbl SELECT generate_series(1, 100);
set columnar.compression to default;
SELECT COUNT(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
   100
(1 row)

TRUNCATE TABLE truncate_schema.truncate_tbl;
SELECT COUNT(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
     0
(1 row)

set columnar.compression = 'pglz';
INSERT INTO truncate_schema.truncate_tbl SELECT generate_series(1, 100);
set columnar.compression to default;
-- create a user that can not truncate
CREATE USER truncate_user;
NOTICE:  not propagating CREATE ROLE/USER commands to worker nodes
HINT:  Connect to worker nodes directly to manually create all necessary users and roles.
GRANT USAGE ON SCHEMA truncate_schema TO truncate_user;
GRANT SELECT ON TABLE truncate_schema.truncate_tbl TO truncate_user;
REVOKE TRUNCATE ON TABLE truncate_schema.truncate_tbl FROM truncate_user;
SELECT current_user \gset
\c - truncate_user
-- verify truncate command fails and check number of rows
SELECT count(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
   100
(1 row)

TRUNCATE TABLE truncate_schema.truncate_tbl;
ERROR:  permission denied for table truncate_tbl
SELECT count(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
   100
(1 row)

-- switch to super user, grant truncate to truncate_user
\c - :current_user
GRANT TRUNCATE ON TABLE truncate_schema.truncate_tbl TO truncate_user;
-- verify truncate_user can truncate now
\c - truncate_user
SELECT count(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
   100
(1 row)

TRUNCATE TABLE truncate_schema.truncate_tbl;
SELECT count(*) FROM truncate_schema.truncate_tbl;
 count
---------------------------------------------------------------------
     0
(1 row)

\c - :current_user
SELECT * FROM chunk_group_consistency;
 consistent
---------------------------------------------------------------------
 t
(1 row)

-- cleanup
DROP SCHEMA truncate_schema CASCADE;
NOTICE:  drop cascades to table truncate_schema.truncate_tbl
DROP USER truncate_user;