--
-- UPDATE syntax tests
--
CREATE TABLE update_test (
a INT DEFAULT 10,
b INT,
c TEXT
);
CREATE TABLE upsert_test (
a INT PRIMARY KEY,
b TEXT
);
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
SELECT * FROM update_test;
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 148
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
^
SELECT * FROM update_test;
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alias is not supported
-- aliases for the UPDATE target table
^
SELECT * FROM update_test;
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alias is not supported
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
^
SELECT * FROM update_test;
--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
SELECT * FROM update_test;
-- fail, wrong data type:
UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
--
-- Test multiple-set-clause syntax
--
INSERT INTO update_test SELECT a,b+1,c FROM update_test;
SELECT * FROM update_test;
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
^
SELECT * FROM update_test;
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
^
SELECT * FROM update_test;
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- fail, multi assignment to same column:
^
-- uncorrelated sub-select:
UPDATE update_test
SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
WHERE a = 100 AND b = 20;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- uncorrelated sub-select:
^
SELECT * FROM update_test;
-- correlated sub-select:
UPDATE update_test o
SET (b,a) = (select a+1,b from update_test i
where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- correlated sub-select:
^
SELECT * FROM update_test;
-- fail, multiple rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- fail, multiple rows supplied:
^
-- set to null if no rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
WHERE a = 11;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- set to null if no rows supplied:
^
SELECT * FROM update_test;
-- *-expansion should work in this context:
UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j)
WHERE update_test.a = v.i;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- *-expansion should work in this context:
^
-- you might expect this to work, but syntactically it's not a RowExpr:
UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j)
WHERE update_test.a = v.i;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 363
-- you might expect this to work, but syntactically it's not a RowExpr:
^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alias is not supported
-- if an alias for the target table is specified, don't allow references
^
-- Make sure that we can update to a TOASTed value.
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
SELECT a, b, char_length(c) FROM update_test;
-- Check multi-assignment with a Result node to handle a one-time filter.
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE update_test t
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
WHERE CURRENT_USER = SESSION_USER;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- Check multi-assignment with a Result node to handle a one-time filter.
^
UPDATE update_test t
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
WHERE CURRENT_USER = SESSION_USER;
-stdin-:: Error: Parse Sql
-stdin-::3:24: Error: Usupported SQLValueFunction: 12
WHERE CURRENT_USER = SESSION_USER;
^
SELECT a, b, char_length(c) FROM update_test;
-- Test ON CONFLICT DO UPDATE
INSERT INTO upsert_test VALUES(1, 'Boo'), (3, 'Zoo');
-- uncorrelated sub-select:
WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
VALUES (1, 'Bar') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- uncorrelated sub-select:
^
-- correlated sub-select:
INSERT INTO upsert_test VALUES (1, 'Baz'), (3, 'Zaz') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a)
RETURNING *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- correlated sub-select:
^
-- correlated sub-select (EXCLUDED.* alias):
INSERT INTO upsert_test VALUES (1, 'Bat'), (3, 'Zot') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
RETURNING *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- correlated sub-select (EXCLUDED.* alias):
^
-- ON CONFLICT using system attributes in RETURNING, testing both the
-- inserting and updating paths. See bug report at:
-- https://www.postgresql.org/message-id/73436355-6432-49B1-92ED-1FE4F7E7E100%40finefun.com.au
INSERT INTO upsert_test VALUES (2, 'Beeble') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
RETURNING tableoid::regclass, xmin = pg_current_xact_id()::xid AS xmin_correct, xmax = 0 AS xmax_correct;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- ON CONFLICT using system attributes in RETURNING, testing both the
^
-- currently xmax is set after a conflict - that's probably not good,
-- but it seems worthwhile to have to be explicit if that changes.
INSERT INTO upsert_test VALUES (2, 'Brox') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
RETURNING tableoid::regclass, xmin = pg_current_xact_id()::xid AS xmin_correct, xmax = pg_current_xact_id()::xid AS xmax_correct;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- currently xmax is set after a conflict - that's probably not good,
^
DROP TABLE update_test;
DROP TABLE upsert_test;
-- Test ON CONFLICT DO UPDATE with partitioned table and non-identical children
CREATE TABLE upsert_test (
a INT PRIMARY KEY,
b TEXT
) PARTITION BY LIST (a);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
-- Test ON CONFLICT DO UPDATE with partitioned table and non-identical children
^
CREATE TABLE upsert_test_1 PARTITION OF upsert_test FOR VALUES IN (1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE upsert_test_1 PARTITION OF upsert_test FOR VALUES IN (1);
^
CREATE TABLE upsert_test_2 (b TEXT, a INT PRIMARY KEY);
ALTER TABLE upsert_test ATTACH PARTITION upsert_test_2 FOR VALUES IN (2);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE upsert_test ATTACH PARTITION upsert_test_2 FOR VALUES IN (2);
^
INSERT INTO upsert_test VALUES(1, 'Boo'), (2, 'Zoo');
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.upsert_test
-- uncorrelated sub-select:
WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
VALUES (1, 'Bar') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- uncorrelated sub-select:
^
-- correlated sub-select:
WITH aaa AS (SELECT 1 AS ctea, ' Foo' AS cteb) INSERT INTO upsert_test
VALUES (1, 'Bar'), (2, 'Baz') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT upsert_test.b||cteb, upsert_test.a FROM aaa) RETURNING *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
-- correlated sub-select:
^
DROP TABLE upsert_test;
---------------------------
-- UPDATE with row movement
---------------------------
-- When a partitioned table receives an UPDATE to the partitioned key and the
-- new values no longer meet the partition's bound, the row must be moved to
-- the correct partition for the new partition key (if one exists). We must
-- also ensure that updatable views on partitioned tables properly enforce any
-- WITH CHECK OPTION that is defined. The situation with triggers in this case
-- also requires thorough testing as partition key updates causing row
-- movement convert UPDATEs into DELETE+INSERT.
CREATE TABLE range_parted (
a text,
b bigint,
c numeric,
d int,
e varchar
) PARTITION BY RANGE (a, b);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
---------------------------
^
-- Create partitions intentionally in descending bound order, so as to test
-- that update-row-movement works with the leaf partitions not in bound order.
CREATE TABLE part_b_20_b_30 (e varchar, c numeric, a text, b bigint, d int);
ALTER TABLE range_parted ATTACH PARTITION part_b_20_b_30 FOR VALUES FROM ('b', 20) TO ('b', 30);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE range_parted ATTACH PARTITION part_b_20_b_30 FOR VALUES FROM ('b', 20) TO ('b', 30);
^
CREATE TABLE part_b_10_b_20 (e varchar, c numeric, a text, b bigint, d int) PARTITION BY RANGE (c);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
CREATE TABLE part_b_10_b_20 (e varchar, c numeric, a text, b bigint, d int) PARTITION BY RANGE (c);
^
CREATE TABLE part_b_1_b_10 PARTITION OF range_parted FOR VALUES FROM ('b', 1) TO ('b', 10);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE part_b_1_b_10 PARTITION OF range_parted FOR VALUES FROM ('b', 1) TO ('b', 10);
^
ALTER TABLE range_parted ATTACH PARTITION part_b_10_b_20 FOR VALUES FROM ('b', 10) TO ('b', 20);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE range_parted ATTACH PARTITION part_b_10_b_20 FOR VALUES FROM ('b', 10) TO ('b', 20);
^
CREATE TABLE part_a_10_a_20 PARTITION OF range_parted FOR VALUES FROM ('a', 10) TO ('a', 20);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE part_a_10_a_20 PARTITION OF range_parted FOR VALUES FROM ('a', 10) TO ('a', 20);
^
CREATE TABLE part_a_1_a_10 PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('a', 10);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE part_a_1_a_10 PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('a', 10);
^
-- Check that partition-key UPDATE works sanely on a partitioned table that
-- does not have any child partitions.
UPDATE part_b_10_b_20 set b = b - 6;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- Create some more partitions following the above pattern of descending bound
-- order, but let's make the situation a bit more complex by having the
-- attribute numbers of the columns vary from their parent partition.
CREATE TABLE part_c_100_200 (e varchar, c numeric, a text, b bigint, d int) PARTITION BY range (abs(d));
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
-- Create some more partitions following the above pattern of descending bound
^
ALTER TABLE part_c_100_200 DROP COLUMN e, DROP COLUMN c, DROP COLUMN a;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_c_100_200 DROP COLUMN e, DROP COLUMN c, DROP COLUMN a;
^
ALTER TABLE part_c_100_200 ADD COLUMN c numeric, ADD COLUMN e varchar, ADD COLUMN a text;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_c_100_200 ADD COLUMN c numeric, ADD COLUMN e varchar, ADD COLUMN a text;
^
ALTER TABLE part_c_100_200 DROP COLUMN b;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_c_100_200 DROP COLUMN b;
^
ALTER TABLE part_c_100_200 ADD COLUMN b bigint;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_c_100_200 ADD COLUMN b bigint;
^
CREATE TABLE part_d_1_15 PARTITION OF part_c_100_200 FOR VALUES FROM (1) TO (15);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE part_d_1_15 PARTITION OF part_c_100_200 FOR VALUES FROM (1) TO (15);
^
CREATE TABLE part_d_15_20 PARTITION OF part_c_100_200 FOR VALUES FROM (15) TO (20);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
CREATE TABLE part_d_15_20 PARTITION OF part_c_100_200 FOR VALUES FROM (15) TO (20);
^
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_100_200 FOR VALUES FROM (100) TO (200);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_100_200 FOR VALUES FROM (100) TO (200);
^
CREATE TABLE part_c_1_100 (e varchar, d int, c numeric, b bigint, a text);
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_1_100 FOR VALUES FROM (1) TO (100);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
ALTER TABLE part_b_10_b_20 ATTACH PARTITION part_c_1_100 FOR VALUES FROM (1) TO (100);
^
\set init_range_parted 'truncate range_parted; insert into range_parted VALUES (''a'', 1, 1, 1), (''a'', 10, 200, 1), (''b'', 12, 96, 1), (''b'', 13, 97, 2), (''b'', 15, 105, 16), (''b'', 17, 105, 19)'
Metacommand \set init_range_parted 'truncate range_parted; insert into range_parted VALUES (''a'', 1, 1, 1), (''a'', 10, 200, 1), (''b'', 12, 96, 1), (''b'', 13, 97, 2), (''b'', 15, 105, 16), (''b'', 17, 105, 19)' is not supported
\set show_data 'select tableoid::regclass::text COLLATE "C" partname, * from range_parted ORDER BY 1, 2, 3, 4, 5, 6'
Metacommand \set show_data 'select tableoid::regclass::text COLLATE "C" partname, * from range_parted ORDER BY 1, 2, 3, 4, 5, 6' is not supported
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- The order of subplans should be in bound order
EXPLAIN (costs off) UPDATE range_parted set c = c - 50 WHERE c > 97;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- The order of subplans should be in bound order
^
-- fail, row movement happens only within the partition subtree.
UPDATE part_c_100_200 set c = c - 20, d = c WHERE c = 105;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- fail, no partition key update, so no attempt to move tuple,
-- but "a = 'a'" violates partition constraint enforced by root partition)
UPDATE part_b_10_b_20 set a = 'a';
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok, partition key update, no constraint violation
UPDATE range_parted set d = d - 10 WHERE d > 10;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok, no partition key update, no constraint violation
UPDATE range_parted set e = d;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- No row found
UPDATE part_c_1_100 set c = c + 20 WHERE c = 98;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok, row movement
UPDATE part_b_10_b_20 set c = c + 20 returning c, b, a;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- fail, row movement happens only within the partition subtree.
UPDATE part_b_10_b_20 set b = b - 6 WHERE c > 116 returning *;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok, row movement, with subset of rows moved into different partition.
UPDATE range_parted set b = b - 6 WHERE c > 116 returning a, b + c;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: ResTarget: alternative is not implemented yet : 353
-- ok, row movement, with subset of rows moved into different partition.
^
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- Common table needed for multiple test scenarios.
CREATE TABLE mintab(c1 int);
INSERT into mintab VALUES (120);
-- update partition key using updatable view.
CREATE VIEW upview AS SELECT * FROM range_parted WHERE (select c > c1 FROM mintab) WITH CHECK OPTION;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
-- update partition key using updatable view.
^
-- ok
UPDATE upview set c = 199 WHERE b = 4;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- fail, check option violation
UPDATE upview set c = 120 WHERE b = 4;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- fail, row movement with check option violation
UPDATE upview set a = 'b', b = 15, c = 120 WHERE b = 4;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok, row movement, check option passes
UPDATE upview set a = 'b', b = 15 WHERE b = 4;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- cleanup
DROP VIEW upview;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: View not found: 'upview'
-- cleanup
^
-- RETURNING having whole-row vars.
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::2:1: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
UPDATE range_parted set c = 95 WHERE a = 'b' and b > 10 and c > 100 returning (range_parted), *;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- Transition tables with update row movement
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::2:1: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
CREATE FUNCTION trans_updatetrigfunc() RETURNS trigger LANGUAGE plpgsql AS
$$
begin
raise notice 'trigger = %, old table = %, new table = %',
TG_NAME,
(select string_agg(old_table::text, ', ' ORDER BY a) FROM old_table),
(select string_agg(new_table::text, ', ' ORDER BY a) FROM new_table);
return null;
end;
$$;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
CREATE FUNCTION trans_updatetrigfunc() RETURNS trigger LANGUAGE plpgsql AS
^
CREATE TRIGGER trans_updatetrig
AFTER UPDATE ON range_parted REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trans_updatetrig
^
UPDATE range_parted set c = (case when c = 96 then 110 else c + 1 end ) WHERE a = 'b' and b > 10 and c >= 96;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
-- Enabling OLD TABLE capture for both DELETE as well as UPDATE stmt triggers
-- should not cause DELETEd rows to be captured twice. Similar thing for
-- INSERT triggers and inserted rows.
CREATE TRIGGER trans_deletetrig
AFTER DELETE ON range_parted REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
-- Enabling OLD TABLE capture for both DELETE as well as UPDATE stmt triggers
^
CREATE TRIGGER trans_inserttrig
AFTER INSERT ON range_parted REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE trans_updatetrigfunc();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trans_inserttrig
^
UPDATE range_parted set c = c + 50 WHERE a = 'b' and b > 10 and c >= 96;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
DROP TRIGGER trans_deletetrig ON range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trans_deletetrig ON range_parted;
^
DROP TRIGGER trans_inserttrig ON range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trans_inserttrig ON range_parted;
^
-- Don't drop trans_updatetrig yet. It is required below.
-- Test with transition tuple conversion happening for rows moved into the
-- new partition. This requires a trigger that references transition table
-- (we already have trans_updatetrig). For inserted rows, the conversion
-- is not usually needed, because the original tuple is already compatible with
-- the desired transition tuple format. But conversion happens when there is a
-- BR trigger because the trigger can change the inserted row. So install a
-- BR triggers on those child partitions where the rows will be moved.
CREATE FUNCTION func_parted_mod_b() RETURNS trigger AS $$
BEGIN
NEW.b = NEW.b + 1;
return NEW;
END $$ language plpgsql;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
-- Don't drop trans_updatetrig yet. It is required below.
^
CREATE TRIGGER trig_c1_100 BEFORE UPDATE OR INSERT ON part_c_1_100
FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trig_c1_100 BEFORE UPDATE OR INSERT ON part_c_1_100
^
CREATE TRIGGER trig_d1_15 BEFORE UPDATE OR INSERT ON part_d_1_15
FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trig_d1_15 BEFORE UPDATE OR INSERT ON part_d_1_15
^
CREATE TRIGGER trig_d15_20 BEFORE UPDATE OR INSERT ON part_d_15_20
FOR EACH ROW EXECUTE PROCEDURE func_parted_mod_b();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trig_d15_20 BEFORE UPDATE OR INSERT ON part_d_15_20
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
UPDATE range_parted set c = (case when c = 96 then 110 else c + 1 end) WHERE a = 'b' and b > 10 and c >= 96;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
UPDATE range_parted set c = c + 50 WHERE a = 'b' and b > 10 and c >= 96;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
-- Case where per-partition tuple conversion map array is allocated, but the
-- map is not required for the particular tuple that is routed, thanks to
-- matching table attributes of the partition and the target table.
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::4:1: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
UPDATE range_parted set b = 15 WHERE b = 1;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
:show_data;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:show_data;
^
DROP TRIGGER trans_updatetrig ON range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trans_updatetrig ON range_parted;
^
DROP TRIGGER trig_c1_100 ON part_c_1_100;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trig_c1_100 ON part_c_1_100;
^
DROP TRIGGER trig_d1_15 ON part_d_1_15;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trig_d1_15 ON part_d_1_15;
^
DROP TRIGGER trig_d15_20 ON part_d_15_20;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trig_d15_20 ON part_d_15_20;
^
DROP FUNCTION func_parted_mod_b();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: DropStmt: alternative is not implemented yet : 386
DROP FUNCTION func_parted_mod_b();
^
-- RLS policies with update-row-movement
-----------------------------------------
ALTER TABLE range_parted ENABLE ROW LEVEL SECURITY;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 245
-- RLS policies with update-row-movement
^
CREATE USER regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 285
CREATE USER regress_range_parted_user;
^
GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 248
GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
^
CREATE POLICY seeall ON range_parted AS PERMISSIVE FOR SELECT USING (true);
-stdin-:: Error: Parse Sql
-stdin-::1:42: Error: ERROR: unrecognized row security option "PERMISSIVE"
CREATE POLICY seeall ON range_parted AS PERMISSIVE FOR SELECT USING (true);
^
CREATE POLICY policy_range_parted ON range_parted for UPDATE USING (true) WITH CHECK (c % 2 = 0);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 340
CREATE POLICY policy_range_parted ON range_parted for UPDATE USING (true) WITH CHECK (c % 2 = 0);
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
SET SESSION AUTHORIZATION regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported name: session_authorization
SET SESSION AUTHORIZATION regress_range_parted_user;
^
-- This should fail with RLS violation error while moving row from
-- part_a_10_a_20 to part_d_1_15, because we are setting 'c' to an odd number.
UPDATE range_parted set a = 'b', c = 151 WHERE a = 'a' and c = 200;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
RESET SESSION AUTHORIZATION;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported kind: 4
RESET SESSION AUTHORIZATION;
^
-- Create a trigger on part_d_1_15
CREATE FUNCTION func_d_1_15() RETURNS trigger AS $$
BEGIN
NEW.c = NEW.c + 1; -- Make even numbers odd, or vice versa
return NEW;
END $$ LANGUAGE plpgsql;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
-- Create a trigger on part_d_1_15
^
CREATE TRIGGER trig_d_1_15 BEFORE INSERT ON part_d_1_15
FOR EACH ROW EXECUTE PROCEDURE func_d_1_15();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 283
CREATE TRIGGER trig_d_1_15 BEFORE INSERT ON part_d_1_15
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
SET SESSION AUTHORIZATION regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported name: session_authorization
SET SESSION AUTHORIZATION regress_range_parted_user;
^
-- Here, RLS checks should succeed while moving row from part_a_10_a_20 to
-- part_d_1_15. Even though the UPDATE is setting 'c' to an odd number, the
-- trigger at the destination partition again makes it an even number.
UPDATE range_parted set a = 'b', c = 151 WHERE a = 'a' and c = 200;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
RESET SESSION AUTHORIZATION;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported kind: 4
RESET SESSION AUTHORIZATION;
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
SET SESSION AUTHORIZATION regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported name: session_authorization
SET SESSION AUTHORIZATION regress_range_parted_user;
^
-- This should fail with RLS violation error. Even though the UPDATE is setting
-- 'c' to an even number, the trigger at the destination partition again makes
-- it an odd number.
UPDATE range_parted set a = 'b', c = 150 WHERE a = 'a' and c = 200;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- Cleanup
RESET SESSION AUTHORIZATION;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported kind: 4
-- Cleanup
^
DROP TRIGGER trig_d_1_15 ON part_d_1_15;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: Not supported object type for DROP
DROP TRIGGER trig_d_1_15 ON part_d_1_15;
^
DROP FUNCTION func_d_1_15();
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: DropStmt: alternative is not implemented yet : 386
DROP FUNCTION func_d_1_15();
^
-- Policy expression contains SubPlan
RESET SESSION AUTHORIZATION;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported kind: 4
-- Policy expression contains SubPlan
^
:init_range_parted;
-stdin-:: Error: Parse Sql
-stdin-::1:2: Error: ERROR: syntax error at or near ":"
:init_range_parted;
^
CREATE POLICY policy_range_parted_subplan on range_parted
AS RESTRICTIVE for UPDATE USING (true)
WITH CHECK ((SELECT range_parted.c <= c1 FROM mintab));
-stdin-:: Error: Parse Sql
-stdin-::2:8: Error: ERROR: unrecognized row security option "RESTRICTIVE"
AS RESTRICTIVE for UPDATE USING (true)
^
SET SESSION AUTHORIZATION regress_range_parted_user;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: VariableSetStmt, not supported name: session_authorization
SET SESSION AUTHORIZATION regress_range_parted_user;
^
-- fail, mintab has row with c1 = 120
UPDATE range_parted set a = 'b', c = 122 WHERE a = 'a' and c = 200;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- ok
UPDATE range_parted set a = 'b', c = 120 WHERE a = 'a' and c = 200;
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_update' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
-- RLS policy expression contains whole row.
RESET SESSION AUTHORIZATION;
-stdin-:: Error: Parse Sql
-stdin-: