Thread: SELECT bug?

============================================================================
                         POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name        : Jacek Kalinski (Jack)
Your email address    : jacek_kal@o2.pl


System Configuration
---------------------
   Architecture (example: Intel Pentium)     : i686 - AMD Athlon XP 1800+

   Operating System (example: Linux 2.0.26 ELF)     : Linux2.4.20/2.4.21 ELF

   PostgreSQL version (example: PostgreSQL-7.3.3): PostgreSQL-7.3.3

   Compiler used (example:  gcc 2.95.2)        : 3.2.2

Memory: 512MB DDR
Avg CPU usage when inserting records to db: 20-35%

Please enter a FULL description of your problem:
------------------------------------------------

Firstly, sorry for my English (it is not my native language).

I have a strange problem: I created a new database in postgres. I tried
to import a tables, views etc. - there was no error.
Then I started a small script (wroted in perl) which started to import
data into my database - but I think that is no problem with this script..
After that I checked, that records are incorrect (are duplicated!).
It is not first time, when records in different tables are duplicated.
In another console i tried to connect to this database (by psql). I wrote:
select usable,count(*) from authors group by usable having count(*)>1;

I get a list of duplicates. When I tried to select only duplicated rows
by select * from authors where usable = '"name" <alias@domain.pl>';
first run this command returned me 0 rows (sometimes, but rarely I also
got that 1 rows were found as you can see below):
  id   |        original          |           usable
------+--------------------------+--------------------------------
13126 | "name" <alias@domain.pl> | "name" <alias@domain.pl>
(1 row)

but when I wrote the same command not with equals '=' but with 'like' I
  got:
  id   |        original          |           usable
------+--------------------------+--------------------------------
  8078 | "name" <alias@domain.pl> | "name" <alias@domain.pl>
13126 | "name" <alias@domain.pl> | "name" <alias@domain.pl>
(2 rows)

I really do not know where can be a problem.
Now it is not a problem that records were inserted twice (or more - I
also have this records), but the script is checking if record already
exist by select.

I don't know what to do with this...



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

How to repeat this problem? It is very easy: just connect to database
and write down select command.
To get different records it is very difficult, but why select does not
return correctly (0 rows)?

Yours faithfully,
Jack Kalinski

Check the CREATE TABLE statement. Possibly you use VARCHAR type and your
data has trailling spaces. Try to TRIM all the data in your database or
remove trailling spaces using text editor.

Dnia 06/26/2003 11:12 PM, Ace napisa³(a):
> Check the CREATE TABLE statement. Possibly you use VARCHAR type and your
> data has trailling spaces. Try to TRIM all the data in your database or
> remove trailling spaces using text editor.

I don't use VARCHAR, but TEXT type. I also checked, but there are no
leading or trailing spaces.
Maybe it will be easiest when I show the table:

CREATE SEQUENCE authors_seq
     START 1
     INCREMENT 1
     MAXVALUE 9223372036854775807
     MINVALUE 1
     CACHE 1;

CREATE TABLE authors (
     id bigint DEFAULT nextval('authors_seq'::text) NOT NULL,
     original text DEFAULT '' NOT NULL,
     usable text DEFAULT '' NOT NULL
) WITHOUT OIDS;

ALTER TABLE ONLY authors
     ADD CONSTRAINT authors_pkey PRIMARY KEY (id);

Jack

On Thu, 26 Jun 2003 23:12:53 +0200
"Ace" <a_s@poczta.fm> wrote:

> Check the CREATE TABLE statement. Possibly you use VARCHAR type and your
> data has trailling spaces. Try to TRIM all the data in your database or
> remove trailling spaces using text editor.

since the code is mine, let's cast more info:
1st. there are only "TEXT" fields.
2nd. insertingo of data is done by pl/pgsql function.
3rd. there are unique indices on tables - but they just didn't work.

i belive this would help a little bit:
CREATE SEQUENCE authors_seq
    START 1
    INCREMENT 1
    MAXVALUE 9223372036854775807
    MINVALUE 1
    CACHE 1;
CREATE TABLE authors (
    id bigint DEFAULT nextval('authors_seq'::text) NOT NULL,
    original text DEFAULT '' NOT NULL,
    usable text DEFAULT '' NOT NULL
) WITHOUT OIDS;

CREATE UNIQUE INDEX authors_original ON authors USING btree (original);
ALTER TABLE ONLY authors ADD CONSTRAINT authors_pkey PRIMARY KEY (id);

CREATE OR REPLACE FUNCTION getAuthorID(TEXT) RETURNS INT8 AS '
DECLARE
        in_author                  ALIAS FOR $1;
        reply INT8;
BEGIN
        SELECT id INTO reply FROM authors WHERE original = in_author;
        IF found AND reply IS NOT NULL THEN
                RETURN reply;
        END IF;
        INSERT INTO authors (original, usable) VALUES (in_author, in_author);
        RETURN currval(''authors_seq'');
END;
' LANGUAGE 'plpgsql';


and the problem is that sometimes calling getAuthorID inserts new row instead of returning id of existing one.

of course everything is inside transactions.

for me it looked like index problem because when making sequential scan over table (for example by forcing it with
"like"- rows are returned ok. but when index-scanning - only some or even none of the rows are returned. 

i know this is far from detailed description, but we didn't found yet small example/proof of bug - we do large isnert
sets- and just sometimes it gets screwed. 

depesz