Re: SELECT bug?

On Thu, 26 Jun 2003 23:12:53 +0200
"Ace" <a_s(at)poczta(dot)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