Re: table partition with inheritance having current_timestamp issue if we miss range table

Поиск

Список

Период

Сортировка

От	Nagaraj Raj
Тема	Re: table partition with inheritance having current_timestamp issue if we miss range table
Дата	29 сентября 2020 г. 21:18:25
Msg-id	1291615891.327892.1601403505183@mail.yahoo.com обсуждение исходный текст
Ответ на	Re: table partition with inheritance having current_timestamp issue if we miss range table (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы	Re: table partition with inheritance having current_timestamp issue if we miss range table (Nagaraj Raj <nagaraj.sf@yahoo.com>) Re: table partition with inheritance having current_timestamp issue if we miss range table (David Rowley <dgrowleyml@gmail.com>)
Список	pgsql-bugs

Дерево обсуждения

Hi,

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.

Postgres 11 | db<>fiddle

Free online SQL environment for experimenting and sharing.

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

Thanks,

On Wednesday, September 16, 2020, 11:51:46 AM PDT, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Nagaraj Raj <nagaraj.sf@yahoo.com> writes:

> How to reproduce the issue,
> 1. Create a table partition with table inheritance by range on the date column2. Place triggers and trigger function3. In the trigger function, update with inheritance tables from September to December or appropriate.4. while creating inheritance tables, skip one of the tables which updated in the trigger function5. If you started to insert, it would write into the master table with a future date.

TBH, I strongly doubt that anyone is going to follow up on this report
as given. It seems at least as likely that the bug is in your trigger
code as in Postgres proper. Without the exact schema and trigger code,
anyone who did try couldn't be sure whether failure to see something
interesting means that there's no Postgres bug or just that they'd
failed to duplicate what you did.

If you'd like us to take an interest, please submit a *self contained*
test case. Preferably a script that starts with an empty database,
creates all the requisite objects, and then does whatever is needed
to exhibit the misbehavior.

regards, tom lane

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

Предыдущее

От: PG Bug reporting form
Дата: 29 сентября 2020 г., 20:31:48
Сообщение: BUG #16642: INFORMATION_SCHEMA-DESIGN

Следующее

От: Nagaraj Raj
Дата: 29 сентября 2020 г., 21:22:50
Сообщение: Re: recovery.conf' is not creating in pg_basebackup with version 13

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Re: table partition with inheritance having current_timestamp issue if we miss range table

Postgres 11 | db<>fiddle

Предыдущее

Следующее