Обсуждение: Re: [PERFORM] out of memory
> i have sql file (it's size are 1GB ) > when i execute it then the String is 987098801 bytr too long for encoding > conversion error occured . > pls give me solution about You hit the upper limit of internal memory allocation limit in PostgreSQL. IMO, there's no way to avoid the error except you use client encoding identical to backend. Hackers: The particular limit seem to be set considering TOAST(from include/utils/memutils.h): * XXX This is deliberately chosen to correspond to the limiting size * of varlena objects under TOAST. See VARSIZE_4B() and related macros * in postgres.h. Many datatypes assume that any allocatable size can * be represented in a varlena header. IMO the SQL string size limit is totally different from TOAST. Shouldn't we have different limit for SQL string? (MAX_CONVERSION_GROWTH is different story, of course) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Tue, Oct 30, 2012 at 6:08 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> i have sql file (it's size are 1GB ) >> when i execute it then the String is 987098801 bytr too long for encoding >> conversion error occured . >> pls give me solution about > > You hit the upper limit of internal memory allocation limit in > PostgreSQL. IMO, there's no way to avoid the error except you use > client encoding identical to backend. We recently had a customer who suffered a failed in pg_dump because the quadruple-allocation required by COPY OUT for an encoding conversion exceeded allocatable memory. I wonder whether it would be possible to rearrange things so that we can do a "streaming" encoding conversion. That is, if we have a large datum that we're trying to send back to the client, could we perhaps chop off the first 50MB or so, do the encoding on that amount of data, send the data to the client, lather, rinse, repeat? Your recent work to increase the maximum possible size of large objects (for which I thank you) seems like it could make these sorts of issues more common. As objects get larger, I don't think we can go on assuming that it's OK for peak memory utilization to keep hitting 5x or more. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 11/05/12 9:27 AM, Robert Haas wrote: > That is, if we have a large datum that we're trying to > send back to the client, could we perhaps chop off the first 50MB or > so, do the encoding on that amount of data, send the data to the > client, lather, rinse, repeat? I'd suggest work_mem sized chunks for this? -- john r pierce N 37, W 122 santa cruz ca mid-left coast