Re: [GENERAL] postmaster services problem

[Dave Page]

 

  
  
  From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED] On Behalf Of 
  liishyanSent: 19 April 2006 10:27To: 
  pgsql-general@postgresql.orgSubject: [GENERAL] postmaster services 
  problem
  
  
  Hi,
   
  I’m having problem starting the 
  postmaster service at my office’s server now. Everything works fine for a 
  year. But today morning, the I was unable to log into the database 
  server.
   
  When I tried to start the 
  postmaster service, it says, 
  “The service started and stopped. 
  Some services will stop automatically when there is no work to 
  do”.
   
Take a look in 
the logfile (probably in C:\Program Files\PostgreSQL\8.0\data\pg_log judging by 
the age of your install and the error message) and the event log to see if 
there are further clues there.
 
Regards, 
Dave.

[GENERAL] disk full scenario

[surabhi.ahuja]

if disk is near to full
 
and i establish a connection with postgres and read 
a table and then close
the following error logs comes in 
postgreslog:
 
17:10:47 CDT%authentication>LOG:  
connection authorized: user=sdc database=dbexpress<2006-04-05 17:10:47 
CDT%idle>LOG:  disconnection: session time: 0:00:00.00 user=sdc 
database=dbexpress host=[local] port=<2006-04-05 17:10:47 
CDT%idle>LOG:  disconnection: session time: 7:41:26.70 user=sdc 
database=dbexpress host=[local] port=<2006-04-05 17:10:47 
CDT%>ERROR:  could not access status of transaction 0<2006-04-05 
17:10:47 CDT%>DETAIL:  could not write to file 
"/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:No space 
left on device<2006-04-05 17:10:48 CDT%>LOG:  could not close 
temporary statistics file 
"/export/home1/sdc_image_pool/dbx/global/pgstat.tmp.24783": No space left on 
device<2006-04-05 17:10:49 CDT%>ERROR:  could not access status 
of transaction 0<2006-04-05 17:10:49 CDT%>DETAIL:  could not 
write to file "/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 
40960:No space left on device<2006-04-05 17:10:50 
CDT%>ERROR:  could not access status of transaction 0<2006-04-05 
17:10:50 CDT%>DETAIL:  could not write to file 
"/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:No space 
left on device<2006-04-05 17:10:52 CDT%>ERROR:  could not access 
status of transaction 0<2006-04-05 17:10:52 CDT%>DETAIL:  could 
not write to file "/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 
40960:No space left on device
 
and it keeps logging the same.
 
 
and later in the log i also see these 
messages:
<2006-04-05 23:46:37 CDT%>LOG:  could 
not close temporary statistics file 
"/export/home1/sdc_image_pool/dbx/global/pgstat.tmp.8207": No space left on 
device<2006-04-05 23:46:47 CDT%>LOG:  received fast shutdown 
request<2006-04-05 23:46:47 CDT%>LOG:  shutting 
down<2006-04-05 23:46:47 CDT%>PANIC:  could not access status of 
transaction 0<2006-04-05 23:46:47 CDT%>DETAIL:  could not write 
to file "/export/home1/sdc_image_pool/dbx/pg_subtrans/0012" at offset 
155648: No space left on device<2006-04-05 23:46:47 
CDT%>LOG:  background writer process (PID 8205) was terminated by signal 
6<2006-04-05 23:46:47 CDT%>LOG:  terminating any other active 
server processes<2006-04-05 23:46:47 CDT%>LOG:  all server 
processes terminated; reinitializing<2006-04-05 23:46:47 
CDT%>LOG:  database system shutdown was interrupted at 2006-04-05 
23:46:47 CDT<2006-04-05 23:46:47 CDT%>LOG:  checkpoint record is 
at 0/1FE5D4A0<2006-04-05 23:46:47 CDT%>LOG:  redo record is at 
0/1FE5D4A0; undo record is at 0/0; shutdown TRUE<2006-04-05 23:46:47 
CDT%>LOG:  next transaction ID: 1217370; next OID: 
612902<2006-04-05 23:46:47 CDT%>LOG:  database system was not 
properly shut down; automatic recovery in progress<2006-04-05 23:46:47 
CDT%>LOG:  record with zero length at 0/1FE5D4DC<2006-04-05 
23:46:47 CDT%>LOG:  redo is not required<2006-04-05 23:46:47 
CDT%>LOG:  database system is ready<2006-04-05 23:46:47 
CDT%>LOG:  shutting down<2006-04-05 23:46:47 CDT%>LOG:  
database system is shut down<2006-04-05 23:46:48 CDT%>LOG:  could 
not write "/export/home1/sdc_image_pool/dbx/global/pg_fsm.cache": No space left 
on device
 
<2006-04-05 23:52:03 CDT%>LOG:  database 
system was shut down at 2006-04-05 23:46:47 CDT<2006-04-05 23:52:03 
CDT%>LOG:  checkpoint record is at 0/1FE5D518<2006-04-05 23:52:03 
CDT%>LOG:  redo record is at 0/1FE5D518; undo record is at 0/0; shutdown 
TRUE<2006-04-05 23:52:03 CDT%>LOG:  next transaction ID: 1217370; 
next OID: 612902<2006-04-05 23:52:03 CDT%>LOG:  database system 
is ready<2006-04-05 23:52:04 CDT%>LOG:  connection received: 
host=[local] port=<2006-04-05 23:52:04 CDT%authentication>LOG:  
connection authorized: user=sdc database=template1<2006-04-05 23:52:04 
CDT%idle>LOG:  disconnection: session time: 0:00:00.00 user=sdc 
database=template1 host=[local] port=<2006-04-05 23:52:05 
CDT%>LOG:  connection received: host=[local] port=<2006-04-05 
23:52:05 CDT%authentication>LOG:  connection authorized: user=sdc 
database=dbexpress<2006-04-05 23:52:05 CDT%idle>LOG:  
disconnection: session time: 0:00:00.68 user=sdc database=dbexpress host=[local] 
port=<2006-04-05 23:52:22 CDT%>LOG:  connection received: 
host=localhost.localdomain port=60013<2006-04-05 23:52:22 
CDT%authentication>LOG:  connection authorized: user=sdc 
database=dbexpress<2006-04-05 23:53:29 CDT%>LOG:  connection 
received: host=[local] port=<2006-04-05 23:53:29 
CDT%authentication>LOG:  connection authorized: user=sdc 
database=dbexpress<2006-04-05 23:53:47 CDT%>LOG:  connection 
received: host=localhost.localdomain port=60017<2006-04-05 23:53:47 
CDT%authentication>LOG:  connection authorized: user=sdc 
database=dbexpress<2006-04-05 23:53:48 CDT%>LOG:  could not close 
temporary statistics file 
"/export/home1/sdc_image_pool/dbx/global/pgstat.tmp.10771": No space left on 
device<2006-04-05 23:54:02 CDT%>LOG:  connection received: 
host=[l

[GENERAL] Unable to connect to PostgreSQL server

[Martin Kuria]

hi,

I have a problem with my postgresql database it always gives me an error:

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL 
server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php 
on line 27


Please advice how I can manage my database to handle enough connections 
please advice.


+-+
| Martin W. Kuria (Mr.) [EMAIL PROTECTED]
++

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/



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

Re: [GENERAL] some error messages in postgeslog

[Richard Huxton]

surabhi.ahuja wrote:

I use PostgreSQL 8.0.0
 
it seems that the disk was close to full,
 
i executed a program (in C++) which opens a connection to Postgres using PQConnectdb.
 
and then it reads from a particluar table, and simply displays the values on the console.
 
after doing that it will close the connection using PQfinish
 
and thats when these error messages come



<2006-04-05 17:10:47 CDT%>DETAIL:  could not write to file 
"/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:
No space left on device
<2006-04-05 17:10:48 CDT%>LOG:  could not close temporary statistics file 
"/export/home1/sdc_image_pool/dbx/global/pgstat.tm
p.24783": No space left on device
<2006-04-05 17:10:49 CDT%>ERROR:  could not access status of transaction 0
<2006-04-05 17:10:49 CDT%>DETAIL:  could not write to file 
"/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:
No space left on device
<2006-04-05 17:10:50 CDT%>ERROR:  could not access status of transaction 0
<2006-04-05 17:10:50 CDT%>DETAIL:  could not write to file 
"/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:
No space left on device



is it true that for starting a connection to postgres read a table and then 
closing connection disk space will be needed.


It looks like it's the statistics monitoring that initially causes a 
problem here, so yes. You would in any case need some disk-space I'm 
sure, not necessarily a lot though.



Is it because of this reason these error messages are coming?


Yes, the "No space left on device" error does imply that you have no 
space left on that disk device. If this is so, you will need to add more 
storage or delete something else from it.



i have another question i have also noticed this error message in the postgres 
log, what are its implications and what happened that this error message came
:
2006-04-05 09:22:08 CDT%>LOG:  received fast shutdown request
<2006-04-05 09:22:08 CDT%>LOG:  aborting any active transactions
<2006-04-05 09:22:08 CDT%>LOG:  checkpoints are occurring too frequently (16 
seconds apart)
<2006-04-05 09:22:08 CDT%>HINT:  Consider increasing the configuration parameter 
"checkpoint_segments".


I don't see the word "error" anywhere in these log lines. Not everything 
logged is an error.


Checkpoints are to do with the Write-Ahead-Log (WAL) which logs 
transaction details before the changes are written to disk. They occur 
after a pre-defined time or when you reach the limit of 
"checkpoint_segments" wal-files written. You can find out more in the 
manuals.

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

HTH
--
  Richard Huxton
  Archonet Ltd

---(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] Permission problem opening database

[Richard Huxton]

Dilan A wrote:
When I started the postgres service and tried connecting using PgAdmin 
III I got the following error:
An Error has occurred. FATAL: could not open relation 16627/16628/2701: 
Permission Denied.


Any ideas on how this can be fixed.


First step is to check the permissions on all your database files. Have 
you any reason to think they may have changed? Do you have a new 
virus-scanner installed?


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] tomcat postgresql connectivity error

[Richard Huxton]

Danish wrote:

Hi,
Im trying to run a web application from tomcat which connects to a
database on postgresql 8.1.3
But when it tries to connect to a database.an E 101 Exception
occured while retrieving results. is shown

When I checked up the logs of tomcat.I could see..
SQLException [ERROR: No parser with id 17555]; nested exception is
org.postgresql.util.PSQLException: ERROR: No parser with id 17555


Google for "no parser with id" and read through the first few results. 
Does this (tsearch2 + undefined locale) sound like it might apply to you?


--
  Richard Huxton
  Archonet Ltd

---(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] Unable to connect to PostgreSQL server

[Harald Armin Massa]

Martin,please check out the server configuration documentation athttp://www.postgresql.org/docs/8.1/interactive/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS
your configuration file of choice is postgresql.conf within the data directory.(btw: because the default is rather big, it is very likely that there is some "not reusing connections" or "not closing connection when ready with usage" bug in your application)
best wishes,HaraldOn 4/20/06, Martin Kuria <[EMAIL PROTECTED]> wrote:
hi,I have a problem with my postgresql database it always gives me an error:Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQLserver: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
on line 27Please advice how I can manage my database to handle enough connectionsplease advice.+-+| Martin W. Kuria (Mr.) 
[EMAIL PROTECTED]++_Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/---(end of broadcast)---
TIP 6: explain analyze is your friend-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607
-PostgreSQL - supported by a community that does not put you on hold

Re: [GENERAL] strange query filter problems

[Jonas Henriksen]

Thanks, increasing the statistics did the trick:
ALTER TABLE tskjema ALTER  species SET STATISTICS 999

Is there a rule of thumb to tell how to set the statistics?

Jonas:))


On 4/19/06, Martijn van Oosterhout  wrote:
> On Wed, Apr 19, 2006 at 01:53:46PM +0200, Jonas Henriksen wrote:
> > Yes, explain analyze looks like this:
>
> Well, incorrect statistics are definitly the culprit, look:
>
>
> > "  ->  Index Scan using sskjema_pkey on sskjema s  (cost=0.00..3868.95 
> > rows=9738 width=157) (actual time=104.465..208.185 rows=14417 loops=1)"
>
> 50% off, not bad.
>
> > "->  Index Scan using speciesix on tskjema t  (cost=0.00..6.01 
> > rows=1 width=75) (actual time=0.140..24.594 rows=1703 loops=1)"
> > "  Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND 
> > (species ~<~ 'TAGGMAKRELM'::bpchar))"
> > "  Filter: (species ~~ 'TAGGMAKRELL%'::text)"
>
> Youch, 170200% percent off, which explains why it gets wildly bad
> timings. Can you increase the statistics on the species column? Check
> the docs for how.
> --
> 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)
>
> iD8DBQFERigoIB7bNG8LQkwRAhIdAJ9zFw0pkEqctLqHmbPteg7ofrDlEgCfS9ZO
> IscpR2d+wW7fzW3OUM3QYvo=
> =OewT
> -END PGP SIGNATURE-
>
>
>

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

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

Re: [GENERAL] Unable to connect to PostgreSQL server

[Shane Ambler]

Look in data/postgres.conf

You will find a line there such as max_connections = 50 you will also need
to check that shared_buffers is at least twice the max_connections.

You can also start Postmaster with -B nBuffers -N maxbackends
 

On 20/4/2006 17:41, "Martin Kuria" <[EMAIL PROTECTED]> wrote:

> hi,
> 
> I have a problem with my postgresql database it always gives me an error:
> 
> Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL
> server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
> on line 27
> 
> Please advice how I can manage my database to handle enough connections
> please advice.
> 
> +-+
> | Martin W. Kuria (Mr.) [EMAIL PROTECTED]
> ++
> 
> _
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 



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

Re: [GENERAL] Categories and Sub Categories (Nested)

[Alban Hertroys]

Ian Harding wrote:

You should look up the contrib module ltree which is made for this
sort of thing.  If you reinvent the wheel like this, you will be stuck
with 2 levels.  With ltree you can have as many as you need and add
more at any time.  It lets you query for ancestors and descendants of
any item at any level.

I highly recommend it.


Ah, one of those modules I still need to investigate. It looks promising.

Can it do networked structures? That'd be kind of hard with an index on 
a path when a node can have multiple parent nodes, I figure...


--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

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

// Integrate Your World //

---(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] Unable to connect to PostgreSQL server

[Shane Ambler]

You may also want to look into your php.ini settings.

pgsql.max_persistent pgsql.max_links pgsql.auto_reset_persistent and
PGSQL_CONNECT_FORCE_NEW are options you would want to check into.


> Look in data/postgres.conf
> 
> You will find a line there such as max_connections = 50 you will also need to
> check that shared_buffers is at least twice the max_connections.
> 
> You can also start Postmaster with -B nBuffers -N maxbackends
> 
> 
> On 20/4/2006 17:41, "Martin Kuria" <[EMAIL PROTECTED]> wrote:
> 
>> hi,
>> 
>> I have a problem with my postgresql database it always gives me an error:
>> 
>> Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL
>> server: FATAL: sorry, too many clients already in ~/includes/DbConnector.php
>> on line 27
>> 
>> Please advice how I can manage my database to handle enough connections
>> please advice.
>> 
>> +-+
>> | Martin W. Kuria (Mr.) [EMAIL PROTECTED]
>> ++
>> 
>> _
>> Express yourself instantly with MSN Messenger! Download today it's FREE!
>> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>> 
>> 
>> ---(end of broadcast)---
>> TIP 6: explain analyze is your friend
>> 
> 


---(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] Calling the same function more than once with the same arguments

[Clodoaldo Pinto]

2006/4/19, Ben <[EMAIL PROTECTED]>:
> Look into the immutable flag on function creation:
>
> http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html
>

That is what I needed to know, Thanks.

Regards, Clodoaldo Pinto

---(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] Query to check existence of stored procedure?

[Jim Buttafuoco]

Give this function a try, examples at the end, I used Postgresql 8.1.3 

-- s is the schema to look in
-- f is the function name

create or replace function isfunctionavailable(s text,f text)
returns bool
as
$$
declare
ans bool;
begin
select into ans true
from pg_proc p
join pg_namespace n on(p.pronamespace = n.oid)
where proname = f
and nspname = s
group by proname
having count(*) > 0;

return coalesce(ans,false);
end;
$$
language plpgsql
;

select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
select IsFunctionAvailable('public'::text,'junk'::text);



-- Original Message ---
From: Alexander Scholz <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Tue, 18 Apr 2006 17:08:50 +0200
Subject: Re: [GENERAL] Query to check existence of stored procedure?

> Hi Jim,
> 
> >> select count(*) from pg_proc where proname = 'your_function';
> >> 
> > don't forget about schema's, you will need to join with
> > pg_namespace.oid and pg_proc.pronamespace
> 
> your answer looks a little bit cryptic for me being somebody who hasn't
> had to dive into the pg_... tables yet. :-)
> 
> What do you exactly mean? Could you provide me a complete query for that
> job?
> 
> Is there anything to consider, if the user performing this query is NOT
> the owner of the stored prodcedure? (but he needs this info as well!)
> 
> Thank you in advance,
> 
> Alexander.
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
--- End of Original Message ---


---(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] Calling the same function more than once with the

[Scott Marlowe]

On Wed, 2006-04-19 at 18:23, Tom Lane wrote:
> "Clodoaldo Pinto" <[EMAIL PROTECTED]> writes:
> > I'm building a function caller() in which a certain function called()
> > will be called many times in from clauses with the same arguments and
> > I'm wondering if is there a performance penalty for that or if the sql
> > engine is smart enough to call called() only once.
> 
> No, it isn't.

HOWEVER, depending on how you declare the function, you should be able
to index it.

---(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: FW: [GENERAL] code to cancel a running query, worker thread

[Tom Lane]

"surabhi.ahuja" <[EMAIL PROTECTED]> writes:
> i have the following peice of code, which is meant for cancelling
> queries in between
> [ big chunk of Java code ]

You'd be more likely to find a knowledgeable answer on the pgsql-jdbc
mailing list.

regards, tom lane

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

[GENERAL] How to replace rows in table so that foreign key rows are not deleted

[Andrus]

I want to replace ( delete and insert) records in master table .
I delete and insert record with same primary key.
I want that foreign key records are not deleted.

I tried

begin;
create temp table t1 ( pk integer primary key );
insert into t1 values(1);
create temp table t2 (fk integer );
alter table t2 add foreign key (fk) references t1 on delete cascade 
deferrable initially deferred;
insert into t2 values(1);
-- Howto: set delete_constraint deferred
delete from t1;
insert into t1 values(1);
commit;
select * from t2;

Observed: no rows

Expected: t2 must contain one row.

foreign key check and deletion should occur only when transaction commits.

Any idea ?
Is there any generic way to turn off foreign key constraints before delete 
command in transaction ?

Andrus. 



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

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

Re: [GENERAL] HUGE Stack space is gettiing consumed

[Martijn van Oosterhout]

On Thu, Apr 20, 2006 at 08:20:30PM +0530, Mavinakuli, Prasanna (STSD) wrote:
>  
> Hello Martijin,
> 
> I am not able to make it ..Code what u sent is *not* giving desired
> result..
> That is -when we try to fetch huge data in a thread routine it says
> "could not receive data from server: Error 0"
> If it was in main thread then it goes thorugh..I.e It's able to get the
> huge data.

Well, I don't understand. There's no interesting difference between the
code I sent and the code you sent...

> Will send the tusc output (stack trace)

Not stack trace, strace which is a system call trace. Don't forget -f
to trace the threads also.
-- 
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] How to replace rows in table so that foreign key rows

[Berend Tober]

Andrus wrote:

Is there any generic way to turn off foreign key constraints before delete 
command in transaction ?


From TFM: "--disable-triggers
This option is only relevant when performing a data-only restore. It 
instructs pg_restore to execute commands to temporarily disable triggers 
on the target tables while the data is reloaded. Use this if you have 
referential integrity checks or other triggers on the tables that you do 
not want to invoke during data reload.


Presently, the commands emitted for --disable-triggers must be done as 
superuser. So, you should also specify a superuser name with -S, or 
preferably run pg_restore as a PostgreSQL superuser."


So, you could use this option with pg_dump/pg_restore, and look at the 
"commands to temporarily disable triggers" it produces.


 I did so, and for a table named 'country' the following SQL statements 
were produced:


-- Disable triggers
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 
'country'::pg_catalog.regclass;


/* COPY command goes here to bulk load table data. */

-- Enable triggers
UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) 
FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 
'country'::pg_catalog.regclass;



Regards,
Berend Tober

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

Re: [GENERAL] grant privileges across schemas

[Fernan Aguero]

+[ Bruno Wolff III <[EMAIL PROTECTED]> (19.Apr.2006 14:39):
|
| On Wed, Apr 19, 2006 at 09:29:50 -0300,
|   Fernan Aguero <[EMAIL PROTECTED]> wrote:
| > 
| > The following works, but I'm not sure about the consequences
| > of granting USAGE to a schema, as the documentation is
| > not clear, IMO : "For schemas, allows access to objects
| > contained in the specified schema (assuming that the
| > objects' own privilege requirements are also met).
| > Essentially this allows the grantee to "look up" objects
| > within the schema."
| > mydb=> GRANT USAGE ON SCHEMA myschema TO readonly;
| > GRANT
| > 
| > After doing this, how do I give SELECT privilege to this
| > user for all tables of this schema? 
| 
| Granting access to a schema allows use of that schema. It does not grant
| access to objects contained in the schema. You will need to do a grant for
| each object in addition to what you are already doing. You will probably want
| to write a script or function to do it, as there isn't a built in command
| to do grants to multiple objects.
|
+]

Bruno,

thanks for your reply, yes I've found a couple of mentions
to 'write your own script/function' by searching Google.

I just hoped someone would have come across this before and
had a solution at hand :)


Fernan

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

Re: [GENERAL] Calling the same function more than once with the same arguments

[Clodoaldo Pinto]

2006/4/19, Ben <[EMAIL PROTECTED]>:
> Look into the immutable flag on function creation:

I have read that section and I'm still not sure about it.

This is the caller() function:

create or replace function caller(int4)
returns some_type as

$body$

select
  array(select distinct a from called($1)) as a
  ,
  array(select distinct b from called($1)) as b
;

$body$
language 'sql' stable strict;

Since called() will read from a table that can be modified it is
declared as stable and not as immutable.

This is what is in section 32.6:
" A STABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments FOR ALL ROWS WITHIN A
SINGLE STATEMENT."

Is called() called from within a single statement in caller()?

Will the optimizer optimize the multiple calls (there will be about
30) to a single call?

Regards, Clodoaldo Pinto

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

Re: [GENERAL] How to replace rows in table so that foreign key rows

[Andrus]

>  I did so, and for a table named 'country' the following SQL statements 
> were produced:
>
> -- Disable triggers
> UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid = 
> 'country'::pg_catalog.regclass;
>
> /* COPY command goes here to bulk load table data. */
>
> -- Enable triggers
> UPDATE pg_catalog.pg_class SET reltriggers = (SELECT pg_catalog.count(*) 
> FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid) WHERE oid = 
> 'country'::pg_catalog.regclass;

Berend,

thank you for bright idea.
this may cause invalid foreign key data to be loaded into database.

I want that in end of transaction Postgres will check for correctness of 
foreign keys like DEFERRED clause suggests.
How this check can be forced ?

Andrus. 



---(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] How to replace rows in table so that foreign key rows

[Stephan Szabo]

On Thu, 20 Apr 2006, Andrus wrote:

> I want to replace ( delete and insert) records in master table .
> I delete and insert record with same primary key.
> I want that foreign key records are not deleted.
>
> I tried
>
> begin;
> create temp table t1 ( pk integer primary key );
> insert into t1 values(1);
> create temp table t2 (fk integer );
> alter table t2 add foreign key (fk) references t1 on delete cascade
> deferrable initially deferred;
> insert into t2 values(1);
> -- Howto: set delete_constraint deferred
> delete from t1;
> insert into t1 values(1);
> commit;
> select * from t2;
>
> Observed: no rows
>
> Expected: t2 must contain one row.
>
> foreign key check and deletion should occur only when transaction commits.

Actually, this looks like a case where SQL99 strongly implies that the
action happens even for non-immediate constraints as part of the delete
but SQL2003 changed that and we didn't notice.  This should probably be
reasonably straightforward to change I think (hope).

> Any idea ?
> Is there any generic way to turn off foreign key constraints before delete
> command in transaction ?

Right now, probably nothing short of dropping and readding the constraint.

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

Re: [GENERAL] grant privileges across schemas

[Bruno Wolff III]

On Thu, Apr 20, 2006 at 13:52:38 -0300,
  Fernan Aguero <[EMAIL PROTECTED]> wrote:
> +[ Bruno Wolff III <[EMAIL PROTECTED]> (19.Apr.2006 14:39):
> |
> | 
> | Granting access to a schema allows use of that schema. It does not grant
> | access to objects contained in the schema. You will need to do a grant for
> | each object in addition to what you are already doing. You will probably 
> want
> | to write a script or function to do it, as there isn't a built in command
> | to do grants to multiple objects.
> |
> +]
> 
> Bruno,
> 
> thanks for your reply, yes I've found a couple of mentions
> to 'write your own script/function' by searching Google.
> 
> I just hoped someone would have come across this before and
> had a solution at hand :)

I am pretty sure some sample ones have been posted to at least one of the lists
and should be in the archives. I wouldn't be surprised to hear that techdocs
had a sample there, if you can't find one in the archives.

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

[GENERAL] sudo-like behavior

[A.M.]

Hello,

I have written a crontab-like daemon which accepts jobs from users through
a table and executes SQL statements after certain events or intervals.
This daemon maintains a persistent connection to the database as a
superuser.

The problem is that I wish to run arbitrary SQL as an unprivileged user
but SET SESSION AUTHORIZATION is easily reversed via RESET SESSION
AUTHORIZATION. Since I don't have the role's password, I cannot connect as
him through a secondary connection.

It seems I am stuck so please allow me to propose an extension:
SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];

If a password is specified, then any call to RESET SESSION AUTHORIZATION
would also need to include the WITH PASSWORD clause (and the correct
password) to be successful. This would allow for blocks of foreign code to
be executed as an arbitrary user. I am not sure this would work for SET
ROLE because of role inheritance.

Does anyone have a better idea?

Thanks,

-M


---(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] odbc problem

[Tom Lane]

"P.MO" <[EMAIL PROTECTED]> writes:
> I've just recompiled a postgres 8.1.3 on freebsd 5.4 and since
> then, I can no more access it with odbc.

What happens exactly when you try?  Does it still work from other
clients, eg psql?

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] sudo-like behavior

[Tom Lane]

"A.M." <[EMAIL PROTECTED]> writes:
> It seems I am stuck so please allow me to propose an extension:
> SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];

This idea is extremely unlikely to be accepted, as the password would be
at risk of exposure in places like the pg_stat_activity view.

I think the correct way to do what you want is via a SECURITY DEFINER
function.

regards, tom lane

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

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

Re: [GENERAL] sudo-like behavior

[Karsten Hilbert]

On Thu, Apr 20, 2006 at 04:06:19PM -0400, A.M. wrote:

> The problem is that I wish to run arbitrary SQL as an unprivileged user

Would wrapping the SQL in a stored procedure with "security
definer" help any ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

   http://archives.postgresql.org

[GENERAL] GiST index slower than seqscan

[CG]

I'm still trying to wrap my brain around this one. Please forgive me if this is
the proverbial "dead horse" that I'm beating.

In case you're unfamiliar with this particular horse, I'm using ltree to create
a full text index on some <= 50 char long fields for a lookup table. The idea
was to be able to tear through tons of data quickly finding case insensitive
substring matches.  

Here's my index...

CREATE INDEX letter_search_vector_idx
  ON letter_search
  USING gist
  (search_vector);

I know that the index is bigger than the table, but shouldn't it be able to
quickly scan the few branches that matter?

I've tried to do a varchar-based substring lookup table, and the size for that
table+index is enormous compared to the ltree table + index (which is huge
anyway)

I'm thinking that I've created something or am using something incorrectly.

I need to be set straight. Please advise!

[snip]

Welcome to psql 8.1.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit
data=# analyze letter_search;
ANALYZE
data=# explain select * from letter_search where search_vector ~
charslquery('669344');
QUERY PLAN


---
 Bitmap Heap Scan on letter_search  (cost=59.14..4978.98 rows=1467 width=162)
   Recheck Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
   ->  Bitmap Index Scan on letter_search_vector_idx  (cost=0.00..59.14
rows=146
7 width=0)
 Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
(4 rows)

data=# explain analyze select * from letter_search where search_vector ~
charslquery('669344');
 QUERY PLAN


-
 Bitmap Heap Scan on letter_search  (cost=59.14..4978.98 rows=1467 width=162)
(a
ctual time=63061.402..63072.362 rows=2 loops=1)
   Recheck Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
   ->  Bitmap Index Scan on letter_search_vector_idx  (cost=0.00..59.14
rows=146
7 width=0) (actual time=63058.094..63058.094 rows=2 loops=1)
 Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
 Total runtime: 63072.411 ms
(5 rows)

data=# set enable_bitmapscan=off;
SET
data=# explain select * from letter_search where search_vector ~ charslqu
ery('669344');
  QUERY PLAN


--
 Index Scan using letter_search_vector_idx on letter_search 
(cost=0.00..5837.70
 rows=1467 width=162)
   Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
(2 rows)

data=# explain analyze select * from letter_search where search_vector ~
charslquery('669344');
   QUERY
PLA
N

-
 Index Scan using letter_search_vector_idx on letter_search 
(cost=0.00..5837.70
 rows=1467 width=162) (actual time=14582.619..162637.887 rows=2 loops=1)
   Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
 Total runtime: 162637.977 ms
(3 rows)

data=# set enable_indexscan=off;
SET
data=# explain select * from letter_search where search_vector ~ charslqu
ery('669344');
  QUERY PLAN
--
 Seq Scan on letter_search  (cost=0.00..55232.18 rows=1467 width=162)
   Filter: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
(2 rows)

data=# explain analyze select * from letter_search where search_vector ~
charslquery('669344');
  QUERY PLAN


--
 Seq Scan on letter_search  (cost=0.00..55232.18 rows=1467 width=162) (actual
ti
me=4725.525..9428.087 rows=2 loops=1)
   Filter: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)
 Total runtime: 9428.118 ms
(3 rows)

[/snip]


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(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] sudo-like behavior

[A.M.]

On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:
> "A.M." <[EMAIL PROTECTED]> writes:
>
>> It seems I am stuck so please allow me to propose an extension:
>> SET SESSION AUTHORIZATION user [WITH PASSWORD 'password];
>>
>
> This idea is extremely unlikely to be accepted, as the password would be
> at risk of exposure in places like the pg_stat_activity view.
>
> I think the correct way to do what you want is via a SECURITY DEFINER
> function.

Perhaps I can't wrap my head around it- I have the SQL as a string in a
table. I interpret that you propose that I accept only function names and
allow users to create security definer functions which I then call as the
superuser (carefully checking for the security definer flag). What about
commands that can't be run from within transactions?

I guess there is no way to stream arbitrary SQL in a permissions sandbox
if the original login user isn't the one I want. The security definer
method is a good enough workaround. Thanks.

-M


---(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] sudo-like behavior

[Tom Lane]

"A.M." <[EMAIL PROTECTED]> writes:
> On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:
>> I think the correct way to do what you want is via a SECURITY DEFINER
>> function.

> Perhaps I can't wrap my head around it- I have the SQL as a string in a
> table.

Well, the simplest thing would be

create function exec(text) returns void as $$
begin
execute $1;
end$$ language plpgsql strict security definer;

revoke execute on exec(text) from public;
grant execute on exec(text) to whoever-you-trust;

although personally I'd try to restrict what the function can be used
for a bit more than that.  If the allowed commands are in a table, you
could perhaps pass the table's key to exec() and let it pull the string
from the table for itself.

> What about commands that can't be run from within transactions?

There aren't that many of those.  Do you really need this for them?

For that matter, do you really need this at all?  Have you considered
granting role membership as an alternative solution path?  The SQL
permissions mechanism is quite powerful as of 8.1, and if it won't
do what you want, maybe you have not thought hard enough.

regards, tom lane

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

[GENERAL] full text search: the concept of a "word"

[Tomi NA]

I'm considering using tsearch2 in the project I'm working on right
now...however, I'm not sure if tsearch2 can handle my very specific
requirements - I therefore hope someone can tell me if the following
is possible and how I should go about it...

My textfields are trigger-generated using information from a number of
tables: these fields can be, say, a couple of thousand characters
wide.
Up to here, there's no problem.
What I'd like to do is define - possibly using regexps - what
constitutes a word. For instance, my word separator is a semicolon,
not a space; a dash is not a separator, and neither are language
specific characters (which might be interpreted that way by a language
agnostic tool)...
BTW, I use UTF-8 as my database encoding if it's of any importance.

What it comes down to is this: is it possible to somehow define what
constitutes a word?

TIA,
Tomislav

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

[GENERAL] setting the environment locale - linux, windows

[Tomi NA]

This is probably somewhat offtopic, but it does relate to postgresql so...
Problem summary: I have a UTF-8 encoded database running on linux on
which upper() and lower() string functions ignore locale specific
characters.
To make things a bit more interesting, the development machines
(including the db server) are linux based, while the target servers
are a mixture of linux and windows servers.

I need to compare strings case insensitive. Proper collation would be
a plus, but I could live without it for the time beeing.
How does one set the e.g. german locale in linux? Or in windows? Is it
given as a parameter of initdb and frozen aftewards or does the
postmaster look to it's environment for this information (LC_ALL,
LC_CTYPE variables)?
Are there plans to enable assigning locale at the database level?

Tomislav

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

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

Re: [GENERAL] full text search: the concept of a "word"

[Teodor Sigaev]

My textfields are trigger-generated using information from a number of
tables: these fields can be, say, a couple of thousand characters
wide.
Up to here, there's no problem.
What I'd like to do is define - possibly using regexps - what
constitutes a word. For instance, my word separator is a semicolon,
not a space; a dash is not a separator, and neither are language
specific characters (which might be interpreted that way by a language
agnostic tool)...
BTW, I use UTF-8 as my database encoding if it's of any importance.


I do not see a big problem: just write your own parser.

It's may be a problem with UTF-8: only CHS head tsearch2 supports UTF-8. But you 
can find a patch on 8.1 at http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/





--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] GiST index slower than seqscan

[Teodor Sigaev]

In case you're unfamiliar with this particular horse, I'm using ltree to create
a full text index on some <= 50 char long fields for a lookup table. The idea
was to be able to tear through tons of data quickly finding case insensitive
substring matches.  



Why it is a ltree, not a tsearch?



 Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery)


That's the problem. Queries which begin with '*' will be slow enough...



Try to reduce SIGLENINT in tsearch2/gistidx.h up to 8 (do not forget reindex !!) 
and try it


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] sudo-like behavior

[Agent M]

I really haven't provided enough details- my fault. What I want to 
accomplish is a general-purpose timer facility for postgresql. Ideally, 
arbitrary roles provide statements to run at certain intervals. The 
benefit here is that the user connections can go away and only a single 
timer connection is maintained (waiting on notifications to update).


Examples of where this could be useful:
1) simulated materialized views
2) daily tasks such as cache cleanup/refresh/updates
3) expensive tasks which run regularly

Arbitrary statements could be executed on a timed basis without needing 
local access for crontab or persistent remote access.


Anyway, here is the table:
CREATE TABLE pgtimer._timer
(
id SERIAL PRIMARY KEY,
repeats INTEGER NOT NULL, --repeats X times as countdown
lastfired TIMESTAMP,

waitinterval INTERVAL, --OR
	specialeventid INTEGER REFERENCES pgtimer.specialevent, --various 
special events such as startup, autovacuum, or notifications

detail TEXT, --stores notification event name if applicable
statement TEXT NOT NULL,
asrole TEXT NOT NULL
);

A separate view with rules handles insert/update capabilities and 
throws a notification so that the daemon is notified to refresh its 
countdown to the next event. The actual statement execution is all I 
have left to do. I could force users to define security definer 
functions but then vacuuming capability is lost (autovacuum can't 
handle everything).


If there is an architecture change I could make to rectify this, I am 
all ears. Thanks!


-M

On Apr 20, 2006, at 5:03 PM, Tom Lane wrote:


"A.M." <[EMAIL PROTECTED]> writes:

On Thu, April 20, 2006 4:21 pm, Tom Lane wrote:

I think the correct way to do what you want is via a SECURITY DEFINER
function.


Perhaps I can't wrap my head around it- I have the SQL as a string in 
a

table.


Well, the simplest thing would be

create function exec(text) returns void as $$
begin
execute $1;
end$$ language plpgsql strict security definer;

revoke execute on exec(text) from public;
grant execute on exec(text) to whoever-you-trust;

although personally I'd try to restrict what the function can be used
for a bit more than that.  If the allowed commands are in a table, you
could perhaps pass the table's key to exec() and let it pull the string
from the table for itself.


What about commands that can't be run from within transactions?


There aren't that many of those.  Do you really need this for them?

For that matter, do you really need this at all?  Have you considered
granting role membership as an alternative solution path?  The SQL
permissions mechanism is quite powerful as of 8.1, and if it won't
do what you want, maybe you have not thought hard enough.

regards, tom lane


¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
[EMAIL PROTECTED]
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬


---(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] [ADMIN] what the problem with this query

[Jim C. Nasby]

On Fri, Apr 14, 2006 at 11:09:16AM +, venu gopal wrote:
> Hi all,
>When i try to run the following query it gives the following error what 
> was wrong in the query.
> 
> Query:SELECT o.orgunitname AS ouname, e.entrynumber AS value_field, 
> centroid(c.the_geom) AS the_geom, c.ogc_fid AS ogc_fid FROM (ctrphc AS c 
> INNER JOIN orgunit o ON c.dhs_ouname = o.orgunitname) INNER JOIN (SELECT 
> orgunitid, entrynumber FROM routinedataou4 WHERE dataelementid=1195 AND 
> dataperiodid = 86) AS e ON o.orgunitid = e.orgunitid) AS new_table USING 
> UNIQUE ogc_fid USING SRID=-1
> 
> Error::ERROR:  syntax error at or near ")" at character 333

Extra ) after the the last ON.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] sudo-like behavior

[Tom Lane]

Agent M <[EMAIL PROTECTED]> writes:
> I really haven't provided enough details- my fault. What I want to 
> accomplish is a general-purpose timer facility for postgresql.

I'm not really sure why you think it'd be a good idea for such a thing
to operate as an unprivileged user that gets around its lack of
privilege by storing copies of everyone else's passwords.  I can think
of several reasonable ways to design the privilege handling for a
cron-like facility, but giving it cleartext copies of everyone's
passwords is not one of them.

regards, tom lane

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

Re: [GENERAL] recovery with pg_xlog

[Jim C. Nasby]

On Tue, Apr 18, 2006 at 11:40:42AM +0530, [EMAIL PROTECTED] wrote:
> We want to prepare a backup machine (for disaster management) by
> passing only the ARCHIVEDIR directory from another online machine - both
> the machines have Postgresql installed. We have sent PGLOG and ARCHIVEDIR
> but the recovery is not successful unless we send the FULL DATA Directory.
> 
> Is it possible,to doing such a procedure,if yes,then please tell me the
> steps.

No. PITR (what you're describing) depends on having a *correct copy* of
PGDATA available. You should read up about PITR at
http://www.postgresql.org/docs/8.1/interactive/backup-online.html
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Query to check existence of stored procedure?

[Jim C. Nasby]

And what happens if you have an overloaded function? :)

On Thu, Apr 20, 2006 at 07:55:50AM -0400, Jim Buttafuoco wrote:
> Give this function a try, examples at the end, I used Postgresql 8.1.3 
> 
> -- s is the schema to look in
> -- f is the function name
> 
> create or replace function isfunctionavailable(s text,f text)
> returns bool
> as
> $$
> declare
> ans bool;
> begin
> select into ans true
> from pg_proc p
> join pg_namespace n on(p.pronamespace = n.oid)
> where proname = f
> and nspname = s
> group by proname
> having count(*) > 0;
> 
> return coalesce(ans,false);
> end;
> $$
> language plpgsql
> ;
> 
> select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
> select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
> select IsFunctionAvailable('public'::text,'junk'::text);
> 
> 
> 
> -- Original Message ---
> From: Alexander Scholz <[EMAIL PROTECTED]>
> To: pgsql-general@postgresql.org
> Sent: Tue, 18 Apr 2006 17:08:50 +0200
> Subject: Re: [GENERAL] Query to check existence of stored procedure?
> 
> > Hi Jim,
> > 
> > >> select count(*) from pg_proc where proname = 'your_function';
> > >> 
> > > don't forget about schema's, you will need to join with
> > > pg_namespace.oid and pg_proc.pronamespace
> > 
> > your answer looks a little bit cryptic for me being somebody who hasn't
> > had to dive into the pg_... tables yet. :-)
> > 
> > What do you exactly mean? Could you provide me a complete query for that
> > job?
> > 
> > Is there anything to consider, if the user performing this query is NOT
> > the owner of the stored prodcedure? (but he needs this info as well!)
> > 
> > Thank you in advance,
> > 
> > Alexander.
> > 
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> > 
> >http://archives.postgresql.org
> --- End of Original Message ---
> 
> 
> ---(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
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [GENERAL] Query to check existence of stored procedure?

[Jim C. Nasby]

If you're on a more recent version, you can try and select the procname
for a proc and trap the error:

decibel=# select 'abs(bigint)'::regprocedure;
 regprocedure 
--
 abs(bigint)
(1 row)

decibel=# select 'abs(text)'::regprocedure;
ERROR:  function "abs(text)" does not exist
decibel=# 

If you don't care about arguments you can use regproc.

On Tue, Apr 18, 2006 at 08:26:49AM +0200, Alexander Scholz wrote:
> Hi Newsgroup,
> 
> I need a query which can check for the existence of a certain stored
> procedure.
> 
> (The pendant for MS SQL is
> 
> IF EXISTS (SELECT * FROM "sysobjects" WHERE "id" =
> object_id(N'"MyTestStoredProcedure"') and OBJECTPROPERTY("id",
> N'IsProcedure') = 1)
> ...
> )
> 
> Any help would be appreciated! :-)
> 
> Thanx in advance,
> 
> Alexander.
> 
> ---(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
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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