Re: [HACKERS] support for hyper-long transactions ...
AFAICS, maximum number of command ids is actually 2^32-1, or over 4 billion. Are you sure you bumped into that limit and not something else? What's the error message you're getting? What version of Postgres is this? PG 8.3 will have another related limit on the number of combocids you can have. it is clearly caused by the CommandCounter - it is indicated by the error message. i don't have the message on my notebook here but this is exactly what is going on if we run out of statements. i would not see atomic as a problem here as we can support it for 64 bit boxes only. i would vote for some autoconf flag which is off by default to make sure that other applications don't waste space here. to answer the question you had before: it is an application going through some enormous amount of raw data and trying to do some filtering, analysis and preaggregation (which is not an issue here). the thing is that filtering and analysis are quite complex and have to be done on a per entry level (a lot of conditional lookups, if statements, custom aggregated, status changed and so on). if you are forced to do this fancy logic for 1xx mio records you can easily run out of commands. many thanks, hans -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at
Re: [HACKERS] support for hyper-long transactions ...
Heikki Linnakangas írta: Hans-Juergen Schoenig wrote: at the moment i am working on an application which is supposed to run extremely large transactions (a lot of server side stored procedure stuff which can hardly be split into small transactions for visibility reasons). so, from time to time it happens that i exceed my CommandCounter (> 2.000.000.000 statements inside the same transaction). Interesting. What kind of a stored procedure is that? One optimization that might be possible is to not increment it for statements that do on-disk changes. my idea is: how about adding a configure option to compile postgres with a 64 bit command counter. this would allow larger transactions for special purpose applications while it would not have an impact on normal applications. One objection to expanding TransactionId to 64-bits has been that we depend on assignment of TransactionId to be atomic. That might not be an issue with CommandIds; I don't think we store commandids in shared memory structures. It still doesn't feel like a good idea to me, though. Isn't 64-bit assignment atomic on 64-bit machines? With a little autoconf magic, the conditional can be disabled for 32-bit platforms. So that's not a real obstacle for going to 64-bit TransactionIds. -- -- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] support for hyper-long transactions ...
Hans-Juergen Schoenig wrote: > at the moment i am working on an application which is supposed to run > extremely large transactions (a lot of server side stored procedure > stuff which can hardly be split into small transactions for visibility > reasons). > so, from time to time it happens that i exceed my CommandCounter (> > 2.000.000.000 statements inside the same transaction). AFAICS, maximum number of command ids is actually 2^32-1, or over 4 billion. Are you sure you bumped into that limit and not something else? What's the error message you're getting? What version of Postgres is this? PG 8.3 will have another related limit on the number of combocids you can have. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] support for hyper-long transactions ...
Hans-Juergen Schoenig wrote: > at the moment i am working on an application which is supposed to run > extremely large transactions (a lot of server side stored procedure > stuff which can hardly be split into small transactions for visibility > reasons). > so, from time to time it happens that i exceed my CommandCounter (> > 2.000.000.000 statements inside the same transaction). Interesting. What kind of a stored procedure is that? One optimization that might be possible is to not increment it for statements that do on-disk changes. > my idea is: how about adding a configure option to compile postgres with > a 64 bit command counter. this would allow larger transactions for > special purpose applications while it would not have an impact on normal > applications. One objection to expanding TransactionId to 64-bits has been that we depend on assignment of TransactionId to be atomic. That might not be an issue with CommandIds; I don't think we store commandids in shared memory structures. It still doesn't feel like a good idea to me, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] support for hyper-long transactions ...
at the moment i am working on an application which is supposed to run extremely large transactions (a lot of server side stored procedure stuff which can hardly be split into small transactions for visibility reasons). so, from time to time it happens that i exceed my CommandCounter (> 2.000.000.000 statements inside the same transaction). my idea is: how about adding a configure option to compile postgres with a 64 bit command counter. this would allow larger transactions for special purpose applications while it would not have an impact on normal applications. comments are welcome ... best regards, hans -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at