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

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 wrote: On Wed

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

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 goi

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

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 "executo

Re: [GENERAL] Version 1 Calling convention

OK..i got it..spi.h was in /usr/include/pgsql/server/executorJasOn 8/30/06, Jasbinder Bali <[EMAIL PROTECTED] > wrote:Isn't there any rpm to download the SPI ?? On 8/30/06, Martijn van Oosterhout < kleptog@svana.org> wrote: On Wed, Aug 30, 2006 at 01:01:32AM -0400, Jasbinder Bali wrote: > Do i need

Re: [GENERAL] Version 1 Calling convention

Isn't there any rpm to download the SPI ??On 8/30/06, Martijn van Oosterhout wrote: On Wed, Aug 30, 2006 at 01:01:32AM -0400, Jasbinder Bali wrote:> Do i need to download spi.h from somewhere or what? I can't see it anywhereYou need the backend header files. In Debian they're i

Re: [GENERAL] Version 1 Calling convention

On Wed, Aug 30, 2006 at 01:01:32AM -0400, Jasbinder Bali wrote: > Do i need to download spi.h from somewhere or what? I can't see it anywhere You need the backend header files. In Debian they're in the postgresql-dev package. You'll need to add -I/usr/include/postgresql/server to your compile comm

Re: [GENERAL] Version 1 Calling convention

Do i need to download spi.h from somewhere or what? I can't see it anywhereOn 8/30/06, Jasbinder Bali <[EMAIL PROTECTED] > wrote:OK. I've removed the ECPG code and error has gone away.Thanks jasOn 8/30/06, Alvaro Herrera < [EMAIL PROTECTED] > wrote:Jasbinder Bali wrote: > I have a .pgc file and i h

Re: [GENERAL] Version 1 Calling convention

OK. I've removed the ECPG code and error has gone away.ThanksjasOn 8/30/06, Alvaro Herrera <[EMAIL PROTECTED] > wrote:Jasbinder Bali wrote:> I have a .pgc file and i have the following code snippet in it: >> PG_FUNCTION_INFO_V1 (test);>> Datum> test (PG_FUNCTION_ARGS)> {>  EXEC SQL BEGIN DECLARE SE

Re: [GENERAL] Version 1 Calling convention

Well I think I can because so far its been working fine.Anyways, as suggested by others too, I'm going to change it to SPI but right now the matter of concern is the error that I'm getting. Is it because I'm using ECPG in server side code or something else? On 8/30/06, Alvaro Herrera <[EMAIL PROTEC

Re: [GENERAL] Version 1 Calling convention

Jasbinder Bali wrote: > I have a .pgc file and i have the following code snippet in it: > > PG_FUNCTION_INFO_V1 (test); > > Datum > test (PG_FUNCTION_ARGS) > { > EXEC SQL BEGIN DECLARE SECTION; You can't use ecpg in server-side functions. You need to use the SPI interface. -- Alvaro Herrera

[GENERAL] Version 1 Calling convention

I have a .pgc file and i have the following code snippet in it:PG_FUNCTION_INFO_V1 (test);Datumtest (PG_FUNCTION_ARGS){  EXEC SQL BEGIN DECLARE SECTION;   int id = PG_GETARG_INT(0);    char *raw_rawemail1 = PG_GETARG_CHAR_P(1);   char *rawemail1 = (char *)  palloc (VARSIZE(r

[GENERAL] reindexdb and "could not open relation" error message

Hi! For the past two days I've been getting the following errors from my cron scripts which connect to the database: day 1, on a SELECT statement: ERROR: could not open relation 1663/35154/221872: No such file or directory day 2, on a LOCK TABLE statement: ERROR: could not open relation 1663

[GENERAL] Santa Clara Pg Training Event (resent to -general)

I'm resending this to -general. I'd originally sent it to -hackers. I've been waiting for atleast one more person to sign up for the training so OTG can confirm this class down there, they said that some of the internal hackers had requested the course out there, so I wanted to ping everyone

Re: [GENERAL] strange sum behaviour

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alvaro Herrera wrote: > Ron Johnson wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Seneca Cunningham wrote: >>> On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: >> [snip] >>> Take a look at

Re: [GENERAL] strange sum behaviour

Ron Johnson wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Seneca Cunningham wrote: > > On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: > [snip] > > Take a look at for > > some information about why you should be using numeric for

Re: [GENERAL] transaction isolation level

My guess would be that you are getting the connection with autocommit (false); Either way try conn = getConn... conn.setAutoCommit(false); conn.changeTransactionLevel On 29-Aug-06, at 9:15 AM, Tom Lane wrote: "josh hes." <[EMAIL PROTECTED]> writes: We recently updated our postgres to 8.1.

Re: [GENERAL] strange sum behaviour

Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Seneca Cunningham wrote: On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: [snip] Take a look at for some information about why you should be using numeric for your amount colum

Re: [GENERAL] counting days

On þri, 2006-08-29 at 17:32 -0400, AgentM wrote: > Perhaps you are trying to count business days? Those vary around the > world and you would certainly need a table to hold the holidays and > such. If you just want to count the number of non-weekend-days, then > get the interval as days and t

Re: [GENERAL] counting days

On Tue, Aug 29, 2006 at 07:35:27PM +0100, garry saddington wrote: > I need to count the days between two dates that are not saturdays or > sundays. I have read the manual and searched the lists but I am > struggling. I can count the days but am finding difficulty > excluding sat and sun from the

Re: [GENERAL] database files are incompatible with server, after computer restart

--- Douglas McNaught <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > > I'm stymied. ;) > > So you've tried running both pg_ctl binaries against the data > directory and both don't work? Yes. > I wonder how you ever had a database > working in that case. :) Me too! After 'i

Re: [GENERAL] counting days

If I understood you correctly, you want the number of weekdays (e.g. an integer number of days) between 2 specified dates. This seems to work (although I am not the greatest plpgsql function person - not enough practice - there may be better ways): CREATE or REPLACE FUNCTION count_weekdays (date

Re: [GENERAL] strange sum behaviour

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Seneca Cunningham wrote: > On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: [snip] > Take a look at for > some information about why you should be using numeric for your amount > column. So how does

Re: [GENERAL] counting days

On þri, 2006-08-29 at 19:35 +0100, garry saddington wrote: > I need to count the days between two dates that are not saturdays or > sundays. I have read the manual and searched the lists but I am > struggling. I can count the days but am finding difficulty excluding sat > and sun from the count. I

Re: [GENERAL] database files are incompatible with server, after computer restart

--- Douglas McNaught <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > > I don't know which to run now! I tried both > > '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' > and > > You might want an absolute path for 'pgdata' > here--have you tried > that? ~ $ /opt/local/lib/pg

Re: [GENERAL] database files are incompatible with server, after

CSN <[EMAIL PROTECTED]> writes: > I don't know which to run now! I tried both > '/opt/local/lib/pgsql8/bin/pg_ctl start -D pgdata' and You might want an absolute path for 'pgdata' here--have you tried that? > putting /opt/local/lib/pgsql8/bin before > /usr/local/bin in PATH and still get the sa

Re: [GENERAL] XPath and XML support

On Thu, 24 Aug 2006 13:56:12 -0700, Frank Cohen wrote: > Thanks Nikolay: Seeing as xml2 hasn't been ported to Windows yet > makes me wonder if this is going to be the best way to use XML in > PostgreSQL in the long-term? Is there anything else on the boards? - > Frank > There's a lot of sco

Re: [GENERAL] counting days

Perhaps you are trying to count business days? Those vary around the world and you would certainly need a table to hold the holidays and such. If you just want to count the number of non-weekend-days, then get the interval as days and then it's a simple matter of math: 2*daysInterval/7+(day

Re: [GENERAL] database files are incompatible with server, after computer restart

--- Douglas McNaught <[EMAIL PROTECTED]> wrote: > CSN <[EMAIL PROTECTED]> writes: > > > I don't think so -- I followed the instructions > here: > > > > > http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx > > > > But looking around, I see there's a pg_ctl in

[GENERAL] SQL Tuning

Hola, soy nuevo por acá, quisiera saber si me pueden ayudar para ver que modificaciones puedo hacerle a esta consulta para que funciones mas rápido ya que como esta ahora consume mucha cpu y memoria. desde ya muchas gracias. saludos query SELECT DISTINCT t.id, emp.descripcion as contratis

Re: [GENERAL] strange sum behaviour

> corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; > amount > - > 4.88 >117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > -- > -1.4210854715202e-14

Re: [GENERAL] Anonymous stored procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David Fetter wrote: > On Tue, Aug 29, 2006 at 11:02:12AM -0500, Ron Johnson wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hi, >> >> Does PostgreSQL support them? > > Not yet. Any plans? Don't see it on the TODO? - -- Ron Johnson,

Re: [GENERAL] strange sum behaviour

Andrew Baerg wrote: corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; amount - 4.88 117.1 -121.98 (3 rows) corp=# select sum(amount) from acc_trans where trans_id=19721 and chart_id=10019; sum -- -1.4210854715202e-14 (1 row)

Re: [GENERAL] strange sum behaviour

Thanks for so many prompt responses. I have researched the differences between floating point and arbitrary precision numbers in the pgsql docs and understand now what is happening. Thanks again to the many great members of the pgsql community. Andrew On 8/29/06, Andrew Baerg <[EMAIL PROTECTED]

[GENERAL] counting days

I need to count the days between two dates that are not saturdays or sundays. I have read the manual and searched the lists but I am struggling. I can count the days but am finding difficulty excluding sat and sun from the count. I need this without reference to any tables. Does anyone have any poi

Re: [GENERAL] strange sum behaviour

On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: Hi, I am getting strange results from the sum function as follows: corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; amount - 4.88 117.1 -121.98 (3 rows) corp=# select sum(amount) from acc_trans where

Re: [GENERAL] strange sum behaviour

> corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > -- > -1.4210854715202e-14 [expected to be 0] Floating-point numbers are typically inaccurate like that, and if you rely in equality comparisons, you're doing something wron

Re: [GENERAL] strange sum behaviour

As an alternative -- you could do an inline type cast SELECT SUM(amount)::numeric(10, 2) FROM acc_trans WHERE trans_id=19721 AND chart_id=10019; "Karen Hill" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > "Andrew Baerg" wrote: >> Hi, >> >> I am getting strange results

Re: [GENERAL] strange sum behaviour

"Andrew Baerg" wrote: > Hi, > > I am getting strange results from the sum function as follows: > > corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; > amount > - > 4.88 >117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans

Re: [GENERAL] Shared Objects (Dynamic loading)

Tiger, Thanks alot for sharing that info. Thats exactly how we have implemented our system after seeing the mismatch in the transactions for any rollback transactions. Regards, ~Jas  On 8/29/06, Tiger Quimpo <[EMAIL PROTECTED]> wrote: [original email got setn to Jasbinder but not to the listfixing

[GENERAL] strange sum behaviour

Hi, I am getting strange results from the sum function as follows: corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; amount - 4.88 117.1 -121.98 (3 rows) corp=# select sum(amount) from acc_trans where trans_id=19721 and chart_id=10019; sum ---

Re: [GENERAL] Anonymous stored procedures

On Tue, Aug 29, 2006 at 11:02:12AM -0500, Ron Johnson wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > > Does PostgreSQL support them? Not yet. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666

[GENERAL] Anonymous stored procedures

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Does PostgreSQL support them? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. Ho

Re: [GENERAL] Atomicity?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Naz Gassiep wrote: > I am getting an error that I think I understand, but that I didn't think > should happen. > > Below is the output from psql that I am getting to trigger this error. > If the violation of the constraint really is being caused WITHI

Re: [GENERAL] Confused on a some deadlocks occuring...

Erik Jones wrote: Ok, consider the following table definition: CREATE TABLE stats ( id SERIAL PRIMARY KEY, hits bigint default 0, clickthrus bigint default 0, referrals bigint default 0); Now, this table has a lot of rows that are constantly being updated by queries of the following form: U

[GENERAL] Confused on a some deadlocks occuring...

Ok, consider the following table definition: CREATE TABLE stats ( id SERIAL PRIMARY KEY, hits bigint default 0, clickthrus bigint default 0, referrals bigint default 0); Now, this table has a lot of rows that are constantly being updated by queries of the following form: UPDATE stats SET cli

Re: [GENERAL] Deathly slow performance on SMP red-hat system

Patrick TJ McPhee wrote: I have a Red Hat ELsmp system running on a dual opteron hp server with 16 gigs of memory. I believe the RH installation is straight out of the box. I've compiled postgres 8.1.4 from sources. The problem is that query performance is horrible, and I can't think why, althou

Re: [GENERAL] Deathly slow performance on SMP red-hat system

Patrick,Have you modified your postgresql.conf file or is this default out-of-the-box too? Are the database and database logs (pg_xlog) on shared disc space?SteveOn 8/28/06, Patrick TJ McPhee <[EMAIL PROTECTED]> wrote: I have a Red Hat ELsmp system running on a dual opteron hp server with16 gigs o

Re: [GENERAL] Deathly slow performance on SMP red-hat system

Patrick TJ McPhee wrote: I have a Red Hat ELsmp system running on a dual opteron hp server with 16 gigs of memory. I believe the RH installation is straight out of the box. I've compiled postgres 8.1.4 from sources. The problem is that query performance is horrible, and I can't think why, althou

Re: [GENERAL] speeding up big query lookup

Thanks for the tips, am Actually, your suggestion is equivalent to JOINing the table with a GROUP BY copy of itself, and EXPLAIN shows both versions using the same index and aggregates. Just a matter of style. Your previous suggestion from the book works well too, but I actually prefer the JOIN me

Re: [GENERAL] transaction isolation level

"josh hes." <[EMAIL PROTECTED]> writes: > We recently updated our postgres to 8.1.4, since then > I’ve been getting the following error: > [Aug 28, 2006 5:43:16 AM ERROR] > org.postgresql.util.PSQLException: Cannot change > transaction isolation level in the middle of a > transaction. > at > org.

Re: [GENERAL] database files are incompatible with server, after

CSN <[EMAIL PROTECTED]> writes: > I don't think so -- I followed the instructions here: > > http://www.robbyonrails.com/articles/2006/05/29/install-ruby-rails-and-postgresql-on-osx > > But looking around, I see there's a pg_ctl in > /usr/local/bin, but 'port contents postgresql8' shows > a pg_ctl

Re: [GENERAL] xpath_string and group by

On Tue, Aug 29, 2006 at 07:05:44AM -0400, Arturo Perez wrote: > > > select note, count(aDate) from > > > (select lower(xpath_string(note, '//Thing')) as note, aDate from > > > theTable) as foo > > > group by note > > > > This is about the same thing, so why not use that? > > You're right, it

Re: [GENERAL] speeding up big query lookup

macgillivary wrote: I just finished reading 'The Art of SQL' by Stephane Faroult who has a chapter (ch 6) discussing this very topic. I'd be curious to know any other references, books, folks would recommend when it comes to writing efficient SQL, as well as references on database design. -

Re: [GENERAL] xpath_string and group by

In article <[EMAIL PROTECTED]>, kleptog@svana.org (Martijn van Oosterhout) wrote: > On Sat, Aug 26, 2006 at 03:51:06PM -0400, Perez wrote: > > Hi all, > > > > Using 8.1.4 and contrib/xml2. When I do a > > > > select xpath_string(note, '//Thing') as note, > >count(aDate) from theTable > >

Re: [GENERAL] xpath_string and group by

On Sat, Aug 26, 2006 at 03:51:06PM -0400, Perez wrote: > Hi all, > > Using 8.1.4 and contrib/xml2. When I do a > > select xpath_string(note, '//Thing') as note, >count(aDate) from theTable > group by lower(xpath_string(note, '//Thing')) > order by 2 desc; > > I get an error: > GROUP BY mus

[GENERAL] Deathly slow performance on SMP red-hat system

I have a Red Hat ELsmp system running on a dual opteron hp server with 16 gigs of memory. I believe the RH installation is straight out of the box. I've compiled postgres 8.1.4 from sources. The problem is that query performance is horrible, and I can't think why, although it seems clear the probl

Re: [GENERAL] speeding up big query lookup

Just for fun, another approach since I believe pg supports it: select whateverFields from object_val as outer where (outer.object_id, outer.object_val_type_id,outer.observation_date) IN (select inner.object_id, inner.object_val_type,max(inner.observation_date) from object_val as inner where i

[GENERAL] transaction isolation level

We recently updated our postgres to 8.1.4, since then I’ve been getting the following error: [Aug 28, 2006 5:43:16 AM ERROR] org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction. at org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionI

Re: [GENERAL] speeding up big query lookup

I just finished reading 'The Art of SQL' by Stephane Faroult who has a chapter (ch 6) discussing this very topic. I strongly recommend any developer dealing with databases take a few days to read this narrative. A solution would seem to depend on whether you have many objects which change in meas

Re: [GENERAL] How do i store arbitrary questions and answers in SQL?

I agree that option 3 is the way to go. There is a little reading at: http://www.varlena.com/varlena/GeneralBits/110.php which may be of interest. Tim Allen wrote: > lifeisgood wrote: > > 3. Different answer tables each with answer types - same problem as 2 > > but even harder. > > This is the o

Re: [GENERAL] Shared Objects (Dynamic loading)

On Mon, 2006-08-28 at 01:29 -0400, Jasbinder Bali wrote: > Also, the triggers that i wrote in C are not all that elaborative. > They are pretty basic triggers. Also, I'm a rookie in perl but don't > need to do something hifi with it. Any reason why you don't do your functions and triggers in pl/p

Re: [GENERAL] mising chunk

Hello , thanks for answer. i've also serach goole to find any answer but nowhere have found that. But how to find it - can you send me some queries that you have used???It would be very glad   Thanks very much Andrzej - Original Message - From: Silvela, Jaime (Exchange)

[GENERAL] Fwd: Can't populate database using Copy

OK, disabled the SELinux , so can copy now; thanks for your time...Jim <[EMAIL PROTECTED]> wrote: Date: Sat, 26 Aug 2006 11:35:56 -0700 (PDT)From: Jim <[EMAIL PROTECTED]>Subject: Can't populate database using CopyTo: pgsql-general@postgresql.org copy tabledaily from '/home/jb/FinalData.txt' with cs

[GENERAL] xpath_string and group by

Hi all, Using 8.1.4 and contrib/xml2. When I do a select xpath_string(note, '//Thing') as note, count(aDate) from theTable group by lower(xpath_string(note, '//Thing')) order by 2 desc; I get an error: GROUP BY must contain note. But I can do that for a plain text/varchar field. Adding t

Re: [GENERAL] Shared Objects (Dynamic loading)

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

Re: [GENERAL] Postgresql mentioned on Newsforge MySQL article

Scott Marlowe wrote: Was this all the same basic task implemented by different teams then? Yep. Can we see the code? hack it? I'm sure someone here could help out. Sure. I don't care about the contest, but it would be nice to be able to put out a version that could compete with MySQL's.