Re: [GENERAL] postmaster services problem
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
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
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
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
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
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
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
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
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)
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
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
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?
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
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
"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
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
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
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
+[ 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
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
> 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
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
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
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
"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
"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
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
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
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
"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"
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
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"
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
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
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
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
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
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?
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?
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