Skip to main content

:: How Commit Works in PL SQL Functions/Triggers ::

COMMIT in Functions/Triggers.
+++++++++++++++++++

It Triggers/functions may be led to bulk associated transactions. This may lead to transaction atomicity failure.

Please find the below test case from Oracle.

Step 1:
---------

SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE1(T INT);

Table created.

SQL> CREATE TABLE COMMIT_TRIG_TEST_TABLE2(T INT,CHECK (T NOT IN(0)));

Table created.

Step 2:
---------
CREATE OR REPLACE FUNCTION INSERT_DATA(NUM IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO COMMIT_TRIG_TEST_TABLE1 VALUES('||NUM||')';
RETURN 'INSERT OK';
END;

Step 3:
---------
CREATE OR REPLACE TRIGGER COMMIT_TRIG
BEFORE INSERT ON COMMIT_TRIG_TEST_TABLE1
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO COMMIT_TRIG_TEST_TABLE2 VALUES(:NEW.T);
END;

Step 4:
--------
However, we know that if the function having dml operations, then we can't make that function as a part of SQL statement. So, please use the below method for inserting

the data.

SQL> VAR V VARCHAR2(10);
SQL> EXEC :V :=INSERT_DATA(10);

PL/SQL procedure successfully completed.

SQL> PRINT V;

V
------------------
INSERT OK

Please observer this statement in INSERT_DATA function .

This insert statement is associated with two transactions.

1. By itself.

2.INSERT INTO COMMIT_TRIG_TEST_TABLE2 VALUES:

InTrigger Body Asper Atomocity(ACID), whether the transaction must Commit/Rollback.


+INSERT ON TABLE1 (Success)+ }

==> TRANSACTION is sucess.

+INSERT ON TABLE2 (Success)+ }


SQL> SELECT * FROM COMMIT_TRIG_TEST_TABLE1;

T
----------
10

SQL> SELECT * FROM COMMIT_TRIG_TEST_TABLE2;

T
----------
10


That's why this EXEC :V :=INSERT_DATA(10) is sucessfull.

Step 5
--------

SQL> DELETE FROM COMMIT_TRIG_TEST_TABLE1;

SQL> DELETE FROM COMMIT_TRIG_TEST_TABLE2;

SQL> exec :v :=insert_data(0);
BEGIN :v :=insert_data(0); END;
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C009672) violated
ORA-06512: at "SCOTT.COMMIT_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.COMMIT_TRIG'
ORA-06512: at "SCOTT.INSERT_DATA", line 4
ORA-06512: at line 1

Here the transactions are as below

+INSERT ON TABLE1 (Success)+}

==> TRANSACTION is failure.

+INSERT ON TABLE2 (Failure)+}

As per Automocity rule, whether the transaction must be sucess/fail. However, here the transaction is having both.

That's why the total transaction is rolled back. So, INSERT ON TABLE1 also rolled back here.

SQL> SELECT * FROM COMMIT_TRIG_TEST_TABLE1;

no rows selected

SQL> SELECT * FROM COMMIT_TRIG_TEST_TABLE2;

no rows selected

Even though if you have autonomous_transactions, those must and should statisfy this automoctiy.


Now, how to insert element '0' in first table.

Step 6
--------

CREATE OR REPLACE FUNCTION INSERT_DATA(NUM IN NUMBER)
RETURN VARCHAR2 IS
pragma autonomous_transaction;
BEGIN
INSERT INTO COMMIT_TRIG_TEST_TABLE1 VALUES(NUM);
COMMIT;
RETURN 'INSERT OK';
END;

Need to make the trigger always commits.Even though it fails, it needs to commit for satisfying the automocity.

CREATE OR REPLACE TRIGGER COMMIT_TRIG
BEFORE INSERT ON COMMIT_TRIG_TEST_TABLE1
FOR EACH ROW
DECLARE
pragma autonomous_transaction;
BEGIN
INSERT INTO COMMIT_TRIG_TEST_TABLE2 VALUES(:NEW.T);
commit;
exception when others then
COMMIT;
END;

SQL> select insert_data(0) from dual;

INSERT_DATA(0)
--------------------------------------------------------------------------------
INSERT OK


SQL> select * from commit_trig_test_table1;

T
----------
0

SQL> select * from commit_trig_test_table2;

no rows selected


--Dinesh

Comments

Popular posts from this blog

Pgpool Configuration & Failback

I would like to share the pgpool configuration, and it's failback mechanism in this post.

Hope it will be helpful to you in creating pgpool and it's failback setup.

Pgpool Installation & Configuration

1. Download the pgpool from below link(Latest version is 3.2.1).
    http://www.pgpool.net/mediawiki/index.php/Downloads


2. Untart the pgpool-II-3.2.1.tar.gz and goto pgpool-II-3.2.1 directory.

3. Install the pgpool by executing the below commands:

./configure ­­prefix=/opt/PostgreSQL92/ ­­--with­-pgsql­-includedir=/opt/PostgreSQL92/include/ --with­-pgsql­-libdir=/opt/PostgreSQL92/lib/ make make install 4. You can see the pgpool files in /opt/PostgreSQL92/bin location.
/opt/PostgreSQL92/bin $ ls clusterdb   droplang  pcp_attach_node  pcp_proc_count pcp_systemdb_info  pg_controldata  pgpool pg_test_fsync pltcl_loadmod  reindexdb createdb    dropuser  pcp_detach_node  pcp_proc_info createlang  ecpg      pcp_node_count   pcp_promote_node oid2name  pcp_pool_status  pcp_stop_pgpool  …

pgBucket - A new concurrent job scheduler

Hi All,

I'm so excited to announce about my first contribution tool for postgresql. I have been working with PostgreSQL from 2011 and I'm really impressed with such a nice database.

I started few projects in last 2 years like pgHawk[A beautiful report generator for Openwatch] , pgOwlt [CUI monitoring. It is still under development, incase you are interested to see what it is, attaching the image here for you ],


pgBucket [Which I'm gonna talk about] and learned a lot and lot about PostgreSQL/Linux internals.

Using pgBucket we can schedule jobs easily and we can also maintain them using it's CLI options. We can update/insert/delete jobs at online. And here is its architecture which gives you a basic idea about how it works.


Yeah, I know there are other good job schedulers available for PostgreSQL. I haven't tested them and not comparing them with this, as I implemented it in my way.
Features are: OS/DB jobsCron style sytaxOnline job modificationsRequired cli options

N-Node Mutlimaster Replication With Bucardo...!

Our team recently got  a problem, which is to solve the N-Node multi master replication in PostgreSQL.

We all know that, there are some other db engines like Postgres-XC which works in this way. But, we don't have any tool available in PostgreSQL, except Bucardo.

Bucardo is the nice solution for 2-Nodes. Is there a way we can exceed this limitation from 2 to N..?

As an initial step on this, I have done with 3 Nodes, which I believe, we can extend this upto N. { I might be wrong here.}

Please follow the below steps to set up the 1 - 1 multi master replication.

1. Follow the below steps to get all the pre-requisites for the Bucardo.

yum install perl-DBIx-Safe or apt-get install libdbix-safe-perl Install the below components from CPAN. DBI DBD::Pg Test::Simple boolean (Bucardo 5.0 and higher) Download the latest tarball from here. tar xvfz Bucardo-4.4.8.tar.gz cd Bucardo-4.4.8 perl Makefile.PL make sudo make install 2. We need to create plperl extension in db. For this, download…