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
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
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
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
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
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
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
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
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
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
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
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
, 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
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
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 (*)
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
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
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
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
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 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
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
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
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
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
:
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
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
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
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
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
, 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
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
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
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
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
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
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
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
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 value of var 1 and value of var2
you can use any number of RAISE NOTICE statements as
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
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:
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
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
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
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.
, 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 kleptog@svana.org http://svana.org/kleptog/
From
.
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
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.
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
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
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
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
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
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
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
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
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
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
:
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
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
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,
I've downloaded dbd:pg 1.49.
Should work?
On 12/1/06, Martijn van Oosterhout kleptog@svana.org 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
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
63 matches
Mail list logo