Re: 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

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

On 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

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 8/24/06, Jasbinder Bali <jsbali(at)gmail(dot)com> 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.
>
> 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
>
>
> On 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> >
> > 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 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 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

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(at)jabber(dot)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 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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 Thu, 24 Aug 2006 15:46:00 -0400 "Jasbinder Bali" <jsbali(at)gmail(dot)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(dot)finner(at)invenius(dot)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 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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 8/26/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

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 8/27/06, Michael Fuhr <mike(at)fuhr(dot)org> 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.
>
> 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
>

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 8/28/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

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(at)svana(dot)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 8/28/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
>
> 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(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to
> litigate.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFE8oe4IB7bNG8LQkwRAkLsAKCF9Gr5m5YY+1QrgAZzD73h2GgoEwCdGcYx
> K2ofNm47/xUcEK6gI5m1TJM=
> =V5t3
> -----END PGP SIGNATURE-----
>
>
>

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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(dot)quimpo(at)qualservcentral(dot)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.

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

On 8/28/06, Gerald Timothy G Quimpo <gerald(dot)quimpo(at)qualservcentral(dot)com>
wrote:
>
> 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(dot)quimpo(at)qualservcentral(dot)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.
>
>

"Jasbinder Bali" <jsbali(at)gmail(dot)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

Is there any way to check what all shared objects are loaded in the memory?
Also, when i say LOAD 'parser' where parser.so is the shared object i've
loaded dynamically
using CREATE FUNCTION, its says

ERROR: could not access file "parser": No such file or directory

Why would it give me this error?

Thanks and regards,
~Jas

On 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

"Jasbinder Bali" <jsbali(at)gmail(dot)com> writes:
> Also, when i say LOAD 'parser' where parser.so is the shared object i've
> loaded dynamically
> using CREATE FUNCTION, its says
> ERROR: could not access file "parser": No such file or directory
> Why would it give me this error?

Probably because you didn't give it a path to the file. You need either
an absolute path or something involving the special symbol $libdir.
This is not different from what's required in CREATE FUNCTION.

regards, tom lane

Do I include this Version 1 syntax in the .pgc file or C file that i get
after doing ECPG to the .pgc file?
Thanks
~Jas

On 8/24/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>

On Mon, Aug 28, 2006 at 03:26:55PM -0400, Jasbinder Bali wrote:
> Do I include this Version 1 syntax in the .pgc file or C file that i get
> after doing ECPG to the .pgc file?

You shouldn't modify the .c file that ecpg generates; the .pgc file
is the source code. However, as Martijn and I have pointed out,
you should probably be using SPI instead of ECPG. And as Tom and
I have mentioned, you probably shouldn't be using C at all because
everything you've said you're doing would be easier in other languages
like PL/pgSQL and PL/Perl.

--
Michael Fuhr

Well, I'm using C because later on i'll have to do socket programing for the
same project (opening a socket between postgresql database and unix server,
though am not sure at all how I'm or any1 else is goin to do it) and thats
something i'll do in C. So to keep things pretty straight and stick to one
single programing language due to other factors, I have no choice but to use
C. Thats one of the design decisions taken for our project.

Now, for version 1 code, i think i'll have to write it in pgc code. right?
And using version 1 conventions only i'll be able to pass and read right
values back and forth from DB to shared object. right?

Thanks,
~Jas

On 8/28/06, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> On Mon, Aug 28, 2006 at 03:26:55PM -0400, Jasbinder Bali wrote:
> > Do I include this Version 1 syntax in the .pgc file or C file that i get
> > after doing ECPG to the .pgc file?
>
> You shouldn't modify the .c file that ecpg generates; the .pgc file
> is the source code. However, as Martijn and I have pointed out,
> you should probably be using SPI instead of ECPG. And as Tom and
> I have mentioned, you probably shouldn't be using C at all because
> everything you've said you're doing would be easier in other languages
> like PL/pgSQL and PL/Perl.
>
> --
> Michael Fuhr
>

[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

Tiger,
Thanks alot for sharing that info.
Thats exactly how we have implemented our system after seeing the mismatch
in the transactions
for any rollback transactions.
Regards,
~Jas

On 8/29/06, Tiger Quimpo <bopolissimus(dot)lists(at)gmail(dot)com> wrote:
>
> [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 8/29/06, Jasbinder Bali <jsbali(at)gmail(dot)com> wrote:

> though am not sure at all how I'm or any1 else is goin to do it) and thats
> something i'll do in C. So to keep things pretty straight and stick to one
> single programing language due to other factors, I have no choice but to use
> C. Thats one of the design decisions taken for our project.
You could stick with the basic Unix design idea and have two
parts to the program, one that does the socket-part (written in
C) and one Postgres-part, written in whatever?

> Thanks,
> ~Jas
Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm