Thread: BYTEA: insert query max size
Dear All. When i am trying to insert bytea with size of escape-coded data less than 16785346 bytes it works fine. But for more than16785346 bytes it causes an error with dropping connection. Please, let me know the reason of this limitation. I am using PostgreSql 8.1.3 on WindowsXp with default settings of postgres. inner mailto:transto@inbox.ru
inner <transto@inbox.ru> writes:
> When i am trying to insert bytea with size of escape-coded data less than 16785346 bytes it works fine. But for more
than16785346 bytes it causes an error with dropping connection.
You'll need to provide more details of what you're doing, what error
message you get, etc.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> inner <transto@inbox.ru> writes:
>> When i am trying to insert bytea with size of escape-coded data less than 16785346 bytes it works fine. But for more
than16785346 bytes it causes an
>> error with dropping connection.
>
> You'll need to provide more details of what you're doing, what error
> message you get, etc.
I have an example table:
'CREATE TABLE "public"."tcad" (
"pk" SERIAL,
"gtype" VARCHAR(50),
"geom" BYTEA,
CONSTRAINT "tcad_pkey" PRIMARY KEY("pk")
) WITH OIDS;'
I am using PostgresDAC 2.3.1 for direct access to Postgres through CBuilder6.
1. creating stream of char 'a', which size is 16785347.
2. loading bytea parameter from stream into PGSQLQuery, where stored SQL query: 'insert into tcad (gtype,geom) values
(:Size,:Data);'.
3. in parameter Size storing string: '16785347 b'.
4. when i try to execute query, i am getting error: 'Software caused connection abort (0x00002745/10053)'.
For stream size of 16785346 it works fine.
It looks like sql query size limit, isn't it?
inner mailto:transto@inbox.ru
inner <transto@inbox.ru> writes:
> I am using PostgresDAC 2.3.1 for direct access to Postgres through CBuilder6.
> 1. creating stream of char 'a', which size is 16785347.
> 2. loading bytea parameter from stream into PGSQLQuery, where stored SQL query: 'insert into tcad (gtype,geom) values
(:Size,:Data);'.
> 3. in parameter Size storing string: '16785347 b'.
> 4. when i try to execute query, i am getting error: 'Software caused connection abort (0x00002745/10053)'.
> For stream size of 16785346 it works fine.
> It looks like sql query size limit, isn't it?
There's no such limit hardwired into Postgres. I don't know anything
about CBuilder or PostgresDAC, though, and wouldn't be surprised if one
of them is creating the limitation.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: >> For stream size of 16785346 it works fine. >> It looks like sql query size limit, isn't it? > > There's no such limit hardwired into Postgres. I don't know anything > about CBuilder or PostgresDAC, though, and wouldn't be surprised if one > of them is creating the limitation. Do you know, same error caused from 'pgAdminIII v1.4.1' and 'EMS Manager for PostgreSql v3.4.0.1'? pgAdmin: 1. I have inserted record with 16785346 bytes of bytea data. 2. Opened table and append additional 10 bytes in that record. 3. Tried to step on another record in the table (for posting data to server) and got same error. EMS: 1. Opened table and append new record. 2. Modified bytea field with file of 16785347 bytes (just char 0x62) 3. Pressed button to post record to server and got again same error. BTW, same error caused with ZeosLib6.5.1-alpha CVS-Version as of 13-10-2005 in CBuilder application. Where is the truth? inner mailto:transto@inbox.ru
inner <transto@inbox.ru> writes:
> Do you know, same error caused from 'pgAdminIII v1.4.1' and 'EMS Manager for
> PostgreSql v3.4.0.1'?
[ shrug... ] it works for me up to past 30Mb, and fails with an "out of
memory" error around 40Mb, but I have a relatively small process size
limit on this machine (128Mb I think). I think you're looking at
ungraceful out-of-memory handling in one or another of those bits of
software, plus a not very large memory limit.
regards, tom lane