Shared Objects (Dynamic loading)

On Thu, Aug 24, 2006 at 01:03:43AM -0400, Jasbinder Bali wrote:

CREATE OR REPLACE FUNCTION sp_trigger_raw_email(int4, text)
RETURNS bool AS
'/usr/local/pgsql/jsbali/parser', 'parse_email'
LANGUAGE 'c' VOLATILE STRICT;
ALTER FUNCTION sp_trigger_raw_email(int4,text ) OWNER TO postgres;

function parse_email(int caseno, char *rawemail)
populates a few global variables first and then
call another function parse_header().
function parse_header() makes use of the global variables and then using
ECPG stores values in a table in the database.

Is there a reason this server-side code is using ECPG instead of SPI?

http://www.postgresql.org/docs/8.1/interactive/spi.html

My question is, when we try to make use of a specific function of a shared
object dynamically loaded as show above, then
would that function be able to access all global variables populated
elsewhere in the program or all the global variables can't be accessed
inside that function of the shared object.

A function should be able to access any global symbol and any static
symbol in the same object file. Are you having trouble doing so?

Also, in the above function definition,
the signature of parse_email function is
parse_email(int, char*) and i am passing (int4 , text) to int as seen in the
function code pasted above.
Is text in pgsql going to match with char* or i should use some other
datatype?

See "C-Language Functions" in the documentation, in particular what
it says about version 1 calling conventions.

http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html

Is there a reason you're coding in C instead of a higher-level
language like PL/Perl? If you're parsing email messages then coding
in Perl, Python, Ruby, etc., would probably be easier than C.

--
Michael Fuhr

Well, the server side code is in ECPG because thats the easiest choice i
could see.
I really don't know how difficult or beneficial would SPI be. Haven't heard
of SPI before.
I was under the impression that ECPG and libpg are the only two choices a
developer has in postgresql for database related activities.

I am using char in postgres function as an analogue for char* in C. Is this
correct?
The link that you gave says varchar* in C has varchar as its analogue in
postgresql.

Thanks and regards,
Jas

Also, when i dynamically load a shared library and then later on change the
code, create the same shared library (same name) and run my function where
in the shared library is loaded, it takes the reference of the old shared
library.
why does this happen and how to get rid of this.

Thanks and regards,
Jas

On Thu, Aug 24, 2006 at 02:51:50AM -0400, Jasbinder Bali wrote:

Well, the server side code is in ECPG because thats the easiest choice i
could see.
I really don't know how difficult or beneficial would SPI be. Haven't heard
of SPI before.
I was under the impression that ECPG and libpg are the only two choices a
developer has in postgresql for database related activities.

ECPG and libpq are client libraries. Server-side functions can use
those libraries to make connections to the same or a different
database, but a function can use SPI to execute commands in the
same backend in which it's running without having to make a separate
client connection. That's more efficient and the commands the
function runs will be executed in the same transaction as the
function itself, so if the calling transaction rolls back then
statements the function executed will roll back. If the function
had executed statements over a separate connection, committed them,
and closed the connection, then those statements wouldn't roll back
even if the function's transaction rolled back.

I am using char in postgres function as an analogue for char* in C. Is this
correct?
The link that you gave says varchar* in C has varchar as its analogue in
postgresql.

If the function accepts a text argument then see the copytext() and
concat_text() examples that show how to work with such data. Look
at the examples that use the version-1 calling conventions, the
ones that are declared like this:

PG_FUNCTION_INFO_V1(copytext);

Datum
copytext(PG_FUNCTION_ARGS)
{
...
}

--
Michael Fuhr

Actually my function accepts char, so what should be the SQL analogue for
that in postgres?
Thanks,
Jas

On Wed, Aug 23, 2006 at 11:41:50PM -0600, Michael Fuhr wrote:

Is there a reason this server-side code is using ECPG instead of SPI?

To make sure it doesn't work? There is NO guarantee that ECPG will work
in this scenario.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

On Thu, Aug 24, 2006 at 03:29:55AM -0400, Jasbinder Bali wrote:

Also, when i dynamically load a shared library and then later on change the
code, create the same shared library (same name) and run my function where
in the shared library is loaded, it takes the reference of the old shared
library.
why does this happen and how to get rid of this.

The "C-Language Functions" documentation explains:

http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html

"After it is used for the first time, a dynamically loaded object
file is retained in memory. Future calls in the same session to
the function(s) in that file will only incur the small overhead of
a symbol table lookup. If you need to force a reload of an object
file, for example after recompiling it, use the LOAD command or
begin a fresh session."

--
Michael Fuhr

On Thu, Aug 24, 2006 at 03:51:28AM -0400, Jasbinder Bali wrote:

Actually my function accepts char, so what should be the SQL analogue for
that in postgres?

You might be able to declare the SQL function to accept a cstring
argument but you really should write the code to conform to
PostgreSQL's preferred (version-1) calling conventions. That might
mean writing simple wrapper function that calls the real function.

If you're doing text manipulation then it would probably be a lot
easier in PL/Perl than in C.

--
Michael Fuhr

Hi,
The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.
As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.
We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

Thanks and regards,
Jas

On Thu, 24 Aug 2006 15:46:00 -0400 "Jasbinder Bali" <jsbali@gmail.com> thought long, then sat down and wrote:

Hi,
The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.
As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.
We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

Use DBD::Pg, available at CPAN.
--
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606 Mail: frank.finner@invenius.de
Telefax: 0271 231 8608 Web: http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651

On Thu, Aug 24, 2006 at 03:46:00PM -0400, Jasbinder Bali wrote:

The way we use ECPG for the database related activites while working with
C, what do i need to look up if i'm using perl.

For information about writing server-side Perl functions see the
PL/Perl documentation (adjust the link if you're using a version
other than 8.1):

http://www.postgresql.org/docs/8.1/interactive/plperl.html

If the function needs to execute SQL statements in the same backend
in which it's running then see the "Database Access from PL/Perl"
section. If it needs to connect to a different database then you
could use the DBI module.

As alot of people have pointed out that perl is the best language to use
while dealing with email parsing.

"Best" is a matter of opinion; others might prefer Ruby, Python,
or something else. Perl is good at text handling, it has a large
number of third-party modules (CPAN), and there's a lot of material
written about it so it's a popular choice.

We have the perl code ready to parse the email.
Just wondering what would be the best method to deal with database
(postgresql) calls from the perl code.

See the aforementioned PL/Perl documentation. You could also use
PL/Perl just for parsing and use PL/pgSQL for working with the
database.

--
Michael Fuhr

Hi,
Do we have any concept of shared objects in perl.
Just wondering, how do we dynamically load something written in perl in
postgresql.

Thanks,
Jas

On Sat, Aug 26, 2006 at 03:32:37PM -0400, Jasbinder Bali wrote:

Do we have any concept of shared objects in perl.
Just wondering, how do we dynamically load something written in perl in
postgresql.

A PL/Perl function can load external code with "use", "require",
or "do". Since those are potentially dangerous operations you'll
need to create the function with plperlu, which means you'll need
to create the function as a database superuser. See "Trusted and
Untrusted PL/Perl" in the documentation for more information.

http://www.postgresql.org/docs/8.1/interactive/plperl-trusted.html

Regarding "use", "require", and "do" see the Perl documentation,
in particular the perlfunc and perlmod manual pages.

--
Michael Fuhr

Hi,
Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl script
running in another machine.
Regards,
Jas

On Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote:

Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl script
running in another machine.

DBI is a Perl module for connecting to databases; to connect to a
PostgreSQL database you'll also need DBD::Pg. If you have those
modules installed then use a command like "man DBI" or "perldoc
DBI" (or however you read documentation on your system) for more
information.

Are you creating a PL/Perl function in one database that needs to
connect to a different database? What exactly are you trying to
do?

--
Michael Fuhr

Michael Fuhr wrote:

On Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote:

Can you please give me pointers to how to establish clinet server model
using PL/Perl.
I mean how do i give the ip address of the database server in my Perl script
running in another machine.

DBI is a Perl module for connecting to databases; to connect to a
PostgreSQL database you'll also need DBD::Pg. If you have those
modules installed then use a command like "man DBI" or "perldoc
DBI" (or however you read documentation on your system) for more
information.

and 'perldoc Pg' for Pg specific info

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin

The actual scenario is like my perl code is on one computer and database
server is on the other
computer. The perl code needs to connect to that database server residing on
a diff computer.

I think client machine should also have DBI module in it. right?

Also, how much of a change would it be if i have to migrate my triggers and
functions written for C to Perl

Thanks,
Jas

On Sun, Aug 27, 2006 at 09:41:39PM -0400, Jasbinder Bali wrote:

The actual scenario is like my perl code is on one computer and database
server is on the other computer. The perl code needs to connect to that
database server residing on a diff computer.

I think client machine should also have DBI module in it. right?

Right. The client machine needs DBI (the database-independent
module), DBD::Pg (the PostgreSQL-specific driver), and libpq (the
PostgreSQL client library).

Also, how much of a change would it be if i have to migrate my triggers
and functions written for C to Perl

That depends on how many triggers you have, how elaborate they are,
and how proficient you are at Perl. I tend to use PL/pgSQL for
functions that involve a lot of SQL statements; I use PL/Perl or
PL/Ruby for things like text manipulation that those languages are
good at.

--
Michael Fuhr

Just wondering why would i need libpq here.
Doesn't DBD::pg has its own functions for database related activities.
I think i'm quite naive in this.

Also, the triggers that i wrote in C are not all that elaborative. They are
pretty basic triggers. Also, I'm a rookie in perl but don't need to do
something hifi with it.

Thanks,
~Jas