BUG #13449: Auto type cast (int -> numeric) non-reasonable, will case performance problem

From: digoal(at)126(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13449: Auto type cast (int -> numeric) non-reasonable, will case performance problem
Date: 2015-06-17 02:17:07
Message-ID: 20150617021707.2740.62807@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13449
Logged by: digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 9.4.4
Operating system: CentOS 6.x x64
Description:

When i use an big digital, it auto convert to numeric. and there is no
int&numeric operator, so left opr auto convert to numeric also.
for exp:
postgres=# create table t3(id int);
CREATE TABLE
postgres=# insert into t3 select generate_series(1,10000000);
INSERT 0 10000000
postgres=# create index idx_t3_id on t3(id);
CREATE INDEX
postgres=# explain analyze select * from t3 where
id>999999999999999999999999999999999;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_t3_id on t3 (cost=0.43..238213.43 rows=3333333
width=4) (actual time=4052.914..4052.914 rows=0 loops=1)
Filter: ((id)::numeric > '999999999999999999999999999999999'::numeric)
Rows Removed by Filter: 10000000
Heap Fetches: 10000000
Planning time: 0.283 ms
Execution time: 4052.944 ms
(6 rows)

postgres=# explain analyze select * from t3 where
id=999999999999999999999999999999999;
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_t3_id on t3 (cost=0.43..238213.43 rows=50000
width=4) (actual time=3907.391..3907.391 rows=0 loops=1)
Filter: ((id)::numeric = '999999999999999999999999999999999'::numeric)
Rows Removed by Filter: 10000000
Heap Fetches: 10000000
Planning time: 0.103 ms
Execution time: 3907.421 ms
(6 rows)

I think ,this case, PostgreSQL should convert
999999999999999999999999999999999 to the same type with column id's type
int. and raise error.

there is some problem, user can use this to SQL injection attack or other
things, Oops, Application has ability to filter the overflow digital, but i
think PostgreSQL also has responsibility to prevent overflow occure.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Paquier 2015-06-17 03:45:16 Re: BUG #13443: master will remove dead rows when hot standby(use slot) disconnect
Previous Message Thomas Munro 2015-06-17 01:25:53 Re: BUG #13440: unaccent does not remove all diacritics