Re: [SQL] Query response time

2006-08-09 Thread Roman Neuhauser 
# [EMAIL PROTECTED] / 2006-08-08 13:25:47 +0100:
> I am using PostgresSQL 7.4 and having some serious performance issues.

> I am using tables that only contain approx 2GB of data.

> The install was performed by yum onto a RAID server using Centos. I am
> sure there is something fundamentally wrong for I can't believe that
> postgres would have the reputation it has based on the statistics I'm
> getting. Does anyone have any advice?

You'll need to configure PostrgreSQL, the default settings seem
to be tuned so that PostgreSQL starts even in very limited
environments.

http://www.postgresql.org/docs/8.1/static/runtime-config.html

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991

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

[SQL] PL/pgSQL and PHP 5

2006-08-09 Thread PostgreSQL Admin 
I'm having this problem inserting data from my form using PL/pgSQL.  
Here is the simplified version of my table and function (this example 
does not work,  also ):


CREATE TABLE theirry.sample (
   staff_id serial PRIMARY KEY NOT NULL,
   firstname varchar(100),
   lastname varchar(150),
   username varchar(35),
   identifier varchar(40),
   address2 varchar(180),
   activated boolean,
   activated_keys varchar(32)
);

CREATE OR REPLACE FUNCTION insert_staff_b
   (insert_firstname varchar)
RETURNS VOID AS
$$
   DECLARE
   BEGIN   
   INSERT INTO theirry.sample

   (firstname)
   VALUES
   (insert_firstname);
   RETURN;
   END;
$$
LANGUAGE plpgsql;


I have a form with a value firstname then call the query in php

select insert_staff_b('$_POST['firstname']::varchar)


Still I get this error:
Warning: pg_query(): Query failed: ERROR: function 
insert_staff_b(character varying) does not exist HINT: No function 
matches the given name and argument types. You may need to add explicit 
type casts.


Suggestions or maybe a place to read up on this problem.

Thanks in advance,
J

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

Re: [SQL] [PHP] PL/pgSQL and PHP 5

2006-08-09 Thread John DeSoi 


On Aug 9, 2006, at 10:36 PM, PostgreSQL Admin wrote:


select insert_staff_b('$_POST['firstname']::varchar)


Still I get this error:
Warning: pg_query(): Query failed: ERROR: function insert_staff_b 
(character varying) does not exist HINT: No function matches the  
given name and argument types. You may need to add explicit type  
casts.



Your select statement above has unbalanced single quotes. Assuming  
this is not really the issue, I would check the search_path and look  
at the function in psql or some admin tool to make sure the function  
name does not have different capitalization.





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


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

Re: [SQL] PL/pgSQL and PHP 5

2006-08-09 Thread Tom Lane 
PostgreSQL Admin <[EMAIL PROTECTED]> writes:
> CREATE OR REPLACE FUNCTION insert_staff_b
> (insert_firstname varchar)
> RETURNS VOID AS
> ...
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function 
> insert_staff_b(character varying) does not exist

Sure looks like it oughta work.  One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...

regards, tom lane

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

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

Re: [SQL] [PHP] PL/pgSQL and PHP 5

2006-08-09 Thread PostgreSQL Admin 

Thanks for the catch. I've tried:

$connection->execute("SELECT 
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT 
insert_staff_b('".$staff['insert_firstname']."'::varchar)");


None work... I'm scratching my head on this one.

Thanks,
J

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

  http://archives.postgresql.org

Re: [SQL] PL/pgSQL and PHP 5

2006-08-09 Thread PostgreSQL Admin 

Tom Lane wrote:

PostgreSQL Admin <[EMAIL PROTECTED]> writes:
  

CREATE OR REPLACE FUNCTION insert_staff_b
(insert_firstname varchar)
RETURNS VOID AS
...
Still I get this error:
Warning: pg_query(): Query failed: ERROR: function 
insert_staff_b(character varying) does not exist



Sure looks like it oughta work.  One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...

regards, tom lane

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

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

  

I never thought of that.  I look into it.

Thanks,
J

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

Re: [SQL] PL/pgSQL and PHP 5 - thanks

2006-08-09 Thread PostgreSQL Admin 

Thanks,

The search path was the problem.  Sometimes it's the simple things.

Big thanks,
J

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

Re: [SQL] [PHP] PL/pgSQL and PHP 5

2006-08-09 Thread John DeSoi 

Glad you found the problem.

On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:

$connection->execute("SELECT insert_staff_b('$staff 
[insert_firstname]'::varchar)");
$connection->execute("SELECT insert_staff_b('".$staff 
['insert_firstname']."'::varchar)");


If you are creating SQL functions you want to call from PHP, you  
might be interested in this simple class:


http://pgedit.com/resource/php/pgfuncall


Then instead of all the quoting issue you have above, you could  
simply call your SQL function like a normal PHP method call:


$connection->insert_staff_b($staff['insert_firstname']);




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


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