SEO

"Joachim Wieland" <joe@mcknight.de> writes:
> There have been several reports that people could not vacuum any more or
> observed strange locks even after server restart. The reason was that they
> still had uncommitted prepared transactions around.

> I wonder if it could help to change the log level from
> ereport(LOG,
> (errmsg("recovering prepared transaction %u", xid)));
> to WARNING maybe in order to make that message more striking within the
> normal startup messages.

That doesn't seem like a good idea. In the first place, recovering
prepared xacts is exactly what system restart is *supposed* to do, and
so calling it a WARNING seems out of line. (I'm not real sure why that
message is even LOG level, rather than DEBUG1 or below.) In the second
place, this wouldn't help anyone unless they tried to fix their problem
by restarting the server --- a mentality suited only for Windows users,
and certainly not something a production system is going to do lightly
--- and then thought to look in the postmaster log, which the average
Windows user wouldn't.

I agree that there's a usability issue here though; I've been burnt by
forgotten prepared xacts myself (eg by control-C'ing pg_regress at just
the wrong time). Would it help if we included prepared xacts in the
pg_stat_activity view? Is there any other place we could make them
more visible during normal server operation?

regards, tom lane

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

On Fri, 2006-11-03 at 01:48 -0500, Tom Lane wrote:

> I agree that there's a usability issue here though; I've been burnt by
> forgotten prepared xacts myself (eg by control-C'ing pg_regress at just
> the wrong time). Would it help if we included prepared xacts in the
> pg_stat_activity view?
> Is there any other place we could make them
> more visible during normal server operation?

We only care when they break, otherwise its just situation normal, yes?

Is there a way to see prepared transactions where the original session
that prepared then has died? Perhaps the message at startup should be
"you have at least one prepared transaction that needs resolution". We
need something at that point, otherwise a PITR recovery is fairly likely
to contain them and we need to know that.

Otherwise on a system using prepared transactions heavily you may not
spot the odd or two that have crashed and need resolution. Presumably
they will effect oldestxmin, so its fairly important to be able to
resolve them in a timely manner or at least know they are there.

Not that I am advising their use though....

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Simon Riggs" <simon@2ndquadrant.com> writes:
> We only care when they break, otherwise its just situation normal, yes?

No, the trouble case is where the XA manager that owns the transaction
has forgotten about it.

> Is there a way to see prepared transactions where the original session
> that prepared then has died? Perhaps the message at startup should be
> "you have at least one prepared transaction that needs resolution".

I am completely baffled by this focus on database startup time. That's
not where the problem is.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Fri, 3 Nov 2006, Tom Lane wrote:
>
> > Is there a way to see prepared transactions where the original session
> > that prepared then has died? Perhaps the message at startup should be
> > "you have at least one prepared transaction that needs resolution".
>
> I am completely baffled by this focus on database startup time. That's
> not where the problem is.
>
> regards, tom lane
>

I'm not alluding to anyone in particular, just responding to the focus on
startup time; When I joined Ingres as a Consultant (back when that was a
revered job), we saw this a lot, too, bubbling through the ranks from
technical support. Engineering was having a cow over it. We Consultants
were expected to backline such problems and be the interface between
engineering and the rest of the world. What we found was that in what we'd
call the ligitimate cases, the cause for concern over startup time had to
do with bugs that forced, one way or another, a server restart.

Illigitimate cases - the VAST majority - were the result of, well, let's
call them less-than-successful DBAs, thrashing their installations with
their management breathing down their necks, often with flailing arms and
fire coming out of their mouths saying things like, "I bet my business on
this!"... The usual causes there were inappropriate configurations, and a
critical cause of _that_ was an instalation toolset that didn't help
people size/position things properly. Often a sales guy or trainee would
configure a test system and then the customer would put that into
production without ever reexamining the settings.

I realized there was an opportunity here; I put together a training
program and we sold it as a service along with installation to new
customers to help them get off on the right foot. Once we did that, new
customers were essentially put on notice that they could either pay us to
help set them up, or they could do it, but that continuing along with what
the salesman or junior techie had done wasn't sufficient for a production
environment that you could bet your business on. ...The complaint and
concern about startup time dropped out of sight nearly immediately...

Opportunity here, for PostgreSql: A Technical Document of some kind
entitled something like: "How to move your testing environment into
production."

No, unfortunately, I can't volunteer to be the point person on this one.
And to the underlying question: is this the case with PostgreSql? I can't
say...

Regards,
Richard


--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy@ScienceTools.com, http://ScienceTools.com/


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

Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
>> We only care when they break, otherwise its just situation normal, yes?
>
> No, the trouble case is where the XA manager that owns the transaction
> has forgotten about it.

Yeah, and there's no way the DBMS can detect that.

>> Is there a way to see prepared transactions where the original session
>> that prepared then has died? Perhaps the message at startup should be
>> "you have at least one prepared transaction that needs resolution".
>
> I am completely baffled by this focus on database startup time. That's
> not where the problem is.

Agreed. Though one way to have orphaned prepared transactions is to
recover from a PITR backup or bring a warm stand-by live. The
transaction manager might have committed a transaction after the backup
was taken. Recovering from the backup resurrects the transaction again
and the TM won't know about it.

The problem of orphaned transactions is most likely to occur on a
dev/test environment, where the TM is run on a developer's laptop and
might be killed and reinstalled or reconfigured at any time.

And unfortunately there's also a lot of broken TMs out there that don't
recover from crashes properly.

I think it's a good idea to at least LOG about prepared transactions at
startup. But it would be nice to also have a timeout, after which a big
fat WARNING would be printed. I don't believe in killing transactions
automatically though, that's a job for the administrator.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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