Обсуждение: Viewing TEXT objects
Hi<br /><br />This is a very simple question, but I couldn't google an answer.<br />I have some tables with TEXT data typecolumns and my application can use them with no problem; just when I query these columns using pgAdmin, they show upas some int id. How can I view the actual column content?<br /> I'm using pgAdmin III 1.8.4 and PostgreSQL server 8.3.<br/><br />Thanks<br />
On Wed, Sep 30, 2009 at 8:40 PM, mad rug <mad.rug.f@gmail.com> wrote: > Hi > > This is a very simple question, but I couldn't google an answer. > I have some tables with TEXT data type columns and my application can use > them with no problem; just when I query these columns using pgAdmin, they > show up as some int id. How can I view the actual column content? > I'm using pgAdmin III 1.8.4 and PostgreSQL server 8.3. Odd. What is the table definition? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
[Please keep the list CC'd]
On Wed, Sep 30, 2009 at 8:49 PM, mad rug <mad.rug.f@gmail.com> wrote:
> Hi
>
> This is the definition:
>
> CREATE TABLE "TBL"
> (
> "ID" bigint NOT NULL,
> "COL1" text NOT NULL,
> "COL2" integer NOT NULL,
> "COL3" character(2) NOT NULL,
> CONSTRAINT "TBL_pkey" PRIMARY KEY ("ID")
> )
> WITH (OIDS=FALSE);
> ALTER TABLE "TBL" OWNER TO postgres;
>
> This is what I get with a select * from "TBL" (csv export):
> "ID";"COL1";"COL2";"COL3"
> 1;"88352";53125;"RE"
>
> The data is correct somewhere... I can query it correctly through JDBC.
Hmm, no reason that shouldn't work. Are you sure you're connecting to
the same database in pgAdmin vs. JDBC? Or that you don't have another
table of the same name in a different schema?
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
What is wrong with these results? COL1 has the text value "88352"
On Wed, Sep 30, 2009 at 2:52 PM, Dave Page <dpage@pgadmin.org> wrote:
[Please keep the list CC'd]
> This is the definition:
>
> CREATE TABLE "TBL"
> (
> "ID" bigint NOT NULL,
> "COL1" text NOT NULL,
> "COL2" integer NOT NULL,
> "COL3" character(2) NOT NULL,
> CONSTRAINT "TBL_pkey" PRIMARY KEY ("ID")
> )
> WITH (OIDS=FALSE);
> ALTER TABLE "TBL" OWNER TO postgres;
>
> This is what I get with a select * from "TBL" (csv export):
> "ID";"COL1";"COL2";"COL3"
> 1;"88352";53125;"RE"
>
> The data is correct somewhere... I can query it correctly through JDBC.
Hmm, no reason that shouldn't work. Are you sure you're connecting to
the same database in pgAdmin vs. JDBC? Or that you don't have another
table of the same name in a different schema?
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
On 30/09/2009 21:28, Michael Shapiro wrote: > What is wrong with these results? COL1 has the text value "88352" I think his point was that this was what he got querying via pgAdmin, while he got the actual column contents via JDBC. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Yes, that's how Ray said.<br /><br /> "88352" is not the data I have in that entry, it is some plain text data. This datacan be quite large, so I use TEXT instead of CHAR/VARCHAR.<br /><br />I'm puzzled how can this seem like some unusualsituation... I thought that it was the way pgAdmin returned large objects (to avoid big unecessary loads or fillingthe screen with data that most of the time is not essential), and so it was only a matter of calling some functionor changing some pgAdmin preference, but none of you seem to have experienced this before.<br /><br />Any ideas?<br/><br />Thanks again!<br /><br /><div class="gmail_quote">On Wed, Sep 30, 2009 at 5:33 PM, Raymond O'Donnell <spandir="ltr"><<a href="mailto:rod@iol.ie">rod@iol.ie</a>></span> wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im">On 30/09/200921:28, Michael Shapiro wrote:<br /> > What is wrong with these results? COL1 has the text value "88352"<br /><br/></div>I think his point was that this was what he got querying via pgAdmin,<br /> while he got the actual column contentsvia JDBC.<br /><br /> Ray.<br /><br /><br /> ------------------------------------------------------------------<br/><font color="#888888">Raymond O'Donnell, Directorof Music, Galway Cathedral, Ireland<br /><a href="mailto:rod@iol.ie">rod@iol.ie</a><br /> Galway Cathedral Recitals:<a href="http://www.galwaycathedral.org/recitals" target="_blank">http://www.galwaycathedral.org/recitals</a><br/> ------------------------------------------------------------------<br/></font></blockquote></div><br />
I have used text in all my tables in Postgres and have never had a problem
inserting data nor viewing it with PgAdmin. How do you know it isn't a JDBC
issue?
inserting data nor viewing it with PgAdmin. How do you know it isn't a JDBC
issue?
On Wed, Sep 30, 2009 at 3:48 PM, mad rug <mad.rug.f@gmail.com> 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.
Any ideas?
Thanks again!On Wed, Sep 30, 2009 at 5:33 PM, Raymond O'Donnell <rod@iol.ie> wrote:On 30/09/2009 21:28, Michael Shapiro wrote:I think his point was that this was what he got querying via pgAdmin,
> What is wrong with these results? COL1 has the text value "88352"
while he got the actual column contents via JDBC.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
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
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:When you say "quite large", about how long are the strings?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.
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
I really don't know. JDBC communication is being handled by a JDO library, so I don't know which one is doing this, but I suspect about JDO. I feel like asking about it in the forums there, but then I'd like to know: is there any performance (disk, read/write speed...) difference between storing large text data as TEXT and large object? I believe this indirection of storing as large object could mean a small overhead, unless it is somehow better using large objects (just then why use TEXT for some data that is just a couple of chars?)
Thanks again!
On Fri, Oct 2, 2009 at 5:35 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 01/10/2009 13:24, mad rug wrote:Actually, your text data is apparently being stored as large objects,
> 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"
rather than being inserted directly into the text column. So it may
indeed be JDBC doing something funny.
I don't know anything about JDBC or PG's large objects (and not a whole
lot about Java either), but I wonder if something is telling JDBC that
the data is binary, rather than just plain text?
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On 02/10/2009 12:55, mad rug wrote: > I really don't know. JDBC communication is being handled by a JDO > library, so I don't know which one is doing this, but I suspect about > JDO. I feel like asking about it in the forums there, but then I'd like > to know: is there any performance (disk, read/write speed...) difference > between storing large text data as TEXT and large object? I believe this > indirection of storing as large object could mean a small overhead, > unless it is somehow better using large objects (just then why use TEXT > for some data that is just a couple of chars?) Well, as I said I don't really know anything about large objects, but I'd suspect that you're right about the performance hit with them. However, TEXT is the recommended type for *any* text storage, big or small: there's no difference performance-wise between TEXT and VARCHAR(n), and if anything I'd guess that VARCHAR is a (very very) tiny bit slower because of the length check. Large object is intended for binary data. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Yes, I'll go for pure TEXT objects. I read this performance tip about TEXT on the docs, makes me like Postgres :-)
Anyway, I just checked alternative types for character data, and tested JDBC type LONGVARCHAR, and now the data is being stored directly to the TEXT field. Just configuration after all, living and learning... better get rid of those large objects.
Thanks Ray!
On Fri, Oct 2, 2009 at 9:33 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 02/10/2009 12:55, mad rug wrote:Well, as I said I don't really know anything about large objects, but
> I really don't know. JDBC communication is being handled by a JDO
> library, so I don't know which one is doing this, but I suspect about
> JDO. I feel like asking about it in the forums there, but then I'd like
> to know: is there any performance (disk, read/write speed...) difference
> between storing large text data as TEXT and large object? I believe this
> indirection of storing as large object could mean a small overhead,
> unless it is somehow better using large objects (just then why use TEXT
> for some data that is just a couple of chars?)
I'd suspect that you're right about the performance hit with them.
However, TEXT is the recommended type for *any* text storage, big or
small: there's no difference performance-wise between TEXT and
VARCHAR(n), and if anything I'd guess that VARCHAR is a (very very) tiny
bit slower because of the length check. Large object is intended for
binary data.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------