From: | Pedro Castillo Larios <pedrodac2(at)hotmail(dot)com> |
---|---|
To: | "pgsql-es-ayuda(at)postgresql(dot)org" <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | Columna Incremental en Tablas particionadas |
Date: | 2017-06-29 22:47:01 |
Message-ID: | SN1PR11MB0701E431F9097D143D469DC3F7D20@SN1PR11MB0701.namprd11.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
Hola amigos, buena tarde a todos:
Mi problema es que por tamaño de La base de datos estoy implementando el partcionamiento de una de mis tablas, y uso reglas en lugar de triggers, el particionamiento es por uno de los campos de fecha creando tabla por dia, ya que se guardar alrededor de 200,000 registros por dia.
La replicacion se ejecuta bien, pero me crea 2 registros 1 en la tabla padre y otro en la tabla hija al hacer el insert, cuando corren los updates solo se realizan en la tabla hija.
Creo que el problema es un campo incremental que se utiliza, ese campo denominado my_rowid, le quite el incremental en la tabla Padre, pero no me guarda datos en la tabla hija
mis tablas y reglas quedaron asi
Table "public.table1"
Column | Type | Modifiers
------------------------------+------------------------+----------------------------------------------------
table1_ver_ | character varying(32) | not null
table1_id | bigint | not null
table1_msg_type | bigint | not null
table1_type_id | bigint | not null
table1_subtype_id | bigint | not null
table1_time_stamp | bigint | not null
table1_input_time_stamp | bigint | not null
table1_transmision_time_stamp | bigint | not null
table1_creation_time_stamp | bigint | not null
table1_reception_time_stamp | bigint | not null
table1_host_time_stamp | bigint | not null
table1_business_date | character varying(4) | not null
table1_capture_time_stamp | bigint | not null
table1_card_number | character varying(48) | not null
table1_processcode | integer | not null
table1_amount | double precision | not null
table1_exp_date | character varying(16) | not null
table1_input_mode | integer | not null
table1_tracki | character varying(160) | not null
my_rowid | integer | not null
Rules:
CREATE OR REPLACE RULE Insert_table1_130617 AS ON INSERT TO table1 WHERE (table1_input_time_stamp >= 1497330001 AND table1_input_time_stamp < 1497416399) DO INSTEAD INSERT INTO table1_130617 VALUES(NEW.*);;;
Table "public.table1_130617"
Column | Type | Modifiers
------------------------------+------------------------+----------------------------------------------------
table1_ver_ | character varying(32) | not null
table1_id | bigint | not null
table1_msg_type | bigint | not null
table1_type_id | bigint | not null
table1_subtype_id | bigint | not null
table1_time_stamp | bigint | not null
table1_input_time_stamp | bigint | not null
table1_transmision_time_stamp | bigint | not null
table1_creation_time_stamp | bigint | not null
table1_reception_time_stamp | bigint | not null
table1_host_time_stamp | bigint | not null
table1_business_date | character varying(4) | not null
table1_capture_time_stamp | bigint | not null
table1_card_number | character varying(48) | not null
table1_processcode | integer | not null
table1_amount | double precision | not null
table1_exp_date | character varying(16) | not null
table1_input_mode | integer | not null
table1_tracki | character varying(160) | not null
my_rowid | integer | not null default nextval('seq_my_rowid'::regclass)
Indexes:
"transactions_130617_pkey" PRIMARY KEY, btree (trn_version_, trn_id)
"_transactions_transactions_130617" UNIQUE, btree (trn_id, trn_version_)
"_trn_batch_transactions_130617" UNIQUE, btree (trn_external_ter_id, trn_hst_id, trn_external_mer_id_close, trn_bat_number_external, trn_close, trn_sequ_number, trn_id, trn_version_)
"_trn_cancel_return_transactions_130617" UNIQUE, btree (trn_ukey, trn_internal_mer_id, trn_internal_ter_id, trn_internal_trace, trn_id, trn_version_)
"_trn_card_transactions_130617" UNIQUE, btree (trn_card_number, trn_creation_time_stamp, trn_id, trn_version_)
"_trn_close_merint_transactions_130617" UNIQUE, btree (trn_close, trn_internal_mer_id, trn_external_mer_id, trn_id, trn_version_)
"_trn_close_term_transactions_130617" UNIQUE, btree (trn_close, trn_external_ter_id, trn_id, trn_version_)
"_trn_host_mer_ter_batchint_transactions_130617" UNIQUE, btree (trn_hst_id, trn_internal_mer_id, trn_internal_ter_id, trn_bat_number_internal, trn_id, trn_version_)
"_trn_host_transactions_130617" UNIQUE, btree (trn_hst_id, trn_pro_id, trn_creation_time_stamp, trn_id, trn_version_)
"_trn_key_transactions_130617" UNIQUE, btree (trn_key, trn_creation_time_stamp, trn_id, trn_version_)
"_trn_mer_card_transactions_130617" UNIQUE, btree (trn_internal_mer_id, trn_card_number, trn_creation_time_stamp, trn_id, trn_version_)
"_trn_notif_host_transactions_130617" UNIQUE, btree (trn_notif, trn_hst_id, trn_id, trn_version_)
Check constraints:
"timestamp_transactions_1306" CHECK (trn_input_time_stamp >= 1497330001 AND trn_input_time_stamp < 1497416399)
Inherits: transactions
Amigos, Espero puedan apoyarme en evitar esta duplicidad
From | Date | Subject | |
---|---|---|---|
Next Message | Gilberto Castillo | 2017-06-30 12:36:29 | Re: [MASSMAIL]Columna Incremental en Tablas particionadas |
Previous Message | Néstor Ramires | 2017-06-27 14:41:04 | Re: Consulta base de datos espacial |