[GENERAL] Check the existance of temporary table
Dear Sirs, how I can check the existance of temporary table? I.e. wich query I have to use to know if MY_TEMP_TABLE exists? IF EXISTS(??? query ???) THEN Thank you in advance Domenico ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PostgreSQL PAM function contrib
A function to authenticate username/password pairs via PAM. y_pam_auth( username text, password text ) returns bool http://www.yellowbank.com/code/PostgreSQL/y_pam/ This obviously requires that you trust the server. Best. -- Ron Peterson https://www.yellowbank.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] postgres error message upon boot
FreeBSD-6.2 (PostgreSQL) 8.2.3 Occasionally, when booting up, I receive this error message: quote Mar 25 08:28:24 scorpio postgres[756]: [1-1] FATAL: the database system is starting up end quote Since it only happens occasionally, I have not been able to figure out what is causing it. It will usually, although not always, not happen when I next boot the system. Any ideas on how to correct this problem would be welcomed. -- Gerard A man is known by the company he organizes. A. Bierce signature.asc Description: PGP signature
Re: [GENERAL] postgres error message upon boot
Gerard Seibert wrote: FreeBSD-6.2 (PostgreSQL) 8.2.3 Occasionally, when booting up, I receive this error message: quote Mar 25 08:28:24 scorpio postgres[756]: [1-1] FATAL: the database system is starting up end quote Since it only happens occasionally, I have not been able to figure out what is causing it. It will usually, although not always, not happen when I next boot the system. Any ideas on how to correct this problem would be welcomed. It's not actually a problem, it just means that some client is connecting to the database while it's starting up. That clients connection will fail with the above error message. As soon as the database startup is complete, they will be able to connect again. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Check the existance of temporary table
Domenico- Assuming your schema will be pg_temp_1 vi InitialTableDisplayStatements.sql select * from pg_tables where pg_namespace = 'pg_temp1'; /usr/local/pgsql/bin/psql -f InitialTableDisplayStatements.sql -U username -h dbname -p Port db.out then write a quick java app to parse the db.out contents for 'Temp' Ciao- M-- --- This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited. --- Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. - Original Message - From: dfx [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Sunday, March 25, 2007 8:32 AM Subject: [GENERAL] Check the existance of temporary table Dear Sirs, how I can check the existance of temporary table? I.e. wich query I have to use to know if MY_TEMP_TABLE exists? IF EXISTS(??? query ???) THEN Thank you in advance Domenico ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Lifecycle of PostgreSQL releases
Josh/Jim- a ford truck will last just as long and you can get parts at any auto parts store in the US the question is do you STILL have the original air freshener? M- --- This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited. --- Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. - Original Message - From: Joshua D. Drake [EMAIL PROTECTED] To: Jim Nasby [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; Erik Jones [EMAIL PROTECTED]; CAJ CAJ [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Saturday, March 24, 2007 11:45 PM Subject: Re: [GENERAL] Lifecycle of PostgreSQL releases Jim Nasby wrote: On Mar 21, 2007, at 10:10 AM, Bruce Momjian wrote: As the owner of a 1986 Toyota Celica, I can accept the argument that a newer car with slightly brighter paint might not be worth the switch. Yup... that's why I drive a 1991 Acura. Of course, there's also the fact that the NSX will do 180MPH... ;) Yes but do you have air conditioned AND heated seats, to fit any occasion? ;) Joshua D. Drake Ironically, it's actually getting some new paint over the next 2 weeks while I'm in New Zealand. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tracking disk writes?
On Mar 24, 2007, at 1:06 PM, Jim Nasby wrote: On Mar 10, 2007, at 4:33 AM, Erik Jones wrote: I've seen that I can get the total number of blocks read from disk over the lifetime of a database via the pg_stat_database view, and by taking successive readings I can track reads over time. How can I track disk writes? Hey Erik... That's not currently tracked anywhere, but it would be good information to have. I suspect it could be added without a great deal of difficulty by looking at what's done for tracking blocks read, as well as another patch that added new statistics (I know there was one for vacuum stats back last summer). Also, someone on -hackers was recently looking into some stuff with the bgwriter and added some logging code; I don't remember if they also added some statistics. Right on the usefulness part!! Although, Tom's thoughts wrt to the bgwriter were that that would be the part that would make this non- trivial. erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r)
[GENERAL] Tsearch2 can't be checked for equality?
Hi all,
I'm trying to compare between 2 tsvector fields and I'm encountering a
problem, for example:
test.com= SELECT 1 WHERE to_tsvector('default', 'bh da') =
to_tsvector('default', 'bh da');
?column?
--
(0 rows)
There is another way that I can check for equality of 2 tsvector fields?
Thanks a lot in advance,
Ben-Nes Yonatan
[GENERAL] Matrix (linear algebra) operations and types in PG?
Hi all, This is just a random question/idea (I tried googling, but couldn't get an answer quickly): Has anyone written a library to effect linear algebra types and operations through Postgres? E.G., convert a float 2-d array to a matrix, convert a float 1-d array to a vector, multiply them, take the matrix power, calculate eigenvalues, some easy functions for converting tables to matrices, etc. This might be extremely useful to some of the data analysis folks out there--I would have used it a year ago on a project. It seems like a fairly straightforward project, which would consist primarily in hooking up the LAPACK libraries and defining some types and operations. Is there something tricky that I haven't foreseen? If I had the summer free, I would apply for a google summer of code project (hint to the world...). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Configure can't find com_err on OpenBSD for --with-krb5
AC_SEARCH_LIBS(com_err, [krb5 'krb5 -ldes -lasn1 -lroken' com_err], [], [AC_MSG_ERROR([could not find function 'com_err' required for Kerberos 5])]) AC_SEARCH_LIBS(krb5_sendauth, [krb5 'krb5 -ldes -lasn1 -lroken'], [], [AC_MSG_ERROR([could not find function 'krb5_sendauth' required for Kerberos 5])]) but I'm not sure why no one else would've complained before, if that were necessary... Where are we on this? A thousand apologies, I never posted back my results. I got it to build by hacking configure as per the attached patch. Notice I don't have roken -- don't know what that is. -lroken should probably be patched a second place; I only did it on the first one and it worked. Alas, I haven't actually had a chance to see if Kereberos authentication works, I only know this builds. -Thanks, Jim *** configure.dist Tue Feb 6 22:48:58 2007 --- configure Sun Mar 4 13:57:52 2007 *** *** 6535,6541 rm -f conftest.err conftest.$ac_objext \ conftest$ac_exeext conftest.$ac_ext if test $ac_cv_search_com_err = no; then ! for ac_lib in krb5 'krb5 -ldes -lasn1 -lroken' com_err; do LIBS=-l$ac_lib $ac_func_search_save_LIBS cat conftest.$ac_ext _ACEOF /* confdefs.h. */ --- 6535,6541 rm -f conftest.err conftest.$ac_objext \ conftest$ac_exeext conftest.$ac_ext if test $ac_cv_search_com_err = no; then ! for ac_lib in krb5 'krb5 -lcrypto -lasn1' com_err; do LIBS=-l$ac_lib $ac_func_search_save_LIBS cat conftest.$ac_ext _ACEOF /* confdefs.h. */ *** *** 6665,6671 rm -f conftest.err conftest.$ac_objext \ conftest$ac_exeext conftest.$ac_ext if test $ac_cv_search_krb5_sendauth = no; then ! for ac_lib in krb5 'krb5 -ldes -lasn1 -lroken'; do LIBS=-l$ac_lib $ac_func_search_save_LIBS cat conftest.$ac_ext _ACEOF /* confdefs.h. */ --- 6665,6671 rm -f conftest.err conftest.$ac_objext \ conftest$ac_exeext conftest.$ac_ext if test $ac_cv_search_krb5_sendauth = no; then ! for ac_lib in krb5 'krb5 -lcrypto -lasn1 -lroken'; do LIBS=-l$ac_lib $ac_func_search_save_LIBS cat conftest.$ac_ext _ACEOF /* confdefs.h. */ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Matrix (linear algebra) operations and types in PG?
Webb Sprague [EMAIL PROTECTED] schrieb: Hi all, This is just a random question/idea (I tried googling, but couldn't get an answer quickly): Has anyone written a library to effect linear algebra types and operations through Postgres? E.G., convert a float 2-d array to a matrix, convert a float 1-d array to a vector, multiply them, take the matrix power, calculate eigenvalues, some easy functions for converting tables to matrices, etc. This might be extremely useful to Take a look at pl/r, http://www.joeconway.com/plr/. This is a interface to R: http://www.r-project.org/ I'm not familiar with this, but i think, this may be helpful for you. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Matrix (linear algebra) operations and types in PG?
Hi all, Take a look at pl/r, http://www.joeconway.com/plr/. This is a interface to R: http://www.r-project.org/ I'm not familiar with this, but i think, this may be helpful for you. Shoot -- I should have said that I knew about plr -- supposedly a great project (maybe the reason there is no matrix type/operator in PG), but not what I am interested in, for reasons more to do with curiousity and the joy of hacking than with any pragmatic need. (Thanks, though, Andreas) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Check the existance of temporary table
dfx [EMAIL PROTECTED] writes: how I can check the existance of temporary table? I.e. wich query I have to use to know if MY_TEMP_TABLE exists? As of 8.2 you can do SELECT ... FROM pg_class WHERE relname = 'whatever' AND relnamespace = pg_my_temp_schema(); In earlier releases pg_my_temp_schema() isn't built in, so you have to do some pushups to determine which schema is your temp schema. 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
Re: [GENERAL] Matrix (linear algebra) operations and types in PG?
Webb Sprague wrote: Hi all, Take a look at pl/r, http://www.joeconway.com/plr/. This is a interface to R: http://www.r-project.org/ I'm not familiar with this, but i think, this may be helpful for you. Shoot -- I should have said that I knew about plr -- supposedly a great project (maybe the reason there is no matrix type/operator in PG), but not what I am interested in, for reasons more to do with curiousity and the joy of hacking than with any pragmatic need. (Thanks, though, Andreas) I've found myself recently wanting a lighter weight way to do certain advanced math operations along these lines. For example, I needed a quick and simple way to do N-order polynomial least squares fits. But I found LAPACK more complex than I really wanted. Looking around a bit I found http://freshmeat.net/projects/ccmath which unfortunately looks unmaintained and LGPL licensed, but has a nice simple API for doing curve fits. It also has all the matrix operations you're looking for. I've already used it to write a polynomial least squares fit custom aggregate for postgres, and was thinking to do something with the fft and power spectrum functions next. BTW, I tried to contact the author to see if he'd be willing to allow me to pull out just the stuff I need and release it under a BSD license along with my code, but he did not reply. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tsearch2 can't be checked for equality?
What version ?
in CVS HEAD I have
postgres=# select to_tsvector('english', 'bh da') = to_tsvector('english', 'bh
da');
?column?
--
t
(1 row)
I think, it should works in 8.2 also.
Oleg
On Sun, 25 Mar 2007, Yonatan Ben-Nes wrote:
Hi all,
I'm trying to compare between 2 tsvector fields and I'm encountering a
problem, for example:
test.com= SELECT 1 WHERE to_tsvector('default', 'bh da') =
to_tsvector('default', 'bh da');
?column?
--
(0 rows)
There is another way that I can check for equality of 2 tsvector fields?
Thanks a lot in advance,
Ben-Nes Yonatan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(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] Tsearch2 can't be checked for equality?
Oleg Bartunov oleg@sai.msu.su writes: What version ? ... I think, it should works in 8.2 also. A look at the CVS log suggests it should work in 8.1.5 and later. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2 can't be checked for equality?
Well I got version 8.1.4 here and no option for upgrade... never mind I'll
solve my problem in a different way.
Thanks a lot,
Ben-Nes Yonatan
On 3/25/07, Oleg Bartunov oleg@sai.msu.su wrote:
What version ?
in CVS HEAD I have
postgres=# select to_tsvector('english', 'bh da') = to_tsvector('english',
'bh da');
?column?
--
t
(1 row)
I think, it should works in 8.2 also.
Oleg
On Sun, 25 Mar 2007, Yonatan Ben-Nes wrote:
Hi all,
I'm trying to compare between 2 tsvector fields and I'm encountering a
problem, for example:
test.com= SELECT 1 WHERE to_tsvector('default', 'bh da') =
to_tsvector('default', 'bh da');
?column?
--
(0 rows)
There is another way that I can check for equality of 2 tsvector fields?
Thanks a lot in advance,
Ben-Nes Yonatan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Re: [GENERAL] Check the existance of temporary table
In stored procedures I used something like BEGIN CREATE TEMPORARY TABLE tmp ... EXCEPTION WHEN ... THEN ... END See pg error codes for details (I don't remember exactly, but maybe it is a dumplicate_table or duplicate_object exception). On 3/25/07, Tom Lane [EMAIL PROTECTED] wrote: dfx [EMAIL PROTECTED] writes: how I can check the existance of temporary table? I.e. wich query I have to use to know if MY_TEMP_TABLE exists? As of 8.2 you can do SELECT ... FROM pg_class WHERE relname = 'whatever' AND relnamespace = pg_my_temp_schema(); In earlier releases pg_my_temp_schema() isn't built in, so you have to do some pushups to determine which schema is your temp schema. 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] Server memory and efficientcy
# - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; listen_addresses = '*' # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 200 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 superuser_reserved_connections = 4 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #bonjour_name = '' # defaults to the computer name # - Security Authentication - #authentication_timeout = 60 # 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = '' # empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - #shared_buffers = 2000 # min 16 or max_connections*2, 8KB each #shared_buffers = 31744 #shared_buffers = 8192 shared_buffers = 16384 #temp_buffers = 1000 # min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). #work_mem = 1024 # min 64, size in KB work_mem = 4096 #maintenance_work_mem = 16384 # min 1024, size in KB maintenance_work_mem = 131078 #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~70 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20 # 0-1 credits #vacuum_cost_limit = 200 # 0-1 credits # - Background writer - #bgwriter_delay = 200 # 10-1 milliseconds between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = on # turns forced synchronization on or off #wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync # fsync # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes #wal_buffers = 8 # min 4, 8KB each wal_buffers = 32 #commit_delay = 0 # range 0-10, in microseconds commit_delay = 20 #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_segments = 8 #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # in seconds, 0 is off # - Archiving - #archive_command = '' # command to use to archive a logfile # segment #--- # QUERY TUNING #--- # - Planner Method Configuration - #enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_mergejoin = on #enable_nestloop = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on # - Planner Cost Constants - #effective_cache_size = 1000 # typically 8KB each effective_cache_size = 307800 #effective_cache_size = 16 #random_page_cost = 4 # units are one sequential page fetch # cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) # - Genetic Query Optimizer - #geqo = on #geqo_threshold = 12 #geqo_effort = 5 # range 1-10 #geqo_pool_size = 0 # selects default based on effort #geqo_generations = 0 # selects default based on effort #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target = 10 # range 1-1000 #constraint_exclusion = off #from_collapse_limit = 8 #join_collapse_limit = 8 # 1
