Re: [GENERAL] Letting a function return multiple columns instead of a single complex one

> test=# select c,(ff).* from (select bar.*,get_a_foo(c) as ff from bar) b; > c | a | b > ---+---+--- > 1 | 1 | 2 > (1 row) > > Tanks! that works great! It managed to get it even a bit more simplified: select bar.*, (get_a_foo(c)).* from bar; > Not amazingly elegant, but it works. Note that you

Re: [GENERAL] tables with lots of columns - what alternative from performance point of view?

On 12/7/05, Oleg Bartunov wrote: contrib/hstore will save you.See http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstorefor details. thanks. i didn't know about it, and it looks great. but i'm not sure if we will be able to use it - my developers use java + hibernate, a

Re: [GENERAL] TSearch2 / Get all unique lexems

On 07.12.2005 16:13, Oleg Bartunov wrote: hmm, you could dump tsvector column and use awk+sort+uniq Thanks. I hoped for something possible inside a pl/pgsql proc. I'm trying to integrate pg_trgm with Tsearch2. I'm still on my UTF-8 database. Yes I know, there is _NO_ UTF-8 support of any kind

Re: [GENERAL] Help on collation and accent sensitivity

jlmssilva wrote: > I'm experienced with SQL Server (now learning pg) > and there only by choosing the appropriate collation I get > case/accent sensitivity policy well defined. Is there any similar > parameter in pg that I've missed and have the same effect? You could try to define your own locale

Re: [GENERAL] fts, compond words?

hrm... that is a problem. Though, I think that's a case of how the compiled expression is built from user input. Unless I'm mistaken a + ( foo1 | foo2 ) is exactly equal to (a + foo1) | (a + foo2) Ahhh... but then there is the more complex example of a + foonish + bar becoming a

Re: [GENERAL] TSearch2 / Get all unique lexems

Thanks. I hoped for something possible inside a pl/pgsql proc. I'm trying to integrate pg_trgm with Tsearch2. I'm still on my UTF-8 database. Yes I know, there is _NO_ UTF-8 support of any kind in Tsearch2 yet, but I got it working to a degree that is OK for my application (Created my own stemm

Re: [GENERAL] TSearch2 / Get all unique lexems

On Thu, 8 Dec 2005, Hannes Dorbath wrote: On 07.12.2005 16:13, Oleg Bartunov wrote: hmm, you could dump tsvector column and use awk+sort+uniq Thanks. I hoped for something possible inside a pl/pgsql proc. I'm trying to integrate pg_trgm with Tsearch2. I'm still on my UTF-8 database. Yes I kn

Re: [GENERAL] memory leak under heavy load?

On Thu, Dec 08, 2005 at 12:29:11PM +0800, Kathy Lo wrote: > > But this shouldn't be an issue here. If you set the IPC_RMID flag then > > the kernel should remove the segment when all users go away. This is > > standard IPC behaviour and is documentated in the manpage... > > > > Would you please te

[GENERAL] how to check if a database exists programatically

 i have to write a shell script in which i have to check if the database "abc" exists or not   how should i do it ? some select query   select * from ??? where database_name = 'abc';

Re: [GENERAL] how to check if a database exists programatically

am 08.12.2005, um 17:25:10 +0530 mailte surabhi.ahuja folgendes: > i have to write a shell script in which i have to check if the database > "abc" exists or not > > how should i do it ? some select query > > select * from ??? where database_name = 'abc'; You can parse the output from 'psql

Re: [GENERAL] how to check if a database exists programatically

Hi, On Thu, 2005-12-08 at 17:25 +0530, surabhi.ahuja wrote: > i have to write a shell script in which i have to check if the > database "abc" exists or not > > how should i do it ? some select query > > select * from ??? where database_name = 'abc'; SELECT 1 from pg_database WHERE datname='a

Re: [GENERAL] how to check if a database exists programatically

Hi, On Thu, 2005-12-08 at 14:20 +0200, Devrim GUNDUZ wrote: > SELECT 1 from pg_database WHERE datname='abc'; Ah of course: $ psql template1 -U postgres -c "SELECT 1 AS result FROM pg_database WHERE datname='yapab'"; result 1 (1 row) $ psql template1 -U postgres -c "SELECT 1 AS

[GENERAL] Multi-Master-Solution (PGCluster)?

Hello list, we need urgently a database cluster with load balancing, high available and multi-master support. We would like use as database engine PostgresSQL. The only propgramm we found is pgcluster. But it seems to be in beta stage und has no support for pgSQl 8.1. Has anybody experience w

[GENERAL] is it possible to delete the psql log while psql is running?

hi, i'd like to delete the postgresql log file (resides in /var/log/pgsql/postgres), because it has become too big. can i simply delete the file while postres is running? or do i have to stop postgres first, and only delete the logfile after that? thanks, gabor ---(end

Re: [GENERAL] is it possible to delete the psql log while psql is

Gabor, Short answer: you'll have to restart. Long answer: It depends a lot on your OS... on windows I bet you can't delete it, the OS will prevent you to do it (you'll get some error). On Linux you can delete it, but the postgres server will still use it even if you can't access it anymore (won't

Re: [GENERAL] Memory Leakage Problem

On 12/8/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Mike Rylander <[EMAIL PROTECTED]> writes: > > To cut to the chase, here are > > some numbers for everyone to digest: > >total gnu ps resident size > > # ps ax -o rss|perl -e '$x += $_ for (<>);print "$x\n";' > > 5810492 > >to

Re: [GENERAL] is it possible to delete the psql log while psql is

On Thu, Dec 08, 2005 at 02:43:44PM +0100, Csaba Nagy wrote: > Gabor, > > Short answer: you'll have to restart. > > Long answer: > It depends a lot on your OS... on windows I bet you can't delete it, the > OS will prevent you to do it (you'll get some error). Note: on Linux I've had success with

Re: [GENERAL] is it possible to delete the psql log while psql is

[snip] > Note: on Linux I've had success with simply truncating the file using: > > : > [filename] > > This does free up the space used straightaway. Depending on the mode > the file was opened with it may also reset the position the postmaster > writes at. Interesting hint, I'll try it next tim

[GENERAL] is it possible to delete the psql log while psql is running?

hi, i'd like to delete the postgresql log file (resides in /var/log/pgsql/postgres), because it has become too big. can i simply delete the file while postgresql is running? or do i have to stop postgresql first, and only delete the logfile after that? thanks, gabor -- That's life for you,

Re: [GENERAL] is it possible to delete the psql log while psql is

gabor schrieb: hi, i'd like to delete the postgresql log file (resides in /var/log/pgsql/postgres), because it has become too big. can i simply delete the file while postgresql is running? or do i have to stop postgresql first, and only delete the logfile after that? echo -n >postgresql.log

[GENERAL] Special message brought to you by Writer's Digest

December 8, 2005 To: pgsql-general@postgresql.org === As part of Writer’s Digest's commitment to bring our e-newsletter subscribers useful information on new products, services, and educational programs, we want to share the following mess

Re: [GENERAL] fts, compond words?

On 12/8/05, Teodor Sigaev <[EMAIL PROTECTED]> wrote: > > (a + foo1 + bar) | (a + foo2 + bar) > > That a simple case, what about languages as norwegian or german? They has > compound words and ispell dictionary can split them to lexemes. But, usialy > there is more than one variant of separation: >

Re: [GENERAL] is it possible to delete the psql log while psql is

I'd do echo > pgql.log Oleg On Thu, 8 Dec 2005, gabor wrote: hi, i'd like to delete the postgresql log file (resides in /var/log/pgsql/postgres), because it has become too big. can i simply delete the file while postgresql is running? or do i have to stop postgresql first, and only delet

Re: [GENERAL] Multi-Master-Solution (PGCluster)?

On Thu, 2005-12-08 at 04:16, Jens Carl wrote: > Hello list, > > we need urgently a database cluster with load balancing, high available > and multi-master support. We would like use as database engine PostgresSQL. > The only propgramm we found is pgcluster. But it seems to be in beta > stage und

[GENERAL] date format

I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change all my existing SQL to specifically ask for

Re: [GENERAL] date format

David Rysdam wrote: I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change all my existing SQL to

Re: [GENERAL] date format

David Rysdam wrote: David Rysdam wrote: I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change a

Re: [GENERAL] is it possible to delete the psql log while psql is running?

cat /dev/null > name_of_log_to_truncate -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of gabor Sent: Thursday, December 08, 2005 6:21 AM To: pgsql-general@postgresql.org Subject:[GENERAL] is it possible to delete the psql log while psql is

[GENERAL] Specify schema in jdbc connection string?

Is there any way to specify which schema I want to use (or have at the front of the search path) within the jdbc connection string? I've seen several people asking for the feature in the archives, one person saying they wrote it but the patch was rejected, but not authoritative answers... Th

[GENERAL] Error in 7.4.9 Documentation

FWIW:  At http://www.postgresql.org/docs/7.4/static/infoschema-triggers.html, it says there is a column in the information_schema ”triggers” table named “event_object_name”.  The name actually is “event_object_table”.   ~ Ken

Re: [GENERAL] Error in 7.4.9 Documentation

Ken Winter wrote: > FWIW: At > http://www.postgresql.org/docs/7.4/static/infoschema-triggers.html, > it says there is a column in the information_schema "triggers" table > named "event_object_name". The name actually is > "event_object_table". Fixing... -- Peter Eisentraut http://developer.pos

Re: [GENERAL] Specify schema in jdbc connection string?

On Thu, 8 Dec 2005, Dan Armbrust wrote: Is there any way to specify which schema I want to use (or have at the front of the search path) within the jdbc connection string? No. It is still an open todo open. http://jdbc.postgresql.org/todo.html Kris Jurka ---(end of

Re: [GENERAL] Specify schema in jdbc connection string?

Kris Jurka <[EMAIL PROTECTED]> writes: > On Thu, 8 Dec 2005, Dan Armbrust wrote: >> Is there any way to specify which schema I want to use (or have at the front >> of the search path) within the jdbc connection string? > No. It is still an open todo open. Depending on what you need, it might be

Re: [GENERAL] is it possible to delete the psql log while psql is

On Thursday December 8 2005 9:19 am, Oleg Bartunov wrote: > I'd do > echo > pgql.log That won't free any space from an open file. You have to stop postgresql first in order to close the file, or use log file rotation and wait until it rotates out to a closed state. Ed > >Oleg > > On Thu,

[GENERAL] find last day of month

I have a table containing month column in format mm. create table months ( tmkuu c(7)); insert into months values ('01.2005'); insert into months values ('02.2005'); How to create select statement which converts this column to date type containing last day of month like '2005-01-31' '2005-0

Re: [GENERAL] find last day of month

Andrus Moor wrote: I have a table containing month column in format mm. create table months ( tmkuu c(7)); insert into months values ('01.2005'); insert into months values ('02.2005'); How to create select statement which converts this column to date type containing last day of month like

[GENERAL] How efficient is select currval?

If I insert a record into a table with a serial primary key, does select currval fetch it from the database or does it get it from within memory? If it fetches it from the database, should I use a Serializable transaction or just a read committed transaction? Chris -- Chris Velevitch Manager -

[GENERAL] copy with where query?

Is it possible to copy data from a table into a file and specify a query for what data should be included? e.g. \copy table1 to 'data.txt' where col1=true thanks csn __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection arou

Re: [GENERAL] copy with where query?

How about something like: BEGIN TRAN SELECT INTO TEMP TABLE foobar WHERE COPY foobar TO data.txt COMMIT > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of CSN > Sent: Thursday, December 08, 2005 5:29 PM > To: pgsql-general@postgresql.org

[GENERAL] bug or not? Trigger preventing delete causes circumvention of FK

I wrote a trigger function with the intent of preventing the deletion of a parent record when a referencing record would not allow it. However, the result is that the referencing record stays, but the referenced one is gone, so that my foreign key constraint is not respected. The behaviour can

Re: [GENERAL] bug or not? Trigger preventing delete causes circumvention of FK

Luca Pireddu <[EMAIL PROTECTED]> writes: > I wrote a trigger function with the intent of preventing the deletion of a > parent record when a referencing record would not allow it. However, the > result is that the referencing record stays, but the referenced one is gone, > so that my foreign ke

Re: [GENERAL] memory leak under heavy load?

On 12/8/05, Martijn van Oosterhout wrote: > On Thu, Dec 08, 2005 at 12:29:11PM +0800, Kathy Lo wrote: > > > But this shouldn't be an issue here. If you set the IPC_RMID flag then > > > the kernel should remove the segment when all users go away. This is > > > standard IPC behaviour and is document

Re: [GENERAL] is it possible to delete the psql log while psql is

hi, use "cp /dev/null pqsql.log" to truncate the logfile "in place" and free some space. re, thies Am 08.12.2005 um 23:26 schrieb Ed L.: On Thursday December 8 2005 9:19 am, Oleg Bartunov wrote: I'd do echo > pgql.log That won't free any space from an open file. You have to stop postg

[GENERAL] after vacuum, db is still "growing" :(

hi, i'm using postgresql 7.4.8. we have a database that only contains one table that contains session data, so it changes very often. until now, we were vacuuming the database weekly. it's current size is 15GB. i know that when i once did a full-vacuum on this database, it's size shrunk to 1

Re: [GENERAL] How efficient is select currval?

Chris Velevitch wrote: If I insert a record into a table with a serial primary key, does select currval fetch it from the database or does it get it from within memory? If it fetches it from the database, should I use a Serializable transaction or just a read committed transaction? You can con