[GENERAL] Drop Index and Create Index
Hi, I would like recreate all indexes on a database, the command reindex is exclusive lock on table so I prefer the method drop index and create index. Is there a script for extract the command for the create index? I see pg_dump but it extract all object definitions. I see the source of pgAdmin for extract the definition of index, so before to begin to develop of a shell script for extract the definition of all indexes, I ask to you if this script already exist. Thanks a lot for help Giovanni -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drop Index and Create Index
On Tue, Jul 22, 2008 at 1:39 AM, Giovanni Nervi [EMAIL PROTECTED] wrote: Hi, I would like recreate all indexes on a database, the command reindex is exclusive lock on table so I prefer the method drop index and create index. Is there a script for extract the command for the create index? I see pg_dump but it extract all object definitions. I see the source of pgAdmin for extract the definition of index, so before to begin to develop of a shell script for extract the definition of all indexes, I ask to you if this script already exist. start with select * from pg_indexes and go from there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using ISpell dictionary - headaches...
Hi everybody. Well... I have a problem when trying to install and use an ISpell dictionary (the Thai one to be more precise) with the tsearch feature. _What I am trying to do_ I have a table containing a title field, and I want to fill a vector field with the following command: *UPDATE thai_table SET vectors = to_tsvector('thai_utf8', coalesce(title,''));* _How I installed the Thai dictionary_ I installed the th_TH.dic and the th_TH.aff files (downloaded from http://wiki.services.openoffice.org/wiki/Dictionaries) in a /usr/local/share/dicts/ispell/ folder, and I executed the following commands: SET search_path = public; BEGIN; INSERT INTO pg_ts_dict (dict_name, dict_init, dict_initoption, dict_lexize, dict_comment) VALUES ( 'th_spell_utf8', 'spell_init(internal)', 'DictFile=/usr/local/share/dicts/ispell/th_TH.dic,AffFile=/usr/local/share/dicts/ispell/th_TH.aff', 'spell_lexize(internal,internal,integer)', 'Thai ISpell dict utf8 encoding' ); INSERT INTO pg_ts_cfg (ts_name, prs_name, locale) VALUES ('thai_utf8', 'default', 'th_TH.utf8'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'email', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'url', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'host', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'sfloat', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'version', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'uri', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'file', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'float', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'int', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'uint', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'lword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'nlword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'word', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'part_hword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'nlpart_hword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'lpart_hword', '{th_spell_utf8,simple}'); COMMIT; _What my problem is_ The problem is that, when i execute the request to fill my vectors field, psql crashes... la connexion au serveur a été coupée à l'improviste Le serveur s'est peut-être arrêté anormalement avant ou durant le traitement de la requête. La connexion au serveur a été perdue. Tentative de réinitialisation: Echec. ! (it means: the connection with the server has been cut unexpectedly. The server may have stop abnormaly before or during the request handling. The connection with the server has been lost. Trying to reinitialization: Failed) I have no idea on what may cause that, nor what I could look for to find idea on how to solve that. It *may* be because I'm using psql 8.0.3 and not the latest version (but I'm stucked with that version), i'm just hoping that one of you have met similar problem and have successfully solved it, or maybe if you know a site where an Ispell dictionary installation is detailed step by step so that I can check if I did something wrong somewhere... Many thanks for your attention, Daniel Chiaramello
[GENERAL] Is it possible to do some damage to database with SELECT query?
Hi, is it possible to make a SELECT query with some nasty follow up commands, which damages the database. Something like: SELECT *,(DROP DATABASE enterprise) AS roger FROM sales WHERE sales (UPDATE order SET order=1); I know this wont work, but is there some possibility to modify database with SELECT query? I'm developing an ERP where I would like to implement a statistical program where you can write your own SELECT queries. Best Regards, Teemu Juntunen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to do some damage to database with SELECT query?
am Tue, dem 22.07.2008, um 12:50:31 +0300 mailte Teemu Juntunen folgendes: Hi, First, don't hijack other threads! is it possible to make a SELECT query with some nasty follow up commands, which damages the database. Something like: SELECT *,(DROP DATABASE enterprise) AS roger FROM sales WHERE sales (UPDATE order SET order=1); I know this wont work, but is there some possibility to modify database with SELECT query? Sure, with sql-injection. There are a lot to read via google, for instance http://en.wikipedia.org/wiki/SQL_injection HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using ISpell dictionary - headaches...
Daniel, early versions of tsearch doesn't support directly OpenOffice dictionaries. Oleg On Tue, 22 Jul 2008, Daniel Chiaramello wrote: Hi everybody. Well... I have a problem when trying to install and use an ISpell dictionary (the Thai one to be more precise) with the tsearch feature. _What I am trying to do_ I have a table containing a title field, and I want to fill a vector field with the following command: *UPDATE thai_table SET vectors = to_tsvector('thai_utf8', coalesce(title,''));* _How I installed the Thai dictionary_ I installed the th_TH.dic and the th_TH.aff files (downloaded from http://wiki.services.openoffice.org/wiki/Dictionaries) in a /usr/local/share/dicts/ispell/ folder, and I executed the following commands: SET search_path = public; BEGIN; INSERT INTO pg_ts_dict (dict_name, dict_init, dict_initoption, dict_lexize, dict_comment) VALUES ( 'th_spell_utf8', 'spell_init(internal)', 'DictFile=/usr/local/share/dicts/ispell/th_TH.dic,AffFile=/usr/local/share/dicts/ispell/th_TH.aff', 'spell_lexize(internal,internal,integer)', 'Thai ISpell dict utf8 encoding' ); INSERT INTO pg_ts_cfg (ts_name, prs_name, locale) VALUES ('thai_utf8', 'default', 'th_TH.utf8'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'email', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'url', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'host', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'sfloat', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'version', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'uri', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'file', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'float', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'int', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'uint', '{simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'lword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'nlword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'word', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'part_hword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'nlpart_hword', '{th_spell_utf8,simple}'); INSERT INTO pg_ts_cfgmap (ts_name, tok_alias, dict_name) VALUES ('thai_utf8', 'lpart_hword', '{th_spell_utf8,simple}'); COMMIT; _What my problem is_ The problem is that, when i execute the request to fill my vectors field, psql crashes... la connexion au serveur a ?t? coup?e ? l'improviste Le serveur s'est peut-?tre arr?t? anormalement avant ou durant le traitement de la requ?te. La connexion au serveur a ?t? perdue. Tentative de r?initialisation: Echec. ! (it means: the connection with the server has been cut unexpectedly. The server may have stop abnormaly before or during the request handling. The connection with the server has been lost. Trying to reinitialization: Failed) I have no idea on what may cause that, nor what I could look for to find idea on how to solve that. It *may* be because I'm using psql 8.0.3 and not the latest version (but I'm stucked with that version), i'm just hoping that one of you have met similar problem and have successfully solved it, or maybe if you know a site where an Ispell dictionary installation is detailed step by step so that I can check if I did something wrong somewhere... Many thanks for your attention, Daniel Chiaramello Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ER diagram software
I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. Thanks. -- Brandon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
On Tue, 22 Jul 2008, Brandon Metcalf wrote: I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. I use dbwrench.com. It's not opensource, but is inexpensive and has trial period. Thanks. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
Brandon Metcalf, 22.07.2008 12:36: I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. Thanks. Try Power*Architect, so far the best open source solution I have seen. http://www.sqlpower.ca/page/architect Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
I have done some research recently and found on acceptable: - DBdesigner4, which is depreceated and replaced by MySQL workbench. Is OSS, but no linux version yet. Also has clunky pgsql support - Aqua data studio (www.aquafold.com). It's java app which I am using for some time already. Originally it had dual license, free for non-commercial use, but after looking at the site they changed it to more restrictive licensing. ER diagrams are acceptable quallity (but far from perfect). Additionally its not cheap anymore :-( Regards, Bohdan On Tue, Jul 22, 2008 at 12:36:39PM +0200, Brandon Metcalf wrote: I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. Thanks. -- Brandon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to do some damage to database with SELECT query?
In response to Teemu Juntunen [EMAIL PROTECTED]: Hi, is it possible to make a SELECT query with some nasty follow up commands, which damages the database. Something like: SELECT *,(DROP DATABASE enterprise) AS roger FROM sales WHERE sales (UPDATE order SET order=1); I know this wont work, but is there some possibility to modify database with SELECT query? I'm developing an ERP where I would like to implement a statistical program where you can write your own SELECT queries. Yes, it's easy to do with stored procedures. i.e.: SELECT drop_table('important_table') FROM some_other_table; This is managed with database permissions. Ensure that the user your dynamic query engine is connecting as does not have permissions to drop tables, or even delete rows from the tables (see the GRANT docs). By doing so, you ensure that even if someone can create a dangerous procedure and execute it via query, that they can't do any damage. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 8.3.3 MSVC2005 (full edition)
Hello. Have I an opportunity to get working dll using MSVC2005 (full edition) for compilation at all? I just tried to compile the following code. This is full text. Project options are below in the letter. - #include postgres.h #include fmgr.h #include executor/executor.h #include utils/timestamp.h #include utils/builtins.h #include utils/formatting.h #define BUILDING_DLL 1 #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif PG_FUNCTION_INFO_V1(add_one); Datum add_one(PG_FUNCTION_ARGS) { int32 arg = PG_GETARG_INT32(0); PG_RETURN_INT32(arg + 1); } --- CREATE FUNCTION service.add_one() RETURNS integer AS '$libdir/my_dll', 'pg_finfo_add_one' LANGUAGE C STRICT; Result: Query OK (0,11 sec) Return Value: 32593236 Even if I do the following: PG_FUNCTION_INFO_V1(add_one); Datum add_one(PG_FUNCTION_ARGS) { PG_RETURN_INT32(10); } I get the same result - Return Value: 32593236 Have I forget some important option? Or I can't use the FULL version for compilation at all - only express edition? By the way - there is a directory 'D:\pgsql83\include\server\port\win32_msvc'. But I can't use it instead of 'D:\pgsql83\include\server\port\win32' (should I ?) - there is an error due to lack of file 'netdb.h' and some others in this directory. Thanks in advance, Marina. -Original Message- From: el dorado [EMAIL PROTECTED] To: pgsql-general@postgresql.org Date: Fri, 18 Jul 2008 11:17:51 +0400 Subject: [GENERAL] C-procedure crashed in Postgres 8.3.3 when using 'text' variable (WinXP) - additional Hello. I'm trying to create a C-procedure returning text variable - again :). Postgres 8.3.3 (standard binaries - so built by means of MSVC), WinXP SP2. I also use MSVC 2005 for compilation my library. Configuration type - Dynamic Library (.dll) Additional include directories - D:\pgsql83\include;D:\pgsql83\include\server;D:\pgsql83\include\server\port\win32 Additional library directories - D:\pgsql83\lib Additional dependencies - postgres.lib Compile as C Code (/TC) Detect 64-bit portability issues - No So, here is the code: -- include postgres.h #include fmgr.h #include executor/executor.h #include utils/timestamp.h #include utils/builtins.h #include utils/formatting.h #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif #define GET_TEXT(cstrp) DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(cstrp))) PG_FUNCTION_INFO_V1(getTimeFromApplication); Datum getTimeFromApplication(PG_FUNCTION_ARGS) { PG_RETURN_TEXT_P(GET_TEXT(success)); } I can compile it and get the library, but there are some warnings: 1d:\pgsql83\getstring\c_getstring.c(10) : warning C4273: 'Pg_magic_func' : inconsistent dll linkage 1d:\pgsql83\getstring\c_getstring.c(10) : see previous definition of 'Pg_magic_func' 1d:\pgsql83\getstring\c_getstring.c(24) : warning C4273: 'pg_finfo_getTimeFromApplication' : inconsistent dll linkage 1d:\pgsql83\getstring\c_getstring.c(24) : see previous definition of 'pg_finfo_getTimeFromApplication' --- Then I put the library into 'lib' directory and create the stored procedure: CREATE OR REPLACE FUNCTION service.get_app_time () RETURNS text AS '$libdir/getstring', 'pg_finfo_getTimeFromApplication' LANGUAGE C STRICT; Then I try to run it: select * from service.get_app_time (); And get an error: ERROR: invalid memory alloc request size 4294967293 What did I wrong? Thanks in advance, Marina. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
b == [EMAIL PROTECTED] writes: b I've been able to find a couple of packages, but wondering if there is b a good system out there what will create an ER diagram of an existing b PostgreSQL DB. Open source would be nice. Thanks for all that have responded so far. I'm looking at Power*Architect which looks very cool and is Open Source. -- Brandon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it possible to do some damage to database with SELECT query?
On Tue, Jul 22, 2008 at 12:20:46PM +0200, A. Kretschmer wrote: is it possible to make a SELECT query with some nasty follow up commands, which damages the database. Something like: SELECT *,(DROP DATABASE enterprise) AS roger FROM sales WHERE sales (UPDATE order SET order=1); I know this wont work, but is there some possibility to modify database with SELECT query? Sure, with sql-injection. There are a lot to read via google, for instance http://en.wikipedia.org/wiki/SQL_injection That's why on important databases you'd configure them set default_transaction_read_only to on and only reverse that connect by connect when a writable connection is truly needed. That way injectors will not only have to hijack *any* connection but pick the right one, too. It also nicely keeps average users from destroying their data with admin tools like pgadmin etc. And then there's role based per-table permissions, of course. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems Restarting PostgreSQL Daemon
My server is rebooted infrequently, usually after a kernel upgrade and on very rare occasions when something causes it to hang. After rebooting I always have serious issues getting postgresql running again, even though the startup script is part of the boot sequence. Yesterday was one of those highly unusual hangs, and I cannot restart the service. I'd like to understand why. When I run the Slackware script, '/etc/rc.d/rc.postgresql start' (script attached), I'm shown a process ID and told the daemon is already running. For example: Starting PostgreSQL 15342 PostgreSQL daemon already running However, there is no process ID 15342, and no postgres running. I manually removed /tmp/.s.PGSQL.5432 and its log file. Also -- apparently in error -- the .pid file. Makes no difference. Perhaps there's an error in the script that I'm not seeing (I didn't write it). Regardless, if I learn why there's a problem I can fix the script and avoid this delay and hassle restarting postgres after the daemon's been shut down. TIA, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863#!/bin/bash # PostgreSQL startup script for Slackware Linux # Copyright 2007 Adis Nezirovic adis _at_ linux.org.ba # Licensed under GNU GPL v2 # Do not source this script (since it contains exit() calls) # Before you can run postgresql you'll need to create the # database files in /var/lib/pgsql. The following should do # the trick. # # $ su postgres -c initdb -D /var/lib/pgsql/data # LOGFILE=/var/log/postgresql DATADIR=/var/lib/pgsql/data POSTGRES=/usr/bin/postgres PIDFILE=postmaster.pid # Return values (according to LSB): # 0 - success # 1 - generic or unspecified error # 2 - invalid or excess argument(s) # 3 - unimplemented feature (e.g. reload) # 4 - insufficient privilege # 5 - program is not installed # 6 - program is not configured # 7 - program is not running pg_ctl() { CMD=/usr/bin/pg_ctl $@ su - postgres -c $CMD } if [ ! -f $POSTGRES ]; then echo Could not find 'postgres' binary. Maybe PostgreSQL is not installed properly? exit 5 fi case $1 in start) echo Starting PostgreSQL touch $LOGFILE chown postgres:wheel $LOGFILE chmod 0640 $LOGFILE if [ ! -e $DATADIR/PG_VERSION ]; then echo You should initialize the PostgreSQL database at location $DATADIR exit 6 fi if pgrep postgres; then echo PostgreSQL daemon already running if [ ! -f $DATADIR/$PIDFILE ]; then echo Warning: Missing pid file $DATADIR/$PIDFILE fi exit 1 else # remove old socket, if it exists and no daemon is running. if [ ! -f $DATADIR/$PIDFILE ]; then rm -f /tmp/.s.PGSQL.5432 rm -f /tmp/.s.PGSQL.5432.lock pg_ctl start -w -l $LOGFILE -D $DATADIR exit 0 else echo PostgreSQL daemon was not properly shut down echo Please remove stale pid file $DATADIR/$PIDFILE exit 7 fi fi ;; stop) echo Shutting down PostgreSQL... pg_ctl stop -l $LOGFILE -D $DATADIR -m smart ;; restart) echo Restarting PostgreSQL... pg_ctl restart -l $LOGFILE -D $DATADIR -m smart ;; reload) echo Reloading configuration for PostgreSQL... pg_ctl reload -l $LOGFILE -D $DATADIR -m smart ;; status) if pgrep postgres; then echo PostgreSQL is running if [ ! -e $DATADIR/$PIDFILE ]; then echo Warning: Missing pid file $DATADIR/$PIDFILE fi exit 0 else echo PostgreSQL is stopped if [ -e $DATADIR/$PIDFILE ]; then echo Detected stale pid file $DATADIR/$PIDFILE fi exit 0 fi ;; *) echo Usage: $0 {start|stop|status|restart|reload} exit 1 ;; esac -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
Rich Shepard [EMAIL PROTECTED] writes: My server is rebooted infrequently, usually after a kernel upgrade and on very rare occasions when something causes it to hang. After rebooting I always have serious issues getting postgresql running again, even though the startup script is part of the boot sequence. Yesterday was one of those highly unusual hangs, and I cannot restart the service. I'd like to understand why. When I run the Slackware script, '/etc/rc.d/rc.postgresql start' (script attached), I'm shown a process ID and told the daemon is already running. The short answer is probably don't use Slackware's startup script. Some distros have PG start scripts that have had the bugs beaten out of them, and others not so much. Perhaps there's an error in the script that I'm not seeing (I didn't write it). Regardless, if I learn why there's a problem I can fix the script and avoid this delay and hassle restarting postgres after the daemon's been shut down. Have you read the script to see what condition causes it to issue the mentioned error? I'd imagine that it's looking at some other lockfile than you think. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
On Tue, 22 Jul 2008 05:36:39 -0500 (CDT) Brandon Metcalf [EMAIL PROTECTED] wrote: I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. For reverse engineering models I've liked dbVisualizer (not OS, relatively cheap) http://www.minq.se/products/dbvis/ Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Did you actually give a bt command, or was that just the initial output from gdb? Yeah I used the bt command, which gave exactly the same output as the initial output. However you'll have to bear with me here, as I am new to gdb, so there is the possibility I'm just not doing things right at all. Another thing to try is looking around the current instruction pointer: x/i $pc x/32i $pc-32 I've just recompiled again after configuring with --enable-debug, and for completeness here's all the output from gdb: # gdb /usr/pgsql_src/postgresql-8.3.3/src/test/regress/tmp_check/install/usr/local/pgsql/bin/initdb core GNU gdb 6.4.90-debian Copyright (C) 2006 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as mipsel-linux-gnu...Using host libthread_db library /lib/libthread_db.so.1. warning: core file may not match specified executable file. Core was generated by `/usr/pgsql_src/postgresql-8.3.3/src/test/regress/tmp_check/install/usr/local/pg'. Program terminated with signal 10, Bus error. #0 0x007572d0 in ?? () (gdb) bt #0 0x007572d0 in ?? () warning: GDB can't find the start of the function at 0x7572d0. GDB is unable to find the start of the function at 0x7572d0 and thus can't determine the size of that function's stack frame. This means that GDB may be unable to access that stack frame, or the frames below it. This problem is most likely caused by an invalid program counter or stack pointer. However, if you think GDB should simply search farther back from 0x7572d0 for code which looks like the beginning of a function, you can increase the range of the search using the `set heuristic-fence-post' command. #1 0x007572d0 in ?? () warning: GDB can't find the start of the function at 0x7572d0. Previous frame identical to this frame (corrupt stack?) (gdb) set heuristic-fence-post 1000 (gdb) bt #0 0x007572d0 in ?? () warning: GDB can't find the start of the function at 0x7572d0. #1 0x007572d0 in ?? () warning: GDB can't find the start of the function at 0x7572d0. Previous frame identical to this frame (corrupt stack?) (gdb) x/i $pc 0x7572d0: Cannot access memory at address 0x7572d0 (gdb) x/32i $pc 0x7572d0: Cannot access memory at address 0x7572d0 (gdb) __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: I've just recompiled again after configuring with --enable-debug, and for completeness here's all the output from gdb: # gdb /usr/pgsql_src/postgresql-8.3.3/src/test/regress/tmp_check/install/usr/local/pgsql/bin/initdb core Well, there's part of your problem: the program that is crashing is not initdb. Specify the postgres executable, instead. Note the warning: core file may not match specified executable file. Core was generated by `/usr/pgsql_src/postgresql-8.3.3/src/test/regress/tmp_check/install/usr/local/pg'. though this is evidently getting confused by the overly long path, so you might still see the warning even after picking the right executable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
On Tue, Jul 22, 2008 at 5:36 AM, Brandon Metcalf [EMAIL PROTECTED] wrote: I've been able to find a couple of packages, but wondering if there is a good system out there what will create an ER diagram of an existing PostgreSQL DB. Open source would be nice. PostgreSQL Autodoc: http://www.rbt.ca/autodoc/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Substitute a variable in PL/PGSQL.
Hi, I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. How do you substitute a variable? Test case: CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text); INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'); INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'); INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10'); CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE ted varchar; bob RECORD; BEGIN FOR bob IN SELECT * FROM test LOOP FOR i IN 1..10 LOOP ted := 'bob.col' || i; RETURN NEXT ted; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; test= select * from testfunc(); testfunc --- bob.col1 bob.col2 bob.col3 bob.col4 bob.col5 bob.col6 bob.col7 bob.col8 bob.col9 bob.col10 bob.col1 bob.col2 bob.col3 bob.col4 bob.col5 bob.col6 bob.col7 bob.col8 bob.col9 bob.col10 bob.col1 bob.col2 bob.col3 bob.col4 bob.col5 bob.col6 bob.col7 bob.col8 bob.col9 bob.col10 (30 rows) test= Or: CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE bob RECORD; ted TEXT; BEGIN FOR i IN 1..10 LOOP ted := 'col' || i; FOR bob IN SELECT ted FROM test LOOP RETURN NEXT bob; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; test= select * from testfunc(); testfunc -- (col1) (col1) (col1) (col2) (col2) (col2) (col3) (col3) (col3) (col4) (col4) (col4) (col5) (col5) (col5) (col6) (col6) (col6) (col7) (col7) (col7) (col8) (col8) (col8) (col9) (col9) (col9) (col10) (col10) (col10) (30 rows) test= Or is there another way other than using another procedural language. Thanks - Steve M. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Well, there's part of your problem: the program that is crashing is not initdb. Specify the postgres executable, instead. Note the warning: core file may not match specified executable file. Core was generated by `/usr/pgsql_src/postgresql-8.3.3/src/test/regress/tmp_check/install/usr/local/pg'. Ok, that helps a bit and the output now looks a little more useful: Program terminated with signal 10, Bus error. #0 InitializeGUCOptions () at guc.c:3133 3133if (new_limit 100) (gdb) bt #0 InitializeGUCOptions () at guc.c:3133 #1 0x004a1ec8 in AuxiliaryProcessMain (argc=4, argv=value optimized out) at bootstrap.c:230 #2 0x005979a8 in main (argc=4, argv=value optimized out) at main.c:147 (gdb) So, guc.c:3133 is doing something to try and set stack depth And I've no idea what I'm looking for unfortunately. And, the instruction pointer info: (gdb) x/i $pc 0x7572d0 InitializeGUCOptions+648: beqzv0,0x75748c InitializeGUCOptions+1092 (gdb) x/32i $pc-32 0x7572b0 InitializeGUCOptions+616: blezv0,0x7572d8 InitializeGUCOptions+656 0x7572b4 InitializeGUCOptions+620:movev1,v0 0x7572b8 InitializeGUCOptions+624:lui v0,0xfff8 0x7572bc InitializeGUCOptions+628:adduv0,v1,v0 0x7572c0 InitializeGUCOptions+632: bltzv0,0x75753c InitializeGUCOptions+1268 0x7572c4 InitializeGUCOptions+636:nop 0x7572c8 InitializeGUCOptions+640:sra a2,v0,0xa 0x7572cc InitializeGUCOptions+644:sltiv0,a2,101 0x7572d0 InitializeGUCOptions+648: beqzv0,0x75748c InitializeGUCOptions+1092 0x7572d4 InitializeGUCOptions+652:sltiv0,a2,2049 0x7572d8 InitializeGUCOptions+656:lw ra,84(sp) 0x7572dc InitializeGUCOptions+660:lw s8,80(sp) 0x7572e0 InitializeGUCOptions+664:lw s7,76(sp) 0x7572e4 InitializeGUCOptions+668:lw s6,72(sp) 0x7572e8 InitializeGUCOptions+672:lw s5,68(sp) 0x7572ec InitializeGUCOptions+676:lw s4,64(sp) 0x7572f0 InitializeGUCOptions+680:lw s3,60(sp) 0x7572f4 InitializeGUCOptions+684:lw s2,56(sp) 0x7572f8 InitializeGUCOptions+688:lw s1,52(sp) 0x7572fc InitializeGUCOptions+692:lw s0,48(sp) ---Type return to continue, or q return to quit--- 0x757300 InitializeGUCOptions+696:jr ra 0x757304 InitializeGUCOptions+700:addiu sp,sp,88 0x757308 InitializeGUCOptions+704:lw t9,60(s0) 0x75730c InitializeGUCOptions+708:nop 0x757310 InitializeGUCOptions+712: beqzt9,0x757330 InitializeGUCOptions+744 0x757314 InitializeGUCOptions+716:li a1,1 0x757318 InitializeGUCOptions+720:lw a0,48(s0) 0x75731c InitializeGUCOptions+724:jalrt9 0x757320 InitializeGUCOptions+728:movea2,zero 0x757324 InitializeGUCOptions+732:lw gp,24(sp) 0x757328 InitializeGUCOptions+736: beqzv0,0x757454 InitializeGUCOptions+1036 0x75732c InitializeGUCOptions+740:addiu a0,s6,-8968 (gdb) though this is evidently getting confused by the overly long path, so you might still see the warning even after picking the right executable. I thought the same, but was hoping it's just a display problem. __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove duplicate lines but save one of the lines?
minor refinement on suggestion: -- CTAS (create table as) is easiest way to create table with same structure create table foo as select * from orig_table; -- truncate is much more efficient than delete truncate orig_table; -- unchanged insert into orig_table select * from foo; -- recompute statistics analyze orig_table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A B Sent: Monday, July 21, 2008 11:51 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to remove duplicate lines but save one of the lines? There is probably a more elegant way of doing it, but a simple way of doing it ( depending on the size of the table ) could be: begin; insert into foo select distinct * from orig_table; delete from orig_table; insert into orig_table select * from foo; commit; Just to make it clear to me Here foo is a table that I have to create with the command CREATE TABLE foo (same columns as orig_table); ? Is it possible to add a unique constraint to the table, with a delete option so it will delete duplicates? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.3 MSVC2005 (full edition)
el dorado wrote: Have I forget some important option? Or I can't use the FULL version for compilation at all - only express edition? They use the same compiler, and will produce compatible object code, so the full or express editions will be fine. By the way - there is a directory 'D:\pgsql83\include\server\port\win32_msvc'. But I can't use it instead of 'D:\pgsql83\include\server\port\win32' (should I ?) - there is an error due to lack of file 'netdb.h' and some others in this directory. That probably won't work well. IIRC it is necessary to put the win32_msvc directory first in the header search path, followed by the win32 directory. From memory I had to create a dummy header for libintl as well - it's not really needed for basic plugins, but is required for compilation of the Pg headers. That dummy header should be floating around in the mailing list archives. There were very similar issues discussed some months ago, so I strongly recommend that you search the archives. On a side note, personally I think the `port/win32' subdir should be renamed to `port/win32_mingw' and any truly shared files should be put in `port/win32' ... but that's just me. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On Tue, 22 Jul 2008, Tom Lane wrote: The short answer is probably don't use Slackware's startup script. Some distros have PG start scripts that have had the bugs beaten out of them, and others not so much. Excellent advice, Tom. I'll take it. Have you read the script to see what condition causes it to issue the mentioned error? I'd imagine that it's looking at some other lockfile than you think. I tried following the logic, and it appears the issue now is 'invalid data in PID file /var/lib/pgsql/data/postmaster.pid '. If I delete that file, is it automatically recreated? I'm using /usr/bin/pg_ctl as user postgres. Thanks, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using ISpell dictionary - headaches...
It *may* be because I'm using psql 8.0.3 and not the latest version (but I'm stucked with that version), i'm just hoping that one of you have met Upgrade to 8.0.17 - there was a several fixes in ISpell code. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text index without accents
And which are the types of argument and returning values of a pl/sql function which preprocess de text? I have been searching that, for example, something like this works fine: CREATE INDEX textindex ON document USING gin(to_tsvector('english',upper(text))); where text is the text column of document. But I have tried to do something like: CREATE INDEX textindex ON document USING gin(to_tsvector('english',myfunction(text))); where myfunction is a PL/SQL function which call upper one, but I didn't find which are the types of the myfunction argument and returning value. I am a PL/SQL novice and I didn't find how to do it yet. Of course, then I will have to change upper experiment to my objective: to index without accents. I don't know if PL/SQL is the better option to build such function. Thanks, Mario Barcala You can preprocess text (replace accent by nothing) before to_tsvector or to_tsquery Oleg On Thu, 3 Jul 2008, [EMAIL PROTECTED] wrote: Hi again: I am trying to create a full text configuration to ignore word accents in my searches. My approach is similar to simple dicionary one, but i want to remove accents after converting to lower. Is it the only way to do it to develop another .c and write my own dict_noaccent.c, and then compile and install it into the system? Regars, Mario Barcala Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
The only thought that comes to mind is that the branch is being attempted but there's garbage at InitializeGUCOptions+1092 ? Try x/32i InitializeGUCOptions+1092 What ulimit settings are operative anyway? (ulimit -a might tell you) (gdb) x/32i InitializeGUCOptions+1092 0x75748c InitializeGUCOptions+1092: bnezv0,0x757498 InitializeGUCOptions+1104 0x757490 InitializeGUCOptions+1096: nop 0x757494 InitializeGUCOptions+1100: li a2,2048 0x757498 InitializeGUCOptions+1104: lw a1,-32720(gp) 0x75749c InitializeGUCOptions+1108: lw t9,-30852(gp) 0x7574a0 InitializeGUCOptions+1112: addiu s0,sp,32 0x7574a4 InitializeGUCOptions+1116: addiu a1,a1,-11352 0x7574a8 InitializeGUCOptions+1120: jalrt9 0x7574ac InitializeGUCOptions+1124: movea0,s0 0x7574b0 InitializeGUCOptions+1128: lw gp,24(sp) 0x7574b4 InitializeGUCOptions+1132: movea1,s0 0x7574b8 InitializeGUCOptions+1136: lw a0,-32720(gp) 0x7574bc InitializeGUCOptions+1140: lw t9,-13328(gp) 0x7574c0 InitializeGUCOptions+1144: addiu a0,a0,-6048 0x7574c4 InitializeGUCOptions+1148: li a2,1 0x7574c8 InitializeGUCOptions+1152: jalrt9 0x7574cc InitializeGUCOptions+1156: li a3,1 0x7574d0 InitializeGUCOptions+1160: lw gp,24(sp) 0x7574d4 InitializeGUCOptions+1164: b 0x7572d8 InitializeGUCOptions+656 0x7574d8 InitializeGUCOptions+1168: nop 0x7574dc InitializeGUCOptions+1172: lw t9,-26816(gp) ---Type return to continue, or q return to quit--- 0x7574e0 InitializeGUCOptions+1176: li a1,3042 0x7574e4 InitializeGUCOptions+1180: jalrt9 0x7574e8 InitializeGUCOptions+1184: addiu a2,s5,-9132 0x7574ec InitializeGUCOptions+1188: lw gp,24(sp) 0x7574f0 InitializeGUCOptions+1192: lwc1$f0,48(s0) 0x7574f4 InitializeGUCOptions+1196: lw a1,-32720(gp) 0x7574f8 InitializeGUCOptions+1200: lwc1$f1,52(s0) 0x7574fc InitializeGUCOptions+1204: lw a2,0(s0) 0x757500 InitializeGUCOptions+1208: lw t9,-27888(gp) 0x757504 InitializeGUCOptions+1212: swc1$f0,16(sp) 0x757508 InitializeGUCOptions+1216: swc1$f1,20(sp) (gdb) q deb:/usr/pgsql_src/postgresql-8.3.3/src/test/regress# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited max nice(-e) 0 file size (blocks, -f) unlimited pending signals (-i) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) unlimited max rt priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) unlimited virtual memory (kbytes, -v) unlimited file locks (-x) unlimited __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
The stack size rlimit looks normal, which makes a crash in this spot look even less probable. I think maybe you are looking at a stale corefile that doesn't quite correspond to this postgres executable. You are correct. I just checked and the core file was created on the 18th, that must be from the first attempt to run make check. I just assumed that the next time I attempted to run make check it'd be overwriting it, and that's obviously not the case. I'll try and get it to generate a fresh file. __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
Rich Shepard [EMAIL PROTECTED] writes: I tried following the logic, and it appears the issue now is 'invalid data in PID file /var/lib/pgsql/data/postmaster.pid '. If I delete that file, is it automatically recreated? I'm using /usr/bin/pg_ctl as user postgres. If you're certain there's no postmaster running, it's safe to remove postmaster.pid. However you really shouldn't have to; the postmaster is generally able to figure out whether a pidfile is live or not. The invalid data bit is interesting though. It looks like pg_ctl would produce that error if the pidfile exists but is empty when it looks. This seems like a race condition hazard, though the odds of hitting it are tiny. What's in the file exactly? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: What ulimit settings are operative anyway? (ulimit -a might tell you) deb:/usr/pgsql_src/postgresql-8.3.3/src/test/regress# ulimit -a core file size (blocks, -c) 0 Hmm, are you sure the core actually corresponds to your failure? Because this says you've got core dumps turned off. stack size (kbytes, -s) 8192 The stack size rlimit looks normal, which makes a crash in this spot look even less probable. I think maybe you are looking at a stale corefile that doesn't quite correspond to this postgres executable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On Tue, 22 Jul 2008, Tom Lane wrote: If you're certain there's no postmaster running, it's safe to remove postmaster.pid. However you really shouldn't have to; the postmaster is generally able to figure out whether a pidfile is live or not. Tom, I thought the postmaster knew what was current and what needed to be replaced, but the process ID in the pidfile did not exist. The invalid data bit is interesting though. It looks like pg_ctl would produce that error if the pidfile exists but is empty when it looks. This seems like a race condition hazard, though the odds of hitting it are tiny. What's in the file exactly? I deleted the .pid, but still could not get the postmaster running. Then I 'touched' the name so I had an empty file. Made no difference. While pg_ctl tells me the server is starting, there is no /tmp/.s.PGSQL*, no pidfile, and no postmaster process. In the past I've managed to start the postmaster daemon manually, but today I seem to have it FUBARed. Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
I tried following the logic, and it appears the issue now is 'invalid data in PID file /var/lib/pgsql/data/postmaster.pid '. If I delete that file, is it automatically recreated? Why not just move it and rename it? If it's recreated, great; if not, you still have the corrupted file on hand to try to fix, no? On Tue, Jul 22, 2008 at 11:15 AM, Rich Shepard [EMAIL PROTECTED] wrote: On Tue, 22 Jul 2008, Tom Lane wrote: The short answer is probably don't use Slackware's startup script. Some distros have PG start scripts that have had the bugs beaten out of them, and others not so much. Excellent advice, Tom. I'll take it. Have you read the script to see what condition causes it to issue the mentioned error? I'd imagine that it's looking at some other lockfile than you think. I tried following the logic, and it appears the issue now is 'invalid data in PID file /var/lib/pgsql/data/postmaster.pid '. If I delete that file, is it automatically recreated? I'm using /usr/bin/pg_ctl as user postgres. Thanks, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimizing a like-cause
Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? Thanks for your Help, Stefan Sturm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: And, the instruction pointer info: (gdb) x/i $pc 0x7572d0 InitializeGUCOptions+648: beqzv0,0x75748c InitializeGUCOptions+1092 Huh. The pc could possibly be a bit off from reality in this type of error, but none of the instructions immediately around it look like they could be making a bogus memory access either. The only thought that comes to mind is that the branch is being attempted but there's garbage at InitializeGUCOptions+1092 ? Try x/32i InitializeGUCOptions+1092 What ulimit settings are operative anyway? (ulimit -a might tell you) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_query transaction: auto rollback? begin or start?? commit or end???
...resending, email didn't go through. On Tue, Jul 22, 2008 at 1:02 PM, Bill Wordsworth [EMAIL PROTECTED] wrote: Is this recommended? pg_query(begin transaction read write;, $connection); if(pg_transaction_status($connection) == 2) { pg_query(insert...;, $connection); pg_query(insert...;, $connection); pg_query(insert...;, $connection); } pg_query(commit transaction;, $connection); pg_close($connection); Now *any* error inside transaction will trigger auto rollback for *all* inserts so I don't need to explicitly issue conditional rollback? Also is begin/commit transaction == start/end transaction?? Cheers, Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 23/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: When I run the Slackware script, '/etc/rc.d/rc.postgresql start' (script attached), I'm shown a process ID and told the daemon is already running. For example: Since there are no official Slackware postgres packages I'd like to ask where that script came from :) and how you installed postges in the first place. Happy to communicate of the list if you prefer that. TIA, Rich Cheers, Andrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ER diagram software
On Tue, 22 Jul 2008, Brandon Metcalf wrote: Thanks for all that have responded so far. I'm looking at Power*Architect which looks very cool and is Open Source. I looked in vain at this very question recently. Power*Architect was nice, but didn't seem to be able to display updates to the diagram after it was initially sucked out of Postgres. It can sorta-kinda diff things, but that's it. Also, I thought it's ER Diagram quality was... not so hot. Also nice was DbVisualizer. (http://www.dbvis.com/products/dbvis/) In many ways it seemed better than Power*Architect, but when it came time to suck up changes from the database since the ER diagram was created, the only way DbVisualizer can do it is by automatically (and randomly) laying out the entire schema again. rant Surely there is somebody out there other than me who just wants to display an ER diagram of a database, and not control the database schema through that same tool? All the tools I've found seem to do, at best, an acceptable job of laying out something that already exists, and let you hand-tweak it to be better but if you want to pick up changes you've made to the database via some out-of-band scripts, well, sux 2 b u. /rant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_query transaction: auto rollback? begin or start?? commit or end???
Is this recommended? pg_query(begin transaction read write;, $connection); if(pg_transaction_status($connection) == 2) { pg_query(insert...;, $connection); pg_query(insert...;, $connection); pg_query(insert...;, $connection); } pg_query(commit transaction;, $connection); pg_close($connection); Now *any* error inside transaction will trigger auto rollback for *all* inserts so I don't need to explicitly issue conditional rollback? Also is begin/commit transaction == start/end transaction?? Cheers, Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing a like-cause
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Sturm Sent: Tuesday, July 22, 2008 11:31 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Optimizing a like-cause Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? If you are searching for words, you could use tsearch2. If you are searching for arbitrary fragments, an idea like this might prove helpful: http://kaiv.wordpress.com/2007/12/11/postgresql-substring-search/ What you are asking for is very difficult, because an ordinary index won't help (you have a wildcard on the front) and an index on the reversed word won't help either (you have a wildcard on the back). So the standard sort of techniques used to solve it are not perfectly on target. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing a like-cause
On Tuesday 22 July 2008, Stefan Sturm [EMAIL PROTECTED] wrote: Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? No index can be used for that query (where it starts with %). -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing a like-cause
Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? The open-ended search is what's killing you. Can you change your query to be like this? select * from _table_ where _field_ like 'STRING%'; That allows the database to use an index. You'll still have to either store the data already in upper-case format, or use a functional index on upper(field). http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
Rich Shepard [EMAIL PROTECTED] writes: On Tue, 22 Jul 2008, Tom Lane wrote: The invalid data bit is interesting though. It looks like pg_ctl would produce that error if the pidfile exists but is empty when it looks. This seems like a race condition hazard, though the odds of hitting it are tiny. What's in the file exactly? I deleted the .pid, but still could not get the postmaster running. Then I 'touched' the name so I had an empty file. Made no difference. While pg_ctl tells me the server is starting, there is no /tmp/.s.PGSQL*, no pidfile, and no postmaster process. Sounds to me like the postmaster tries to start and fails. Look into the postmaster log. (If the log is going to /dev/null, send it someplace else...) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On Wed, 23 Jul 2008, Andrej Ricnik-Bay wrote: Since there are no official Slackware postgres packages I'd like to ask where that script came from :) and how you installed postges in the first place. Happy to communicate of the list if you prefer that. Andrej, Unless others consider this topic to be not appropriate for the list, I don't mind a public conversation. I thought that I attached the script to my original message; regardless, here's the attribution: # PostgreSQL startup script for Slackware Linux # Copyright 2007 Adis Nezirovic adis _at_ linux.org.ba # Licensed under GNU GPL v2 I upgraded postgres manually, not creating and using a Slackware package. It worked just fine until yesterday's reboot. Thanks, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing a like-cause
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dann Corbit Sent: Tuesday, July 22, 2008 1:30 PM To: Stefan Sturm; pgsql-general@postgresql.org Subject: Re: [GENERAL] Optimizing a like-cause -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Sturm Sent: Tuesday, July 22, 2008 11:31 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Optimizing a like-cause Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? If you are searching for words, you could use tsearch2. If you are searching for arbitrary fragments, an idea like this might prove helpful: http://kaiv.wordpress.com/2007/12/11/postgresql-substring-search/ What you are asking for is very difficult, because an ordinary index won't help (you have a wildcard on the front) and an index on the reversed word won't help either (you have a wildcard on the back). So the standard sort of techniques used to solve it are not perfectly on target. Second idea: It seems to me that you might also store strings as arrays of characters, create a GIN index, and then use the contains operators: @ contains @ is contained by I did not try it myself, but it seems it could be helpful. I think it would also return anagrams of STRING, but you would filter those with the original where clause restriction. It's hardly ideal, as these seem to qualify under %STRING% using the GIN idea: gi n r ts gi n rst gi n rt s gi n srt gi n str gi n tr s gi n trs gi n tsr gi nrst gi nrt s gi nrts gi ns rt gi ns tr gi nst r gi nstr gi ntr s gi ntrs gi nts r gi rnt s gi rtn s gi rtns gi snt r gi stn r gi strn gi tnr s gi tns r gi trn s gi tsn r gins rt gins tr gint r s gints r girn ts girt n s girt ns girts n gist n r git n r s git nrs git ns r git nsr git rns git rsn git snr git srn gitn r s gitr n s gitr ns gits n r gnir ts gnirts gnis rt gnis tr gnits r gnr i ts gnr ist gnr its gnr sit gnr sti gnr tis gnr tsi gnt i r s gnt isr gnt ri s gnt rsi gnt sri gri n ts gri nst gri nts gri snt gri stn gri tns gri tsn grin ts grinst grist n grits n grn i ts grn ist grn its grn sit grn sti grn tis grn tsi grnt i s grs int grs itn grs nit grs nti grs tni grt isn grt n i s grt ni s grt nis grt ns i grt nsi grt sni gs int r gs intr gs itn r gs n i rt gs n i tr gs n irt gs n itr gs n rit gs n rti gs n tir gs n tri gs ni rt gs ni tr gs nit r gs nrt i gs nti r gs ntr i gs rint gs rnt i gs rtin gs rtn i gs tni r gs tnr i gs trn i gsi n rt gsi n tr gsi nrt gsi ntr gsi rnt gsi rtn gsi tnr gsi trn gsin rt gsin tr gsn i rt gsn i tr gsn irt gsn itr gsn rit gsn rti gsn tir gsn tri gst inr gst irn gst n i r gst n ri gst ni r gst nir gst nri gst rin gst rni gsti n r gt inr s gt inrs gt insr gt irn s gt isn r gt n isr gt n ri s gt n rsi gt n sri gt ni r s gt nir s gt nirs gt nis r gt nri s gt nris gt nrs i gt ns i r gt ns ri gt nsi r gt nsr i gt rin s gt rins gt rni s gt rnis gt rns i gt rsin gt rsn i gt sni r gt snir gt snr i gt srin gt srn i gtin r s gtis n r gtn i r s gtn isr gtn ri s gtn rsi gtn sri gtr isn gtr n i s gtr ni s gtr nis gtr ns i gtr nsi gtr sni gtri n s gtri ns gtrs n i gtrs ni gtsi n r ign r ts ign rst ign rt s ign srt ign str ign tr s ign trs ign tsr igr n ts igr nst igr nts igr snt igr stn igr tns igr tsn igs n rt igs n tr igs nrt igs ntr igs rnt igs rtn igs tnr igs trn igst n r igt n r s igt nrs igt ns r igt nsr igt rns igt rsn igt snr igt srn ing r ts ing rst ing rt s ing srt ing str ing tr s ing trs ing tsr ingr ts ings rt ings tr instrg intg r s intgr s irng ts isg n rt isg n tr isg nrt isg ntr isg rnt isg rtn isg tnr isg trn istg n r itg n r s itg nrs itg ns r itg nsr itg rns itg rsn itg snr itg srn itnsg r ngi r ts ngi rst ngi rt s ngi srt ngi str ngi tr s ngi trs ngi tsr ngis rt ngis tr ngit r s ngr i ts ngr ist ngr its ngr sit ngr sti ngr tis ngr tsi ngs i rt ngs i tr ngs irt ngs itr ngs rit ngs rti ngs tir ngs tri ngt i r s ngt isr ngt ri s ngt rsi ngt sri nig r ts nig rst nig rt s nig srt nig str nig tr s nig trs nig tsr nigs rt nigs tr nirg ts nrg i ts nrg ist nrg its nrg sit nrg sti nrg tis nrg tsi nsg i rt nsg i tr nsg irt nsg itr nsg rit nsg rti nsg tir nsg tri nsig rt nsig tr nstig r ntg i r s ntg isr ntg ri s ntg rsi ntg sri rg inst rg int s rg isnt rg itn s rg n i ts rg n ist rg n its rg n sit rg n sti rg n tis rg n tsi rg ni ts rg nist rg nit s rg nits rg nsit rg nst i rg nsti rg nti s rg ntis rg nts i rg sint rg sitn rg snit rg snt i rg stin rg stn i rg tins rg tisn rg tni s rg tnis rg tns i rg tsn i rgi n ts rgi nst rgi nts rgi snt rgi stn rgi tns rgi tsn rgn i ts rgn ist rgn its rgn sit rgn sti rgn tis rgn tsi rgs int rgs itn rgs nit rgs nti rgs tni rgt isn rgt n i s rgt ni s rgt nis rgt ns i rgt nsi
Re: [GENERAL] Optimizing a like-cause
Dann Corbit [EMAIL PROTECTED] writes: I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What you are asking for is very difficult, because an ordinary index won't help (you have a wildcard on the front) and an index on the reversed word won't help either (you have a wildcard on the back). Actually ... if the usage is autocompletion, why in the world is the OP searching for '%STRING%' and not 'STRING%'? If I type barTAB, I certainly don't expect to be offered foobar as one of the possible completions. This makes a difference since an anchored-left pattern *can* be searched for using a standard index ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
On 12:33 am 07/22/08 Steve Martin [EMAIL PROTECTED] wrote: Hi, I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. Are you trying to do a generic function that would work for any table or for just a single table? Is it goint to run against a large data set? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimizing a like-cause
We have contrib/wildspeed extension which uses new partial match feature of GIN index. See our presentation http://www.pgcon.org/2008/schedule/events/58.en.html It index all permutations, so index is very big, but for not long read-only sstring it works fast. Oleg On Tue, 22 Jul 2008, Stefan Sturm wrote: Hello, I'm developing a autocomplete Feature using php and PostgreSQL 8.3. To fill the autocomplete box I use the following SQL Statement: select * from _table_ where upper( _field_ ) like '%STRING%'; This SQL Statement takes 900 ms on a Table with 300.000 entries. What can I do to speed up the Statement? What Index can I set? Thanks for your Help, Stefan Sturm Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY between 7.4.x and 8.3.x
On 6:01 pm 07/21/08 Jack Orenstein [EMAIL PROTECTED] wrote: to this: psql -h $SOURCE_HOST ... -c copy binary $SOURCE_SCHEMA.$SOURCE_T ABLE to stdout |\ psql ... -c copy binary $TARGET_SCHEMA.$TARGET_TABLE from stdin http://www.postgresql.org/docs/8.3/interactive/sql-copy.html The BINARY key word causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the normal text mode, but a binary-format file is less portable across machine architectures and PostgreSQL versions. I would suggest to not go that route. However, you could just test it and see if it works. If you are doing multiple tables I still think you should consider pg_dump -Fc. You can restore just the data without the DDL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text index without accents
Here is an example CREATE FUNCTION dropatsymbol(text) RETURNS text AS 'select replace($1, ''@'', '' '');' LANGUAGE SQL; arxiv=# select to_tsvector('english',dropatsymbol('[EMAIL PROTECTED]')); to_tsvector - 'oleg':1 'sai.msu.su':2 On Tue, 22 Jul 2008, Fco. Mario Barcala Rodr?guez wrote: And which are the types of argument and returning values of a pl/sql function which preprocess de text? I have been searching that, for example, something like this works fine: CREATE INDEX textindex ON document USING gin(to_tsvector('english',upper(text))); where text is the text column of document. But I have tried to do something like: CREATE INDEX textindex ON document USING gin(to_tsvector('english',myfunction(text))); where myfunction is a PL/SQL function which call upper one, but I didn't find which are the types of the myfunction argument and returning value. I am a PL/SQL novice and I didn't find how to do it yet. Of course, then I will have to change upper experiment to my objective: to index without accents. I don't know if PL/SQL is the better option to build such function. Thanks, Mario Barcala You can preprocess text (replace accent by nothing) before to_tsvector or to_tsquery Oleg On Thu, 3 Jul 2008, [EMAIL PROTECTED] wrote: Hi again: I am trying to create a full text configuration to ignore word accents in my searches. My approach is similar to simple dicionary one, but i want to remove accents after converting to lower. Is it the only way to do it to develop another .c and write my own dict_noaccent.c, and then compile and install it into the system? Regars, Mario Barcala Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On 23/07/2008, Rich Shepard [EMAIL PROTECTED] wrote: Andrej, Hi Rich, Unless others consider this topic to be not appropriate for the list, I don't mind a public conversation. I thought that I attached the script to my original message; regardless, here's the attribution: You did - my bad. I usually ignore attachments on mailing-lists, and did so with yours. I upgraded postgres manually, not creating and using a Slackware package. It worked just fine until yesterday's reboot. Now there's an interesting piece of information :) How long ago did you upgrade it? From which version of pg to which version did you upgrade, and how did you go about it? Chances are indeed that the postmasters logfile (/var/log/postgres) may hold crucial information as Tom suggested. Thanks, Rich Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On Wed, 23 Jul 2008, Andrej Ricnik-Bay wrote: Now there's an interesting piece of information :) How long ago did you upgrade it? Andrej, A month ago; June 17th to be exact. From which version of pg to which version did you upgrade, From 8.1.13 to 8.3.3. and how did you go about it? Chances are indeed that the postmasters logfile (/var/log/postgres) may hold crucial information as Tom suggested. Well, after digging myself into a hole, I received help here and climbed out. It was working last week (when I made some entries into my accounting system and viewed the local version of our web site). However, ... ... something broke during the reboot. From /var/log/postgresql: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 812, but the server was compiled with PG_CONTROL_VERSION 833. HINT: It looks like you need to initdb. I still have the old pgsql (8.1.13) still in a non-standard directory. I had run initdb after cleaning up the upgrade. Should I do so again? Thanks, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems Restarting PostgreSQL Daemon
On Tue, 2008-07-22 at 18:05 -0700, Rich Shepard wrote: On Wed, 23 Jul 2008, Andrej Ricnik-Bay wrote: Now there's an interesting piece of information :) How long ago did you upgrade it? ... something broke during the reboot. From /var/log/postgresql: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 812, but the server was compiled with PG_CONTROL_VERSION 833. HINT: It looks like you need to initdb. I still have the old pgsql (8.1.13) still in a non-standard directory. I had run initdb after cleaning up the upgrade. Should I do so again? It looks to me like your init script just isn't pointing to the 8.3.3 data directory. If you are unsure you can do this: find / -name PG_VERSION You likely have 2 or 3 of them. Find the one that says 8.3 and make sure your start up script points there. Joshua D. Drake Thanks, Rich -- Richard B. Shepard, Ph.D. | IntegrityCredibility Applied Ecosystem Services, Inc.|Innovation http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_query transaction: auto rollback? begin or start?? commit or end???
Bill Wordsworth wrote: ...resending, email didn't go through. On Tue, Jul 22, 2008 at 1:02 PM, Bill Wordsworth [EMAIL PROTECTED] wrote: Is this recommended? pg_query(begin transaction read write;, $connection); if(pg_transaction_status($connection) == 2) { pg_query(insert...;, $connection); pg_query(insert...;, $connection); pg_query(insert...;, $connection); } pg_query(commit transaction;, $connection); pg_close($connection); Now *any* error inside transaction will trigger auto rollback for *all* inserts so I don't need to explicitly issue conditional rollback? Also is begin/commit transaction == start/end transaction?? What if something gets an invalid state (eg you expect a record to have 'active = 156' but it's something else). So in some cases yes you'll need to do a rollback. On the other hand, if you don't explicitly do a commit, everything is rolled back. Yes begin == start transaction and commit == end transaction. -- Postgresql php tutorials http://www.designmagick.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general