Re: [BUGS] server crash in very big transaction [postgresql

BEGIN;
...
...
...
END;

PANIC: invalid xlog record length 236052
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

=?UTF-8?Q?=E5=A7=9C?= =?UTF-8?Q?=E7=BB=B4?= <jiangwei_1976(at)yahoo(dot)com(dot)cn> writes:
> BEGIN;
> ...
> ...
> ...
> END;

> PANIC: invalid xlog record length 236052
> server closed the connection unexpectedly

This is quite unhelpful, if you're not going to show us what you did to
cause it.

regards, tom lane

On Sun, Aug 22, 2004 at 09:39:07AM +0800, ?????? wrote:
> BEGIN;
> ...
> ...
> ...
> END;
>
> PANIC: invalid xlog record length 236052

Huh, so what kind of operations did you execute within the transaction?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Sun, Aug 22, 2004 at 09:39:07AM +0800, ?????? wrote:
>> PANIC: invalid xlog record length 236052

> Huh, so what kind of operations did you execute within the transaction?

I found one possible explanation, though I don't know if it's the
submitter's problem or not. Create a SQL file that generates a whole
lot of subtransactions, like more than 16000. I used

begin;
create table foo(d1 int);
drop table foo;
savepoint x;
release x;
-- repeat above 2 lines 20000 times
commit;

First try gave me

WARNING: out of shared memory
ERROR: out of shared memory
HINT: You may need to increase max_locks_per_transaction.
WARNING: StartAbortedSubTransaction while in START state
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
... etc ...

which is fine except for the StartAbortedSubTransaction warning; that
may indicate a problem. (What do you think about it, Alvaro?)

I bumped up max_locks_per_transaction to 1000 and tried again, and got

psql:zzbig.sql:40004: PANIC: invalid xlog record length 80024
server closed the connection unexpectedly

What is happening of course is that more than 16K subtransaction IDs
won't fit in a commit record (since XLOG records have a 16-bit length
field). We're gonna have to rethink the representation of subxact
commit in XLOG.

regards, tom lane

I wrote:
> What is happening of course is that more than 16K subtransaction IDs
> won't fit in a commit record (since XLOG records have a 16-bit length
> field). We're gonna have to rethink the representation of subxact
> commit in XLOG.

After some further thought, I think there are basically two ways to
attack this:

1. Allow XLOG records to be larger than 64K.

2. Split transaction commit into multiple XLOG records when there are
many subtransactions.

#2 looks pretty painful because of the need to ensure that transaction
commit is still an atomic action. It's probably doable in principle
with something similar to the solution we are using for btree page split
logging (ie, record enough info so that the replay logic can complete
the commit even if the later records aren't recoverable from the log).
But I don't see all the details right off, and it sure seems risky.

I'm inclined to go with #1. There are various ways we could do it
but the most straightforward would be to just widen the xl_len field
to 32 bits. This would cost either 4 or 8 bytes per XLOG record
(because of MAXALIGN restrictions) but we could more than buy that back
by eliminating the xl_prev and/or xl_xact_prev fields, which have no use
in the current system. (They were intended to support UNDO but it seems
clear that we will never do that.)

Or we could assign an rmgr value to represent an "extension" record that
is to be merged with a following "normal" record. This is kinda klugy
but would avoid wasting bits on xl_len in the vast majority of records.
Also we'd not have to force an initdb since the file format would
remain upward-compatible.

Thoughts?

regards, tom lane

On Tue, 24 Aug 2004, Tom Lane wrote:

> I wrote:
> > What is happening of course is that more than 16K subtransaction IDs
> > won't fit in a commit record (since XLOG records have a 16-bit length
> > field). We're gonna have to rethink the representation of subxact
> > commit in XLOG.
>
> After some further thought, I think there are basically two ways to
> attack this:
>
> 1. Allow XLOG records to be larger than 64K.
>
> 2. Split transaction commit into multiple XLOG records when there are
> many subtransactions.
>

[snip]

> I'm inclined to go with #1. There are various ways we could do it
> but the most straightforward would be to just widen the xl_len field
> to 32 bits. This would cost either 4 or 8 bytes per XLOG record
> (because of MAXALIGN restrictions) but we could more than buy that back
> by eliminating the xl_prev and/or xl_xact_prev fields, which have no use
> in the current system. (They were intended to support UNDO but it seems
> clear that we will never do that.)

If we have to do an initdb for a subsequent beta, could we just remove
these anyway? By my count, we've got at least 16 bytes there.

As for extending the length of xl_len, what happens if someone now has
2^30 subtransaction IDs (as unlikely as that sounds)? What I mean is, it
would be good if we could detect this at a point when we can issue an
ERROR. If we go down this path, we should also document the maximum number
of sub transaction IDs which can be used within a single block so that
if/when people look at doing stuff on that scale are aware of the
limitations.

>
> Or we could assign an rmgr value to represent an "extension" record that
> is to be merged with a following "normal" record. This is kinda klugy
> but would avoid wasting bits on xl_len in the vast majority of records.
> Also we'd not have to force an initdb since the file format would
> remain upward-compatible.

This is a better idea, I think, as it avoids the problems above and, as
you say, will be binary compatible.

Gavin

On Tue, Aug 24, 2004 at 06:10:40PM -0400, Tom Lane wrote:

> WARNING: out of shared memory
> ERROR: out of shared memory
> HINT: You may need to increase max_locks_per_transaction.
> WARNING: StartAbortedSubTransaction while in START state
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> ERROR: current transaction is aborted, commands ignored until end of transaction block
> ... etc ...
>
> which is fine except for the StartAbortedSubTransaction warning; that
> may indicate a problem. (What do you think about it, Alvaro?)

I think the problem here is that we can't "safely" call
StartAbortedSubTransaction when in TRANS_START state. This is because
we could have some subsystems already initialized, and we will be
initializing them again. For example, AtSubStart_Memory() will be
called twice, which will lead to the loss of an (empty) memory context.
It doesn't seem a big problem, only a memory leak.

(We also lose a ResourceOwner, but since it doesn't own anything yet, it
isn't a problem.)

Fortunately I think we are good regarding other subsystems --- for
example if we happened to do something with sinval.c list-of-lists, it
could get out of sync with the transaction stack and it could get ugly.
But we don't.

I don't think we should get rid of the warning. It shows that there's a
problem, but it's not critical. We could set a flag indicating that
memory and resource owner are already initialized (another TRANS state?
a static bool?), but I don't know if it's worth the trouble.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)

On Wed, Aug 25, 2004 at 11:21:49AM +1000, Gavin Sherry wrote:
> On Tue, 24 Aug 2004, Tom Lane wrote:

> > 1. Allow XLOG records to be larger than 64K.
> >
> > 2. Split transaction commit into multiple XLOG records when there are
> > many subtransactions.
>
> [snip]
>
> > I'm inclined to go with #1. There are various ways we could do it
> > but the most straightforward would be to just widen the xl_len field
> > to 32 bits. This would cost either 4 or 8 bytes per XLOG record
> > (because of MAXALIGN restrictions) but we could more than buy that back
> > by eliminating the xl_prev and/or xl_xact_prev fields, which have no use
> > in the current system. (They were intended to support UNDO but it seems
> > clear that we will never do that.)

If we agree to never implement UNDO, there's a bunch of other code that
could be removed. Is there anyone that thinks we have any chance of not
doing it?

OTOH, if those fields are unused, we could just remove them for now in
any case. It's unlikely that there won't be a catalog update for some
other reason before someone implements UNDO anyway.

> As for extending the length of xl_len, what happens if someone now has
> 2^30 subtransaction IDs (as unlikely as that sounds)?

The commit xlog record also carries dropped table information, 12 bytes
apiece (on 32 bit machines?). It's unlikely that anyone will drop 2^13
tables on a single transaction, but it adds to the child xid list.

> > Or we could assign an rmgr value to represent an "extension" record that
> > is to be merged with a following "normal" record. This is kinda klugy
> > but would avoid wasting bits on xl_len in the vast majority of records.
> > Also we'd not have to force an initdb since the file format would
> > remain upward-compatible.
>
> This is a better idea, I think, as it avoids the problems above and, as
> you say, will be binary compatible.

I also think this is a good idea. Would it be generalized or only
applicable to xl_xact_{commit,abort} records?

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

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> As for extending the length of xl_len, what happens if someone now has
> 2^30 subtransaction IDs (as unlikely as that sounds)?

They'll have run out of RAM to store the subxact-related storage before
that (not to mention most likely have exhausted the CommandCounter
range, not to mention exhausted their patience --- it takes a good while
even to exercise the 2^16-subxact case). I'm satisfied if we can
approach that limit. Exceeding it will be a task for some other release.

regards, tom lane

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> If we agree to never implement UNDO, there's a bunch of other code that
> could be removed.

Yeah, I've been thinking of going around and cleaning out the deadwood,
but beta is not the time for it.

> The commit xlog record also carries dropped table information, 12 bytes
> apiece (on 32 bit machines?).

Good point --- someone will eventually hit that case too, if we don't
increase the XLOG record size limit.

>>> Or we could assign an rmgr value to represent an "extension" record that
>>> is to be merged with a following "normal" record.

> I also think this is a good idea. Would it be generalized or only
> applicable to xl_xact_{commit,abort} records?

I was envisioning it as a general mechanism --- I see no point in
restricting it to commit/abort records. If anything it would take extra
code to restrict it to that case ...

regards, tom lane

--- Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> wrote:

> On Sun, Aug 22, 2004 at 09:39:07AM +0800, ??????
> wrote:
> > BEGIN;
> > ...
> > ...
> > ...
> > END;
> >
> > PANIC: invalid xlog record length 236052
>
> Huh, so what kind of operations did you execute
> within the transaction?
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> Voy a acabar con todos los humanos / con los humanos
> yo acabar?> voy a acabar con todos / con todos los
humanos
> acabar?(Bender)
>
>

-------------------example 1--------------------
$ echo "BEGIN;" > backup.sql
$ pg_dump -o >> backup.sql
$ echo "END;" >> backup.sql
...
$ psql -f backup.sql

PANIC: invalid xlog record length 236052

----------------example 2 ------------------------

There are 1600 tables in database 'db1', I wrote a
pl/pgsql function "update_tables" like

"
FOR table IN SELECT relname FROM pg_class
LOOP
...
DROP INDEX ON ... ;
ALTER TABLE DROP CONSTRAINT ...;
...
CREATE INDEX xxx ON TABLE xxx;
...
ALTER TABLE xxx ADD PRIMARY KEY...
ALTER TABLE xxx ADD ...
...
END LOOP
...
"

$ select update_tables();

PANIC: invalid xlog record length 236052

__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

"ffffceffffac" "ffffbdffffaa" <jiangwei_1976(at)yahoo(dot)com(dot)cn> writes:
> --- Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> wrote:
>> Huh, so what kind of operations did you execute
>> within the transaction?

> There are 1600 tables in database 'db1', I wrote a
> pl/pgsql function "update_tables" like

> "
> FOR table IN SELECT relname FROM pg_class
> LOOP
> ...
> DROP INDEX ON ... ;
> ALTER TABLE DROP CONSTRAINT ...;
> ...
> CREATE INDEX xxx ON TABLE xxx;
> ...
> ALTER TABLE xxx ADD PRIMARY KEY...
> ALTER TABLE xxx ADD ...
> ...
> END LOOP

Okay, so it was the number-of-deleted-files issue and not the
number-of-subtransactions issue. Still says we have to allow
commit records to be bigger than 64K ...

regards, tom lane

Is this fixed?

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

Tom Lane wrote:
> "ffffceffffac" "ffffbdffffaa" <jiangwei_1976(at)yahoo(dot)com(dot)cn> writes:
> > --- Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> wrote:
> >> Huh, so what kind of operations did you execute
> >> within the transaction?
>
> > There are 1600 tables in database 'db1', I wrote a
> > pl/pgsql function "update_tables" like
>
> > "
> > FOR table IN SELECT relname FROM pg_class
> > LOOP
> > ...
> > DROP INDEX ON ... ;
> > ALTER TABLE DROP CONSTRAINT ...;
> > ...
> > CREATE INDEX xxx ON TABLE xxx;
> > ...
> > ALTER TABLE xxx ADD PRIMARY KEY...
> > ALTER TABLE xxx ADD ...
> > ...
> > END LOOP
>
> Okay, so it was the number-of-deleted-files issue and not the
> number-of-subtransactions issue. Still says we have to allow
> commit records to be bigger than 64K ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

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

Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
> On Wed, Aug 25, 2004 at 11:21:49AM +1000, Gavin Sherry wrote:
>> On Tue, 24 Aug 2004, Tom Lane wrote:
>>> Or we could assign an rmgr value to represent an "extension" record that
>>> is to be merged with a following "normal" record. This is kinda klugy
>>> but would avoid wasting bits on xl_len in the vast majority of records.
>>> Also we'd not have to force an initdb since the file format would
>>> remain upward-compatible.
>>
>> This is a better idea, I think, as it avoids the problems above and, as
>> you say, will be binary compatible.

> I also think this is a good idea. Would it be generalized or only
> applicable to xl_xact_{commit,abort} records?

After looking into this I've decided that it's not very practical --- it
would require major rewriting of XLogInsert, which I'm disinclined to do
at this stage of the beta cycle. Widening the xl_len field seems much
safer. It's not really an initdb-forcing change anyway; all you need to
do to upgrade an existing 8.0beta1 installation is run pg_resetxlog
(assuming you shut down the old postmaster cleanly).

regards, tom lane

This has just been fixed by Tom and will be in beta2.

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

Tom Lane wrote:
> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> > As for extending the length of xl_len, what happens if someone now has
> > 2^30 subtransaction IDs (as unlikely as that sounds)?
>
> They'll have run out of RAM to store the subxact-related storage before
> that (not to mention most likely have exhausted the CommandCounter
> range, not to mention exhausted their patience --- it takes a good while
> even to exercise the 2^16-subxact case). I'm satisfied if we can
> approach that limit. Exceeding it will be a task for some other release.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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