[GENERAL] pgaccess not connecting
Running Debian 2.2. Had to reinstall postgresql 7.02 Can access database with psql but not pgaccess. The log that follows is from a stop and restart of postmaster. Script started on Sat Aug 26 01:17:33 2000 kgb10:/home/billb# /etc/init.d/postgresql stop Stopping PostgreSQL postmaster Stopped /usr/lib/postgresql/bin/postmaster (pid 24386). kgb10:/home/billb# /etc/init.d/postgresql stop kgb10:/home/billb# /etc/init.d/postgreql start -i Starting PostgreSQL postmaster kgb10:/home/billb# ps ax | grep postmaster 24746 pts/5S 0:00 /usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres -B 128 -D /var/lib/postgres/data -d 0 24749 pts/5S 0:00 grep postmaster kgb10:/home/billb# ls -al /tmp | grep .s.PGSQL srwxrwxrwx1 postgres postgres0 Aug 26 01:18 .s.PGSQL.5432 kgb10:/home/billb# exit What could account for the missing -i switch in the running postmaster? The last 2 lines of /etc/init.d/postgresql/pg_hda.conf: # By default, allow anything over UNIX domain sockets and localhost. localall trust host all 127.0.0.1 255.255.255.255 trust Thanks Bill Barnes
Re: [GENERAL] Help on analyzing performance problem.
"Travis Bauer" [EMAIL PROTECTED] writes: I have a problem with a _very_ slow query. I'm not sure how to understand the explain output. There's some basic info about EXPLAIN in the "understanding performance" section of the user manual (at least today, it's at http://www.postgresql.org/docs/postgres/c4962.htm). The thing I usually try to do first is understand how the estimated row counts (rows=N) relate to reality. For this you need to know how large the tables are and how restrictive the WHERE clauses related to each table are, so that you can figure out the true output row count for each part of the query plan. The EXPLAIN output alone is not too useful without seeing both the query text and the context information about table sizes etc. regards, tom lane
Re: [GENERAL] pgaccess not connecting
Check the script in /etc/rc.d/init.d/postgresql it doesn't accept parameters in the command linea except start, stop, status. Change the line that runs postmaster adding -i option. -- Jesus Aneiros Sosa mailto:[EMAIL PROTECTED] http://jagua.cfg.sld.cu/~aneiros On Sat, 26 Aug 2000, Tom Lane wrote: Bill Barnes [EMAIL PROTECTED] writes: kgb10:/home/billb# /etc/init.d/postgreql start -i Starting PostgreSQL postmaster kgb10:/home/billb# ps ax | grep postmaster 24746 pts/5S 0:00 /usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres -B 128 -D /var/lib/postgres/data -d 0 What could account for the missing -i switch in the running postmaster? Are you sure your /etc/init.d/postgreql is designed to pass its own commandline switches on to the invoked postmaster? Usually startup scripts don't do that, since they are not designed to be run by hand but from /etc/rc. My bet is that there is a config file somewhere that the init.d script is reading (which is where the other switches given to the postmaster are coming from). You need to put -i in that file. regards, tom lane
[GENERAL] Re: Stripping a prefix
Date: Fri, 25 Aug 2000 09:35:43 -0600 From: Bruce Guenter [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Stripping a prefix Is there a function available to strip a string from the start of a string? trim and ltrim can strip any occurrence of a set of characters from the start of a string, but I want to be able to strip an exact string. In particular, if the string starts with "www." (including the dot), then it should be stripped. trim(leading 'www.' from foo) -- Mike
RE: [GENERAL] pgaccess not connecting
Thanks for the response. You will already have my last posting. The script, BTW, I referred to at /etc/postgresql/postmaster.init has the tag PGALLOWTCPIP=yes/no and the default is no. regards, Bill = Original Message From Jesus Aneiros [EMAIL PROTECTED] = Check the script in /etc/rc.d/init.d/postgresql it doesn't accept parameters in the command linea except start, stop, status. Change the line that runs postmaster adding -i option. -- Jesus Aneiros Sosa mailto:[EMAIL PROTECTED] http://jagua.cfg.sld.cu/~aneiros On Sat, 26 Aug 2000, Tom Lane wrote: Bill Barnes [EMAIL PROTECTED] writes: kgb10:/home/billb# /etc/init.d/postgreql start -i Starting PostgreSQL postmaster kgb10:/home/billb# ps ax | grep postmaster 24746 pts/5S 0:00 /usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres -B 128 -D /var/lib/postgres/data -d 0 What could account for the missing -i switch in the running postmaster? Are you sure your /etc/init.d/postgreql is designed to pass its own commandline switches on to the invoked postmaster? Usually startup scripts don't do that, since they are not designed to be run by hand but from /etc/rc. My bet is that there is a config file somewhere that the init.d script is reading (which is where the other switches given to the postmaster are coming from). You need to put -i in that file. regards, tom lane
[GENERAL] split up tables or one big one?
Hi! I'm writing a system which i could logically separate it into hundreds, perhaps thousands, of tables, or it could put it all into one big table. Since each tables could probably only grow to 10s of MBs in size, from a design, speed, and scalability perspective, is it perferable to split up the tables (as I've currently programmed it) or to put everything into one gigantic, multi-GB table? This is for the web, so transaction speed is important. Patrick.
Re: [GENERAL] Stripping a prefix
On Fri, Aug 25, 2000 at 09:28:59PM -0700, Ian Turner wrote:
If the string is like `^www\.', then trim `w' and `.'.
But this trims all 'w's and all '.'s, even if the string doesn't start
with 'www.':
select trim(leading 'www.' from 'wfoo');
ltrim
---
foo
(1 row)
I ended up writing a function to do it, and for the data sets I'm
dealing with, it's fast enough:
CREATE FUNCTION strip_www (text ) RETURNS text AS
'BEGIN
IF position(''www.'' IN $1) = 1 THEN
RETURN substring($1 FROM 5);
ELSE
RETURN $1;
END IF;
END;' LANGUAGE 'plpgsql';
--
Bruce Guenter [EMAIL PROTECTED] http://em.ca/~bruceg/
PGP signature
[GENERAL] creating functions
Hi All, I'm trying to create a function that takes an interval (in seconds) and returns HH:MM:SS similar to reltime() but not dividing down to num# days,months etc... anyway, I've been looking into the CREATE FUNCTION routines and here I struck a stumbling block. I tried(from the doco): -- CREATE FUNCTION concat_text (text, text) RETURNS text AS ' BEGIN RETURN $1 || $2; END; ' LANGUAGE 'plpgsql'; -- but I received this as the error: -- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. -- I'm running PostgreSQL 7.0.2 on FreeBSD-4.1. I'm new to postgres (just migrating from mysql). What I'm wondering: 1. Am I missing something basic and fundamental here?? 2. Am I looking at the right doco.. I recall on the list a few weeks back there was a coment about the wrong doco being used... Any help is appreciated.. -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
Re: [GENERAL] creating functions
but I received this as the error: -- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. -- By default the procedural languages are not loaded. There's a script createlang which you can use to load the plpgsql language into your database. (If you want it in all future databases, I believe if you add it to template1, all later dbs have it enabled)
Re: [GENERAL] creating functions
Stephan Szabo wrote: but I received this as the error: -- ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. -- By default the procedural languages are not loaded. There's a script createlang which you can use to load the plpgsql language into your database. (If you want it in all future databases, I believe if you add it to template1, all later dbs have it enabled) got it. and guess what the sample function worked! Thanks for that.. -- Dale Walker [EMAIL PROTECTED] Independent Computer Retailers (ICR) http://www.icr.com.au ICRnet http://www.icr.net.au
[GENERAL] Local Users su'ing (REPOST)
Hi, Don't think this made it the first time... Thanks, Andrew -- Forwarded message -- Date: Sat, 26 Aug 2000 15:45:55 +1000 (EST) From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Local Users "su'ing" Hi, I'm running postgresql 7.0.2 under FreeBSD 4.1-STABLE. If a user runs pgsql from the command line and then types \c - user they can connect to the database with the priveleges of user. No password is required, presumably because of the line in pg_hba.conf: localall trust Great fun for someone who su's to pgsql... A couple of questions... 1) This seems to be an odd default behaviour. Should it be documented fairly clearly somewhere (perhaps it is but I missed it) or should the default pg_hba.conf require passwords? 2) Is it possible to not require passwords if the local user connects to postgres as a postgres user of the same name but require a password in all other circumstances? Thanks, Andrew
Re: [GENERAL] split up tables or one big one?
Patrick, Any time your design is heading in this direction, take a good hard look at it. Proper organization with the appropriate indexes is the way to go. With tens of hundreds of tables, how will you decide which to use? How will you write your queries? Customize them for the different tables? Will you be generating a lot of data, thereby creating a lot of tables? How long will they take to create and populate? With fewer, large tables you are appending data at the end, and maintaining indexes. An inherently simpler operation. Queries are written to a known design and structure. You will, admittedly, have large index files, but you will not have hundreds to thousands of tables, each with indexes. The Fishcart ecommerce system, which can be implemented in PostgreSQL, has only 20 tables, four of which have any degree of traffic. A proprietary system done in here in Halifax for the employer's association has about 16 core tables, two of them are regularly updated, the rest contain relatively static information on members, rates, tax rates, piers, etc. Rethink your design, talk it over with the fencepost, draw little pictures, ask "what if", do some rough data storage calculations -- but the general rule of thumb, with proper normalization, is "fewer is better". Regards - Miles Thompson Patrick Goodwill wrote: Hi! I'm writing a system which i could logically separate it into hundreds, perhaps thousands, of tables, or it could put it all into one big table. Since each tables could probably only grow to 10s of MBs in size, from a design, speed, and scalability perspective, is it perferable to split up the tables (as I've currently programmed it) or to put everything into one gigantic, multi-GB table? This is for the web, so transaction speed is important. Patrick.
Re: [GENERAL] table count limitation
Marcin Inkielman wrote: On Sat, 26 Aug 2000, Jurgen Defurne wrote: Date: Sat, 26 Aug 2000 07:36:25 +0200 From: Jurgen Defurne [EMAIL PROTECTED] To: Marcin Inkielman [EMAIL PROTECTED] Cc: postgreSQL general mailing list [EMAIL PROTECTED] Subject: Re: [GENERAL] table count limitation Marcin Inkielman wrote: HI! I have such problem: Is the amount of tables limited in Postgresql7.0? Has anybody tried to use EFFECTIVELY a database with 1 tables at all? Thx for help. Are you really sure you NEED a database with 1 tables ? yes ;) I suggest you read this message which also came up today on the mailing list : Patrick, Any time your design is heading in this direction, take a good hard look at it. Proper organization with the appropriate indexes is the way to go. With tens of hundreds of tables, how will you decide which to use? How will you write your queries? Customize them for the different tables? Will you be generating a lot of data, thereby creating a lot of tables? How long will they take to create and populate? With fewer, large tables you are appending data at the end, and maintaining indexes. An inherently simpler operation. Queries are written to a known design and structure. You will, admittedly, have large index files, but you will not have hundreds to thousands of tables, each with indexes. The Fishcart ecommerce system, which can be implemented in PostgreSQL, has only 20 tables, four of which have any degree of traffic. A proprietary system done in here in Halifax for the employer's association has about 16 core tables, two of them are regularly updated, the rest contain relatively static information on members, rates, tax rates, piers, etc. Rethink your design, talk it over with the fencepost, draw little pictures, ask "what if", do some rough data storage calculations -- but the general rule of thumb, with proper normalization, is "fewer is better". Regards - Miles Thompson Patrick Goodwill wrote: Hi! I'm writing a system which i could logically separate it into hundreds, perhaps thousands, of tables, or it could put it all into one big table. Since each tables could probably only grow to 10s of MBs in size, from a design, speed, and scalability perspective, is it perferable to split up the tables (as I've currently programmed it) or to put everything into one gigantic, multi-GB table? This is for the web, so transaction speed is important. Patrick. Jurgen
