pg-test-files/expected/insert.sql

--
-- insert with DEFAULT in the target_list
--
CREATE TABLE inserttest (
    col1 int4,
    col2 int4 NOT NULL,
    col3 text DEFAULT 'testing'
);

INSERT INTO inserttest (col1, col2, col3)
    VALUES (DEFAULT, DEFAULT, DEFAULT);

INSERT INTO inserttest (col2, col3)
    VALUES (3, DEFAULT);

INSERT INTO inserttest (col1, col2, col3)
    VALUES (DEFAULT, 5, DEFAULT);

INSERT INTO inserttest
    VALUES (DEFAULT, 5, 'test');

INSERT INTO inserttest
    VALUES (DEFAULT, 7);

SELECT
    *
FROM
    inserttest;

--
-- insert with similar expression / target_list values (all fail)
--
INSERT INTO inserttest (col1, col2, col3)
    VALUES (DEFAULT, DEFAULT);

INSERT INTO inserttest (col1, col2, col3)
    VALUES (1, 2);

INSERT INTO inserttest (col1)
    VALUES (1, 2);

INSERT INTO inserttest (col1)
    VALUES (DEFAULT, DEFAULT);

SELECT
    *
FROM
    inserttest;

--
-- VALUES test
--
INSERT INTO inserttest
    VALUES (10, 20, '40'), (-1, 2, DEFAULT), ((
        SELECT
            2), (
        SELECT
            i
        FROM (
            VALUES (3)) AS foo (i)), 'values are fun!');

SELECT
    *
FROM
    inserttest;

--
-- TOASTed value test
--
INSERT INTO inserttest
    VALUES (30, 50, repeat('x', 10000));

SELECT
    col1,
    col2,
    char_length(col3)
FROM
    inserttest;

DROP TABLE inserttest;

--
-- check indirection (field/array assignment), cf bug #14265
--
-- these tests are aware that transformInsertStmt has 3 separate code paths
--
CREATE TYPE insert_test_type AS (
    if1 int,
    if2 text[]
);

CREATE TABLE inserttest (
    f1 int,
    f2 int[],
    f3 insert_test_type,
    f4 insert_test_type[]
);

INSERT INTO inserttest (f2[1], f2[2])
    VALUES (1, 2);

INSERT INTO inserttest (f2[1], f2[2])
    VALUES (3, 4), (5, 6);

INSERT INTO inserttest (f2[1], f2[2])
SELECT
    7,
    8;

INSERT INTO inserttest (f2[1], f2[2])
    VALUES (1, DEFAULT);

-- not supported
INSERT INTO inserttest (f3.if1, f3.if2)
    VALUES (1, ARRAY['foo']);

INSERT INTO inserttest (f3.if1, f3.if2)
    VALUES (1, '{foo}'), (2, '{bar}');

INSERT INTO inserttest (f3.if1, f3.if2)
SELECT
    3,
    '{baz,quux}';

INSERT INTO inserttest (f3.if1, f3.if2)
    VALUES (1, DEFAULT);

-- not supported
INSERT INTO inserttest (f3.if2[1], f3.if2[2])
    VALUES ('foo', 'bar');

INSERT INTO inserttest (f3.if2[1], f3.if2[2])
    VALUES ('foo', 'bar'), ('baz', 'quux');

INSERT INTO inserttest (f3.if2[1], f3.if2[2])
SELECT
    'bear',
    'beer';

INSERT INTO inserttest (f4[1].if2[1], f4[1].if2[2])
    VALUES ('foo', 'bar');

INSERT INTO inserttest (f4[1].if2[1], f4[1].if2[2])
    VALUES ('foo', 'bar'), ('baz', 'quux');

INSERT INTO inserttest (f4[1].if2[1], f4[1].if2[2])
SELECT
    'bear',
    'beer';

SELECT
    *
FROM
    inserttest;

-- also check reverse-listing
CREATE TABLE inserttest2 (
    f1 bigint,
    f2 text
);

CREATE RULE irule1 AS ON INSERT TO inserttest2
    DO ALSO
    INSERT INTO inserttest (f3.if2[1], f3.if2[2])
    VALUES (NEW.f1, NEW.f2);

CREATE RULE irule2 AS ON INSERT TO inserttest2
    DO ALSO
    INSERT INTO inserttest (f4[1].if1, f4[1].if2[2])
    VALUES (1, 'fool'), (NEW.f1, NEW.f2);

CREATE RULE irule3 AS ON INSERT TO inserttest2
    DO ALSO
    INSERT INTO inserttest (f4[1].if1, f4[1].if2[2])
    SELECT
        NEW.f1,
        NEW.f2;

\d+ inserttest2
DROP TABLE inserttest2;

DROP TABLE inserttest;

DROP TYPE insert_test_type;

-- direct partition inserts should check partition bound constraint
CREATE TABLE range_parted (
    a text,
    b int
)
PARTITION BY RANGE (a, (b + 0));

-- no partitions, so fail
INSERT INTO range_parted
    VALUES ('a', 11);

CREATE TABLE part1 PARTITION OF range_parted
FOR VALUES FROM ('a', 1) TO ('a', 10);

CREATE TABLE part2 PARTITION OF range_parted
FOR VALUES FROM ('a', 10) TO ('a', 20);

CREATE TABLE part3 PARTITION OF range_parted
FOR VALUES FROM ('b', 1) TO ('b', 10);

CREATE TABLE part4 PARTITION OF range_parted
FOR VALUES FROM ('b', 10) TO ('b', 20);

-- fail
INSERT INTO part1
    VALUES ('a', 11);

INSERT INTO part1
    VALUES ('b', 1);

-- ok
INSERT INTO part1
    VALUES ('a', 1);

-- fail
INSERT INTO part4
    VALUES ('b', 21);

INSERT INTO part4
    VALUES ('a', 10);

-- ok
INSERT INTO part4
    VALUES ('b', 10);

-- fail (partition key a has a NOT NULL constraint)
INSERT INTO part1
    VALUES (NULL);

-- fail (expression key (b+0) cannot be null either)
INSERT INTO part1
    VALUES (1);

CREATE TABLE list_parted (
    a text,
    b int
)
PARTITION BY LIST (lower(a));

CREATE TABLE part_aa_bb PARTITION OF list_parted
FOR VALUES IN ('aa', 'bb');

CREATE TABLE part_cc_dd PARTITION OF list_parted
FOR VALUES IN ('cc', 'dd');

CREATE TABLE part_null PARTITION OF list_parted
FOR VALUES IN (NULL);

-- fail
INSERT INTO part_aa_bb
    VALUES ('cc', 1);

INSERT INTO part_aa_bb
    VALUES ('AAa', 1);

INSERT INTO part_aa_bb
    VALUES (NULL);

-- ok
INSERT INTO part_cc_dd
    VALUES ('cC', 1);

INSERT INTO part_null
    VALUES (NULL, 0);

-- check in case of multi-level partitioned table
CREATE TABLE part_ee_ff PARTITION OF list_parted
FOR VALUES IN ('ee', 'ff')
PARTITION BY RANGE (b);

CREATE TABLE part_ee_ff1 PARTITION OF part_ee_ff
FOR VALUES FROM (1) TO (10);

CREATE TABLE part_ee_ff2 PARTITION OF part_ee_ff
FOR VALUES FROM (10) TO (20);

-- test default partition
CREATE TABLE part_default PARTITION OF list_parted DEFAULT;

-- Negative test: a row, which would fit in other partition, does not fit
-- default partition, even when inserted directly
INSERT INTO part_default
    VALUES ('aa', 2);

INSERT INTO part_default
    VALUES (NULL, 2);

-- ok
INSERT INTO part_default
    VALUES ('Zz', 2);

-- test if default partition works as expected for multi-level partitioned
-- table as well as when default partition itself is further partitioned
DROP TABLE part_default;

CREATE TABLE part_xx_yy PARTITION OF list_parted
FOR VALUES IN ('xx', 'yy')
PARTITION BY LIST (a);

CREATE TABLE part_xx_yy_p1 PARTITION OF part_xx_yy
FOR VALUES IN ('xx');

CREATE TABLE part_xx_yy_defpart PARTITION OF part_xx_yy DEFAULT;

CREATE TABLE part_default PARTITION OF list_parted DEFAULT PARTITION BY RANGE (b);

CREATE TABLE part_default_p1 PARTITION OF part_default
FOR VALUES FROM (20) TO (30);

CREATE TABLE part_default_p2 PARTITION OF part_default
FOR VALUES FROM (30) TO (40);

-- fail
INSERT INTO part_ee_ff1
    VALUES ('EE', 11);

INSERT INTO part_default_p2
    VALUES ('gg', 43);

-- fail (even the parent's, ie, part_ee_ff's partition constraint applies)
INSERT INTO part_ee_ff1
    VALUES ('cc', 1);

INSERT INTO part_default
    VALUES ('gg', 43);

-- ok
INSERT INTO part_ee_ff1
    VALUES ('ff', 1);

INSERT INTO part_ee_ff2
    VALUES ('ff', 11);

INSERT INTO part_default_p1
    VALUES ('cd', 25);

INSERT INTO part_default_p2
    VALUES ('de', 35);

INSERT INTO list_parted
    VALUES ('ab', 21);

INSERT INTO list_parted
    VALUES ('xx', 1);

INSERT INTO list_parted
    VALUES ('yy', 2);

SELECT
    tableoid::regclass,
    *
FROM
    list_parted;

-- Check tuple routing for partitioned tables
-- fail
INSERT INTO range_parted
    VALUES ('a', 0);

-- ok
INSERT INTO range_parted
    VALUES ('a', 1);

INSERT INTO range_parted
    VALUES ('a', 10);

-- fail
INSERT INTO range_parted
    VALUES ('a', 20);

-- ok
INSERT INTO range_parted
    VALUES ('b', 1);

INSERT INTO range_parted
    VALUES ('b', 10);

-- fail (partition key (b+0) is null)
INSERT INTO range_parted
    VALUES ('a');

-- Check default partition
CREATE TABLE part_def PARTITION OF range_parted DEFAULT;

-- fail
INSERT INTO part_def
    VALUES ('b', 10);

-- ok
INSERT INTO part_def
    VALUES ('c', 10);

INSERT INTO range_parted
    VALUES (NULL, NULL);

INSERT INTO range_parted
    VALUES ('a', NULL);

INSERT INTO range_parted
    VALUES (NULL, 19);

INSERT INTO range_parted
    VALUES ('b', 20);

SELECT
    tableoid::regclass,
    *
FROM
    range_parted;

-- ok
INSERT INTO list_parted
    VALUES (NULL, 1);

INSERT INTO list_parted (a)
    VALUES ('aA');

-- fail (partition of part_ee_ff not found in both cases)
INSERT INTO list_parted
    VALUES ('EE', 0);

INSERT INTO part_ee_ff
    VALUES ('EE', 0);

-- ok
INSERT INTO list_parted
    VALUES ('EE', 1);

INSERT INTO part_ee_ff
    VALUES ('EE', 10);

SELECT
    tableoid::regclass,
    *
FROM
    list_parted;

-- some more tests to exercise tuple-routing with multi-level partitioning
CREATE TABLE part_gg PARTITION OF list_parted
FOR VALUES IN ('gg')
PARTITION BY RANGE (b);

CREATE TABLE part_gg1 PARTITION OF part_gg
FOR VALUES FROM (MINVALUE) TO (1);

CREATE TABLE part_gg2 PARTITION OF part_gg
FOR VALUES FROM (1) TO (10)
PARTITION BY RANGE (b);

CREATE TABLE part_gg2_1 PARTITION OF part_gg2
FOR VALUES FROM (1) TO (5);

CREATE TABLE part_gg2_2 PARTITION OF part_gg2
FOR VALUES FROM (5) TO (10);

CREATE TABLE part_ee_ff3 PARTITION OF part_ee_ff
FOR VALUES FROM (20) TO (30)
PARTITION BY RANGE (b);

CREATE TABLE part_ee_ff3_1 PARTITION OF part_ee_ff3
FOR VALUES FROM (20) TO (25);

CREATE TABLE part_ee_ff3_2 PARTITION OF part_ee_ff3
FOR VALUES FROM (25) TO (30);

TRUNCATE list_parted;

INSERT INTO list_parted
    VALUES ('aa'), ('cc');

INSERT INTO list_parted
SELECT
    'Ff',
    s.a
FROM
    generate_series(1, 29) s (a);

INSERT INTO list_parted
SELECT
    'gg',
    s.a
FROM
    generate_series(1, 9) s (a);

INSERT INTO list_parted (b)
    VALUES (1);

SELECT
    tableoid::regclass::text,
    a,
    min(b) AS min_b,
    max(b) AS max_b
FROM
    list_parted
GROUP BY
    1,
    2
ORDER BY
    1;

-- direct partition inserts should check hash partition bound constraint
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different matchines.  The hash function for int4 simply returns
-- the sum of the values passed to it and the one for text returns the length
-- of the non-empty string value passed to it or 0.
CREATE OR REPLACE FUNCTION part_hashint4_noop (value int4, seed int8)
    RETURNS int8
    AS $$
    SELECT
        value + seed;

$$
LANGUAGE sql
IMMUTABLE;

CREATE OPERATOR class part_test_int4_ops FOR TYPE int4
    USING HASH AS
    OPERATOR 1 =,
    FUNCTION 2 part_hashint4_noop (int4, int8
);

CREATE OR REPLACE FUNCTION part_hashtext_length (value text, seed int8)
    RETURNS int8
    AS $$
    SELECT
        length(coalesce(value, ''))::int8
$$
LANGUAGE sql
IMMUTABLE;

CREATE OPERATOR class part_test_text_ops FOR TYPE text
    USING HASH AS
    OPERATOR 1 =,
    FUNCTION 2 part_hashtext_length (text, int8
);

CREATE TABLE hash_parted (
    a int
)
PARTITION BY HASH (a part_test_int4_ops);

CREATE TABLE hpart0 PARTITION OF hash_parted
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE hpart1 PARTITION OF hash_parted
FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE hpart2 PARTITION OF hash_parted
FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE hpart3 PARTITION OF hash_parted
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO hash_parted
    VALUES (generate_series(1, 10));

-- direct insert of values divisible by 4 - ok;
INSERT INTO hpart0
    VALUES (12), (16);

-- fail;
INSERT INTO hpart0
    VALUES (11);

-- 11 % 4 -> 3 remainder i.e. valid data for hpart3 partition
INSERT INTO hpart3
    VALUES (11);

-- view data
SELECT
    tableoid::regclass AS part,
    a,
    a % 4 AS "remainder = a % 4"
FROM
    hash_parted
ORDER BY
    part;

-- test \d+ output on a table which has both partitioned and unpartitioned
-- partitions
\d+ list_parted
-- cleanup
DROP TABLE range_parted, list_parted;

DROP TABLE hash_parted;

-- test that a default partition added as the first partition accepts any value
-- including null
CREATE TABLE list_parted (
    a int
)
PARTITION BY LIST (a);

CREATE TABLE part_default PARTITION OF list_parted DEFAULT;

\d+ part_default
INSERT INTO part_default
    VALUES (NULL);

INSERT INTO part_default
    VALUES (1);

INSERT INTO part_default
    VALUES (-1);

SELECT
    tableoid::regclass,
    a
FROM
    list_parted;

-- cleanup
DROP TABLE list_parted;

-- more tests for certain multi-level partitioning scenarios
CREATE TABLE mlparted (
    a int,
    b int
)
PARTITION BY RANGE (a, b);

CREATE TABLE mlparted1 (
    b int NOT NULL,
    a int NOT NULL
)
PARTITION BY RANGE ((b + 0));

CREATE TABLE mlparted11 (
    LIKE mlparted1
);

ALTER TABLE mlparted11
    DROP a;

ALTER TABLE mlparted11
    ADD a int;

ALTER TABLE mlparted11
    DROP a;

ALTER TABLE mlparted11
    ADD a int NOT NULL;

-- attnum for key attribute 'a' is different in mlparted, mlparted1, and mlparted11
SELECT
    attrelid::regclass,
    attname,
    attnum
FROM
    pg_attribute
WHERE
    attname = 'a'
    AND (attrelid = 'mlparted'::regclass
        OR attrelid = 'mlparted1'::regclass
        OR attrelid = 'mlparted11'::regclass)
ORDER BY
    attrelid::regclass::text;

ALTER TABLE mlparted1 ATTACH PARTITION mlparted11
FOR VALUES FROM (2) TO (5);

ALTER TABLE mlparted ATTACH PARTITION mlparted1
FOR VALUES FROM (1, 2) TO (1, 10);

-- check that "(1, 2)" is correctly routed to mlparted11.
INSERT INTO mlparted
    VALUES (1, 2);

SELECT
    tableoid::regclass,
    *
FROM
    mlparted;

-- check that proper message is shown after failure to route through mlparted1
INSERT INTO mlparted (a, b)
    VALUES (1, 5);

TRUNCATE mlparted;

ALTER TABLE mlparted
    ADD CONSTRAINT check_b CHECK (b = 3);

-- have a BR trigger modify the row such that the check_b is violated
CREATE FUNCTION mlparted11_trig_fn ()
    RETURNS TRIGGER
    AS $$
BEGIN
    NEW.b := 4;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER mlparted11_trig
    BEFORE INSERT ON mlparted11 FOR EACH ROW
    EXECUTE PROCEDURE mlparted11_trig_fn ();

-- check that the correct row is shown when constraint check_b fails after
-- "(1, 2)" is routed to mlparted11 (actually "(1, 4)" would be shown due
-- to the BR trigger mlparted11_trig_fn)
INSERT INTO mlparted
    VALUES (1, 2);

DROP TRIGGER mlparted11_trig ON mlparted11;

DROP FUNCTION mlparted11_trig_fn ();

-- check that inserting into an internal partition successfully results in
-- checking its partition constraint before inserting into the leaf partition
-- selected by tuple-routing
INSERT INTO mlparted1 (a, b)
    VALUES (2, 3);

-- check routing error through a list partitioned table when the key is null
CREATE TABLE lparted_nonullpart (
    a int,
    b char
)
PARTITION BY LIST (b);

CREATE TABLE lparted_nonullpart_a PARTITION OF lparted_nonullpart
FOR VALUES IN ('a');

INSERT INTO lparted_nonullpart
    VALUES (1);

DROP TABLE lparted_nonullpart;

-- check that RETURNING works correctly with tuple-routing
ALTER TABLE mlparted
    DROP CONSTRAINT check_b;

CREATE TABLE mlparted12 PARTITION OF mlparted1
FOR VALUES FROM (5) TO (10);

CREATE TABLE mlparted2 (
    b int NOT NULL,
    a int NOT NULL
);

ALTER TABLE mlparted ATTACH PARTITION mlparted2
FOR VALUES FROM (1, 10) TO (1, 20);

CREATE TABLE mlparted3 PARTITION OF mlparted
FOR VALUES FROM (1, 20) TO (1, 30);

CREATE TABLE mlparted4 (
    LIKE mlparted
);

ALTER TABLE mlparted4
    DROP a;

ALTER TABLE mlparted4
    ADD a int NOT NULL;

ALTER TABLE mlparted ATTACH PARTITION mlparted4
FOR VALUES FROM (1, 30) TO (1, 40);

WITH ins (
    a,
    b,
    c
) AS (
INSERT INTO mlparted (b, a)
    SELECT
        s.a,
        1
    FROM
        generate_series(2, 39) s (a)
    RETURNING
        tableoid::regclass,
        *
)
SELECT
    a,
    b,
    min(c),
    max(c)
FROM
    ins
GROUP BY
    a,
    b
ORDER BY
    1;

ALTER TABLE mlparted
    ADD c text;

CREATE TABLE mlparted5 (
    c text,
    a int NOT NULL,
    b int NOT NULL
)
PARTITION BY LIST (c);

CREATE TABLE mlparted5a (
    a int NOT NULL,
    c text,
    b int NOT NULL
);

ALTER TABLE mlparted5 ATTACH PARTITION mlparted5a
FOR VALUES IN ('a');

ALTER TABLE mlparted ATTACH PARTITION mlparted5
FOR VALUES FROM (1, 40) TO (1, 50);

ALTER TABLE mlparted
    ADD CONSTRAINT check_b CHECK (a = 1 AND b < 45);

INSERT INTO mlparted
    VALUES (1, 45, 'a');

CREATE FUNCTION mlparted5abrtrig_func ()
    RETURNS TRIGGER
    AS $$
BEGIN
    NEW.c = 'b';
    RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER mlparted5abrtrig
    BEFORE INSERT ON mlparted5a FOR EACH ROW
    EXECUTE PROCEDURE mlparted5abrtrig_func ();

INSERT INTO mlparted5 (a, b, c)
    VALUES (1, 40, 'a');

DROP TABLE mlparted5;

ALTER TABLE mlparted
    DROP CONSTRAINT check_b;

-- Check multi-level default partition
CREATE TABLE mlparted_def PARTITION OF mlparted DEFAULT PARTITION BY RANGE (a);

CREATE TABLE mlparted_def1 PARTITION OF mlparted_def
FOR VALUES FROM (40) TO (50);

CREATE TABLE mlparted_def2 PARTITION OF mlparted_def
FOR VALUES FROM (50) TO (60);

INSERT INTO mlparted
    VALUES (40, 100);

INSERT INTO mlparted_def1
    VALUES (42, 100);

INSERT INTO mlparted_def2
    VALUES (54, 50);

-- fail
INSERT INTO mlparted
    VALUES (70, 100);

INSERT INTO mlparted_def1
    VALUES (52, 50);

INSERT INTO mlparted_def2
    VALUES (34, 50);

-- ok
CREATE TABLE mlparted_defd PARTITION OF mlparted_def DEFAULT;

INSERT INTO mlparted
    VALUES (70, 100);

SELECT
    tableoid::regclass,
    *
FROM
    mlparted_def;

-- Check multi-level tuple routing with attributes dropped from the
-- top-most parent.  First remove the last attribute.
ALTER TABLE mlparted
    ADD d int,
    ADD e int;

ALTER TABLE mlparted
    DROP e;

CREATE TABLE mlparted5 PARTITION OF mlparted
FOR VALUES FROM (1, 40) TO (1, 50)
PARTITION BY RANGE (c);

CREATE TABLE mlparted5_ab PARTITION OF mlparted5
FOR VALUES FROM ('a') TO ('c')
PARTITION BY LIST (c);

CREATE TABLE mlparted5_a PARTITION OF mlparted5_ab
FOR VALUES IN ('a');

CREATE TABLE mlparted5_b (
    d int,
    b int,
    c text,
    a int
);

ALTER TABLE mlparted5_ab ATTACH PARTITION mlparted5_b
FOR VALUES IN ('b');

TRUNCATE mlparted;

INSERT INTO mlparted
    VALUES (1, 2, 'a', 1);

INSERT INTO mlparted
    VALUES (1, 40, 'a', 1);

-- goes to mlparted5_a
INSERT INTO mlparted
    VALUES (1, 45, 'b', 1);

-- goes to mlparted5_b
SELECT
    tableoid::regclass,
    *
FROM
    mlparted
ORDER BY
    a,
    b,
    c,
    d;

ALTER TABLE mlparted
    DROP d;

TRUNCATE mlparted;

-- Remove the before last attribute.
ALTER TABLE mlparted
    ADD e int,
    ADD d int;

ALTER TABLE mlparted
    DROP e;

INSERT INTO mlparted
    VALUES (1, 2, 'a', 1);

INSERT INTO mlparted
    VALUES (1, 40, 'a', 1);

-- goes to mlparted5_a
INSERT INTO mlparted
    VALUES (1, 45, 'b', 1);

-- goes to mlparted5_b
SELECT
    tableoid::regclass,
    *
FROM
    mlparted
ORDER BY
    a,
    b,
    c,
    d;

ALTER TABLE mlparted
    DROP d;

DROP TABLE mlparted5;

-- check that message shown after failure to find a partition shows the
-- appropriate key description (or none) in various situations
CREATE TABLE key_desc (
    a int,
    b int
)
PARTITION BY LIST ((a + 0));

CREATE TABLE key_desc_1 PARTITION OF key_desc
FOR VALUES IN (1)
PARTITION BY RANGE (b);

CREATE USER regress_insert_other_user;

GRANT SELECT (a) ON key_desc_1 TO regress_insert_other_user;

GRANT INSERT ON key_desc TO regress_insert_other_user;

SET ROLE regress_insert_other_user;

-- no key description is shown
INSERT INTO key_desc
    VALUES (1, 1);

RESET ROLE;

GRANT SELECT (b) ON key_desc_1 TO regress_insert_other_user;

SET ROLE regress_insert_other_user;

-- key description (b)=(1) is now shown
INSERT INTO key_desc
    VALUES (1, 1);

-- key description is not shown if key contains expression
INSERT INTO key_desc
    VALUES (2, 1);

RESET ROLE;

REVOKE ALL ON key_desc FROM regress_insert_other_user;

REVOKE ALL ON key_desc_1 FROM regress_insert_other_user;

DROP ROLE regress_insert_other_user;

DROP TABLE key_desc, key_desc_1;

-- test minvalue/maxvalue restrictions
CREATE TABLE mcrparted (
    a int,
    b int,
    c int
)
PARTITION BY RANGE (a, abs(b), c);

CREATE TABLE mcrparted0 PARTITION OF mcrparted
FOR VALUES FROM (MINVALUE, 0, 0) TO (1,
MAXVALUE,
MAXVALUE);

CREATE TABLE mcrparted2 PARTITION OF mcrparted
FOR VALUES FROM (10, 6,
MINVALUE) TO (10,
MAXVALUE,
MINVALUE);

CREATE TABLE mcrparted4 PARTITION OF mcrparted
FOR VALUES FROM (21,
MINVALUE, 0) TO (30, 20,
MINVALUE);

-- check multi-column range partitioning expression enforces the same
-- constraint as what tuple-routing would determine it to be
CREATE TABLE mcrparted0 PARTITION OF mcrparted
FOR VALUES FROM (MINVALUE,
MINVALUE,
MINVALUE) TO (1,
MAXVALUE,
MAXVALUE);

CREATE TABLE mcrparted1 PARTITION OF mcrparted
FOR VALUES FROM (2, 1,
MINVALUE) TO (10, 5, 10);

CREATE TABLE mcrparted2 PARTITION OF mcrparted
FOR VALUES FROM (10, 6,
MINVALUE) TO (10,
MAXVALUE,
MAXVALUE);

CREATE TABLE mcrparted3 PARTITION OF mcrparted
FOR VALUES FROM (11, 1, 1) TO (20, 10, 10);

CREATE TABLE mcrparted4 PARTITION OF mcrparted
FOR VALUES FROM (21,
MINVALUE,
MINVALUE) TO (30, 20,
MAXVALUE);

CREATE TABLE mcrparted5 PARTITION OF mcrparted
FOR VALUES FROM (30, 21, 20) TO (MAXVALUE,
MAXVALUE,
MAXVALUE);

-- null not allowed in range partition
INSERT INTO mcrparted
    VALUES (NULL, NULL, NULL);

-- routed to mcrparted0
INSERT INTO mcrparted
    VALUES (0, 1, 1);

INSERT INTO mcrparted0
    VALUES (0, 1, 1);

-- routed to mcparted1
INSERT INTO mcrparted
    VALUES (9, 1000, 1);

INSERT INTO mcrparted1
    VALUES (9, 1000, 1);

INSERT INTO mcrparted
    VALUES (10, 5, -1);

INSERT INTO mcrparted1
    VALUES (10, 5, -1);

INSERT INTO mcrparted
    VALUES (2, 1, 0);

INSERT INTO mcrparted1
    VALUES (2, 1, 0);

-- routed to mcparted2
INSERT INTO mcrparted
    VALUES (10, 6, 1000);

INSERT INTO mcrparted2
    VALUES (10, 6, 1000);

INSERT INTO mcrparted
    VALUES (10, 1000, 1000);

INSERT INTO mcrparted2
    VALUES (10, 1000, 1000);

-- no partition exists, nor does mcrparted3 accept it
INSERT INTO mcrparted
    VALUES (11, 1, -1);

INSERT INTO mcrparted3
    VALUES (11, 1, -1);

-- routed to mcrparted5
INSERT INTO mcrparted
    VALUES (30, 21, 20);

INSERT INTO mcrparted5
    VALUES (30, 21, 20);

INSERT INTO mcrparted4
    VALUES (30, 21, 20);

-- error
-- check rows
SELECT
    tableoid::regclass::text,
    *
FROM
    mcrparted
ORDER BY
    1;

-- cleanup
DROP TABLE mcrparted;

-- check that a BR constraint can't make partition contain violating rows
CREATE TABLE brtrigpartcon (
    a int,
    b text
)
PARTITION BY LIST (a);

CREATE TABLE brtrigpartcon1 PARTITION OF brtrigpartcon
FOR VALUES IN (1);

CREATE OR REPLACE FUNCTION brtrigpartcon1trigf ()
    RETURNS TRIGGER
    AS $$
BEGIN
    NEW.a := 2;
    RETURN new;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER brtrigpartcon1trig
    BEFORE INSERT ON brtrigpartcon1 FOR EACH ROW
    EXECUTE PROCEDURE brtrigpartcon1trigf ();

INSERT INTO brtrigpartcon
    VALUES (1, 'hi there');

INSERT INTO brtrigpartcon1
    VALUES (1, 'hi there');

-- check that the message shows the appropriate column description in a
-- situation where the partitioned table is not the primary ModifyTable node
CREATE TABLE inserttest3 (
    f1 text DEFAULT 'foo',
    f2 text DEFAULT 'bar',
    f3 int
);

CREATE ROLE regress_coldesc_role;

GRANT INSERT ON inserttest3 TO regress_coldesc_role;

GRANT INSERT ON brtrigpartcon TO regress_coldesc_role;

REVOKE SELECT ON brtrigpartcon FROM regress_coldesc_role;

SET ROLE regress_coldesc_role;

WITH result AS (
INSERT INTO brtrigpartcon
        VALUES (1, 'hi there')
    RETURNING
        1)
    INSERT INTO inserttest3 (f3)
    SELECT
        *
    FROM
        result;

RESET ROLE;

-- cleanup
REVOKE ALL ON inserttest3 FROM regress_coldesc_role;

REVOKE ALL ON brtrigpartcon FROM regress_coldesc_role;

DROP ROLE regress_coldesc_role;

DROP TABLE inserttest3;

DROP TABLE brtrigpartcon;

DROP FUNCTION brtrigpartcon1trigf ();

-- check that "do nothing" BR triggers work with tuple-routing (this checks
-- that estate->es_result_relation_info is appropriately set/reset for each
-- routed tuple)
CREATE TABLE donothingbrtrig_test (
    a int,
    b text
)
PARTITION BY LIST (a);

CREATE TABLE donothingbrtrig_test1 (
    b text,
    a int
);

CREATE TABLE donothingbrtrig_test2 (
    c text,
    b text,
    a int
);

ALTER TABLE donothingbrtrig_test2
    DROP COLUMN c;

CREATE OR REPLACE FUNCTION donothingbrtrig_func ()
    RETURNS TRIGGER
    AS $$
BEGIN
    RAISE NOTICE 'b: %', NEW.b;
    RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER donothingbrtrig1
    BEFORE INSERT ON donothingbrtrig_test1 FOR EACH ROW
    EXECUTE PROCEDURE donothingbrtrig_func ();

CREATE TRIGGER donothingbrtrig2
    BEFORE INSERT ON donothingbrtrig_test2 FOR EACH ROW
    EXECUTE PROCEDURE donothingbrtrig_func ();

ALTER TABLE donothingbrtrig_test ATTACH PARTITION donothingbrtrig_test1
FOR VALUES IN (1);

ALTER TABLE donothingbrtrig_test ATTACH PARTITION donothingbrtrig_test2
FOR VALUES IN (2);

INSERT INTO donothingbrtrig_test
    VALUES (1, 'foo'), (2, 'bar');

SELECT
    tableoid::regclass,
    *
FROM
    donothingbrtrig_test;

-- cleanup
DROP TABLE donothingbrtrig_test;

DROP FUNCTION donothingbrtrig_func ();

-- check multi-column range partitioning with minvalue/maxvalue constraints
CREATE TABLE mcrparted (
    a text,
    b int
)
PARTITION BY RANGE (a, b);

CREATE TABLE mcrparted1_lt_b PARTITION OF mcrparted
FOR VALUES FROM (MINVALUE,
MINVALUE) TO ('b',
MINVALUE);

CREATE TABLE mcrparted2_b PARTITION OF mcrparted
FOR VALUES FROM ('b',
MINVALUE) TO ('c',
MINVALUE);

CREATE TABLE mcrparted3_c_to_common PARTITION OF mcrparted
FOR VALUES FROM ('c',
MINVALUE) TO ('common',
MINVALUE);

CREATE TABLE mcrparted4_common_lt_0 PARTITION OF mcrparted
FOR VALUES FROM ('common',
MINVALUE) TO ('common', 0);

CREATE TABLE mcrparted5_common_0_to_10 PARTITION OF mcrparted
FOR VALUES FROM ('common', 0) TO ('common', 10);

CREATE TABLE mcrparted6_common_ge_10 PARTITION OF mcrparted
FOR VALUES FROM ('common', 10) TO ('common',
MAXVALUE);

CREATE TABLE mcrparted7_gt_common_lt_d PARTITION OF mcrparted
FOR VALUES FROM ('common',
MAXVALUE) TO ('d',
MINVALUE);

CREATE TABLE mcrparted8_ge_d PARTITION OF mcrparted
FOR VALUES FROM ('d',
MINVALUE) TO (MAXVALUE,
MAXVALUE);

\d+ mcrparted
\d+ mcrparted1_lt_b
\d+ mcrparted2_b
\d+ mcrparted3_c_to_common
\d+ mcrparted4_common_lt_0
\d+ mcrparted5_common_0_to_10
\d+ mcrparted6_common_ge_10
\d+ mcrparted7_gt_common_lt_d
\d+ mcrparted8_ge_d
INSERT INTO mcrparted
    VALUES ('aaa', 0), ('b', 0), ('bz', 10), ('c', -10), ('comm', -10), ('common', -10), ('common', 0), ('common', 10), ('commons', 0), ('d', -10), ('e', 0);

SELECT
    tableoid::regclass,
    *
FROM
    mcrparted
ORDER BY
    a,
    b;

DROP TABLE mcrparted;

-- check that wholerow vars in the RETURNING list work with partitioned tables
CREATE TABLE returningwrtest (
    a int
)
PARTITION BY LIST (a);

CREATE TABLE returningwrtest1 PARTITION OF returningwrtest
FOR VALUES IN (1);

INSERT INTO returningwrtest
    VALUES (1)
RETURNING
    returningwrtest;

-- check also that the wholerow vars in RETURNING list are converted as needed
ALTER TABLE returningwrtest
    ADD b text;

CREATE TABLE returningwrtest2 (
    b text,
    c int,
    a int
);

ALTER TABLE returningwrtest2
    DROP c;

ALTER TABLE returningwrtest ATTACH PARTITION returningwrtest2
FOR VALUES IN (2);

INSERT INTO returningwrtest
    VALUES (2, 'foo')
RETURNING
    returningwrtest;

DROP TABLE returningwrtest;