Thread: subtransactions -- storage manager
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
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
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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
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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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
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 pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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)