CODEWITHSUNDEEP
postgresql
sameer jewalikar
sameer jewalikar

Reputation: 21

How to commit processed data after catching exceptions in postgresql

I have around 400k members in source table & we want to process each member & insert to target table. We want to commit the processed member's data in target table until we get exception. //Code

CREATE OR REPLACE FUNCTION abc(
    )
    RETURNS integer
    LANGUAGE 'plpgsql'
declare
member_ct int;
PerMember1 cursor FOR select distinct member__c from source_table;
BEGIN
OPEN PerMember1;    --- Opening Cursors
LOOP
FETCH PerMember1 INTO c_member ;  --- Fetching Cursor Data into Variables
insert into target_table(member_c__c,dining_total__c,credit_card_total__c)
SELECT member__c,
(select COALESCE( NULLIF(sum(reward__c),0) , '0' ) FROM source_table where category__c='Dining' and member__c=c_member GROUP BY member__c,category__c),
(select COALESCE( NULLIF(sum(reward__c),0) , '0' ) FROM source_table where category__c='Credit Card' and member__c=c_member GROUP BY member__c,category__c)

END LOOP;
Close PerMember1;
RETURN 0;

exception when others then 


GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;

        RAISE INFO 'Error Name:%',SQLERRM;

        RAISE INFO 'Error State:%', SQLSTATE;

        RAISE INFO 'Error Context:%', err_context;

insert into error_log(procedure_name,error_code,error_message,last_modified_date,error_context)values('abc',SQLSTATE,SQLERRM,now(),err_context);

RETURN -1;

END;

$BODY$;

Currently if we call the stored proc by select abc(), it doesn't insert any records after getting exception for one member. We want to proceed with the remaining members after logging the exception data for that particular member. How can we commit processed members before catching exception and proceed further?

Upvotes: 1

Views: 2106

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

You may put the exception within another begin end inside the loop.

Commit cannot be done inside the function in Postgres

..
..
BEGIN --main begin
OPEN PerMember1;    --- Opening Cursors
LOOP
 BEGIN --begin within loop
FETCH PerMember1 INTO c_member ;  --- Fetching Cursor Data into Variables
  insert into target_table(member_c__c,dining_total__c,credit_card_total__c)
  SELECT member__c,
  (select COALESCE( NULLIF(sum(reward__c),0) , '0' ) FROM source_table
            where category__c='Dining' and member__c=c_member GROUP BY member__c,category__c),
  (select COALESCE( NULLIF(sum(reward__c),0) , '0' ) FROM source_table 
            where category__c='Credit Card' and member__c=c_member GROUP BY member__c,category__c);
  commit;
 EXCEPTION  --exception within loop
  WHEN OTHERS THEN

INSERT INTO error_log (
    procedure_name,
    error_code,
    error_message,
    last_modified_date,
    error_context
) VALUES (
    'abc',
    sqlstate,
    sqlerrm,
    now(),
    err_context
);
END; --end within loop
END LOOP;
..
..
END; --main end

Upvotes: 0

Maxim Kasyanov
Maxim Kasyanov

Reputation: 1058

Functions are part of the transaction they're called from. Their effects are rolled back if the transaction rolls back. Their work commits if the transaction commits. Any BEGIN ... EXCEPT blocks within the function operate like (and under the hood use) savepoints like the SAVEPOINT and ROLLBACK TO SAVEPOINT SQL statements.

The function either succeeds in its entirety or fails in its entirety, barring BEGIN ... EXCEPT error handling. If an error is raised within the function and not handled, the transaction calling the function is aborted. Aborted transactions cannot commit, and if they try to commit the COMMIT is treated as ROLLBACK, same as for any other transaction in error.

Try to user dblink. Dblink able to opens a persistent connection to a remote database and execute query. You can read more info postgres dbLink ref

Upvotes: 1

Related Questions