Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmI9B-0000FL-69 for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 17:51:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmI9A-0004aU-1j for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 17:51:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmI99-0004aL-Kj for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 17:51:11 +0000 Received: from mail-pl1-x630.google.com ([2607:f8b0:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmI92-002H7U-An for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 17:51:10 +0000 Received: by mail-pl1-x630.google.com with SMTP id 20so10422393plk.10 for ; Tue, 11 Apr 2023 10:51:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1681235462; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=A0Vf9CsioDPUIy/35X2GZQaeu4eRriiCtifiBdf8Iqo=; b=hQ25o010DIdMKrsH9weq+Ycupi1boFsCr42IiGnpuv8WJQu/+QVTKeNq0kQ9oy3CzK 4unWO5IK/MZ+/+jtf7idj3Ts9G5nDUhTCSiEFI+6zPUtwJ+tfEiYqfwoYCNFSwxX9zJT GLIjwVQRmd6ClMKTYUqfyWdEBbaXEbugM5j5k60oheCUfv+F6u++8uX/A4xkFFoZGiYp Z4Y/efTWEMTUW8vReCSOYCKNqs2GKnUJ4fBwedQjaEBcyx3nWUFZntD+8g49lRruGAfP JgpRzi9J+osdEp2aFNO4o1rWnakjwuZxDPsd0kK6cXrpg5PVK7ZcJLBQrJLidXzl7YjH FoeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1681235462; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=A0Vf9CsioDPUIy/35X2GZQaeu4eRriiCtifiBdf8Iqo=; b=2PJavd+yQMs/u0rmIPRWOmZtF4dL8QbEAi5iSifHUxe3ul1HoeF6EquSgru8J1cdFx QMglzup8WV2T2YBBxBS/wADX7W18AIbaAV3quWiWOJ961n3AOlRs3e2PA32xzdlVTnq9 BOifC2xbl8D+1Z3RlM22LD4+F3kvXbFjOcJFQa2KDRKv4EaZNGS+fz7mKUfQKAmD/QQv HO+Dvo8C6mE/ytcTMntZN00WYIBpHcRfJCsK49sQb565iEniOO8BMobi3bnupDGIu0N+ 7JmsDPlwgSGL2VMgzw2y/5+Oei9iO0lENBgDEdvdLqrQ/qzjTw8Y1OJWw0sjEuDGDpDM M9YA== X-Gm-Message-State: AAQBX9eEJmTg8Oiv0UyRVXVrtw2TOxYOmpCJKVB0X4tM6S6a7VpjXZXs TILZKsRyhK6UQqFzDtqP+GcHUX44z7w= X-Google-Smtp-Source: AKy350ZFNrjIp7bOhcbPusXl1U5rhNwO17de8zP2uB93aIV8p1HyZvlrPeYq/zLYdGYzAOyVqdsWWg== X-Received: by 2002:a17:903:24e:b0:1a2:17b1:71b with SMTP id j14-20020a170903024e00b001a217b1071bmr18514249plh.66.1681235461832; Tue, 11 Apr 2023 10:51:01 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:a423:c98:f7d5:55ad? ([2601:681:5a00:f790:a423:c98:f7d5:55ad]) by smtp.gmail.com with ESMTPSA id f9-20020a170902e98900b001a19438336esm9985690plb.67.2023.04.11.10.51.01 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 11 Apr 2023 10:51:01 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------bIDkMakzTI0xXmt5Nd0rC8Nt" Message-ID: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com> Date: Tue, 11 Apr 2023 11:51:00 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: TEXT column > 1Gb Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <80025ECD-44A6-454F-A4F9-784474B84952@lbl.gov> From: Rob Sargent In-Reply-To: <80025ECD-44A6-454F-A4F9-784474B84952@lbl.gov> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------bIDkMakzTI0xXmt5Nd0rC8Nt Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/11/23 11:41, Joe Carlson wrote: > Hello, > > I’ve recently encountered the issue of trying to insert more than 1 Gb into a TEXT column. While the docs say TEXT is unlimited length, I had been unaware of the 1Gb buffer size limitations. > > We can debate whether or not saving something this big in a single column is a good idea (spoiler: it isn’t. But not my design and, in fairness, was not anticipated when the schema was designed.), I’d like to implement something that is not a major disruption and try to keep the mods on the server side. My first idea is to have a chunked associated table (in pseudo code) > > CREATE TABLE associated(key_id integer references main_table(key_id), chunk integer, text_start integer, text_end integer, text_chunk TEXT); > > And define functions for inserting and selecting by dividing into 1Mb chunks > > CREATE FUNCTION insertText(INTEGER,TEXT) RETURNS INTEGER AS $$ > DECLARE > chunk INTEGER := 0; > key_id ALIAS for $1; > the_text ALIAS for $2; > text_chunk TEXT; > BEGIN > LOOP > text_chunk := substr(the_text,chunk*1000000,1000000); > IF length(text_chunk) = 0 THEN > EXIT; > END IF; > INSERT INTO associated(key_id,chunk,text_start,text_end,text_chunk) VALUES (key_id,chunk,chunk*1000000,(chunk*1000000+length(text_chunk)),text_chunk); > chunk := chunk + 1; > END LOOP; > RETURN chunk; > END; > $$ LANGUAGE plpgsql; > > This apparently runs into the same issues of buffers size: I get an ‘invalid message length’ in the log file and the insert fails. I can see from adding notices in the code that I never enter the LOOP; I assume having function arguments > 1Gb is also a bad thing. > > I’d like to continue to keep the modifications on the server size. And I’d like to believe someone else has had this problem before. Any suggestions other than have the client do the chunking? Can I use a different language binding and get around the argument length limitations? > > Thanks > > > I've hit this same limitation in Java (with write to db).  What is your stack in this case?  Not sure my solution applies. --------------bIDkMakzTI0xXmt5Nd0rC8Nt Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 4/11/23 11:41, Joe Carlson wrote:
Hello,

I’ve recently encountered the issue of trying to insert more than 1 Gb into a TEXT column. While the docs say TEXT is unlimited length, I had been unaware of the 1Gb buffer size limitations.

We can debate whether or not saving something this big in a single column is a good idea (spoiler: it isn’t. But not my design and, in fairness, was not anticipated when the schema was designed.), I’d like to implement something that is not a major disruption and try to keep the mods on the server side. My first idea is to have a chunked associated table (in pseudo code)

CREATE TABLE associated(key_id integer references main_table(key_id), chunk integer, text_start integer, text_end integer, text_chunk TEXT);

And define functions for inserting and selecting by dividing into 1Mb chunks

CREATE FUNCTION insertText(INTEGER,TEXT) RETURNS INTEGER AS $$
DECLARE
  chunk INTEGER := 0;
  key_id ALIAS for $1;
  the_text ALIAS for $2;
  text_chunk TEXT;
BEGIN
  LOOP
    text_chunk := substr(the_text,chunk*1000000,1000000);
    IF length(text_chunk) = 0 THEN
      EXIT;
    END IF;
    INSERT INTO associated(key_id,chunk,text_start,text_end,text_chunk) VALUES (key_id,chunk,chunk*1000000,(chunk*1000000+length(text_chunk)),text_chunk);
    chunk := chunk + 1;
  END LOOP;
  RETURN chunk;
END;
$$ LANGUAGE plpgsql;

This apparently runs into the same issues of buffers size: I get an ‘invalid message length’ in the log file and the insert fails. I can see from adding notices in the code that I never enter the LOOP; I assume having function arguments > 1Gb is also a bad thing.

I’d like to continue to keep the modifications on the server size. And I’d like to believe someone else has had this problem before. Any suggestions other than have the client do the chunking? Can I use a different language binding and get around the argument length limitations?

Thanks



I've hit this same limitation in Java (with write to db).  What is your stack in this case?  Not sure my solution applies.


--------------bIDkMakzTI0xXmt5Nd0rC8Nt--