Re: [GENERAL] return row from plpgsql?

In response to zhong ming wu : > Hello > > I have tried to return rowtypes and record from plpgsql > but they don't look like anything what is returned from select a,b,c > from table d; Can you show us your function? > I prefer to do this simply as > > select aplpgsqlfunction('%u') > > The

[GENERAL] return row from plpgsql?

Hello I have tried to return rowtypes and record from plpgsql but they don't look like anything what is returned from select a,b,c from table d; My application is for dovecot imap server userdb where I can only put one sql statement to retrieve a few items. I need to perform some logic in retrie

[GENERAL] Result order of Insert ... returning

Hello! I'm using INSERT ... RETURNING with multiply values, for example INSERT INTO links.tags (name) VALUES ('tag x'), ('tag y') RETURNING id, name and fetch result. Is it guaranteed that order of returned rows will always match order of values (that is, first returned row will be with name='tag x

Re: [GENERAL] app table names

On Tue, 2010-03-16 at 16:35 -0400, Vick Khera wrote: > On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee wrote: > > I'm curious what people consider best practice (or how do you do it) to > > help ensure these name collisions don't happen. > > Do not mix data from multiple applications in one databa

Re: [GENERAL] app table names

On 3/16/2010 3:35 PM, Vick Khera wrote: > On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee wrote: > >> I'm curious what people consider best practice (or how do you do it) to >> help ensure these name collisions don't happen. >> > Do not mix data from multiple applications in one database.

Re: [GENERAL] app table names

On Tue, Mar 16, 2010 at 3:03 PM, Jamie Kahgee wrote: > I'm curious what people consider best practice (or how do you do it)  to > help ensure these name collisions don't happen. Do not mix data from multiple applications in one database. Use multiple databases to isolate them entirely. -- Sent

[GENERAL] Answer to my own question

I had forgotten about hub.org. That's where I'm headed. Sorry for the interruption. John -- 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] app table names

On Tue, Mar 16, 2010 at 1:03 PM, Jamie Kahgee wrote: > My company has a handful of apps that we deploy in the websites we build. >  Recently a very old app needed to be included along side a newer app and > there was a conflict w/ a duplicate table name needed to be used by both > apps. > We are n

[GENERAL] Best ISP for Postgres

Does anybody want to share their opinion as to the best beginner ISP that supports Postgres? I want to use PHP against a Postgres database. Hostpoint in Switzerland gives you 10gig's for 9.9CHF (less than 7euros) per month as a starter package...with MySQL, which I definitely do not want.

[GENERAL] app table names

My company has a handful of apps that we deploy in the websites we build. Recently a very old app needed to be included along side a newer app and there was a conflict w/ a duplicate table name needed to be used by both apps. We are now in the process of updating an old app and there will be some

Re: [GENERAL] reuse data in existing data directory

Thanks Richard, in fact I just had to run the manual install of postgres and define the data directory to my existing one and everything was restoredgreat, but I preferred to ask rather than erasing my data ;) Thanks Peter On Tue, 16 Mar 2010 12:24:41 +, Richard Huxton wrote: > On 16/0

Re: [GENERAL] UPDATE with JOIN not using index

Le 16/03/2010 16:52, Tom Lane a écrit : Sometime in the future they might generate the same plan. Right now the planner's ability to optimize sub-SELECTs is pretty limited, and so you typically get a nestloop-like plan even if some other join style would be faster. OK, thanks for the clarifica

Re: [GENERAL] UPDATE with JOIN not using index

Arnaud Lesauvage writes: > I am still a bit intrigued by the different query plans. Both query look > very similar to me, so why does the planner make so different choices ? Sometime in the future they might generate the same plan. Right now the planner's ability to optimize sub-SELECTs is pret

Re: [GENERAL] Move to core xml from contrib/xml2

Frank jansen writes: > Am 15.03.2010 22:10, schrieb Bruce Momjian: >> We have fixed the serious bugs in /contrib/xml2 with the minor releases >> that came out today, 2010-03-15. >> > As already said: "out of memory on 8.4.3" (the script consumes up > machines memory till it dies) You were alrea

Re: [GENERAL] Move to core xml from contrib/xml2

Am 15.03.2010 22:10, schrieb Bruce Momjian: Frank jansen wrote: Hi, we have encountered huge memory problems (segfault on 8.4.2, out of memory on 8.4.3) with our xml functions when moving from 8.3 to 8.4, as mentioned here: http://archives.postgresql.org/pgsql-general/2010-03/msg00563.php

Re: [GENERAL] restoring a database to its initial state

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane ha scritto: > Pavel Stehule writes: >> 2010/3/13 Manlio Perillo : >>> Usually when I need to restore a database to its initial state, what I >>> do is to simply drop it, and then re-create it. >>> However on a shared hosting this is not possi

Re: [GENERAL] Daylight savings time confusion

Tom Lane wrote: > If my guesses are correct, then the minimum change to avoid this type > of problem in the future is to change UTCTimestamp to be declared as > timestamp WITHOUT time zone, so that you don't get two extra zone > rotations in there. However, I would strongly suggest that you rethi

Re: [GENERAL] UPDATE with JOIN not using index

Le 16/03/2010 16:02, t...@fuzzy.cz a écrit : I will try increasing work_mem, but it is already set at 16MB which I found is quite high. What do you mean by "high"? I believe the proper value of work_mem is such that results in highest performance of the query while not causing problems to the

Re: [GENERAL] UPDATE with JOIN not using index

Le 16/03/2010 15:37, Tom Lane a écrit : Arnaud Lesauvage writes: First query : "Merge Join (cost=699826.38..704333.80 rows=13548 width=836)" " Merge Cond: (((c.rue)::text = (r.rue)::text) AND ((c.codesite)::text = (r.codesite)::text))" Second query : "Seq Scan on cellules c (cost=0.

Re: [GENERAL] UPDATE with JOIN not using index

> Le 16/03/2010 15:25, Richard Huxton a écrit : >> OK - we have a merge join in the first case where it joins the >> pre-sorted output of both tables. >> >> In the second case it queries the index once for each row in "cellules". >> >> Now look at the costs. The first one is around 704,000 and th

Re: [GENERAL] Text search

Richard thanks for the pointers unfortunately its not just attribute names. Here is what I am thinking of doing; In a first step I run a query SELECT id FROM time_series WHERE to_tsvector(xml_string) @@ to_tsquery( anystring ); then I load the actual xml string into memory for each i

Re: [GENERAL] UPDATE with JOIN not using index

Le 16/03/2010 15:25, Richard Huxton a écrit : OK - we have a merge join in the first case where it joins the pre-sorted output of both tables. In the second case it queries the index once for each row in "cellules". Now look at the costs. The first one is around 704,000 and the second one is 5,

Re: [GENERAL] Text search

On 16/03/10 13:49, Richard Huxton wrote: You could run an xslt transform over the xml fragments and extract what you want and then use tsearch to index that, I suppose. Similarly, you might be able to do the same via xslt and xquery. Actually, if it's only attribute names you're interested in y

Re: [GENERAL] UPDATE with JOIN not using index

Arnaud Lesauvage writes: > First query : > "Merge Join (cost=699826.38..704333.80 rows=13548 width=836)" > " Merge Cond: (((c.rue)::text = (r.rue)::text) AND ((c.codesite)::text > = (r.codesite)::text))" > " -> Sort (cost=696320.21..697701.07 rows=552343 width=823)" > "Sort Key: c.r

Re: [GENERAL] UPDATE with JOIN not using index

On 16/03/10 13:57, Arnaud Lesauvage wrote: First query : "Merge Join (cost=699826.38..704333.80 rows=13548 width=836)" " Merge Cond: (((c.rue)::text = (r.rue)::text) AND ((c.codesite)::text = (r.codesite)::text))" " -> Sort (cost=696320.21..697701.07 rows=552343 width=823)" " Sort Key: c.rue, c.

Re: [GENERAL] hardware for a server

Greg Smith wrote: > Bruce Momjian wrote: > > Greg Smith wrote: > > > >> Given what you've said about your budget here, I suspect that you're > >> heading toward either 3ware or LSI and all SATA drives. I wouldn't > >> expect that big of a performance difference between the two with only 8 >

Re: [GENERAL] UPDATE with JOIN not using index

Le 16/03/2010 14:50, Richard Huxton a écrit : On 16/03/10 13:05, Arnaud Lesauvage wrote: PostgreSQL 8.4 here. I have a simple update query that looks like this : UPDATE t1 SET col = t2.col FROM t2 WHERE t1.key1 = t2.key1 AND t1.key2 = t2.key2; There is an index on (key1,key2) on the joi

Re: [GENERAL] UPDATE with JOIN not using index

On 16/03/10 13:05, Arnaud Lesauvage wrote: Hi all ! PostgreSQL 8.4 here. I have a simple update query that looks like this : UPDATE t1 SET col = t2.col FROM t2 WHERE t1.key1 = t2.key1 AND t1.key2 = t2.key2; There is an index on (key1,key2) on the joined table (t2). This query does not use the

Re: [GENERAL] Text search

On 16/03/10 12:36, Chris Roffler wrote: Richard I tried all that and you can see it on this thread, there are some limitations on indexs on xpath work http://archives.postgresql.org/pgsql-general/2010-03/msg00270.php OK - I'v

[GENERAL] UPDATE with JOIN not using index

Hi all ! PostgreSQL 8.4 here. I have a simple update query that looks like this : UPDATE t1 SET col = t2.col FROM t2 WHERE t1.key1 = t2.key1 AND t1.key2 = t2.key2; There is an index on (key1,key2) on the joined table (t2). This query does not use the index. If I rewrite it with a subselect, t

Re: [GENERAL] Text search

Richard I tried all that and you can see it on this thread, there are some limitations on indexs on xpath work http://archives.postgresql.org/pgsql-general/2010-03/msg00270.php On Tue, Mar 16, 2010 at 2:21 PM, Richard Huxton wr

Re: [GENERAL] reuse data in existing data directory

On 16/03/10 12:02, Peter Schmidtke wrote: Dear PostGreSQL users, I have the following problem. I have a system with several partitions (openSuse Linux 11.1). Far what is important related to this question : - 1 system partition (where postgres is installed) - 1 data partition (where the postgre

Re: [GENERAL] Text search

On 16/03/10 10:29, Chris Roffler wrote: I have a text column in a table. We store XML in this column. Now I want to search for tags and values select * from where to_tsvector('english',xml_column) @@ to_tsquery('Citi Bank') This works fine but it also works for any tag as long as the nam

[GENERAL] reuse data in existing data directory

Dear PostGreSQL users, I have the following problem. I have a system with several partitions (openSuse Linux 11.1). Far what is important related to this question : - 1 system partition (where postgres is installed) - 1 data partition (where the postgres data directory is lying) my system parti

[GENERAL] Text search

I have a text column in a table. We store XML in this column. Now I want to search for tags and values Example data: Citi Bank . . / I would like to run the following query: select * from where to_tsvector('english',xml_column) @@ to_tsquery('Citi Bank') This works fine but

Re: [GENERAL] hardware for a server

A B wrote: Don't put SAS drives on a 3ware controller. They say that works now, but they haven't really gotten it right yet--their controllers are still only good with SATA drives. How bad will it be with SAS drives? Is there so little performance gain witn 3ware+SAS? The concern isn

Re: [GENERAL] hardware for a server

Bruce Momjian wrote: Greg Smith wrote: Given what you've said about your budget here, I suspect that you're heading toward either 3ware or LSI and all SATA drives. I wouldn't expect that big of a performance difference between the two with only 8 drives on there. If you had 24, the 3ware

Re: [GENERAL] hardware for a server

John R Pierce wrote: Vick Khera wrote: Interesting... same advice goes for Adaptec + FreeBSD. I guess Adaptec + !Windows == bad? i've never liked adaptec, windows or not. Yeah, I was trying to be as nice as possible since I don't run Windows anymore, and for all I know their cards are f