Trigger function is not working as expected after migration from v9.6 to v11.7

Поиск
Список
Период
Сортировка
От Nagaraj Raj
Тема Trigger function is not working as expected after migration from v9.6 to v11.7
Дата
Msg-id 592586284.142826.1601426716345@mail.yahoo.com
обсуждение исходный текст
Ответы Re: Trigger function is not working as expected after migration from v9.6 to v11.7  (David Rowley <dgrowleyml@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Hi,

I have implemented table partition with an inheritance approach  and the trigger function is not working as expected after migration from v9.6 to v11.7

The issue is, I have a table and created inheritance tables for each month; it has a date column load_date as it takes current_timestamp  and respective this created trigger function with BEFORE  INSERT OR UPDATE. 

Everything good so far, but earlier  I have inheritance tables till August 2020, so I created inheritance tables till December 2020, and somehow I missed for the month September 2020 in the table creation. Still, I updated the trigger function without missing September 2020.


So, when injection started for September 2020 into a master table, the trigger didn't occur through any error because it's satisfied the conditions of the trigger function and after passing through the trigger function, it should look for a table September 2020, because the condition is base on load_date (current_timestamp ) if not it should insert into a master table or through any error,

The server acted strangely, records inserted to the master table, but load_date didn't take current_timestamp; it recorded future timestamp, i.e., January 2021.



Attached trigger function and trigger with table structure, problem is if the child table does exit it's not omitting the error and data directing to the master table with a futuristic data value of load_dttm.

basically, it was working code in version 9.6.









-- Table: l_billing_account

-- DROP TABLE l_billing_account;

CREATE TABLE l_billing_account
(
    billing_account_guid character varying(40) COLLATE pg_catalog."default" NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    log_position_number character varying(40) COLLATE pg_catalog."default" NOT NULL,
    operation_code character varying(40) COLLATE pg_catalog."default" NOT NULL,
    commit_dttm timestamp(6) without time zone NOT NULL,
    oracle_scn character varying(40) COLLATE pg_catalog."default",
    ban integer NOT NULL,
    bl_cur_bill_seq_no smallint,
    bl_last_prod_date timestamp(6) without time zone,
    bill_method character varying(2) COLLATE pg_catalog."default",
    bill_method_eff_date timestamp(6) without time zone,
    rstr_fee_qid character varying(15) COLLATE pg_catalog."default",
    prev_bill_format character varying(2) COLLATE pg_catalog."default",
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
);



-- FUNCTION: func_l_billing_account_insert_trigger()



CREATE FUNCTION func_l_billing_account_insert_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
   
   IF  ( NEW.load_dttm >=  '2020-02-01 00:00:00-07' AND
         NEW.load_dttm <  '2020-02-16 00:00:00-07' ) THEN
        INSERT INTO l_billing_account_y2020m02begin VALUES (NEW.*);
          
ELSIF ( NEW.load_dttm >=  '2020-02-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-03-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m02end VALUES (NEW.*);
        
    ELSIF ( NEW.load_dttm >=  '2020-03-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-03-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m03begin VALUES (NEW.*);
ELSIF ( NEW.load_dttm >=  '2020-03-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-04-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m03end VALUES (NEW.*);

    ELSIF ( NEW.load_dttm >=  '2020-04-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-04-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m04begin VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-04-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-05-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m04end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-05-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-05-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m05begin VALUES (NEW.*);
ELSIF ( NEW.load_dttm >=  '2020-05-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-06-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m05end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-06-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-06-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m06begin VALUES (NEW.*);
ELSIF ( NEW.load_dttm >=  '2020-06-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-07-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m06end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-07-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-07-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m07begin VALUES (NEW.*);
ELSIF ( NEW.load_dttm >=  '2020-07-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-08-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m07end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-08-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-08-16 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m08begin VALUES (NEW.*);
ELSIF ( NEW.load_dttm >=  '2020-08-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-09-01 00:00:00-07' ) THEN
            
        INSERT INTO l_billing_account_y2020m08end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-09-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-09-16 00:00:00-07' ) THEN
INSERT INTO l_billing_account_y2020m09begin VALUES (NEW.*);
           

ELSIF ( NEW.load_dttm >=  '2020-09-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-10-01 00:00:00-07' ) THEN

        INSERT INTO l_billing_account_y2020m09end VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-10-01 00:00:00-07' AND
            NEW.load_dttm <  '2020-10-16 00:00:00-07' ) THEN

INSERT INTO l_billing_account_y2020m10begin VALUES (NEW.*);

ELSIF ( NEW.load_dttm >=  '2020-10-16 00:00:00-07' AND
            NEW.load_dttm <  '2020-11-01 00:00:00-07' ) THEN

INSERT INTO l_billing_account_y2020m10end VALUES (NEW.*);

    
    ELSE
        RAISE EXCEPTION 
        ' out of range exception.  Fix the func_l_billing_account_insert_trigger() function. 
         HINT: Might need to create a new partition table, load_dttm is out of range in existing child_tables
         Action:  Please contact DBA' ;
    END IF;
    RETURN NULL;
END;
$BODY$;




-- Trigger: l_billing_account_partition_trigger



CREATE TRIGGER l_billing_account_partition_trigger
    BEFORE INSERT OR UPDATE 
    ON l_billing_account
    FOR EACH ROW
    EXECUTE PROCEDURE func_l_billing_account_insert_trigger();

-- Table: l_billing_account_y2020m09end

-- DROP TABLE l_billing_account_y2020m09end;

CREATE TABLE l_billing_account_y2020m09end
( CONSTRAINT l_billing_account_y2020m09end_load_dttm_check CHECK (load_dttm >= '2020-09-16 00:00:00'::timestamp without time zone 
AND load_dttm < '2020-10-01 00:00:00'::timestamp without time zone)
) INHERITS (l_billing_account);



We are running PostgreSQL 11.7 on x86_64-pc-Linux-gnu, compiled by GCC (GCC) 4.9.3, 64-bit

Thanks,
Rj








В списке pgsql-bugs по дате отправления:

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: ERROR: insufficient columns in the PRIMARY KEY constraint definition
Следующее
От: Nagaraj Raj
Дата:
Сообщение: Re: ERROR: insufficient columns in the PRIMARY KEY constraint definition