Re: [PATCHES] subtransactions -- storage manager

2004-04-30 Thread Simon Riggs
On Fri, 2004-04-30 at 01:02, Bruce Momjian wrote:
 Simon Riggs wrote:
  On Sun, 2004-04-25 at 19:06, Alvaro Herrera wrote:
   Hackers,
   
   This patch adds subtransaction support into the storage manager.  Files
   created or dropped inside a subtransaction are correctly dealt with at
   subtransaction commit or abort.
  
   - pg_clog/pg_subtrans.  Need a solution.
   
   
   PS: somehow I managed to get tired of the phrase nested transactions
   and I'm using the term subtransactions instead.  In my head they are
   the same thing ...
  
  Impressive.
  
  As you're aware, our current work overlaps.
  pg_clog doesn't seem like the place to record subtransactions, though
  maybe it is... could we not give subtransactions a txnid just as with
  flat transactions? That way we can record everything in pg_clog AND
  recovery will work without further modification - as long as the failure
  of a top level transaction causes failure of every subtransaction EVEN
  if the subtrans originally committed.
 
 The problem is we have to atomically mark multiple transactions as
 committed/aborted in pg_clog.  Each subtransaction does get its own xid,
 it is just that pg_subtrans maps each xid to is parent xid for use in
 atomically marking the xids as committed/aborted.
 
 Recovery via xlog should be fine.
 

Phewww!

  If you add pg_subtrans, you will need to make recovery work all over
  again...really, you don't want to be doing that, do you?
  
  I also have other questions
  Forgive my lack of attention: I want SAVEPOINTs, not subtransactions...
  how do we do those? 
 
 Savepoints are basically just a BEGIN at the save point, and a ROLLBACK
 to get you back to the saved spot.  It is just window-dressing on top of
 nested transactions.
 

OK, but is anybody working on the window dressing bit, so that we have
ANSI compliant commands?

Best Regards, Simon


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


Re: [PATCHES] subtransactions -- storage manager

2004-04-30 Thread Bruce Momjian
Simon Riggs wrote:
   If you add pg_subtrans, you will need to make recovery work all over
   again...really, you don't want to be doing that, do you?
   
   I also have other questions
   Forgive my lack of attention: I want SAVEPOINTs, not subtransactions...
   how do we do those? 
  
  Savepoints are basically just a BEGIN at the save point, and a ROLLBACK
  to get you back to the saved spot.  It is just window-dressing on top of
  nested transactions.
  
 
 OK, but is anybody working on the window dressing bit, so that we have
 ANSI compliant commands?

Not sure but that part should be easy once we have the stuff working.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] subtransactions -- storage manager

2004-04-30 Thread Alvaro Herrera
On Thu, Apr 29, 2004 at 11:38:52PM +0100, Simon Riggs wrote:
 On Sun, 2004-04-25 at 19:06, Alvaro Herrera wrote:

  - pg_clog/pg_subtrans.  Need a solution.

 As you're aware, our current work overlaps.
 pg_clog doesn't seem like the place to record subtransactions, though
 maybe it is... could we not give subtransactions a txnid just as with
 flat transactions? That way we can record everything in pg_clog AND
 recovery will work without further modification - as long as the failure
 of a top level transaction causes failure of every subtransaction EVEN
 if the subtrans originally committed.
 
 If you add pg_subtrans, you will need to make recovery work all over
 again...really, you don't want to be doing that, do you?

I'm not sure if I follow you.  I suppose you haven't read the previous
discussions on this issue.  pg_subtrans will have, for each Xid, the Xid
of its parent xact; if it's toplevel (as all xacts are implicitly in the
current implementation), it will have 0.  In pg_clog, a committed
subxact will be marked with 11; committed top-level xact will still be
10.  Aborted xact (toplevel and subxact) will have 01.

So whenever you see a xact with 10 in pg_clog, you know it committed.
Whenever you see 11, you know you have to fetch pg_subtrans and check
its parent (which could in turn be 11 so you have to recurse; or 10 so
you know it's committed; or 01 so you know if it's aborted; or 00 so you
know it's in progress).

After a suitable time (after the parent xact commits) the 11 can be
changed to 10.  

I don't think there's really a change in how recovery works.  There
will likely be more traffic to pg_xlog involving writes to pg_clog and
pg_subtrans but it shouldn't affect much, should it?

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo (Jaime Salinas)

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PATCHES] subtransactions -- storage manager

2004-04-29 Thread Simon Riggs
On Sun, 2004-04-25 at 19:06, Alvaro Herrera wrote:
 Hackers,
 
 This patch adds subtransaction support into the storage manager.  Files
 created or dropped inside a subtransaction are correctly dealt with at
 subtransaction commit or abort.

 - pg_clog/pg_subtrans.  Need a solution.
 
 
 PS: somehow I managed to get tired of the phrase nested transactions
 and I'm using the term subtransactions instead.  In my head they are
 the same thing ...

Impressive.

As you're aware, our current work overlaps.
pg_clog doesn't seem like the place to record subtransactions, though
maybe it is... could we not give subtransactions a txnid just as with
flat transactions? That way we can record everything in pg_clog AND
recovery will work without further modification - as long as the failure
of a top level transaction causes failure of every subtransaction EVEN
if the subtrans originally committed.

If you add pg_subtrans, you will need to make recovery work all over
again...really, you don't want to be doing that, do you?

I also have other questions
Forgive my lack of attention: I want SAVEPOINTs, not subtransactions...
how do we do those? 

My last focus on this was to do with SQL handling of transactional
rollback characteristics on error. PostgreSQL performs rollback on
complete txn when error occurs, rather than allowing statement level
abort and then retry...this was characterised as requiring nested
transactions...are your aware of this...is it on your roadmap.

Best Regards, Simon Riggs


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PATCHES] subtransactions -- storage manager

2004-04-29 Thread Bruce Momjian
Simon Riggs wrote:
 On Sun, 2004-04-25 at 19:06, Alvaro Herrera wrote:
  Hackers,
  
  This patch adds subtransaction support into the storage manager.  Files
  created or dropped inside a subtransaction are correctly dealt with at
  subtransaction commit or abort.
 
  - pg_clog/pg_subtrans.  Need a solution.
  
  
  PS: somehow I managed to get tired of the phrase nested transactions
  and I'm using the term subtransactions instead.  In my head they are
  the same thing ...
 
 Impressive.
 
 As you're aware, our current work overlaps.
 pg_clog doesn't seem like the place to record subtransactions, though
 maybe it is... could we not give subtransactions a txnid just as with
 flat transactions? That way we can record everything in pg_clog AND
 recovery will work without further modification - as long as the failure
 of a top level transaction causes failure of every subtransaction EVEN
 if the subtrans originally committed.

The problem is we have to atomically mark multiple transactions as
committed/aborted in pg_clog.  Each subtransaction does get its own xid,
it is just that pg_subtrans maps each xid to is parent xid for use in
atomically marking the xids as committed/aborted.

Recovery via xlog should be fine.

 If you add pg_subtrans, you will need to make recovery work all over
 again...really, you don't want to be doing that, do you?
 
 I also have other questions
 Forgive my lack of attention: I want SAVEPOINTs, not subtransactions...
 how do we do those? 

Savepoints are basically just a BEGIN at the save point, and a ROLLBACK
to get you back to the saved spot.  It is just window-dressing on top of
nested transactions.

 My last focus on this was to do with SQL handling of transactional
 rollback characteristics on error. PostgreSQL performs rollback on
 complete txn when error occurs, rather than allowing statement level
 abort and then retry...this was characterised as requiring nested
 transactions...are your aware of this...is it on your roadmap.

Yes, that is the whole point ---  to allow individual queries to fail
without rolling back the entire transaction.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PATCHES] subtransactions -- storage manager

2004-04-26 Thread Bruce Momjian

Alvaro, is this ready to be applied?

---

Alvaro Herrera wrote:
 Hackers,
 
 This patch adds subtransaction support into the storage manager.  Files
 created or dropped inside a subtransaction are correctly dealt with at
 subtransaction commit or abort.
 
 This works:
 create table foo (a int);
 create table foo2 (a int);
 begin;
   begin;
   create table bar (a int);
   select relfilenode, relname from pg_class where relname in ('foo', 
 'bar');
   drop table foo2;
   rollback;
   drop table foo;
   create table baz (a int);
   select relfilenode, relname from pg_class where relname='baz';
 commit;
 
 At this point, the files for bar and foo have disappeared, while
 foo2 and baz remain.  (Note however that the catalog entries are not
 correct -- this is because we don't have correctly recorded results in
 pg_clog.)
 
 While making this I realized I had made a mistake regarding portal
 memory, so I also correct it with this patch.  As a side effect, the
 following works;
 
 begin;
   begin;
   declare foo cursor for select 1;
   commit;
   begin;
   declare bar cursor for select 1;
   rollback;
   fetch all from foo; -- returns 1 row
   fetch all from bar; -- no such cursor
 rollback;
 
 (This patch will only apply cleanly with the previous patch applied.)
 
 
 
 Still missing:
 
 - support for prepared statements, async notifies.  Easy.
 - support for on commit actions.  Not sure.
 - support for deferred triggers.  Not so easy, maybe hard.
 - correct LWLock handling.  Should be easy (release them all on abort)
 - correct regular lock handling.  Not so easy.
 - pg_clog/pg_subtrans.  Need a solution.
 
 
 PS: somehow I managed to get tired of the phrase nested transactions
 and I'm using the term subtransactions instead.  In my head they are
 the same thing ...
 
 -- 
 Alvaro Herrera (alvherre[a]dcc.uchile.cl)
 Hi! I'm a .signature virus!
 cp me into your .signature file to help me spread!

[ Attachment, skipping... ]

 
 ---(end of broadcast)---
 TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly