postgresql idle

[Jon Pastore]

I have a strange error.

when I do a ps aux I get:

postgres 15018 0.0 0.6 9192 7396 ? S 09:20 0:00 /usr/bin/perl
/www/nexum/cgi-bin/accounting/posting/automatic_posting
postgres 15019 0.0 0.3 25696 4176 ? S 09:20 0:00 postgres:
postgres nexum [local]: idle

a little bit about the system.

It's a Dell Power Edge 500 sc with 1G Mem and a PIII 1Ghz processor 40G HDD
running RHEL 3.0 with apache 2.0.46

This perl script is designed to handle payment posting for an application we
developed. It runs fine on our development server which is running apache
1.3.27 on ES 2.1

on the production server the script hangs and we see the above msg in "ps
aux"

we wrote a wrapper for the program that will exec it from the command line
and everything runs a-o-k

has anyone else encounted something like this? should I downgrade the
installation of apache?

the script is using Pg.pm and makes many database calls in side of a
transaction session. turning on max debug from postgresql has not yeilded
any errors and putting in our own debug statements to print to STDERR to
goto the apache log also yeilds nothing since we can't see anything until we
kill the process. then what it remembers gets dumped to the error log.

Jon Pastore RHCE, President
IDE Tech, Inc.
[phone] Office
[phone] Fax
Public Key: <http://www.idetech.net/keys/jpastore.asc>
http://www.idetech.net/keys/jpastore.asc

[Michael Chaney]

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:

This perl script is designed to handle payment posting for an application we
developed. It runs fine on our development server which is running apache
1.3.27 on ES 2.1

on the production server the script hangs and we see the above msg in "ps
aux"

we wrote a wrapper for the program that will exec it from the command line
and everything runs a-o-k

has anyone else encounted something like this? should I downgrade the
installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see
where it's hanging. That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
md******@michaelchaney.com
http://www.michaelchaney.com/

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

[Michael Chaney]

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:

This perl script is designed to handle payment posting for an application we
developed. It runs fine on our development server which is running apache
1.3.27 on ES 2.1

on the production server the script hangs and we see the above msg in "ps
aux"

we wrote a wrapper for the program that will exec it from the command line
and everything runs a-o-k

has anyone else encounted something like this? should I downgrade the
installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see
where it's hanging. That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
md******@michaelchaney.com
http://www.michaelchaney.com/

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

[Jon Pastore]

Thank you for your response...and you are correct that is something to be
concerned about but after much frustration we found that pgsql thought there
was a transaction in progress and was waiting for it to complete when in
fact the commit had already taken place.

What we ended up doing was after a commit or rollback we would

undef $main::conn;

Forcing it to reestablish a new connection for the next itteration of the
loop. This seemed to be an adequate solution but strangely we ran our test
and it failed as expected, implemented this change ran the test and it
worked, but then when we commented out the change and ran the test again and
it succeeded...so I have no idea.
Jon Pastore RHCE, President
IDE Tech, Inc.
[phone] Office
[phone] Fax
Public Key: http://www.idetech.net/keys/jpastore.asc

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Michael Chaney
Sent: Thursday, April 22, 2004 10:26 AM
To: pg***********@postgresql.org
Subject: Re: [GENERAL] postgresql idle

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:

This perl script is designed to handle payment posting for an
application we developed. It runs fine on our development server
which is running apache
1.3.27 on ES 2.1

on the production server the script hangs and we see the above msg in
"ps aux"

we wrote a wrapper for the program that will exec it from the command
line and everything runs a-o-k

has anyone else encounted something like this? should I downgrade the
installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see where
it's hanging. That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
md******@michaelchaney.com
http://www.michaelchaney.com/

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

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

[Jon Pastore]

Thank you for your response...and you are correct that is something to be
concerned about but after much frustration we found that pgsql thought there
was a transaction in progress and was waiting for it to complete when in
fact the commit had already taken place.

What we ended up doing was after a commit or rollback we would

undef $main::conn;

Forcing it to reestablish a new connection for the next itteration of the
loop. This seemed to be an adequate solution but strangely we ran our test
and it failed as expected, implemented this change ran the test and it
worked, but then when we commented out the change and ran the test again and
it succeeded...so I have no idea.
Jon Pastore RHCE, President
IDE Tech, Inc.
[phone] Office
[phone] Fax
Public Key: http://www.idetech.net/keys/jpastore.asc

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Michael Chaney
Sent: Thursday, April 22, 2004 10:26 AM
To: pg***********@postgresql.org
Subject: Re: [GENERAL] postgresql idle

On Wed, Apr 21, 2004 at 09:12:27PM -0400, Jon Pastore wrote:

This perl script is designed to handle payment posting for an
application we developed. It runs fine on our development server
which is running apache
1.3.27 on ES 2.1

on the production server the script hangs and we see the above msg in
"ps aux"

we wrote a wrapper for the program that will exec it from the command
line and everything runs a-o-k

has anyone else encounted something like this? should I downgrade the
installation of apache?

It's a Perl script, so why aren't you concerned about the Perl version?
If you can run it through the Perl debugger, you should be able to see where
it's hanging. That's probably your best course of action.
Barring that, start putting some printf's in there to see where it's
hanging.

Michael
--
Michael Darrin Chaney
md******@michaelchaney.com
http://www.michaelchaney.com/

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

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

[Andrew Sullivan]

On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:

pgsql thought there was a transaction in progress and was waiting
for it to complete when in fact the commit had already taken place.

Come again? That doesn't sound possible. What ismore likely is that
somebody issues COMMIT;BEGIN; all at once. You get one transaction
through, but you still end up idle in transaciton.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Sullivan]

On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:

pgsql thought there was a transaction in progress and was waiting
for it to complete when in fact the commit had already taken place.

Come again? That doesn't sound possible. What ismore likely is that
somebody issues COMMIT;BEGIN; all at once. You get one transaction
through, but you still end up idle in transaciton.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Rawnsley]

I find that some clients (DBVisualizer for one) do exactly that -
execute the COMMIT;BEGIN sequence, and leaves idle
transactions on a consistent basis.

On Apr 29, 2004, at 3:19 PM, Andrew Sullivan wrote:

On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:

pgsql thought there was a transaction in progress and was waiting
for it to complete when in fact the commit had already taken place.

Come again? That doesn't sound possible. What ismore likely is that
somebody issues COMMIT;BEGIN; all at once. You get one transaction
through, but you still end up idle in transaciton.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Rawnsley]

I find that some clients (DBVisualizer for one) do exactly that -
execute the COMMIT;BEGIN sequence, and leaves idle
transactions on a consistent basis.

On Apr 29, 2004, at 3:19 PM, Andrew Sullivan wrote:

On Thu, Apr 29, 2004 at 02:04:47PM -0400, Jon Pastore wrote:

pgsql thought there was a transaction in progress and was waiting
for it to complete when in fact the commit had already taken place.

Come again? That doesn't sound possible. What ismore likely is that
somebody issues COMMIT;BEGIN; all at once. You get one transaction
through, but you still end up idle in transaciton.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Sullivan]

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach. It has
burned us at work several times, and I knew in advance it was a
problem. I can't imagine what it's like for people who aren't aware
of it.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Sullivan]

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach. It has
burned us at work several times, and I knew in advance it was a
problem. I can't imagine what it's like for people who aren't aware
of it.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

regards, tom lane

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

[Tom Lane]

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

regards, tom lane

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

[Bruce Momjian]

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

regards, tom lane

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

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ 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

[Bruce Momjian]

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

regards, tom lane

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

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ 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

[Alvaro Herrera]

On Thu, Apr 29, 2004 at 09:54:08PM -0400, Bruce Momjian wrote:

Tom Lane wrote:

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

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 ma*******@postgresql.org

[Alvaro Herrera]

On Thu, Apr 29, 2004 at 09:54:08PM -0400, Bruce Momjian wrote:

Tom Lane wrote:

Andrew Sullivan <aj*@crankycanuck.ca> writes:

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.

Almost all the things I've see that set the autocommit behaviour will
also do this. I suspect it's a pretty common approach.

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...

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 ma*******@postgresql.org

[Bruce Momjian]

Alvaro Herrera wrote:

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...

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, 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
pg***@candle.pha.pa.us | [phone]
+ 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]

Alvaro Herrera wrote:

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...

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, 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
pg***@candle.pha.pa.us | [phone]
+ 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]

On Thu, Apr 29, 2004 at 10:31:07PM -0400, Bruce Momjian wrote:

Alvaro Herrera wrote:

> 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...
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, can I ask what is left?

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.
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?

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]

On Thu, Apr 29, 2004 at 10:31:07PM -0400, Bruce Momjian wrote:

Alvaro Herrera wrote:

> 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...
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, can I ask what is left?

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.
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?

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]

On Thu, 29 Apr 2004, 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.

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]

On Thu, 29 Apr 2004, 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.

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]

Alvaro Herrera <al******@dcc.uchile.cl> writes:

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.

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?

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]

Alvaro Herrera <al******@dcc.uchile.cl> writes:

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.

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?

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]

Alvaro Herrera <al******@dcc.uchile.cl> writes:

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.

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Alvaro Herrera <al******@dcc.uchile.cl> writes:

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.

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly