Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bug or my crooked hands?

Bug or my crooked hands?

From
Yury Don
Date:
Hello All,

I have the following situation:

CREATE TABLE "Contacts" (
        "Contact"       Serial,
        "StuffTheir"   Text,
        "TheDate"  datetime DEFAULT TEXT 'now',
        "Subj"  Text,
        "CustomerId"  int4,
        "Agent" int4,
        "ActionType"   int2,
        "Done"      bool default false not null,
        "ExecutionDate"        datetime,
        "Whom"  int4
);

Then I have inserted data from text file and do a query

mdb=> select Contact from Contacts;
   Contacts
-----------
-2112563299
-2111287024
-2110307960
-2110004687
-2109773688
-2109611925
-2108616455
...

mdb=> select Contact from Contacts where Contact=-2112563299;
Contacts
-------
(0 rows)

What is this and how to treat it?

Best regards,
 Yury  ICQ 11831432
 mailto:yura@vpcit.ru

Re: [GENERAL] Bug or my crooked hands?

From
"J. Roeleveld"
Date:
> Hello All,
>
> I have the following situation:
>
> CREATE TABLE "Contacts" (
>         "Contact"       Serial,
>         "StuffTheir"   Text,
>         "TheDate"  datetime DEFAULT TEXT 'now',
>         "Subj"  Text,
>         "CustomerId"  int4,
>         "Agent" int4,
>         "ActionType"   int2,
>         "Done"      bool default false not null,
>         "ExecutionDate"        datetime,
>         "Whom"  int4
> );
>
> Then I have inserted data from text file and do a query
>
> mdb=> select Contact from Contacts;
>    Contacts
> -----------
> -2112563299
> -2111287024
> -2110307960
> -2110004687
> -2109773688
> -2109611925
> -2108616455
> ...

How did you get negative values in a Serial - field??
Serial-fields default to positive numbers....

Re[2]: [GENERAL] Bug or my crooked hands?

From
Yury Don
Date:
Hello J.,

Once, Wednesday, January 19, 2000, 4:19:12 PM, you wrote:

>> Hello All,
>>
>> I have the following situation:
>>
>> CREATE TABLE "Contacts" (
>>         "Contact"       Serial,
>>         "StuffTheir"   Text,
>>         "TheDate"  datetime DEFAULT TEXT 'now',
>>         "Subj"  Text,
>>         "CustomerId"  int4,
>>         "Agent" int4,
>>         "ActionType"   int2,
>>         "Done"      bool default false not null,
>>         "ExecutionDate"        datetime,
>>         "Whom"  int4
>> );
>>
>> Then I have inserted data from text file and do a query
>>
>> mdb=> select Contact from Contacts;
>>    Contacts
>> -----------
>> -2112563299
>> -2111287024
>> -2110307960
>> -2110004687
>> -2109773688
>> -2109611925
>> -2108616455
>> ...

JR> How did you get negative values in a Serial - field??
JR> Serial-fields default to positive numbers....

I have copied the data from MS Access table and there where negative
values. I can't change data in field Contacts because they are using
in other tables.

--
Best regards,
 Yury  ICQ 11831432
 mailto:yura@vpcit.ru

Re: Re[2]: [GENERAL] Bug or my crooked hands?

From
Mark Jewiss
Date:
Hello,

On Wed, 19 Jan 2000, Yury Don wrote:

> >>         "Contact"       Serial,

> JR> How did you get negative values in a Serial - field??
> JR> Serial-fields default to positive numbers....
>
> I have copied the data from MS Access table and there where negative
> values. I can't change data in field Contacts because they are using
> in other tables.

I'd go along with the previous statement - a serial should only be a
positive number.

What was the schema for the table in MS Access? You may have to change
that (by copying everything into a new table), and then moving it into
Postgres.

What are you using this column for anyway? Is it just an identifier?

Regards,

Mark.
--
Mark Jewiss
Knowledge Matters Limited
http://www.knowledge.com

Re[2]: [GENERAL] Bug or my crooked hands?

From
"Richard Huxton"
Date:
----- Original Message -----
From: Yury Don <yura@vpcit.ru>
To: pgsql-general <pgsql-general@postgreSQL.org>
Sent: Wednesday, January 19, 2000 11:32 AM
Subject: Re[2]: [GENERAL] Bug or my crooked hands?
> >> I have the following situation:
> >>
> >> CREATE TABLE "Contacts" (
> >>         "Contact"       Serial,
[snipped fields]
> >> );
> >>
> >> Then I have inserted data from text file and do a query
> >>
> >> mdb=> select Contact from Contacts;
> >>    Contacts
> >> -----------
> >> -2112563299
[snip]
> >> ...
>
> JR> How did you get negative values in a Serial - field??
> JR> Serial-fields default to positive numbers....
>
> I have copied the data from MS Access table and there where negative
> values. I can't change data in field Contacts because they are using
> in other tables.

Speaking of which, what range have you got on that sequence (serial type =
sequence AFAIK)?

It couldn't be that postgres is looking at your WHERE and deciding that the
value is out of range, so there can't be any records to return.

What happens if you insert a new record and then try to look for that?

--
  Richard Huxton
  Archonet Ltd.

Re[3]: [GENERAL] Bug or my crooked hands?

From
Yury Don
Date:
Hello Richard,

Once, Thursday, January 20, 2000, 3:04:43 PM, you wrote:

RH> ----- Original Message -----
RH> From: Yury Don <yura@vpcit.ru>
RH> To: pgsql-general <pgsql-general@postgreSQL.org>
RH> Sent: Wednesday, January 19, 2000 11:32 AM
RH> Subject: Re[2]: [GENERAL] Bug or my crooked hands?
>> >> I have the following situation:
>> >>
>> >> CREATE TABLE "Contacts" (
>> >>         "Contact"       Serial,
RH> [snipped fields]
>> >> );
>> >>
>> >> Then I have inserted data from text file and do a query
>> >>
>> >> mdb=> select Contact from Contacts;
>> >>    Contacts
>> >> -----------
>> >> -2112563299
RH> [snip]
>> >> ...
>>
>> JR> How did you get negative values in a Serial - field??
>> JR> Serial-fields default to positive numbers....
>>
>> I have copied the data from MS Access table and there where negative
>> values. I can't change data in field Contacts because they are using
>> in other tables.

RH> Speaking of which, what range have you got on that sequence (serial type =
RH> sequence AFAIK)?

RH> It couldn't be that postgres is looking at your WHERE and deciding that the
RH> value is out of range, so there can't be any records to return.

RH> What happens if you insert a new record and then try to look for that?

RH> --
RH>   Richard Huxton
RH>   Archonet Ltd.


RH> ************

I am already found that this is seems a bug in postgres with using of
indexes on int4.

I have created the table

CREATE TABLE "tt" (
        "cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....

2146589610
2146589611
2146589612
\.

About 30 000 records totally

Then I am doing the following:

mdb=> select cc from tt where cc = -2112563299;
         cc
-----------
-2112563299
(1 row)

mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)

mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
         cc
-----------
-2112563299
(1 row)

So, whith index postgresql works incorectly in this situation.
But when there are less records in the table (about 12 000),
everything works well.
I tested this on 3 different computers on Debian Linux 2.1 with
postgresql 6.5.2 and 6.5.3.


Best regards,
 Yury  ICQ 11831432
 mailto:yura@vpcit.ru