PgAdmin III erroneous behavior?

Question

I made a request to pgAdmin and came across this weird behavior.
I was connected to a server running PostgreSQL 9.1.9.
I have a table called messages with the following definition:

ghareh@godot:~$ psql psql (9.1.9) Type "help" for help. ghareh=# \d messages Table "public.messages" Column | Type | Modifiers ---------------+-----------------------------+--------------------------------- messageid | character varying(200) | not null senderaliasid | integer | not null referenceid | character varying(200) | default NULL::character varying recipaliasid | integer | datetime | timestamp(2) with time zone | not null subject | character varying(512) | not null body | text | not null listid | integer | Indexes: "messages_pkey" PRIMARY KEY, btree (messageid) "messages_datetime_idx" btree (datetime) "recipaliasid_idx" btree (recipaliasid) "referenceid_idx" btree (referenceid) "senderaliasid_idx" btree (senderaliasid) Foreign-key constraints: "messages_listid_fkey" FOREIGN KEY (listid) REFERENCES lists(listid) "messages_recip_fkey" FOREIGN KEY (recipaliasid, listid) REFERENCES aliases(aliasid, listid) "messages_sender_fkey" FOREIGN KEY (senderaliasid, listid) REFERENCES aliases(aliasid, listid) Referenced by: TABLE "messages_attachments" CONSTRAINT "pkfkmid" FOREIGN KEY (messageid) REFERENCES messages(messageid) 

My question includes body and subject columns.

I had a query that generated a result set. Then, to clarify my query, I added the term: where body like '%JSON%' ie, a subset of the results in which the body contains the string "JSON".
I got some results containing the word, and some not! But if I were looking for an arbitrary string, the results would be fine. I checked and found out that the query consists not only in finding the body column, but also in the topic column, which is crazy.

Here is my initial request:

 select * from messages where messageid = '[e-mail]' 

which returns 1 row:

 messageid: "[e-mail]"; senderaliasid: 13777; referenceid: "[e-mail]"; recipaliasid: ; datetime: "2006-07-17 20:53:35-07"; listid: 251; subject: "Re: svn commit: r422930 - /incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java"; body: "busted! thanks for the thorough review. -Elias Garrett Rooney wrote: > On 7/17/06, [e-mail] <[e-mail]> wrote: >> Author: eliast >> Date: Mon Jul 17 17:44:10 2006 >> New Revision: 422930 >> >> URL: http://svn.apache.org/viewvc?rev=422930 (...)" 

If I am looking for:

 select * from messages where messageid = '[e-mail]' and body like '%JSON%' 

I should not get any results, because there is not one in the body. But I still get the same line - it would seem because "JSON" is in subject ?

I even tried this:

 select * from messages where messageid = '[e-mail]' and body like '%incubator/abdera/java/trunk/extensions/src/main/java/org/apache/abdera/ext/json/JSONWriter.java%' 

and I still got the same row back. I am very confused.

I tried to reproduce the results on sqlfiddle.com but I failed. There I get what is expected from the sql select query:
http://sqlfiddle.com/#!1/ec74c/4

Answer 1

You cannot reproduce the same effect on SQL Fiddle.

I recreated your table in Postgres 9.1.13 ( always updated to the latest version! ) And ran queries in pgAdmin (current version 1.18.1). I can not reproduce the problem.

pgAdmin?

I don’t see how pgAdmin can play a role in this - unless you select only part of your request without realizing this effect:
pgAdmin shortcuts for script execution

Or you may be fooled by the setting "Maximum characters per column", which truncates long values ​​on the display, hiding the match in the truncated part, for example @IMSoP, suggested in his comment . Check File -> Options ...

If this is not the case, and if we are not dealing with typos or circumstances that are not your question, this indicates that something is broken in your database.

Corruption?

In simple cases, with only a damaged index, REINDEX TABLE can do the trick:

 REINDEX TABLE messages; 

However, upon closer inspection, I do not see a pointer that could be the culprit here.

Corrupted system directory? Read this first:
http://wiki.postgresql.org/wiki/Corruption

Then read the Notes Section for REINDEX and run from the shell:

 $ export PGOPTIONS="-P" $ psql broken_db ... broken_db=> REINDEX DATABASE broken_db; broken_db=> \q 

Corruption often indicates a problem with your equipment. Bad drive or something like that. Follow this ...

Related Question:
Repair corrupted postgresql database

Answer 2

In this case, it was a pgAdmin error. As mentioned in @IMSoP, it turned out that pgAdmin truncates the results. I was confused since I recently installed a new version of pgAdmin, and this behavior is new for this version (at least by default), since I clearly remember how I ran the same queries 1 year ago and got full text results.