Re: [GENERAL] SLEEP in posgresql
I don't know how is a sleep of 1 second going to be harmful here instead of keeping a busy wait. Even if a=b is going to take 1 week as u say, isn't a sleep of 1 second, if nothing, would save some CPU resources instead of blindly looping for ever ? Aren't busy On 10/10/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Jasbinder Singh Bali wrote: > > my loop is a busy wait and keeps iterating until a=b condition is met. > > However, it would lead to millions of instructions executing per second. > > > > So to save resources, I want to keep a sleep before re-iterating. Don't > > understand how is SLEEP disastrous here even if i don't know when is my > loop > > going to end > > What if it takes a week? > > That means you'll have a transaction open for a week blocking vacuum > from reclaiming space. > > -- >Richard Huxton >Archonet Ltd >
Re: [GENERAL] SLEEP in posgresql
I don't know how is a sleep of 1 second going to be harmful here instead of keeping a busy wait. Even if a=b is going to take 1 week as u say, isn't a sleep of 1 second, if nothing, would save some CPU resources instead of blindly looping for ever ? Aren't busy waits dangerous from CPU resources point of view ? Won't it keep my CPU busy for ever. Also, if there's a sleep of 1 second, is it going to give me some saving in CPU resources or not ? On 10/10/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Jasbinder Singh Bali wrote: > > my loop is a busy wait and keeps iterating until a=b condition is met. > > However, it would lead to millions of instructions executing per second. > > > > So to save resources, I want to keep a sleep before re-iterating. Don't > > understand how is SLEEP disastrous here even if i don't know when is my > loop > > going to end > > What if it takes a week? > > That means you'll have a transaction open for a week blocking vacuum > from reclaiming space. > > -- >Richard Huxton >Archonet Ltd >
Re: [GENERAL] SLEEP in posgresql
my loop is a busy wait and keeps iterating until a=b condition is met. However, it would lead to millions of instructions executing per second. So to save resources, I want to keep a sleep before re-iterating. Don't understand how is SLEEP disastrous here even if i don't know when is my loop going to end On 10/10/07, Richard Huxton <[EMAIL PROTECTED]> wrote: > > Jasbinder Singh Bali wrote: > > What if its just SLEEP for 1 second. Why would it keep my stored > procedure > > hanging ? > > Because presumably your loop-condition isn't under your control > (otherwise you wouldn't need to sleep). > > Can you *always* guarantee the condition (a=b) will happen within a > reasonable time-frame? > > -- >Richard Huxton >Archonet Ltd >
Re: [GENERAL] SLEEP in posgresql
What if its just SLEEP for 1 second. Why would it keep my stored procedure hanging ? On 10/10/07, Michal Taborsky - Internet Mall <[EMAIL PROTECTED]> wrote: > > Jasbinder Singh Bali napsal(a): > > Instead of that, I re-engineered my while loop in the stored procedure > > as follows. > ... > > I was wondering how to implement the SLEEP functionality here. > > Hello. > > I can't comment the function itself, but I want to bring something else > to your attention. Note, that the stored procedure is always run as a > single transaction and by doing the sleep in it, it will probbly run for > a long time, or maybe even forever. The problem is that "Long running > transactions are evil(tm)" > > Postgres, and almost any real database engine for that matter, has > problems when there are tansactions that run for a very long time. It > prevents the cleanup of stale records, because the engine has to keep > them around for this long running transaction. > > You might consider doing the actual work in the transaction, but the > sleeping in between shoud be done outside. > > Note to PG developers: > Is there any thought being given to have the PL/pgSQL scripting language > outside the function body? Like Ora has? It would be perfect for this > case and I remember more than a dozen times in last year when I could > have used it and saved some PHP work (and network communiaction). > > -- > Michal Táborský > chief systems architect > Internet Mall, a.s. > <http://www.MALL.cz> >
Re: [GENERAL] SLEEP in posgresql
I'm using Postgresql Version 8.1.4. on fedora core 6 I'm pretty sure that pg_sleep is not implemented in 8.1. Am not sure what is the work around Jas On 10/10/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: > > I think pg_sleep is not implemented in 8.1 and earlier versions. Is there > any alternative if someone is using versions before 8.2 ? > > On 10/9/07, Guy Rouillier <[EMAIL PROTECTED] > wrote: > > > > Jasbinder Singh Bali wrote: > > > Hi, > > > > > > I have a while loop and I want to re-iterate after every 't' seconds. > > > I was reading up on the postgresql documentation that says pg_sleep(t) > > > should be handy. > > > However i doesn't work. > > > > Hmmm, I'm looking at section 9.9.5 Delaying Execution in the PostgreSQL > > 8.2.0 Documentation. Following the example presented there, I fired up > > psql and ran the following: > > > > postgres=# select current_timestamp; select pg_sleep(3); select > > current_timestamp; > > now > > > > 2007-10-09 23:50:32.649-04 > > (1 row) > > > > pg_sleep > > -- > > > > (1 row) > > > > now > > > > 2007-10-09 23:50:35.774-04 > > (1 row) > > > > Seems to be working. What version are you using and on what platform? > > > > -- > > Guy Rouillier > > > > ---(end of broadcast)--- > > TIP 1: 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] SLEEP in posgresql
Hi, I have a while loop and I want to re-iterate after every 't' seconds. I was reading up on the postgresql documentation that says pg_sleep(t) should be handy. However i doesn't work. Instead of that, I re-engineered my while loop in the stored procedure as follows. while a=b loop --do something select pg_sleep(5); end loop I doubt this would work because when I try to run SELECT pg_sleep(5) stand alone, it throws error. I was wondering how to implement the SLEEP functionality here. Thanks, ~Jas
[GENERAL] Security Advances in Postgresql over other RDBMS
Hi, The way postgres has the concept of host base authentication, is this a step forward over other RDBMS like sql server and oracle? I was wondering, what are some novel security features in postgres as compared to other RDBMS. Thanks, Jas
Re: [GENERAL] language interface in postgresql
Hi, Let me fine tune my question here. What I mean to say is the way we can write stored procedures in C, perl etc in Postgres specifying the language parameter at the end of stored procedure, compared to that, in SQL Server 2000 I've seen SP writing in pure SQL only. Can you write Stored Procedures in SQL Server 2000 or Oracle in different programing languages as well ? Thanks ~Jas On 8/15/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > > On 8/14/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: > > > I was wondering if any other database has language interface for > different > > programing languages such as C , perl , python etc the way postgresql > has. > > Assuming you mean stored procedures, Microsoft's SQL Server hosts the > CLR, which means pretty much any language capable of targeting .NET > will work. The mechanisms behind this are quite involved, to > guarantee different levels of security and reliability; it's rather > interesting to dig into. > > It is just one platform though, so might not be quite what you had in mind > :) >
[GENERAL] language interface in postgresql
Hi, I was wondering if any other database has language interface for different programing languages such as C , perl , python etc the way postgresql has. Thanks, Jas
[GENERAL] Automation Using Databases.
Hello, I'm going to give you all an overview of my system which is as follows: A record gets inserted in a table, trigger is fired (after insert) and this tirggers calls a function written in perl. This function is a client socket implementation and opens a socket connection between the database server and the Unix tools server. Unix tools server runs tools like traceroute etc, fetches the result, opens an ODBC connection with the same database server and stores tools result in a table. So, one can conclude that in my case database is not only a data storing engine but an automation engine as well that automates the start of unix tools. I am wondering if someone else has done this kind of stuff or related to it. Also, if someone has accomplished the same thing without using triggers. In short, I'm looking for any related work. Anything directly or indirectly. I need some pointers and any kind of help would be highly appreciated. I'm trying to compare my work with what people have already done. Also, It would be great if someone could give me some pointers for any papers published in this regard. I'm basically looking for some technical papers in this field of databases and then compare my work with them. I tried finding something on internet. No success so far. Thanks alot in advance Jas
[GENERAL] Automation using postgres
Hi, I my system, I am using postgres triggers to launch some unix tools and thus postgres not only serves the purpose of data storage but also works as an engine to automate the whole system. (this is about my system, talking on a broader level ) I just wanted to know if there's any related work in this area so that I can compare my system with already existing systems related to it. Thanks, Jas
Re: [GENERAL] new line in psotgres
I tried '\r\n' in my plperl function to check for a newline character in the
table and its working fine.
Also, would E'\n' work ?
I really did not understand if word newline is a key word for a newline
character in postgres.
Thanks,
~Jas
On 8/6/07, Jeff Davis <[EMAIL PROTECTED]> wrote:
>
> On Mon, 2007-08-06 at 13:28 -0400, Jasbinder Singh Bali wrote:
> > Hi,
> > Can anyone please tell me what is the character for a new line in
> > postgres ?
> > I mean how does a new line get stored in postgres ?
> > Is it "\n" or "\\n" or something else ?
> >
>
> You can just put the newline directly in the SQL:
>
> INSERT INTO mytable(myattr) VALUES('first line
> second line
> third line');
>
> Regards,
> Jeff Davis
>
>
[GENERAL] new line in psotgres
Hi, Can anyone please tell me what is the character for a new line in postgres ? I mean how does a new line get stored in postgres ? Is it "\n" or "\\n" or something else ? Thanks, Jas
[GENERAL] Capturing return value of a function
Hi, I have a function like this CREATE OR REPLACE FUNCTION sp_insert(text, text, text, text) RETURNS text AS $BODY$ BEGIN INSERT INTO tbl(a,b,c,d) VALUES ($1,$2, $3, $4); RETURN 'success'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; I was wondering what would the function return if insert fails. I want it to return 'success' upon a successful insert and 'failure' if insert fails. Would the following implementation work for that? CREATE OR REPLACE FUNCTION sp_insert(text, text, text, text) RETURNS text AS $BODY$ BEGIN BEGIN INSERT INTO tbl(a,b,c,d) VALUES ($1,$2, $3, $4); RETURN 'success'; END; RETURN 'failure'; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
[GENERAL] Count(*) throws error
Hi,
I'm using the following statement in my plpgsql function
SELECT INTO no_rows COUNT(*) FROM tbl_concurrent;
I have decalred no_rows int4 and initialized it to zero
Running the function throws the following error:
ERROR: syntax error at or near "(" at character 13
QUERY: SELECT $1 (*) FROM tbl_concurrent
CONTEXT: SQL statement in PL/PgSQL function "sp_insert_tbl_concurrent" near
line 8
If i comment this count(*) line, the error goes.
I don't know why isn't count(*) working
Thanks
Jas
Re: [GENERAL] Implementing 2 different transactions in a PL/Perl function
You mean to say keep using spi_exec till I want everything in the same transaction and the point where I want a separate transaction, use DBI ? On 7/10/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Jasbinder Singh Bali wrote: > Hi, > > How can I have two different transactions is a plperlu function? > My purpose is as follows:- > > Transaction 1 does some series of inserts in tbl_abc > Transaction 2 updates some columns in tbl_abc fetching records from some > other table. You'll have to connect back to yourself using dblink() or dbilink() - see contrib/ for details. Don't forget to consider what it means to have a connection string in a function-body either. -- Richard Huxton Archonet Ltd
[GENERAL] Implementing 2 different transactions in a PL/Perl function
Hi, How can I have two different transactions is a plperlu function? My purpose is as follows:- Transaction 1 does some series of inserts in tbl_abc Transaction 2 updates some columns in tbl_abc fetching records from some other table. I basically want 2 independent transactions in my function so that 1 commits as soon as it is done and 2 doesn't depend on it at all. Thanks, ~Jas
Re: [GENERAL] Database Insertion commitment
On 7/9/07, Viatcheslav Kalinin <[EMAIL PROTECTED]> wrote: Jasbinder Singh Bali wrote: > > One reason I see that new insert does't see the values of old insert > is because as soon as socket connection is established, my trigger > returns and 1st insert is complete even though I'm not sure whether > the unix tools server has already inserted values in table test or > not. There might be a time lag based on how fast traceroute returns, > though right now its very fast. This is most likely it if I understand you scheme right. Do you see any work around for this? Nothing that I could think of.
Re: [GENERAL] Database Insertion commitment
On 7/9/07, Viatcheslav Kalinin <[EMAIL PROTECTED]> wrote:
Jasbinder Singh Bali wrote:
> My scenario is something like this. I'll try to make it modular and
> simple.
>
> Start Function A (Written in plperlu with no subtransactions)
> Insert 1 on tbl_abc; (fires trigger A)
> Insert 2 on tbl_abc; (fires trigger A)
> End Function A
>
> Start Trigger A
>check the value of col_abc in tbl_abc
>Start Activity A if col_abc in tbl_abc doesn't is not duplicated.
> End Trigger A
>
> Now, if Insert 1 inserts col_abc = 'xyz' in tbl_abc
> and Insert 2 inserts the same value of col_abc ='xyz' the its not able
> to see the value of insert 1
> and erroneously starts Activity A that it should not actually.
>
> Do you think I am missing something vital here?
> I'm kind of stuck and confused because fundamentally Insert 2 should
> be able to see the value of Insert 1 as there is no subtransaction
> involved.
>
> Thanks,
> ~Jas
>
> On 7/9/07, *Viatcheslav Kalinin* <[EMAIL PROTECTED] <mailto:[EMAIL
PROTECTED]>>
> wrote:
>
> Jasbinder Singh Bali wrote:
> > Hi,
> >
> > If I have a series of Insert statements within a loop in a
> function on
> > the same table.
> > Would an Insert be able to see the values of previous insert in
> that
> > table ?
> > I just wanted to know, when would the records be committed, as
> in, is
> > it after the whole function is done for with its execution or
> > right after one single insert.
> >
> > Right now what I'm observing is that all the inserts are committed
> > after the whole function is executed and one insert doesn't see
the
> > value of its previous insert.
> > In this scenario, how can an insert see the value of its previous
> > insert even though the whole transaction that lies within the
> function
> > is not complete.
> >
> > Thanks,
> > ~Jas
> Functions are run in a single separate transaction (unless then have
> BEGIN ... EXCEPTION ... END block inside them which implies
> subtransaction) thus inside a function all statements can see
> results of
> the previous ones just like if you ran them one by one. All
> changes the
> function does are committed at the end of the transaction, whether
> they
> are visible or not from the outside of that transaction depends on
> the
> transaction isolation level. There are only two distinct levels of
> isolation in Postgresql: READ COMMITTED and SERIALIZABLE, hence
> uncommitted data can never be seen before the transaction which
> changed
> them is over, the second one makes transaction fully independent
> just as
> the name states.
>
>
Hmm, afaik triggers are run within the same transaction so it shouldn't
really matter if the trigger is involved. I've made some tests too
(written in plpgsql, I hope you are fine with it):
CREATE TABLE test (x varchar);
CREATE OR REPLACE FUNCTION "public"."test_trg" () RETURNS trigger AS
$body$
BEGIN
perform 1 from test1 where x = new.x;
if not found then
raise info 'not found';
else
raise info 'found';
end if;
return new;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "trigger1" BEFORE INSERT
ON "public"."test" FOR EACH ROW
EXECUTE PROCEDURE "public"."test_trg"();
CREATE OR REPLACE FUNCTION "public"."test" () RETURNS "pg_catalog"."void"
AS
$body$
begin
insert into test values ('xxx');
insert into test values ('xxx');
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;
select test();
>INFO: not found
>CONTEXT: SQL statement "INSERT INTO test values ('xxx')"
>PL/pgSQL function "test" line 5 at SQL statement
>INFO: found
>CONTEXT: SQL statement "INSERT INTO test values ('xxx')"
>PL/pgSQL function "test" line 6 at SQL statement
As you can see it has found inserted value on the second insert. Could
it be that you misused after trigger instead of before?
The only difference between you test case my a sample test case that I would
provide is the Perform part in the trigger function.
In my program Perform part is about opening a socket connection with a Unix
Tools server that runs traceroute and populates records in table test.
Before this Perform part, I would check if table test has one row whose col1
value is 'xyz' say.
If No, then Perform
else
don't perform.
One reason I see that new insert does't see the values of old insert is
because as soon as socket connection is established, my trigger returns and
1st insert is complete even though I'm not sure whether the unix tools
server has already inserted values in table test or not. There might be a
time lag based on how fast traceroute returns, though right now its very
fast.
Any comments?
Thanks,
Jas
Re: [GENERAL] Database Insertion commitment
My scenario is something like this. I'll try to make it modular and simple. Start Function A (Written in plperlu with no subtransactions) Insert 1 on tbl_abc; (fires trigger A) Insert 2 on tbl_abc; (fires trigger A) End Function A Start Trigger A check the value of col_abc in tbl_abc Start Activity A if col_abc in tbl_abc doesn't is not duplicated. End Trigger A Now, if Insert 1 inserts col_abc = 'xyz' in tbl_abc and Insert 2 inserts the same value of col_abc ='xyz' the its not able to see the value of insert 1 and erroneously starts Activity A that it should not actually. Do you think I am missing something vital here? I'm kind of stuck and confused because fundamentally Insert 2 should be able to see the value of Insert 1 as there is no subtransaction involved. Thanks, ~Jas On 7/9/07, Viatcheslav Kalinin <[EMAIL PROTECTED]> wrote: Jasbinder Singh Bali wrote: > Hi, > > If I have a series of Insert statements within a loop in a function on > the same table. > Would an Insert be able to see the values of previous insert in that > table ? > I just wanted to know, when would the records be committed, as in, is > it after the whole function is done for with its execution or > right after one single insert. > > Right now what I'm observing is that all the inserts are committed > after the whole function is executed and one insert doesn't see the > value of its previous insert. > In this scenario, how can an insert see the value of its previous > insert even though the whole transaction that lies within the function > is not complete. > > Thanks, > ~Jas Functions are run in a single separate transaction (unless then have BEGIN ... EXCEPTION ... END block inside them which implies subtransaction) thus inside a function all statements can see results of the previous ones just like if you ran them one by one. All changes the function does are committed at the end of the transaction, whether they are visible or not from the outside of that transaction depends on the transaction isolation level. There are only two distinct levels of isolation in Postgresql: READ COMMITTED and SERIALIZABLE, hence uncommitted data can never be seen before the transaction which changed them is over, the second one makes transaction fully independent just as the name states.
[GENERAL] Database Insertion commitment
Hi, If I have a series of Insert statements within a loop in a function on the same table. Would an Insert be able to see the values of previous insert in that table ? I just wanted to know, when would the records be committed, as in, is it after the whole function is done for with its execution or right after one single insert. Right now what I'm observing is that all the inserts are committed after the whole function is executed and one insert doesn't see the value of its previous insert. In this scenario, how can an insert see the value of its previous insert even though the whole transaction that lies within the function is not complete. Thanks, ~Jas
[GENERAL] date time function
Hi, I have a timestamp field in my talbe. I need to check its difference in days with the current date. field name is time_stamp and I did it as follows: select age(timestamp '2000-06-28 15:39:47.272045') it gives me something like 6 years 11 mons 29 days 08:20:12.727955 How can i convert this result into absolute number of days. thanks, ~Jas
Re: [GENERAL] timestamp wiht time zone
Thanks Alvaro,
Your information proved very handy.
~Jas
On 6/27/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Jasbinder Singh Bali escribió:
> Hi,
> i have a column in my table defined like this:
>
> time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone
Note that the column is of type timestamp, which _doesn't_ have a time
zone. You probably want
time_stamp timestamp with time zone DEFAULT ('now'::text)::timestamp with
time zone
> 1. What is the value after the dot (period) at the end. Like 760133 and
> 90582
milliseconds
> 2. How does it talk about the time zone.
It doesn't because the time zone information is not being stored due to
the datatype issue I mentioned above.
Note: the time zone is not actually stored. What actually happens is
that the value is "rotated" to GMT and stored as a GMT value, and then
when you extract it from the database it is "rotated" to the current
TimeZone for display. If you need to store what time zone a value "is
in" you need to store that information in a separate column.
--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
[GENERAL] timestamp wiht time zone
Hi,
i have a column in my table defined like this:
time_stamp timestamp DEFAULT ('now'::text)::timestamp with time zone
Data gets filled in this column from a Unix tools server.
Example values of this field are:
time_stamp
2007-06-27 14:52:14.760133
2007-06-27 15:06:56.90582
I have the following questions on this field.
1. What is the value after the dot (period) at the end. Like 760133 and
90582
2. How does it talk about the time zone.
Also, the reason I'm using time zone is that I have to compare different
values in the tables correctly without any error(s)
based on time zones.
Any kind of help would be greatly appreciated.
Thanks,
~Jas
Re: [GENERAL] SPI using perl
my $query = "SELECT sp_insert(" . $a . "," . $b . "," . $c . ")";
my $exec_query = spi_exec_query($query);
here i'm calling a function sp_insert and passing parameters a,b,c to it.
Is this the right usage to spi_exec_query?
Thanks,
~Jas
On 6/22/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
Jasbinder Singh Bali wrote:
> Hi
> I was wondering if its necessary to download DBI::PgSPI package from
> CPAN to use spi_exec_query in perl
No. Using plperl is the way to go. See the docs there are examples under
plperl.
Joshua D. Drake
> or it can be used without downloading the above mentioned package. I've
> tried using spi_exec_query without that package
> but doesn't work.
>
> Thanks,
> Jas
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
[GENERAL] SPI using perl
Hi I was wondering if its necessary to download DBI::PgSPI package from CPAN to use spi_exec_query in perl or it can be used without downloading the above mentioned package. I've tried using spi_exec_query without that package but doesn't work. Thanks, Jas
Re: [GENERAL] Atomicity in DB transactions (Rollback related)
Could you please give me some quick and helpful pointers for SPI programing
in pl/perl?
Thanks,
Jas
On 6/18/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
Jasbinder Singh Bali escribió:
> Hi,
> I have a Pl/Perlu function in which I have a statement like this:
>
> ***
> my $query_tbl_l_header = $dbh->prepare("SELECT
> sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
>
> my $exec_l_from
>
=$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis);
> ***
You have a PL/PerlU opening an independent transaction via DBI? That's
a bad idea and the explanation to your problem. You should be using SPI
instead; there are methods for this in PL/Perl.
--
Alvaro Herrera
http://www.advogato.org/person/alvherre
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentro de él no son, por desgracia,
nada idílicas" (Ijon Tichy)
[GENERAL] Atomicity in DB transactions (Rollback related)
Hi,
I have a Pl/Perlu function in which I have a statement like this:
***
my $query_tbl_l_header = $dbh->prepare("SELECT
sp_insert_tbl_l_header(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
my $exec_l_from
=$query_tbl_l_header->execute($unmask_id,$from,$to,$sender,$subject,$replyto,$cc,$bcc,$messageid,$inreplyto,$reference,$mversion,$con_type,$con_id,$con_des,$con_enc,$con_length,$con_dis);
***
even if this execute, that calls a function sp_insert_tbl_l_header, fails,
subsequent trasactions continue without failing the whole perl function
there and then and makes the Db inconsistent.
Shouldn't the whole function fail and exit at that particular failure and
don't continue?
Please let me know how do these transactions work in postgres.
Thanks,
Jas
[GENERAL] Foreign Key error
Hi, I have a Pl/Perlu function in which I have written an insert statement in tbl_xyz. If there's not foreign key in this table, insert works fine. As soon as i make one of its fields refer to tbl_abc i.e field a in tbl_xyz refering to field a in tbl_abc, insert fails. Now the problem is, I can't see the actual error, i mean the place where it fails. Is there any way by which I can include a few lines of code that would log any error in a file whenever the insert fails. Thanks, ~Jas
Re: [GENERAL] Transactional DDL
On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > Whats so novel about postgresql here? > This would happen in any RDBMS. right? > You induced divide by zero exception that crashed the whole > transaction and it did not create the table bar? [Please don't top-post. It makes the discussion hard to follow.] I used the divide by zero to raise an error to show that both the CREATE TABLE and the INSERT were rolled back when the transaction failed. If there's another definition of transactional DDL, I'd like to know what it is. Michael Glaesemann grzm seespotcode net This is what happens in every RDBMS. Whats so special about postgres then?
Re: [GENERAL] Transactional DDL
But its said that transactions in any RDBMS follow ACID properties. So if i put a create table and an Insert statement in the same begin end block as one single transactioin, won't both create and insert follow acid property, being in one single trasaction, and either both get committed or none, talking about oracle lets say On 6/2/07, Alexander Staubo <[EMAIL PROTECTED]> wrote: On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: [snip] > I believe that if a database supports transactional ddl then ddl1 and ddl2 > would commit together as a batch > And > If a Db doesn't support this transactional DDL feature then ddl1 executes > and commits without even caring about ddl2. Right? Exactly right -- Oracle, for example, implicitly commits the transaction when you execute a DDL statement such as "create table". Alexander.
[GENERAL] Transactional DDL
Hi, A few days back, it was commented by someone in the community that Postgres has this Transactional DDL feature. What I understand about Transactional DDL is something like this: begin --ddl 1 --ddl 2 end; I believe that if a database supports transactional ddl then ddl1 and ddl2 would commit together as a batch And If a Db doesn't support this transactional DDL feature then ddl1 executes and commits without even caring about ddl2. Right? ~Jas
[GENERAL] why postgresql over other RDBMS
Hi I was wondering, apart from extensive procedural language support and being free, what are other major advantages of Postgresql over other major RDBMS like oracle and sql server. Any pointers would be highly appreciated. Thanks, ~Jas
[GENERAL] EXCEPTION clause not identified
Hi, In one of my trigger functions, i'm trying to catch invalid ip address exception CREATE OR REPLACE FUNCTION func_client_socket() RETURNS "trigger" AS $BODY$ DECLARE ip_address_present int4; BEGIN ip_address_present = 1; SELECT inet(NEW.canonical_name); EXCEPTION WHEN invalid_text_representation THEN ip_address=0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; when i run this function, it gives me the followin error ERROR: syntax error at or near "EXCEPTION" at character 1343 which is the line where I have the EXCEPTION clause. Can anyone please tell me whats going wrong here? Thanks, ~Jas
Re: [GENERAL] Database transaction related
could you please elaborate this concept of queue table? ~Jas On 5/12/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote: On Fri, May 11, 2007 at 04:07:10PM -0400, Jasbinder Singh Bali wrote: > I have a transaction in postgres database as follows: > > 1 Insert in table xyz > 2 trigger fired on table xyz after insert > 3 Trigger calls a function func > 4 func is an implementation of a client socket in perl > > 1-4 happens in database environment only > Now my question is, what all happens in the unix tools server, is that > a part of the database transaction that started from step 1 above? No, how could the database know they are the same transaction? You would somehow have to get the unix server to send commands via your socket. What you're doing is usually the wrong approach. What people usually do is have the trigger insert a row into a queue table and have the unix tools server connect and do the work listed in the table, possibly marking the original row "done" somehow. > Why I'm concerned about all this is because off late, a database > insert failed in the unix tool server and the whole transaction start > from step 1 above was not rolled back. It was still successful till > step 4. To make that work the client would have to detect the unix tools server failed and abort the transaction locally also... Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> 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) iD8DBQFGRYxoIB7bNG8LQkwRAl5uAJ998zJyTN9S48bPYm0nM8qMW5D5DgCfQAXc tnDynaKd9KXyatpWGDkYDMw= =IXZO -END PGP SIGNATURE-
[GENERAL] Database transaction related
I have a transaction in postgres database as follows: 1 Insert in table xyz 2 trigger fired on table xyz after insert 3 Trigger calls a function func 4 func is an implementation of a client socket in perl 1-4 happens in database environment only at 4, a socket connection is opened to a unix tools server, that altogether a different machine. some unix tools are run in the unix tools machine, an ODBC connection is opened back to the database server (where 1-4 took place) and result of running the tools are stored in various tables in the database. Now my question is, what all happens in the unix tools server, is that a part of the database transaction that started from step 1 above? My assumption was yes because unix tools server was invoked from the client socket thats the part of the database transaction. Don't know if my assumption was correct. One more thing that I would like to mention here is that as soon as the unix tools server is done with its tool running job, it never writes back to the client socket. Communication from Unix tools server to Database server is done using ODBC connection and not the socket connection. Why I'm concerned about all this is because off late, a database insert failed in the unix tool server and the whole transaction start from step 1 above was not rolled back. It was still successful till step 4. So I'm just wondering and confused about the whole transaction behaviour in such a scenario Any kind of help would be highly appreciated. Thanks ~Jas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] IP Address Validation
Hi, I need to check whether the input string is in ip address format or not in one of my pl/pgsql functions. What function should be used to accomplish this I tried using if inet(strInput) However, it throws an exception if the input string is not in IP address format. I could have caught this exception but really don't know what type of exception category would this exception come under.. Any kind of help would be highly appreciated. Thanks, Jas
Re: [GENERAL] printing variable values in pl/pgsql functions
you can use RAISE NOTICE 'i want to print % and %', var1,var2; then run your function and click the MESSAGE tab at the bottom of your query analyzer screen and you'll see sometime like NOTICE: i want to print and wrote: Hi, I have a pl/pgsql in which i have to print various variable values during the execution of the function to see what values are getting populated inside those variables with due course of time. PRINT doesn't work. Can anyone tell me how to print these values?? Thanks, ~Harpreet
[GENERAL] Spliting a string in plpgsql
Hi, I'm writing a function in plpgsql and i need to do the following: I have a string in the following format. mail.yahoo.com In this string, i need to figure out the number of dots in it and split the string into two on last but one dot. Is there any way to accomplish this. Please let me know Thanks ~Jas
[GENERAL] Utility of OIDs in postgres
Hi, What would be the benefit of creating tables with OIDs as against one's not with OIDs Giving a unique identifier to each row inserted has some extra efficiency factor involved or what. Thanks, Jas
Re: [GENERAL] Unusual PK contraint error
how do u do reindexing? On 3/31/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes: > DBD::Pg::st execute failed: ERROR: duplicate key violates unique constraint > "pk_verify_mx" > I don't know whats goin on. Corrupt index maybe? Does REINDEXing the pk index help? regards, tom lane
[GENERAL] Unusual PK contraint error
Hi I have a compostie primary key in my table comprising of 3 fields. My systems tries to enter records in this table for which the combination of these 3 fields is not there at all The new record gets inserted but with the following error being displayed on console DBD::Pg::st execute failed: ERROR: duplicate key violates unique constraint "pk_verify_mx" I don't know whats goin on. Records get inserted but this duplicate key error is being thrown ~Jas
Re: [GENERAL] cursors in postgres
Actually I'm doing a duplicate check
My function accepts 4 parameters.
If all four exist in a particular row then i should not be inserting that
record again.
so is
INSERT INTO table(a,b,c) SELECT 'a','b','c' WHERE NOT EXISTS
( SELECT * FROM table WHERE (a,b,c) = ('a','b','c') );
going to solve my problem?
On 3/29/07, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote:
2007/3/29, Jasbinder Singh Bali <[EMAIL PROTECTED]>:
> Hi,
> I've written a function using cursors as follows:
> can anyone please comment on the text in red.
>
>
> --
>
> CREATE OR REPLACE FUNCTION
> sp_insert_tbl_email_address(int4, text, text, text)
> RETURNS void AS
> $BODY$
> DECLARE
> uid int4 ;
> src text;
> local text;
> domain text;
> cur_dup_check CURSOR FOR SELECT * FROM tbl_email_address
> WHERE unmask_id=$1 and source=$2 and email_local=$3 and
email_domain=$4;
>
>
> BEGIN
>
> OPEN cur_dup_check ;
>
> FETCH cur_dup_check INTO uid,src,local,domain;
> --need to check the fetch status of the cursor whether any rows were
> returned or not and keep moving to the next record till fetch status is
not
> zero
>
> INSERT INTO
> tbl_email_address(unmask_id,source,email_local,email_domain)
> VALUES ($1,$2,$3,$4) ;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION sp_insert_tbl_email_address(int4, int4,
> text, text, text) OWNER TO postgres;
>
You could check builtin FOUND variable.
Did you read
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html ?
and do you realize that probably, this can (and should) be done
without cursors? or even without any user defined function?
if I understand correctly, you want something like:
INSERT INTO table(a,b,c) SELECT 'a','b','c' WHERE NOT EXISTS
( SELECT * FROM table WHERE (a,b,c) = ('a','b','c') );
--
Filip Rembiałkowski
[GENERAL] cursors in postgres
Hi, I've written a function using cursors as follows: can anyone please comment on the text in red. -- CREATE OR REPLACE FUNCTION sp_insert_tbl_email_address(int4, text, text, text) RETURNS void AS $BODY$ DECLARE uid int4 ; src text; local text; domain text; cur_dup_check CURSOR FOR SELECT * FROM tbl_email_address WHERE unmask_id=$1 and source=$2 and email_local=$3 and email_domain=$4; BEGIN OPEN cur_dup_check ; FETCH cur_dup_check INTO uid,src,local,domain; --need to check the fetch status of the cursor whether any rows were returned or not and keep moving to the next record till fetch status is not zero INSERT INTO tbl_email_address(unmask_id,source,email_local,email_domain) VALUES ($1,$2,$3,$4) ; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION sp_insert_tbl_email_address(int4, int4, text, text, text) OWNER TO postgres; - Thanks, ~Jas
[GENERAL] making postgres DB stable, efficient and secure
Hi, I'm done with my database design and almost got it working (with all triggers and functions) pefectly. Now, i need to see how can I make my DB stable, efficient and secure. I wanted to know how should I go about it as far as postgres is concerned What are the best practices is this regard. Any kind of help would be higly appreciated. Thanks, ~Jas
Re: [GENERAL] exception handling in plperlu
just wondeng why doesn't it let me put
my $dbh=DBI->connect("dbi:Pg:dbname=dbunmask; host=192.168.0.120;
port=5432;", "", "");
in eval
says
Global symbol "$dbh" requires explicit package name at line
Jas
On 3/16/07, Martijn van Oosterhout wrote:
On Thu, Mar 15, 2007 at 10:06:06PM -0400, Jasbinder Singh Bali wrote:
> How about using a try catch block?
> isn't that more efficient that eval?
Umm, eval is perl's equivalent of try/catch. There is no other way.
Have a nice day,
--
Martijn van Oosterhout 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)
iD8DBQFF+oqkIB7bNG8LQkwRApyEAJ413cmp0bIuasEceWhXgTgSE6NOFACeNijH
ZHknBQrtHqg30xL8Wh219Ik=
=Xhhm
-END PGP SIGNATURE-
Re: [GENERAL] exception handling in plperlu
How about using a try catch block?
isn't that more efficient that eval?
~Jas
On 3/15/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Jasbinder Singh Bali" <[EMAIL PROTECTED]> writes:
> Actually, if I rephrase my requirement, I need to catch an exception at
any
> point ,where ever it is raised, in the perl code.
> E.g during an insert, there is a foreign key contraint violation, then i
> need to catch this specific error and do something with it.
Since PG 8.0, you should be able to trap exceptions with eval{}, same as
you would do in any Perl code.
regards, tom lane
[GENERAL] exception handling in plperlu
Hi, Actually, if I rephrase my requirement, I need to catch an exception at any point ,where ever it is raised, in the perl code. E.g during an insert, there is a foreign key contraint violation, then i need to catch this specific error and do something with it. Hope i make some sense here. Thanks, Jas
Re: [GENERAL] Exception handling in plperl
Hi, Actually, if I rephrase my requirement, I need to catch an exception at any point ,where ever it is raised, in the perl code. E.g during an insert, there is a foreign key contraint violation, then i need to catch this specific error and do something with it. Hope i make some sense here. Thanks, Jas On 3/14/07, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Tue, Mar 13, 2007 at 11:23:03PM -0400, Jasbinder Singh Bali wrote: > I have a stored procedure written in plperl. > This procedure has series of inserts at various levels. A few inserts on > certain tables invoke triggers > that launch tools outside the domain of the database. > > How can I make everything as one single transaction and simply roll back > everything whenever an exception occurs. Statements are always executed in a transaction; if you're not inside an explicit transaction block then statements will be implicitly wrapped in a transaction for you. If the outermost statement is "SELECT function_that_does_inserts()" then everything that happens inside that function is part of the same transaction, and if any of the function's statements fail then the entire transaction will fail unless you trap the error. In plperlu you can trap errors with "eval"; see the Perl documentation for more information. Are you wanting to trap errors so you can roll back actions that happened outside the database? If so then you could use eval to handle failures, then do whatever cleanup needs to be done (and can be done) outside the database, then use elog to raise an error and make the current transaction fail. However, if this is what you're trying to do then understand that actions outside the database aren't under the database's transaction control and might not be able to be rolled back. If I've misunderstood what you're asking then please provide more information about what you're trying to do. -- Michael Fuhr
[GENERAL] Exception handling in plperl
Hi, I have a stored procedure written in plperl. This procedure has series of inserts at various levels. A few inserts on certain tables invoke triggers that launch tools outside the domain of the database. How can I make everything as one single transaction and simply roll back everything whenever an exception occurs. Thanks, Jas
Re: [GENERAL] server closed unexpectedly while executing a function
To add to my last post,
in my perl function I'm using
use DBI;
my $dbh=DBI->connect("dbi:Pg:dbname=xyz; host=192.168.0.120; port=5432;",
"", "");
to connect to the same DB server.
And then i have series of
dbh->prepare and ->execute
Unofrtunately it has started crying smth like
no connection to the server where ever i have these execute statements.
I just executes the first sql statement and fails at the rest
Jas
On 2/23/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
Hi
I'm running a function in perl and it says
---
NOTICE: DBD::Pg::st execute failed: server closed the connection
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
CONTEXT: SQL statement "SELECT sp_email( $1 , $2 )"
PL/pgSQL function "func_trg_email" line 2 at perform
NOTICE: DBD::Pg::st execute failed: no connection to the server
-
how can i get more verbose messages that would tell me where exactly I'm
going wrong.
I'm calling function sp_email from a trigger function func_trg_email here.
Thanks,
jas
[GENERAL] server closed unexpectedly while executing a function
Hi I'm running a function in perl and it says --- NOTICE: DBD::Pg::st execute failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. CONTEXT: SQL statement "SELECT sp_email( $1 , $2 )" PL/pgSQL function "func_trg_email" line 2 at perform NOTICE: DBD::Pg::st execute failed: no connection to the server - how can i get more verbose messages that would tell me where exactly I'm going wrong. I'm calling function sp_email from a trigger function func_trg_email here. Thanks, jas
[GENERAL] Sockets in perl (Db transaction ends abnormally at client when server closes client socket)
Hi, I have a table on which there is a trigger fired after insert. This trigger opens a socket with another server. Server, after executing what it has to, closes the client socket and at the client end (DB side) the whole transaction rolls back saying that server closed the connection abnormally which means that the transaction at the DB side ended abnormally and hence the rollback. To fix this, i tried closing the client socket at DB end (client side) itself. Unfortunatel, it keeps running the insert query forever without accomplishing anything. However, it opens the connection with the server but doesn't complete its own transaction at all. Can anyone please help me solving this problem Thanks, Jas
[GENERAL] Some unknown error in a function
Hi Following is the script of my plpgsql function CREATE OR REPLACE FUNCTION sp_insert_tbl_vrfy_mx(int4,text, text, inet, text, text) RETURNS void AS$$ DECLARE sequence_no int4; BEGIN SELECT INTO sequence_no MAX(seq_no) FROM tbl_verify_mx WHERE unmask_id = $1; IF sequence_no > 1 THEN sequence_no = sequence_no + 1; ELSE sequence_no = 1; END IF; IF $4 =' ' THEN INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local, email_domain, mail_server, mx_records ) VALUES ($1,sequence_no,$2,$3,$5,$6) ; ELSE INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local, email_domain, ip_address, mail_server, mx_records ) VALUES ($1,sequence_no,$2,$3,CAST($4 as inet), $5,$6) ; END IF; END; $$ LANGUAGE 'plpgsql' VOLATILE; I run this function using select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', ' mail.xyz.com,'mxrecoredmxjdlkfjdk') and get the following error:- CONTEXT: SQL statement "SELECT $1 =' '" PL/pgSQL function "sp_insert_tbl_vrfy_mx" line 12 at if Don't know where I'm going wrong. Thanks Jas
[GENERAL] CREATE FUNCTION Fails with an Insert Statement in it
I have created the following function : CREATE OR REPLACE FUNCTION sp_insert_tbl_l_header(int4,text) RETURNS bool AS $BODY$ INSERT INTO tbl_xyz (unmask_id,email_from) VALUES ($1,$2) $BODY$ LANGUAGE 'sql' VOLATILE; when i try to create this fucntion by running this script, i get the following error: ERROR: return type mismatch in function declared to return boolean DETAIL: Function's final statement must be a SELECT. CONTEXT: SQL function "sp_insert_tbl_xyz" So i think there is some problem with the return type of the function when it has an insert statement but really don't know what should be the return type of this function. Thanks, Jas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions
Thats exactly what I'm facing here. CREATE OR REPLACE FUNCTION test(int4) RETURNS int4 AS $BODY$ require "abc.pl" $BODY$ LANGUAGE 'plperlu' VOLATILE; SELECT test(23) doesn't run the script inside abc.pl that happens to be a some insert statements. Now, when i actually copy and paste the script of abc.pl inside the postgres function body and then do SELECT test(23) it works fine by executing those insert statements. Don't know whats wrong here now. Can anyone please throw some light on it. Thanks, Jas On 1/18/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: Don't think it would work the way you are doing it. This way it would only work if you dealing with shared objects in C where in you dynamically load the shared object and then call a specific function of that shared object. Lately i tried the following for you but it doesn't execute the Insert script inside sql.pl CREATE OR REPLACE FUNCTION test_perl_param(int4) RETURNS bool AS $BODY$ require "/usr/local/pgsql/jsbali/sql.pl" $BODY$ LANGUAGE 'plperlu' VOLATILE; ALTER FUNCTION test_perl_param(int4) OWNER TO postgres; SELECT test_perl_param(23) Here in sql.pl i have one insert statement but SELECT test_perl_param(23) doesn't run the INSERT statement inside the sql.pl Can anyone throw light on how to make it work so that whatever script i have inside sql.pl run as soon as I run SELECT test_perl_param(23). I think this wold help out jas alot. Thanks, Harpreet On 1/18/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote: > > Lately i've been able to user 'require' command successfully and the > script was pretty straight forward and simple. I had to play around > with @INC. > > Moving forward, I have another question here, > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS > require " abc.pl" > $$ LANGUAGE plperlu; > > In the above script if I have to call a particular sub-routine in abc.pl. > How can that be done? > I have to pass values to the arguments of a sub routine in abc.pl from > the the function funcname (arguments of the funcname in particular). > How would this thing be done? > > Thanks, > Jas > > On 1/16/07, Harpreet Dhaliwal < [EMAIL PROTECTED]> wrote: > > so my syntax is correct? just wondering if there's some fundamental mistake > > in it > > ~Harpreet > > > > > > On 1/16/07, Tom Lane < [EMAIL PROTECTED]> wrote: > > > "Harpreet Dhaliwal" < [EMAIL PROTECTED] > writes: > > > > I was just wondering if one could use something like this > > > > > > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS > > $$ > > > > require " abc.pl" > > > > $$ LANGUAGE plperl; > > > > > > You'd have to use plperlu, since "require" isn't considered a trusted > > > operation. > > > > > > > To include abc.pl here, how is the path of abc.pl specified. > > > > > > Same as you'd do it in plain Perl. > > > > > > regards, tom lane > > > > > > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions
Lately i've been able to user 'require' command successfully and the script was pretty straight forward and simple. I had to play around with @INC. Moving forward, I have another question here, CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS require " abc.pl" $$ LANGUAGE plperlu; In the above script if I have to call a particular sub-routine in abc.pl. How can that be done? I have to pass values to the arguments of a sub routine in abc.pl from the the function funcname (arguments of the funcname in particular). How would this thing be done? Thanks, Jas On 1/16/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: so my syntax is correct? just wondering if there's some fundamental mistake in it ~Harpreet On 1/16/07, Tom Lane < [EMAIL PROTECTED]> wrote: > "Harpreet Dhaliwal" < [EMAIL PROTECTED]> writes: > > I was just wondering if one could use something like this > > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS $$ > > require " abc.pl" > > $$ LANGUAGE plperl; > > You'd have to use plperlu, since "require" isn't considered a trusted > operation. > > > To include abc.pl here, how is the path of abc.pl specified. > > Same as you'd do it in plain Perl. > > regards, tom lane > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions
I'm kind of confused how this require thing would actually work because I tried testing it at my end at its really not working with postgres. I'm sure there is some fundamental mistake. I have to do the following: I have a perl file and need to call and use full functionaily of this perl file in my postgres function. For this, the create function script that I wrote is as follows: CREATE FUNCTION test_perl (int) RETURNS int AS $$ require 'email_parser1.pl' $$ LANGUAGE plperlu; really don't know if this is correct because if i try to load a file that doesn't exist atall, then also postgres doesn't throw any errors. Also do i need to give the fully qualified path of the perl file in the create function script? A quick help would be highly appreciated as I'm badly stuck up here. Thanks, Jas On 1/16/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: so my syntax is correct? just wondering if there's some fundamental mistake in it ~Harpreet On 1/16/07, Tom Lane < [EMAIL PROTECTED]> wrote: > "Harpreet Dhaliwal" < [EMAIL PROTECTED]> writes: > > I was just wondering if one could use something like this > > > CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS $$ > > require " abc.pl" > > $$ LANGUAGE plperl; > > You'd have to use plperlu, since "require" isn't considered a trusted > operation. > > > To include abc.pl here, how is the path of abc.pl specified. > > Same as you'd do it in plain Perl. > > regards, tom lane > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions
So there is nothing called dynamic loading of perl code in postgres. I'll have to include the whole perl script in the postgres function you mean? Thanks, Jas On 1/16/07, John DeSoi <[EMAIL PROTECTED]> wrote: Assuming you have pl/perl support compiled into PostgreSQL, just use CREATE FUNCTION: http://www.postgresql.org/docs/8.2/interactive/plperl-funcs.html On Jan 16, 2007, at 9:24 AM, Jasbinder Singh Bali wrote: > Actually I want to load my perl code in postgres function. > How would i do that? > In C you have the notion of shared objects that you dynamically load > in postgres functions. > If one has to dynamically load the functionality of some perl code in > postgres function, how would that be done? > I think i was not clear enough in my last email John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: 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] Dynamic loading of Perl Code in Postgres functions
Actually I want to load my perl code in postgres function. How would i do that? In C you have the notion of shared objects that you dynamically load in postgres functions. If one has to dynamically load the functionality of some perl code in postgres function, how would that be done? I think i was not clear enough in my last email Thanks Jas On 1/16/07, Adam Rich <[EMAIL PROTECTED]> wrote: You treat it like any other perl code (you don't have to do anything special just because it's in postgres): If it's pure perl code, see "require" http://perldoc.perl.org/functions/require.html If it's a perl module, see "use" http://perldoc.perl.org/functions/use.html If you want to access a C library using perl, see XS: http://perldoc.perl.org/perlxs.html http://perldoc.perl.org/perlxstut.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jasbinder Singh Bali Sent: Tuesday, January 16, 2007 8:07 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Dynamic loading of Perl Code in Postgres functions Hi, I have some perl code that I need to load dynamically in my postgres function. How can this be accomplished? I can do it in C using shared objects but don't know how would the same work with perl. Is there anything like shared objects in Perl or something. Thanks, Jas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Dynamic loading of Perl Code in Postgres functions
Hi, I have some perl code that I need to load dynamically in my postgres function. How can this be accomplished? I can do it in C using shared objects but don't know how would the same work with perl. Is there anything like shared objects in Perl or something. Thanks, Jas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DBI module for postgres 1.4.3
Hi, Whats the difference between a module and a bundle as i can see while downloading DBI from CPAN website. What exactly needs to be downloaded . I'm kind of not sure about it. Thanks, Jas On 12/4/06, Albe Laurenz <[EMAIL PROTECTED]> wrote: > Trying to connect to it throught perl code. > Just wondering if DBI would be the best tool to use to > accomplish this task. > Which version of DBI should I be using. > I mean if any one of you could give me exact pointers to it, > would be highly appreciated. Yes, perl(DBI) is the canonical way to connect to a database from Perl. You will need the DBD::Pg driver too. Both modules can be obtained from CPAN (e.g. http://www.cpan.org), maybe there are even binary packages for your operating system available. I'd use the latest stable version. Yours, Laurenz Albe
Re: [GENERAL] DBI module for postgres 1.4.3
Oops my bad. That 1.4.3 was pgadmin versioin actually. Sorry about that On 12/1/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote: On Fri, 2006-12-01 at 21:05 +0100, Martijn van Oosterhout wrote: > On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote: > > Hi > > I'm using postgresql version 1.4.3. > > No such version exists. What exactly do you mean? I am guessing he means DBD::Pg 1.4.3 to PostgreSQL version Joshua D. Drake > > > Trying to connect to it throught perl code. > > Just wondering if DBI would be the best tool to use to accomplish this task. > > Which version of DBI should I be using. > > Whichever version is installed by your system should be fine. Clients > are compatable across many versions. > > Have a nice day, -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
Re: [GENERAL] DBI module for postgres 1.4.3
I've downloaded dbd:pg 1.49. Should work? On 12/1/06, Martijn van Oosterhout wrote: On Fri, Dec 01, 2006 at 02:49:59PM -0500, Jasbinder Singh Bali wrote: > Hi > I'm using postgresql version 1.4.3. No such version exists. What exactly do you mean? > Trying to connect to it throught perl code. > Just wondering if DBI would be the best tool to use to accomplish this task. > Which version of DBI should I be using. Whichever version is installed by your system should be fine. Clients are compatable across many versions. Have a nice day, -- Martijn van Oosterhout 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) iD8DBQFFcIr3IB7bNG8LQkwRAnVTAJ4soDezZtEbxosNd+LrmnV2Lm08OwCffIh/ AhHObMuuj5dCXHllcWSCYaI= =X3Gq -END PGP SIGNATURE-
[GENERAL] DBI module for postgres 1.4.3
Hi I'm using postgresql version 1.4.3. Trying to connect to it throught perl code. Just wondering if DBI would be the best tool to use to accomplish this task. Which version of DBI should I be using. I mean if any one of you could give me exact pointers to it, would be highly appreciated. Thanks, Jas
