[GENERAL] Vacuum analyze keeps hanging (RedHat 6.2, PG 7.03)
Vacuum analyze keeps hanging here... NOTICE: --Relation referer_log-- NOTICE: Pages 529: Changed 1, reaped 509, Empty 0, New 0; Tup 24306: Vac 43000, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 72, MaxLen 324; Re-using: Free/Avail. Space 5205100/5193540; EndEmpty/Avail. Pages 0/508. CPU 0.03s/0.11u sec. NOTICE: Index referer_log_date_idx: Pages 159; Tuples 24306: Deleted 0. CPU 0.01s/0.08u sec. -- My system RedHat 6.2 PostgreSQL 7.03 PIII 500 MHz, 320 MB memory - JT James Thornton, http://jamesthornton.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] function with multi-values
Dear: I wanna create a function that return a multiple rows in to a single row. example: CREATE FUNCTION GETNAME() RETURNS SETOF VARCHAR AS 'SELECT NAME FROM TEST;' LANGUAGE 'SQL'; when i call this function it return: -- peter susan john but I wonder is it possible to make it to return something like - petersusanjohn I have to use this result to print in quick report. THANK YOU VERY MUCH Harry Yau ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] are there plans for a threaded alternative to multipledaemons?
On Fri, 27 Apr 2001, Clayton Vernon wrote: I was wondering what the plans were for PostgreSQL to convert to the one process multithreaded approach, as Apache, Interbase and others are doing? there has been talk about doing some threads actions inside of a process, but, if I recall my read of Apache2, they are still doing multi-process, with threading inside of each process ... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] On the _need_ to vacuum...
Hello all: I am part of a software development team evaluating RDBMSs for inclusion as a base component of a messaging system. I've been thrashing hard on PostgreSQL under Solaris 8 and the GNU compiler for a few days now, and personally, I'm impressed. Thank you, developers. The only two major problems I face when considering the use of PostgreSQL 7.1 as released are: 1) index efficiency appears to drop over relatively short time periods on highly volatile tables, causing producers to eventually start pulling away from more efficient consumers of data in long-term tests which include well-oiled situations in the load mix. 2) vacuum analyze holds an exclusive table lock for a _significant_ period of time, particularly when vacuuming tables that have been highly volatile. The system we are building needs to have the ability to keep chugging along 24/7 - without _any_ long lapses of table availability. Is there any other way to keep this type of table preened and performant without a heavyweight table lock being involved? If not, please consider this as an item for prioritized future development. I thank you in advance for your replies via email or this newsgroup. -- Jack Bates Portland, OR, USA http://www.floatingdoghead.net My PGP public key: http://www.floatingdoghead.net/pubkey.txt ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Vacuum analyze keeps hanging (RedHat 6.2, PG 7.03)
I had this problem with 7.0.3, but it cleared up completely with 7.1 W James Thornton wrote: Vacuum analyze keeps hanging here... NOTICE: --Relation referer_log-- NOTICE: Pages 529: Changed 1, reaped 509, Empty 0, New 0; Tup 24306: Vac 43000, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 72, MaxLen 324; Re-using: Free/Avail. Space 5205100/5193540; EndEmpty/Avail. Pages 0/508. CPU 0.03s/0.11u sec. NOTICE: Index referer_log_date_idx: Pages 159; Tuples 24306: Deleted 0. CPU 0.01s/0.08u sec. -- My system RedHat 6.2 PostgreSQL 7.03 PIII 500 MHz, 320 MB memory - JT James Thornton, http://jamesthornton.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] VACUUMing in general
Is any work being done to streamline/redesign the way PostgreSQL handles updates and/or the way VACUUM works so that it doesn't lock the entire table while it does it's job? It seems that tables that need to be VACUUM'ed the most would typically have fewer acceptable "windows" in which to work.
[GENERAL] Re: [HACKERS] While we're on the subject of searches...
Over the past few months there've been a number of requests for an interactive type documentation setup like the folks at php.net have. Great to add to the documentation, but I hope the PostgreSQL project doesn't take it so far as to make the primary documentation interactive. A well-thought out, coherent document is _much_ more useful than the skads of random tips that characterize some other projects. The current document is very well-written (though perhaps incomplete). I would hate to see that decline in quality. Cheers, Brook ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] On the _need_ to vacuum...
* Jack Bates [EMAIL PROTECTED] [010428 13:31] wrote: Hello all: I am part of a software development team evaluating RDBMSs for inclusion as a base component of a messaging system. I've been thrashing hard on PostgreSQL under Solaris 8 and the GNU compiler for a few days now, and personally, I'm impressed. Thank you, developers. The only two major problems I face when considering the use of PostgreSQL 7.1 as released are: 1) index efficiency appears to drop over relatively short time periods on highly volatile tables, causing producers to eventually start pulling away from more efficient consumers of data in long-term tests which include well-oiled situations in the load mix. 2) vacuum analyze holds an exclusive table lock for a _significant_ period of time, particularly when vacuuming tables that have been highly volatile. The system we are building needs to have the ability to keep chugging along 24/7 - without _any_ long lapses of table availability. Is there any other way to keep this type of table preened and performant without a heavyweight table lock being involved? If not, please consider this as an item for prioritized future development. I thank you in advance for your replies via email or this newsgroup. There's a fix for Postgresql 7.0.3 here: http://www.freebsd.org/~alfred/vacfix I'm strongly considering taking the patches offline and reselling them as I seem to be the only source for them nowadays. -- -Alfred Perlstein - [[EMAIL PROTECTED]] http://www.egr.unlv.edu/~slumos/on-netbsd.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: On the _need_ to vacuum...
I am rather staggered by a developer considering it necessary to attempt to cooerce the core development team into including a patch. If the work that Alfred has done is as effective as he claims, then there must be a *REALLY* good reason why it isn't being included. I don't want to start any form of war But as a user I'd be interested to know why such a patch would appear to be unacceptable. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Glen Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand 5301 Ph/Fax: +64 6 357 8168, Mob: +64 21 424 015 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Re: crypt(table.field) ?
quote who=J.H.M. Dassen (Ray) will trillich [EMAIL PROTECTED] wrote: Care to explain -- in terms a Debian newbie might grok -- what contrib/pgcrypto means? Peter is referring to a directory in the PostgreSQL sources, not to a part of a binary package. apt-get source postgresql and look around. You'll often find things like these in the /usr/share/doc/package/examples directory under Debian. There's always a few goodies in there anyway. :) - Jeff -- o/~ In spite of all those keystrokes, you're addicted to vim. *ka-ching!* o/~ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] creating names in plpgsql-functions
Under 7.0.x and earlier, no.
Under 7.1, you can use execute to execute a string which
could have dynamic bits (replace create sequence seqname;
with execute ''create sequence '' || seqname;)
On Sat, 28 Apr 2001, Lieven Van Acker wrote:
Hi,
is there a way to make a name in a plpgsql procedure dynamic? I want to
do something like this:
CREATE FUNCTION create_admin(BPCHAR)
RETURNS BPCHAR AS '
DECLARE
seqname name;
a alias for $1;
BEGIN
insert into admin(adminid)
values (a);
seqname := a || ''_seq'';
create sequence seqname;
return seqname;
END;'
LANGUAGE 'plpgsql';
Calling this function returns
SELECT create_admin('TS');
ERROR: parser: parse error at or near $1
Thanks,
Lieven
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
---(end of broadcast)---
TIP 3: 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] Re: On the _need_ to vacuum...
* [EMAIL PROTECTED] [EMAIL PROTECTED] [010428 21:44] wrote: I am rather staggered by a developer considering it necessary to attempt to cooerce the core development team into including a patch. I'm assuming you refer to the updated page at: http://people.freebsd.org/~alfred/vacfix/ If the work that Alfred has done is as effective as he claims, then there must be a *REALLY* good reason why it isn't being included. The work is not mine. It was contracted by my previous employer that I still maintain a close working relationship with. I don't want to start any form of war But as a user I'd be interested to know why such a patch would appear to be unacceptable. I never said anyone accused the patch was unacceptable I just said it was never integrated nor brought up to date with the 7.1 branch. I'll update the vacfix page to explain better. I also need to update it to explain that the vacfix is not a cure-all, certain degenerate conditions cause it to perform as bad if not worse than a traditional vacuum. -- -Alfred Perlstein - [[EMAIL PROTECTED]] Represent yourself, show up at BABUG http://www.babug.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] JDBC speed question.
Hi Clayton, From what I remember, using -p will change BOTH the TCP/IP port and the Unix domain socket. i.e. pg_ctl start -o '-i -p ' Should make PostgreSQL listen on TCP port , and on most unix systems will create a socket file in /tmp/.s.PGSQL. (and its corresponding lock file). Regards and best wishes, Justin Clift Clayton Vernon wrote: Thanks, but can I specify each port? I've only seen the one documented command option -p which I assume was for the TCP/IP port. Again, thanks for the help, Clayton - Original Message - From: Doug McNaught [EMAIL PROTECTED] To: Clayton Vernon [EMAIL PROTECTED] Cc: John Oakes [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 26, 2001 6:19 PM Subject: Re: [GENERAL] JDBC speed question. Clayton Vernon [EMAIL PROTECTED] writes: Related question: how do you run postgreSQL to simultaneously support a Unix socket and TCP/IP? Just add '-i' to the postmaster startup options. The Unix socket will still be available. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: On the _need_ to vacuum...
On Sun, Apr 29, 2001 at 04:33:42PM -0020, [EMAIL PROTECTED] wrote: If the work that Alfred has done is as effective as he claims, then there must be a *REALLY* good reason why it isn't being included. Isn't 7.1 in a code freeze? That seems like a *REALLY* good reason not to include such a change at this moment. Nothing prevents YOU from using it, of course. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen ---(end of broadcast)--- TIP 3: 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] Re: crypt(table.field) ?
On Sun, Apr 29, 2001 at 03:04:18PM +1000, Jeff Waugh wrote: quote who=J.H.M. Dassen (Ray) will trillich [EMAIL PROTECTED] wrote: Care to explain -- in terms a Debian newbie might grok -- what contrib/pgcrypto means? Peter is referring to a directory in the PostgreSQL sources, not to a part of a binary package. apt-get source postgresql and look around. You'll often find things like these in the /usr/share/doc/package/examples directory under Debian. There's always a few goodies in there anyway. :) aha. there's apt-get install postgresql-crypt but for 7.0.3 there's no crypt yet. i'll wait. :) -- don't visit this page. it's bad for you. take my expert word for it. http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
