[GENERAL] problem enabling postgres in php

[Parang Saraf]

hello,



I am working with Postgresql 8.1.3, Apache 2.0.58 and PHP 5.1.4. on windows 2k.



My phpinfo is working fine. But I am not able to enable postgres
support. According to phpinfo the configuration file (php.ini) path is
"C:\WINNT".  All my extensions are in the  "C:\php\ext"  dir. I
modified the php.ini file situated in the C:\WINNT dir as follows : 



extension_dir = "C:\php\ext  and also removed the ';' (semicolon)
infront of the extension=php_pgsql.dll  with all the other extension
being blocked by semicolon. Then I restarted the comp but it is not
reflecting the changes in the phpinfo. 



Under the "php configuration core" phpinfo is showing the extension_dir
as C:\php5 and include_path as ".;C:\php5\pear". But i dont have any
directory named php5. So, i created a directory named php5 in C:\ and
copied all the extension dlls into it and made a folder named pear in
it and copied the php.ini file in it. But it still didnt reflect any
changes. 



I doubt that it is not reading the php,ini file from the "C:\WINNT"
directory. To check that I tried changing the color code of 
"highlight.string" from DD to FF9900. I again restarted the comp
but still it didnt reflect this change too. So, i think that it is
using some other php.ini file. So, i searched for the php.ini file and
i found it in the following locations : 



C:\WINNT\

C:\php5\



So, i made the same changes everywhere including the "php.ini-dist" and
"php.ini-recommended" files found in C:\php dir. But still the problem
persisted. Then I removed the php.ini file from the "C:\WINNT" dir but
the phpinfo still worked even without it being present in the dir. 



I also tried reinstalling php several times but that also didnt help.
But what I found strange was that i was not able to remove the
"php5ts.dll"  from the C:\WINNT dir and "php5apache2.dll" from the
C:\php dir. Everytime it gave an error that these files are in use. I
dont have any information about these files. 



Do i have to modify the "System Path variable" ?? I also tried
modifying that too by adding C:\php and C:\php5 in it. I also added one
more system variable PHPRC with the value C:\WINNT. 



But still i am not able to enable the postgres with php. I think there
is no compatibility issues as google search gives some phpinfo links
that works with the same version of php and postgres. When i try to
connect to the database it gives the error "Call to undefined function
pg_connect()" which i think is obvious as phpinfo is not showing
postgres in it. 



It would be a great help if you could figure out the error and can help
me enabling the postgres in php. I have been trying for the past 3 days
to enable it. 



Thanks a lot for spending your valuable time.



Regards

Parang Saraf

[EMAIL PROTECTED]

Re: [GENERAL] Junk binary date?

[Michael Fuhr]

On Tue, Jun 20, 2006 at 09:12:50PM -0700, Kevin Jenkins wrote:
> I call PQexecParams with the last parameter as 1 to return binary 
> data.  I then get this data with:
> 
> fileLengthPtr = PQgetvalue(result, rowIndex, fileLengthColumnIndex);
> memcpy(&fileLength, fileLengthPtr, sizeof(fileLength));
> 
> The value being returned is of type integer.
> 
> It should have the value 7237 in binary.
> It actually has the value:
> fileLengthPtr[0]  0   char
> fileLengthPtr[1]  0   char
> fileLengthPtr[2]  28 '?'  char
> fileLengthPtr[3]  69 'E'  char
> 
> Which is not 7237, it's:
> fileLength1159462912  int
> 
> Why?

7237 decimal = 1c45 hex
28 decimal = 1c hex
69 decimal = 45 hex

The data looks correct once you recognize that it's in network byte
order (big endian).  You'll need to convert it to host byte order.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Junk binary date?

[Kevin Jenkins]

I call PQexecParams with the last parameter as 1 to return binary 
data.  I then get this data with:


fileLengthPtr = PQgetvalue(result, rowIndex, fileLengthColumnIndex);
memcpy(&fileLength, fileLengthPtr, sizeof(fileLength));

The value being returned is of type integer.

It should have the value 7237 in binary.
It actually has the value:
fileLengthPtr[0]0   char
fileLengthPtr[1]0   char
fileLengthPtr[2]28 '?'  char
fileLengthPtr[3]69 'E'  char

Which is not 7237, it's:
fileLength  1159462912  int

Why?


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

Re: [GENERAL] multiple statement 'instead of' rule

[Tom Lane]

"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> create or replace rule insert_fsv as on insert to frequency_service_view
>   do instead
>   (
> insert into frequency_operation
>   select new.table_name, new.frequency, old.code where new.set =
> true and old.set = false;
> delete from frequency_operation
>   where table_name = old.table_name and frequency_operation.code =
> old.code and
> frequency_operation.frequency = new.frequency and new.set = false;
> update operation
>   set code = new.code where code = old.code and old.code != new.code;
>   );

What is frequency_service_view?  Is it by any chance dependent on
frequency_operation?  If so, your changes to frequency_operation will
affect the behavior of OLD references.

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

[GENERAL] multiple statement 'instead of' rule

[Merlin Moncure]

I have a repeatable issue where on an update rule (attached to a view)
not all of the statements are firing under certain conditions.  There
is no error but the rule does not seem to be completely going through
the statements. I'm not sure if this is correct behavior reading the
documentation but it smells funny.  I confirmed the statements are not
updating by
inserting sql log statements  in between the queries in the rule.
Also, by converting the rule to a rule + sql function it works 100%
correct every time.  this is on postgresql 8.1.4 on windows xp.

create or replace rule insert_fsv as on insert to frequency_service_view
 do instead
 (
   insert into frequency_operation
 select new.table_name, new.frequency, old.code where new.set =
true and old.set = false;
   delete from frequency_operation
 where table_name = old.table_name and frequency_operation.code =
old.code and
   frequency_operation.frequency = new.frequency and new.set = false;
   update operation
 set code = new.code where code = old.code and old.code != new.code;
 );

merlin

---(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] Lock contention during inserts

[Tom Lane]

"Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> During mass inserts, we have some locking contention in tables referenced by
> foreign keys. It´s a 8.0.3 box
> and I know that 8.1 solved this but I would like to know if there is an easy
> and safe way to only apply this patch to 8.0.3?

If it were a simple fix it would have been fixed many versions ago.
Update to 8.1.

regards, tom lane

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

[GENERAL] Lock contention during inserts

[Carlos H. Reimer]

Hello,

During mass inserts, we have some locking contention in tables referenced by
foreign keys. It´s a 8.0.3 box
and I know that 8.1 solved this but I would like to know if there is an easy
and safe way to only apply this patch to 8.0.3?

Reimer


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

Re: [GENERAL] simple query terminated by signal 11

[Qingqing Zhou]

""Thomas Chille"" <[EMAIL PROTECTED]> wrote
>
> I don't know how to post it, because the size is 1,5 MB?! I try to
> attch it as gzip.
>

No ...  I mean the "bt" result of the core dump.

$gdb  -c 
bt

> .
> Program terminated with signal 11, Segmentation fault.
> #0  0x080753c2 in DataFill ()
> (gdb) where
> #0  0x080753c2 in DataFill ()
> #1  0xb74253d4 in ?? ()
> #2  0x001d in ?? ()
> #3  0x08356fa8 in ?? ()
> #4  0x08379420 in ?? ()
> #5  0x in ?? ()
> (gdb)
>

Since it is repeatable in your machine, you can compile a new postgres
version with "--enable-cassert" (enable assertions in code) and
"--enable-debug"  (enable gcc debug support) configuration. Then run it on
your data and "bt" the core dump.

Regards,
Qingqing




---(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] Adding foreign key constraints without integrity check?

[Florian G. Pflug]

Wes wrote:

On 6/20/06 5:07 AM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:


My suggestion was to create the fk _before_ loading the data, and disable it
similarly to what "--disable-triggers" doest. It turned out, however, that a
FK always depends on a unique index (be it a primary key, or not), which
prevents
my plan from working :-(


That was a great idea - too bad it didn't pan out.

I don't suppose there's any (reasonable) way to directly insert into the
system tables to create the constraint?  I could knock almost 2 days off of
the almost 4 days to reload if I could solve this.


I believe that it's possible, but it's hard to get right. A foreign key
consists of at least
.) The actual definition in pg_constraints or so
.) A trigger
.) Various reconds in pg_depend

To be on the safe side, you'd need to look at the sourcecode, and miminc
what is done there.

Maybe you could ask at some postgresql support companies how much effort it 
would
be to add a "without check" flag to "alter table add constraint foreign key", 
and
how much they'd charge for it...

greetings, Florian Pflug


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

Re: [GENERAL] Help requd in writing functions in C and using in Postgres

[Michael Fuhr]

On Tue, Jun 20, 2006 at 02:03:06PM -0400, Jasbinder Bali wrote:
> Does that mean I can't make a function in C that will have all the database
> transactions and then would just be included in the Stored Procedure in
> postgres (though i know there is no concept of stored procs in postgres, its
> all functions that you have in it).

Functions are executed in the context of an outer transaction, so
if you need multiple transactions then you'll have to initiate them
from the client side.  However, as I mentioned, a server-side
function could connect to the database and act as a client, so that
would be one way to execute multiple transactions via a server-side
function.  A possible drawback of doing so is that if the function
is called from a transaction that's later rolled back, the statements
that the function executed over its client connection would have
already been committed and thus wouldn't be rolled back.

> Also when i say include , my function would not find it coz its
> in some different path altogether. Where exactly is this postgres.h and
> other postgres header files??

The "C-Language Functions" documentation I previously sent explains
how to find include files, but if you use the PGXS build infrastructure
then you don't need to worry about it.

http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#AEN32313
http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#DFUNC
http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html#XFUNC-C-PGXS

-- 
Michael Fuhr

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

Re: [GENERAL] merge result sets

[simon]

On Die, 2006-06-20 at 15:34 -0500, Bruno Wolff III wrote:
> On Tue, Jun 20, 2006 at 12:06:24 +0200,
>   simon <[EMAIL PROTECTED]> wrote:
> > hi all
> > 
> > i'm using postgres 7.3
> > 
> > my problem is i want to build a helper table:
> > 
> > UPDATE studienmodul_summary 
> >SET kategorie = (SELECT kategorie_bezeichnung
> > 
> > if the SELECT gives back just one result row, everthing is easy. my
> > problem is, there is sometimes more than one result row. 
> > 
> > is there a way to concatenate all result rows and insert them in one
> > field?
> 
> Yes. You can have the subselect call a custom aggregate function that does
> this for you. Sample code for doing this has been posted on the list
> multiple times and you should be able to find it in the archives.

thanks for this hint i didn't konw about the custom aggregate function.
i found comma_aggregate(text,text) amd similar examples.
unfortunatly i didn't found something like comma_aggregate(SELECT...). 

is it possible to write an aggregate function that takes the result rows
of any number and makes a long string out of it?

it would be great if someone would have done something before and is
willing to share.
but hints where to find docu and/or howtos about writting
customaggregate functions are also very welcom.

simon
> 
-- 
Simon Litwan   [EMAIL PROTECTED]
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com  http://lenya.apache.org


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

   http://archives.postgresql.org

Re: [GENERAL] Dynamic loading of C functions

[Erin Sheldon]

Was the object compiled on the same architecture as postgres?
Sometimes when one is compiled on a 64-bit, the other on 32-bit,
or two wildly different versions of gcc, I have seen this cryptic "No such
file or directory".

Erin

On 6/20/06, Jasbinder Bali <[EMAIL PROTECTED]> wrote:


chmod 666 filename is something i've done to give permissions to all..
still doesn't work.



On 6/20/06, Bill Moran <[EMAIL PROTECTED]> wrote:
> In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:
>
> > I've written a function in C, compiled it and trying to use the same
> > function in one of my postgres functions like this:
> >
> > CREATE FUNCTION add_one(integer) RETURNS integer
> >  AS '/usr/include/pgsql/server/test_func, 'add_one'
> >  LANGUAGE C STRICT
> >
> > test_func is the name of my object file and add_one is the name of
> > the function i want to call from test_func.c  C file.
> >
> > I get the follwing error
> > ERROR:  could not access file
"/usr/include/pgsql/server/test_func": No such
> > file or directory
> >
> > /usr/include/pgsql/server/ is exactly the path where test_func object
file
> > resides.
> > Don't know why isn't postgres able to find it there.
> >
> > Any kind of help would be appreciated.
>
> Check the permissions.  Can the Postgres user read the file?
>
> I don't remember if it has to be marked executable or not, but that's
> something to check.
>
> --
> Bill Moran
> Collaborative 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

Re: [GENERAL] Dynamic loading of C functions

[Jasbinder Bali]

yes, i've named it as .so file.
On 6/20/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Jasbinder Bali" <[EMAIL PROTECTED]> writes:>>> /usr/include/pgsql/server/ is exactly the path where test_func object
>>> file resides.Hmmm  when you say "object file", do you mean it's really named"test_func.o"?If so, that's both the wrong name and the wrong type of file.  Postgres
is looking for a shared library, eg "test_func.so" (or on some platforms".sl" or ".dylib").  There's some advice in our manual about thecompiler switches to use to create a shared library, or see your
compiler documentation.Given AS '/usr/include/pgsql/server/test_func', Postgres will look forboth "test_func" and "test_func.so" (not sure which order, try the LOADreference page for details).  It won't look for "test_func.o" though.
BTW, most people would say that /usr/include is exactly where NOT toput an executable file ... conventionally this kind of file goes under/usr/lib.  That's not what's causing your problem, it's just a question
of keeping your filesystem tidy enough to be able to find things again.   regards, tom lane

Re: [GENERAL] Dynamic loading of C functions

[Tom Lane]

"Jasbinder Bali" <[EMAIL PROTECTED]> writes:
>>> /usr/include/pgsql/server/ is exactly the path where test_func object
>>> file resides.

Hmmm  when you say "object file", do you mean it's really named
"test_func.o"?

If so, that's both the wrong name and the wrong type of file.  Postgres
is looking for a shared library, eg "test_func.so" (or on some platforms
".sl" or ".dylib").  There's some advice in our manual about the
compiler switches to use to create a shared library, or see your
compiler documentation.

Given AS '/usr/include/pgsql/server/test_func', Postgres will look for
both "test_func" and "test_func.so" (not sure which order, try the LOAD
reference page for details).  It won't look for "test_func.o" though.

BTW, most people would say that /usr/include is exactly where NOT to
put an executable file ... conventionally this kind of file goes under
/usr/lib.  That's not what's causing your problem, it's just a question
of keeping your filesystem tidy enough to be able to find things again.

regards, tom lane

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

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

Re: [GENERAL] Dynamic loading of C functions

[elein]

Do you need to specify test_func.so not ...test_func
or has that changed?

--elein

On Tue, Jun 20, 2006 at 05:13:32PM -0400, Jasbinder Bali wrote:
> chmod 666 filename is something i've done to give permissions to all..
> still doesn't work.
> 
>  
> On 6/20/06, Bill Moran <[EMAIL PROTECTED]> wrote:
> 
> In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:
> 
> > I've written a function in C, compiled it and trying to use the same
> > function in one of my postgres functions like this:
> >
> > CREATE FUNCTION add_one(integer) RETURNS integer
> >  AS '/usr/include/pgsql/server/test_func, 'add_one'
> >  LANGUAGE C STRICT
> >
> > test_func is the name of my object file and add_one is the name of
> > the function i want to call from test_func.c  C file.
> >
> > I get the follwing error
> > ERROR:  could not access file "/usr/include/pgsql/server/test_func": No
> such
> > file or directory
> >
> > /usr/include/pgsql/server/ is exactly the path where test_func object
> file
> > resides.
> > Don't know why isn't postgres able to find it there.
> >
> > Any kind of help would be appreciated.
> 
> Check the permissions.  Can the Postgres user read the file?
> 
> I don't remember if it has to be marked executable or not, but that's
> something to check.
> 
> --
> Bill Moran
> Collaborative Fusion Inc.
> 
> 

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

Re: [GENERAL] Dynamic loading of C functions

[Jasbinder Bali]

chmod 666 filename is something i've done to give permissions to all..
still doesn't work. 
On 6/20/06, Bill Moran <[EMAIL PROTECTED]> wrote:
In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:
> I've written a function in C, compiled it and trying to use the same> function in one of my postgres functions like this:>> CREATE FUNCTION add_one(integer) RETURNS integer>  AS '/usr/include/pgsql/server/test_func, 'add_one'
>  LANGUAGE C STRICT>> test_func is the name of my object file and add_one is the name of> the function i want to call from test_func.c  C file.>> I get the follwing error
> ERROR:  could not access file "/usr/include/pgsql/server/test_func": No such> file or directory>> /usr/include/pgsql/server/ is exactly the path where test_func object file> resides.
> Don't know why isn't postgres able to find it there.>> Any kind of help would be appreciated.Check the permissions.  Can the Postgres user read the file?I don't remember if it has to be marked executable or not, but that's
something to check.--Bill MoranCollaborative Fusion Inc.

Re: [GENERAL] Dynamic loading of C functions

[Bill Moran]

In response to Tom Lane <[EMAIL PROTECTED]>:

> Bill Moran <[EMAIL PROTECTED]> writes:
> > In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:
> >> I get the follwing error
> >> ERROR:  could not access file "/usr/include/pgsql/server/test_func": No 
> >> such
> >> file or directory
> 
> > Check the permissions.  Can the Postgres user read the file?
> 
> The error is pretty clearly "file not found", not "no permissions".

Hmmm ... I was getting PostgreSQL confused with other software that
provides less precise errors.

My apologies.

-- 
Bill Moran
Collaborative Fusion Inc.

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

   http://archives.postgresql.org

Re: [GENERAL] Dynamic loading of C functions

[Tom Lane]

Bill Moran <[EMAIL PROTECTED]> writes:
> In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:
>> I get the follwing error
>> ERROR:  could not access file "/usr/include/pgsql/server/test_func": No such
>> file or directory

> Check the permissions.  Can the Postgres user read the file?

The error is pretty clearly "file not found", not "no permissions".

One possibility is that the complaint is not about this file itself
but about some other shared library it depends on.  Try "ldd" or
local equivalent on the file to see if it shows any unresolved
references.

Also, you might try looking in the postmaster log to see if any
additional info appears there --- anything the dynamic linker spit out
to stderr is not going to appear on your terminal.

regards, tom lane

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

Re: [GENERAL] Dynamic loading of C functions

[Bill Moran]

In response to "Jasbinder Bali" <[EMAIL PROTECTED]>:

> I've written a function in C, compiled it and trying to use the same
> function in one of my postgres functions like this:
> 
> CREATE FUNCTION add_one(integer) RETURNS integer
>  AS '/usr/include/pgsql/server/test_func, 'add_one'
>  LANGUAGE C STRICT
> 
> test_func is the name of my object file and add_one is the name of
> the function i want to call from test_func.c  C file.
> 
> I get the follwing error
> ERROR:  could not access file "/usr/include/pgsql/server/test_func": No such
> file or directory
> 
> /usr/include/pgsql/server/ is exactly the path where test_func object file
> resides.
> Don't know why isn't postgres able to find it there.
> 
> Any kind of help would be appreciated.

Check the permissions.  Can the Postgres user read the file?

I don't remember if it has to be marked executable or not, but that's
something to check.

-- 
Bill Moran
Collaborative Fusion Inc.

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

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

Re: [GENERAL] help with error message from perl Pg

[Bruno Wolff III]

On Tue, Jun 20, 2006 at 10:43:32 -0400,
  "A.M." <[EMAIL PROTECTED]> wrote:
> 
> You are using a completely outdated interface to postgres. Looking on
> CPAN, Pg.pm was last updated 04 Apr 2000. Which version of postgresql are
> you using? It is likely that the old interface blows up when connecting to
> a postgresql from >2000.

I still use Pg for some stuff and it works with 8.1.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Start up question about triggers

[Chander Ganesan]

  I
know that this question may be really simple, but I have decided to ask
here due to fact that I don't know how to search for this on google or
on the docs.
   
  I
created a trigger fuction which updates a specific row in some table A.
Is it possible to retain the query that was used to trigger the
function. For example
   
  Table
A
  query_row_id
  query_row
   
   
   
  TABLE
B
  id
  name
   
   
   
   
   
  If
I create a trigger on table B that says that after the insert command
to write the query into  table A. So if I do 
   
  insert
into B values(1,"Blah")
   
  this
will trigger my trigger. Is there any way to get the "insert into B
values(1,"Blah")? At the moment I can see only the type of query that
it is (INSERT UPDATE DELETE)
   
  best
regards,
  Fotis
   

Are you looking for the exact query that was entered?  If so, I'm not
sure you can get the *exact* query (aside from turning on some
logging and looking at the logs ... but that wouldn't go in a table ;-)
).  However, you can get all of the values that were used - and
essentially get all of the information that was inserted/updated. 
Basically, you can recreate a query that will have the exact same
effect - though it may not be the exact same query - assuming you have
primary keys on all of your tables.

Look at the 'NEW' and 'OLD' records within your trigger .  In the case
of an INSERT, the
'NEW' record will contain all of the columns that are being inserted
(so you could simply store all of the values in the NEW record to get
the inserted information and store it).  In the case of UPDATE, the NEW
record will contain all of the new row data, and the OLD record will
contain the old row data.  In the case of DELETE, the OLD record will
contain the information pertaining to the record being deleted.

http://www.postgresql.org/docs/8.1/static/triggers.html

You might also take a look at Slony-I, since it does something similar
with INSERT UPDATE and
DELETE triggers - it stores the information that has changed so that
the slon process can replicate it to other servers.
-- 
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Re: [GENERAL] merge result sets

[Bruno Wolff III]

On Tue, Jun 20, 2006 at 12:06:24 +0200,
  simon <[EMAIL PROTECTED]> wrote:
> hi all
> 
> i'm using postgres 7.3
> 
> my problem is i want to build a helper table:
> 
> UPDATE studienmodul_summary 
>SET kategorie = (SELECT kategorie_bezeichnung
> 
> if the SELECT gives back just one result row, everthing is easy. my
> problem is, there is sometimes more than one result row. 
> 
> is there a way to concatenate all result rows and insert them in one
> field?

Yes. You can have the subselect call a custom aggregate function that does
this for you. Sample code for doing this has been posted on the list
multiple times and you should be able to find it in the archives.

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

Re: [GENERAL] Adding foreign key constraints without integrity check?

[Bruno Wolff III]

On Tue, Jun 20, 2006 at 00:49:21 -0400,
  louis gonzales <[EMAIL PROTECTED]> wrote:
> Florian,
> I understand where you're coming from.  Indexes are always unique and 
> all RDBMS systems use them to 'uniquely' identify a row from the the 
> perspective of internal software management.  Index != PrimaryKey, so 
> every table created, despite any Primary/Foreign key contraints put on 
> them, always have a 1-1 Index per row entry.  At least that's the way I 
> understand it, can someone else affirm this statement or redirect a 
> misguided 'me ;)'?

Note that indexes are not always unique. They can sometimes still be useful for
speeding up performance even when there are duplicates. Postgres also has
partial indexes which cover only some of the rows in a table, based on
a where condition.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Exporting data from view

[Martijn van Oosterhout]

On Tue, Jun 20, 2006 at 12:44:24PM -0700, Aaron Koning wrote:
> copyable, importable... into Excel or another postgres db?

Ofcourse. It doesn't have the table definition ofcourse, just the data.
The CSV format was added specifically for importing into Excel and
similar programs...

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Dynamic loading of C functions

[Jasbinder Bali]

I've written a function in C, compiled it and trying to use the same function in one of my postgres functions like this:CREATE FUNCTION add_one(integer) RETURNS integer AS '/usr/include/pgsql/server/test_func, 'add_one'
 LANGUAGE C STRICTtest_func is the name of my object file and add_one is the name of the function i want to call from test_func.c  C file.I get the follwing error ERROR:  could not access file "/usr/include/pgsql/server/test_func": No such file or directory
/usr/include/pgsql/server/ is exactly the path where test_func object file resides. Don't know why isn't postgres able to find it there.Any kind of help would be appreciated.~Jas

Re: [GENERAL] Exporting data from view

[Aaron Koning]

copyable, importable... into Excel or another postgres db?On 6/20/06, Martijn van Oosterhout  wrote:On Tue, Jun 20, 2006 at 10:29:21AM -0700, Aaron Koning wrote:> CREATE TABLE sometable AS SELECT * FROM someview;
> pg_dump -t sometable dbname> DROP TABLE sometable>> Que? Si!Eh? If you're going to create the table anyway, I'd use psql:psql -c "COPY table TO STDOUT"If you put "CSV" there you can get the output in CSV. Far cleaner than
pg_dump.--Martijn van Oosterhout      http://svana.org/kleptog/> From each according to his ability. To each according to his ability to litigate.
-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFEmE9VIB7bNG8LQkwRAhJTAJ9loGC2v/inI+28RLvbRGGAljS6/ACdEJDne/aJg1Qu6XaBNIuhiPWt+MU==rpRd-END PGP SIGNATURE-
-- +|  Aaron Koning|  Information Technologist|  Prince George, BC, Canada.+
|  http://datashare.gis.unbc.ca/fist/|  http://datashare.gis.unbc.ca/gctp-js/+

Re: [GENERAL] Exporting data from view

[Martijn van Oosterhout]

On Tue, Jun 20, 2006 at 10:29:21AM -0700, Aaron Koning wrote:
> CREATE TABLE sometable AS SELECT * FROM someview;
> pg_dump -t sometable dbname
> DROP TABLE sometable
> 
> Que? Si!

Eh? If you're going to create the table anyway, I'd use psql:

psql -c "COPY table TO STDOUT"

If you put "CSV" there you can get the output in CSV. Far cleaner than
pg_dump.
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] A slow query - Help please?

[hubert depesz lubaczewski]

On 6/19/06, Alban Hertroys <[EMAIL PROTECTED]> wrote:
I found a way that works, and is indeed quite a bit faster. It is evenuglier than what you proposed. The problem wasn't the "order by" in thesubquery, but the "order by" combined with the "union":
sorry, i always forget about  the fact that union doesn't like internal order and limits. subquery is of course perfectly fine - i'm glad you found that :)depesz
-- http://www.depesz.com/ - nowy, lepszy depesz

Re: [GENERAL] Help requd in writing functions in C and using in Postgres

[Jasbinder Bali]

Does that mean I can't make a function in C that will have all the database transactions and then would just be included in the Stored Procedure in postgres (though i know there is no concept of stored procs in postgres, its all functions that you have in it).

 
If this is not the case then i think its just a normal function that'll have some sort of business logic and would be included in a postgres function instead of writing that function in postgres itself.
 
Please correct me if I am wrong and please add some more knowledge here so that my doubts are clearified.
 
Also when i say include , my function would not find it coz its in some different path altogether. Where exactly is this postgres.h and other postgres header files??
 
Regards,
~Jas 
On 6/20/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
[Please copy the mailing list on replies so others can participatein and learn from the discussion.]
On Tue, Jun 20, 2006 at 11:39:03AM -0400, Jasbinder Bali wrote:> Well, as in normal Sql server or oracle Stored procedures, you write a> procedure in the database server and some middle tier would invoke it.
> Now here what is the concept of server side or client side function in> postres if i just have to create a function in C and then would like my> Stored procedure to invoke it. Also all the DB related activities like
> select, delete, update should be taken care of by the C function itself.It sounds like you want to write a server-side function that aclient application or another server-side function could invoke
with SELECT do_stuff() (or PERFORM do_stuff() in PL/pgSQL).  Youcan do that in C, but unless you need the kind of low-levelfunctionality that only C can provide then you'd probably be betteroff using a higher level server-side language like PL/pgSQL, PL/Perl,
PL/Python, PL/Tcl, PL/Ruby, PL/Java, PL/R, etc.Functions in PostgreSQL aren't the same as stored procedures insome other DBMSs.  For example, a function can't start or end atransaction since it's already being executed in the context of a
transaction, although in 8.0 and later a function can do partialrollbacks via exception handling.  However, a function could connectto the database via dblink, Perl DBI, etc., and then do anythingthat an ordinary client could do.

 
 
 
--Michael Fuhr

Re: [GENERAL] Exporting data from view

[Aaron Koning]

CREATE TABLE sometable AS SELECT * FROM someview;

pg_dump -t sometable dbname

DROP TABLE sometable



Que? Si!On 6/20/06, Worky Workerson <[EMAIL PROTECTED]> wrote:
On 6/20/06, Aaron Koning <[EMAIL PROTECTED]> wrote:> google pg_dumpReally?  What command do you use?  I've tried the following:pg_dump -t viewname dbname
and I get the view definition, whereas I would like the data.  Isthere an option to pg_dump that I'm missing?---(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
-- +|  Aaron Koning|  Information Technologist|  Prince George, BC, Canada.+|  
http://datashare.gis.unbc.ca/fist/|  http://datashare.gis.unbc.ca/gctp-js/+

Re: [GENERAL] Exporting data from view

[Worky Workerson]

On 6/20/06, Aaron Koning <[EMAIL PROTECTED]> wrote:

google pg_dump


Really?  What command do you use?  I've tried the following:

pg_dump -t viewname dbname

and I get the view definition, whereas I would like the data.  Is
there an option to pg_dump that I'm missing?

---(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] Database Clustering on multiple harddisk

[Alex Turner]

On 6/20/06, arie nugraha <[EMAIL PROTECTED]> wrote:
Thanks a Alex,But can tou give a bit explanation about multiple separate tablespaceThis will give you the basic syntax for the commands.
http://www.postgresql.org/docs/8.1/static/sql-createtablespace.htmlhttp://www.postgresql.org/docs/8.1/static/sql-createtable.html
A tablespace is a designated location on a parition to store database objects such as tables and indexes.  They are often used to spread a large databases over multiple logical partitions to improve performance.
and HBA,The host bus adapter is what connects your computer to your storage/discs. Often a SCSI, iSCSI, SAS. Fibre Channel or a SATA card depending on your storage type.  This card controls how your disc is configured and set up with regard to RAID levels (If you have a RAID capable card) (Redundant Array of Independant Discs, typically mirroring/striping or striping with parity RAID 1, RAID 10 or RAID 5)
http://en.wikipedia.org/wiki/RAID talks more about RAID levels and what they mean.RAID arrays are typicaly transparent to your OS, so your OS doesn't care about the RAID level, only the HBA does.
RAID allows you to configure redunancy in your disc array, so that if one disc fails, there are others with the data on it so that your server doesn't go down and you don't loose data (which is what I suspect you want).  A RAID array is managed purely by the HBA (at least in the case of hardware RAID, which is what you want because it supports Battery Backed Caches), and doesn't affect how the OS sees the disc.  And seeing that postgresql makes calls to disc through the OS, if the OS doesn't care, neither does postgresql.  The thing you have to be carefull of is drivers for your HBA in the OS.  Many HBAs have very poor drivers, especially for Linux and other UNIXes, so you can often see very bad performance because of a poor HBA driver.
or is there any resources, tutorial on how to use this features onpostgresql
Thanks a lotAlex Turner wrote:> I think you may be a little confused...>> Last time I checked, Oracle RAC doesn't actualy support clustering the> I/O, it's one Database backend with multiple instances on the
> front-end, which is only clustering the CPU bound part (it's also VERY> expensive).>> If by clustering you mean multiple seperate tablespaces, then> Postgresql does support them.>
> If you mean RAID, then that is implemented by the HBA (host bus> adapter), and is transparent to Postgresql.>> If you are looking for replication, many people find Slony a good add-on.>
> Alex>> On 6/20/06, *Arie Nugraha* <[EMAIL PROTECTED]> [EMAIL PROTECTED]>> wrote:>> Hi list,
>> I am new postgresql user and i had read the documentation but> i dont if PostgreSQL support database clustering on multiple harddisk> (multiple I/O, just like DB2 and Oracle does)
> or not.>> If postgreSQL support the database clustering is there any> documentation or tutorial that explain how to do it?>> Thanks all.>>
---(end of broadcast)---TIP 3: Have you checked our extensive FAQ?   http://www.postgresql.org/docs/faq

Re: [GENERAL] [INTERFACES] help with error message from perl Pg

[A.M.]

On Tue, June 20, 2006 10:44 am, Tom Lane wrote:
> 78 would be ASCII 'N', but that's not really significant AFAICS.  The
> problem here is that the frontend and backend have lost sync: the server is
> expecting to find a message beginning at a place in the frontend data
> stream that evidently isn't the start of a message.  In short, the
> frontend has sent corrupted data of some sort.
>
> Martijn's theory of inadequately locked threaded access is certainly one
> likely way this can happen, but it's not the only one.  It might be useful
> for you to capture the data stream (with something like tcpdump) and try
> to get more information about the nature of the corruption. Frequently, if
> you can identify "ah-hah, THIS data is being inserted into the middle of
> THAT" or whatever, the cause becomes obvious.
>
>
> Also, before you spend too much time on this, make sure your DBI and
> DBD::Pg modules are up-to-date.  If it's a bug in that level, it'd be
> foolish to waste much of your own time chasing it.

If you look at his example code, he's not even using DBI- he's using Pg.pm
which is for all practical purposes deprecated and supplanted by DBD::Pg.



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

Re: [GENERAL] Exporting data from view

[Aaron Koning]

google pg_dumpOn 6/20/06, Worky Workerson <[EMAIL PROTECTED]> wrote:
I read recently about the efforts underway to COPY from a view,however I was wondering what the current best-practices are for beingable to copy out of a view and import that data into an actual tableelsewhere.  I am currently doing psql -c "SELECT ..." and the using a
bit of perl to transform that into something copyable (i.e. CSV), butis there a way to directly export the data in an easily importableform?Thanks!---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster-- +|  Aaron Koning|  Information Technologist|  Prince George, BC, Canada.
+|  http://datashare.gis.unbc.ca/fist/|  http://datashare.gis.unbc.ca/gctp-js/
+

Re: [GENERAL] Help requd in writing functions in C and using in Postgres

[Michael Fuhr]

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Tue, Jun 20, 2006 at 11:39:03AM -0400, Jasbinder Bali wrote:
> Well, as in normal Sql server or oracle Stored procedures, you write a
> procedure in the database server and some middle tier would invoke it.
> Now here what is the concept of server side or client side function in
> postres if i just have to create a function in C and then would like my
> Stored procedure to invoke it. Also all the DB related activities like
> select, delete, update should be taken care of by the C function itself.

It sounds like you want to write a server-side function that a
client application or another server-side function could invoke
with SELECT do_stuff() (or PERFORM do_stuff() in PL/pgSQL).  You
can do that in C, but unless you need the kind of low-level
functionality that only C can provide then you'd probably be better
off using a higher level server-side language like PL/pgSQL, PL/Perl,
PL/Python, PL/Tcl, PL/Ruby, PL/Java, PL/R, etc.

Functions in PostgreSQL aren't the same as stored procedures in
some other DBMSs.  For example, a function can't start or end a
transaction since it's already being executed in the context of a
transaction, although in 8.0 and later a function can do partial
rollbacks via exception handling.  However, a function could connect
to the database via dblink, Perl DBI, etc., and then do anything
that an ordinary client could do.

-- 
Michael Fuhr

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

[GENERAL] Exporting data from view

[Worky Workerson]

I read recently about the efforts underway to COPY from a view,
however I was wondering what the current best-practices are for being
able to copy out of a view and import that data into an actual table
elsewhere.  I am currently doing psql -c "SELECT ..." and the using a
bit of perl to transform that into something copyable (i.e. CSV), but
is there a way to directly export the data in an easily importable
form?

Thanks!

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

Re: [GENERAL] join on next row

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Sim Zacks <[EMAIL PROTECTED]> writes:

> I want my query resultset to be
> Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
> Where Event(2) is the first event of the employee that took place
> after the other event.

> Example
> EventIDEmployeeEventDateEventTimeEventType
> 1John6/15/20067:00A
> 2Frank6/15/20067:15B
> 3Frank6/15/20067:17C
> 4John6/15/20067:20C
> 5Frank6/15/20067:25D
> 6John6/16/20067:00A
> 7John6/16/20068:30R

> Expected Results
> John, 6/15/2006, 7:00, A, 7:20, C
> Frank, 6/15/2006, 7:15, B, 7:17, C
> Frank, 6/15/2006, 7:17, C, 7:25, D
> John, 6/16/2006, 7:00, A, 8:30, R

> To get this result set it would have to be an inner join on employee
> and date where the second event time is greater then the first. But I
> don't want the all of the records with a greater time, just the first
> event after.

You can filter the others out by an OUTER JOIN:

  SELECT e1.Employee, e1.EventDate,
 e1.EventTime, e1.EventType,
 e2.EventTime, e2.EventType
  FROM events e1
  JOIN events e2 ON e2.Employee = e1.Employee
AND e2.EventDate = e1.EventDate
AND e2.EventTime > e1.EventTime
  LEFT JOIN events e3 ON e3.Employee = e1.Employee
 AND e3.EventDate = e1.EventDate
 AND e3.EventTime > e1.EventTime
 AND e3.EventTime < e2.EventTime
  WHERE e3.EventID IS NULL
  ORDER BY e1.EventDate, e1.EventTime


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] question about performance of libpq

[Lee Riquelmei]

I am so sorry. I sent a mail draft.On 6/20/06, Lee Riquelmei <[EMAIL PROTECTED]> wrote:
hi,all. A strange question is as follows:I have two PCs:machine A: FreeBSD 
5.4 with PostgreSQL 8.1.2machine B: Windows XP with PostgreSQL 8.1.2A and B are with same hardware configuration and in a 100Mbit LAN.
a large table "lineitem" about 600572 rows exists in both A and B.On machine B, i run psql and issue a sql "select * from lineitem" to A:  the execution time is about 10s.On machine A, i run psql and issue 

Re: [GENERAL] Help requd in writing functions in C and using in Postgres

[Michael Fuhr]

On Tue, Jun 20, 2006 at 11:23:33AM -0400, Jasbinder Bali wrote:
> Can you help me in writing functions in C and the using in Postgres.
> would like to know some pointers and what all needs to be taken into
> considerations.

Are you writing server-side or client-side code?  For server-side
see "C-Language Functions" in the documentation:

http://www.postgresql.org/docs/8.1/interactive/xfunc-c.html

For examples you could look through the PostgreSQL source code, in
particular the files under contrib and in src/backend/utils/adt.

If you're writing client-side code then see "libpq - C Library":

http://www.postgresql.org/docs/8.1/interactive/libpq.html

If that doesn't help then please provide more information about
what you'd like to do or what concerns you have.

-- 
Michael Fuhr

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

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

Re: [GENERAL] join on next row

[Martijn van Oosterhout]

On Tue, Jun 20, 2006 at 05:13:50PM +0200, Sim Zacks wrote:
> Thank you for responding.
> I was thinking along those lines as well, though that would be an 
> absolute performance killer.

I shouldn't be too bad, if you have the appropriate indexes defined.

However, it seems to me this is the kind of problem that is solved
trivially in a function. You simply store the previous row and when you
get the next one you output both.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] question about performance of libpq

[Lee Riquelmei]

hi,all. A strange question is as follows:I have two PCs:machine A: FreeBSD 5.4 with PostgreSQL 8.1.2machine B: Windows XP with PostgreSQL 8.1.2A and B are with same hardware configuration and in a 100Mbit LAN.
a large table "lineitem" about 600572 rows exists in both A and B.On machine B, i run psql and issue a sql "select * from lineitem" to A:  the execution time is about 10s.On machine A, i run psql and issue 

[GENERAL] Help requd in writing functions in C and using in Postgres

[Jasbinder Bali]

Hi,
Can you help me in writing functions in C and the using in Postgres.
would like to know some pointers and what all needs to be taken into considerations.
I'm kind of confused how to do it
~Jas

Re: [GENERAL] join on next row

[Gurjeet Singh]

   I agree about the performance; but it won't be that bad if PG can
unnest these subqueries and convert them into join views!!! In that
case, these views would return just one row (LIMIT 1), and that is the
best a developer can do to help the optimizer make the decision. If
the optimizer knows that a relation in the join is going to return
just one row, it would try to evaluate that relation first, yeilding
better performance in the subsequent join operations.

   But I dont think we have a choice; unless, of course, if someday
PG starts supporting Oracle-like 'lateral views', where we can write
have predicates in the where clause of a view which refer the columns
of another relation in the join which this view is a part of!!!

   Do let us all know if you find a better solution.

Regards,
Gurjeet.

On 6/20/06, Sim Zacks <[EMAIL PROTECTED]> wrote:

Thank you for responding.
I was thinking along those lines as well, though that would be an
absolute performance killer.

Gurjeet Singh wrote:
> It would have been quite easy if done in Oracle's 'lateral view'
> feature. But I think it is achievable in standard SQL too; using
> subqueries in the select-clause.
>
> Try something like this:
>
> select
> Employee, EventDate,
> EventTime as e1_time,
> EventType as e1_type,
> (select
> EventTime
> from
> Events
> whereEmployee = O.Employee
> andEventDate = O.EventDate
> andEventTime > O.EventTime
> limit1
> )as e_time_1,
> (select
> EventType
> from
> Events
> whereEmployee = O.Employee
> andEventDate = O.EventDate
> andEventTime > O.EventTime
> limit1
> )
> from
> Events
>
> Hope it helps...
>
> Regards,
> Gurjeet.
>
> On 6/20/06, Sim Zacks <[EMAIL PROTECTED]> wrote:
>> I am having brain freeze right now and was hoping someone could help me
>> with a (fairly) simple query.
>>
>> I need to join on the next row in a similar table with specific criteria.
>>
>> I have a table with events per employee.
>> I need to have a query that gives per employee each event and the event
>> after it if it happened on the same day.
>>
>> The Events table structure is:
>>
>> EventID
>> Employee
>> EventDate
>> EventTime
>> EventType
>>
>> I want my query resultset to be
>> Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
>> Where Event(2) is the first event of the employee that took place after
>> the other event.
>>
>> Example
>> EventIDEmployeeEventDateEventTimeEventType
>> 1John6/15/20067:00A
>> 2Frank6/15/20067:15B
>> 3Frank6/15/20067:17C
>> 4John6/15/20067:20C
>> 5Frank6/15/20067:25D
>> 6John6/16/20067:00A
>> 7John6/16/20068:30R
>>
>> Expected Results
>> John, 6/15/2006, 7:00, A, 7:20, C
>> Frank, 6/15/2006, 7:15, B, 7:17, C
>> Frank, 6/15/2006, 7:17, C, 7:25, D
>> John, 6/16/2006, 7:00, A, 8:30, R
>>
>> To get this result set it would have to be an inner join on employee and
>> date where the second event time is greater then the first. But I don't
>> want the all of the records with a greater time, just the first event
>> after.
>>
>> Thank You
>> Sim
>>
>> ---(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
>>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>   http://www.postgresql.org/docs/faq
>

---(end of broadcast)---
TIP 6: explain analyze is your friend



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

[GENERAL] question about performance of libpq

[Lee Riquelmei]

hi,all. A strange question is as follows:I have two PCs:machine A: FreeBSD 5.4machine B: Windows XP.Both of them 

Re: [GENERAL] Interface Guidance and Opinions Needed

[John DeSoi]

Chris,

On Jun 20, 2006, at 9:54 AM, Chris Golden wrote:

This is my first time posting to this forum and I am very new to  
PostgreSQL.  I am very excited about using it.  I have set up a  
database and just need a point in the right direction on  
interfacing.   I have an orders and messages database.  One of the  
providers I would like to interface with has sent me an XML spec to  
make HTTPS posts to.  I am just wondering what would be the easiest  
thing to use to implement the two.  I would like to scan my  
database for new orders then do an HTTPS post to the provider.  I  
would also like to periodically check for new orders/messages from  
the provider via an HTTPS post and bring them into my database (a  
constant exchange of information).  I am wondering if something  
like PHP would be a universal solution, or something similar?


PHP is a good choice. It has a nice interface to PostgreSQL and there  
is plenty of support for HTTP client applications and XML. Probably  
the best bet for HTTPS post is to use the PHP interface to CURL:


http://www.php.net/manual/en/ref.curl.php



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


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

Re: [GENERAL] help with error message from perl Pg

[Tom Lane]

"A.M." <[EMAIL PROTECTED]> writes:
> You are using a completely outdated interface to postgres. Looking on
> CPAN, Pg.pm was last updated 04 Apr 2000. Which version of postgresql are
> you using? It is likely that the old interface blows up when connecting to
> a postgresql from >2000.

It shouldn't ... current servers are supposed to be backwards compatible
with old clients, at least at the protocol level.  But yes, version skew
is something to keep in mind here.

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] help with error message from perl Pg

[Martijn van Oosterhout]

Err...

On Tue, Jun 20, 2006 at 10:20:19AM -0400, Geoffrey wrote:
> Martijn van Oosterhout wrote:
> >On Tue, Jun 20, 2006 at 09:33:13AM -0400, Geoffrey wrote:
> >>We have an unusual problem with some perl code that is processing data 
> >>via DBD facility.  Basically, the code consists of various subroutines 
^^^

> my $conn = Pg::connectdb ("dbname=$db port=$port");
 ^
Are you using Pg or DBI?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Interface Guidance and Opinions Needed

[Joshua D. Drake]

  I would like to scan my database for new orders then do an HTTPS
post to the provider.  I would also like to periodically check for new 
orders/messages from the provider via an HTTPS post and bring them into 
my database (a constant exchange of information).  I am wondering if 
something like PHP would be a universal solution, or something similar?



Well honestly, you have just opened a huge thread because everyone is 
going to post an opinion on this. So let me be first :)


I would suggest Python. It has excellent XML parsing capabilities, is an 
easy language to learn and is widely used.


PHP is common to use but I find that the better you get at development 
the more frustrating PHP becomes.


Other suitable languages are Perl, Ruby and Java. I would stay away from 
Java due to complexity and overhead. Perl is a mature but potentially 
difficult language to those new to the environment. Ruby is just Perl 
with a cleaner overall design.


All have their strengths and weaknesses. You should really use whatever 
makes you comfortable.


I hope this was helpful.

Sincerely,

--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



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

Re: [GENERAL] psql for winxp?

[Magnus Hagander]

> > Is it possible to have the installer only emplace psql and the 
> > necessary support libs?
> 
> 
> I'm not sure offhand. The installer has customization 
> options, so it may be possible. But if not, it is probably 
> easy enough just to install it, copy the files that you need, 
> and then uninstall it.

You can do it, but it may not be the easiest way. But try it - just
deselect everythign except psql. You can also just grab the
binaries_no_installer.zip file and get them from there.

(Notw that psql depends on several DLL files that you'll also need to
add, such as libpq, openssl and gettext)

//Magnus

---(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] [INTERFACES] help with error message from perl Pg

[Tom Lane]

Geoffrey <[EMAIL PROTECTED]> writes:
> ... Any subroutine that does a delete 
> or update runs fine, but the subsequent routine fails with the following 
> error:

> prgcaphlg: FATAL:  invalid frontend message type 78
> server closed the connection unexpectedly
>   This probably means the server terminated abnormally
>   before or while processing the request.

> Searching google for 'invalid frontend message type 78' returns no hits, 
> which is a scary thing.

78 would be ASCII 'N', but that's not really significant AFAICS.  The
problem here is that the frontend and backend have lost sync: the server
is expecting to find a message beginning at a place in the frontend data
stream that evidently isn't the start of a message.  In short, the
frontend has sent corrupted data of some sort.

Martijn's theory of inadequately locked threaded access is certainly one
likely way this can happen, but it's not the only one.  It might be
useful for you to capture the data stream (with something like tcpdump)
and try to get more information about the nature of the corruption.
Frequently, if you can identify "ah-hah, THIS data is being inserted
into the middle of THAT" or whatever, the cause becomes obvious.

Also, before you spend too much time on this, make sure your DBI and
DBD::Pg modules are up-to-date.  If it's a bug in that level, it'd be
foolish to waste much of your own time chasing it.

regards, tom lane

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

Re: [GENERAL] help with error message from perl Pg

[A.M.]

On Tue, June 20, 2006 10:20 am, Geoffrey wrote:

>
> We considered that and have verified that we are not closing it.  But,
> the question came up, should we be passing it by reference or value?  We
> are doing the following:
>
> my $conn = Pg::connectdb ("dbname=$db port=$port"); .
> .
> my $retVal = &$prog($conn, @args);
>
> Question is, should we be doing:
>
>
> my $retVal = &$prog(\$conn, @args);
>
>
> FYI, there's no expectation of changing $conn in anyway within the sub
> routines.

You are using a completely outdated interface to postgres. Looking on
CPAN, Pg.pm was last updated 04 Apr 2000. Which version of postgresql are
you using? It is likely that the old interface blows up when connecting to
a postgresql from >2000.

-M


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

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

Re: [GENERAL] Adding foreign key constraints without integrity

[Wes]

On 6/20/06 5:07 AM, "Florian G. Pflug" <[EMAIL PROTECTED]> wrote:

> My suggestion was to create the fk _before_ loading the data, and disable it
> similarly to what "--disable-triggers" doest. It turned out, however, that a
> FK always depends on a unique index (be it a primary key, or not), which
> prevents
> my plan from working :-(

That was a great idea - too bad it didn't pan out.

I don't suppose there's any (reasonable) way to directly insert into the
system tables to create the constraint?  I could knock almost 2 days off of
the almost 4 days to reload if I could solve this.

Wes



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

Re: [GENERAL] help with error message from perl Pg

[Geoffrey]

Martijn van Oosterhout wrote:

On Tue, Jun 20, 2006 at 09:33:13AM -0400, Geoffrey wrote:
We have an unusual problem with some perl code that is processing data 
via DBD facility.  Basically, the code consists of various subroutines 
that are identified in a hash.  The primary script then references the 
subroutines through a &$prog(...) syntax.  What appears to happen is 
that all subroutines will run just fine if they do not need to do any 
deletes or updates to the database.  Any subroutine that does a delete 
or update runs fine, but the subsequent routine fails with the following 
error:


prgcaphlg: FATAL:  invalid frontend message type 78


Errors in the FE-BE protocol are usually caused by users trying to use
the same connection from multiple threads simultaneously. libpq doesn't
handle that and so ends up sending things out of order.

Are you using threads?


Not intentionally.  I'm assuming I would have to specify a threaded 
process explicitly.



Also, the database connection, how is the handle passed around? Youre
not closing it accedently somewhere?


We considered that and have verified that we are not closing it.  But, 
the question came up, should we be passing it by reference or value?  We 
are doing the following:


my $conn = Pg::connectdb ("dbname=$db port=$port");
.
.
my $retVal = &$prog($conn, @args);

Question is, should we be doing:

my $retVal = &$prog(\$conn, @args);


FYI, there's no expectation of changing $conn in anyway within the sub 
routines.


--
Until later, Geoffrey

Any society that would give up a little liberty to gain a little
security will deserve neither and lose both.  - Benjamin Franklin

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

Re: [GENERAL] join on next row

[Sim Zacks]

Thank you for responding.
I was thinking along those lines as well, though that would be an 
absolute performance killer.


Gurjeet Singh wrote:

It would have been quite easy if done in Oracle's 'lateral view'
feature. But I think it is achievable in standard SQL too; using
subqueries in the select-clause.

Try something like this:

select
Employee, EventDate,
EventTime as e1_time,
EventType as e1_type,
(select
EventTime
from
Events
whereEmployee = O.Employee
andEventDate = O.EventDate
andEventTime > O.EventTime
limit1
)as e_time_1,
(select
EventType
from
Events
whereEmployee = O.Employee
andEventDate = O.EventDate
andEventTime > O.EventTime
limit1
)
from
Events

Hope it helps...

Regards,
Gurjeet.

On 6/20/06, Sim Zacks <[EMAIL PROTECTED]> wrote:

I am having brain freeze right now and was hoping someone could help me
with a (fairly) simple query.

I need to join on the next row in a similar table with specific criteria.

I have a table with events per employee.
I need to have a query that gives per employee each event and the event
after it if it happened on the same day.

The Events table structure is:

EventID
Employee
EventDate
EventTime
EventType

I want my query resultset to be
Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
Where Event(2) is the first event of the employee that took place after
the other event.

Example
EventIDEmployeeEventDateEventTimeEventType
1John6/15/20067:00A
2Frank6/15/20067:15B
3Frank6/15/20067:17C
4John6/15/20067:20C
5Frank6/15/20067:25D
6John6/16/20067:00A
7John6/16/20068:30R

Expected Results
John, 6/15/2006, 7:00, A, 7:20, C
Frank, 6/15/2006, 7:15, B, 7:17, C
Frank, 6/15/2006, 7:17, C, 7:25, D
John, 6/16/2006, 7:00, A, 8:30, R

To get this result set it would have to be an inner join on employee and
date where the second event time is greater then the first. But I don't
want the all of the records with a greater time, just the first event 
after.


Thank You
Sim

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



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

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



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] DocBook 4.2 detecting at configure time

[Tom Lane]

"Oleg Golovanov" <[EMAIL PROTECTED]> writes:
> I have changed configure command. Currently I issue command:
> SGML_CATALOG_FILES=/usr/local/share/xml/docbook/4.2/docbook.cat 

Are you sure that's the place to look?  On my machine the appropriate
stuff is under .../share/sgml/, not /xml/.

> configure:22300: checking for DocBook V4.2
> onsgmls:conftest.sgml:1:54:W: cannot generate system identifier for 
> public text
> "-//OASIS//DTD DocBook V4.2//EN"

The catalog file you point to needs to have a publicId item for that
name, for instance on my machine I find

  

in /usr/share/sgml/docbook/xmlcatalog

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Interface Guidance and Opinions Needed

[Kenneth Downs]

Chris Golden wrote:

  
  
  
  
   
  Hello to all,
  

I use PHP as the web programming layer, it has a great community and
support for such things as XML RPC, which may fit the bill.  


  
   
  This is my first time
posting to this forum and I am very
new to PostgreSQL.  I am very excited about using it.  I have set up
a database and just need a point in the right direction on
interfacing.   I have an orders and messages database.  One of
the providers I would like to interface with has sent me an XML spec to
make HTTPS
posts to.  I am just wondering what would be the easiest thing to use
to
implement the two.  I would like to scan my database for new orders
then
do an HTTPS post to the provider.  I would also like to periodically
check
for new orders/messages from the provider via an HTTPS post and bring
them into
my database (a constant exchange of information).  I am wondering if
something like PHP would be a universal solution, or something similar?
   
  Sorry if the information
I have given is basic, I have been
doing research on the internet trying to figure out which direction to
go and
starting off is a little overwhelming.
   
  Thanks in advance for any
information
   
  Chris Golden
   
  




begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


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

Re: [GENERAL] help with error message from perl Pg

[Martijn van Oosterhout]

On Tue, Jun 20, 2006 at 09:33:13AM -0400, Geoffrey wrote:
> We have an unusual problem with some perl code that is processing data 
> via DBD facility.  Basically, the code consists of various subroutines 
> that are identified in a hash.  The primary script then references the 
> subroutines through a &$prog(...) syntax.  What appears to happen is 
> that all subroutines will run just fine if they do not need to do any 
> deletes or updates to the database.  Any subroutine that does a delete 
> or update runs fine, but the subsequent routine fails with the following 
> error:
> 
> prgcaphlg: FATAL:  invalid frontend message type 78

Errors in the FE-BE protocol are usually caused by users trying to use
the same connection from multiple threads simultaneously. libpq doesn't
handle that and so ends up sending things out of order.

Are you using threads?

Also, the database connection, how is the handle passed around? Youre
not closing it accedently somewhere?

Have a ncie day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] Interface Guidance and Opinions Needed

[Chris Golden]

 

Hello to all,

 

This is my first time posting to this forum and I am very
new to PostgreSQL.  I am very excited about using it.  I have set up
a database and just need a point in the right direction on
interfacing.   I have an orders and messages database.  One of
the providers I would like to interface with has sent me an XML spec to make HTTPS
posts to.  I am just wondering what would be the easiest thing to use to
implement the two.  I would like to scan my database for new orders then
do an HTTPS post to the provider.  I would also like to periodically check
for new orders/messages from the provider via an HTTPS post and bring them into
my database (a constant exchange of information).  I am wondering if
something like PHP would be a universal solution, or something similar?

 

Sorry if the information I have given is basic, I have been
doing research on the internet trying to figure out which direction to go and
starting off is a little overwhelming.

 

Thanks in advance for any information

 

Chris Golden

 

[GENERAL] help with error message from perl Pg

[Geoffrey]

We have an unusual problem with some perl code that is processing data 
via DBD facility.  Basically, the code consists of various subroutines 
that are identified in a hash.  The primary script then references the 
subroutines through a &$prog(...) syntax.  What appears to happen is 
that all subroutines will run just fine if they do not need to do any 
deletes or updates to the database.  Any subroutine that does a delete 
or update runs fine, but the subsequent routine fails with the following 
error:


prgcaphlg: FATAL:  invalid frontend message type 78
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Searching google for 'invalid frontend message type 78' returns no hits, 
which is a scary thing.


Any insights would be greatly appreciated.

--
Until later, Geoffrey

Any society that would give up a little liberty to gain a little
security will deserve neither and lose both.  - Benjamin Franklin

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

Re: [GENERAL] dynamic sorting...

[Florian G. Pflug]

Yavuz Kavus wrote:

this works fine, however the next doesnt(i couldnt compile it ) :
CREATE OR REPLACE FUNCTION ftest(_sort_column "varchar", _sort_direction 
"varchar")

  RETURNS refcursor AS
$BODY$
declare
 _result_set refcursor;
begin
 open _result_set for
select firstname, lastname from tb_test
order by
case
when _sort_column  ilike 'lastname' then lastname
else firstname
end
case
when _sort_direction  ilike 'asc' then asc
else desc
end;
 return _result_set;
end;$BODY$
  LANGUAGE 'plpgsql';

any suggestions to run it? (except EXECUTE SQLSTRING).

There isn't any other ;-)

The first example works, because you replaced something that
represents a _value_ by a case. But in the second example, you
replaces a _keyword_ by a case, and that won't work.

Even for the first example, using "execute ..." will probably be faster,
at least if tb_test has moderate size. Using "case" in the first example
is bound to confuse the optimizer, and leads potentially bad performance
IMHO.

greetings, Florian Pflug


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] psql for winxp?

[John DeSoi]

Jerry,

On Jun 19, 2006, at 10:47 PM, Jerry LeVan wrote:

Is it possible to have the installer only emplace psql and the  
necessary

support libs?



I'm not sure offhand. The installer has customization options, so it  
may be possible. But if not, it is probably easy enough just to  
install it, copy the files that you need, and then uninstall it.



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


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

Re: [GENERAL] simple query terminated by signal 11

[Thomas Chille]

Hi Qingqing,

thanks for your reply!

The postgresql version is 8.0.4 and runs on a debian based linux
server with kernel  2.6.11.2.

I never dealed with a core dump before. but  after setting "ulimit -c
1024" i got it.

I don't know how to post it, because the size is 1,5 MB?! I try to
attch it as gzip.

I also could not install dbg on the erroneous system, so i tried to
examine the core dump on another machine (gentoo) with  postgres 8.0.4
anf got the following output:

spoonpc01 ~ # gdb /usr/bin/postgres core
GNU gdb 6.4
Copyright 2005 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i686-pc-linux-gnu"...(no debugging symbols found)
Using host libthread_db library "/lib/tls/libthread_db.so.1".


warning: core file may not match specified executable file.
(no debugging symbols found)
Core was generated by `postgres: postgres backoffice_db [local] SELECT'
.
Program terminated with signal 11, Segmentation fault.
#0  0x080753c2 in DataFill ()
(gdb) where
#0  0x080753c2 in DataFill ()
#1  0xb74253d4 in ?? ()
#2  0x001d in ?? ()
#3  0x08356fa8 in ?? ()
#4  0x08379420 in ?? ()
#5  0x in ?? ()
(gdb)

What i can say too, is that i can reproduce the error  everytime with
the same query.

thanks in advonce

On 6/20/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:


""Thomas Chille"" <[EMAIL PROTECTED]> wrote
> Hi List,
>
> i run in to an error while dumping a db.
>
> after investigating it, i found a possible corrupted table. but i am not
sure.
> and i dont know how i can repair it? could it be a harddrive error?
>
>
> # now the error: SELECT * FROM hst_sales_report WHERE id = 5078867
>
> [6216 / 2006-06-19 18:46:23 CEST]LOG:  0: connection received:
> host=[local] port=
> [6216 / 2006-06-19 18:46:23 CEST]LOCATION:  BackendRun, postmaster.c:2679
> [6216 / 2006-06-19 18:46:23 CEST]LOG:  0: connection authorized:
> user=postgres database=backoffice_db
> [6216 / 2006-06-19 18:46:23 CEST]LOCATION:  BackendRun, postmaster.c:2751
> [6216 / 2006-06-19 18:46:23 CEST]LOG:  0: statement: SELECT * FROM
> hst_sales_report WHERE id = 5078867
> [6216 / 2006-06-19 18:46:23 CEST]LOCATION:  pg_parse_query, postgres.c:526
> [3762 / 2006-06-19 18:46:23 CEST]LOG:  0: server process (PID
> 6216) was terminated by signal 11
> [3762 / 2006-06-19 18:46:23 CEST]LOCATION:  LogChildExit,
postmaster.c:2358
> [3762 / 2006-06-19 18:46:23 CEST]LOG:  0: terminating any other
> active server processes
> [3762 / 2006-06-19 18:46:23 CEST]LOCATION:  HandleChildCrash,
postmaster.c:2251
> [3985 / 2006-06-19 18:46:23 CEST]WARNING:  57P02: terminating
> connection because of crash of another server process
> [3985 / 2006-06-19 18:46:23 CEST]DETAIL:  The postmaster has commanded
> this server process to roll back the current transaction and exit,
> because another server process exited abnormally and possibly
> corrupted shared memory.

Which verison are you using? In any way, except a random hardware error, we
expect Postgres to be able to detect and report the problem instead of a
silent core dump.  So can you gather the core dump and post it here?

Regards,
Qingqing



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



core.gz
Description: GNU Zip compressed data

---(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] join on next row

[Gurjeet Singh]

It would have been quite easy if done in Oracle's 'lateral view'
feature. But I think it is achievable in standard SQL too; using
subqueries in the select-clause.

Try something like this:

select
Employee, EventDate,
EventTime as e1_time,
EventType as e1_type,
(   select
EventTime
from
Events
where   Employee = O.Employee
and EventDate = O.EventDate
and EventTime > O.EventTime
limit   1
)as e_time_1,
(   select
EventType
from
Events
where   Employee = O.Employee
and EventDate = O.EventDate
and EventTime > O.EventTime
limit   1
)
from
Events

Hope it helps...

Regards,
Gurjeet.

On 6/20/06, Sim Zacks <[EMAIL PROTECTED]> wrote:

I am having brain freeze right now and was hoping someone could help me
with a (fairly) simple query.

I need to join on the next row in a similar table with specific criteria.

I have a table with events per employee.
I need to have a query that gives per employee each event and the event
after it if it happened on the same day.

The Events table structure is:

EventID
Employee
EventDate
EventTime
EventType

I want my query resultset to be
Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2)
Where Event(2) is the first event of the employee that took place after
the other event.

Example
EventIDEmployeeEventDateEventTimeEventType
1John6/15/20067:00A
2Frank6/15/20067:15B
3Frank6/15/20067:17C
4John6/15/20067:20C
5Frank6/15/20067:25D
6John6/16/20067:00A
7John6/16/20068:30R

Expected Results
John, 6/15/2006, 7:00, A, 7:20, C
Frank, 6/15/2006, 7:15, B, 7:17, C
Frank, 6/15/2006, 7:17, C, 7:25, D
John, 6/16/2006, 7:00, A, 8:30, R

To get this result set it would have to be an inner join on employee and
date where the second event time is greater then the first. But I don't
want the all of the records with a greater time, just the first event after.

Thank You
Sim

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



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

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

Re: [GENERAL] problem connecting to server

[Trigve Siver]

Hi,     Thank you, for the link... was helpfull.     Trigve  Qingqing Zhou <[EMAIL PROTECTED]> wrote:  "Trigve Siver" <[EMAIL PROTECTED]>wrote>> Thank you for reply. No there isn't any panic message. Log contain only> messages I have posted.>Check out this thread (or search "background writer process exited" inlist):http://archives.postgresql.org/pgsql-general/2006-05/msg01568.phpRegards,Qingqing---(end of broadcast)---TIP 5: don't forget to increase your free space map settings 
	
		Sneak preview the  all-new Yahoo.com. It's not radically different. Just radically better. 

[GENERAL] merge result sets

[simon]

hi all

i'm using postgres 7.3

my problem is i want to build a helper table:

UPDATE studienmodul_summary 
   SET kategorie = (SELECT kategorie_bezeichnung

if the SELECT gives back just one result row, everthing is easy. my
problem is, there is sometimes more than one result row. 

is there a way to concatenate all result rows and insert them in one
field?

any hint is very much appreciated.

thanks
simon

-- 
Simon Litwan   [EMAIL PROTECTED]
Wyona Inc.  -   Open Source Content Management   -   Apache Lenya
http://www.wyona.com  http://lenya.apache.org


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

Re: [GENERAL] Adding foreign key constraints without integrity check?

[Florian G. Pflug]

louis gonzales wrote:

Florian,
I understand where you're coming from.  Indexes are always unique and 
all RDBMS systems use them to 'uniquely' identify a row from the the 
perspective of internal software management.

Surely there are non-unique indices - meaning indices for which there
are more then one entry for a given key.

Index != PrimaryKey, so 
every table created, despite any Primary/Foreign key contraints put on 
them, always have a 1-1 Index per row entry.  At least that's the way I 
understand it, can someone else affirm this statement or redirect a 
misguided 'me ;)'?


In postgresql at least, I believe that if you create no index (or pk), then
there is no index. The only exception are toast-tables, but you don't even
see those tables normally, and they're just an implementation detail of how
large attributes are stored.

However, the whole point of this thread was whether there is a way to create
a FK without postgres checking if it's actually satisfied, or not. This could
speed up restoring a dump, because you know that the FK is actually satisfied in
that case.

My suggestion was to create the fk _before_ loading the data, and disable it
similarly to what "--disable-triggers" doest. It turned out, however, that a
FK always depends on a unique index (be it a primary key, or not), which 
prevents
my plan from working :-(

greetings, Florian Pflug


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

Re: [GENERAL] problem connecting to server

[Qingqing Zhou]

"Trigve Siver" <[EMAIL PROTECTED]> wrote
>
> Thank you for reply. No there isn't any panic message. Log contain only
> messages I have posted.
>

Check out this thread (or search "background writer process exited" in
list):

http://archives.postgresql.org/pgsql-general/2006-05/msg01568.php

Regards,
Qingqing



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

[GENERAL] best way to get PKey and FKey from inside applications (permission pb)

[Sandro Dentella]

Hi all,

I started using sqlalchemy (python ORM) that works really well but fails
detecting Primary and Foreign key other than for owner or
superusers. Sqlalchemy queries the information schema with the following query
that returns nothing if you are connected as a user with no particular
privileges: 

SELECT 
  table_constraints.constraint_name ,
  table_constraints.constraint_type ,
  table_constraints.table_name ,
  key_column_usage.table_schema ,
  key_column_usage.table_name ,
  key_column_usage.column_name ,
  key_column_usage.constraint_name ,
  constraint_column_usage.table_schema ,
  constraint_column_usage.table_name ,
  constraint_column_usage.column_name ,
  constraint_column_usage.constraint_name 

FROM information_schema.table_constraints 
  JOIN information_schema.constraint_column_usage ON
   constraint_column_usage.constraint_name = table_constraints.constraint_name 
  JOIN  information_schema.key_column_usage ON
   key_column_usage.constraint_name = constraint_column_usage.constraint_name

WHERE table_constraints.table_name = 'my_table'
  AND table_constraints.table_schema = 'public' ;

If you use '\d mytable' you get these information correctly so that there's
no reason to deny the same info from information_schema, correct?

Looking at how '\d' returns the information I always used a different query
(look here http://www.sqlalchemy.org/trac/ticket/71) that is not using
information_schema, but sqlalchemy would prefere to stick to the more
standard information_schema.  What would you guys suggest in this case?

Thanks in advance
sandro
*:-)


-- 
Sandro Dentella  *:-)
e-mail: [EMAIL PROTECTED] 
http://www.tksql.orgTkSQL Home page - My GPL work

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

   http://archives.postgresql.org

[GENERAL] Start up question about triggers

[Forums @ Existanze]

Hello 
all,
 
I know that this 
question may be really simple, but I have decided to ask here due to fact that I 
don't know how to search for this on google or on the docs.
 
I created a trigger 
fuction which updates a specific row in some table A. Is it possible to retain 
the query that was used to trigger the function. For example
 
Table 
A
query_row_id
query_row
 
 
 
TABLE 
B
id
name
 
 
 
 
 
If I create a 
trigger on table B that says that after the insert command to write the query 
into  table A. So if I do 
 
insert into B 
values(1,"Blah")
 
this will trigger my 
trigger. Is there any way to get the "insert into B values(1,"Blah")? At the 
moment I can see only the type of query that it is (INSERT UPDATE 
DELETE)
 
best 
regards,
Fotis
 

[GENERAL] DocBook 4.2 detecting at configure time

[Oleg Golovanov]

Hi!

I am using FreeBSD 5.4. There are installed:
docbook-4.2 V4.2 of the DocBook DTD, designed for technical 
documentati
docbook-sk-4.1.2_3  XML version of the DocBook DTD version controlled 
for Scrol

docbook-xml-4.2_1   XML version of the DocBook DTD
docbook-xsl-1.69.1  XSL DocBook stylesheets
dsssl-docbook-modular-1.79,1 DSSSL stylesheets for the DocBook DTD by 
Norman Walsh

sdocbook-xml-4.1.2.5_2 "Simplified" DocBook XML DTD

jade-1.2.1_9An object-oriented SGML/XML parser toolkit and DSSSL 
engine
OpenSP-1.5_6This package is a collection of SGML/XML tools 
called OpenSP


I have changed configure command. Currently I issue command:
SGML_CATALOG_FILES=/usr/local/share/xml/docbook/4.2/docbook.cat 
./configure --prefix=/usr/local/pgsql --enable-depend --enable-nls 
--enable-integer-datetimes --with-openssl --with-pam 
--enable-thread-safety --with-includes=/usr/local/include 
--with-libraries=/usr/local/lib --with-perl --with-python --with-tcl 
--with-tclconfig=/usr/local/lib/tcl8.4 2>&1 > sci-pgsql.log


I got the following:

checking for onsgmls... onsgmls
checking for openjade... no
checking for jade... jade
checking for DocBook V4.2... no
checking for DocBook stylesheets... 
/usr/local/share/sgml/docbook/dsssl/modular
checking for collateindex.pl... 
/usr/local/share/sgml/docbook/dsssl/modular/bin/collateindex.pl

checking for sgmlspl... no

and in file config.log:

configure:22300: checking for DocBook V4.2
onsgmls:conftest.sgml:1:54:W: cannot generate system identifier for 
public text

"-//OASIS//DTD DocBook V4.2//EN"
onsgmls:conftest.sgml:1:54:E: reference to entity "BOOK" for which no 
system ide

ntifier could be generated
onsgmls:conftest.sgml:1:0: entity was defined here
onsgmls:conftest.sgml:1:54:E: DTD did not contain element declaration 
for docume

nt type name
onsgmls:conftest.sgml:2:5:E: element "BOOK" undefined
onsgmls:conftest.sgml:3:7:E: element "TITLE" undefined
onsgmls:conftest.sgml:4:9:E: element "CHAPTER" undefined
onsgmls:conftest.sgml:5:8:E: element "TITLE" undefined
onsgmls:conftest.sgml:6:9:E: element "SECT1" undefined
onsgmls:conftest.sgml:7:10:E: element "TITLE" undefined
onsgmls:conftest.sgml:8:9:E: element "PARA" undefined
configure:22329: result: no


How it can be fixed?

Best regards,

Oleg Golovanov
Equant LLC



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

Re: [GENERAL] Database Clustering on multiple harddisk

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

arie nugraha wrote:
> Thanks a Alex,
> 
> But can tou give a bit explanation about multiple separate tablespace
> and HBA,
> or is there any resources, tutorial on how to use this features on
> postgresql

An HBA is a physical card.  Think of it as an "optical SCSI card".
Thus, there won't be any PostgreSQL docs for it.

Tablespaces let you specify what directory you want to put an object
in.  This is useful for large systems with multiple RAID sets on
different SCSI busses.  It's also useful for smaller systems where
you want named subdirectories for different groups of data.

> Thanks a lot
> 
> 
> Alex Turner wrote:
>> I think you may be a little confused...
>>
>> Last time I checked, Oracle RAC doesn't actualy support
clustering the
>> I/O, it's one Database backend with multiple instances on the 
>> front-end, which is only clustering the CPU bound part (it's
>> also VERY expensive).
>> 
>> If by clustering you mean multiple seperate tablespaces, then 
>> Postgresql does support them.
>> 
>> If you mean RAID, then that is implemented by the HBA (host bus
>>  adapter), and is transparent to Postgresql.
>> 
>> If you are looking for replication, many people find Slony a
>> good
add-on.

>> Alex
>>
>> On 6/20/06, *Arie Nugraha* <[EMAIL PROTECTED]
>> > wrote:
>>
>> Hi list,
>>
>> I am new postgresql user and i had read the documentation but
>> i dont if PostgreSQL support database clustering on multiple harddisk
>> (multiple I/O, just like DB2 and Oracle does)
>> or not.
>>
>> If postgreSQL support the database clustering is there any
>> documentation or tutorial that explain how to do it?
>>
>> Thanks all.

- --
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.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEl57xS9HxQb37XmcRAnmJAKCrBjCSH8CKJ8AS5zbXspoKqlS0VwCfWe4S
1RaKPq0A3cBKDKUSy9mAqhw=
=UN33
-END PGP SIGNATURE-

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

   http://archives.postgresql.org

[GENERAL] dynamic sorting...

[Yavuz Kavus]

hi everybody.i have a sp. i am passing two argument to it.first one : _sort_column --> defines which column will be used in "order by".second : _sort_direction --> define sorting direction(asc or desc).
i writing sp as :CREATE OR REPLACE FUNCTION ftest(_sort_column "varchar", _sort_direction "varchar")  RETURNS refcursor AS$BODY$declare _result_set refcursor;
begin open _result_set for    select firstname, lastname from tb_test     order by     case         when _sort_column  ilike 'lastname' then lastname        else firstname    end; return _result_set;
end;$BODY$  LANGUAGE 'plpgsql';this works fine, however the next doesnt(i couldnt compile it ) :CREATE OR REPLACE FUNCTION ftest(_sort_column "varchar", _sort_direction "varchar")

  RETURNS refcursor AS
$BODY$
declare
 _result_set refcursor;
begin
 open _result_set for
    select firstname, lastname from tb_test 
    order by 
    case 
        when _sort_column  ilike 'lastname' then lastname
        else firstname
    end 

    case 

        when _sort_direction  ilike 'asc' then asc

        else desc

    end;
 return _result_set;
end;$BODY$
  LANGUAGE 'plpgsql';any suggestions to run it? (except EXECUTE SQLSTRING).thanks a lot.