Skip site navigation (1) Skip section navigation (2)

BUG 1614 and BUG 1616 : Inadequate GIST Behaviour

From: Sokolov Yura <falcon(at)intercable(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG 1614 and BUG 1616 : Inadequate GIST Behaviour
Date: 2005-04-27 15:07:22
Message-ID: 1832661918.20050427190722@intercable.ru (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello, pgsql-bugs.

Here's smaller example of presentation bugs 1614 and 1616.
Run on PostgreSQL 8.0.1/8.0.2 on Windows and PostgreSQL 8.0.1 on Slackware 10.0 (throw PGAdmin3)

/*
contrib/btree_gist installed
*/
/*tool function for drop table*/
create or replace function drop_try_gist() returns void as $$
begin
  begin
    drop table try_gist;
  exception
    when OTHERS then NULL;
  end;
  return;
end;
$$ language plpgsql;

/*tool function for fill table*/
create or replace function fill_try_gist(fields text[][],counts int[]) returns void as $$
declare
  i  int;
  j  int;
  f_low int;
  f_upp int;
  comm text;
  commt text;
begin
  f_low:=array_lower(fields,2);
  f_upp:=array_upper(fields,2);
  comm:='insert into try_gist ("'|| array_to_string(fields[1:1][f_low:f_upp],'","') || '") values (';
  for i in array_lower(counts,1) .. array_upper(counts,1) loop 
    commt=comm || array_to_string(fields[i+1 : i+1][f_low:f_upp],',') ||')';
    for j in 1 .. counts[i] loop
      execute replace(commt,'#$i$#',j::text);
    end loop;
  end loop;
  return;
end;
$$ language plpgsql;

/*testing gist with text field*/
create or replace function create_try_gist_text() returns void as $$
begin
  perform drop_try_gist();
  CREATE TABLE try_gist
  (
    id serial NOT NULL,
    port varchar(30),
    phone varchar(30),
    CONSTRAINT try_gist_pkey PRIMARY KEY (id)
  ) 
  WITHOUT OIDS;
  return;
end;
$$ language plpgsql;

create or replace function try_gist_text() returns setof int as $$
declare
  i record;
begin
  for i in execute '(select count(*) as c from try_gist where port=''two'' and phone='''')' loop
    return next i.c;
  end loop;
  execute 'create index ix_try_gist on try_gist using gist ((port::text),(phone::text) )';
  for i in execute '(select count(*) as c from try_gist where port=''two'' and phone='''')' loop
    return next i.c;
  end loop;
  return;
end;
$$ language plpgsql;

set enable_seqscan=off;

select create_try_gist_text();
select fill_try_gist('{{"port","phone"},{"''''","''#$i$#''"},{"''two''","''''"}}','{2225,21}');
select * from try_gist_text();
/*returns
try_gist_text(int4)
-------------------
   21
   21
*/

select create_try_gist_text();
select fill_try_gist('{{"port","phone"},{"''two''","''''"},{"''''","''#$i$#''"}}','{21,2225}');
select * from try_gist_text();
/*returns
try_gist_text(int4)
-------------------
   21
   0
*/

vacuum full verbose try_gist;
/* Postgres 8.0.2 Windows
INFO:  vacuuming "public.try_gist"
INFO:  "try_gist": found 0 removable, 2246 nonremovable row versions in 15 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 45 to 48 bytes long.
...
INFO:  index "ix_try_gist" now contains 2246 row versions in 430 pages !!!
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
...
*/
/* Postgres 8.0.1 Slackware 10.0
NFO:  vacuuming "public.try_gist"
INFO:  "try_gist": found 0 removable, 2246 nonremovable row versions in 14 pages
DETAIL:  0 dead row versions cannot be removed yet.
...
INFO:  index "ix_try_gist" now contains 2246 row versions in 628 pages !!!
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
...
*/
create or replace function create_try_gist_int4() returns void as $$
begin
  perform drop_try_gist();
  CREATE TABLE try_gist
  (
    id serial NOT NULL,
    portn int4,
    phonen int4,
    CONSTRAINT try_gist_pkey PRIMARY KEY (id)
  ) 
  WITHOUT OIDS;
  return;
end;
$$ language plpgsql;

create or replace function try_gist_int4() returns setof int as $$
declare
  i record;
begin
  for i in execute '(select count(*) as c from try_gist where portn=10 and phonen=0)' loop
    return next i.c;
  end loop;
  execute 'create index ix_try_gist on try_gist using gist ( portn,phonen )';
  for i in execute '(select count(*) as c from try_gist where portn=10 and phonen=0)' loop
    return next i.c;
  end loop;
  return;
end;
$$ language plpgsql;

select create_try_gist_int4();
select fill_try_gist('{{"portn","phonen"},{"0","#$i$#"},{"10","0"}}','{2225,21}');
select * from try_gist_int4();
/*returns right, but works (on creating index) 6 seconds in 8.0.2/Windows and >18 seconds on 8.0.1/Slackware*/

vacuum full verbose try_gist;
/* Postgres 8.0.2 Windows
INFO:  vacuuming "public.try_gist"
INFO:  "try_gist": found 0 removable, 2246 nonremovable row versions in 15 pages
DETAIL:  0 dead row versions cannot be removed yet.
INFO:  index "ix_try_gist" now contains 2246 row versions in 7603 pages !!!!!!!!
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
*/
/* Postgres 8.0.1 Slackware 10.0
INFO:  vacuuming "public.try_gist"
INFO:  "try_gist": found 0 removable, 2246 nonremovable row versions in 13 pages
DETAIL:  0 dead row versions cannot be removed yet.
...
INFO:  index "ix_try_gist" now contains 2246 row versions in 7603 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
*/

-- 
with regards,
 Sokolov Yura                          mailto:falcon(at)intercable(dot)ru

PS: I ask moderators for removing my previous messages.



pgsql-bugs by date

Next:From: Shelby CainDate: 2005-04-27 15:14:44
Subject: Re: BUG #1630: Wrong conversion in to_date() function. See example.
Previous:From: Ariel CarnaDate: 2005-04-27 15:06:13
Subject: Re: BUG #1630: Wrong conversion in to_date() function. See example.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group