Thread: Using TEMP ON COMMIT DROP, but need logging too.
I've written a function that uses several temporary tables with the ON COMMIT DROP option. This function does no updates to the database. Now I would like to put in some logging. In otherwords:
insert into my_logging_table values (...);
commit;
insert into my_logging_table values (...);
commit;
Obviously I can't do the commit without dropping my temp tables.
What is a good way to implement my intended functionality?
Thanks, Mike
I've written a function that uses several temporary tables with the ON COMMIT DROP option. This function does no updates to the database. Now I would like to put in some logging. In otherwords:
insert into my_logging_table values (...);
commit;Obviously I can't do the commit without dropping my temp tables.What is a good way to implement my intended functionality?
You can't put "commit" inside a function so I feel like you are failing to share some important details by not providing code.
David J.
Thanks David. I didn't know that as I have only written retrieval functions as of now. I'm going back to do some due diligence.
On Thu, Oct 13, 2016 at 2:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I've written a function that uses several temporary tables with the ON COMMIT DROP option. This function does no updates to the database. Now I would like to put in some logging. In otherwords:
insert into my_logging_table values (...);
commit;Obviously I can't do the commit without dropping my temp tables.What is a good way to implement my intended functionality?You can't put "commit" inside a function so I feel like you are failing to share some important details by not providing code.David J.
> On 13 Oct 2016, at 23:24, Michael Moore <michaeljmoore@gmail.com> wrote: > ... > In otherwords: > > insert into my_logging_table values (...); > commit; > > Obviously I can't do the commit without dropping my temp tables. > > What is a good way to implement my intended functionality? If you are looking for something like "autonomous transactions” in Oracle’s pl/sql, using the new extension ‘pg_background’might help: http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html Let me also know, if you find a more elegant way. Cheers Benjamin
This is VERY different than Oracle, but I think I know how it works now. My pgplsql function is going to be called via JDBC. This function is a replacement for an Oracle TABLE function. In Oracle PL/SQL, all commits and rollbacks are handled within the TABLE function. This means that the JDBC layer never has to deal with the transaction; only the session. Our Java coders never do Commit or Rollback. Since, from the Java code, we are only changing the JDBC Driver (in order to migrate to Postgres), my new understanding is that the Java code will have to be responsible for try/catch(ing) any errors raised from pgplsql. The Java code must then decide weather a rollback or a commit is required. This will require a slight bit more coordination between the Java and pgplsql developer.
Have I got this right?
Have I got this right?
Mike
On Thu, Oct 13, 2016 at 2:53 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
Thanks David. I didn't know that as I have only written retrieval functions as of now. I'm going back to do some due diligence.On Thu, Oct 13, 2016 at 2:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:I've written a function that uses several temporary tables with the ON COMMIT DROP option. This function does no updates to the database. Now I would like to put in some logging. In otherwords:
insert into my_logging_table values (...);
commit;Obviously I can't do the commit without dropping my temp tables.What is a good way to implement my intended functionality?You can't put "commit" inside a function so I feel like you are failing to share some important details by not providing code.David J.
This is VERY different than Oracle, but I think I know how it works now. My pgplsql function is going to be called via JDBC. This function is a replacement for an Oracle TABLE function. In Oracle PL/SQL, all commits and rollbacks are handled within the TABLE function. This means that the JDBC layer never has to deal with the transaction; only the session. Our Java coders never do Commit or Rollback. Since, from the Java code, we are only changing the JDBC Driver (in order to migrate to Postgres), my new understanding is that the Java code will have to be responsible for try/catch(ing) any errors raised from pgplsql. The Java code must then decide weather a rollback or a commit is required. This will require a slight bit more coordination between the Java and pgplsql developer.
Have I got this right?
Yes, your application initiates and ends all transactions. The JDBC merely provides a client API you can leverage to perform your work - and handles all of the protocol details.
David J.
Thanks guys. Really appreciate it! Regards, Mike
On Fri, Oct 14, 2016 at 10:32 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
This is VERY different than Oracle, but I think I know how it works now. My pgplsql function is going to be called via JDBC. This function is a replacement for an Oracle TABLE function. In Oracle PL/SQL, all commits and rollbacks are handled within the TABLE function. This means that the JDBC layer never has to deal with the transaction; only the session. Our Java coders never do Commit or Rollback. Since, from the Java code, we are only changing the JDBC Driver (in order to migrate to Postgres), my new understanding is that the Java code will have to be responsible for try/catch(ing) any errors raised from pgplsql. The Java code must then decide weather a rollback or a commit is required. This will require a slight bit more coordination between the Java and pgplsql developer.
Have I got this right?Yes, your application initiates and ends all transactions. The JDBC merely provides a client API you can leverage to perform your work - and handles all of the protocol details.David J.