X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7C417D1B275 for ; Wed, 16 Jun 2004 11:59:36 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21547-01 for ; Wed, 16 Jun 2004 14:59:33 +0000 (GMT) Received: from trade-india.com (unknown [61.16.154.82]) by svr1.postgresql.org (Postfix) with SMTP id 0588AD1B20E for ; Wed, 16 Jun 2004 11:59:30 -0300 (ADT) Received: (qmail 30163 invoked from network); 16 Jun 2004 14:43:06 -0000 Received: from system100.trade-india-local.com (HELO ?192.168.0.100?) (192.168.0.100) by system66.trade-india-local.com with SMTP; 16 Jun 2004 14:43:06 -0000 Message-ID: <40D06096.90200@trade-india.com> Date: Wed, 16 Jun 2004 20:30:38 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: weired behavior... after pg_resetxlog-> dump->initdb-->reload. References: <40CFFC68.5090704@trade-india.com> <21524.1087394109@sss.pgh.pa.us> In-Reply-To: <21524.1087394109@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------000403040403070606020802" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200406/203 X-Sequence-Number: 13863 This is a multi-part message in MIME format. --------------000403040403070606020802 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Dear Tom, Sorry i could not replicate that issue . But here is something which was the original problem and is replicable. Regds Mallah. rt2=# CREATE UNIQUE INDEX users_1 on users_sample(name); ERROR: could not create unique index DETAIL: Table contains duplicated values. rt2=# rt2=# SELECT name from users_sample group by name having count(*) > 1 limit 20; +------+ | name | +------+ +------+ (0 rows) Time: 766.725 ms rt2=# SELECT version(); rt2=# SELECT name from users_sample group by name having count(*) > 1 ; +------+ | name | +------+ +------+ (0 rows) Time: 905.586 ms rt2=# SELECT version(); +----------------------------------------------------------------------------------------------------------+ | version | +----------------------------------------------------------------------------------------------------------+ | PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-34) | +----------------------------------------------------------------------------------------------------------+ (1 row) Time: 338.191 ms Tom Lane wrote: >Rajesh Kumar Mallah writes: > > >>rt2=# SELECT name from users group by name having count(*) > 1 limit 20; >>(4 rows) >>rt2=# SELECT name from users group by name having count(*) > 1 ; >>(0 rows) >> >> > >Weird. What PG version is this? What does EXPLAIN show for each of >these queries (maybe EXPLAIN ANALYZE too)? Can you provide a >self-contained test case? > > regards, tom lane > > > --------------000403040403070606020802 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Dear Tom,

Sorry i could not replicate that issue . But
here is something which was the original problem
and is replicable.


Regds
Mallah.



rt2=# CREATE UNIQUE INDEX users_1 on users_sample(name);
ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.
rt2=#
rt2=# SELECT name from users_sample group by name having count(*) > 1 limit 20;
+------+
| name |
+------+
+------+
(0 rows)

Time: 766.725 ms
rt2=# SELECT version();
rt2=# SELECT name from users_sample group by name having count(*) > 1 ;
+------+
| name |
+------+
+------+
(0 rows)

Time: 905.586 ms
rt2=# SELECT version();
+----------------------------------------------------------------------------------------------------------+
|                                                 version                                                  |
+----------------------------------------------------------------------------------------------------------+
| PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-34) |
+----------------------------------------------------------------------------------------------------------+
(1 row)

Time: 338.191 ms


Tom Lane wrote:
Rajesh Kumar Mallah <mallah@trade-india.com> writes:
  
rt2=# SELECT name from users group by name having count(*) > 1 limit 20;
(4 rows)
rt2=# SELECT name from users group by name having count(*) > 1 ;
(0 rows)
    

Weird.  What PG version is this?  What does EXPLAIN show for each of
these queries (maybe EXPLAIN ANALYZE too)?  Can you provide a
self-contained test case?

			regards, tom lane

  

--------------000403040403070606020802--