I'm working on Odoo and our database is grow and grow every days. I would like to do table partition for that and I have no experience on table partition process. First, I tried on new database. It was OK and then I worked on some functions and triggers for Odoo table partition. I got error.
Following is the insert functions and triggers for account_move and account_move_line tables.
------------- Create Partiton tables for 2020 ------------------
CREATE TABLE account_move_2020(
CHECK ( create_date >= DATE '2020-01-01' AND create_date < DATE '2020-12-31' )
) INHERITS (account_move);
CREATE INDEX account_move_2020_create_date ON account_move_2020 (create_date);
CREATE TABLE account_move_line_2020(
CHECK ( create_date >= DATE '2020-01-01' AND create_date < DATE '2020-12-31' )
) INHERITS (account_move_line);
CREATE INDEX account_move_line_2020_create_date ON account_move_line_2020 (create_date);
--------- Insert Function for Account Move -----------------------------
CREATE OR REPLACE FUNCTION account_move_insert_function()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE format('INSERT INTO account_move_2020 (id, is_bank_reconcilation, date, name, ref, journal_id, company_id, state, period_id, narration, received_by, rev_rate, is_wallet_tran, partner_id, to_check, create_uid, write_uid, create_date, write_date) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)') using NEW.id, NEW.is_bank_reconcilation, NEW.date, NEW.name, NEW.ref, NEW.journal_id, NEW.company_id, NEW.state, NEW.period_id, NEW.narration, NEW.received_by, NEW.rev_rate, NEW.is_wallet_tran, NEW.partner_id, NEW.to_check, NEW.create_uid, NEW.write_uid, NEW.create_date, NEW.write_date;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
------- Trigger for Account Move Insert ------------------------------
CREATE TRIGGER insert_account_move
BEFORE INSERT ON account_move
FOR EACH ROW EXECUTE PROCEDURE account_move_insert_function();
--------- Insert Function for Account Move Line-----------------------
CREATE OR REPLACE FUNCTION account_move_line_insert()
RETURNS TRIGGER AS $BODY$
BEGIN
EXECUTE format('INSERT INTO account_move_line_2020 (id, create_date, statement_id, journal_id, currency_id,date_maturity, partner_id, reconcile_partial_id, blocked, analytic_account_id, create_uid, credit, centralisation, company_id, reconcile_ref, tax_code_id, state, debit, ref, account_id, period_id, write_date, date_created, date, write_uid, move_id, reconcile_id, tax_amount, product_id, account_tax_id, product_uom_id, amount_currency, quantity, bank_reconciled, to_reconcile, bank_stmt_reconcile_id, invoice_id, cpe_id, service_id, line_employee_id, payment_invoice_id, project, name, asset_id) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44)') using NEW.id, NEW.create_date, NEW.statement_id, NEW.journal_id, NEW.currency_id, NEW.date_maturity, NEW.partner_id, NEW.reconcile_partial_id, NEW.blocked, NEW.analytic_account_id, NEW.create_uid, NEW.credit, NEW.centralisation, NEW.company_id, NEW.reconcile_ref, NEW.tax_code_id, NEW.state, NEW.debit, NEW.ref, NEW.account_id, NEW.period_id, NEW.write_date, NEW.date_created, NEW.date, NEW.write_uid, NEW.move_id, NEW.reconcile_id, NEW.tax_amount, NEW.product_id, NEW.account_tax_id, NEW.product_uom_id, NEW.amount_currency, NEW.quantity, NEW.bank_reconciled, NEW.to_reconcile, NEW.bank_stmt_reconcile_id, NEW.invoice_id, NEW.cpe_id, NEW.service_id, NEW.line_employee_id, NEW.payment_invoice_id, NEW.project, NEW.name, NEW.asset_id;
RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;
------- Trigger for Account Move Line Insert ------------------
CREATE TRIGGER account_move_line_insert_trigger
BEFORE INSERT ON account_move_line
FOR EACH ROW EXECUTE PROCEDURE account_move_line_insert();
After running these queries, I created new Invoice from UI. When I validated the invoice I got error like this.
That error happened because of inserting data is not only on partition table but also on master table when I used RETURN NEW in insert functions. So I added new functions and trigger to delete master data like that.
-- Trigger function to delete from the master table after the insert
CREATE OR REPLACE FUNCTION account_move_delete_master()
RETURNS trigger AS $$
BEGIN
DELETE FROM ONLY account_move where id = new.id;
RETURN NULL;
end;
$$
LANGUAGE plpgsql;
-- Create the after insert trigger
CREATE TRIGGER after_insert_account_move_trigger
AFTER INSERT ON account_move
FOR EACH ROW EXECUTE PROCEDURE account_move_delete_master();
-- Trigger function to delete from the master table after the insert
CREATE OR REPLACE FUNCTION account_move_line_delete_master()
RETURNS trigger AS $$
BEGIN
DELETE FROM ONLY account_move_line where id = new.id;
RETURN NULL;
end;
$$
LANGUAGE plpgsql;
-- Create the after insert trigger
CREATE TRIGGER after_insert_account_move_line_trigger
AFTER INSERT ON account_move_line
FOR EACH ROW EXECUTE PROCEDURE account_move_line_delete_master();
After I run these queries and validated again from UI. But I got another error like this.
What I found is the records is with the same id data in both master and partition table.
e.g. When I added new record, it inserted with index id:11 in partition table. Now what happened in master table is including with 2 records with id:11.
When I tried to delete master data with id, it may also be deleted from partition data.
So, if you have any ideas for that, please share with me. Thanks in advance.
