Re: [PATCHES] 64-bit CommandIds
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 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
Hans-Juergen Schoenig [EMAIL PROTECTED] 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 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
Tom Lane wrote: Hans-Juergen Schoenig [EMAIL PROTECTED] 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 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
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@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
Bruce Momjian [EMAIL PROTECTED] 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 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] 64-bit CommandIds
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] 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 (132ULL - 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 [EMAIL PROTECTED] and Hans-Jürgen Schönig [EMAIL PROTECTED] Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.orig/configure pgsql-cid64/configure *** pgsql.orig/configure 2008-03-02 13:44:42.0 +0100 --- pgsql-cid64/configure 2008-03-04 16:53:46.0 +0100 *** if test -n $ac_init_help; then *** 1349,1354 --- 1349,1355 Optional Features: --disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no) --enable-FEATURE[=ARG] include FEATURE [ARG=yes] + --enable-huge-commandidenable 64-bit CommandId support --enable-integer-datetimes enable 64-bit integer date/time support --enable-nls[=LANGUAGES] enable Native Language Support --disable-shareddo not build shared libraries *** fi *** 2175,2180 --- 2176,2219 # + # 64-bit CommandId + # + echo $as_me:$LINENO: checking whether to build with 64-bit CommandId support 5 + echo $ECHO_N checking whether to build with 64-bit CommandId support... $ECHO_C 6 + + pgac_args=$pgac_args enable_huge_commandid + + # Check whether --enable-huge-commandid or --disable-huge-commandid was given. + if test ${enable_huge_commandid+set} = set; then + enableval=$enable_huge_commandid + + case $enableval in + yes) + + cat confdefs.h \_ACEOF + #define USE_64BIT_COMMANDID 1 + _ACEOF + + ;; + no) + : + ;; + *) + { { echo $as_me:$LINENO: error: no argument expected for --enable-huge-commandid option 5 + echo $as_me: error: no argument expected for --enable-huge-commandid option 2;} +{ (exit 1); exit 1; }; } + ;; + esac + + else + enable_huge_commandid=no + + fi; + + echo $as_me:$LINENO: result: $enable_huge_commandid 5 + echo ${ECHO_T}$enable_huge_commandid 6 + + # # 64-bit integer date/time storage (--enable-integer-datetimes) # { echo $as_me:$LINENO: checking whether to build with 64-bit integer date/time support 5 diff -dcrpN pgsql.orig/configure.in pgsql-cid64/configure.in *** pgsql.orig/configure.in 2008-03-02 13:44:43.0 +0100 --- pgsql-cid64/configure.in 2008-03-04 16:53:46.0 +0100 *** PGAC_ARG_REQ(with, libs, [ --with- *** 128,133 --- 128,142 # + # 64-bit CommandId + # + AC_MSG_CHECKING([whether to build with 64-bit CommandId support]) + PGAC_ARG_BOOL(enable, huge-commandid, no, [ --enable-huge-commandidenable 64-bit CommandId support], + [AC_DEFINE([USE_64BIT_COMMANDID], 1, + [Define to 1 if you want 64-bit CommandId support. (--enable-huge-commandid)])]) + AC_MSG_RESULT([$enable_huge_commandid]) + + # # 64-bit integer date/time storage (--enable-integer-datetimes) # AC_MSG_CHECKING([whether to build with 64-bit integer date/time support]) diff -dcrpN pgsql.orig/doc/src/sgml/installation.sgml pgsql-cid64/doc/src/sgml/installation.sgml *** pgsql.orig/doc/src/sgml/installation.sgml 2008-02-18 13:49:58.0 +0100 --- pgsql-cid64/doc/src/sgml/installation.sgml 2008-03-04 17:16:14.0 +0100 *** su - postgres *** 1011,1016 --- 1011,1027 /varlistentry varlistentry +termoption--enable-huge-commandid/option/term +listitem + para + Use 64-bit CommandIds if you are planning to run transactions + consisting of more than 4 billion commands. This is off by default + to save disk space. + /para +/listitem + /varlistentry + + varlistentry termoption--enable-integer-datetimes/option/term listitem para diff -dcrpN pgsql.orig/src/backend/access/transam/xact.c pgsql-cid64/src/backend/access/transam/xact.c *** pgsql.orig/src/backend/access/transam/xact.c 2008-01-15 19:56:59.0 +0100 --- pgsql-cid64/src/backend/access/transam/xact.c 2008-03-04 16:57:54.0 +0100 *** CommandCounterIncrement(void) *** 592,598 --- 592,602 currentCommandId -= 1; ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), + #ifdef USE_64BIT_COMMANDID + errmsg(cannot have more than 2^64-1 commands in a transaction))); + #else errmsg(cannot have more than 2^32-1 commands in a transaction))); + #endif } currentCommandIdUsed = false; diff -dcrpN
Re: [PATCHES] 64-bit CommandIds
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 Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] 64-bit CommandIds
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/ -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] 64-bit CommandIds
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/ diff -dcrpN pgsql.orig/configure pgsql-cid64/configure *** pgsql.orig/configure 2008-03-02 13:44:42.0 +0100 --- pgsql-cid64/configure 2008-03-04 16:53:46.0 +0100 *** if test -n $ac_init_help; then *** 1349,1354 --- 1349,1355 Optional Features: --disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no) --enable-FEATURE[=ARG] include FEATURE [ARG=yes] + --enable-huge-commandidenable 64-bit CommandId support --enable-integer-datetimes enable 64-bit integer date/time support --enable-nls[=LANGUAGES] enable Native Language Support --disable-shareddo not build shared libraries *** fi *** 2175,2180 --- 2176,2219 # + # 64-bit CommandId + # + echo $as_me:$LINENO: checking whether to build with 64-bit CommandId support 5 + echo $ECHO_N checking whether to build with 64-bit CommandId support... $ECHO_C 6 + + pgac_args=$pgac_args enable_huge_commandid + + # Check whether --enable-huge-commandid or --disable-huge-commandid was given. + if test ${enable_huge_commandid+set} = set; then + enableval=$enable_huge_commandid + + case $enableval in + yes) + + cat confdefs.h \_ACEOF + #define USE_64BIT_COMMANDID 1 + _ACEOF + + ;; + no) + : + ;; + *) + { { echo $as_me:$LINENO: error: no argument expected for --enable-huge-commandid option 5 + echo $as_me: error: no argument expected for --enable-huge-commandid option 2;} +{ (exit 1); exit 1; }; } + ;; + esac + + else + enable_huge_commandid=no + + fi; + + echo $as_me:$LINENO: result: $enable_huge_commandid 5 + echo ${ECHO_T}$enable_huge_commandid 6 + + # # 64-bit integer date/time storage (--enable-integer-datetimes) # { echo $as_me:$LINENO: checking whether to build with 64-bit integer date/time support 5 diff -dcrpN pgsql.orig/configure.in pgsql-cid64/configure.in *** pgsql.orig/configure.in 2008-03-02 13:44:43.0 +0100 --- pgsql-cid64/configure.in 2008-03-04 16:53:46.0 +0100 *** PGAC_ARG_REQ(with, libs, [ --with- *** 128,133 --- 128,142 # + # 64-bit CommandId + # + AC_MSG_CHECKING([whether to build with 64-bit CommandId support]) + PGAC_ARG_BOOL(enable, huge-commandid, no, [ --enable-huge-commandidenable 64-bit CommandId support], + [AC_DEFINE([USE_64BIT_COMMANDID], 1, + [Define to 1 if you want 64-bit CommandId support. (--enable-huge-commandid)])]) + AC_MSG_RESULT([$enable_huge_commandid]) + + # # 64-bit integer date/time storage (--enable-integer-datetimes) # AC_MSG_CHECKING([whether to build with 64-bit integer date/time support]) diff -dcrpN pgsql.orig/doc/src/sgml/installation.sgml pgsql-cid64/doc/src/sgml/installation.sgml *** pgsql.orig/doc/src/sgml/installation.sgml 2008-02-18 13:49:58.0 +0100 --- pgsql-cid64/doc/src/sgml/installation.sgml 2008-03-04 17:16:14.0 +0100 *** su - postgres *** 1011,1016 --- 1011,1027 /varlistentry varlistentry +termoption--enable-huge-commandid/option/term +listitem + para + Use 64-bit CommandIds if you are planning to run transactions + consisting of more than 4 billion commands. This is off by default + to save disk space. + /para +/listitem + /varlistentry + + varlistentry termoption--enable-integer-datetimes/option/term listitem para diff -dcrpN pgsql.orig/src/backend/access/transam/xact.c pgsql-cid64/src/backend/access/transam/xact.c *** pgsql.orig/src/backend/access/transam/xact.c 2008-01-15 19:56:59.0 +0100 --- pgsql-cid64/src/backend/access/transam/xact.c 2008-03-04 16:57:54.0 +0100 *** CommandCounterIncrement(void) *** 592,598 --- 592,602 currentCommandId -= 1; ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), + #ifdef USE_64BIT_COMMANDID + errmsg(cannot have more than 2^64-1 commands in a transaction))); + #else errmsg(cannot have more than 2^32-1 commands in a transaction))); + #endif } currentCommandIdUsed = false; diff -dcrpN