[BUGS] SELECT bug?

[Jacek Kalinski]

POSTGRESQL BUG REPORT TEMPLATE

Your name   : Jacek Kalinski (Jack)
Your email address  : [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>';
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" <[EMAIL PROTECTED]> | "name" <[EMAIL PROTECTED]>
(1 row)

but when I wrote the same command not with equals '=' but with 'like' I 
 got:
 id   |original  |   usable
--+--+
 8078 | "name" <[EMAIL PROTECTED]> | "name" <[EMAIL PROTECTED]>
13126 | "name" <[EMAIL PROTECTED]> | "name" <[EMAIL PROTECTED]>
(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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [BUGS] SELECT bug?

[Jacek Kalinski]

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

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [BUGS] Daylight saving bug in PostgreSQL 7.2.1 ?

[Jacek Kalinski]

Dnia 07/12/2003 04:54 AM, Jonas Lindholm napisał(a):
Hi!

I believe I've found a bug with daylight saving time.
The example below shows that after inserting a row with a date and time 
when daylight saving is changing (6 April 2003 02:00:00) any selects 
using the same time and date are only returning correct result first 
time. If the select is done again the row is not found.
Hi,

I had similar problem that select returned 1 or 0 rows (with the same 
query) (if you want - see "SELECT bug?")

I don't know where is the problem, but try to reindex table timespan.
In my database it resolved all problems (hope so ;) )
Now select return correct values.
Jacek

---(end of broadcast)---
TIP 8: explain analyze is your friend