Constraints on DOMAIN types are not enforced

Products

• VMware Tanzu Greenplum
• Greenplum
• Pivotal Data Suite Non Production Edition
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

In Greenplum DB (GPDB) 7.x the constraints on a DOMAIN are not always enforced.

This issue is present in Greenplum DB 6.x also if "gp_autostats_mode=none".

The constraints are enforced when using Postgres query optimizer, but not always enforced when using the Pivotal optimizer (GPORCA).

Example:

-- Create the DOMAIN
CREATE DOMAIN not_null_t integer NOT NULL;

-- Create a test table with a column of type DOMAIN above
CREATE TABLE test_table(a int, b not_null_t) DISTRIBUTED BY (a);

-- Set optimizer to on and insert NULL values into column b...
SET optimizer=on;

-- Insert a specific NULL value in column "b" reports and error (as expected)
INSERT INTO test_table VALUES (0, null::integer);
ERROR:  domain not_null_t does not allow null values.

-- Insert a value in column "a" and implies a NULL in column "b" is allowed. This is incorrect behaviour
INSERT INTO test_table(a) VALUES (1);
INSERT 0 1

-- Select from the table shows a NULL in column "b"
SELECT * from test_table;
 a | b 
---+---
 1 |  
(1 row)

-- Set optimizer to off and both inserts will fail as expected
SET optimizer=off;

INSERT INTO test_table VALUES (0, null::integer);
ERROR:  domain not_null_t does not allow null values

INSERT INTO test_table(a) VALUES (1);
ERROR:  domain not_null_t does not allow null values

Resolution

Workaround

Use "optimizer=off" when working with a DOMAIN.

Fix

A fix is in development for Greenplum DB 7.x. It will be released in a future release. Check the Release Notes for issue 33692.