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:[EMAIL PROTECTED]
PS: I ask moderators for removing my previous messages.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
