from:"Harpreet Dhaliwal"

So you mean to say something like this as far as oracle is concerned:

BEGIN
  DDL 1 (commits right after its execution)
  DDL 2 (commits right after its execution)
END

That means there's no concept of putting DDL statements in a transaction in
oracle basically, right?

Thanks,
~Harpreet

On 8/15/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
> On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
> > So you mean to say DDL statements can't be put in one single transaction
> in
> > Oracle ?
>
> You can put them in, but then they will cause the previous DMK to be
> silently committed
>

Re: [GENERAL] Transactional DDL

And this feature i.e. transactional DDL is not there in other major RDBMS
like sql server, oracle etc?

thanks
~Harpreet


On 8/15/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
> > I read a few lines about SP compilation in postgres
> >
> http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
>
> > Is this what the Transactional DDL feature of postgresql talks about ?
>
> I'd say it's one very small aspect of what's involved in that.
>
> Updates of stored procedures are a relatively trivial matter, because a
> procedure is defined by just a single catalog entry (one row in
> pg_proc).  So either you see the new version or you see the old version,
> not much to talk about.  The DDL updates that are really interesting
> ... at least from an implementor's standpoint ... are the ones that
> involve coordinated changes to multiple catalog entries and some
> underlying filesystem files as well.  In other words, ALTER TABLE.
> There are not that many other systems that can choose to commit or roll
> back an arbitrary collection of ALTER TABLE commands.
>
> This doesn't come for free of course.  What it mostly costs you in
> Postgres-land is transient disk space requirements, since we have to
> store both the "before" and "after" states until commit/rollback.
>
>regards, tom lane
>

Re: [GENERAL] Transactional DDL

So you mean to say DDL statements can't be put in one single transaction in
Oracle ?

On 8/15/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
> On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
> >
> > Hi,
> > I read a few lines about SP compilation in postgres
> >
> >
> http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html
> >
> > 1. stored procedure compilation is transactional.
> > "You can recompile a stored procedure on a live system, and only
> > transactions starting after that compilation will see the changes," he
> said.
> > "Transactions in process can complete with the old version. Oracle just
> > blocks on the busy procedure."
> >
> > Is this what the Transactional DDL feature of postgresql talks about ?
>
> That's just one of the DDLs that postgresql can handle in a
> transaction.  Basically, create / drop database and create / drop
> tablespace aren't transactable.  Anything else is fair game.  Note
> that wrapping alter table or reindex or truncate in a long running
> transaction will likely lock the table for an unacceptable period of
> time.  But, putting a migration script that includes DDL and DML
> together and wrapping it in begin; commit; pairs means that either it
> all goes or none does, and the locks on alter table etc are only held
> for the period it takes the migration script to run.
>
> Oracle's lack of transactable DDL means you HAVE to take your system
> down and have rollback scripts ready to go should your migration fail.
> Having worked with both databases, I can honestly say this is one of
> the areas PostgreSQL seriously beats Oracle in terms of usefulness.
>

[GENERAL] Transactional DDL

 Hi,
I read a few lines about SP compilation in postgres

http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html


*1. stored procedure compilation is transactional. *
*"You can recompile a stored procedure on a live system, and only
transactions starting after that compilation will see the changes," he said.
"Transactions in process can complete with the old version. Oracle just
blocks on the busy procedure." *

Is this what the Transactional DDL feature of postgresql talks about ?

Thanks
~Harpreet

[GENERAL] Performance check of my database

2007-08-12 Thread Harpreet Dhaliwal

Hi,

Lately I completed the business logic of my application and all related
database work.

Now i need to check the performance of my database, how much load it can
bear, perfomance to different queries and stored procedures.

Basically i need to do the performance testing of my database and based on
that I need to take a call whether i should go for clustering or not.

Please let me know the best practices in postgres for such an activity.

Thanks ,
~Harpreet

Re: [GENERAL] Duplicate Unique Key constraint error

2007-07-12 Thread Harpreet Dhaliwal

How can one rollover a sequence back to zero after you delete records from a
table with one such sequence.
I see it starting with the last value of the sequence inserted.

On 7/11/07, Tom Allison <[EMAIL PROTECTED]> wrote:

On Jul 10, 2007, at 3:09 PM, Tom Lane wrote:

>
> "Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
>> Transaction 1 started, saw max(dig_id) = 30 and inserted new
>> dig_id=31.
>> Now the time when Transaction 2 started and read max(dig_id) it
>> was still 30
>> and by the time it tried to insert 31, 31 was already inserted by
>> Transaction 1 and hence the unique key constraint error.
>
> This is exactly why you're recommended to use sequences (ie serial
> columns) for generating IDs.  Taking max()+1 does not work, unless
> you're willing to lock the whole table and throw away vast amounts of
> concurrency.

I wonder how SQL server is handling this?  Are they locking the table?
I realize it's off-topic, but I'm still curious.

Sequences are your friend.  they come in INT and BIGINT flavors, but
BIGINT is a lot of rows.

Can set set Sequences to automatically rollover back to zero?

Re: [GENERAL] Duplicate Unique Key constraint error

2007-07-10 Thread Harpreet Dhaliwal

Thanks alot for all your suggestions gentlemen.
I changed it to a SERIAL column and all the pain has been automatically
alleviated :)

Thanks a ton.
~Harpreet

On 7/10/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
> Transaction 1 started, saw max(dig_id) = 30 and inserted new dig_id=31.
> Now the time when Transaction 2 started and read max(dig_id) it was
still 30
> and by the time it tried to insert 31, 31 was already inserted by
> Transaction 1 and hence the unique key constraint error.

This is exactly why you're recommended to use sequences (ie serial
columns) for generating IDs.  Taking max()+1 does not work, unless
you're willing to lock the whole table and throw away vast amounts of
concurrency.

regards, tom lane

Re: [GENERAL] Duplicate Unique Key constraint error

2007-07-10 Thread Harpreet Dhaliwal


I lately figured out the actual problem PHEW.
Its something like two different transactions are seeing the same snapshot
of the database.

Transaction 1 started, saw max(dig_id) = 30 and inserted new dig_id=31.
Now the time when Transaction 2 started and read max(dig_id) it was still 30
and by the time it tried to insert 31, 31 was already inserted by
Transaction 1 and hence the unique key constraint error.

I thought this would be taken care by the database itself by locking the
transactions but now I really don't know how does this locking takes place
in postgres. I used to work with SQL Server and never faced this problem
there.

Please guide me throug to get rid of this problem.

Thanks,
~Harpreet

On 7/10/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:


my primary key is neither SERIAL nor a SEQUENCE.

CONSTRAINT pk_dig PRIMARY KEY (dig_id)

This is the clause that I have for my primary key in the create table
script.

thanks,
~Harpreet

On 7/10/07, Ron St-Pierre <[EMAIL PROTECTED]> wrote:
>
> Harpreet Dhaliwal wrote:
> > Hi,
> >
> > I keep getting this duplicate unique key constraint error for my
> > primary key even
> > though I'm not inserting anything duplicate. It even inserts the
> > records properly
> > but my console throws this error that I'm sure of what it is all
> about.
> >
> > Corruption of my Primary Key can be one of the possibilities but I'm
> > really not sure how
> > to get rid of this corruption and how to re-index the primary key.
> >
> > Also, I was wondering what could be the cause of this PK  corruption,
> > if possible and what does can this corruption lead to.
> > I mean what are its cons.
> >
> > Thanks,
> > ~Harpreet
> You haven't really given any useful information about your primary key,
> but if you are using SERIAL as the column type (INT type with a
> sequence) you may just be having a problem with its current value (but
> then inserts shouldn't work).
>
> If you are using a sequence here, see what it's current value is and
> compare it to the highest value in the column. If its value is less than
>
> the columns max() value, just reset the value in the sequence.
>
> imp=# CREATE TABLE dup_pkey (id SERIAL PRIMARY KEY, insert_order int);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (1);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (2);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (3);
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (4);
>
> imp=# SELECT * FROM dup_pkey;
> id | insert_order
> +--
>   1 |1
>   2 |2
>   3 |3
>   4 |4
> (4 rows)
>
> Now, if you set the value below what the max() column value is, you will
> have a problem with inserts.
> imp=# SELECT setval('dup_pkey_id_seq',3);
> setval
> 
>   3
> (1 row)
>
> imp=# INSERT INTO dup_pkey (insert_order) VALUES (5);
> ERROR:  duplicate key violates unique constraint "dup_pkey_pkey"
>
>
> If this is the case, use setval() to update the value of the sequence to
>
> the max() value of your primary key. You can use \d to get information
> about your table, including the sequence name. However if, as you say,
> it IS inserting records properly, then this ISN'T going to help.
>
> hth
>
> Ron
>

Re: [GENERAL] Duplicate Unique Key constraint error

2007-07-10 Thread Harpreet Dhaliwal

my primary key is neither SERIAL nor a SEQUENCE.

CONSTRAINT pk_dig PRIMARY KEY (dig_id)

This is the clause that I have for my primary key in the create table
script.

thanks,
~Harpreet

On 7/10/07, Ron St-Pierre <[EMAIL PROTECTED]> wrote:

Harpreet Dhaliwal wrote:
> Hi,
>
> I keep getting this duplicate unique key constraint error for my
> primary key even
> though I'm not inserting anything duplicate. It even inserts the
> records properly
> but my console throws this error that I'm sure of what it is all about.
>
> Corruption of my Primary Key can be one of the possibilities but I'm
> really not sure how
> to get rid of this corruption and how to re-index the primary key.
>
> Also, I was wondering what could be the cause of this PK  corruption,
> if possible and what does can this corruption lead to.
> I mean what are its cons.
>
> Thanks,
> ~Harpreet
You haven't really given any useful information about your primary key,
but if you are using SERIAL as the column type (INT type with a
sequence) you may just be having a problem with its current value (but
then inserts shouldn't work).

If you are using a sequence here, see what it's current value is and
compare it to the highest value in the column. If its value is less than
the columns max() value, just reset the value in the sequence.

imp=# CREATE TABLE dup_pkey (id SERIAL PRIMARY KEY, insert_order int);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (1);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (2);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (3);
imp=# INSERT INTO dup_pkey (insert_order) VALUES (4);

imp=# SELECT * FROM dup_pkey;
id | insert_order
+--
  1 |1
  2 |2
  3 |3
  4 |4
(4 rows)

Now, if you set the value below what the max() column value is, you will
have a problem with inserts.
imp=# SELECT setval('dup_pkey_id_seq',3);
setval

  3
(1 row)

imp=# INSERT INTO dup_pkey (insert_order) VALUES (5);
ERROR:  duplicate key violates unique constraint "dup_pkey_pkey"

If this is the case, use setval() to update the value of the sequence to
the max() value of your primary key. You can use \d to get information
about your table, including the sequence name. However if, as you say,
it IS inserting records properly, then this ISN'T going to help.

hth

Ron

[GENERAL] Duplicate Unique Key constraint error

2007-07-09 Thread Harpreet Dhaliwal


Hi,

I keep getting this duplicate unique key constraint error for my primary key
even
though I'm not inserting anything duplicate. It even inserts the records
properly
but my console throws this error that I'm sure of what it is all about.

Corruption of my Primary Key can be one of the possibilities but I'm really
not sure how
to get rid of this corruption and how to re-index the primary key.

Also, I was wondering what could be the cause of this PK  corruption, if
possible and what does can this corruption lead to.
I mean what are its cons.

Thanks,
~Harpreet

[GENERAL] Capturing return value of a function in Pl/Perl

2007-07-06 Thread Harpreet Dhaliwal


Hi,

I'm calling a plpgsql function in a plperl function.
plpgsql function is a simple select query returning a single row of records.
How can i retrieve the values of different fields in my plperl function?

If i write something like

my $query = "SELECT sp_select";
my $exec = spi_exec_query($sp_select);

How can values returned by executing $query be captured in my perl code ?

Thanks,
Harpreet

[GENERAL] writing debug output in perl

2007-06-22 Thread Harpreet Dhaliwal


Hi,
Can anyone help me out with funciton(s) to write a debug output in a perl
function on postgres.
Thanks,
~Harpreet

Re: [GENERAL] Transactional DDL


my bad.. i replied to that in a wrong thread. sorry

On 6/2/07, Leif B. Kristensen <[EMAIL PROTECTED]> wrote:


On Saturday 2. June 2007 20:39, Ron Johnson wrote:
>You were politely asked not to top-post.
>
>On 06/02/07 11:46, Harpreet Dhaliwal wrote:
>> So, while writing any technical document, would it be wrong to
>> mention stored procedures in postgresql?
>> what is the general convention?
>
>Did I miss something?  What does "stored procedures" have to do with
>  "Transactional DDL"?

I believe that he posted this in reply to the "Stored procedures and
functions" thread. It kind of fits in there.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Transactional DDL


On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:




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?






Exactly. this seems like proving the ACIC property of a database thats true
for every RDBMS.
Whats so different in postgresql then?

Re: [GENERAL] Transactional DDL


So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?

On 6/2/07, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:


On 6/2/07, Jasbinder Singh Bali <[EMAIL PROTECTED]> wrote:
> 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

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2.  Value 1 remains in the
table,
-- because it is already committed.

   Regards,
   Dawid

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Transactional DDL


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?
I can't see any Transactional DDL philosophy here.
Could you please throw some more light on it to point out the transactional
DDL fundamental as
pointed out by Jas?

Thanks,
~Harpreet

On 6/2/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:



On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote:

> 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

test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
(2 rows)

test=# begin;
BEGIN
test=# create table foo (a integer);
CREATE TABLE
test=# insert into foo (a) values (1);
INSERT 0 1
test=# commit;
COMMIT
test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)

test=# select * from foo;
a
---
1
(1 row)

test=# begin;
BEGIN
test=# create table bar (a integer);
CREATE TABLE
test=# insert into bar (a) values (1);
INSERT 0 1
test=# select * from bar;
a
---
1
(1 row)

test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | bar  | table | postgres
public | foo  | table | postgres
(4 rows)

test=# select 1/0;
ERROR:  division by zero
test=# commit;
ROLLBACK
test=# \d
 List of relations
Schema | Name | Type  |  Owner
+--+---+--
public | a| table | postgres
public | b| table | postgres
public | foo  | table | postgres
(3 rows)


Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Stored Procedures and Functions


Hi,

Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.

Thanks,
~Harpreet

Re: [GENERAL] why postgresql over other RDBMS

2007-05-26 Thread Harpreet Dhaliwal


is the host base configuration methodology in postgres superior to other
RDBMS.
is this something novel that postgres has come up with?

~Harpreet

On 5/26/07, Tom Lane <[EMAIL PROTECTED]> wrote:


Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> A more interesting question is what sort of hardware you need for that
>> actually to be a win, though.  Loading a few tables in parallel sounds
>> like an ideal recipe for oversaturating your disk bandwidth...

> you don't actually need that much of disk bandwidth both COPY and CREATE
> INDEX are CPU bottlenecked on modern boxes and reasonable disk
> subsystems - spreading their work over multiple cores/processes can give
> big benefits.

Hmm ... I wonder if that's true for COPY BINARY ...

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Vacuum DB in Postgres Vs similar concept in other RDBMS

2007-05-23 Thread Harpreet Dhaliwal

I was just wondering if Vacuum Db in postgresql is somehow superior to the
ones that we have in other RDBMS.

On 5/23/07, Alexander Staubo <[EMAIL PROTECTED]> wrote:

On 5/23/07, Dann Corbit <[EMAIL PROTECTED]> wrote:
> In SQL*Server it is called "UPDATE STATISTICS"
>
> http://msdn2.microsoft.com/en-us/library/ms187348.aspx

No -- MS SQL Server's "update statistics" is the equivalent of
"analyze", not "vacuum."

Alexander.

[GENERAL] Vacuum DB in Postgres Vs similar concept in other RDBMS

2007-05-23 Thread Harpreet Dhaliwal


Hi,

I was wondering if Vacuum DB concept in Postgres is really novel and there's
no concept like this in other RDBMS like oracle or sql server.
If at all other RDBMS have such a concept implemented, how good or bad it is
as compared to postgres's vacuum db concept.

Any type of pointers would be highly appreciated.

Thanks,
~Harpreet

[GENERAL] replace function in a query

2007-05-21 Thread Harpreet Dhaliwal


Hi,

I have to write a query that does the following.

select column_x from tbl_xyz
where column_y = 'abc def ghi'

new line characters i.e. '\n' in column_y should be replaced with a space
character i.e. ' ' in this query.
Is there any replace function that when used in the query with colulmn_y
would do it?

Thanks,
~Harpreet

Re: [GENERAL] Database transaction related

2007-05-14 Thread Harpreet Dhaliwal


Thanks alot john.
that was helpful

On 5/14/07, John D. Burger <[EMAIL PROTECTED]> wrote:


Jasbinder Singh Bali wrote:

> could you please elaborate this concept of queue table?

The basic idea is to for the insert trigger to not explicitly kick
off the work you need to have done, but simply insert a row into a
"work request table".  A separate process notices that there is a
work request, and performs the work.  This can be done by a periodic
cron job, or using NOTIFY/LISTEN.

There was a fairly detailed discussion of this last month - the
thread starts here:

   http://archives.postgresql.org/pgsql-general/2007-04/msg01152.php

- John D. Burger
   MITRE



---(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] Printing values on pgadmin tool window using plperlu

2007-05-13 Thread Harpreet Dhaliwal


Hi

I have a function written in language plpelu.
Normally, values in perl code are printed using
print statement.
Then same when used in postgres environment won't print messages in
'Messages' tab to pgadmin query tool window.
How and where can I print these values while running the plperlu function.

Thanks,
~Harpreet

[GENERAL] SQL Transaction related

2007-05-09 Thread Harpreet Dhaliwal


Hi,

I have a transaction like following:

BEGIN

INSERT INTO tbl_xyz VALUES (val1, val2);

   SELECT INTO wid MAX(val1) FROM tbl_xyz;

END;

My question is in the SELECT INTO statement, will I get the value of val1
from the INSERT INTO in the same transaction
even though the transaction has not ended yet.
I think no.
How would I get that latest value of val1 in the same transaction because
its not committed yet as the transaction has not ended.

Thanks,

~Harpreeet

[GENERAL] printing variable values in pl/pgsql functions

2007-05-08 Thread Harpreet Dhaliwal


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] printing variable values in pl/pgsql functions

2007-05-08 Thread Harpreet Dhaliwal


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] Postgres (selection of thesis topic)

2007-05-01 Thread Harpreet Dhaliwal


Hi,
I'm kind of new to postgresql and the project that I'm working on currently
deals with parsing emails, storing parsed components in postgresql DB and
fire triggers
on certain inserts that opens socket connection with a unix tools server,
initiates tools like whois, traceroute etc and unix tools server opens ODBC
connection back to same
postgres database and stores the results fetched from running the unix
tools.

In this regard, I have to start working on some thesis topic related to the
postgres database that we are using in the project. It can be in conjunction
with email parsing on unix tools but the theme of the thesis topic should
revolve around postgres database.

I have done alot of homework on this and could think of something like "bulk
of data storage in email parsing and how vacuuming it would increase the
performance" because i think this vacuum DB concept is not there in other
RDBMS. This is just a petty topic but i was thinking something on these
lines.

I have no clue what other options or topics do I have write to start writing
my thesis on.
Any kind of help would be highly appreciated in this regard.

Thanks,
~Harpreet

Re: [GENERAL] IF function?

2007-04-30 Thread Harpreet Dhaliwal


IF ( condition here)
-- to do
ELSE
 -- to
END IF


On 5/1/07, novnov <[EMAIL PROTECTED]> wrote:



Does postgresql have a built in IF function that allows one to eval a
condition and return one or another value? Like:

IIF(mybooleanfield = true, "It's true", "It's not true")


--
View this message in context:
http://www.nabble.com/IF-function--tf3673523.html#a10264910
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] reindexing keys in postgres

2007-04-06 Thread Harpreet Dhaliwal


Hi,
Lately i was searching for a way I could reindex all my keys. Primary Keys
in particular.
Really didn't find any manual that could guide me through.

Reason i wanted to reindex my PK is that whenever i insert a record in the
table, even though that record is unique, i get an error saying violation of
primary key.
So i thought of reindexing my keys.

Can anyone give me pointers for the same

Thanks,
~Harpreet

[GENERAL] No of triggers of one single table

2007-04-06 Thread Harpreet Dhaliwal


Hi,
Can i have more than one trigger on one single table. Actually I want 2
different events to take place simultaneously and independently after
insert.

~Harpreet.

[GENERAL] introduction of FK results in no data insert

2007-03-31 Thread Harpreet Dhaliwal


Hi,
Upto the point when i don't have FKs in my database, things work fine.
As soon as i put FK in all the tables referencing to  a field of one single
table, nothing is inserted.
Is it an indexing issue?
Can anyone throw some light on this?
~Harpreet

Re: [GENERAL] some ports closed in Fedora


lol

On 2/3/07, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:


Harpreet Dhaliwal <[EMAIL PROTECTED]> schrieb:

> Is there any fedora mailinglist? Never  knew if there was one

Yes, more then one, ask google.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] some ports closed in Fedora


Is there any fedora mailinglist? Never  knew if there was one

On 2/3/07, A. Kretschmer <[EMAIL PROTECTED]> wrote:


am  Sat, dem 03.02.2007, um  9:49:46 -0500 mailte Harpreet Dhaliwal
folgendes:
> Also, wanted to know how to prevent remote root ftp login in fedora.

1. configure your ftp-server appropriate
2. this is a postgresql-mailinglist, not fedore or such
3. i don't like silly fullquote below the answer


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] some ports closed in Fedora

Also, wanted to know how to prevent remote root ftp login in fedora.

On 2/3/07, Andreas Kretschmer <[EMAIL PROTECTED]> wrote:

Harpreet Dhaliwal <[EMAIL PROTECTED]> schrieb:

> Hi,
> though this is not directly related to postgres, but i was wondering why
are
> most of the ports seen closed
> when one does nmap scan of these ports. Whats reason can we attribute to
the
> fact that most of the ports in an OS are
> in closed state.

A port is 'closed', if no process is listen on this port. You can use
'netstat -tulpen' or similar commands to list open ports and processes
which are in LISTEN state.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] some ports closed in Fedora


Hi,
though this is not directly related to postgres, but i was wondering why are
most of the ports seen closed
when one does nmap scan of these ports. Whats reason can we attribute to the
fact that most of the ports in an OS are
in closed state.

Thanks,
Harpreet

Re: [GENERAL] Defining and Using variables in a postgres function

2007-02-02 Thread Harpreet Dhaliwal

About the concurrency control, if i have both Select Max(id) and insert (id)
in the same function, then would it be
a nice idea to put both these statements in the same function or differenct
functions and then put the insert in a transaction and lock the table for
any further query till insert commits.

Also, should i go with a table level lock or a row level lock in this
scenario?
Thanks
~Harpreet

On 2/2/07, Alban Hertroys <[EMAIL PROTECTED]> wrote:

Harpreet Dhaliwal wrote:
> I have a function like the follwoing:
>
> CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text,
> int4,text,text,text,text,text,text,text,timestamp)
>  RETURNS void AS
> $BODY$
> BEGIN
> -- SELECT STATEMENT GOES HERE--
> INSERT INTO tbl_email(option_public,
>  agency , id)
> VALUES ($1,$2) ;
> END;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE;
>
>
> For inserting the id, i need to query a table xyz, fetch the maximum id
in
> it, increment it by 1 and store it in tbl_email.

Shouldn't you circumvent the whole concurrency mess you're getting
yourself into by using a sequence?

You're in trouble if this function gets called concurrently from
different sessions, unless you lock the relevant records. They'll both
see the same MAX(id) and try to insert records with the same id values.

> How should i define this variable first and how to push the result of
the
> query fired on table xyz.

Yes indeed, like this:

DECLARE
   x int;
BEGIN
   SELECT INTO x MAX(id) + 1 FROM xyz;
   INSERT INTO tbl_email(option_public, agency , id)
   VALUES ($1,$2, x) ;

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

[GENERAL] Defining and Using variables in a postgres function

2007-02-01 Thread Harpreet Dhaliwal


I have a function like the follwoing:

CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text,
int4,text,text,text,text,text,text,text,timestamp)
 RETURNS void AS
$BODY$
BEGIN
-- SELECT STATEMENT GOES HERE--
INSERT INTO tbl_email(option_public,
 agency , id)
VALUES ($1,$2) ;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;


For inserting the id, i need to query a table xyz, fetch the maximum id in
it, increment it by 1 and store it in tbl_email.
Right after BEGIN in my function I have a commnet where in I need to query
the xyz table, fetch the max id and store it in a variable and then I can
increment this variable and store it in tbl_email.

How should i define this variable first and how to push the result of the
query fired on table xyz.

Thanks in advance,
~Harpreet

Re: [GENERAL] PostgreSQL data loss

2007-01-28 Thread Harpreet Dhaliwal

While making POC (proof of concept) for any project, we clearly mention at
the end of the document that loss of data is not going to be our
responsibility and thats how we guys save our ass right in the begening.
What happened with you has happened with us many a times but our bold and
italicized lines about data loss have always saved us. I suggest you
something like this for your future projects.
Hope this helps.
Regards

On 1/28/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:

On 1/26/07, BluDes <[EMAIL PROTECTED]> wrote:
> Hi everyone,
>   I have a problem with one of my costomers.
> I made a program that uses a PostgreSQL (win32) database to save its
data.
> My customer claims that he lost lots of data reguarding his own clients
> and that those data had surely been saved on the database.
> My first guess is that he is the one who deleted the data but wants to
> blame someone else, obviously I can't prove it.

I've been working with PostgreSQL since early 7.1 on dozens of
projects and I've had maybe two or three cases of data corruption that
were not explained by hardware failure or something like that (and
even these cases were debatable since I was not in direct control of
the server).  Both of those cases had side effects...the corruption
busted something else which sent immediate red flags that something
was wrong.

I think your customer is CYA.

merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Firing triggers based on certain Insert conditions

2007-01-28 Thread Harpreet Dhaliwal

I never said I don't want my trigger to do anything.
My subject line only made it pretty clear that I want to fire my trigger
based on
certain conditions.
I know its kind of a silly feature request but you really can't help it
when you are working with stupid advisors :)
thanks for your reponse anyways.
Harpreet

On 1/28/07, Tom Lane <[EMAIL PROTECTED]> wrote:

"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
> I want this trigger to fire only when after Insert this field 'source'
> has value = 'from', otherwise trigger should not be fired at all.
> Just wondering if its really possible?

No, and it seems pretty silly as a feature request.  Why don't you just
put the test in the trigger, and have it do nothing when you don't want
it to do anything?

   regards, tom lane

[GENERAL] Firing triggers based on certain Insert conditions

2007-01-28 Thread Harpreet Dhaliwal


Hi
I have a table in which i have a field named 'source'
A trigger is written on this table.
I want this trigger to fire only when after Insert this field 'source'
has value = 'from', otherwise trigger should not be fired at all.
Just wondering if its really possible?

Thanks in advance.
Harpreet

Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions

2007-01-18 Thread Harpreet Dhaliwal

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

Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions

2007-01-16 Thread Harpreet Dhaliwal

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

Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions

2007-01-16 Thread Harpreet Dhaliwal

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;

To include abc.pl here, how is the path of abc.pl specified. Also,
just wondering if the structure of above function is correct atall at
the first place

~Harpreet

On 1/16/07, John DeSoi <[EMAIL PROTECTED]> wrote:

I assume your choices are to include the entire script in a
PostgreSQL function or simply create a small stub function that calls
a function you have loaded with use or require (as mentioned by the
other poster). But either way, you have to create a function so you
can tell PostgreSQL what to call, what the parameters are, and what
values should be returned.

John

On Jan 16, 2007, at 10:25 AM, Jasbinder Singh Bali wrote:

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

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] NEED URGENT HELP....

2006-12-25 Thread Harpreet Dhaliwal


select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record

it would match ur query against '1,4' for the corressponding field in the
table.
do u really have one such value for that field in your table, i mean '1,4'
??
it won't search for 1 and 4 separately if that is what you want your query
to work.

~Harpreet


On 12/21/06, Sandip G <[EMAIL PROTECTED]> wrote:


 I am using PostgreSql 8.1 with pgAdmin III. OS is XP.

 this is my function:

CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
  RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT  BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
  AddInfo1, AddInfo2
FROMT_PHASE
WHERE   (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
  LANGUAGE 'sql' VOLATILE;


When I run
select * from sp_get_phase ('sandip', 'oms', '4')   returns 1
record.this works fine

select * from sp_get_phase ('sandip', 'oms', '1')  returns 1
record.this also works fine... BUT

select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record.

I tried to execute the SQL statement from the function

SELECT  BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
  AddInfo1, AddInfo2
FROMT_PHASE
WHERE   (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany')
AND BOOK_NO IN (1,4)

- This Works fine... returns 2 records.   What may be the problem?

Thanks in advance.
Regards,
Sandip.



--

Re: [GENERAL] why not kill -9 postmaster

2006-10-22 Thread Harpreet Dhaliwal

what type of start up script are you talking about here?
On 10/21/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Ian Harding" <[EMAIL PROTECTED]> writes:
> On 10/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:>> Personally I think the TIP that's really needed is "never remove>> postmaster.pid by hand".
> When the machine crashes, don't you have to remove the pid file by> hand to get the Postgres to start?  I seem to remember having to do> thatGiven a properly written startup script and a reasonably recent
postmaster, that shouldn't be necessary.  In any case, retrying thestartup script is a *far* safer habit to develop than manually removingthe pidfile (and putting an "rm" into the script itself is folly of the
first magnitude).   regards, tom lane---(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] why not kill -9 postmaster

2006-10-20 Thread Harpreet Dhaliwal

After all that discussion that took place while i was sleeping, I have a few more questions simply haunting me.Someitmes, rather most of the times, when I start postgres using pg_ctl, it says antoher postmaster is running. Being a total naive about the hazzards of kill -9 postmaster, i simply used to kill -9 all postmaster related process IDs. 
Now, what should i do to get rid of the postmaster that is already running from a safe perspective.Also, even though it says, postmaster is still running, i can't start my pgadmin because it starts crying over the fact that postgres server is not running.
Another thing that worries me is the importance of postmaster.pid.What happens if I simply do rm postmaster.pid after killing all the postmaster processes.How big a pain in the neck is that going to be?
Thanks,~HarpreetOn 10/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Martijn van Oosterhout  writes:> Well, if you kill -9 the postmaster all the connections stay alive and> stay processing tuples and writing to disk, except the coordination is
> gone.The postmaster isn't involved in any critical inter-backend coordination.If you kill -9 the postmaster *and then kill or wait out all thebackends*, you won't lose data.  This is not a desirable long-term
operating mode, because it cripples autovacuum and some other things,but it's not dangerous.The only really serious risk I'm aware of in this scenario is:1. DBA does "kill -9" postmaster, but some backends are still alive and
processing.2. DBA tries to start new postmaster, gets message about "shared memorysegment still in use".3. DBA does "rm postmaster.pid" (this is the step that qualifies himas an idiot).
4. DBA starts new postmaster.  Since the interlock file is gone, itstarts up without any awareness that there are old backends still alive.At this point, you have two separate sets of backends that are not
communicating (they're using two different shared memory segments)but they are munging the same data files.  It will not take longto turn the data files into irrecoverable hash --- for just onereason, transaction numbering will diverge between the two sets of
backends.regards, tom lane---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to   choose an index scan if your joining column's datatypes do not
   match

Re: Can't get ECPG to connect (was Re: [GENERAL] Urgen help required)

2006-10-20 Thread Harpreet Dhaliwal

Jas, you might need to check the uplink and downlink time of your network router.
Also,check the postgresql.conf file to see various timeouts there. Comment all the timeouts to set them to infinity so that your Unix tools server keeps  trying connecting to the Postgres server. 
Also, check if you closed the client socket in ur postgres database. This might be one of the reasons of the whole fuss.
 
Also, your  "Urgent help required" subject line is more than enough to catch an eye. I don't think it needs any amendment. 
Anyways, do let me know if you need any further help. I'll be glad to assist you with this.
You are working on the side effects of  postgres and its quite obvious that you'll face all such problems. Keep your cools.
 
Regards,
~Harpreet. 
On 10/18/06, Bill Moran <[EMAIL PROTECTED]> wrote:
In response to "J S B" <[EMAIL PROTECTED]>:> Hi,
> I have the following scenario:->> 1. There's a function in the postgres datbase that when executed calls a> shared object (dynamically loaded).> 2. The shared object is a Client (made using socket library) which connects
> to a server (coded again using socket library) hosted on some other machine.> 3. This server when invoked through the postgres function described above> has an ECPG code in it that gets executed after a successful write back to
> the client, which inserts a record in a table in the postgres database.>> I use ECPGdebug in the begening of the server code file to see the state of> affairs with the ECPG code and it always raises -402 sqlcode which means the
> connection attempt to the database did not succeed.>> All firewalls in the datbase server and this machine wherein my socket> server is running are disabled.> Other function calls from this machine to my database machine are working
> fine.>> Also, my postgres function is able to establish connection with the this> outside deamon (that i refer to as server coded using socket library).> Just the ECPG calls from this deamon back to postgres database are not
> successful.>> Any kind of help would be highly appreciated.How about help posting a better question.First off, use a descriptive subject.  Notice how I've improved this foryou.
Secondly, always cut/paste exact error messages.  There are some peoplewho will quickly know exactly what is wrong if they see the whole errormessage.Thirdly, have a look in the PostgreSQL server logs.  If there's nothing
telling there, bump up the authentication/connection debugging inpostgresql.conf and try again.  If you get messages but aren't sure whatthey mean, post them to the list.Fourthly, be more specific.  It would be worthwhile to show the snippit
of code that connects, so people know whether you're using IP sockets areunix domain sockets.Fifthly, are you using unix domain sockets or IP sockets?  And are theappropriate permissions in pg_hba.conf?
--Bill MoranCollaborative Fusion Inc.---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] why not kill -9 postmaster

2006-10-20 Thread Harpreet Dhaliwal

Its always said that don't kill -9 postmaster.
Whats the reason not to do it. Why is it so strictly  prohibited?
 
Thanks,
~Harpreet.

[GENERAL] calling two shared objects in one single function call

Hi,Can two different shared objects be loaded one after the other in the same function so that the second one starts its execution right after the first one finishes.~Harpreet

[GENERAL] function for current date-time

Hi,I have a timestamp field in my table and want to set a default value of current date/time for it.What should i enter as its default value? is there any function like now() in postgres?Thanks,~Harpreet

Re: [GENERAL] difference b/w text and varchar datatypes

can you please provide me the link for the same.thanksOn 10/16/06, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Harpreet Dhaliwal wrote:> Hi,> Just wanted to know how would it make a difference if i use text datatype
> instead of varchar.See FAQ item.--  Bruce Momjian   [EMAIL PROTECTED]  EnterpriseDBhttp://www.enterprisedb.com
  + If your life is a hard drive, Christ can be your backup. +

[GENERAL] difference b/w text and varchar datatypes

Hi,Just wanted to know how would it make a difference if i use text datatype instead of varchar.Thanks,~Harpreet

[GENERAL] Server Added Y'day. Missing Today

2006-10-13 Thread Harpreet Dhaliwal

Hi,Yesterday I configured a postgres server using Pgadmin3, restored the database from a backup.Today when i come and check my pgadmin, i can't see any server added there.Kind of confused.Can anyone please let me know whats happeneing here and what should i do to see the server that I added yesterday.
Thanks,Harpreet

Re: [GENERAL] Trigger (Transaction related)

2006-09-01 Thread Harpreet Dhaliwal

Exactly Tom,I was missing a few paranthesis like a nut.Thanks for pointing out.Thanks and regards~HarpreetOn 9/1/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"Harpreet Dhaliwal" <
[EMAIL PROTECTED]> writes:> sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d,> SELECT contents FROM parser WHERE id = %d ), id, id);
> SPI_exec(buffer,0).You're short some parentheses --- try executing a comparable querymanually.regards, tom lane

Re: [GENERAL] Trigger (Transaction related)

2006-09-01 Thread Harpreet Dhaliwal

Thats OK, but my question is why did the original insert not take place. Syntax error was in the select query that is fired as a result of the triggerThanks,~Harpreet
On 9/1/06, Roman Neuhauser <[EMAIL PROTECTED]> wrote:
# [EMAIL PROTECTED] / 2006-09-01 03:19:03 -0400:> If that is the case then why does it throw error in one on the insert> queries in the shared object written in SPI without inserting the row on the
> table on which record is inserted.>> Follwing query in the shared object throws an error.>>> INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM> parser WHERE id = 1)
>> Error is> ERROR:  syntax error at or near "SELECT" at character 53Because there's a syntax error.test=# INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1);
ERROR:  syntax error at or near "SELECT" at character 53LINE 1: ...SERT INTO headers (id, header_content) VALUES (1, SELECT raw... ^
test=# INSERT INTO headers (id, header_content) VALUES (1, (SELECT raw_email FROM parser WHERE id = 1));ERROR:  relation "headers" does not existtest=#> Regardless the error in the shared object, why doesn't it insert the
> row in the table on which AFTER INSERT trigger is written?Perhaps you should verify the syntax of your queries in a lessdemanding environment, like psql, first.--How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.Cause you weren't THERE. http://bash.org/?255991

Re: [GENERAL] Trigger (Transaction related)

2006-09-01 Thread Harpreet Dhaliwal

If that is the case then why does it throw error in one on the insert queries in the shared object written in SPI without inserting the row on the table on which record is inserted.Follwing query in the shared object throws an error.
INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1)Error is ERROR:  syntax error at or near "SELECT" at character 53Regardless the error in the shared object, why doesn't it insert the row in the table on which AFTER INSERT trigger is written?
Thanks,~HarpreetOn 9/1/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Fri, Sep 01, 2006 at 02:51:59AM -0400, Harpreet Dhaliwal wrote:> Hi,> I've written a trigger after insert on a table (parser) and the trigger> calls a function that dynamically loads a shared object written in C.
>> This shared object intends to use the newly inserted row in the table on> which trigger is written and insert values in another table, something like> this,> Above line of code is not able to read the values of newly inserted code 
i.e> SELECT contents FROM parser WHERE id = %d> and throws an error.The new values are in the "NEW" pseudo-tuple. See the triggerdocumentation on how to access it. In the pl/sql and pl/pgsql languages
you just say "NEW.". Not sure about C though.> Is it because its still the same transaction going on and the insert fired> on parser talbe won't be reflected in the select query in the shared object
> which is a part of the same transaction???I don't think its visible in the table yet for a BEFORE INSERT trigger.AFTER INSERT maybe, but in either case, the NEW tuple has the actualvalues.
Hope this helps,--Martijn van Oosterhout   <kleptog@svana.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)iD8DBQFE99qBIB7bNG8LQkwRAq7KAJ4p+rTE1TYgKjpydTlb30YY0yDdFgCghDiPYj3c+7ayJihJ9LKYwrFYoX0==4W51-END PGP SIGNATURE-

[GENERAL] Trigger (Transaction related)

2006-08-31 Thread Harpreet Dhaliwal

Hi,I've written a trigger after insert on a table (parser) and the trigger calls a function that dynamically loads a shared object written in C.This shared object intends to use the newly inserted row in the table on which trigger is written and insert values in another table, something like this,
sprintf(buffer, "INSERT INTO headers (id, header_content) VALUES ( %d, SELECT contents FROM parser WHERE id = %d ), id, id);SPI_exec(buffer,0).Above line of code is not able to read the values of newly inserted code 
i.e SELECT contents FROM parser WHERE id = %d and throws an error.Is it because its still the same transaction going on and the insert fired on parser talbe won't be reflected in the select query in the shared object which is a part of the same transaction???
Regards,~Harpreet

Re: [GENERAL] #include "executor/spi.h" throwing errors

Problem was that i was using -L as in LEAMON, not I as in Indigo.Its working now. Martijn, thanks alot for your patience. I realy don't know why was I doing -L, actually i was thinkin of linking something. HUH.
Had to include. Thanks alot once again~HarpreetOn 8/30/06, Martijn van Oosterhout <kleptog@svana.org
> wrote:On Wed, Aug 30, 2006 at 04:14:20AM -0400, Harpreet Dhaliwal wrote:
> Did not understand about capital L and small L.> What should i be using, capital or small.Please use a font that distinguishes between I and l. It's capital Ilike in INDIGO. Not an L at all...
> I also tried> gcc -I `pg_config --includedir-server` a.c>> and it still says executor/spi.h doesn't exist.Then your setup is badly broken. "pg_config --includedir-server" should
point to the include directory of your postgres installation.Have a nice day,--Martijn van Oosterhout   <kleptog@svana.org>   
http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFE9UrRIB7bNG8LQkwRAskiAKCB8L4FUs5JfmaNalY/g4FetZjzqQCfWCwg
50juNfNEXM/nkIqH0EbmgOo==wbAT-END PGP SIGNATURE-

Re: [GENERAL] #include "executor/spi.h" throwing errors

Did not understand about capital L and small L. What should i be using, capital or small.I also tried gcc -I `pg_config --includedir-server` a.cand it still says executor/spi.h doesn't exist.
On 8/30/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Wed, Aug 30, 2006 at 03:53:13AM -0400, Harpreet Dhaliwal wrote:> Also,> Can you please email me ur directory structure, as in, absolute path> of a.cand absolute path of> spi.h.The absolute path of 
a.c is the irrelevent, it's in the currentdirectory. executor/spi.h is in /usr/include/postgresql/server.BTW, in your sample output you have -l (lowercase L), why? If it were -I(capital I) I could understand.
The directory structure is simply the normal one.gcc -I `pg_config --includedir-server` a.cwill aso work.--Martijn van Oosterhout   <kleptog@svana.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)iD8DBQFE9UXOIB7bNG8LQkwRAvCmAKCHbkjgjiQWKOGTRhpNAvPWNiDaCACgkJzJZn1eVoDmB4AwDf0gzjlMENQ==31fE-END PGP SIGNATURE-

Re: [GENERAL] #include "executor/spi.h" throwing errors

Also, Can you please email me ur directory structure, as in, absolute path of a.c and absolute path of spi.h.Thanks,~HarpreetOn 8/30/06, Harpreet Dhaliwal
 <[EMAIL PROTECTED]> wrote:
Also,a.c is at/usr/local/pgsql/hpsand sp.h is at/usr/include/pgsql/server/executorDoes that make a difference?Regards,~harpreet
On 8/30/06, 
Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:

Also, Where should the directoriresexecutor, nodes, utils, access, catalog be? Over here, its searching it in /usr/includeHowever, these files are located at /usr/include/pgsql/server/When i copy these directories to /usr/include, it throws the error that I posted in the form 
of a file a minute ago.If i don't copy these files to /usr/include and let them be where they are i.e /usr/include/pgsql/serverthen it says, file /executor/spi.h not found.don't know what is this mess all about.
Thanks,~HarpreetOn 8/30/06, Harpreet Dhaliwal <

[EMAIL PROTECTED]> wrote:
Hi,Please find attached the error file.
Thanks~Harpreet
On 8/30/06, Martijn van Oosterhout <
kleptog@svana.org
> wrote:
On Wed, Aug 30, 2006 at 02:59:45AM -0400, Harpreet Dhaliwal wrote:
> Can you please email me your spi.h.> Need to see the path of other header files that spi.h includes and compare> it with what i have.Perhaps you should post some of the actual errors you're getting?
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/spi.h?rev=1.53;content-type=text%2Fplain
That's that standard 8.1 version.Have a nice day,--Martijn van Oosterhout   <kleptog@svana.org
>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFE9Tg+IB7bNG8LQkwRAkJZAJ41NKMEqk/UI/axBkRohYGXQzHrngCfYggr
sK5+2Z5Frf91Z9rFf6Gfm0U==Fyvk-END PGP SIGNATURE-

Re: [GENERAL] #include "executor/spi.h" throwing errors

Also,a.c is at/usr/local/pgsql/hpsand sp.h is at/usr/include/pgsql/server/executorDoes that make a difference?Regards,~harpreetOn 8/30/06, 
Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
Also, Where should the directoriresexecutor, nodes, utils, access, catalog be? Over here, its searching it in /usr/includeHowever, these files are located at /usr/include/pgsql/server/When i copy these directories to /usr/include, it throws the error that I posted in the form 
of a file a minute ago.If i don't copy these files to /usr/include and let them be where they are i.e /usr/include/pgsql/serverthen it says, file /executor/spi.h not found.don't know what is this mess all about.
Thanks,~HarpreetOn 8/30/06, Harpreet Dhaliwal <
[EMAIL PROTECTED]> wrote:
Hi,Please find attached the error file.
Thanks~Harpreet
On 8/30/06, Martijn van Oosterhout <
kleptog@svana.org
> wrote:
On Wed, Aug 30, 2006 at 02:59:45AM -0400, Harpreet Dhaliwal wrote:
> Can you please email me your spi.h.> Need to see the path of other header files that spi.h includes and compare> it with what i have.Perhaps you should post some of the actual errors you're getting?
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/spi.h?rev=1.53;content-type=text%2Fplain
That's that standard 8.1 version.Have a nice day,--Martijn van Oosterhout   <kleptog@svana.org
>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFE9Tg+IB7bNG8LQkwRAkJZAJ41NKMEqk/UI/axBkRohYGXQzHrngCfYggr
sK5+2Z5Frf91Z9rFf6Gfm0U==Fyvk-END PGP SIGNATURE-

Re: [GENERAL] PostgreSQL on system with root as only user

No, you can't.You need postgres user for sure.Postgresql database doesn't run on root account.~HarpreetOn 8/30/06, Fredrik Israelsson <
[EMAIL PROTECTED]> wrote:
Hi!I am about to install PostgreSQL on a minimal Linux system, where rootis the only user that is allowed to exist. I would prefer to use abinary installer. Is it possible to install PostgreSQL without the
postgres user?/Fredrik---(end of broadcast)---TIP 6: explain analyze is your friend

Re: [GENERAL] #include "executor/spi.h" throwing errors

Also, Where should the directoriresexecutor, nodes, utils, access, catalog be? Over here, its searching it in /usr/includeHowever, these files are located at /usr/include/pgsql/server/When i copy these directories to /usr/include, it throws the error that I posted in the form 
of a file a minute ago.If i don't copy these files to /usr/include and let them be where they are i.e /usr/include/pgsql/serverthen it says, file /executor/spi.h not found.don't know what is this mess all about.
Thanks,~HarpreetOn 8/30/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
Hi,Please find attached the error file.Thanks~Harpreet
On 8/30/06, Martijn van Oosterhout <
kleptog@svana.org
> wrote:
On Wed, Aug 30, 2006 at 02:59:45AM -0400, Harpreet Dhaliwal wrote:
> Can you please email me your spi.h.> Need to see the path of other header files that spi.h includes and compare> it with what i have.Perhaps you should post some of the actual errors you're getting?
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/spi.h?rev=1.53;content-type=text%2Fplain
That's that standard 8.1 version.Have a nice day,--Martijn van Oosterhout   <kleptog@svana.org
>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFE9Tg+IB7bNG8LQkwRAkJZAJ41NKMEqk/UI/axBkRohYGXQzHrngCfYggr
sK5+2Z5Frf91Z9rFf6Gfm0U==Fyvk-END PGP SIGNATURE-

Re: [GENERAL] #include "executor/spi.h" throwing errors

Can you please email me your spi.h. Need to see the path of other header files that spi.h includes and compare it with what i have.~HarpreetOn 8/30/06, 
Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
Now i did something like this$ cat a.c#include $ gcc -I /usr/include/pgsql/server -c a.c$still getting the same errors.
The only difference between your and my code being /usr/include/postgresql/server and /usr/include/pgsql/server
On 8/30/06, Martijn van Oosterhout <
kleptog@svana.org> wrote:

On Wed, Aug 30, 2006 at 02:16:36AM -0400, Harpreet Dhaliwal wrote:> Hi,> I'm trying to use SPI for database connectivity at the server side.> This throws hell lot of errors (hundreds of them), even if i don't have any
> SPI code in my C file as of now.>> Can anyone please tell me whats going wrong and what header files should be> kept where?Well, you're doing something wrong then:$ cat a.c

#include $ gcc -I /usr/include/postgresql/server -c a.c$No errors or warnings. Please show the exact command you're using.Have a nice day,--Martijn van Oosterhout   <
kleptog@svana.org>   
http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.
-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFE9S/1IB7bNG8LQkwRAolRAJ9Kx0cFrzFiox0Bva5dD/nps+9EKQCdGsyn+46ZT9EMGLoYC55WGuhaRvQ==eDn0-END PGP SIGNATURE-

Re: [GENERAL] #include "executor/spi.h" throwing errors

2006-08-29 Thread Harpreet Dhaliwal

Now i did something like this$ cat a.c#include $ gcc -I /usr/include/pgsql/server -c a.c$still getting the same errors.The only difference between your and my code being /usr/include/postgresql/server and /usr/include/pgsql/server
On 8/30/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Wed, Aug 30, 2006 at 02:16:36AM -0400, Harpreet Dhaliwal wrote:> Hi,> I'm trying to use SPI for database connectivity at the server side.> This throws hell lot of errors (hundreds of them), even if i don't have any
> SPI code in my C file as of now.>> Can anyone please tell me whats going wrong and what header files should be> kept where?Well, you're doing something wrong then:$ cat a.c
#include $ gcc -I /usr/include/postgresql/server -c a.c$No errors or warnings. Please show the exact command you're using.Have a nice day,--Martijn van Oosterhout   <
kleptog@svana.org>   http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.
-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFE9S/1IB7bNG8LQkwRAolRAJ9Kx0cFrzFiox0Bva5dD/nps+9EKQCdGsyn+46ZT9EMGLoYC55WGuhaRvQ==eDn0-END PGP SIGNATURE-

[GENERAL] #include "executor/spi.h" throwing errors

2006-08-29 Thread Harpreet Dhaliwal

Hi,I'm trying to use SPI for database connectivity at the server side.My database is intialized at /usr/local/pgsql/hpsspi.h is at the follwoing location/usr/include/pgsql/server/executor.
I have a C file that has the SPI code and is located at /usr/local/pgsql/hpsI thin file i do #include "executor/spi.h"This throws hell lot of errors (hundreds of them), even if i don't have any SPI code in my C file as of now.
Can anyone please tell me whats going wrong and what header files should be kept where?Thanks in advance,~Harpreet

Re: [GENERAL] Perl language creation failed

Exactly. Sorry for being so careless. Was thinking something else after being bugged up.Done now. Thanks alot,~HarpreetOn 8/28/06, Jorge Godoy
 <[EMAIL PROTECTED]> wrote:"Harpreet Dhaliwal" <
[EMAIL PROTECTED]> writes:> I did> sudo yum install postgresql-plperl*> and it says dependency perl-base = 2:5.8.8 is missing.
> I did yum install perl-base = 2:5.8.8 and its says nothing to do> Tried sudo yum install perl-base = 2:5.8.8 and says nothing do> Even tried yum install perl-base* and still says nothing to do> Don't know whats going wrong
To start you're not typing the command Tom said you to.  Try it first.--Jorge Godoy  <[EMAIL PROTECTED]>

Re: [GENERAL] Perl language creation failed

I didsudo yum install postgresql-plperl*and it says dependency perl-base = 2:5.8.8 is missing.I did yum install perl-base = 2:5.8.8 and its says nothing to doTried sudo yum install perl-base = 2:5.8.8
 and says nothing doEven tried yum install perl-base* and still says nothing to doDon't know whats going wrong~HarpreetOn 8/28/06, Tom Lane
 <[EMAIL PROTECTED]> wrote:"Harpreet Dhaliwal" <
[EMAIL PROTECTED]> writes:> Can anyone give me the right path for downlaod postgresql-plperl package for> fedora core 5 (32 bit) postgresql version 
8.1.4.sudo yum install postgresql-plregards, tom lane

Re: [GENERAL] Perl language creation failed

Can anyone give me the right path for downlaod postgresql-plperl package for fedora core 5 (32 bit) postgresql version 8.1.4.The one that i found closest to the needs is  not working. Tried a few more but all of them throw the  same problem.
What would be the most authentic source? Thanks,~HarpreetOn 8/28/06, Tom Lane <[EMAIL PROTECTED]
> wrote:"Harpreet Dhaliwal" <
[EMAIL PROTECTED]> writes:> I tried to install postgresql-plperl-8.1package.> It asks for a few dependencies.> i did yum install of those dependencies but says "nothing to do"
My guess is that you're trying to install the wrong package, ie anRPM built for a different distro or different Fedora version.The Red Hat/Fedora RPMs package all the postgres PLs in asingle postgresql-pl RPM.  I don't know where postgresql-plperl
would have come from ... does Debian package it that way?regards, tom lane

Re: [GENERAL] Perl language creation failed

I tried to install postgresql-plperl-8.1package.It asks for a few dependencies.i did yum install of those dependencies but says "nothing to do"Can you tell me whats wrong with it?
Thanks,~Harpreet.On 8/28/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:
I'm fedora core 5 user with PG 8.1
On 8/28/06, A. Kretschmer <[EMAIL PROTECTED]

> wrote:am  Mon, dem 28.08.2006
, um  1:25:51 -0400 mailte Harpreet Dhaliwal folgendes:
> Hi,> I'm trying the following>> CREATE LANGUAGE plperl> before executing my functions written in perl> but get the follwing errors
>> ERROR:  could not access file "$libdir/plperl": No such file or directory
>> Can anyone tell me how to fix this?You need the postgresql-plperl-8.1 (I assume, you are a Debian-User withPG 8.1). Install this package first and then CREATE LANGUAGE.HTH, Andreas
--Andreas KretschmerKontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   
http://wwwkeys.de.pgp.net
---(end of broadcast)---TIP 4: Have you searched our list archives?   
http://archives.postgresql.org

[GENERAL] Perl language creation failed

2006-08-27 Thread Harpreet Dhaliwal

Hi,I'm trying the followingCREATE LANGUAGE plperlbefore executing my functions written in perlbut get the follwing errorERROR:  could not access file "$libdir/plperl": No such file or directory
Can anyone tell me how to fix this?Thanks~Harpreet

[GENERAL] Passing arguments to a trigger function

2006-08-22 Thread Harpreet Dhaliwal

Hi,Can anyone give me pointers for how to pass arguments to a trigger function.I think it is done using tg_argv or something but not very sure how to do it.Regards,Harpreet

Re: [NOVICE] [GENERAL] DB insert Error

2006-08-16 Thread Harpreet Dhaliwal

Also, I think you should check the special characters in the text that you
are trying to store.
 
I realize from one of your emails that when u run the same query using the PGAdmin query tool, only a part
of the the varchar gets stores and rest it trimmed.
 
This has something to do with the special characters like single quote or a back slash.
 
Harpreet. 
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 04:11:23PM -0400, Jasbinder Bali wrote:> I'm sending u the main() function that has the ECPG Insert statement and
> commit in it.The program you posted isn't a simplified example as I requested.The idea is that anybody should be able to compile the code and runit; that's not possible with the code you posted because it's
incomplete.  It also contains many lines that aren't relevant tothe problem, which makes it harder to focus on what is relevant.While stripping down the code I noticed a problem: it never initializesthe i variable before doing this:
>while(!feof(fp))>  {ch[i]=fgetc(fp);>   if(ch[i]=='\n') lines++;>   i++;>  }>ch[i-1]='\0';On my system that results in a segmentation fault and core dump
because i contains garbage, causing ch[i] to point somewhere illegal.It's possible that on your system i contains garbage but that ch[i]points to valid memory, just not to where it should.  As a result,the ch you insert into the database doesn't contain the data it's
supposed to.  See if initializing i = 0 fixes the problem.  Mostcompilers have options to warn about uninitialized variables; I'drecommend using them.If that doesn't help then please post a small (10-20 line), complete
program that anybody could compile and run.--Michael Fuhr---(end of broadcast)---TIP 5: don't forget to increase your free space map settings

[GENERAL] Triggers invoking a stored procedure or a C function

2006-08-16 Thread Harpreet Dhaliwal

Hi,
Conventionally a trigger would fire a few sql queries on a particular event and we have standard code for that.
 
My requirement is to start a stored procedure or a C function as a trigger action.
 
Is this possible?
 
Thanks,
~Harpreet

Re: [NOVICE] [GENERAL] DB insert Error

2006-08-16 Thread Harpreet Dhaliwal

Forgot to write that that was my question.
I mean can we call a stored procedure as an action of a trigger? 
On 8/16/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:

Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function.

Harpreet 

On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]
> wrote: 

On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar 
> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.Are you committing the transaction?  See the bottom of the 
following page:http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html
"In the default mode, statements are committed only when EXEC SQL COMMIT is issued."--Michael Fuhr---(end of broadcast)---TIP 4: Have you searched our list archives?
   http://archives.postgresql.org

Re: [GENERAL] Trigger (Calling a Procedure)

Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function.

Harpreet 
On 8/16/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:

I got your point. 
however, my requirement is something like this.
The trigger shold start another function (a stored procedure) after any event is fired.

how do I accomplish this goal?

Harpreet 

On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:

On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote:> I'm trying to create a trigger with the following definition: 
>> CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON> raw_email>  FOR EACH ROW EXECUTE PROCEDURE add_one(1);>> Procedure / Function add_one has the following definition 
>> CREATE FUNCTION add_one(integer) RETURNS integer> AS '/usr/local/pgsql/jsb/add_one', 'add_one'> LANGUAGE C STRICT;>> function add_one is running fine.>> When I try to create the trigger insert_price_change, it throws me the 
> follwoing error:>> ERROR:  function add_one() does not existTrigger functions must return type "trigger" and they must bedeclared with no arguments.  You can pass an argument as in your 
CREATE TRIGGER statement but a trigger function receives its argumentsdifferently than an ordinary function.  See the Triggers chapterin the documentation, especially "Writing Trigger Functions in C": 
http://www.postgresql.org/docs/8.1/interactive/triggers.html
Unless you need to use C I'd suggest using PL/pgSQL.  Even if youdo need to use C I'd recommend practicing with PL/pgSQL to learn the basics with a simpler language.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html--Michael Fuhr

Re: [NOVICE] [GENERAL] DB insert Error

Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function.
 
Harpreet 
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar
> ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.Are you committing the transaction?  See the bottom of the
following page:http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html"In the default mode, statements are committed only when EXEC SQL
COMMIT is issued."--Michael Fuhr---(end of broadcast)---TIP 4: Have you searched our list archives?  
http://archives.postgresql.org

Re: [GENERAL] Trigger (Calling a Procedure)

I got your point. 
however, my requirement is something like this.
The trigger shold start another function (a stored procedure) after any event is fired.
 
how do I accomplish this goal?
 
Harpreet 
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote:> I'm trying to create a trigger with the following definition:
>> CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON> raw_email>  FOR EACH ROW EXECUTE PROCEDURE add_one(1);>> Procedure / Function add_one has the following definition
>> CREATE FUNCTION add_one(integer) RETURNS integer> AS '/usr/local/pgsql/jsb/add_one', 'add_one'> LANGUAGE C STRICT;>> function add_one is running fine.>> When I try to create the trigger insert_price_change, it throws me the
> follwoing error:>> ERROR:  function add_one() does not existTrigger functions must return type "trigger" and they must bedeclared with no arguments.  You can pass an argument as in your
CREATE TRIGGER statement but a trigger function receives its argumentsdifferently than an ordinary function.  See the Triggers chapterin the documentation, especially "Writing Trigger Functions in C":
http://www.postgresql.org/docs/8.1/interactive/triggers.htmlUnless you need to use C I'd suggest using PL/pgSQL.  Even if youdo need to use C I'd recommend practicing with PL/pgSQL to learn
the basics with a simpler language.http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html--Michael Fuhr

[GENERAL] Trigger (Calling a Procedure)

Hi,I'm trying to create a trigger with the following definition:CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON raw_email  FOR EACH ROW EXECUTE PROCEDURE add_one(1);Procedure / Function add_one has the following definition
CREATE FUNCTION add_one(integer) RETURNS integer AS '/usr/local/pgsql/jsb/add_one', 'add_one' LANGUAGE C STRICT;function add_one is running fine.When I try to create the trigger insert_price_change, it throws me the follwoing error:
ERROR:  function add_one() does not existHowever, I can see function add_one(int4) as one of the functions in  pgadmin.Don't know whats going on wrong.Thanks,~Harpreet

Re: [GENERAL] Connection string

Its doneservice iptables stop did the trickfirewall was running on my DB serverforgot to stop itThanks~HarpreetOn 8/15/06, Michael Fuhr
 <[EMAIL PROTECTED]> wrote:On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:> > Will you take care of it or should I submit a patch?  I've noticed>> I you have the time to write the patch I woul dappreciate it.
I'll submit a patch.  However, in the case of string literals notworking, is that a documentation bug or a code bug?  Are theysupposed to work?> > a few other discrepancies between the documentation and actual
> > behavior, like examples with "VARCHAR val;" that the preprocessor> > rejects with "ERROR: pointer to varchar are not implemented.">> Do you have an example? This surely looks like a bug.
See several code examples in the last half of the "Using HostVariables" documentation:http://www.postgresql.org/docs/8.1/interactive/ecpg-variables.html
Here's a complete example with code pasted from the documentation:% cat foo.pgcint main(void){EXEC SQL BEGIN DECLARE SECTION;int v1;VARCHAR v2;EXEC SQL END DECLARE SECTION;
return 0;}% ecpg foo.pgcfoo.pgc:5: ERROR: pointer to varchar are not implementedIs that a documentation bug or a code bug?--Michael Fuhr---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Connection string

in my previous mail both the ip addressed should be read as 192.168.0.123thanksharpreetOn 8/15/06, Harpreet Dhaliwal <
[EMAIL PROTECTED]> wrote:Hi Micheal,sudde2nly a problem has cropped up in my connection.
Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database dbxyz on 192.168.0.123
 port 5432 for user jsb in line 16 could not connect to server: No route to host Is the server running on host "
192.168.0.110" and accepting TCP/IP connections on port 5432?
My server is very much running. I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.Don't know  whats wrong nowAlso, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my 
test.c file usinggcc -o test test.c -lecpg -L/usr/lib/pgsqlthere's not pgsql directory in /usr/lib.Where exactly do i have to do this linking thing and to what I have to link it.Thanks
~Harpreet
On 8/13/06, Michael Fuhr <
[EMAIL PROTECTED]> wrote:

On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:> Problem was with pg_hba.conf file>> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for> localhost only and not for other ip addresses.
>> I had to change the configuration for IPV4 local connections>> It should have been something like>> hostall all 
192.168.0.0/24   trust
> instead of> host   all all   127.0.0.1/32   trust (which is meant for localhost> only)
>> I think i got it...right?You might want to add 
192.168.0.0/24 on another line rather thanreplacing 127.0.0.1.  And allowing "trust" connections is bad
security practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.Consider using a stronger authentication method and modifying theclient code accordingly.

http://www.postgresql.org/docs/8.1/interactive/auth-methods.html--Michael Fuhr

Re: [GENERAL] Connection string

Hi Micheal,sudde2nly a problem has cropped up in my connection.Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database dbxyz on 192.168.0.123
 port 5432 for user jsb in line 16 could not connect to server: No route to host Is the server running on host "192.168.0.110" and accepting TCP/IP connections on port 5432?
My server is very much running. I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.Don't know  whats wrong nowAlso, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my 
test.c file usinggcc -o test test.c -lecpg -L/usr/lib/pgsqlthere's not pgsql directory in /usr/lib.Where exactly do i have to do this linking thing and to what I have to link it.Thanks~Harpreet
On 8/13/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:> Problem was with pg_hba.conf file>> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for> localhost only and not for other ip addresses.
>> I had to change the configuration for IPV4 local connections>> It should have been something like>> hostall all 192.168.0.0/24   trust
> instead of> host   all all   127.0.0.1/32   trust (which is meant for localhost> only)>> I think i got it...right?You might want to add 
192.168.0.0/24 on another line rather thanreplacing 127.0.0.1.  And allowing "trust" connections is badsecurity practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.Consider using a stronger authentication method and modifying theclient code accordingly.
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html--Michael Fuhr

Re: [GENERAL] Connection string

Yeah. I just realized when I could not start pgadminIII in the DB server computer.Added that line for localhost too :)Yeah, i need stronger authentication for my application. Thanks for the valuable advice.
Thanks again~Harpreet.On 8/13/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:> Problem was with pg_hba.conf file>> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for> localhost only and not for other ip addresses.
>> I had to change the configuration for IPV4 local connections>> It should have been something like>> hostall all 192.168.0.0/24   trust
> instead of> host   all all   127.0.0.1/32   trust (which is meant for localhost> only)>> I think i got it...right?You might want to add 
192.168.0.0/24 on another line rather thanreplacing 127.0.0.1.  And allowing "trust" connections is badsecurity practice because anybody on one of the allowed IP addresses
could connect as any user without being challenged for a password.Consider using a stronger authentication method and modifying theclient code accordingly.
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html--Michael Fuhr

Re: [GENERAL] Connection string

Thanks alot all.You guys have been really helpful.I've crossed the first obstacle, a major one though...Phew.Let me see where do I trip later :-)~HarpreetOn 8/13/06, 
Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> Postgres is not installed in the client computer.> When i try to connect to the postgres db from the computer in which postgres> is installed, it goes through. No hassles in that.> Problem comes up when I'm trying to connect to the same database from a
> different computer in which no postgres is installed.If you try installing a seperate installation of pgadmin 3 on the client computer to test remotewith your postgresql server could help you determine where your problem is.
Also, it sounds like you might still have problems in your pg_hba.conf, or postgresql.conf files.I can't remember if you've already posted them but maybe it would hurt to post them again.Regards,
Richard Broersma Jr.

Re: [GENERAL] Connection string

Michael,Please ignore my last emailAt last I got error code 0.Problem was with pg_hba.conf fileECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for localhost only and not for other ip addresses.
I had to change the configuration for IPV4 local connectionsIt should have been something likehost    all all 192.168.0.0/24   trust  instead of host   all all   
127.0.0.1/32   trust (which is meant for localhost only)I think i got it...right?thanks alot for your help. Do you anymore inputs or warnings for me on this?I'm highly obliged MIchael..
Thanks and regards,~HarpreetOn 8/13/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Aug 13, 2006 at 01:58:54AM -0400, Harpreet Dhaliwal wrote:> -402 sqlcode means "connection attempt to the database did not succeed">> does this mean that my application is connecting to the database server but
> somehow failing in connecting to the database dbxyz due to some> authentication problems???What do the server's logs say?  Can you connect to the databasefrom the same machine using psql?  If you add the following line
to your program before the connect, what output do you get?ECPGdebug(1, stderr);--Michael Fuhr

Re: [GENERAL] Connection string

ECPGdebug(1, stderr);After including this line of code in my program, it says:raising sqlcode -402 in line 14, 'could not connect to database dbxyz in line 14'Thanks,~Harpreet.
On 8/13/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Sun, Aug 13, 2006 at 01:58:54AM -0400, Harpreet Dhaliwal wrote:> -402 sqlcode means "connection attempt to the database did not succeed">> does this mean that my application is connecting to the database server but
> somehow failing in connecting to the database dbxyz due to some> authentication problems???What do the server's logs say?  Can you connect to the databasefrom the same machine using psql?  If you add the following line
to your program before the connect, what output do you get?ECPGdebug(1, stderr);--Michael Fuhr

Re: [GENERAL] Connection string

Hi Richard,I'm attaching pg_hba.conf and postgresql.conf files as you had asked for.Don't know if I've missed anything in these two configuration files that would not let my client machine to connect to the DB computer.
Also, what exactly do u want me to install on the client computer? A full fledged installation of postgres or what?Thanks and regards,~HarpreetOn 8/13/06, 
Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> Postgres is not installed in the client computer.> When i try to connect to the postgres db from the computer in which postgres> is installed, it goes through. No hassles in that.> Problem comes up when I'm trying to connect to the same database from a
> different computer in which no postgres is installed.If you try installing a seperate installation of pgadmin 3 on the client computer to test remotewith your postgresql server could help you determine where your problem is.
Also, it sounds like you might still have problems in your pg_hba.conf, or postgresql.conf files.I can't remember if you've already posted them but maybe it would hurt to post them again.Regards,
Richard Broersma Jr.


pg_hba.conf
Description: Binary data


postgresql.conf
Description: Binary data

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Connection string

Postgres is not installed in the client computer.
When i try to connect to the postgres db from the computer in which postgres is installed, it goes through. No hassles in that.
Problem comes up when I'm trying to connect to the same database from a different computer in which no postgres is installed. 
On 8/13/06, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> What do i do next buddy?> ~harpreetWhen you created your database using PGadmin 3, was PGadmin 3 on your Postgresql Server or was it
on the client machine that you are trying to establish an ECPG connection from?I just want to be sure that you are infact able to connect to your database from your clientcomputer.Regards,Richard Broersma Jr.

Re: [GENERAL] Connection string

What do i do next buddy?~harpreetOn 8/13/06, Harpreet Dhaliwal <[EMAIL PROTECTED]
> wrote:i started the postmaster using pg_ctl in user a/c jsb.Switched to root and ran nmap -sS localhost
for port 5432 it says5432/tcp opne postgresOn 8/13/06, 
Richard Broersma Jr <[EMAIL PROTECTED]> wrote:

--- Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:> Hello,>
> I'm really not able to connect to my database server.
Doesnmap -sS localhost show that port 5432 is open and used by postgresql?

Re: [GENERAL] Connection string

i started the postmaster using pg_ctl in user a/c jsb.Switched to root and ran nmap -sS localhostfor port 5432 it says5432/tcp opne postgresOn 8/13/06, 
Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
--- Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote:> Hello,>> I'm really not able to connect to my database server.
Doesnmap -sS localhost show that port 5432 is open and used by postgresql?

Re: [GENERAL] Connection string

Also,-402 sqlcode means "connection attempt to the database did not succeed"does this mean that my application is connecting to the database server but somehow failing in connecting to the database dbxyz due to some authentication problems???
~HarpreetOn 8/13/06, Harpreet Dhaliwal <
[EMAIL PROTECTED]> wrote:
Hello,I'm really not able to connect to my database server.

Let me explain the whole thing once again so that I don't miss telling anything.

I have a user account jsb that owns directory /usr/local/pgsql/jsb
initdb is done on the same directory i.e. /usr/local/pgsql/jsb

So all the db related files are in the same directory.

Now there is a dabatase dbxyz created using pgadmin3 and user/profile jsb is the owner of 
this database, which is again set using pgadmin3 only.

Now, in the machine (where all my middle tier programs would reside), I
have a .pgc using which i am trying to connect to the Database server
whose IP address is 192.168.0.123.

Also, in postgresql.conf file, I have set listen_address='*' and port = 5432.

Now, i have a test.pgc file whose contents are as follows:
-
-
#include 
EXEC SQL INCLUDE sqlca;

int main()
{
    EXEC SQL BEGIN DECLARE SECTION;
 char abc[20];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123:5432/dbxyz USER jsb;

   printf("Error code is %d \n", SQLCODE);

}
---
---

Here User jsb is used because jsb owns database xyz and the database directory /usr/local/pgsql/jsb aswell

I always get error code -402.

I don't know where am i going wrong or what extra i need to do.
My deadline is approaching very close and I'm feeling baffled now coz i
don't think so there's anything more i can do, not something in my
knowledge.

A prompt and quick help would be greatly and deeply appreciated.

Thanks and regards,
harpreetOn 8/12/06, Michael Fuhr <

[EMAIL PROTECTED]> wrote:
On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote:> What kind of patch are you talking about?A documentation patch.  Michael Meskes, to whom I was responding,maintains ECPG.  I was asking whether he wanted me to submit a patch
to fix misleading parts of the documentation or whether he'd committhe necessary changes based on what we've already discussed in thisthread.--Michael Fuhr

Re: [GENERAL] Connection string

Hello,I'm really not able to connect to my database server.

Let me explain the whole thing once again so that I don't miss telling anything.

I have a user account jsb that owns directory /usr/local/pgsql/jsb
initdb is done on the same directory i.e. /usr/local/pgsql/jsb

So all the db related files are in the same directory.

Now there is a dabatase dbxyz created using pgadmin3 and user/profile jsb is the owner of
this database, which is again set using pgadmin3 only.

Now, in the machine (where all my middle tier programs would reside), I
have a .pgc using which i am trying to connect to the Database server
whose IP address is 192.168.0.123.

Also, in postgresql.conf file, I have set listen_address='*' and port = 5432.

Now, i have a test.pgc file whose contents are as follows:
-
-
#include
EXEC SQL INCLUDE sqlca;

int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char abc[20];
EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO tcp:postgresql://192.168.0.123:5432/dbxyz USER jsb;

printf("Error code is %d \n", SQLCODE);

}
---
---

Here User jsb is used because jsb owns database xyz and the database directory /usr/local/pgsql/jsb aswell

I always get error code -402.

I don't know where am i going wrong or what extra i need to do.
My deadline is approaching very close and I'm feeling baffled now coz i
don't think so there's anything more i can do, not something in my
knowledge.

A prompt and quick help would be greatly and deeply appreciated.

Thanks and regards,
harpreetOn 8/12/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Fri, Aug 11, 2006 at 11:40:53PM -0400, Harpreet Dhaliwal wrote:> What kind of patch are you talking about?A documentation patch. Michael Meskes, to whom I was responding,maintains ECPG. I was asking whether he wanted me to submit a patch
to fix misleading parts of the documentation or whether he'd committhe necessary changes based on what we've already discussed in thisthread.--Michael Fuhr

Re: [GENERAL] Connection Object

http://www.postgresql.org/docs/8.1/interactive/ecpg-errors.html#AEN27672
 
printf("Error code is %d",SQLCODE)
 
I hope this helps 
On 8/12/06, Sandeep Kumar Jakkaraju <[EMAIL PROTECTED]> wrote:

Hi All I want to know specifically from the exception thrown by the "connection" object when the connnection string is wrong..whether it is due to wrong database name or wrong user name or wrong password !!! 
Is it possible ??Thanx in advance
-- Sandeep Kumar Jakkaraju WeBlog:http://jakkarajus.blogspot.com

Re: [GENERAL] Connection string