Обсуждение: apparent problem with a PL
Hi,
If I limit a particular query to show 3 results which has a function call
how can the function get called 4 times ? its apparently happening to me.
tradein_clients=# SELECT company_id, utils.pgxml_xpath_pl('/Company@CompanyId' , xml) from
companies where xml is not null limit 3;
INFO: function pgxml_xpath_pl has been called
INFO: function pgxml_xpath_pl has been called
INFO: function pgxml_xpath_pl has been called
INFO: function pgxml_xpath_pl has been called
+------------+----------------+
| company_id | pgxml_xpath_pl |
+------------+----------------+
| 65 | 65 |
| 187 | 187 |
| 382 | 382 |
+------------+----------------+
(3 rows)
but when i filter by a column the behaviour is rite:
tradein_clients=# SELECT company_id, utils.pgxml_xpath_pl('/Company@CompanyId' , xml) from
companies where xml is not null and company_id=65;
INFO: function pgxml_xpath_pl has been called
+------------+----------------+
| company_id | pgxml_xpath_pl |
+------------+----------------+
| 65 | 65 |
+------------+----------------+
(1 row)
tradein_clients=#
output of vacuum full verbose analyze :
( but the faulty behaviour persists)
tradein_clients=# VACUUM FULL Verbose ANALYZE companies ;
INFO: --Relation public.companies--
INFO: Pages 385: Changed 0, reaped 377, Empty 0, New 0; Tup 713: Vac 713, Keep/VTL 0/0, UnUsed 2759, MinLen 244,
MaxLen2033; Re-using: Free/Avail. Space 2525848/2524400; EndEmpty/Avail. Pages 0/374. CPU 0.00s/0.00u sec elapsed
0.00sec.
INFO: Index companies_company_id_key: Pages 15; Tuples 713: Deleted 713. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index companies_keywordidx: Pages 75; Tuples 555: Deleted 555. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index companies_email: Pages 34; Tuples 713: Deleted 713. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Rel companies: Pages: 385 --> 77; Tuple(s) moved: 359. CPU 0.01s/0.05u sec elapsed 0.11 sec.
INFO: Index companies_company_id_key: Pages 15; Tuples 713: Deleted 359. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index companies_keywordidx: Pages 75; Tuples 555: Deleted 329. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Index companies_email: Pages 34; Tuples 713: Deleted 359. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_14656776--
INFO: Pages 300: Changed 300, reaped 0, Empty 0, New 0; Tup 1382: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 37, MaxLen
2034;Re-using: Free/Avail. Space 285232/284268; EndEmpty/Avail. Pages 0/289. CPU 0.00s/0.00u sec elapsed 0.00
sec.
INFO: Index pg_toast_14656776_index: Pages 16; Tuples 1382. CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: Rel pg_toast_14656776: Pages: 300 --> 300; Tuple(s) moved: 0. CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: Analyzing public.companies
VACUUM
tradein_clients=#
CREATE OR REPLACE FUNCTION utils.pgxml_xpath_pl (varchar,text) RETURNS text AS '
use XML::XPath::Simple;
my ($xpath , $xml ) = @_;
my $xp;
elog INFO , "function pgxml_xpath_pl has been called";
eval { $xp = new XML::XPath::Simple(xml => $xml ,context => "/");
};
if ($@)
{ elog ERROR , "There was an error: $@ ";
}
my $content = $xp->valueof($xpath);
return $content;
' LANGUAGE 'plperlu';
Can anyone shed some light
Regds
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
On Sat, 19 Apr 2003, Rajesh Kumar Mallah wrote: > If I limit a particular query to show 3 results which has a function call > how can the function get called 4 times ? its apparently happening to me. In versions before 7.4 I think it grabbed one extra row beyond what you specify for the limit and then doesn't return that row, so if you say limit 3 it gets 4 rows (and thus calls the function 4 times). This appears to be different in 7.4.
Thanks so much for responding so i assume its harmless rite? But the second problem (refer next posting) seems to be serious any comments? do u want me to post more details? Regds Mallah. On Saturday 19 Apr 2003 12:20 pm, Stephan Szabo wrote: > On Sat, 19 Apr 2003, Rajesh Kumar Mallah wrote: > > If I limit a particular query to show 3 results which has a function > > call how can the function get called 4 times ? its apparently happening > > to me. > > In versions before 7.4 I think it grabbed one extra row beyond what you > specify for the limit and then doesn't return that row, so if you say > limit 3 it gets 4 rows (and thus calls the function 4 times). This > appears to be different in 7.4. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Sat, 19 Apr 2003, Rajesh Kumar Mallah wrote: > Thanks so much for responding > so i assume its harmless rite? Basically yes, excepting that any side effects are done 1 "too many" times. > But the second problem (refer next posting) seems to be serious > any comments? do u want me to post more details? Unfortunately I've never build plperl (and I don't think I have a shared libperl) so I'm not really sure. I think there was a message about something similar in the past, so you might be able to find something in the archives (a bug where something was cleared maybe?)
Thanks, Fortunately its not difficult anymore to build plperl on modern redhat linuxes i did not have to recomplie perl for shared libperl , i just used --with-perl option and it worked. regds mallah. On Saturday 19 Apr 2003 10:00 pm, Stephan Szabo wrote: > On Sat, 19 Apr 2003, Rajesh Kumar Mallah wrote: > > Thanks so much for responding > > so i assume its harmless rite? > > Basically yes, excepting that any side effects are done 1 "too many" > times. > > > But the second problem (refer next posting) seems to be serious > > any comments? do u want me to post more details? > > Unfortunately I've never build plperl (and I don't think I have a shared > libperl) so I'm not really sure. I think there was a message about > something similar in the past, so you might be able to find something > in the archives (a bug where something was cleared maybe?) > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.