Thread: Autonomous transaction

On Sun, Apr 4, 2010 at 10:26 AM, Loïc Vaumerel <shefla@gmail.com> wrote:
> Is there a way to use real and clean autonomous transactions in PostgreSQL
> yet ?

No.

> If no, is it planned to do so ? When ?

To my knowledge, no one is working on this.

...Robert

On Sun, Apr 4, 2010 at 10:26 AM, Loïc Vaumerel <shefla@gmail.com> wrote:
[...]
> All solutions I found are working the same way : they use dblink.
> I consider these solution more as handiwork than a clean solution.
> I am a little bit concerned about side effects as dblink were not intially
> designed for this.
>

the only side effect i can think of is that you will use another
connection slot (that's because dblink will stablish a new connection)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Loïc Vaumerel <shefla@gmail.com> writes:
> All solutions I found are working the same way : they use dblink.
> I consider these solution more as handiwork than a clean solution.
> I am a little bit concerned about side effects as dblink were not
> intially designed for this.

See plproxy which is designed for this kind of work. Or about…

> Is there a way to use real and clean autonomous transactions in
> PostgreSQL yet ?

None that I know of.

> If no, is it planned to do so ? When ?

We get demands quite often, it seems it's one of the "big tickets" we're
still missing. I don't remember any development effort proposal, though.

Regards,
--
dim

-----Original Message-----
From: Loïc Vaumerel [mailto:shefla@gmail.com]
Sent: Sunday, April 4, 2010 10:26 AM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] Autonomous transaction

Hi,

I have an application project based on a database.

I am really interested in using PostgreSQL.

I have only one issue, I want to use autonomous transactions to put in place a debug / logging functionality.

To do so, I insert messages in a "debug" table.

The problem is, if the main transaction / process rollback, my debug message insert will be rolled back too.

This is not the behavior I wish.

I need a functionality with the same behavior than the Oracle "PRAGMA AUTONOMOUS_TRANSACTION" one.

I have searched for it in the documentation and on the net, unfortunately nothing. (maybe I missed something)

I just found some posts regarding this :

http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php

https://labs.omniti.com/trac/pgtreats/browser/trunk/autonomous_logging_tool

... and some others ...

All solutions I found are working the same way : they use dblink.

I consider these solution more as handiwork than a clean solution.

I am a little bit concerned about side effects as dblink were not intially designed for this.

So my questions :

Is there a way to use real and clean autonomous transactions in PostgreSQL yet ?

If no, is it planned to do so ? When ?

Thanks in advance

Best regards

Shefla

pg@thetdh.com wrote:
>  It would be useful to have a relation such that all dirtied
>  buffers got written out even for failed transactions (barring
>  a crash) and such that read-any-undeleted were easy to do,
>  despite the non-ACIDity. The overhead of a side transaction
>  seems overkill for such things as logs or advisory relations,
>  and non-DB files would be harder to tie in efficiently to DB
>  activity. A side transaction would still have to be committed
>  in order to be useful; either you're committing frequently
>  (ouch!), or you risk failing to commit just as you would the
>  main transaction.

Yea, having some things in our system be non-transactional is odd and
hard to understand.  Just thinking about it, it seems it would introduce
all sorts of odd behaviors.

-- Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

On Tue, Apr 13, 2010 at 8:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Yea, having some things in our system be non-transactional is odd and
> hard to understand.  Just thinking about it, it seems it would introduce
> all sorts of odd behaviors.

I think it would be really useful, though, for users and maybe even
for system internals.  Working out the semantics is a challenge, but
not an insurmountable one, I think.

...Robert

Robert Haas wrote:
> On Tue, Apr 13, 2010 at 8:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Yea, having some things in our system be non-transactional is odd and
> > hard to understand. ?Just thinking about it, it seems it would introduce
> > all sorts of odd behaviors.
> 
> I think it would be really useful, though, for users and maybe even
> for system internals.  Working out the semantics is a challenge, but
> not an insurmountable one, I think.

Yea, it is going to feel like a ship with a leaky hull, so we are going
to have to re-think a bunch of stuff, like how do we handle visibility,
cleanout of old rows from UPDATE, etc.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com

On Tue, Apr 13, 2010 at 8:24 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> I think it would be really useful, though, for users and maybe even
>> for system internals.  Working out the semantics is a challenge, but
>> not an insurmountable one, I think.
>
> Yea, it is going to feel like a ship with a leaky hull, so we are going
> to have to re-think a bunch of stuff, like how do we handle visibility,
> cleanout of old rows from UPDATE, etc.

Yeah, agreed.

...Robert