How to do a check constraint correctly?

Question

PostgreSQL

There is a table

events

Table "public.events"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------------+-----------------------------------------------------+----------+--------------+-------------
eventtype_id | integer | not null | plain | |
order_id | integer | | plain | |
product_id | integer | | plain | |
user_id | integer | | plain | |
values | character varying(64) | | extended | |
dtime | timestamp without time zone | not null default now() | plain | |
id | bigint | not null default nextval('events_id_seq'::regclass) | plain | |
config_id | integer | | plain | |
Triggers:
insert_events_trigger BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE events_insert_trigger()
Child tables: events2016m02,
events2016m03,
events2016m04,
events2016m05,
events2016m06,
events2016m07,
events2016m08,
events2016m09,
events2016m10,
events2016m11,
events2016m12,
---

There is a trigger:

Trigger on events on INSERT

DECLARE
table_master varchar(255) := 'events';
table_part varchar(255) := '';
curr_month varchar(255) := '';
next_month varchar(255) := '';
BEGIN
-- Даём имя партиции --------------------------------------------------
table_part := table_master
|| '' || to_char(NOW(), 'YYYY')::text
|| 'm' || to_char(NOW(), 'MM')::text;
curr_month := date_part( 'year', NEW.dtime)::text ||'-'|| date_part( 'month', NEW.dtime )::text ||'-01';
next_month := date_part( 'year', NEW.dtime)::text ||'-'|| date_part( 'month', NEW.dtime )::text ||'-31';
-- Проверяем партицию на существование --------------------------------
PERFORM
1
FROM
pg_class
WHERE
relname = table_part
LIMIT
1;
-- Если её ещё нет, то создаём --------------------------------------------
IF NOT FOUND
THEN
-- Cоздаём партицию, наследуя мастер-таблицу --------------------------
EXECUTE '
CREATE TABLE ' || table_part || ' ( like '|| table_master ||' including all ) inherits ('|| table_master ||')' ;
EXECUTE '
alter table ' || table_part || ' add CONSTRAINT dtime_check CHECK (dtime >= '''|| curr_month||'''::timestamp AND dtime < '''||next_month||'''::timestamp)';
END IF;
-- Вставляем данные в партицию --------------------------------------------
EXECUTE '
INSERT INTO ' || table_part || '
SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*';
RETURN NULL;
END;

To test, I generate the following query:

insert INTO  events  select
                       floor(random()*(6-3+1)+1)*4,
                       floor(random()*(25-10+1))*543,
                       floor(random()*(6-3+1)+1)*4,
                       floor(random()*(25-10+1))*543,
                       floor(random()*(25-10+1))*543,
                       '2017-08-20 11:34:58.408739' ,
                       nextval('events_id_seq'::regclass),
                       floor(random()*(25-10+1))*543

                     from
                         generate_series(1, 60000) ;

I'm getting an error!
ERROR: new row for relation "events2017m10" violates check constraint "dtime_check"
What am I doing wrong*?

Answer 1

next_month := date_part( 'year', NEW.dtime)::text ||'-'|| date_part( 'month', NEW.dtime )::text ||'-31';

What an amazingly untested way to shoot yourself in the foot. Half a year is just not needed? February or November?
At the same time

table_part := table_master
|| '' || to_char(NOW(), 'YYYY')::text
|| 'm' || to_char(NOW(), 'MM')::text;

What a doubly daring way to shoot off a foot. Did you try to debug what you can do in principle?
And why does it really violates check constraint? Generally not clear. And nothing alerted you even in an attempt to insert the August data into the m10 partition? Well, you can't, really.
I don’t even mention the race condition, at least it doesn’t really matter for all applications.

Answer 2

now configured the trigger like this|:

DECLARE
table_master    varchar(255)        := 'events';
table_part      varchar(255)        := '';
curr_month      varchar(255)        := '';

next_month      varchar(255)        := '';

month_number    integer       := 0;
month_number_text    varchar(255)       :='';

BEGIN

month_number := date_part( 'month', NEW.dtime);

IF month_number < 10 THEN
month_number_text := '0'|| month_number::text;

END IF;
-- Даём имя партиции --------------------------------------------------
table_part := table_master
|| '' || to_char(NEW.dtime::date, 'YYYY')::text
|| 'm' || to_char(NEW.dtime::date, 'MM')::text;

--date '2001-12-28' + interval '1 month'

curr_month :=  date_part( 'year', NEW.dtime)::text ||'-'||to_char(NEW.dtime::date, 'MM')::text ||'-01';

-- next_month :=  date_part( 'year', NEW.dtime)::text ||'-'|| month_number_text ||'-31';
next_month :=   NEW.dtime + interval '1 month';
next_month :=   to_char(next_month::date, 'YYYY-MM')||'-01';


-- Проверяем партицию на существование --------------------------------
PERFORM
1
FROM
pg_class
WHERE
relname = table_part
LIMIT
1;

-- Если её ещё нет, то создаём --------------------------------------------
IF NOT FOUND
THEN
-- Cоздаём партицию, наследуя мастер-таблицу --------------------------
EXECUTE '
             CREATE TABLE ' || table_part || ' (  like '|| table_master ||' including all ) inherits ('|| table_master ||')'   ;
EXECUTE '
      alter table  ' || table_part || '  add CONSTRAINT dtime_check CHECK (dtime >= '''|| curr_month||'''::timestamp AND dtime < '''||next_month||'''::timestamp)';

END IF;



-- Вставляем данные в партицию --------------------------------------------
EXECUTE '
        INSERT INTO ' || table_part || '
        SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*';

RETURN NULL;
END;

and all autocomplete earned.

DECLARE
table_master    varchar(255)        := 'events';
table_part      varchar(255)        := '';
curr_month      varchar(255)        := '';

next_month      varchar(255)        := '';

month_number    integer       := 0;
month_number_text    varchar(255)       :='';

BEGIN

month_number := date_part( 'month', NEW.dtime);

IF month_number < 10 THEN
month_number_text := '0'|| month_number::text;

END IF;
-- Даём имя партиции --------------------------------------------------
table_part := table_master
|| '' || to_char(NEW.dtime::date, 'YYYY')::text
|| 'm' || to_char(NEW.dtime::date, 'MM')::text;

--date '2001-12-28' + interval '1 month'

curr_month :=  date_part( 'year', NEW.dtime)::text ||'-'||to_char(NEW.dtime::date, 'MM')::text ||'-01';

-- next_month :=  date_part( 'year', NEW.dtime)::text ||'-'|| month_number_text ||'-31';
next_month :=   NEW.dtime + interval '1 month';
next_month :=   to_char(next_month::date, 'YYYY-MM')||'-01';


-- Проверяем партицию на существование --------------------------------
PERFORM
1
FROM
pg_class
WHERE
relname = table_part
LIMIT
1;

-- Если её ещё нет, то создаём --------------------------------------------
IF NOT FOUND
THEN
-- Cоздаём партицию, наследуя мастер-таблицу --------------------------
EXECUTE '
             CREATE TABLE ' || table_part || ' (  like '|| table_master ||' including all ) inherits ('|| table_master ||')'   ;
EXECUTE '
      alter table  ' || table_part || '  add CONSTRAINT dtime_check CHECK (dtime >= '''|| curr_month||'''::timestamp AND dtime < '''||next_month||'''::timestamp)';

END IF;



-- Вставляем данные в партицию --------------------------------------------
EXECUTE '
        INSERT INTO ' || table_part || '
        SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*';

RETURN NULL;
END;

now it looks like this:
Table "public.events"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
eventtype_id | integer | not null | plain | |
order_id | integer | | plain | |
product_id | integer | | plain | |
user_id | integer | | plain | |
values | character varying(64) | | extended | |
dtime | timestamp without time zone | not null default now() | plain | |
id | bigint | not null default nextval('events_id_seq'::regclass) | plain | |
config_id | integer | | plain | |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
"dtime" btree (dtime)
"eventtype_id" btree (eventtype_id)
"order_id" btree (order_id)
"product_id" btree (product_id)
"users_id" btree (user_id)
Triggers:
insert_events_trigger BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE events_insert_trigger()
Child tables: events2016m02,
events2016m03,
events2016m04,
events2016m05,
events2016m06,
events2016m07,
events2016m08,
events2016m09,
events2016m10,
events2016m11,
events2016m12,
events2017m01,
events2017m02,
events2017m03,
events2017m04,
events2017m05,
events2017m06,
events2017m07,
corrected the formation of the table name based on the date that came., and the next month made the first day.
Now all the heirs are:

Check constraints:

Check constraints:
"dtime_check" CHECK (dtime >= '2017-04-01 00:00:00'::timestamp without time zone AND dtime < '2017-05-01 00:00:00'::timestamp without time zone)
Inherits: events