In a previous tip we learned that Partitioning is another term for physically dividing large tables in YugabyteDB into smaller, more manageable tables to improve performance.
We also learned that because partitioned tables do not appear nor act differently from the original table, applications accessing the database are not always aware of the fact that partitioning has taken place.
Unfortunately we also discovered that there are no “out of the box” ways to enforce uniqueness across partitions.
But in that tip we saw that it was possible via a check constraint which called function that looks for existing records.
In today’s tip we will employ another trick to enforce uniqueness across partitions, but this we’ll will use a unique constraint in the example.
Let’s start off with the partitioned table TEST again. This time we’ll add a third column that we want to be unique.
yugabyte=# CREATE TABLE test (c1 INT, c2 VARCHAR, c3 VARCHAR, PRIMARY KEY(c1, c2), UNIQUE(c3, c2)) PARTITION BY LIST (c2);
CREATE TABLE
yugabyte=# CREATE TABLE test_a PARTITION OF test (c1, c2) FOR VALUES IN ('a');
CREATE TABLE
yugabyte=# CREATE TABLE test_b PARTITION OF test (c1, c2) FOR VALUES IN ('b');
CREATE TABLE
Let’s see what happens when we try to insert a non-unique value in the C3 column:
yugabyte=# INSERT INTO test SELECT 1, 'a', 'z';
INSERT 0 1
yugabyte=# INSERT INTO test SELECT 2, 'a', 'z';
ERROR: duplicate key value violates unique constraint "test_a_c3_c2_key"
yugabyte=# INSERT INTO test SELECT 2, 'b', 'z';
INSERT 0 1
yugabyte=# SELECT * FROM test ORDER BY c1;
c1 | c2 | c3
----+----+----
1 | a | z
2 | b | z
(2 rows)
So we see that the unique constraint is enforced in the “a” partition, but we can get a duplicate of c3 by inserting it into partition “b”.
If you want c3 to be unique across partitions, you can employ the check constraint method discussed in the previous tip mentioned above, or you can try another method!
First, for this trick, we’re going to create a non-partitioned table having only the columns of the unique key constraint on the TEST table, but in the new table, the columns are defined as a primary key:
yugabyte=# CREATE TABLE test_c3_uk (c3 VARCHAR PRIMARY KEY);
CREATE TABLE
Next, we’re going to create a trigger so then when a record is inserted into the TEST table, a record will also be inserted in the new table.
We”l need a trigger function for that:
yugabyte=# CREATE OR REPLACE FUNCTION insert_test_c3_uk()
yugabyte-# RETURNS trigger AS
yugabyte-# $$
yugabyte$# BEGIN
yugabyte$# INSERT INTO test_c3_uk (c3) VALUES(NEW.c3);
yugabyte$# RETURN NEW;
yugabyte$# END;
yugabyte$# $$
yugabyte-# LANGUAGE 'plpgsql';
CREATE FUNCTION
Now that we have the function we can create the TEST table trigger:
yugabyte=# CREATE TRIGGER test_insert_c3_uk_trg AFTER INSERT ON test FOR EACH ROW EXECUTE FUNCTION insert_test_c3_uk();
CREATE TRIGGER
Let’s see what happens when we try to insert a non-unique value in the C3 column:
yugabyte=# INSERT INTO test SELECT 1, 'a', 'z';
INSERT 0 1
yugabyte=# INSERT INTO test SELECT 2, 'a', 'z';
ERROR: duplicate key value violates unique constraint "test_a_c3_c2_key"
yugabyte=# INSERT INTO test SELECT 2, 'b', 'z';
ERROR: duplicate key value violates unique constraint "test_c3_uk_pkey"
yugabyte=# SELECT * FROM test ORDER BY c1;
c1 | c2 | c3
----+----+----
1 | a | z
(1 row)
So now, the uniqueness of column c3 is maintained across the partitions!
Note that just like in the previous tip where a check constraint was used to enforce uniqueness across partitions, the trigger method in this current tip may not scale well. Please test thoroughly!
This tip was inspired by a blog post by Franck Pachot titled Global Unique Constraint on a partitioned table in PostgreSQL and YugabyteDB. Please refer to it for a very in depth discussion on this topic!
Have Fun!
