[GENERAL] PL/Perl
Hi! Could I use "use", "require" functions in plperl? for example, CREATE OR REPLACE FUNCTION perl_func (text) RETURNS real AS ' use HTTP::Request; use HTTP::Headers; return $value; ' LANGUAGE 'plperl'; with me it doesn't work and returns error message "Query failed: ERROR: creation of function failed: 'require' trapped by operation mask at (eval 2) line 2. in ..." Thanx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PL/Perl
Hi All!
I'm trying in 'plperl' forking the processes by 'fork' function,
but receiving this message
Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork'
trapped by operation mask at (eval 2) line 11.
Does it mean, that in 'plperl' I can't use 'fork' function???
function example
=
CREATE OR REPLACE FUNCTION perl_fork_test ()
RETURNS int2
AS '
my %pid;
my @urls = (
"http://domain1.com/index.php";,
"http://domain2.com/index.php";
);
foreach my $url (@urls) {
unless ($pid{$url} = fork) {
my $html = qx/GET "$url"/;
$ENV{TERM} = &my_exit;
sub my_exit {
exit(0);
}
}
}
sleep 6;
foreach my $url (keys %pid) {
kill SIGTERM, $pid{$url};
}
wait;
return 1;
'
LANGUAGE 'plperl';
===
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
===
Thanx
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] Pl/;perl
Hello All, I´m trying to install pl/perl in my windows machine, but for some reason it just wont work. createlang wont work, create language doesnt work either. i have activeperl 5.1 installed and pgsql 8.3 it returns me this error: 126 unknown - could not load library. any tips? Thanks George -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl/perl problem
Title: RE: [GENERAL] Convert Cursor to array hi i wrote a store procedure using the pl/perlU language, and the comportment is strange. my procedure do a select on my database and some traitments too and write the result in a file; when i run the procedure a first time, it works fine, the file is create and data are in. but when i run my procedure a second time, the file is create but the data aren't write in it. where is the problem ? i had an other problem the past week, but i not able to reproduce it. it was a very simple funtion who store a string into a variable and display it on the screen : something like this : my $toto = '->'; $toto.='titi'; elog NOTICE, $toto; the problem was : the first time i ran the procedure and i get : ->titi and the second time : ->titititi, etc the variable $toto wasn't reinitialize. maybe somebody had the same problem. (if i have enough time, i will post the code) thanks in advance Will
Re: [GENERAL] PL/Perl
Hello, you have to use plperlu, untrusted plperl regards Pavel Stehule On Fri, 7 Jan 2005, ON.KG wrote: > Hi! > > Could I use "use", "require" functions in plperl? > > for example, > > CREATE OR REPLACE FUNCTION perl_func (text) > RETURNS real > AS ' > use HTTP::Request; > use HTTP::Headers; > > return $value; > ' > LANGUAGE 'plperl'; > > > with me it doesn't work and returns error message > "Query failed: ERROR: creation of function failed: 'require' trapped by > operation mask at (eval 2) line 2. in ..." > > Thanx > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PL/Perl
with me it doesn't work and returns error message "Query failed: ERROR: creation of function failed: 'require' trapped by operation mask at (eval 2) line 2. in ..." You need to use plperlu. Sincerely, Joshua D. Drake Thanx ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl
On Tue, 2005-01-11 at 14:59 +0300, ON.KG wrote: > I'm trying in 'plperl' forking the processes by 'fork' function, > but receiving this message > > Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork' > trapped by operation mask at (eval 2) line 11. > > Does it mean, that in 'plperl' I can't use 'fork' function??? there are 2 variants of the language: plperl and plperlu plperlu is 'untrusted', that is, it is allowed to do dangerous stuff, but plperl is more limited and can be used by a user without privileges. OTOH, i doubt that fork is allowed even in plperlu, as i imagine it could have weird effects on the backend. gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PL/Perl
ON.KG wrote: Hi All! I'm trying in 'plperl' forking the processes by 'fork' function, but receiving this message Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork' trapped by operation mask at (eval 2) line 11. Does it mean, that in 'plperl' I can't use 'fork' function??? Think about it - you're trying to fork an active database backend. Personally, I'd decouple things and fetch webpages/files from outside the database anyway. You might find the documentation on NOTIFY/LISTEN useful. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/Perl
ON.KG wrote:
Hi All!
I'm trying in 'plperl' forking the processes by 'fork' function,
but receiving this message
Have you tried performing this with plperlU?
Sincerely,
Joshua D. Drake
Warning: pg_exec(): Query failed: ERROR: creation of function failed: 'fork'
trapped by operation mask at (eval 2) line 11.
Does it mean, that in 'plperl' I can't use 'fork' function???
function example
=
CREATE OR REPLACE FUNCTION perl_fork_test ()
RETURNS int2
AS '
my %pid;
my @urls = (
"http://domain1.com/index.php";,
"http://domain2.com/index.php";
);
foreach my $url (@urls) {
unless ($pid{$url} = fork) {
my $html = qx/GET "$url"/;
$ENV{TERM} = &my_exit;
sub my_exit {
exit(0);
}
}
}
sleep 6;
foreach my $url (keys %pid) {
kill SIGTERM, $pid{$url};
}
wait;
return 1;
'
LANGUAGE 'plperl';
===
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
===
Thanx
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Pl/;perl
George R. C. Silva wrote: > i have activeperl 5.1 installed and pgsql 8.3 You'll need ActivePerl 5.8 . -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl/perl error
I have a perl script running as a daemon. It's using DBD::Pg (1.43) to
connect to my Postgres server (8.0.7) running on the same box and talking
over a socket. When I start the server, it runs fine for about a day, and
then at some point I start getting this error repeatedly:
DBD::Pg::db do failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
There are only two calls to DBI->do in my script which amount to:
update foo set ipaddr = null where ipaddr = '$ip' and mac != '$mac'
and
update foo set ipaddr = '$ip' where mac = '$mac'
The first statement is run fairly often and I believe it's the one
resulting in the error. Usually when it is run, it doesn't actually modify
the database, i.e. mac will == $mac.
As soon as I restart my script, it runs fine again for about a day and then
it blows up in the same fashion.
My table is set up like:
CREATE TABLE foo (
macmacaddr UNIQUE,
ipaddr inetUNIQUE
);
There are two triggers on this table:
create trigger new_mac
AFTER INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE new_foo_fx();
create trigger remove_mac
BEFORE DELETE ON macs
FOR EACH ROW EXECUTE PROCEDURE remove_foo_fx();
Neither of these procedures modify what the statement does to the database.
They make some external changes and then "return;". The only hit I see in
Google for this error message refers to the trigger documentation which
talks about the return value of triggers. However, section 32.1 (Overview
of Trigger Behavior) and section 37.6 (PL/Perl Triggers) seem to disagree.
I went with the PL/Perl example assuming that it does the Right Thing
behind the scenes.
At first I thought this was my script losing connection to the database, so
I started using DBI->connect_cached but that didn't change anything.
Neither of my calls to DBI->do() seem to throw an error, i.e.
$dbh->do($statement) or { warn $dbh->errstr }
doesn't output anything.
I just increased my postgres debug to -d 2 to see if that provides any
useful information. Does anyone else have any debugging suggestions or
know what might be causing this problem?
Thanks,
Frank
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[GENERAL] PL/Perl questions...
Hi List;
I have 2 questions...
1) I want to create a perl function which connects to an external non-Postgres database and retrieves data. I wonder is the best way to do this to return a set or an array? I assume returning a data set is a better way to go, I want to be able to run something like this:
"select a,b,c from my_new_perl_func('odbc-conn', 'sql-string') as t1(a,b,c)";
2) can anyone provide me with an example of how to return data from a perl function in this manner based on a DBI query?
Thanks in advance for your help...
Re: [GENERAL] PL/Perl
when i did what's described below, i had no problems at all with PL/Perl (and, in fact, am using it for a couple of triggers that are lightly used in a production environment) http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/pl/plperl/README?rev=1.2&content-type=text/x-cvsweb- markup you can email me directly if you're still having trouble regards ed.bridges On Mon, 13 Nov 2000 09:58:31 -0600 (CST), Wade D. Oberpriller wrote: > Are there any caveats as to which versions PL/Perl works with? > > I am running PostgreSQL v7.0.2 and Perl 5.005_03, and I built postgres using the > --with-perl option. I built the plperl code and copied the resulting plperl.so > into my postgres distributions lib directory. I am able to do a createlang for > plperl, but when I execute any code in a stored procedure, the backend crashes. > > Any body have any ideas? > > Wade Oberpriller > StorageTek > [EMAIL PROTECTED] >
Re: [GENERAL] PL/Perl
n.b. to clarify, i got PL/Perl working on RH Linux 6.2 am in the process of attempting to get it working on NT. ==BEGIN FORWARDED MESSAGE== when i did what's described below, i had no problems at all with PL/Perl (and, in fact, am using it for a couple of triggers that are lightly used in a production environment) http://www.postgresql.org/cgi/cvsweb.cgi/pgsql/src/pl/plperl/README?rev=1.2&content-type=text/x-cvsweb- markup you can email me directly if you're still having trouble regards ed.bridges On Mon, 13 Nov 2000 09:58:31 -0600 (CST), Wade D. Oberpriller wrote: > Are there any caveats as to which versions PL/Perl works with? > > I am running PostgreSQL v7.0.2 and Perl 5.005_03, and I built postgres using the > --with-perl option. I built the plperl code and copied the resulting plperl.so > into my postgres distributions lib directory. I am able to do a createlang for > plperl, but when I execute any code in a stored procedure, the backend crashes. > > Any body have any ideas? > > Wade Oberpriller > StorageTek > [EMAIL PROTECTED] > ===END FORWARDED MESSAGE===
[GENERAL] Pl/Perl speed
I am pretty sure I know this already, but every time you run a Pl/Perl function it is just like running a perl script as far as having to load and compile the code right? My application runs under mod perl so I'm thinking that speed is not something I would gain by putting any of the code into the database server via Pl/Perl. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] PL/Perl documentation ...
Is there available some more examples of writing functions in PL/Perl ? In standard documentation there is only just 2 examples. Thanks in advance for any help. Adam -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] pl-perl setup?
Hello, I'm fairly new to PostgreSQL. Does anyone have a resource for setting up plperl from scratch? The online documentation doesn't offer much help. -- Jeff Boes vox 616.226.9550 Database Engineer fax 616.349.9076 Nexcerpt, Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PL/Perl compilation error Re: [GENERAL] PL/Perl compilation error
Tom Lane wrote: > Alex Guryanow <[EMAIL PROTECTED]> writes: > > [root@eaccess plperl]# perl Makefile.PL > > For recent Perl versions you need to do > perl Makefile.PL POLLUTE=1 > instead. The src/pl Makefile would've done it that way for you, > but it looks like that code patch didn't make it to the docs... > > Someone needs to update our Perl code so that it will compile cleanly > against both newer and not-so-new Perls. There are notes in our mail > archives about how to do this (basically "use Devel::PPPort" is the > long-term answer) but it hasn't gotten to the top of anyone's to-do > list. Can someone eventually enlighten me a little? We'vehad problems like platform/version dependant compilation errors with PL/Tcl in the past too, but they got fixed pretty quick and a reasonable number of people worked on that all together. We have frequent compilation error reports with PL/perl but nobody seems to be able/willing to do anything about it. PL/perl was once highly requested feature. Now there is a code base and lesser experienced programmers could continue the work, but nobody does. What is the problem with perl? Are there only alot of users but no hackers? The frequent fail reports suggest that there are folks who want to have that thing running. I can't believe that a piece of open source software, that is so popular, is implemented in such an ugly way that nobody has a clue how to fix that damned thing. So please tell me why people spend their time writing error reports again and again instead of simply fixing it and submitting a patch. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] Convert Cursor to array
my
function is very long but i found an example with the same comportment
:
CREATE
OR REPLACE FUNCTION adoc.totoTest() RETURNS int4
AS$BODY$ my $var =
'->>>'; &concat($var);
sub concat { $var .=
'tagada'; } elog NOTICE, $var; return 4;
$BODY$ LANGUAGE 'plperl' VOLATILE;
first
execution : ->>>tagada
second
execution : ->>>
(for
my second problem, i not able to reproduce iti deleted the source
code)
but
what means 'use strict' ?
thanks
-Message d'origine-De : Sean Davis
[mailto:[EMAIL PROTECTED]Envoyé : lundi 21 mars 2005
16:46À : FERREIRA William (COFRAMI);
pgsql-general@postgresql.orgObjet : Re: [GENERAL] pl/perl
problem
- Original Message -
From:
FERREIRA William (COFRAMI)
To: 'pgsql-general@postgresql.org'
Sent: Monday, March 21, 2005 9:22
AM
Subject: [GENERAL] pl/perl
problem
hi
i
wrote a store procedure using the pl/perlU language, and the comportment is
strange.
my
procedure do a select on my database and some traitments too and write the
result in a file;
when i run the procedure a first time, it works fine, the file is
create and data are in. but when i run my procedure a second time, the file
is create but the data aren't write in it. where is the problem
?
Can you show the
function?
i
had an other problem the past week, but i not able to reproduce it. it was a
very simple funtion who store a string into a variable and display
it on the screen : something like this :
my
$toto = '->';
$toto.='titi';
elog NOTICE, $toto;
Again, can you
show the whole function? And do you 'use strict' when
coding?
Re: [GENERAL] pl/perl problem
FERREIRA William (COFRAMI) wrote:
my function is very long but i found an example with the same comportment :
CREATE OR REPLACE FUNCTION adoc.totoTest()
RETURNS int4 AS
$BODY$
my $var = '->>>';
&concat($var);
sub concat {
$var .= 'tagada';
}
elog NOTICE, $var;
return 4;
$BODY$
LANGUAGE 'plperl' VOLATILE;
first execution : ->>>tagada
second execution : ->>>
In the example above $var in sub concat is NOT an argument provided to
the function. What you've done there is create a named closure (if I'm
getting my terms right) in which the inner $var is allocated on first
call but not afterwards. The second time you run totoTest() the outer
$var (my $var) is a new variable, whereas the inner one still refers to
the original.
If you actually want to return a concatenated string you'd want
something like:
sub concat {
my $var = shift;
return $var . 'tagada';
}
If you want to affect an outer variable you'll want something like
sub concat {
my $var_ref = shift;
$$var_ref .= 'tagada';
}
Does that help?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] pl/perl problem
On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:
my function is very long but i found an example with the same
comportment :
CREATE OR REPLACE FUNCTION adoc.totoTest()
RETURNS int4 AS
$BODY$
my $var = '->>>';
&concat($var);
sub concat {
$var .= 'tagada';
}
elog NOTICE, $var;
return 4;
$BODY$
LANGUAGE 'plperl' VOLATILE;
first execution : ->>>tagada
second execution : ->>>
Here is a slightly modified version of your code that does what you
want, I think. A couple of things:
1) If you want to pass arguments to a subroutine, what you do above
won't work.
2) You have to be careful in perl when you modify variables that you
know the scope of the variables (where they will be seen versus not)
that you are modifying.
3) If you want a subroutine to modify the value of a variable passed
to it, you need to pass a REFERENCE to that variable, not the value of
the variable.
CREATE OR REPLACE FUNCTION adoc.totoTest2() RETURNS int4 AS
$BODY$
use strict; #see below for explanation
my $var = '->>>';
concat(\$var); #use a reference to the variable
elog NOTICE, $var;
return 4;
sub concat {
my $ref=shift;#get a REFERENCE to the variable
${$ref} .= 'tagada'; #this dereferences the variable and modifies it
}
$BODY$
LANGUAGE 'plperl' VOLATILE;
(for my second problem, i not able to reproduce iti deleted the
source code)
but what means 'use strict' ?
See this article
http://perl.about.com/od/perlforbeginners/l/aa081701a.htm
Sean
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] pl/perl problem
thanks a lot
with your example and the example of Richard it works fine
-Message d'origine-
De : Sean Davis [mailto:[EMAIL PROTECTED]]
Envoyé : mardi 22 mars 2005 12:51
À : FERREIRA William (COFRAMI)
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem
On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same
> comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
> RETURNS int4 AS
> $BODY$
> my $var = '->>>';
> &concat($var);
>
>
> sub concat {
> $var .= 'tagada';
> }
> elog NOTICE, $var;
> return 4;
>
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
>
> first execution : ->>>tagada
> second execution : ->>>
Here is a slightly modified version of your code that does what you
want, I think. A couple of things:
1) If you want to pass arguments to a subroutine, what you do above
won't work.
2) You have to be careful in perl when you modify variables that you
know the scope of the variables (where they will be seen versus not)
that you are modifying.
3) If you want a subroutine to modify the value of a variable passed
to it, you need to pass a REFERENCE to that variable, not the value of
the variable.
CREATE OR REPLACE FUNCTION adoc.totoTest2() RETURNS int4 AS
$BODY$
use strict; #see below for explanation
my $var = '->>>';
concat(\$var); #use a reference to the variable
elog NOTICE, $var;
return 4;
sub concat {
my $ref=shift; #get a REFERENCE to the variable
${$ref} .= 'tagada'; #this dereferences the variable and modifies it
}
$BODY$
LANGUAGE 'plperl' VOLATILE;
>
> (for my second problem, i not able to reproduce iti deleted the
> source code)
> but what means 'use strict' ?
>
>
See this article
http://perl.about.com/od/perlforbeginners/l/aa081701a.htm
Sean
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] pl/perl problem
yes, it works
exactly what i needed, thanks a lot
-Message d'origine-
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : mardi 22 mars 2005 12:41
À : FERREIRA William (COFRAMI)
Cc : 'Sean Davis'; pgsql-general@postgresql.org
Objet : Re: [GENERAL] pl/perl problem
FERREIRA William (COFRAMI) wrote:
> my function is very long but i found an example with the same comportment :
> CREATE OR REPLACE FUNCTION adoc.totoTest()
> RETURNS int4 AS
> $BODY$
> my $var = '->>>';
> &concat($var);
>
> sub concat {
> $var .= 'tagada';
> }
> elog NOTICE, $var;
> return 4;
>
> $BODY$
> LANGUAGE 'plperl' VOLATILE;
>
> first execution : ->>>tagada
> second execution : ->>>
In the example above $var in sub concat is NOT an argument provided to
the function. What you've done there is create a named closure (if I'm
getting my terms right) in which the inner $var is allocated on first
call but not afterwards. The second time you run totoTest() the outer
$var (my $var) is a new variable, whereas the inner one still refers to
the original.
If you actually want to return a concatenated string you'd want
something like:
sub concat {
my $var = shift;
return $var . 'tagada';
}
If you want to affect an outer variable you'll want something like
sub concat {
my $var_ref = shift;
$$var_ref .= 'tagada';
}
Does that help?
--
Richard Huxton
Archonet Ltd
This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.
Re: [GENERAL] pl/perl problem
Title: RE: [GENERAL] Convert Cursor to array - Original Message - From: FERREIRA William (COFRAMI) To: 'pgsql-general@postgresql.org' Sent: Monday, March 21, 2005 9:22 AM Subject: [GENERAL] pl/perl problem hi i wrote a store procedure using the pl/perlU language, and the comportment is strange. my procedure do a select on my database and some traitments too and write the result in a file; when i run the procedure a first time, it works fine, the file is create and data are in. but when i run my procedure a second time, the file is create but the data aren't write in it. where is the problem ? Can you show the function? i had an other problem the past week, but i not able to reproduce it. it was a very simple funtion who store a string into a variable and display it on the screen : something like this : my $toto = '->'; $toto.='titi'; elog NOTICE, $toto; Again, can you show the whole function? And do you 'use strict' when coding?
Re: [GENERAL] pl/perl error
Frank <[EMAIL PROTECTED]> writes: > I have a perl script running as a daemon. It's using DBD::Pg (1.43) to > connect to my Postgres server (8.0.7) running on the same box and talking > over a socket. When I start the server, it runs fine for about a day, and > then at some point I start getting this error repeatedly: > > DBD::Pg::db do failed: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. There should be something in the server logs that corresponds to this--what do they say? -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pl/perl error
Frank <[EMAIL PROTECTED]> writes: > I have a perl script running as a daemon. It's using DBD::Pg (1.43) to > connect to my Postgres server (8.0.7) running on the same box and talking > over a socket. When I start the server, it runs fine for about a day, and > then at some point I start getting this error repeatedly: > DBD::Pg::db do failed: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. Do you see any complaint in the postmaster log when this happens? If so what? I'd personally wonder about a memory leak or something like that in your trigger functions. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pl/perl error
On Sun, Apr 09, 2006 at 06:22:40PM -0400, Tom Lane wrote: > Frank <[EMAIL PROTECTED]> writes: > > I have a perl script running as a daemon. It's using DBD::Pg (1.43) to > > connect to my Postgres server (8.0.7) running on the same box and talking > > over a socket. When I start the server, it runs fine for about a day, and > > then at some point I start getting this error repeatedly: > > > DBD::Pg::db do failed: server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > Do you see any complaint in the postmaster log when this happens? If so > what? > > I'd personally wonder about a memory leak or something like that in your > trigger functions. > > regards, tom lane > After running with debugging at level 2, I saw that the script died at 00:57:35. Attached is the log from that time. I noticed specifically the following lines: Apr 9 00:57:35 MYSERVER postgres[7881]: [511892-1] LOG: disconnection: session time: 9:33:45.60 user=dbuser database=mydb host=[local] port= Apr 9 00:57:35 MYSERVER postgres[7874]: [13-1] DEBUG: server process (PID 7881) exited with exit code 0 Apr 9 00:57:35 MYSERVER postgres[7874]: [14-1] DEBUG: forked new backend, pid=10009 socket=7 I don't see those lines at all before the problems begin, but I notice them very often afterwards. Would I see the memory leak in the postgres footprint? I will attempt to monitor the server's memory usage. Any other debugging tips? Thanks, Frank ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/Perl questions...
On Fri, Jun 16, 2006 at 11:42:51AM -0500, LLC wrote:
> Hi List;
>
> I have 2 questions...
>
> 1) I want to create a perl function which connects to an external
> non-Postgres database and retrieves data. I wonder is the best way
> to do this to return a set or an array? I assume returning a data
> set is a better way to go, I want to be able to run something like
> this:
>
> "select a,b,c from my_new_perl_func('odbc-conn', 'sql-string') as t1(a,b,c)";
DBI-Link does something a lot like this.
http://pgfoundry.org/projects/dbi-link/
> 2) can anyone provide me with an example of how to return data from
> a perl function in this manner based on a DBI query?
See above :)
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] PL/Perl Performance Problems
Hi, I am experiencing some strange behavior when executing a not too complicated pl/perl function. The Function is not too complicated. It does... 1. Selects about 20 Records from Table A ( - loops though the list and deletes in total about 50k records in Table B2. For each record form Table A it then selects Records from Table C - loops through these records about 50K in total - for each runs a query 3 Tables, 10-20M records - inserts a record in Table B .. about 50K3. Returns some stats on the whole operation (100 records). I am using PL/Perl for this and everything runs pretty well. In a single execution i.e. if I only process 1 records the whole process is done within a few milliseconds. The system has 16BG of Memory, and fast disks. Now here is what I noticed. a) if I run it in the morning, processing starts very slow, but after a few thousand records it will speed up until I actually get about 100 records processed per millisecond. b) it sometime takes about 5-10k records till i really get up to speed. meaning the first few hundreds can take up to 1-2 minutes. c) if i run the same job a few hrs later (we run it twice a day) it generally runs much faster. Even though we added more data to one of the big tables it selects from. d) this however starts again the next day. ( not much data has been changed between the 2nd run of the day and the first one of the next one, but yet it will start crawling again. e) sometime the 2nd run of the day can also be slow and even though the data in the system does not change by a large margin, run times of the jobs vary by a large amount. from 17-50 minutes. Here are my questions: A) I am running the Auto vacuum. Is it possible that this for some reason slows down the system? B) Are the query planner stats re-set over night i.e. date change? This behavior is consistent. i.e. Every morning the processing is slow, afternoon generally much faster. C) Does pl/perl have some memory issues? D) If i run above job processing 50 records, but stop the process after 10K, then run it again it will run fast for the first 10K then slow down. My assumption is that the query planner keeps the stats for a particular record based on a time stamp (used in a select) and every day it will forget about that. Is there a way to let the query planner keep stats for a function permanently? Thanks for any advise. Alex _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
[GENERAL] pl/perl setof function
Hi,i have a PL/PERL RETURN SETOF function which processes a few 10k records.The processing takes quite some time and in order to display progressI use a return_next after every few thousand records. However, the function returns all messages when it completes and exists which is not really what i want. I also tried the elog NOTICE but then I only get the messages back to the psql. I am calling the function from a perl script and would like to catch these progress messages. Is there any way to solve my problem? ThanksAlex _ Search for properties that match your lifestyle! Start searching NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
[GENERAL] PL/Perl compilation error
Hi, I have done a little work concerning the famous PL/Perl compilation Error and also into Interfaces/Perl5. The confusing POLLUTE option is no more used to see these parts compiled. I thinks it's now fully compatible with all Perl versions, yes Tom I use PPPort :-) The way to put it into the distribution package is very simple. 1) Replace the current GNUmakefile in these directories src/interface/perl5 and src/pl/plperl by those given in the attachment. 2) Copy the lastest version of the ppport.h file into the same directories (latest can be found on CPAN) I provide one in the attachment (the latest at this day Version 1.0007) That done, just compile postgresql exactly as before (with ./configure --with-perl at least). What I have done is very simple : - cp Devel-PPPort-1.0007/ppport.h postgresql-snapshotsrc/pl/plperl/ - cp Devel-PPPort-1.0007/ppport.h postgresql-snapshot/src/interfaces/perl5/ And in the 2 GNUmakefile in the "Makefile: Makefile.PL" section: - I have remove the call to the POLLUTE option - Added the following lines at the begining of the section: $(PERL) -x ppport.h *.c *.h *.xs > ppport.patch patch < ppport.patch rm ppport.patch Thanks to Kenneth Albanowski for his PPPort.pm usefull package and to Tom Lane for his ligth. Note: the attachment is a tar of all modified and added files in the source tree. Regards, Gilles DAROLD ppport-change.tar.gz
Re: [GENERAL] Pl/Perl speed
"Chris Ochs" <[EMAIL PROTECTED]> writes: > I am pretty sure I know this already, but every time you run a Pl/Perl > function it is just like running a perl script as far as having to load and > compile the code right? No, the perl script gets compiled only the first time the function is invoked (within a given backend session). Subsequent calls to the function use the already-compiled function object. I'm not clear on exactly how much overhead this saves in Perl, but it's certainly not as bad as re-loading a script on each call. I believe all the PL languages have comparable optimizations. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Pl/Perl speed
Chris Ochs wrote: I am pretty sure I know this already, but every time you run a Pl/Perl function it is just like running a perl script as far as having to load and compile the code right? My application runs under mod perl so I'm thinking that speed is not something I would gain by putting any of the code into the database server via Pl/Perl. You can also preload (if you are running Mammoth or 7.4) the plperl.so which should give you a speed increase. It entirely depends on what you are trying to do with pl/Perl however. If you need access to tuple data (ability to modify records etc...) then your kind of out of luck. However, if you are using it to perform logic and then spit back the results pl/Perl could be very useful and give a speed increase. The speed increase would come from only returning the results of your calculation versus returning a set of data and then performing calculations on that set and then displaying it. Sincerely, Joshua D. Drake Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl Question
Hitesh Patel <[EMAIL PROTECTED]> writes: > I need to be able to execute SQL commands from inside a plperl > function and can't figure if I can do this. Also.. after executing > the query I need to be able to grab the number of tuples returned > and there values.. how would I go about doing this.. any help would > be appreciated. As I understand it, PL/Perl doesn't currently have an interface to the server backend, so all you can really do with it is string and number manipulation. You'll have to use Tcl or PL/PGSQL for your functions. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] PL/Perl in 7.1.1
I recently upgraded from 7.0.3 to 7.1. Everything went fine until I went to install plperl using createlang. It throws the following error message: ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: /usr/lib/perl5/5.6.0/i686-linux/auto/Opcode/Opcode.so: undefined symbol: Perl_save_vptr createlang: language installation failed. I verified the plperl.so file compiled without problems and is installed correctly at /usr/local/pgsql/lib/. Any ideas? -- Jeff Patterson Site Administrator The Melanoma Patients' Information Page http://www.mpip.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pl/perl problem of memory
Title: pl/perl problem of memory Hi, I load data in a varible of type text, and i arrive to out of memory message. I want to know if it exists a function which allows to empty a variable and the place uses in memory without destroy it (because I use the variable again). In fact, now i put the variable to '' ( $myvar = '' ) but it seems that Perl don't empty totally memory. Best regards, Nicolas Giroire
[GENERAL] PL/PERL: raise notice, exception ?
Hi, Is there in PL/PERL, under PG 8.01, an equivalent for the "raise notice, exception" commands of PL/PGSQL? Philippe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pl/perl autonomous transactions question
I would like to use autonomous transactions for a large batch process and I want this all encapsulated within stored procedures. I want to commit after say every 15,000 records. The only way I have found to do this is to use the perl DBI in my stored procedure to establish a new connection to the database.
1. Is there any way to tell the DBI connection to use the current credtials just with a new connection? 2. Is there any way to get the spi call to create a new connection instead of using the connection it is called with?
One issue I see with my current DBI solution is I need to hard code or pass in as variables the connection information. I would prefer not to have the password lying around in plain site. Keep in mind this is a batch process not a something I that is called manually where a user is
going to be entering their username and password in.Any help or ideas would be great.Below is a simple example to demonstrate. CREATE TABLE test_values ( c1 SERIAL, c2 VARCHAR (200));
CREATE OR REPLACE FUNCTION proc_perl_test_insert() RETURNS VOID AS $$use DBI;my $db_host = 'localhost';my $db_user = 'postgres';my $db_pass = 'somepassword';my $db_name = 'dev';elog(NOTICE,"Executeing proc_perl_test_insert");
#Creates a new connection so that an autonomous transactions can take place independent of main transaction.#INSERT INTO test_values (c2) VALUES ('Autonomous Transaction') will commit regardless if the calling transaction fails or is rolled back.
my $db = "DBI:PgPP:dbname=${db_name};host=${db_host}";my $dbh=DBI->connect("DBI:PgPP:dbname=dev;host=localhost","postgres", "c21993b"); if ($dbh) { my $sth = $dbh->prepare("INSERT INTO test_values (c2) VALUES ('Autonomous Transaction')");
$sth->execute(); }#This inserts using spi_exec_query and will only commit if the calling transaction commits. for ($count=1; $count<2; $count++){ my $query = qq{
INSERT INTO test_values ( c2 ) VALUES ( 'Non Autonomous Transaction' ) }; my $rv = spi_exec_query($query);}$$ LANGUAGE plperlu;--Now Test the pl/perl function from psql and use a outer transaction
START TRANSACTION;SELECT proc_perl_test_insert(); ROLLBACK TRANSACTION;SELECT * FROM test_values;--HERE IS MY psql commands being run as you can see it does what I want in that it commits my one statement but not the other
dev=# START TRANSACTION;START TRANSACTIONTime: 0.000 msdev=# SElECT * FROM proc_perl_test_insert();NOTICE: Running proc_perl_test_insert proc_perl_test_insert---(1 row)
Time: 70.000 msdev=# ROLLBACK TRANSACTION;ROLLBACKTime: 0.000 msdev=# select * from test_values; c1| c2-+ 2898364 | Autonomous Transaction
(1 row)Time: 0.000 msdev=#Regards,Bob Henkel
Re: [GENERAL] PL/Perl Performance Problems
Alex - writes: > Now here is what I noticed. > a) if I run it in the morning, processing starts very slow, but after a few > thousand records it will speed up until I actually get about 100 records > processed per millisecond. > b) it sometime takes about 5-10k records till i really get up to speed. > meaning the first few hundreds can take up to 1-2 minutes. > c) if i run the same job a few hrs later (we run it twice a day) it generally > runs much faster. Even though we added more data to one of the big tables it > selects from. > d) this however starts again the next day. ( not much data has been changed > between the 2nd run of the day and the first one of the next one, but yet it > will start crawling again. What this sounds like is it's fast when most of the data has been swapped in to kernel disk cache, and slow when the data actually has to be read from disk. Probably the reason it's slow in the morning is there are some unrelated tasks that run overnight and cause memory to get filled with all their data instead of the PG tables. You could check this theory by watching with vmstat or similar tool to see how much actual disk I/O is happening in the slow and fast cases. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I actually looked at that too, but there is no swapping going on. The system also has 16GB memory allocated to postgres and during the processing there is not other process really active. > To: ainto...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] PL/Perl Performance Problems > Date: Fri, 18 Dec 2009 15:25:16 -0500 > From: t...@sss.pgh.pa.us > > Alex - writes: > > Now here is what I noticed. > > a) if I run it in the morning, processing starts very slow, but after a few > > thousand records it will speed up until I actually get about 100 records > > processed per millisecond. > > b) it sometime takes about 5-10k records till i really get up to speed. > > meaning the first few hundreds can take up to 1-2 minutes. > > c) if i run the same job a few hrs later (we run it twice a day) it > > generally runs much faster. Even though we added more data to one of the > > big tables it selects from. > > d) this however starts again the next day. ( not much data has been changed > > between the 2nd run of the day and the first one of the next one, but yet > > it will start crawling again. > > What this sounds like is it's fast when most of the data has been > swapped in to kernel disk cache, and slow when the data actually has to > be read from disk. Probably the reason it's slow in the morning is > there are some unrelated tasks that run overnight and cause memory to > get filled with all their data instead of the PG tables. You could > check this theory by watching with vmstat or similar tool to see how > much actual disk I/O is happening in the slow and fast cases. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Alex - writes: > I actually looked at that too, but there is no swapping going on. If you were only watching for swapping, that wouldn't catch what I'm talking about. Replacing cached disk buffers with other disk data doesn't count as swapping in any system I've used. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Thank, I will check that out. > To: ainto...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] PL/Perl Performance Problems > Date: Fri, 18 Dec 2009 15:36:15 -0500 > From: t...@sss.pgh.pa.us > > Alex - writes: > > I actually looked at that too, but there is no swapping going on. > > If you were only watching for swapping, that wouldn't catch what I'm > talking about. Replacing cached disk buffers with other disk data > doesn't count as swapping in any system I've used. > > regards, tom lane _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
2248 1661 609 5 0 74 21 0 1 0 1468 294088 106768 1712195200 2032 2290 1586 752 4 0 74 21 0 1 1 1468 278312 107084 1713626400 2548 2706 1711 914 6 0 75 19 0 0 2 1468 244156 107208 1716823200 2284 3154 1635 858 6 0 74 19 0 2 0 1468 68 107420 1718557200 2035 3388 1555 717 12 0 71 17 0 0 1 1464 229088 107464 1719996000 1279 5878 2048 302 21 0 73 6 0 1 0 1464 220524 107488 1720880400 1116 3497 1301 270 19 0 75 6 0 1 0 1464 204912 107520 1722420800 1321 3519 1321 282 20 0 75 5 0 1 0 1464 182580 107556 1724698400 1446 3726 1371 329 20 0 75 5 0 1 0 1464 170096 107600 1725962000 1270 3300 1307 289 20 0 75 5 0 0 1 1464 148800 107636 1728065600 1393 3710 1351 319 19 0 75 6 0 1 0 1464 136432 107700 1729218800 1384 3291 1308 291 19 0 75 5 0 1 0 1464 118672 107808 1730830800 1383 3656 1365 410 21 1 73 6 0 0 1 1464 110880 107840 173170240 0 1534 6340 2207 403 17 1 74 8 0 1 0 1464 106996 107848 173210240 0 1142 7217 2932 340 15 1 75 10 0 0 1 1464 93096 107876 17335540 00 1268 7923 2944 387 14 1 74 11 0 0 1 1464 109156 107900 17318800 00 1333 7776 2871 380 15 1 75 9 0 0 1 1464 93360 107928 1733510000 1300 8944 3393 378 17 1 75 8 0 0 1 1464 96200 107972 1733175200 1136 8365 3101 369 16 1 74 9 0 1 0 1464 105692 108020 1732183200 1171 7041 2744 352 14 1 75 11 0 1 0 1464 106068 108056 1732128800 1176 6564 2765 347 14 0 75 11 0 0 1 1464 100424 108100 1732699600 1236 6687 2755 360 14 1 74 10 0 1 0 1464 104532 108140 1732268000 1194 5106 2230 342 13 0 75 12 0 1 0 1464 98196 107216 1732838800 1238 6720 2611 352 15 1 75 10 0 > To: ainto...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] PL/Perl Performance Problems > Date: Fri, 18 Dec 2009 15:36:15 -0500 > From: t...@sss.pgh.pa.us > > Alex - writes: > > I actually looked at that too, but there is no swapping going on. > > If you were only watching for swapping, that wouldn't catch what I'm > talking about. Replacing cached disk buffers with other disk data > doesn't count as swapping in any system I've used. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Get more out of Hotmail Check out the latest features today http://windowslive.ninemsn.com.au/hotmail/article/878466/your-hotmail-is-about-to-get-even-better
Re: [GENERAL] PL/Perl Performance Problems
Alex - wrote: > > Tom, I am logging these stats now, but i am having a similar issue. both jobs > in the morning and after noon insert about 400k records (200k each) > In the morning that job takes 450secIn the afternoon only 150; No select, > simple parsing a file and insert the records > These two tables don't grow as at night a cleanup job removes about the same > amount of historical data (older about 40 days). > Its kind of the same behavior and i don't really see where the swapping fits > in. Are you running vacuum of some sort on this table regularly? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I run AutovacuumI run a Vaccum Full Analyze every SatI re-index the tables every Sat > Date: Fri, 18 Dec 2009 21:20:23 -0300 > From: alvhe...@commandprompt.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > Subject: Re: [GENERAL] PL/Perl Performance Problems > > Alex - wrote: > > > > Tom, I am logging these stats now, but i am having a similar issue. both > > jobs in the morning and after noon insert about 400k records (200k each) > > In the morning that job takes 450secIn the afternoon only 150; No select, > > simple parsing a file and insert the records > > These two tables don't grow as at night a cleanup job removes about the > > same amount of historical data (older about 40 days). > > Its kind of the same behavior and i don't really see where the swapping > > fits in. > > Are you running vacuum of some sort on this table regularly? > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Note that you seem to have a lot more IO wait in the first run than in the second, which means that the task is hitting the disks more in the first run than in the second one. Once IO wait starts to climb, performance starts to dive, generally. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Hmm...how can that be. This is happening every day, so its not a one off or happens once in the morning then in the afternoon. There is also no other task running on the system, its dedicated to postgres. Could the Autovacuum cause problems? Starting to invoke Analyze at the beginning of the day but the keep silent till the day timestamp breaks ? The think is that I have 4 servers setup in a similar way and all have exactly the same problem. > Date: Fri, 18 Dec 2009 23:00:16 -0700 > Subject: Re: [GENERAL] PL/Perl Performance Problems > From: scott.marl...@gmail.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > > Note that you seem to have a lot more IO wait in the first run than in > the second, which means that the task is hitting the disks more in the > first run than in the second one. Once IO wait starts to climb, > performance starts to dive, generally. _ Use Messenger in your Hotmail inbox Find out how http://windowslive.ninemsn.com.au/hotmail/article/823454/web-im-for-hotmail-is-here
Re: [GENERAL] PL/Perl Performance Problems
On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: > Hmm... > how can that be. This is happening every day, so its not a one off or > happens once in the morning then in the afternoon. There is also no other > task running on the system, its dedicated to postgres. > Could the Autovacuum cause problems? Starting to invoke Analyze at the > beginning of the day but the keep silent till the day timestamp breaks ? > The think is that I have 4 servers setup in a similar way and all have > exactly the same problem. What cron jobs are on that machine that run at night? Note that on many OSes, maintenance crons are scheduled in a dir something like /etc/cron.daily etc... On my laptop they all run at midnight. I'm wondering if they're blowing out your cache so that you just don't have the same performance the first time you hit a particular dataset after they've run. Just a guess. You could try disabling them for a day and see what happens. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
I have the standard ones and no others. # run-parts01 * * * * root run-parts /etc/cron.hourly02 4 * * * root run-parts /etc/cron.daily22 4 * * 0 root run-parts /etc/cron.weekly42 4 1 * * root run-parts /etc/cron.monthly ls -l /etc/cron*-rw-r--r-- 1 root root0 Apr 6 2009 /etc/cron.deny-rw-r--r-- 1 root root 255 Jan 6 2007 /etc/crontab/etc/cron.d:-rw-r--r-- 1 root root 192 Mar 12 2009 sysstat/etc/cron.daily:-rwxr-xr-x 1 root root 379 Mar 28 2007 0anacronlrwxrwxrwx 1 root root 39 Apr 6 2009 0logwatch -> /usr/share/logwatch/scripts/logwatch.pl-rwxr-xr-x 1 root root 118 Jan 21 2009 cups-rwxr-xr-x 1 root root 180 Mar 12 2009 logrotate-rwxr-xr-x 1 root root 418 Jan 6 2007 makewhatis.cron-rwxr-xr-x 1 root root 137 Mar 12 2009 mlocate.cron-rwxr-xr-x 1 root root 2181 Jun 21 2006 prelink-rwxr-xr-x 1 root root 296 Jan 21 2009 rpm-rwxr-xr-x 1 root root 328 Feb 26 2009 tmpwatch/etc/cron.hourly:-rwxr-xr-x 1 root root 71 Jan 10 2007 mcelog.cron/etc/cron.monthly:-rwxr-xr-x 1 root root 381 Mar 28 2007 0anacron/etc/cron.weekly:-rwxr-xr-x 1 root root 380 Mar 28 2007 0anacron-rwxr-xr-x 1 root root 414 Jan 6 2007 makewhatis.cron > Date: Fri, 18 Dec 2009 23:45:07 -0700 > Subject: Re: [GENERAL] PL/Perl Performance Problems > From: scott.marl...@gmail.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > > On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: > > Hmm... > > how can that be. This is happening every day, so its not a one off or > > happens once in the morning then in the afternoon. There is also no other > > task running on the system, its dedicated to postgres. > > Could the Autovacuum cause problems? Starting to invoke Analyze at the > > beginning of the day but the keep silent till the day timestamp breaks ? > > The think is that I have 4 servers setup in a similar way and all have > > exactly the same problem. > > What cron jobs are on that machine that run at night? Note that on > many OSes, maintenance crons are scheduled in a dir something like > /etc/cron.daily etc... On my laptop they all run at midnight. I'm > wondering if they're blowing out your cache so that you just don't > have the same performance the first time you hit a particular dataset > after they've run. Just a guess. You could try disabling them for a > day and see what happens. _ A world FIRST in property search has arrived! Check out Domain Radar NOW! http://clk.atdmt.com/NMN/go/157631292/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
On a 2nd thought... where does the cach come into play when i only do inserts and no selects. Alex > Date: Fri, 18 Dec 2009 23:45:07 -0700 > Subject: Re: [GENERAL] PL/Perl Performance Problems > From: scott.marl...@gmail.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > > On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: > > Hmm... > > how can that be. This is happening every day, so its not a one off or > > happens once in the morning then in the afternoon. There is also no other > > task running on the system, its dedicated to postgres. > > Could the Autovacuum cause problems? Starting to invoke Analyze at the > > beginning of the day but the keep silent till the day timestamp breaks ? > > The think is that I have 4 servers setup in a similar way and all have > > exactly the same problem. > > What cron jobs are on that machine that run at night? Note that on > many OSes, maintenance crons are scheduled in a dir something like > /etc/cron.daily etc... On my laptop they all run at midnight. I'm > wondering if they're blowing out your cache so that you just don't > have the same performance the first time you hit a particular dataset > after they've run. Just a guess. You could try disabling them for a > day and see what happens. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Looking for a great date? Meet singles at ninemsn dating http://clk.atdmt.com/NMN/go/150855801/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
According to your original post, you do selects in step 1 and 2... Or is this a different job and I've lost the thread (happens to me plenty :) ) 1. Selects about 20 Records from Table A ( - loops though the list and deletes in total about 50k records in Table B 2. For each record form Table A it then selects Records from Table C - loops through these records about 50K in total - for each runs a query 3 Tables, 10-20M records - inserts a record in Table B .. about 50K 3. Returns some stats on the whole operation (100 records). On Sat, Dec 19, 2009 at 12:07 AM, Alex - wrote: > On a 2nd thought... where does the cach come into play when i only do > inserts and no selects. > Alex > >> Date: Fri, 18 Dec 2009 23:45:07 -0700 >> Subject: Re: [GENERAL] PL/Perl Performance Problems >> From: scott.marl...@gmail.com >> To: ainto...@hotmail.com >> CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org >> >> On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: >> > Hmm... >> > how can that be. This is happening every day, so its not a one off or >> > happens once in the morning then in the afternoon. There is also no >> > other >> > task running on the system, its dedicated to postgres. >> > Could the Autovacuum cause problems? Starting to invoke Analyze at the >> > beginning of the day but the keep silent till the day timestamp breaks ? >> > The think is that I have 4 servers setup in a similar way and all have >> > exactly the same problem. >> >> What cron jobs are on that machine that run at night? Note that on >> many OSes, maintenance crons are scheduled in a dir something like >> /etc/cron.daily etc... On my laptop they all run at midnight. I'm >> wondering if they're blowing out your cache so that you just don't >> have the same performance the first time you hit a particular dataset >> after they've run. Just a guess. You could try disabling them for a >> day and see what happens. >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > Meet singles at ninemsn dating Looking for a great date? -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Yes I do, but this is the pl/perl function called by a batch job i run. before the pl/perl function is called i insert 2x200k records into 2 tables (200k per table). First i thought that it might be a problem with the perl function, but then i noticed that it even started earlier with the simple inserts. after the insert the job will call the function and there i have the same issues. runs slow in the morning, and fast in the afternoon. it will pick up speed after 5-10k records thanks for your help > Date: Sat, 19 Dec 2009 00:10:36 -0700 > Subject: Re: [GENERAL] PL/Perl Performance Problems > From: scott.marl...@gmail.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > > According to your original post, you do selects in step 1 and 2... Or > is this a different job and I've lost the thread (happens to me plenty > :) ) > > 1. Selects about 20 Records from Table A ( >- loops though the list and deletes in total about 50k records in Table B > 2. For each record form Table A it then selects Records from Table C >- loops through these records about 50K in total >- for each runs a query 3 Tables, 10-20M records >- inserts a record in Table B .. about 50K > 3. Returns some stats on the whole operation (100 records). > > On Sat, Dec 19, 2009 at 12:07 AM, Alex - wrote: > > On a 2nd thought... where does the cach come into play when i only do > > inserts and no selects. > > Alex > > > >> Date: Fri, 18 Dec 2009 23:45:07 -0700 > >> Subject: Re: [GENERAL] PL/Perl Performance Problems > >> From: scott.marl...@gmail.com > >> To: ainto...@hotmail.com > >> CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > >> > >> On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: > >> > Hmm... > >> > how can that be. This is happening every day, so its not a one off or > >> > happens once in the morning then in the afternoon. There is also no > >> > other > >> > task running on the system, its dedicated to postgres. > >> > Could the Autovacuum cause problems? Starting to invoke Analyze at the > >> > beginning of the day but the keep silent till the day timestamp breaks ? > >> > The think is that I have 4 servers setup in a similar way and all have > >> > exactly the same problem. > >> > >> What cron jobs are on that machine that run at night? Note that on > >> many OSes, maintenance crons are scheduled in a dir something like > >> /etc/cron.daily etc... On my laptop they all run at midnight. I'm > >> wondering if they're blowing out your cache so that you just don't > >> have the same performance the first time you hit a particular dataset > >> after they've run. Just a guess. You could try disabling them for a > >> day and see what happens. > >> > >> -- > >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > > > > > > Meet singles at ninemsn dating Looking for a great date? > > > > -- > When fascism comes to America, it will be intolerance sold as diversity. _ If It Exists, You'll Find it on SEEK Australia's #1 job site http://clk.atdmt.com/NMN/go/157639755/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Tom, Scott, Alvaro,thanks for the hints on this issue. It looks as if one of the EOD maintenance jobs which does a few extensive queries does push data out of memory leading to this behavior. Is there a way to permanently cash some tables into memory? ThanksAlex > Date: Sat, 19 Dec 2009 00:10:36 -0700 > Subject: Re: [GENERAL] PL/Perl Performance Problems > From: scott.marl...@gmail.com > To: ainto...@hotmail.com > CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > > According to your original post, you do selects in step 1 and 2... Or > is this a different job and I've lost the thread (happens to me plenty > :) ) > > 1. Selects about 20 Records from Table A ( >- loops though the list and deletes in total about 50k records in Table B > 2. For each record form Table A it then selects Records from Table C >- loops through these records about 50K in total >- for each runs a query 3 Tables, 10-20M records >- inserts a record in Table B .. about 50K > 3. Returns some stats on the whole operation (100 records). > > On Sat, Dec 19, 2009 at 12:07 AM, Alex - wrote: > > On a 2nd thought... where does the cach come into play when i only do > > inserts and no selects. > > Alex > > > >> Date: Fri, 18 Dec 2009 23:45:07 -0700 > >> Subject: Re: [GENERAL] PL/Perl Performance Problems > >> From: scott.marl...@gmail.com > >> To: ainto...@hotmail.com > >> CC: t...@sss.pgh.pa.us; pgsql-general@postgresql.org > >> > >> On Fri, Dec 18, 2009 at 11:37 PM, Alex - wrote: > >> > Hmm... > >> > how can that be. This is happening every day, so its not a one off or > >> > happens once in the morning then in the afternoon. There is also no > >> > other > >> > task running on the system, its dedicated to postgres. > >> > Could the Autovacuum cause problems? Starting to invoke Analyze at the > >> > beginning of the day but the keep silent till the day timestamp breaks ? > >> > The think is that I have 4 servers setup in a similar way and all have > >> > exactly the same problem. > >> > >> What cron jobs are on that machine that run at night? Note that on > >> many OSes, maintenance crons are scheduled in a dir something like > >> /etc/cron.daily etc... On my laptop they all run at midnight. I'm > >> wondering if they're blowing out your cache so that you just don't > >> have the same performance the first time you hit a particular dataset > >> after they've run. Just a guess. You could try disabling them for a > >> day and see what happens. > >> > >> -- > >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > > > > > > Meet singles at ninemsn dating Looking for a great date? > > > > -- > When fascism comes to America, it will be intolerance sold as diversity. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Looking for a great date? Meet singles at ninemsn dating http://clk.atdmt.com/NMN/go/150855801/direct/01/
Re: [GENERAL] PL/Perl Performance Problems
Alex - writes: > Tom, Scott, Alvaro,thanks for the hints on this issue. It looks as if one of > the EOD maintenance jobs which does a few extensive queries does push data > out of memory leading to this behavior. > Is there a way to permanently cash some tables into memory? Not as such, and if there were it probably wouldn't be an overall performance win anyway, because you'd hurt your maintenance tasks. What you might consider doing is, at the end of the EOD sequence, run some dummy queries that scan the tables you use normally, causing them to get swapped back in so the cache is already primed when people come to work in the morning. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl Performance Problems
Thanks,I am already have started doing that. i.e. running the dummy task to get the tables loaded again and it worked fine today. regardsAlex > To: ainto...@hotmail.com > CC: scott.marl...@gmail.com; pgsql-general@postgresql.org > Subject: Re: [GENERAL] PL/Perl Performance Problems > Date: Sun, 20 Dec 2009 22:39:00 -0500 > From: t...@sss.pgh.pa.us > > Alex - writes: > > Tom, Scott, Alvaro,thanks for the hints on this issue. It looks as if one > > of the EOD maintenance jobs which does a few extensive queries does push > > data out of memory leading to this behavior. > > Is there a way to permanently cash some tables into memory? > > Not as such, and if there were it probably wouldn't be an overall > performance win anyway, because you'd hurt your maintenance tasks. > What you might consider doing is, at the end of the EOD sequence, > run some dummy queries that scan the tables you use normally, causing > them to get swapped back in so the cache is already primed when people > come to work in the morning. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Get more out of Hotmail Check out the latest features today http://windowslive.ninemsn.com.au/hotmail/article/878466/your-hotmail-is-about-to-get-even-better
Re: [GENERAL] pl/perl setof function
On 1/13/2010 12:20 PM, Alex - wrote: Hi, i have a PL/PERL RETURN SETOF function which processes a few 10k records. The processing takes quite some time and in order to display progress I use a return_next after every few thousand records. However, the function returns all messages when it completes and exists which is not really what i want. I also tried the elog NOTICE but then I only get the messages back to the psql. I am calling the function from a perl script and would like to catch these progress messages. Is there any way to solve my problem? Thanks Alex In the doc's you'll see that return_next copies the record into an in-memory table which is then returned once the storedproc is finished. Not sure how raise works though. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pl/perl setof function
On 1/13/2010 1:26 PM, Andy Colson wrote: On 1/13/2010 12:20 PM, Alex - wrote: Hi, i have a PL/PERL RETURN SETOF function which processes a few 10k records. The processing takes quite some time and in order to display progress I use a return_next after every few thousand records. However, the function returns all messages when it completes and exists which is not really what i want. I also tried the elog NOTICE but then I only get the messages back to the psql. I am calling the function from a perl script and would like to catch these progress messages. Is there any way to solve my problem? Thanks Alex In the doc's you'll see that return_next copies the record into an in-memory table which is then returned once the storedproc is finished. Not sure how raise works though. -Andy Humm.. further to that, you could probably catch the notices from perl with this: http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_notifies I'm assuming elog is like raise, and that they get sent immediately and not when the stored proc is finished. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl compilation error
Hi,
I have take a look to the source code concerning PL/Perl, it seems that 2 variables
have a bad call : errgv and na.
If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will get
success to compile the lib plperl.so.
Also in Perl documentation you will find the answer for backward compatibility :
> The API function perl_get_sv("@",FALSE) should be used instead of directly accessing
> perl globals as GvSV(errgv). The API call is backward compatible with existing perls
>and
> provides source compatibility with threading is enabled.
It seems to be easily repared. I have no time yet but I will take a look as soon as
possible.
Regards
Gilles
Alex Guryanow wrote:
> Hi,
>
> I have just installed Perl 5.6.0 and PostgreSQL 7.0.2. After successfull
>installation of both these
> programs I tried to make PL/Perl support. After running the commands from Postgres
>manual I have
> received the following errors
>
Re: [GENERAL] PL/Perl compilation error
Can you send me a patch?
> Hi,
>
> I have take a look to the source code concerning PL/Perl, it seems that 2 variables
> have a bad call : errgv and na.
>
> If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will get
> success to compile the lib plperl.so.
>
> Also in Perl documentation you will find the answer for backward compatibility :
>
> > The API function perl_get_sv("@",FALSE) should be used instead of directly
>accessing
> > perl globals as GvSV(errgv). The API call is backward compatible with existing
>perls and
> > provides source compatibility with threading is enabled.
>
> It seems to be easily repared. I have no time yet but I will take a look as soon as
>possible.
>
> Regards
> Gilles
>
> Alex Guryanow wrote:
>
> > Hi,
> >
> > I have just installed Perl 5.6.0 and PostgreSQL 7.0.2. After successfull
>installation of both these
> > programs I tried to make PL/Perl support. After running the commands from Postgres
>manual I have
> > received the following errors
> >
>
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] PL/Perl compilation error
Bruce Momjian wrote: > Can you send me a patch? > > > Hi, > > > > I have take a look to the source code concerning PL/Perl, it seems that 2 variables > > have a bad call : errgv and na. > > > > If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will get > > success to compile the lib plperl.so. > > This patch (simple diff) applies to postgresql-7.0.2. See attachment... Regards Gilles DAROLD 328c328 < if (SvTRUE(GvSV(PL_errgv))) --- > if (SvTRUE(GvSV(errgv))) 334c334 < elog(ERROR, "creation of function failed : %s", SvPV(GvSV(PL_errgv), PL_na)); --- > elog(ERROR, "creation of function failed : %s", SvPV(GvSV(errgv), na)); 444c444 < if (SvTRUE(GvSV(PL_errgv))) --- > if (SvTRUE(GvSV(errgv))) 450c450 < elog(ERROR, "plperl : error from function : %s", SvPV(GvSV(PL_errgv), PL_na)); --- > elog(ERROR, "plperl : error from function : %s", SvPV(GvSV(errgv), >na)); 654c654 < (SvPV(perlret, PL_na), --- > (SvPV(perlret, na), 2192c2192 < output = perl_eval_pv(SvPV(output, PL_na), TRUE); --- > output = perl_eval_pv(SvPV(output, na), TRUE);
Re: [GENERAL] PL/Perl compilation error
Gilles DAROLD <[EMAIL PROTECTED]> writes: I have take a look to the source code concerning PL/Perl, it seems that 2 >variables have a bad call : errgv and na. If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will >get success to compile the lib plperl.so. > This patch (simple diff) applies to postgresql-7.0.2. The problem is this will break on older copies of Perl. regards, tom lane
Re: [GENERAL] PL/Perl compilation error
On Tue, 17 Oct 2000, Tom Lane wrote: > Gilles DAROLD <[EMAIL PROTECTED]> writes: > >> The problem is this will break on older copies of Perl. > > > This problem is solved by perl itself ! > > Yeah, it is: there is a module called Devel::PPPort that isolates > user C code from the incompatibilities of different Perl APIs. Until > someone gets around to submitting a proper fix using PPPort, we'll stick > with the POLLUTE=1 solution we have now. I see no reason to install an > incomplete solution that will fail on older Perls --- we are not in the > business of forcing people to update their Perls. I believe that POLLUTE should be a default. People who are using perl5.004 are definitely a minority now. 5.004 is 3 years old now... -alex
Re: [GENERAL] PL/Perl in 7.1.1
Jeff Patterson <[EMAIL PROTECTED]> writes: > I recently upgraded from 7.0.3 to 7.1. Everything went fine until I went to > install plperl using createlang. It throws the following error message: > ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: >/usr/lib/perl5/5.6.0/i686-linux/auto/Opcode/Opcode.so: undefined symbol: >Perl_save_vptr Hm. There is no such symbol in the source text of Postgres, so I suspect you are looking at a Perl version-compatibility problem, rather than something that's directly Postgres' fault. I suggest asking Perl gurus rather than Postgres gurus for help ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] pl/perl problem of memory
"GIROIRE Nicolas (COFRAMI)" <[EMAIL PROTECTED]> writes: > In fact, now i put the variable to '' ( $myvar = '' ) but it seems that Perl > don't empty totally memory. See the nearby thread "plperl doesn't release memory". There's some evidence that this may be due to a Perl configuration issue. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PL/PERL: raise notice, exception ?
On Tue, Apr 05, 2005 at 04:28:10PM +0200, Philippe Lang wrote: > Hi, > > Is there in PL/PERL, under PG 8.01, an equivalent for the "raise > notice, exception" commands of PL/PGSQL? Use the elog() function. :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PL/PERL: raise notice, exception ?
On Tue, Apr 05, 2005 at 07:37:03AM -0700, David Fetter wrote: > On Tue, Apr 05, 2005 at 04:28:10PM +0200, Philippe Lang wrote: > > Hi, > > > > Is there in PL/PERL, under PG 8.01, an equivalent for the "raise > > notice, exception" commands of PL/PGSQL? > > Use the elog() function. :) See also http://www.postgresql.org/docs/current/static/plpgsql-errors-and-messages.html -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pl/perl autonomous transactions question
On Sep 25, 2006, at 3:05 PM, Bob wrote: One issue I see with my current DBI solution is I need to hard code or pass in as variables the connection information. I would prefer not to have the password lying around in plain site. Keep in mind this is a batch process not a something I that is called manually where a user is going to be entering their username and password in. You might be able to use ident authentication. There's also the .pgpass file. Unfortunately, there's no way right now to handle the authentication automatically, though that would be nice to have. BTW, you should take a look at contrib/dblink. It'd probably be more performant than a perl procedure would be, among other benefits. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pl/perl autonomous transactions question
On Monday 25 September 2006 15:05, Bob wrote:
> I would like to use autonomous transactions for a large batch process and I
> want this all encapsulated within stored procedures. I want to commit after
> say every 15,000 records. The only way I have found to do this is to use
> the perl DBI in my stored procedure to establish a new connection to the
> database.
>
> 1. Is
> there any way to tell the DBI connection to use the current credtials
> just with a new connection?
>
I guess the depends on where you draw the line between credentials and
connection information.
> 2. Is there any way to get the spi call to create a new connection instead
> of using the connection it is called with?
>
no... that is after all the whole point of spi.
> One issue I see with my current DBI solution is
> I need to hard code or pass in as variables the connection
> information. I would prefer not to have the password lying around in
> plain site. Keep in mind this is a batch process not a something I
> that is called manually where a user is
> going to be entering their username and password in.
>
One option is to use dbi-link, which will store the connection information in
it's own table, so you only pass in a reference to the particular connection
inside your function. You can then control permissions on this table more
granularly. Taking that a step farther would be creating specific functions
to handle the posting of the autonomous transactions and then calling those
inside your functions.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: > The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is > counter intuitive to some people and those of us who are coming from say a Perl > background are going to be much more proficient in using pl/Perl then having > to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Basically, what I'm doing is using htDig to index and search text objects within Postgres. I spent a lot of time trying to get GiST and tsearch to work, but the lack of documentation and complexity of it made it impossible. Plus, htDig already has features that allow it to ignore HTML, phrase searching as well as fuzzy logic for lexemes, soudex and whatnot. We donated a G4 (and hopefully more soon) to the htDig team to help get 3.2 out of beta, and it is paying off big time. Here's a prototype of one component of the search engine: http://newfind.mcgill.ca/ads/ which basically is an index of: http://www.mcgill.ca/classified/ That search tool works well, but it is a PHP wrapper/hack. I would much rather do it at the DB level rather than PHP as it makes it a much more powerful tool. If I had a month or two, I would take the htDig source and make it a Postgres plugin, but unfortunately I don't. The worst part of this is that I have about two days to finish building this. :-( So, perhaps I should stick with Perl for now, and hope that with a real SPI, the speed will improve significantly. Someone mentioned earlier that there is an experimental SPI... just how experimental exactly? Segfault and die or less dangerous? Thanks again for all the feedback. I'd be happy for any more thoughts and ideas. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl returning multiple rows
Hello, If you can code in Perl then pl/C wouldn't be a deep jump. J Christopher Murtagh wrote: On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Basically, what I'm doing is using htDig to index and search text objects within Postgres. I spent a lot of time trying to get GiST and tsearch to work, but the lack of documentation and complexity of it made it impossible. Plus, htDig already has features that allow it to ignore HTML, phrase searching as well as fuzzy logic for lexemes, soudex and whatnot. We donated a G4 (and hopefully more soon) to the htDig team to help get 3.2 out of beta, and it is paying off big time. Here's a prototype of one component of the search engine: http://newfind.mcgill.ca/ads/ which basically is an index of: http://www.mcgill.ca/classified/ That search tool works well, but it is a PHP wrapper/hack. I would much rather do it at the DB level rather than PHP as it makes it a much more powerful tool. If I had a month or two, I would take the htDig source and make it a Postgres plugin, but unfortunately I don't. The worst part of this is that I have about two days to finish building this. :-( So, perhaps I should stick with Perl for now, and hope that with a real SPI, the speed will improve significantly. Someone mentioned earlier that there is an experimental SPI... just how experimental exactly? Segfault and die or less dangerous? Thanks again for all the feedback. I'd be happy for any more thoughts and ideas. Cheers, Chris -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 16:52, Alvaro Herrera wrote: Better start learning Tcl ... Thanks for the info. I'm ok with that, I like Perl, but I can live without it too. :-) Two questions: 1) Can Tcl return multiple rows? 3) ok, 3 questions... Any word on pl/php and a release date? AFAIK, the only PLs that support returning multiple rows at the moment are SQL, PL/pgSQL, and PL/R. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote: > Hello, > >If you can code in Perl then pl/C wouldn't be a deep jump. That might not be a bad idea. Haven't done much C programming since my CS days, but I really loved it then. Other than here: http://www.postgres.org/docs/7.3/static/xfunc-c.html Can you recommend more reading on writing C functions for Postgres? Books, anything? Thanks again. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote: > >>Better start learning Tcl ... > What are we torturing people now? Can plPython do this? Well, aparently Tcl is not up to the task either, nor is plPython. At least I can find no mention on the docs nor the source code. Can your plPHP or plPerl do it? It seems the only choices left are PL/pgSQL and C ... -- Alvaro Herrera () "Acepta los honores y aplausos y perderás tu libertad" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Write a Pl/Perl function that just does the syscall, and call it from PL/pgSQL. Similarly for complex string parsing, etc. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote: Hello, If you can code in Perl then pl/C wouldn't be a deep jump. That might not be a bad idea. Haven't done much C programming since my CS days, but I really loved it then. Other than here: http://www.postgres.org/docs/7.3/static/xfunc-c.html Can you recommend more reading on writing C functions for Postgres? Books, anything? PostgreSQL Developer's Handbook. Also look into ecpg... it will make your life easier. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote:
On Tue, 2003-11-11 at 00:07, Joe Conway wrote:
Write a Pl/Perl function that just does the syscall, and call it from
PL/pgSQL. Similarly for complex string parsing, etc.
That would work if I could get the Pl/Perl function to return an array
or set of results, but this brings me back to the original problem
(unless I'm missing something obvious).
Sorry, I guess I didn't sufficiently understand the issue. I don't
really use PL/Perl myself, but I would think there was some way to
return an array. In the docs, I see:
"Conversely, the return command will accept any string that is
acceptable input format for the function's declared return type. So, the
PL/Perl programmer can manipulate data values as if they were just text."
So if you declare the PL/Perl function to return text[], and return a
properly formatted array, e.g. something like
"{\"blah blah\",\"foo bar\",\"etc etc\"}"
it ought to work.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PL/Perl returning multiple rows
Joe Conway wrote:
Christopher Murtagh wrote:
That would work if I could get the Pl/Perl function to return an array
or set of results, but this brings me back to the original problem
(unless I'm missing something obvious).
Sorry, I guess I didn't sufficiently understand the issue. I don't
really use PL/Perl myself, but I would think there was some way to
return an array. In the docs, I see:
"Conversely, the return command will accept any string that is
acceptable input format for the function's declared return type. So, the
PL/Perl programmer can manipulate data values as if they were just text."
So if you declare the PL/Perl function to return text[], and return a
properly formatted array, e.g. something like
"{\"blah blah\",\"foo bar\",\"etc etc\"}"
it ought to work.
Just to follow up, this works:
create or replace function foo(text, text, text)
returns text[] as '
return "{\\"" . $_[0] . "\\",\\"" . $_[1] . "\\",\\"" . $_[2] . "\\"}";
' language plperl;
regression=# select f[2] from (select foo('blah1','blah2','blah3') as f)
as t; f
---
blah2
(1 row)
So maybe you can do the syscall and return an array from plperl, then do
the rest of the work in plpgsql?
Working with arrays in plpgsql in 7.3 is no fun though :(. Here is an
example that's been posted before:
-
CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4,
usename name);
CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
rec record;
groview record;
low int;
high int;
BEGIN
FOR rec IN SELECT grosysid FROM pg_group LOOP
SELECT INTO low
replace(split_part(array_dims(grolist),'':'',1),''['',)::int
FROM pg_group WHERE grosysid = rec.grosysid;
SELECT INTO high
replace(split_part(array_dims(grolist),'':'',2),'']'',)::int
FROM pg_group WHERE grosysid = rec.grosysid;
FOR i IN low..high LOOP
SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
WHERE grosysid = rec.grosysid;
RETURN NEXT groview;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE STRICT;
grolist is an array. the "SELECT INTO low..." and "SELECT INTO high..."
parts get you the array index bounds, and the FOR LOOP shows how to work
with the array elements (i.e. g.grolist[i]).
Hopefully this gets you closer.
Joe
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] PL/Perl returning multiple rows
Alvaro Herrera wrote: Well, I wasn't the OP ;-). I thought Tcl had the capability, as it is sometimes said to be the most advanced PL. Nah, that would be PL/R ;-) Joe ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PL/Perl returning multiple rows
On Tue, 2003-11-11 at 00:07, Joe Conway wrote: > Write a Pl/Perl function that just does the syscall, and call it from > PL/pgSQL. Similarly for complex string parsing, etc. That would work if I could get the Pl/Perl function to return an array or set of results, but this brings me back to the original problem (unless I'm missing something obvious). Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl without shared libperl.a
Andrew Perrin <[EMAIL PROTECTED]> writes: > Has anyone got advice on building postgres 7.1 with PL/Perl support > WITHOUT having one's perl installation built with a shared libperl.a? Try repeating the Perl build with shared-lib selected and then just installing the resulting libperl.so beside libperl.a. However: > I'm happy enough to build a special libperl.a for postgresql's use, but > I don't want my general perl build to use it since perl's documentation > notes a significant performance hit when using a shared libperl. That advice is doubtless platform-specific, and I think it may well be horsepucky for Intel-based Linux. Isn't *all* code built position-independent on that platform? I believe you could actually use a non-shared libperl.a on Intel Linux; just dike out the test for shared-ness in plperl's Makefile.PL. The reason it's there is we couldn't think of a direct test for position-independent code, which is the real requirement... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl without shared libperl.a
On Fri, 11 May 2001, Tom Lane wrote: > I believe you could actually use a non-shared libperl.a on Intel Linux; > just dike out the test for shared-ness in plperl's Makefile.PL. > The reason it's there is we couldn't think of a direct test for > position-independent code, which is the real requirement... Can you tell me how to fix this particular problem in compiling plperl? I've tried both a threading and non-threading compile of perl as well as a shared and non-shared compile (both 5.6.0 and 5.6.1) and get the same response everytime. The only hint I can find says that you can't do a threaded and shared perl at the same time, but none of the discussions mentioned plperl and it was 2 years old. Thanks for your time. In file included from plperl.c:80: /usr/lib/perl5/5.6.1/i686-linux-thread-multi/CORE/perl.h:469: warning: `USE_LOCALE' redefined ../../../src/include/config.h:38: warning: this is the location of the previous definition In file included from plperl.c:80: /usr/lib/perl5/5.6.1/i686-linux-thread-multi/CORE/perl.h:2155: warning: `DEBUG' redefined ../../../src/include/utils/elog.h:23: warning: this is the location of the previous definition plperl.c: In function `plperl_init_all': plperl.c:195: `my_perl' undeclared (first use in this function) plperl.c:195: (Each undeclared identifier is reported only once plperl.c:195: for each function it appears in.) plperl.c: In function `plperl_init_safe_interp': plperl.c:243: warning: passing arg 2 of `perl_parse' from incompatible pointer type plperl.c:251: `my_perl' undeclared (first use in this function) plperl.c: In function `plperl_create_sub': plperl.c:320: `my_perl' undeclared (first use in this function) plperl.c: In function `plperl_init_shared_libs': plperl.c:387: `my_perl' undeclared (first use in this function) plperl.c:387: warning: passing arg 3 of `Perl_newXS' from incompatible pointer type plperl.c:388: warning: passing arg 3 of `Perl_newXS' from incompatible pointer type plperl.c: In function `plperl_call_perl_func': plperl.c:399: `my_perl' undeclared (first use in this function) plperl.c: In function `plperl_func_handler': plperl.c:500: `my_perl' undeclared (first use in this function) plperl.c: In function `plperl_build_tuple_argument': plperl.c:2159: `my_perl' undeclared (first use in this function) make[1]: *** [plperl.o] Error 1 make[1]: Leaving directory `/home/src/postgresql-7.1/src/pl/plperl' make: *** [all] Error 2 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl without shared libperl.a
Ian Lance Taylor <[EMAIL PROTECTED]> writes: >> I believe you could actually use a non-shared libperl.a on Intel Linux; >> just dike out the test for shared-ness in plperl's Makefile.PL. >> The reason it's there is we couldn't think of a direct test for >> position-independent code, which is the real requirement... > I don't have context, so I'm not sure why that would be the real > requirement. Position independent code is a mechanism to make shared > libraries more efficient. Most ELF systems support creating shared > libraries with position dependent code. It's just less efficient. Hm. Most of the systems I've dealt with will refuse to build a shared library from position-dependent code. If libperl.a contains PIC code then plperl can build a plperl.so that contains libperl linked directly into its .so, rather than using a cross-reference to a shared libperl.so. But with non-PIC libperl, you're flat out of luck. At least on non-ELF systems. > It's possible to test whether you can build a shared library with > position dependent code, if that is of interest. The GNU binutils > linker testsuite has such a test. And on a non-GNU-binutils platform, how do we do that? On any platform, how do we determine what kind of code libperl.a actually contains? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PL/Perl without shared libperl.a
Ian Lance Taylor <[EMAIL PROTECTED]> writes: > As far as I know, there is no simple way to test whether libperl.a is > compiled as position independent code or not. But it would be fairly > easy to test whether you can build a shared library using libperl.a, > by writing a little test case which does just that. The test could be > run at run time or at configure time. Hmm. Or perhaps we could just go ahead and try to build libperl.so, but not abort the make if it fails. The reason for the shlib test originally was that we didn't want the whole build of Postgres to blow up if we couldn't link libperl.so. Seems like you end up with no libperl.so either way, so perhaps we could hack the Makefile to not treat link failure as fatal. The trick is that it's a makefile generated by MakeMaker and not entirely under our control... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Pl/Perl w/ Postgres 8.0 on Windows
Hi all, I am throughly enjoying using Postgres 8.0 on Windows, so I can develop my Windows client against Postgres. I would like to write some pl/perl functions, and I'm a little stumped. Now comes the sad confession - I installed PG from the Windows installer rather than building it. Can I still build pl/perl against it? Better yet, is there a pl/perl binary for Windows? Many thanks, Eric ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pl/perl not rethrowing pl/pgsql exceptions
is this behavior correct?
create or replace function error() returns text as
$$
begin
raise exception 'test!';
end;
$$ language plpgsql;
create or replace function test() returns text as
$$
my $res = spi_query("select error()"); # this error is ignored
my $res = spi_query("something stupid!"); # this is not?
$$ language plperlu;
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Perl 64-bit and sending emails
Hello! I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06 for over a year. Now, I need to put a perl function call into it to allow emails to be sent by the database backend. I tried installing plperl, but it looks like only a 32-bit version is available. Does the 64-bit version of plperl exist? Or, does someone know of another way to get the backend to send an email? Thanks! Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/PERL with composite return values & PGSQL 7.4?
Hi, Documentation mentions that PGSQL 8 supports a version of PL/PERL with composite return values. Is there a way to install this new version of PL/PERL on an old 7.4 database, or is it absolutely necessary to upgrade? Thanks Philippe Lang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Pl/Perl w/ Postgres 8.0 on Windows
> Hi all, > I am throughly enjoying using Postgres 8.0 on Windows, so > I can develop my Windows client against Postgres. > I would like to write some pl/perl functions, and I'm a > little stumped. > Now comes the sad confession - I installed PG from the > Windows installer rather than building it. Can I still build > pl/perl against it? Better yet, is there a pl/perl binary > for Windows? Well, aren't you in luck today :-) The MSI install *always* installs the plperl DLL. IF you have installed ActiveState perl, it even gives you the option to enable it in template1 durnig the install. So you need to: 1) Install ActiveState Perl 5.8, if you haven't already (that's what it's compiled against) 2) Run "createlang" for PL/Perl, similar as you'd do on Unix. (2) can be replaced with uninstall/reinstall of the MSI if you haven't started using your database yet. The option to enable the two plperl variants should be ungrayed if it finds activeState Perl 5.8 in the system path. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Pl/Perl w/ Postgres 8.0 on Windows
Thanks to both of you gentlemen. I actually had to restart Postgres after installing ActiveState Perl before I could successfully run createlang and add the language. Seems to be working now, though. Cheers, Eric Magnus Hagander wrote: Hi all, I am throughly enjoying using Postgres 8.0 on Windows, so I can develop my Windows client against Postgres. I would like to write some pl/perl functions, and I'm a little stumped. Now comes the sad confession - I installed PG from the Windows installer rather than building it. Can I still build pl/perl against it? Better yet, is there a pl/perl binary for Windows? Well, aren't you in luck today :-) The MSI install *always* installs the plperl DLL. IF you have installed ActiveState perl, it even gives you the option to enable it in template1 durnig the install. So you need to: 1) Install ActiveState Perl 5.8, if you haven't already (that's what it's compiled against) 2) Run "createlang" for PL/Perl, similar as you'd do on Unix. (2) can be replaced with uninstall/reinstall of the MSI if you haven't started using your database yet. The option to enable the two plperl variants should be ungrayed if it finds activeState Perl 5.8 in the system path. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] PL/Perl trusted throws error on example function
Hi,
I tried to test plperl and got the following error with the very first
example from chapter 37:
CREATE OR REPLACE FUNCTION perlmax(integer,integer)
RETURNS integer AS $$
if ($_[0] > $_[1]) { return $_[0]; } return $_[1];
$$ LANGUAGE plperl;
--
error from Perl function: trusted Perl functions disabled - please
upgrade Perl Safe module to version 2.09 or later at (eval 4) line 1.
--
PostgreSQL is 8.0.1, System is SuSE 9.2 (64 bit) on AMD64. Perl Safe
module says, it__s version 2.11, which seems to be the latest release on
CPAN.
plperl is installed as "trusted". If I install it as plperlu
"untrusted", the example above works correctly.
What is so bad with this function that I only can run it untrusted?
Regards, Frank
--
Frank Finner
Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606Mail: [EMAIL PROTECTED]
Telefax: 0271 231 8608Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651
pgpVhA0LHkrXR.pgp
Description: PGP signature
Re: [GENERAL] pl/perl not rethrowing pl/pgsql exceptions
On Tue, Jan 5, 2010 at 12:49 PM, Merlin Moncure wrote:
> is this behavior correct?
>
> create or replace function error() returns text as
> $$
> begin
> raise exception 'test!';
> end;
> $$ language plpgsql;
>
>
> create or replace function test() returns text as
> $$
> my $res = spi_query("select error()"); # this error is ignored
> my $res = spi_query("something stupid!"); # this is not?
> $$ language plperlu;
I got the answer on IRC. spi_query only parses the statement but does
not necessarily execute it (it's more like spi_prepare).
spi_exec_query works the way I think it should.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pl/perl and perl version-tip in doc
It is need tip in doc which version of perl must be installed. Error message tells nothing. For example Postgres 8.4 works only with perl 5.10. -- Sent from my mobile device pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl 64-bit and sending emails
On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: Hello! I've been running the 64-bit version of 8.3.4 on OpenSolaris 2009.06 for over a year. Now, I need to put a perl function call into it to allow emails to be sent by the database backend. I tried installing plperl, but it looks like only a 32-bit version is available. Does the 64-bit version of plperl exist? Or, does someone know of another way to get the backend to send an email? Have a queue table in the database you put your emails into and an external process that polls the table, sends the email and deletes the entry from the queue. Apart from avoiding the ickiness of doing high latency work from a database function this also makes sending email transaction safe - if the transaction rolls back after "sending" the email, the email doesn't get sent. Using listen/notify based on a trigger on the table makes it a little more responsive. This comes up fairly often. It's probably worth doing a tidy perl daemon to handle it and stashing it up on pgfoundry. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/Perl 64-bit and sending emails
Hi, Steve Atkins writes: > On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: >> Or, does someone know of another way to get the >> backend to send an email? > > Have a queue table in the database you put your emails into and an external > process that polls the table, sends the email and deletes the entry from > the queue. Apart from avoiding the ickiness of doing high latency work from > a database function this also makes sending email transaction safe - if the > transaction rolls back after "sending" the email, the email doesn't get > sent. > > Using listen/notify based on a trigger on the table makes it a little more > responsive. > > This comes up fairly often. It's probably worth doing a tidy perl daemon to > handle it and stashing it up on pgfoundry. Or have a look at PGQ which is made to handle this kind of queue processing: http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl-perl for 64 bits in Solaris 9
Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt but when I do "make" get the next message: /port -L/usr/local/lib -o plpgsql.so make[4]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql/src' make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql' make[3]: Entering directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' /usr/local/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -shared plperl.o spi_internal.o SPI.o -L/usr/lib/sparcv9 -L/usr/local/lib -L../../../src/port -L/usr/local/lib -m64 -lsocket -lnsl -ldl -lm -lc -Wl,-R'/usr/local/lib/perl5/5.8.9/sun4-solaris-64/CORE' -o plperl.so ld: fatal: file plperl.o: wrong ELF class: ELFCLASS32 ld: fatal: File processing errors. No output written to plperl.so collect2: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src' make: *** [all] Error 2 then i do: bash-2.05# file src/pl/plperl/plperl.o src/pl/plperl/plperl.o: ELF 32-bit MSB relocatable SPARC Version 1 My questions are: is possible compile postgres with pl-perl for 64? where i can read more about this problem? thank's
[GENERAL] pl-perl for 64 bits in Solaris 9
Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt but when I do "make" get the next message: /port -L/usr/local/lib -o plpgsql.so make[4]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql/src' make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql' make[3]: Entering directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' /usr/local/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -shared plperl.o spi_internal.o SPI.o -L/usr/lib/sparcv9 -L/usr/local/lib -L../../../src/port -L/usr/local/lib -m64 -lsocket -lnsl -ldl -lm -lc -Wl,-R'/usr/local/lib/perl5/5.8.9/sun4-solaris-64/CORE' -o plperl.so ld: fatal: file plperl.o: wrong ELF class: ELFCLASS32 ld: fatal: File processing errors. No output written to plperl.so collect2: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src' make: *** [all] Error 2 then i do: bash-2.05# file src/pl/plperl/plperl.o src/pl/plperl/plperl.o: ELF 32-bit MSB relocatable SPARC Version 1 My questions are: is possible compile postgres with pl-perl for 64? where i can read more about this problem? thank's -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/PERL with composite return values & PGSQL 7.4?
"Philippe Lang" <[EMAIL PROTECTED]> writes: > Documentation mentions that PGSQL 8 supports a version of PL/PERL with > composite return values. Is there a way to install this new version of > PL/PERL on an old 7.4 database, No. The rest of it might work, but not that feature, at least not without significant rewrite. We changed the underlying mechanisms for handling composite-valued functions in 8.0 ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl trusted throws error on example function
Frank Finner <[EMAIL PROTECTED]> writes: > error from Perl function: trusted Perl functions disabled - please > upgrade Perl Safe module to version 2.09 or later at (eval 4) line 1. What do you get from perl -e 'require Safe; print "$Safe::VERSION\n"' regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl trusted throws error on example function
Hi Tom, I found an error right between my ears, means, I had looked at the client__s version of Safe, not thinking about the fact that I was connected to the database on a server which actually had Safe installed only with version 2.07. I upgraded Safe now from CPAN on my server and everything works fine now. Thanks for your help, it opened my eyes. Regards, Frank On Thu, 03 Mar 2005 12:14:53 -0500 Tom Lane <[EMAIL PROTECTED]> thought long, then sat down and wrote: > Frank Finner <[EMAIL PROTECTED]> writes: > > error from Perl function: trusted Perl functions disabled - please > > upgrade Perl Safe module to version 2.09 or later at (eval 4) line > > 1. > > What do you get from > perl -e 'require Safe; print "$Safe::VERSION\n"' > > regards, tom lane -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606Mail: [EMAIL PROTECTED] Telefax: 0271 231 8608Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651 pgp29HO6ihqA3.pgp Description: PGP signature
Re: [GENERAL] Pl/perl and perl version-tip in doc
On 06/01/11 18:07, pasman pasmański wrote: It is need tip in doc which version of perl must be installed. Error message tells nothing. For example Postgres 8.4 works only with perl 5.10. Are you sure that's the case? Could it be that you're using a pre-compiled version of plperl? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
