Как сделать правильно check constraint?

Question

[masimka]

Есть таблица

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,

---

Есть триггер:

Триггер на events на 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;

Для теста генерю такой запрос:

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) ;

Получаю ошибку!
ERROR: new row for relation "events2017m10" violates check constraint "dtime_check"
Что я делаю не так*?

Answer 1

[Melkij]

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

Какой удивительно-непроверенный способ выстрелить себе в ногу. Половина года просто не нужна? Ни февраль ни ноябрь?

При этом же

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

INSERT INTO ' || table_part || '
Какой дважды отважный способ отстрелить ногу. Дебажить что у вас получается не пробовали в принципе?
И с чего же это действительно violates check constraint? Вообще не понятно. И вас ничего на насторожило даже в попытке вставить августовские данные в m10 раздел? Ну нельзя же так, в самом деле.

Про race condition не упоминаю даже, он хотя бы действительно не для всех применений имеет значение.

Comments

[masimka]

я уже окончательно запутался. Сейчас сделал так:

events trigger

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(NEW.dtime, 'YYYY')::text
|| 'm' || to_char(NEW.dtime, '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;

Вставляет за дату: '2017-10-20 00:00:00.000000'
а за '2017-11-20 00:00:00.000000' Нет.

Половина года просто не нужна? Ни февраль ни ноябрь?

Что значит половина года?
Дата текущего месяца

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';

[Melkij]

Что значит половина года?

Не во всех месяцах есть 31 день. И Postgresql об этом знает. Даже 29 день не всегда бывает. И об этом база тоже знает. И даст за такое безобразие как 31 ноября ошибку.

Выведите в notice переменные, посмотрите какие именно констрейнты создаются. Раз триггер ведёт себя странно - значит отлаживайте триггер.

Answer 2

[masimka]

теперь настроил триггер так|:

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;

и все автозаполнения заработали.

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;

сейчас получилось так:

\d+ 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 | |
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,

подправил формирование названия таблицы изсходя из пришедешей даты., и следующий месяц сделал первый день
Сейчас все наследники такие:

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