Thread: Shared Objects (Dynamic loading)

Hi,
I have a function in which i dynamicall load my shared object and the function definition is as follows:

----------------------------------------------------

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.

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.


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?

Thanks and regards,
Jas

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

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!

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

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

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

On Mon, Aug 28, 2006 at 01:29:11AM -0400, Jasbinder Bali wrote:
> 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.

DBD::Pg uses libpq to do the talking to the database. Think of it as
the driver that interfaces DBI (the perl database interface) with
libpq.

Incidently, there is a DBD::PgPP (Pg Pure Perl) which tries to do the
same without libpq, but I've never seen it in action.

> 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.

What are you good at then. If you understand SQL, write them in
pl/pgsql. Use what you're confortable with.

And you don't need DBI or anything like that for writing triggers,
that's what SPI is for.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Thanks for that reply Martijn,

Just wondering what is this SPI all about and where can i read up on that. Any quick pointers please?

Thanks
~Jas

On Mon, Aug 28, 2006 at 02:08:28AM -0400, Jasbinder Bali wrote:
> Thanks for that reply Martijn,
>
> Just wondering what is this SPI all about and where can i read up on that.
> Any quick pointers please?

The documentation is a good start,

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

Though you should go through the entire "Server Side Programming"
section to wrok out what is relevent for you.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Its because my trigger has to initiate some unix tools and the code for
the same is already written in Perl.
So my trigger just needs to call the Perl program that would do the needful eventually.

~Jas

"Jasbinder Bali" <jsbali@gmail.com> writes:
> Its because my trigger has to initiate some unix tools and the code for
> the same is already written in Perl.
> So my trigger just needs to call the Perl program that would do the needful
> eventually.

Seems like you should be writing the trigger in plperl then.  Writing
triggers in C is a task with a *steep* learning curve that you've
evidently barely started to climb, and frankly I don't see where you're
expecting to get any return from it.

            regards, tom lane

[original email got setn to Jasbinder but not to the list
 fixing that]
On 8/28/06, Gerald Timothy G Quimpo... wrote:
>         Any reason why you don't do your functions and triggers in
>         pl/pgsql instead?  It's simpler to setup and learn
>         than pl/perl, IMO.

On Mon, 2006-08-28 at 03:27 -0400, Jasbinder Bali wrote:
> Its because my trigger has to initiate some unix tools
> and the code for the same is already written in Perl.
> So my trigger just needs to call the Perl program that
> would do the needful eventually.

OK. Something to keep in mind:

Whatever your triggers do can be rolled back if the
transaction rolls back for whatever reason.  If
you're calling external programs inside your trigger,
you're performing actions which you can't rollback.
For any rolledback transactions where you already ran
the external unix tools, there's going to be a
disconnect between the data and what the unix tools
did.

One approach to this is to have a status table where
you push things that should be executed externally,
have the triggers insert into that status table, and
have a separate program that either polls the status
table or does a LISTEN for notification that it should
check the status table.

There's some discussion of that in the archives. This
way, inserts into the status table ALSO rollback if the
transaction rolls back, so if a status table entry
ever becomes visible, it's only because the transaction
already completed and it's safe to do the external
unix tool tasks.

tiger

On Mon, 2006-08-28 at 01:29 -0400, Jasbinder Bali wrote:
> 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.

Any reason why you don't do your functions and triggers in
pl/pgsql instead?  It's simpler to setup and learn
than pl/perl, IMO.

tiger

--
Gerald Timothy Quimpo   gerald.quimpo@qualservcentral.com
Business Systems Development, KFC/Mr Donut/Ramcar

  Debugging code is twice as hard as writing it, so by definition,
   if you code to the best of your ability, you are not capable of
   debugging it.