pathman_inserts \

pathman_runtime_nodes \

pathman_callbacks \

pathman_calamity \

/*

* Test CTE query

*/

EXPLAIN (COSTS OFF)

WITH ttt AS (SELECT * FROM test.range_rel WHERE dt >= '2015-02-01' AND dt < '2015-03-15')

SELECT * FROM ttt;

QUERY PLAN

--------------------------------------------------------------------------------------------

CTE Scan on ttt

CTE ttt

-> Append

-> Seq Scan on range_rel_2

-> Index Scan using range_rel_3_dt_idx on range_rel_3

Index Cond: (dt < 'Sun Mar 15 00:00:00 2015'::timestamp without time zone)

(6 rows)

EXPLAIN (COSTS OFF)

WITH ttt AS (SELECT * FROM test.hash_rel WHERE value = 2)

SELECT * FROM ttt;

QUERY PLAN

--------------------------------------

CTE Scan on ttt

CTE ttt

-> Append

-> Seq Scan on hash_rel_1

Filter: (value = 2)

(5 rows)

/*

* Test CTE query - by @parihaaraka (add varno to WalkerContext)

*/

CREATE TABLE test.cte_del_xacts (id BIGSERIAL PRIMARY KEY, pdate DATE NOT NULL);

INSERT INTO test.cte_del_xacts (pdate) SELECT gen_date FROM generate_series('2016-01-01'::date, '2016-04-9'::date, '1 day') AS gen_date;

create table test.cte_del_xacts_specdata

(

tid BIGINT PRIMARY KEY,

test_mode SMALLINT,

state_code SMALLINT NOT NULL DEFAULT 8,

regtime TIMESTAMP WITHOUT TIME ZONE NOT NULL

);

INSERT INTO test.cte_del_xacts_specdata VALUES(1, 1, 1, current_timestamp); /* for subquery test */

/* create 2 partitions */

SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '50 days'::interval);

NOTICE: sequence "cte_del_xacts_seq" does not exist, skipping

create_range_partitions

-------------------------

2

(1 row)

EXPLAIN (COSTS OFF)

WITH tmp AS (

SELECT tid, test_mode, regtime::DATE AS pdate, state_code

FROM test.cte_del_xacts_specdata)

DELETE FROM test.cte_del_xacts t USING tmp

WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;

QUERY PLAN

--------------------------------------------------------------------------------

Delete on cte_del_xacts t

Delete on cte_del_xacts t

Delete on cte_del_xacts_1 t_1

Delete on cte_del_xacts_2 t_2

CTE tmp

-> Seq Scan on cte_del_xacts_specdata

-> Hash Join

Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_pkey on cte_del_xacts t

-> Hash Join

Hash Cond: ((tmp.tid = t_1.id) AND (tmp.pdate = t_1.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t_1

-> Hash Join

Hash Cond: ((tmp.tid = t_2.id) AND (tmp.pdate = t_2.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_2_pkey on cte_del_xacts_2 t_2

(24 rows)

SELECT pathman.drop_partitions('test.cte_del_xacts'); /* now drop partitions */

NOTICE: function test.cte_del_xacts_upd_trig_func() does not exist, skipping

NOTICE: 50 rows copied from test.cte_del_xacts_1

NOTICE: 50 rows copied from test.cte_del_xacts_2

drop_partitions

-----------------

2

(1 row)

/* create 1 partition */

SELECT pathman.create_range_partitions('test.cte_del_xacts'::regclass, 'pdate', '2016-01-01'::date, '1 year'::interval);

create_range_partitions

-------------------------

1

(1 row)

/* parent enabled! */

SELECT pathman.set_enable_parent('test.cte_del_xacts', true);

set_enable_parent

-------------------

(1 row)

EXPLAIN (COSTS OFF)

WITH tmp AS (

SELECT tid, test_mode, regtime::DATE AS pdate, state_code

FROM test.cte_del_xacts_specdata)

DELETE FROM test.cte_del_xacts t USING tmp

WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;

QUERY PLAN

--------------------------------------------------------------------------------

Delete on cte_del_xacts t

Delete on cte_del_xacts t

Delete on cte_del_xacts_1 t_1

CTE tmp

-> Seq Scan on cte_del_xacts_specdata

-> Hash Join

Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_pkey on cte_del_xacts t

-> Hash Join

Hash Cond: ((tmp.tid = t_1.id) AND (tmp.pdate = t_1.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t_1

(17 rows)

/* parent disabled! */

SELECT pathman.set_enable_parent('test.cte_del_xacts', false);

set_enable_parent

-------------------

(1 row)

EXPLAIN (COSTS OFF)

WITH tmp AS (

SELECT tid, test_mode, regtime::DATE AS pdate, state_code

FROM test.cte_del_xacts_specdata)

DELETE FROM test.cte_del_xacts t USING tmp

WHERE t.id = tmp.tid AND t.pdate = tmp.pdate AND tmp.test_mode > 0;

QUERY PLAN

------------------------------------------------------------------------------

Delete on cte_del_xacts_1 t

CTE tmp

-> Seq Scan on cte_del_xacts_specdata

-> Hash Join

Hash Cond: ((tmp.tid = t.id) AND (tmp.pdate = t.pdate))

-> CTE Scan on tmp

Filter: (test_mode > 0)

-> Hash

-> Index Scan using cte_del_xacts_1_pkey on cte_del_xacts_1 t

(9 rows)

/* create stub pl/PgSQL function */

CREATE OR REPLACE FUNCTION test.cte_del_xacts_stab(name TEXT)

RETURNS smallint AS

$$

begin

return 2::smallint;

end

$$

LANGUAGE plpgsql STABLE;

/* test subquery planning */

WITH tmp AS (

SELECT tid FROM test.cte_del_xacts_specdata

WHERE state_code != test.cte_del_xacts_stab('test'))

SELECT * FROM test.cte_del_xacts t JOIN tmp ON t.id = tmp.tid;

id | pdate | tid

----+------------+-----

1 | 01-01-2016 | 1

(1 row)

/* test subquery planning (one more time) */

WITH tmp AS (

SELECT tid FROM test.cte_del_xacts_specdata

WHERE state_code != test.cte_del_xacts_stab('test'))

SELECT * FROM test.cte_del_xacts t JOIN tmp ON t.id = tmp.tid;

id | pdate | tid

----+------------+-----

1 | 01-01-2016 | 1

(1 row)

DROP FUNCTION test.cte_del_xacts_stab(TEXT);

DROP TABLE test.cte_del_xacts, test.cte_del_xacts_specdata CASCADE;

NOTICE: drop cascades to table test.cte_del_xacts_1

/* Test foreign keys */

CREATE TABLE test.messages(id SERIAL PRIMARY KEY, msg TEXT);

CREATE TABLE test.replies(id SERIAL PRIMARY KEY, message_id INTEGER REFERENCES test.messages(id), msg TEXT);

INSERT INTO test.messages SELECT g, md5(g::text) FROM generate_series(1, 10) as g;

INSERT INTO test.replies SELECT g, g, md5(g::text) FROM generate_series(1, 10) as g;

SELECT create_range_partitions('test.messages', 'id', 1, 100, 2);

WARNING: foreign key "replies_message_id_fkey" references relation "test.messages"

ERROR: relation "test.messages" is referenced from other relations

ALTER TABLE test.replies DROP CONSTRAINT replies_message_id_fkey;

SELECT create_range_partitions('test.messages', 'id', 1, 100, 2);

NOTICE: sequence "messages_seq" does not exist, skipping

create_range_partitions

-------------------------

2

(1 row)

EXPLAIN (COSTS OFF) SELECT * FROM test.messages;

QUERY PLAN

------------------------------

Append

-> Seq Scan on messages_1

-> Seq Scan on messages_2

(3 rows)

DROP TABLE test.messages, test.replies CASCADE;

NOTICE: drop cascades to 2 other objects

/* Test recursive CTE */

CREATE TABLE test.recursive_cte_test_tbl(id INT NOT NULL, name TEXT NOT NULL);

SELECT * FROM create_hash_partitions('test.recursive_cte_test_tbl', 'id', 2);

create_hash_partitions

------------------------

2

(1 row)

INSERT INTO test.recursive_cte_test_tbl (id, name) SELECT id, 'name'||id FROM generate_series(1,100) f(id);

INSERT INTO test.recursive_cte_test_tbl (id, name) SELECT id, 'name'||(id + 1) FROM generate_series(1,100) f(id);

INSERT INTO test.recursive_cte_test_tbl (id, name) SELECT id, 'name'||(id + 2) FROM generate_series(1,100) f(id);

SELECT * FROM test.recursive_cte_test_tbl WHERE id = 5;

id | name

----+-------

5 | name5

5 | name6

5 | name7

(3 rows)

WITH RECURSIVE test AS (

SELECT min(name) AS name

FROM test.recursive_cte_test_tbl

WHERE id = 5

UNION ALL

SELECT (SELECT min(name)

FROM test.recursive_cte_test_tbl

WHERE id = 5 AND name > test.name)

FROM test

WHERE name IS NOT NULL)

SELECT * FROM test;

name

-------

name5

name6

name7

(4 rows)

NOTICE: drop cascades to 54 other objects