pgAdmin III错误行为? [英] pgAdmin III faulty behavior?

问题描述

我当时在pgAdmin上进行查询，偶然发现了这种奇怪的行为.
我已连接到运行PostgreSQL 9.1.9的服务器.
我有一个名为messages的表，定义如下:

I was doing a query on pgAdmin and stumbled upon 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)

我的问题涉及列body和subject.

我有一个查询，该查询生成了一组结果.然后，为完善查询条件，我添加了术语:where body like '%JSON%'，即正文包含字符串'JSON'的结果子集.
我得到了一些包含单词的结果，而有些却没有！但是，如果我搜索任意字符串，结果将是可以的.我检查发现，查询不仅在搜索主体列，而且还在搜索主题列，这很疯狂.

I had a query that generated a set of results. Then, to refine my query, I added the term: where body like '%JSON%' i.e., the subset of results where body contains the string 'JSON'.
I got some results containing the word, and some that did not! But if I searched for an arbitrary string the results would be ok. I checked and found out that the query is not just searching the body column, but also the subject column as well which is crazy.

这是我的初始查询:

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

返回1行:

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 (...)"

如果我搜索:

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

我不应该得到任何结果，因为体内没有任何结果.但我仍然返回相同的行-似乎是因为subject中的JSON是吗?

I should not get any results because there is none in the body. But I still get the same row returned - it would seem because 'JSON' is in the subject?

我什至尝试过:

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 severely confused.

我试图在sqlfiddle.com上重现结果，但未成功.在那里，我得到了对SQL选择查询的期望:
http://sqlfiddle.com/#!1/ec74c/4

I attempted to reproduce the results on sqlfiddle.com, but I was not successful. There, I get what is expected of an sql select query:
http://sqlfiddle.com/#!1/ec74c/4

推荐答案

您无法在SQL Fiddle上重现同样的效果.

You cannot reproduce the same effect on SQL Fiddle.

我在Postgres 9.1.13中重新创建了您的表(始终升级到最新的发行版！)，然后在pgAdmin(当前版本1.18.1)中运行查询. 我无法重现该问题.

I recreated your table in Postgres 9.1.13 (always upgrade to the latest point release!) and ran the queries in pgAdmin (current version 1.18.1). I cannot reproduce the problem.

我不知道pgAdmin如何在其中发挥作用-除非您只选择查询的一部分，否则不知道这种影响:
执行脚本的pgAdmin快捷方式

I don't see how pgAdmin could play a role in this - unless you have been selecting only a part of your query, unaware of this effect:
pgAdmin shortcuts to execute scripts

或者您可能会被每列最大字符数"设置所迷惑，该设置会截断显示中的长值，将匹配项隐藏在截断的部分中，例如

Or you might be fooled by the "Max. characters per column" setting, which truncates long values in the display, hiding the match in the truncated part, like @IMSoP suggested in his comment. Check File -> Options ...

如果不是那样，并且除非我们处理错别字或您所不在的情况，否则这表明您的数据库中有损坏的东西.

If that's not it and unless we are dealing with typos or circumstances not in your question, this would indicate something is broken in your database.

在简单的情况下，只要索引损坏，就会使用 REINDEX TABLE 可能会成功:

In simple cases with just a corrupted index, a REINDEX TABLE might do the trick:

REINDEX TABLE messages;

但是，仔细观察，我发现没有索引可能是这里的罪魁祸首.

However, on a closer look, I don't see an index that could possibly be the culprit here.

系统目录损坏? 请先阅读以下内容:
http://wiki.postgresql.org/wiki/Corruption

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

然后阅读 REINDEX的注释部分 a>并从外壳运行:

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 hardware. A failing disk or something. Follow up on that ...

相关问题:
修复损坏的数据库postgresql

Related question:
Repair Corrupt database postgresql

这篇关于pgAdmin III错误行为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！