CODEWITHSUNDEEP
postgresqltextinsertfieldpostgresql-9.2
Jérôme Verstrynge
Jérôme Verstrynge

Reputation: 59616

Inserting text of length > 4681 characters results in empty table field with no error message

I am facing a strange bug. I have a table in Postgresql with a text field "txtj". I also have a function that populates this table with:

INSERT INTO "precomputed" ("stmt","param","slice","txtj","crea","exp")
     VALUES (statement_name,parametr,sliice,textj,creation,expiration);

When "txtj" has a character length of 4681 at most, the field is populated correctly. But, if it has more characters, the field is left blank, with no error message raised. I could find this number by checking with substrings:

textj := substring(textj from 0 for 4681);

Why? Is there a restriction on the length of text fields? My textj contains about 7500 characters. How can I solve this issue

UPDATE

I can replicate this issue from pgAdmin III's SQL console using the following script (I am under windows 7):

CREATE TABLE IF NOT EXISTS "test" (i integer, ttt text);

CREATE OR REPLACE FUNCTION fill_test()
RETURNS void AS $$
DECLARE
    ffill text = '';
BEGIN
    TRUNCATE "test";
    FOR i IN 0..250 LOOP
        INSERT INTO "test" VALUES ((i*20),ffill);
        ffill := ffill || '01234567890123456789';
    END LOOP;
END; $$
LANGUAGE plpgsql;

SELECT fill_test();
SELECT * FROM "test" ORDER BY "i";

Above 4680, the fields are empty...

UPDATE II

select version() returns:

"PostgreSQL 9.2.6, compiled by Visual C++ build 1600, 32-bit"

pgAdmin's version: 1.16.1

Upvotes: 0

Views: 104

Answers (1)

Jérôme Verstrynge
Jérôme Verstrynge

Reputation: 59616

For the records, there is no bug on Postgresql' side. I have managed to retrieve the field value correctly using a small node app connected to the database.

I am leaving this answer as info for others. According to jjanes, the issue does not happen in 9.3.2 / 1.18.1.

Upvotes: 1

Related Questions