Columna Incremental en Tablas particionadas

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

Responses

Browse pgsql-es-ayuda by date

  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