postgresql idle

[Alvaro Herrera]

Re: postgresql idle

On Thu, Apr 29, 2004 at 09:54:08PM -0400, Bruce Momjian wrote:
[color=blue]
> Tom Lane wrote:[color=green]
> > Andrew Sullivan <ajs@crankycanu ck.ca> writes:[color=darkred]
> > > On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
> > >> I find that some clients (DBVisualizer for one) do exactly that -
> > >> execute the COMMIT;BEGIN sequence, and leaves idle
> > >> transactions on a consistent basis.[/color]
> >[color=darkred]
> > > Almost all the things I've see that set the autocommit behaviour will
> > > also do this. I suspect it's a pretty common approach.[/color]
> >
> > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > side by postponing the actual transaction start until the first command
> > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > decided it wasn't quite trivial and I hadn't time to make it happen.
> > No one's gone back to work on it during the 7.5 cycle either.
> >
> > Right now I'm not wanting to touch that code since both Alvaro and the
> > 2PC guy have open patches against it...[/color][/color]

Actually, my patch is waiting for you to review it ;-) On the other
hand, since I'm already touching that code, maybe I can include it in my
patch. Or would you prefer to keep those things separate?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Alvaro Herrera]

Re: postgresql idle

On Thu, Apr 29, 2004 at 09:54:08PM -0400, Bruce Momjian wrote:
[color=blue]
> Tom Lane wrote:[color=green]
> > Andrew Sullivan <ajs@crankycanu ck.ca> writes:[color=darkred]
> > > On Thu, Apr 29, 2004 at 03:57:59PM -0400, Andrew Rawnsley wrote:
> > >> I find that some clients (DBVisualizer for one) do exactly that -
> > >> execute the COMMIT;BEGIN sequence, and leaves idle
> > >> transactions on a consistent basis.[/color]
> >[color=darkred]
> > > Almost all the things I've see that set the autocommit behaviour will
> > > also do this. I suspect it's a pretty common approach.[/color]
> >
> > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > side by postponing the actual transaction start until the first command
> > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > decided it wasn't quite trivial and I hadn't time to make it happen.
> > No one's gone back to work on it during the 7.5 cycle either.
> >
> > Right now I'm not wanting to touch that code since both Alvaro and the
> > 2PC guy have open patches against it...[/color][/color]

Actually, my patch is waiting for you to review it ;-) On the other
hand, since I'm already touching that code, maybe I can include it in my
patch. Or would you prefer to keep those things separate?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Bruce Momjian]

Re: postgresql idle

Alvaro Herrera wrote:[color=blue][color=green][color=darkred]
> > > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > > side by postponing the actual transaction start until the first command
> > > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > No one's gone back to work on it during the 7.5 cycle either.
> > >
> > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > 2PC guy have open patches against it...[/color][/color]
>
> Actually, my patch is waiting for you to review it ;-) On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch. Or would you prefer to keep those things separate?[/color]

Alvaro, can I ask what is left? I know you have pg_subtrans, but what
plans do you have to abort subtransactions and bring the system back to
the state before the subtransaction started?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruce Momjian]

Re: postgresql idle

Alvaro Herrera wrote:[color=blue][color=green][color=darkred]
> > > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > > side by postponing the actual transaction start until the first command
> > > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > No one's gone back to work on it during the 7.5 cycle either.
> > >
> > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > 2PC guy have open patches against it...[/color][/color]
>
> Actually, my patch is waiting for you to review it ;-) On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch. Or would you prefer to keep those things separate?[/color]

Alvaro, can I ask what is left? I know you have pg_subtrans, but what
plans do you have to abort subtransactions and bring the system back to
the state before the subtransaction started?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Alvaro Herrera]

Re: postgresql idle

On Thu, Apr 29, 2004 at 10:31:07PM -0400, Bruce Momjian wrote:[color=blue]
> Alvaro Herrera wrote:[color=green][color=darkred]
> > > > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > > > side by postponing the actual transaction start until the first command
> > > > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > > No one's gone back to work on it during the 7.5 cycle either.
> > > >
> > > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > > 2PC guy have open patches against it...[/color]
> >
> > Actually, my patch is waiting for you to review it ;-) On the other
> > hand, since I'm already touching that code, maybe I can include it in my
> > patch. Or would you prefer to keep those things separate?[/color]
>
> Alvaro, can I ask what is left?[/color]

Several things. I think I wrote them along with my previous patch. The
visibility rules and the pg_clog protocol are what comes to mind
immediately. This is the difficult part.
[color=blue]
> I know you have pg_subtrans, but what plans do you have to abort
> subtransactions and bring the system back to the state before the
> subtransaction started?[/color]

Some of those things are already in place. For example cursors are
closed/dropped, file deletions (DROP TABLE) no longer take place, file
creation is reverted, and the server is in a known state. Some things
are missing: how to deal with deferred triggers, prepared statements,
locks, on-commit actions.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

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

http://archives.postgresql.org

[Alvaro Herrera]

Re: postgresql idle

On Thu, Apr 29, 2004 at 10:31:07PM -0400, Bruce Momjian wrote:[color=blue]
> Alvaro Herrera wrote:[color=green][color=darkred]
> > > > Yeah. We agreed in principle awhile back to "fix" this on the backend
> > > > side by postponing the actual transaction start until the first command
> > > > after BEGIN. I looked at this just before 7.4 feature freeze, but
> > > > decided it wasn't quite trivial and I hadn't time to make it happen.
> > > > No one's gone back to work on it during the 7.5 cycle either.
> > > >
> > > > Right now I'm not wanting to touch that code since both Alvaro and the
> > > > 2PC guy have open patches against it...[/color]
> >
> > Actually, my patch is waiting for you to review it ;-) On the other
> > hand, since I'm already touching that code, maybe I can include it in my
> > patch. Or would you prefer to keep those things separate?[/color]
>
> Alvaro, can I ask what is left?[/color]

Several things. I think I wrote them along with my previous patch. The
visibility rules and the pg_clog protocol are what comes to mind
immediately. This is the difficult part.
[color=blue]
> I know you have pg_subtrans, but what plans do you have to abort
> subtransactions and bring the system back to the state before the
> subtransaction started?[/color]

Some of those things are already in place. For example cursors are
closed/dropped, file deletions (DROP TABLE) no longer take place, file
creation is reverted, and the server is in a known state. Some things
are missing: how to deal with deferred triggers, prepared statements,
locks, on-commit actions.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

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

http://archives.postgresql.org

[Kris Jurka]

Re: postgresql idle



On Thu, 29 Apr 2004, Andrew Rawnsley wrote:
[color=blue]
>
> I find that some clients (DBVisualizer for one) do exactly that -
> execute the COMMIT;BEGIN sequence, and leaves idle
> transactions on a consistent basis.
>[/color]

The 7.5 JDBC driver has been fixed to avoid this problem.

Kris Jurka


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

[Kris Jurka]

Re: postgresql idle



On Thu, 29 Apr 2004, Andrew Rawnsley wrote:
[color=blue]
>
> I find that some clients (DBVisualizer for one) do exactly that -
> execute the COMMIT;BEGIN sequence, and leaves idle
> transactions on a consistent basis.
>[/color]

The 7.5 JDBC driver has been fixed to avoid this problem.

Kris Jurka


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

[Tom Lane]

Re: postgresql idle

Alvaro Herrera <alvherre@dcc.u chile.cl> writes:[color=blue][color=green]
>> Yeah. We agreed in principle awhile back to "fix" this on the backend
>> side by postponing the actual transaction start until the first command
>> after BEGIN.[/color][/color]
[color=blue]
> Actually, my patch is waiting for you to review it ;-) On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch. Or would you prefer to keep those things separate?[/color]

I'd opt for keeping it separate I think ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Re: postgresql idle

Alvaro Herrera <alvherre@dcc.u chile.cl> writes:[color=blue][color=green]
>> Yeah. We agreed in principle awhile back to "fix" this on the backend
>> side by postponing the actual transaction start until the first command
>> after BEGIN.[/color][/color]
[color=blue]
> Actually, my patch is waiting for you to review it ;-) On the other
> hand, since I'm already touching that code, maybe I can include it in my
> patch. Or would you prefer to keep those things separate?[/color]

I'd opt for keeping it separate I think ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Re: postgresql idle

Alvaro Herrera <alvherre@dcc.u chile.cl> writes:[color=blue]
> Several things. I think I wrote them along with my previous patch. The
> visibility rules and the pg_clog protocol are what comes to mind
> immediately. This is the difficult part.[/color]

Difficult part? I think those are easy --- they are narrow and already
solved-in-principle problems. What I do not understand is how you are
going to handle error recovery and undo in general. Every single
backend module that has any at-abort or at-commit cleanup is going to
need work to extend its data structures to handle subtransactions .
That seems like a major mess :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: postgresql idle

Alvaro Herrera <alvherre@dcc.u chile.cl> writes:[color=blue]
> Several things. I think I wrote them along with my previous patch. The
> visibility rules and the pg_clog protocol are what comes to mind
> immediately. This is the difficult part.[/color]

Difficult part? I think those are easy --- they are narrow and already
solved-in-principle problems. What I do not understand is how you are
going to handle error recovery and undo in general. Every single
backend module that has any at-abort or at-commit cleanup is going to
need work to extend its data structures to handle subtransactions .
That seems like a major mess :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly