Re: 64-bit CommandIds

Hi,

attached is our patch against HEAD which enables extending CommandIds
to 64-bit. This is for enabling long transactions that really do that much
non-read-only work in one transaction.

The feature is off by default, you need to --enable-huge-commandid.
It fails only one regression test (without_oid) that measures the saved
space in 8.3.

Also, modifying FirstCommandId to be (1<<32ULL - 4) to early overflow
the 32-bit limit) doesn't show any real problem besides the combocid
regression failure that explicitly lists cmin/cmax values, which is
expected.

It was written by Zoltán Böszörményi <zb(at)cybertec(dot)at> and
Hans-Jürgen Schönig <hs(at)cybertec(dot)at>

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Zoltan Boszormenyi wrote:

> attached is our patch against HEAD which enables extending CommandIds
> to 64-bit. This is for enabling long transactions that really do that much
> non-read-only work in one transaction.

I think you should add a pg_control field and corresponding check, to
avoid a 64bit-Cid postmaster to start on a 32bit-Cid data area and vice
versa.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera írta:
> Zoltan Boszormenyi wrote:
>
>
>> attached is our patch against HEAD which enables extending CommandIds
>> to 64-bit. This is for enabling long transactions that really do that much
>> non-read-only work in one transaction.
>>
>
> I think you should add a pg_control field and corresponding check, to
> avoid a 64bit-Cid postmaster to start on a 32bit-Cid data area and vice
> versa.
>

You're right, thanks.

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Alvaro Herrera írta:
> Zoltan Boszormenyi wrote:
>
>
>> attached is our patch against HEAD which enables extending CommandIds
>> to 64-bit. This is for enabling long transactions that really do that much
>> non-read-only work in one transaction.
>>
>
> I think you should add a pg_control field and corresponding check, to
> avoid a 64bit-Cid postmaster to start on a 32bit-Cid data area and vice
> versa.
>

I added the check but I needed to add it BEFORE checking for
toast_max_chunk_size otherwise it complained about this more
cryptic problem. I think it's cleaner to report this failure to know
why toast_max_chunk_size != TOAST_MAX_CHUNK_SIZE.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

So, is this an option we want for configure?

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

Zoltan Boszormenyi wrote:
> Alvaro Herrera ?rta:
> > Zoltan Boszormenyi wrote:
> >
> >
> >> attached is our patch against HEAD which enables extending CommandIds
> >> to 64-bit. This is for enabling long transactions that really do that much
> >> non-read-only work in one transaction.
> >>
> >
> > I think you should add a pg_control field and corresponding check, to
> > avoid a 64bit-Cid postmaster to start on a 32bit-Cid data area and vice
> > versa.
> >
>
> I added the check but I needed to add it BEFORE checking for
> toast_max_chunk_size otherwise it complained about this more
> cryptic problem. I think it's cleaner to report this failure to know
> why toast_max_chunk_size != TOAST_MAX_CHUNK_SIZE.
>
> Best regards,
> Zolt?n B?sz?rm?nyi
>
> --
> ----------------------------------
> Zolt?n B?sz?rm?nyi
> Cybertec Sch?nig & Sch?nig GmbH
> http://www.postgresql.at/
>

>
> --
> Sent via pgsql-patches mailing list (pgsql-patches(at)postgresql(dot)org)
> To make changes to your Subscription:
> http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-patches

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> So, is this an option we want for configure?

I think the case for it got a whole lot weaker in 8.3, with lazy
consumption of CIDs. If someone had tables big enough to make the
32-bit-CID limit still be a problem despite that fix, I'd think they'd
not be very happy about adding another 4 bytes of tuple header overhead
(or more likely 8 bytes, on the kind of machine where this patch would
make some sense). I don't foresee many people paying that cost rather
than breaking up their transactions.

My feeling is we should avoid the extra complexity, at least till such
time as we see whether there are still any real field complaints about
this with 8.3.

In any case the patch is broken by --enable-float8-byval, and would
need some adjustments to play nice with that.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > So, is this an option we want for configure?
>
> I think the case for it got a whole lot weaker in 8.3, with lazy
> consumption of CIDs. If someone had tables big enough to make the
> 32-bit-CID limit still be a problem despite that fix, I'd think they'd
> not be very happy about adding another 4 bytes of tuple header overhead
> (or more likely 8 bytes, on the kind of machine where this patch would
> make some sense). I don't foresee many people paying that cost rather
> than breaking up their transactions.
>
> My feeling is we should avoid the extra complexity, at least till such
> time as we see whether there are still any real field complaints about
> this with 8.3.
>
> In any case the patch is broken by --enable-float8-byval, and would
> need some adjustments to play nice with that.

Agreed. Let's see if we get requests for it in >= 8.3 releases.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

> > I think the case for it got a whole lot weaker in 8.3, with lazy
> > consumption of CIDs.
>
> Agreed. Let's see if we get requests for it in >= 8.3 releases.

In the original submission message you find this text:

: attached is our patch against HEAD which enables extending CommandIds
: to 64-bit. This is for enabling long transactions that really do that
: much non-read-only work in one transaction.

Question for Hans-Juergen and Zoltan: have you tested 8.3 and do you
still see the need for this?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
>
>>> I think the case for it got a whole lot weaker in 8.3, with lazy
>>> consumption of CIDs.
>>>
>> Agreed. Let's see if we get requests for it in >= 8.3 releases.
>>
>
> In the original submission message you find this text:
>
> : attached is our patch against HEAD which enables extending CommandIds
> : to 64-bit. This is for enabling long transactions that really do that
> : much non-read-only work in one transaction.
>
> Question for Hans-Juergen and Zoltan: have you tested 8.3 and do you
> still see the need for this?
>
>

good morning,

i have seen this problem two or three times within the past 2-3 years or
so. so, it can basically happen in the field for some special purpose
applications but i don't see this as an every day problem. it would be
nice to have it in.
we could also go for some special contrib module which could contain a
patch along with some documentation but i am not quite sure how this
fits in there.
we would of course maintain the patch.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

Hans-Juergen Schoenig <hs(at)cybertec(dot)at> writes:
> Alvaro Herrera wrote:
>> Question for Hans-Juergen and Zoltan: have you tested 8.3 and do you
>> still see the need for this?

> i have seen this problem two or three times within the past 2-3 years or
> so. so, it can basically happen in the field for some special purpose
> applications but i don't see this as an every day problem. it would be
> nice to have it in.

So these experiences were pre-8.3, right?

The reason that I'm harping on that is that plpgsql does a
CommandCounterIncrement for each expression it evaluates, whether
or not there's any visible database access. As of 8.3 that won't
cause consumption of CIDs, but before it did. I suspect that in a
lot of real-world scenarios, CID consumption from triggers will be
down by an order of magnitude.

regards, tom lane

Tom Lane wrote:
> Hans-Juergen Schoenig <hs(at)cybertec(dot)at> writes:
>
>> Alvaro Herrera wrote:
>>
>>> Question for Hans-Juergen and Zoltan: have you tested 8.3 and do you
>>> still see the need for this?
>>>
>
>
>> i have seen this problem two or three times within the past 2-3 years or
>> so. so, it can basically happen in the field for some special purpose
>> applications but i don't see this as an every day problem. it would be
>> nice to have it in.
>>
>
> So these experiences were pre-8.3, right?
>
> The reason that I'm harping on that is that plpgsql does a
> CommandCounterIncrement for each expression it evaluates, whether
> or not there's any visible database access. As of 8.3 that won't
> cause consumption of CIDs, but before it did. I suspect that in a
> lot of real-world scenarios, CID consumption from triggers will be
> down by an order of magnitude.
>
> regards, tom lane
>

we found those problems when we dealt with stored procedures basically
(during huge analysis and data modification transactions - rollup and
materialize stuff, basically). i would think as well that it should have
improved a lot.

i have not tested with 8.3, however.
on 8.2 it took something like 18 hours to reach that threshold - just to
give you an impression.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com