RE: [SQL] List Concatination [warning]
Title: RE: [SQL] List Concatination [warning] A word of warning for the newbies... *Don't* create a function called textcat. You *will* be sorry. There's already a textcat builtin and you kinda need it for || and whatnot. (Yes, I found out the hard way...) There might be a better way to recover, but I did a pg_dumpall, clobbered the postgres DB files, re-inited, re-loaded... and that got me textcat back. A related question: how do you *display* a function? Obviously there's CREATE and DROP, but I could find nothing the equivalent of "LOOK AT". I want to be able to see the code! Anyhow, in case a working example of some list catting SQL would be useful, here's what I put together to list all the states in a "zone": Table "newstate" Attribute | Type | Modifier ---+-+-- state | varchar(4) | fullstate | varchar(40) | zone | varchar(4) | country | varchar(4) | Table "zone_names" Attribute | Type | Modifier ---++-- zoneid | varchar(4) | not null zonename | varchar(8) | not null Indices: zone_names_zoneid_key, zone_names_zonename_key CREATE FUNCTION commacat(text,text) RETURNS text AS ' begin if $1 <> then return $1 || '', '' || $2 ; else return $2; end if; end; ' LANGUAGE 'plpgsql'; CREATE AGGREGATE catenate( sfunc1=commacat, stype1=text, basetype=text, initcond1='' ); CREATE VIEW zones AS SELECT newstate.zone AS id, zone_names.zonename AS zone_name, catenate(newstate.fullstate) AS desc FROM newstate, zone_names WHERE newstate.zone = zone_names.zoneid GROUP BY newstate.zone, zone_names.zonename; The output looks like: cwilkins=# select * from zones where id = 'Z1'; id | zone_name | desc +---+-- Z1 | Zone 1 | Delaware, New York, Pennsylvania (1 row) Many thanks to those here who provided clues on how to do this. I'm a happy camper! :) -cw- > -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 15, 2001 11:30 AM > Cc: sqllist > Subject: Re: [SQL] List Concatination > > > Richard, > > I wanted to thank you for the concatination suggestion > ... on testing, > a custom aggregate *was* faster than procedural concatination ... much > faster. > > > But - if you don't care about the order of contacts you can > define an > > aggregate function: > > > > create aggregate catenate(sfunc1=textcat, basetype=text, > stype1=text, initcond1=''); > > > > Then group by client and catenate(firstname || ' ' || lastname) > > > > You'll want to read the CREATE AGGREGATE page in the > reference manual, > > replace textcat with your own routine that adds a comma and > you'll need > > a finalisation routine to strip the final trailing comma. > > Actually, if you use a sub-select as your data source, you can control > both the appearance and the order of the catenated values: > > SELECT client, catenate(con_name) > FROM (SELECT client, (firstname || ' ' || lastname || ', ' > FROM contacts ORDER BY lastname ASC) AS con_list > GROUP BY client; > > This seems to work pretty well. > > > Note that this is probably not a good idea - the ordering of the > > contacts will not be well-defined. When I asked about this > Tom Lane was > > quite surprised that it worked, so no guarantees about > long-term suitability. > > Hmmm ... this feature is very, very, useful now that I know how to use > it. I'd love to see it hang around for future versions of > PgSQL. Tom? > > -Josh Berkus > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus > Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
[SQL] VACUUM VERBOSE ANALYZE locking up?? Please help!
Title: VACUUM VERBOSE ANALYZE locking up?? Please help! Ok it doesn't kill the whole backend, but VACUUM... comes to a halt partway through and it's necessary to ^C out of the transaction in psql. Sometimes trying again will work, mostly that doesn't work. Restarting the backend and then doing the VACUUM... will usually result in success. I was having this problem in 7.0.something and it's still happening in 7.1.3 (built from scratch on a Redhat 6.2 box). I used pg_dumpall to migrate the data. I've made a serious effort to hunt the web for a clue with no real success. Here's what I get: cwilkins=# vacuum verbose analyze; NOTICE: --Relation pg_type-- NOTICE: Pages 4: Changed 0, reaped 1, Empty 0, New 0; Tup 244: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 26, MinLen 106, MaxLen 109; Re-using: Free/Avail. Space 4472/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec. NOTICE: Index pg_type_oid_index: Pages 2; Tuples 244: Deleted 0. CPU 0.00s/0.00u sec. NOTICE: Index pg_type_typname_index: Pages 4; Tuples 244: Deleted 0. CPU 0.00s/0.00u sec. NOTICE: Analyzing... NOTICE: --Relation pg_attribute-- NOTICE: Rel pg_attribute: TID 24/20: DeleteTransactionInProgress 0 - can't shrink relation NOTICE: Rel pg_attribute: TID 24/21: DeleteTransactionInProgress 0 - can't shrink relation NOTICE: Rel pg_attribute: TID 24/22: DeleteTransactionInProgress 0 - can't shrink relation NOTICE: Rel pg_attribute: TID 24/23: DeleteTransactionInProgress 0 - can't shrink relation NOTICE: Rel pg_attribute: TID 24/24: DeleteTransactionInProgress 0 - can't shrink relation NOTICE: Rel pg_attribute: TID 24/25: DeleteTransactionInProgress 0 - can't shrink relation [[[etc... etc... snip...]]] NOTICE: Index pg_rewrite_oid_index: Pages 2; Tuples 11: Deleted 0. CPU 0.00s/0.00u sec. NOTICE: Index pg_rewrite_rulename_index: Pages 2; Tuples 11: Deleted 0. CPU 0.00s/0.00u sec. NOTICE: --Relation pg_toast_17058-- NOTICE: Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 2: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 3, MinLen 834, MaxLen 2034; Re-using: Free/Avail. Space 5280/0; EndEmpty/Avail. Pages 0/0. CPU 0.01s/0.00u sec. NOTICE: Index pg_toast_17058_idx: Pages 2; Tuples 2: Deleted 0. CPU 0.00s/0.00u sec. [[[query stops here and will go no further]]] Any useful insights or solutions? Pretty please? :) Thanks for any help, Charlie (Apologies in advance if this message has any HTML component. I've told ^%$#@! Outlook as loudly as possible PLAIN TEXT ONLY!) -- Charlie Wilkinson TRIS Development Systems Administrator M:I:SD:CT:CC:TD Phone: 202-283-3241 MSMail: [EMAIL PROTECTED] SMTP: [EMAIL PROTECTED] Home: [EMAIL PROTECTED] This message constructed from 90% post-consumer electrons.
Re: [SQL] Disk is full, what's cool to get rid of?
Won't help some of us, who set -m 0 on selected filesystems to begin with. But if we could get tune2fs -m -5 to work, then we could unreserve space that didn't previously exist. Think of the possibilties! I'll look into that as soon as I'm done modding my C compiler to handle the --force option. -cw- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Erik Jones Sent: Thursday, July 27, 2006 4:00 PM To: Jeff Frost Cc: Scott Marlowe; General Postgresql List Subject: Re: [SQL] Disk is full, what's cool to get rid of? Awesome. Makes sense as 5% is exactly the amount of space that appeared after running it. Thanks! Jeff Frost wrote: > Depends what the default is on your system. The default is 5% with > the version of mke2fs that I have here, so you would just: > > tune2fs -m 5 > > to put it back. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
