pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problem with SELECT on large negative INT4
18+ messages / 5 participants
[nested] [flat]

* Problem with SELECT on large negative INT4
@ 2000-01-27 12:37  Nicolas Huillard <nhuillard@ghs.fr>
  0 siblings, 2 replies; 18+ messages in thread

From: Nicolas Huillard @ 2000-01-27 12:37 UTC (permalink / raw)
  To: 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

I have a DB with is updated using MS Access. Primary keys are Int4 with default random values ("NuméroAuto" + "Aléatoire" in Access).
The DB is migrated as-is in Postgres, with tbl_prod.cle_prod field containing values from -2057496808 to 2139583719.
When I SELECT in the table, using the INT4 cle_prod value, PG doesn't find the tuple. When I SELECT using the VARCHAR(10) ref_prod value, PG finds the tuple, and show the right value for the cle_prod filed : the same as the one I SELECTed for...

This sounds like the long negative integer values given in PSQL is not converted correctly while executing.
Using a long positive integer value, all works like a charm...

Below is the queries type sto sho what append.
I'm using Postgres 6.5.2 from the RPMs.

Nicolas Huillard




gest=> select cle_prod, ref_prod from tbl_prod where ref_prod='4866VC';
   cle_prod|ref_prod
-----------+--------
-2057496808|4866VC  
(1 row)

gest=> select cle_prod, ref_prod from tbl_prod where cle_prod = -2057496808;
cle_prod|ref_prod
--------+--------
(0 rows)

gest=> \d tbl_prod
Table    = tbl_prod
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| cle_prod                         | int4 not null default nextval('" |     4 |
| ref_prod                         | varchar() not null               |    10 |
...
+----------------------------------+----------------------------------+-------+
Index:    tbl_prod_pkey

gest=> select version();
version                                                       
--------------------------------------------------------------
PostgreSQL 6.5.2 on i486-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)

gest=> 




^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 16:30  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Nicolas Huillard <nhuillard@ghs.fr>
  1 sibling, 3 replies; 18+ messages in thread

From: Hiroshi Inoue @ 2000-01-27 16:30 UTC (permalink / raw)
  To: Nicolas Huillard <nhuillard@ghs.fr>; +Cc: pgsql-general@hub.org; pgsql-sql@hub.org

> -----Original Message-----
> From: owner-pgsql-general@postgresql.org
> [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
>
> I have a DB with is updated using MS Access. Primary keys are
> Int4 with default random values ("NuméroAuto" + "Aléatoire" in Access).
> The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> field containing values from -2057496808 to 2139583719.
> When I SELECT in the table, using the INT4 cle_prod value, PG
> doesn't find the tuple. When I SELECT using the VARCHAR(10)
> ref_prod value, PG finds the tuple, and show the right value for
> the cle_prod filed : the same as the one I SELECTed for...
>
> This sounds like the long negative integer values given in PSQL
> is not converted correctly while executing.
> Using a long positive integer value, all works like a charm...
>
> Below is the queries type sto sho what append.
> I'm using Postgres 6.5.2 from the RPMs.
>

Could you try the follwoing patch ?

Regards.

Hiroshi Inoue

inline patch 1: nbtcompare.c 2000/01/19 05:42:06 (+6/-1 lines) download inline-1.patch*** nbtcompare.c 2000/01/11 03:33:34 1.3 --- nbtcompare.c 2000/01/19 05:42:06 *************** *** 34,40 **** int32 btint4cmp(int32 a, int32 b) { ! return a - b; } int32 --- 34,45 ---- int32 btint4cmp(int32 a, int32 b) { ! if (a > b) ! return 1; ! else if (a == b) ! return 0; ! else ! return -1; } int32
^ permalink raw reply [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 16:37  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  2 siblings, 2 replies; 18+ messages in thread

From: Bruce Momjian @ 2000-01-27 16:37 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: Nicolas Huillard <nhuillard@ghs.fr>; pgsql-general@hub.org; pgsql-sql@hub.org

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: owner-pgsql-general@postgresql.org
> > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
> >
> > I have a DB with is updated using MS Access. Primary keys are
> > Int4 with default random values ("Num_roAuto" + "Al_atoire" in Access).
> > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > field containing values from -2057496808 to 2139583719.
> > When I SELECT in the table, using the INT4 cle_prod value, PG
> > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > ref_prod value, PG finds the tuple, and show the right value for
> > the cle_prod filed : the same as the one I SELECTed for...
> >
> > This sounds like the long negative integer values given in PSQL
> > is not converted correctly while executing.
> > Using a long positive integer value, all works like a charm...
> >
> > Below is the queries type sto sho what append.
> > I'm using Postgres 6.5.2 from the RPMs.
> >
> 
> Could you try the follwoing patch ?

Hiroshi, I don't see this in the main tree, nor do I see it having been
requested for application. Should I apply it?

> 
> Regards.
> 
> Hiroshi Inoue
> 
> *** nbtcompare.c        2000/01/11 03:33:34     1.3
> --- nbtcompare.c        2000/01/19 05:42:06
> ***************
> *** 34,40 ****
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       return a - b;
>   }
> 
>   int32
> --- 34,45 ----
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       if (a > b)
> !               return 1;
> !       else if (a == b)
> !               return 0;
> !       else
> !               return -1;
>   }
> 
>   int32
> 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 17:52  Nicolas Huillard <nhuillard@ghs.fr>
  0 siblings, 1 reply; 18+ messages in thread

From: Nicolas Huillard @ 2000-01-27 17:52 UTC (permalink / raw)
  To: pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

I haven't got the opportunity to recompile Postgres right now, but will do that. Just reading the code, I can't figure out the exact difference, but who knows...
Did someone already applied this ?

Nicolas Huillard

-----Message d'origine-----
De:	Hiroshi Inoue [SMTP:Inoue@tpf.co.jp]
Date:	jeudi 27 janvier 2000 17:31
À:	Nicolas Huillard
Cc:	pgsql-general@hub.org; pgsql-sql@hub.org
Objet:	RE: [GENERAL] Problem with SELECT on large negative INT4

> -----Original Message-----
> From: owner-pgsql-general@postgresql.org
> [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
>
> I have a DB with is updated using MS Access. Primary keys are
> Int4 with default random values ("NuméroAuto" + "Aléatoire" in Access).
> The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> field containing values from -2057496808 to 2139583719.
> When I SELECT in the table, using the INT4 cle_prod value, PG
> doesn't find the tuple. When I SELECT using the VARCHAR(10)
> ref_prod value, PG finds the tuple, and show the right value for
> the cle_prod filed : the same as the one I SELECTed for...
>
> This sounds like the long negative integer values given in PSQL
> is not converted correctly while executing.
> Using a long positive integer value, all works like a charm...
>
> Below is the queries type sto sho what append.
> I'm using Postgres 6.5.2 from the RPMs.
>

Could you try the follwoing patch ?

Regards.

Hiroshi Inoue

inline patch 1: nbtcompare.c 2000/01/19 05:42:06 (+6/-1 lines) download inline-1.patch*** nbtcompare.c 2000/01/11 03:33:34 1.3 --- nbtcompare.c 2000/01/19 05:42:06 *************** *** 34,40 **** int32 btint4cmp(int32 a, int32 b) { ! return a - b; } int32 --- 34,45 ---- int32 btint4cmp(int32 a, int32 b) { ! if (a > b) ! return 1; ! else if (a == b) ! return 0; ! else ! return -1; } int32
************ ^ permalink raw reply [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 17:52  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 18+ messages in thread

From: Tom Lane @ 2000-01-27 17:52 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Hiroshi Inoue <Inoue@tpf.co.jp>; pgsql-sql@hub.org; pgsql-hackers@postgreSQL.org

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Could you try the follwoing patch ?

> Hiroshi, I don't see this in the main tree, nor do I see it having been
> requested for application. Should I apply it?

If this does fix things, I would venture that the blame really belongs
to whatever caller of the comparison function is failing to interpret
the return value correctly ...

BTW there is a somewhat-related issue that has been bothering me:
the system is pretty erratic about converting between Datum and bool.
Some places use "DatumGetInt32(datum) != 0" as their effective test
for whether a Datum value is "true", some places use
"DatumGetInt8(datum) != 0", some places just do "(bool) datum", and
there may be other variants.

I am thinking of introducing DatumGetBool and BoolGetDatum macros and
trying to make the code use those uniformly, but there's a lot of code
to clean up.  I was intending to do it during the function manager
interface rewrite, since all these same places will need to be looked
at for that.

Perhaps we should also try to clean up btree's sort comparison functions
in the same way, with some macros that provide a well-defined conversion
between Datum and the "<", "=", ">" return conditions that are intended.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 17:59  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Nicolas Huillard <nhuillard@ghs.fr>
  0 siblings, 0 replies; 18+ messages in thread

From: Bruce Momjian @ 2000-01-27 17:59 UTC (permalink / raw)
  To: Nicolas Huillard <nhuillard@ghs.fr>; +Cc: pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

[Charset iso-8859-1 unsupported, filtering to ASCII...]
> I haven't got the opportunity to recompile Postgres right now, but will do that. Just reading the code, I can't figure out the exact difference, but who knows...
> Did someone already applied this ?

No, not sure how to deal with it.

> 
> Nicolas Huillard
> 
> -----Message d'origine-----
> De:	Hiroshi Inoue [SMTP:Inoue@tpf.co.jp]
> Date:	jeudi 27 janvier 2000 17:31
> _:	Nicolas Huillard
> Cc:	pgsql-general@hub.org; pgsql-sql@hub.org
> Objet:	RE: [GENERAL] Problem with SELECT on large negative INT4
> 
> > -----Original Message-----
> > From: owner-pgsql-general@postgresql.org
> > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
> >
> > I have a DB with is updated using MS Access. Primary keys are
> > Int4 with default random values ("Num_roAuto" + "Al_atoire" in Access).
> > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > field containing values from -2057496808 to 2139583719.
> > When I SELECT in the table, using the INT4 cle_prod value, PG
> > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > ref_prod value, PG finds the tuple, and show the right value for
> > the cle_prod filed : the same as the one I SELECTed for...
> >
> > This sounds like the long negative integer values given in PSQL
> > is not converted correctly while executing.
> > Using a long positive integer value, all works like a charm...
> >
> > Below is the queries type sto sho what append.
> > I'm using Postgres 6.5.2 from the RPMs.
> >
> 
> Could you try the follwoing patch ?
> 
> Regards.
> 
> Hiroshi Inoue
> 
> *** nbtcompare.c        2000/01/11 03:33:34     1.3
> --- nbtcompare.c        2000/01/19 05:42:06
> ***************
> *** 34,40 ****
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       return a - b;
>   }
> 
>   int32
> --- 34,45 ----
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       if (a > b)
> !               return 1;
> !       else if (a == b)
> !               return 0;
> !       else
> !               return -1;
>   }
> 
>   int32
> 
> 
> 
> ************
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-27 23:59  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 0 replies; 18+ messages in thread

From: Hiroshi Inoue @ 2000-01-27 23:59 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Nicolas Huillard <nhuillard@ghs.fr>; pgsql-general@hub.org; pgsql-sql@hub.org

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> 
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > > -----Original Message-----
> > > From: owner-pgsql-general@postgresql.org
> > > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of 
> Nicolas Huillard
> > >
> > > I have a DB with is updated using MS Access. Primary keys are
> > > Int4 with default random values ("Num_roAuto" + "Al_atoire" 
> in Access).
> > > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > > field containing values from -2057496808 to 2139583719.
> > > When I SELECT in the table, using the INT4 cle_prod value, PG
> > > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > > ref_prod value, PG finds the tuple, and show the right value for
> > > the cle_prod filed : the same as the one I SELECTed for...
> > >
> > > This sounds like the long negative integer values given in PSQL
> > > is not converted correctly while executing.
> > > Using a long positive integer value, all works like a charm...
> > >
> > > Below is the queries type sto sho what append.
> > > I'm using Postgres 6.5.2 from the RPMs.
> > >
> > 
> > Could you try the follwoing patch ?
> 
> Hiroshi, I don't see this in the main tree, nor do I see it having been
> requested for application. Should I apply it?
>

Recently I have often seen this kind of bug reports though I don't
know why * recently *.
This is the second time I sent the patch but I have seen no reply.

Anyway,this is clearly a bug.
I could commit it to current tree but couldn't commit to REL tree
because I don't maintain REL tree.
Moreover int42cmp/int24cmp seems to have similar bugs and
we had better check comparison functions again.
I'm happy if you could commit it to both trees.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 03:48  John Brothers <johnbr@mindspring.com>
  parent: Nicolas Huillard <nhuillard@ghs.fr>
  1 sibling, 0 replies; 18+ messages in thread

From: John Brothers @ 2000-01-28 03:48 UTC (permalink / raw)
  To: Nicolas Huillard <nhuillard@ghs.fr>; 'pgsql-general@hub.org' <pgsql-general@hub.org>; 'pgsql-sql@hub.org' <pgsql-sql@hub.org>

We have the same problem with 64-bit integers - and it isn't just
psql - the same problem exists in jdbc.

Anyways, to fix it, we did the following:

'-11233232322233232323232321232'::int8

whenever we did any operation on the large field.

you may want to try 

'-2057496808|'::int4

in the same manner.



At 01:37 PM 1/27/00 +0100, Nicolas Huillard wrote:
>I have a DB with is updated using MS Access. Primary keys are Int4 with 
>default random values ("NuméroAuto" + "Aléatoire" in Access).
>The DB is migrated as-is in Postgres, with tbl_prod.cle_prod field 
>containing values from -2057496808 to 2139583719.
>When I SELECT in the table, using the INT4 cle_prod value, PG doesn't find 
>the tuple. When I SELECT using the VARCHAR(10) ref_prod value, PG finds the 
>tuple, and show the right value for the cle_prod filed : the same as the one 
>I SELECTed for...
>
>This sounds like the long negative integer values given in PSQL is not 
>converted correctly while executing.
>Using a long positive integer value, all works like a charm...
>
>Below is the queries type sto sho what append.
>I'm using Postgres 6.5.2 from the RPMs.
>
>Nicolas Huillard
>
>
>
>
>gest=> select cle_prod, ref_prod from tbl_prod where ref_prod='4866VC';
>   cle_prod|ref_prod
>-----------+--------
>-2057496808|4866VC  
>(1 row)
>
>gest=> select cle_prod, ref_prod from tbl_prod where cle_prod = -2057496808;
>cle_prod|ref_prod
>--------+--------
>(0 rows)
>
>gest=> \d tbl_prod
>Table    = tbl_prod
>+----------------------------------+----------------------------------+----
---+
>|              Field               |              Type                |
Length|
>+----------------------------------+----------------------------------+----
---+
>| cle_prod                         | int4 not null default nextval('" |
 4 |
>| ref_prod                         | varchar() not null               |
10 |
>...
>+----------------------------------+----------------------------------+----
---+
>Index:    tbl_prod_pkey
>
>gest=> select version();
>version                                                       
>--------------------------------------------------------------
>PostgreSQL 6.5.2 on i486-pc-linux-gnu, compiled by gcc 2.7.2.3
>(1 row)
>
>gest=> 
>
>
>************

------------
johnbr@incanta.net

        



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 03:51  John Brothers <johnbr@mindspring.com>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  2 siblings, 2 replies; 18+ messages in thread

From: John Brothers @ 2000-01-28 03:51 UTC (permalink / raw)
  To: pgsql-general@hub.org; pgsql-sql@hub.org

I don't think that patch will work - Hiroshi whipped up that patch for me a
week ago for a
different problem - we have a table with duplicate primary keys, which
seems to be
an arithmetic overflow problem because the index key values can be both
very large positive
and very large negative numbers.


At 11:37 AM 1/27/00 -0500, Bruce Momjian wrote:
>> Could you try the follwoing patch ?
>
>Hiroshi, I don't see this in the main tree, nor do I see it having been
>requested for application. Should I apply it?
>
>> 
>> Regards.
>> 
>> Hiroshi Inoue

------------
johnbr@incanta.net

        



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 04:40  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: John Brothers <johnbr@mindspring.com>
  1 sibling, 0 replies; 18+ messages in thread

From: Hiroshi Inoue @ 2000-01-28 04:40 UTC (permalink / raw)
  To: John Brothers <johnbr@mindspring.com>; +Cc: pgsql-general@hub.org; pgsql-sql@hub.org

> -----Original Message-----
> From: owner-pgsql-general@postgresql.org
> [mailto:owner-pgsql-general@postgresql.org]On Behalf Of John Brothers
> 
> I don't think that patch will work - Hiroshi whipped up that 
> patch for me a
> week ago for a
> different problem - we have a table with duplicate primary keys, which
> seems to be
> an arithmetic overflow problem because the index key values can be both
> very large positive
> and very large negative numbers.
>

What was the result on your environment after applying my patch ?
My patch workded for your test case at least on my machine.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 04:49  Tom Lane <tgl@sss.pgh.pa.us>
  parent: John Brothers <johnbr@mindspring.com>
  1 sibling, 2 replies; 18+ messages in thread

From: Tom Lane @ 2000-01-28 04:49 UTC (permalink / raw)
  To: John Brothers <johnbr@mindspring.com>; +Cc: pgsql-general@hub.org; pgsql-sql@hub.org

John Brothers <johnbr@mindspring.com> writes:
> I don't think that patch will work - Hiroshi whipped up that patch for
> me a week ago for a different problem - we have a table with duplicate
> primary keys, which seems to be an arithmetic overflow problem because
> the index key values can be both very large positive and very large
> negative numbers.

Actually, if Nicolas' table contains both very large positive and very
large negative integers, then his index could be messed up pretty badly.
What Hiroshi saw (and I missed :-() was that btint4cmp can fail and
return a result of the wrong sign if the difference between two integers
overflows.  Since index sorting depends critically on the assumption
that the comparator always returns consistent results (a < b and b < c
must imply a < c, but this can fail if a - c overflows), you could have
an out-of-order index.  And then probes into the index could fail to
find items they should find ... which is exactly the complained-of
symptom.

Hiroshi neglected to mention that you'd probably need to drop and
recreate the index after applying the patch; if it's indeed out of
order, just patching the comparator bug isn't enough to fix it.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 05:31  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 18+ messages in thread

From: Bruce Momjian @ 2000-01-28 05:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Brothers <johnbr@mindspring.com>; pgsql-general@hub.org; pgsql-sql@hub.org

> Actually, if Nicolas' table contains both very large positive and very
> large negative integers, then his index could be messed up pretty badly.
> What Hiroshi saw (and I missed :-() was that btint4cmp can fail and
> return a result of the wrong sign if the difference between two integers
> overflows.  Since index sorting depends critically on the assumption
> that the comparator always returns consistent results (a < b and b < c
> must imply a < c, but this can fail if a - c overflows), you could have
> an out-of-order index.  And then probes into the index could fail to
> find items they should find ... which is exactly the complained-of
> symptom.
> 
> Hiroshi neglected to mention that you'd probably need to drop and
> recreate the index after applying the patch; if it's indeed out of
> order, just patching the comparator bug isn't enough to fix it.
> 

Does Hiroshi's patch get applied?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 05:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 18+ messages in thread

From: Tom Lane @ 2000-01-28 05:59 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: John Brothers <johnbr@mindspring.com>; pgsql-general@hub.org; pgsql-sql@hub.org

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Hiroshi neglected to mention that you'd probably need to drop and
>> recreate the index after applying the patch; if it's indeed out of
>> order, just patching the comparator bug isn't enough to fix it.

> Does Hiroshi's patch get applied?

Now that I understand the problem, yes, his patch is good as far as
it goes.  There are probably other places with the same problem...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 07:05  Hiroshi Inoue <Inoue@tpf.co.jp>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 18+ messages in thread

From: Hiroshi Inoue @ 2000-01-28 07:05 UTC (permalink / raw)
  To: John Brothers <johnbr@mindspring.com>; +Cc: pgsql-general@hub.org; pgsql-sql@hub.org; Tom Lane <tgl@sss.pgh.pa.us>

> -----Original Message-----
> From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL.
> org]On Behalf Of Tom Lane
> 
> Hiroshi neglected to mention that you'd probably need to drop and
> recreate the index after applying the patch; if it's indeed out of

Oops I neglected to mention it,sorry.

> order, just patching the comparator bug isn't enough to fix it.

Yes,please report us the result.
If the result is wrong,we have to think more about it.

Regards. 

Hiroshi Inoue
Inoue@tpf.co.jp



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 17:19  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Hiroshi Inoue <Inoue@tpf.co.jp>
  2 siblings, 0 replies; 18+ messages in thread

From: Bruce Momjian @ 2000-01-28 17:19 UTC (permalink / raw)
  To: Hiroshi Inoue <Inoue@tpf.co.jp>; +Cc: Nicolas Huillard <nhuillard@ghs.fr>; pgsql-general@hub.org; pgsql-sql@hub.org

Applied.


[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: owner-pgsql-general@postgresql.org
> > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
> >
> > I have a DB with is updated using MS Access. Primary keys are
> > Int4 with default random values ("Num_roAuto" + "Al_atoire" in Access).
> > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > field containing values from -2057496808 to 2139583719.
> > When I SELECT in the table, using the INT4 cle_prod value, PG
> > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > ref_prod value, PG finds the tuple, and show the right value for
> > the cle_prod filed : the same as the one I SELECTed for...
> >
> > This sounds like the long negative integer values given in PSQL
> > is not converted correctly while executing.
> > Using a long positive integer value, all works like a charm...
> >
> > Below is the queries type sto sho what append.
> > I'm using Postgres 6.5.2 from the RPMs.
> >
> 
> Could you try the follwoing patch ?
> 
> Regards.
> 
> Hiroshi Inoue
> 
> *** nbtcompare.c        2000/01/11 03:33:34     1.3
> --- nbtcompare.c        2000/01/19 05:42:06
> ***************
> *** 34,40 ****
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       return a - b;
>   }
> 
>   int32
> --- 34,45 ----
>   int32
>   btint4cmp(int32 a, int32 b)
>   {
> !       if (a > b)
> !               return 1;
> !       else if (a == b)
> !               return 0;
> !       else
> !               return -1;
>   }
> 
>   int32
> 
> 
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 17:19  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 18+ messages in thread

From: Bruce Momjian @ 2000-01-28 17:19 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: John Brothers <johnbr@mindspring.com>; pgsql-general@hub.org; pgsql-sql@hub.org

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> Hiroshi neglected to mention that you'd probably need to drop and
> >> recreate the index after applying the patch; if it's indeed out of
> >> order, just patching the comparator bug isn't enough to fix it.
> 
> > Does Hiroshi's patch get applied?
> 
> Now that I understand the problem, yes, his patch is good as far as
> it goes.  There are probably other places with the same problem...

Applied.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 21:41  Nicolas Huillard <nhuillard@ghs.fr>
  0 siblings, 0 replies; 18+ messages in thread

From: Nicolas Huillard @ 2000-01-28 21:41 UTC (permalink / raw)
  To: pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

Thank to everybody, I recompiled Postgres and tested it on the same DB : the offending SELECT worked, without any index regeneration. Maybe the order in which the items where inserted in the table (using COPY, before creating the index) made it working.
I only had problems with the source RPMs : I installed it (6.5.2), added a patch file for this, recompiled, but wasn't able to get brand new RPM package for installing on my other machines...
What I did for testing my compilation with the fresh "postgres" binary, is installing it in /usr/bin, in place of the other one. Is it the only think to do, or will there be side effects if I don't install more binaries ?
Maybe Lamar Owen could help me getting a fresh patched RPM ?

Nicolas Huillard

-----Message d'origine-----
De:	Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Date:	vendredi 28 janvier 2000 05:49
À:	John Brothers
Cc:	pgsql-general@hub.org; pgsql-sql@hub.org
Objet:	Re: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4 

John Brothers <johnbr@mindspring.com> writes:
> I don't think that patch will work - Hiroshi whipped up that patch for
> me a week ago for a different problem - we have a table with duplicate
> primary keys, which seems to be an arithmetic overflow problem because
> the index key values can be both very large positive and very large
> negative numbers.

Actually, if Nicolas' table contains both very large positive and very
large negative integers, then his index could be messed up pretty badly.
What Hiroshi saw (and I missed :-() was that btint4cmp can fail and
return a result of the wrong sign if the difference between two integers
overflows.  Since index sorting depends critically on the assumption
that the comparator always returns consistent results (a < b and b < c
must imply a < c, but this can fail if a - c overflows), you could have
an out-of-order index.  And then probes into the index could fail to
find items they should find ... which is exactly the complained-of
symptom.

Hiroshi neglected to mention that you'd probably need to drop and
recreate the index after applying the patch; if it's indeed out of
order, just patching the comparator bug isn't enough to fix it.

			regards, tom lane

************





^ permalink  raw  reply  [nested|flat] 18+ messages in thread

* RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4
@ 2000-01-28 22:31  Nicolas Huillard <nhuillard@ghs.fr>
  0 siblings, 0 replies; 18+ messages in thread

From: Nicolas Huillard @ 2000-01-28 22:31 UTC (permalink / raw)
  To: pgsql-general@hub.org <pgsql-general@hub.org>; pgsql-sql@hub.org <pgsql-sql@hub.org>

A little adjustment : SOME queries work with the new binary and the old indexes. Many other don't, as Tom and Hiroshi figured.
With a new binary and after reloding the DB, every seems to work correctly.

Nicolas Huillard

-----Message d'origine-----
De:	Nicolas Huillard [SMTP:nhuillard@ghs.fr]
Date:	vendredi 28 janvier 2000 22:42
À:	pgsql-general@hub.org; pgsql-sql@hub.org
Objet:	RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4 

Thank to everybody, I recompiled Postgres and tested it on the same DB : the offending SELECT worked, without any index regeneration. Maybe the order in which the items where inserted in the table (using COPY, before creating the index) made it working.
 ... 



^ permalink  raw  reply  [nested|flat] 18+ messages in thread


end of thread, other threads:[~2000-01-28 22:31 UTC | newest]

Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-27 12:37 Problem with SELECT on large negative INT4 Nicolas Huillard <nhuillard@ghs.fr>
2000-01-27 16:30 ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-01-27 16:37   ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-27 17:52     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-27 23:59     ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-01-28 03:51   ` John Brothers <johnbr@mindspring.com>
2000-01-28 04:40     ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-01-28 04:49     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-28 05:31       ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-28 05:59         ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-28 17:19           ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-28 07:05       ` Hiroshi Inoue <Inoue@tpf.co.jp>
2000-01-28 17:19   ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-28 03:48 ` John Brothers <johnbr@mindspring.com>
2000-01-27 17:52 RE: [GENERAL] Problem with SELECT on large negative INT4 Nicolas Huillard <nhuillard@ghs.fr>
2000-01-27 17:59 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-28 21:41 RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4 Nicolas Huillard <nhuillard@ghs.fr>
2000-01-28 22:31 RE: [SQL] RE: [GENERAL] Problem with SELECT on large negative INT4 Nicolas Huillard <nhuillard@ghs.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox