Re: Viewing TEXT objects - Mailing list pgadmin-support

From mad rug
Subject Re: Viewing TEXT objects
Date
Msg-id ba6e95cf0910010524n42f6a318maa4b779cb094629b@mail.gmail.com
Whole thread Raw
In response to Re: Viewing TEXT objects  (Dan Halbert <halbert@halwitz.org>)
List pgadmin-support
Well, all the data is inserted and used by JDBC (other libraries on top of JDBC, in fact), and it is all working perfectly for months. I can update and/or read data through JDBC or pgAdmin, and the change is correctly reflected on the other.
The first TEXT columns I used store text files around 50k, but that part is done and tested. Now I'm storing data ranging from a dozen bytes up to 1k, but with no real known upper bound, so I chose TEXT again. I'll do a lot of testing on these objects so I need to quickly see and update them... but this issue is slowing me down.

I was writing this reply, then I got a way of doing it. I opened some of the backup scripts generated by pgAdmin 'backup' command (this is other of my older tables with TEXT column):

CREATE TABLE "ABC" (
    "DT" date NOT NULL,
    "TXT" text NOT NULL
);
INSERT INTO "ABC" VALUES ('2009-07-27', '44828');
SET search_path = pg_catalog;
SELECT lo_open(lo_create(44828), 131072);
SELECT lowrite(0, 'all my text is here');
SELECT lo_close(0);
COMMIT;

This seems to indicate that it is really some id being stored in that column. I could correctly query the column data with this command:
SELECT "DT",loread(lo_open("TXT"::int, 131072), 999999999) from "ABC"

The number 131072 is some flag I couldn't find what means, and 999999999 is the max read size, but I could read the value anyway.

Well, now I'd like if someone could tell me if it is possible to make this 'loread' automatic on my queries... or will I have to keep it at hand for everytime I need a TEXT object?

Also, I'd be grateful if someone could help me why is this happening behind the curtains. I suspect the text field by itself can hold the large text objects (I mean, I could paste many K of data on it using pgAdmin, and it would be stored correctly... you guys seem to do it already), so why would an ID be stored in the text field, and then require some read function to read it from elsewhere? Some old PostgreSQL trick or workaround?... I just won't be able to do much is this is some weirdness on the logic of JDBC or my data storage lib (and can't be changed by some config).

Thanks for all your time, guys!

On Wed, Sep 30, 2009 at 8:24 PM, Dan Halbert <halbert@halwitz.org> wrote:
mad rug wrote:
Yes, that's how Ray said.

"88352" is not the data I have in that entry, it is some plain text data. This data can be quite large, so I use TEXT instead of CHAR/VARCHAR.

I'm puzzled how can this seem like some unusual situation... I thought that it was the way pgAdmin returned large objects (to avoid big unecessary loads or filling the screen with data that most of the time is not essential), and so it was only a matter of calling some function or changing some pgAdmin preference, but none of you seem to have experienced this before.
When you say "quite large", about how long are the strings?

Do you get the right data if you use psql?

Dan

--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support

pgadmin-support by date:

Previous
From: "Andrus"
Date:
Subject: Re: Editing config files which are not in database directory
Next
From: mad rug
Date:
Subject: Re: Viewing TEXT objects