Postgres Partition Table Trigger inserting duplicate records

Question

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger

CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
 IF ( NEW.timedate >= '2015-01-01' AND NEW.timedate < '2015-01-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-02-01' AND NEW.timedate < '2015-02-28' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-03-01' AND NEW.timedate < '2015-03-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-04-01' AND NEW.timedate < '2015-04-30' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-05-01' AND NEW.timedate < '2015-05-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-06-01' AND NEW.timedate < '2015-06-30' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-07-01' AND NEW.timedate < '2015-07-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-08-01' AND NEW.timedate < '2015-08-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-09-01' AND NEW.timedate < '2015-09-30' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
 ELSIF ( NEW.timedate >= '2015-10-01' AND NEW.timedate < '2015-10-31' ) THEN
 INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
 ELSE
 RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
 END IF;
 RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trigger_SMS_RECEIPT_insert
 AFTER INSERT ON "SMS_RECEIPT"
 FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

If use Before in place of AFTER it gives the following error in java

 CREATE TRIGGER trigger_SMS_RECEIPT_insert
 BEFORE INSERT ON "SMS_RECEIPT"
 FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();
Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I also tried using INSTEAD OF inplace of AFTER and before but its giving

Tables cannot have INSTEAD OF triggers. I am new to table partitioning and using triggers. I have searched for all this in google and also gone through the post here: Insert trigger ends up inserting duplicate rows in partitioned table

But it didn't work for me.

Can anyone tell me how to resolve this.

• András Váczi: Could you tell if PostgreSQL itself complains when inserting a row?
• kirti: @dezso no it doesnot give problem in postgres but it gives in java when i make it before.it gives 0 rows affected and that creates the problem

Answer 1

You can (pick one)

1. Create a view and build on it the trigger with INSTEAD OF. Then you mae insert on the view, not on the table (you can have a trigger on the table raising an error if you want to be sure).
2. Do not make direct insert. Call a stored procedure and move logic there

More on trigger and where they can be applied here: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html