Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.
On 18 August 2010 06:30, Jose Ildefonso Camargo Tolosa wrote: > Hi, again, > > I just had this wacky idea, and wanted to share it: > > what do you think of having the dataset divided among several servers, > and sending the query to all of them, and then just have the > application "unify" the results from all the servers? > > Would that work for this kind of *one table* search? (there are no > joins, and will never be). I think it should, but: what do you think? There is a tool for this - http://plproxy.projects.postgresql.org/doc/tutorial.html > > Ildefonso. > > On Tue, Aug 17, 2010 at 9:51 PM, Jose Ildefonso Camargo Tolosa > wrote: >> Hi! >> >> I'm analyzing the possibility of using PostgreSQL to store a huge >> amount of data (around 1000M records, or so), and these, even >> though are short (each record just have a timestamp, and a string that >> is less than 128 characters in length), the strings will be matched >> against POSIX Regular Expressions (different regexps, and maybe >> complex). >> >> Because I don't have a system large enough to test this here, I have >> to ask you (I may borrow a medium-size server, but it would take a >> week or more, so I decided to ask here first). How is the performance >> of Regexp matching in PostgreSQL? Can it use indexes? My guess is: >> no, because I don't see a way of generally indexing to match regexp :( >> , so, tablescans for this huge dataset. >> >> What do you think of this? >> >> Sincerely, >> >> Ildefonso Camargo >> > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql out parameter with select into
Hi, SELECT column_name INTO var_name FROM ... 2010/8/17 Horváth Imre : > Hi! > > My question is, how can I get the out parameter from a function with > SELECT INTO by name? > I mean: > > create function testfunc1(OUT _status integer) returns integer as > $BODY$ > _status := 0; > $BODY$ > language plpgsql; > > create function testfunc2() as > declare > status integer; > $BODY$ > select into status * from testfunc1(); > $BODY$ > language plpgsql; > > create function testfunc3() as > declare > status integer; > $BODY$ > select into status _status from testfunc1(); > $BODY$ > language plpgsql; > > testfunc2 works, testfunc3 not. > > Thanks in advance: > Imre Horvath > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql out parameter with select into
Hi! My question is, how can I get the out parameter from a function with SELECT INTO by name? I mean: create function testfunc1(OUT _status integer) returns integer as $BODY$ _status := 0; $BODY$ language plpgsql; create function testfunc2() as declare status integer; $BODY$ select into status * from testfunc1(); $BODY$ language plpgsql; create function testfunc3() as declare status integer; $BODY$ select into status _status from testfunc1(); $BODY$ language plpgsql; testfunc2 works, testfunc3 not. Thanks in advance: Imre Horvath -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql out parameter with select into
Hello It cannot work, you mix the sql with plpgsql language 2010/8/17 Imre Horvath : > Hi! > > My question is, how can I get the out parameter from a function with > SELECT INTO by name? > I mean: > > create function testfunc1(OUT _status integer) returns integer as > $BODY$ > _status := 0; > $BODY$ > language plpgsql; > > create function testfunc2() as > declare > status integer; > $BODY$ > select into status * from testfunc1(); > $BODY$ > language plpgsql; > > create function testfunc3() as > declare > status integer; > $BODY$ > select into status _status from testfunc1(); > $BODY$ > language plpgsql; > > testfunc2 works, testfunc3 not. > > Thanks in advance: > Imre Horvath > > create or replace function test1(out _status integer) returns integer as $$ begin _status := 10; end; $$ language plpgsql; create or replace function test3() returns void as $$ declare status integer; begin select into status _status from test1(); raise notice '%', status; end; $$ language plpgsql; this working for me. postgres=# select test3(); NOTICE: 10 test3 ─── (1 row) Regards Pavel Stehule > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.
You can do something similar on the same machine if you can come up with a common way to partition your data. Then you split your 1B rows up into chunks of 10M or so and put each on a table and hit the right table. You can use partitioning / table inheritance if you want to, or just know the table name ahead of time. We did something similar with mnogo search. We break it up into a few hundred different schemas and hit the one for a particular site to keep the individual mnogo search tables small and fast. On Tue, Aug 17, 2010 at 8:30 PM, Jose Ildefonso Camargo Tolosa wrote: > Hi, again, > > I just had this wacky idea, and wanted to share it: > > what do you think of having the dataset divided among several servers, > and sending the query to all of them, and then just have the > application "unify" the results from all the servers? > > Would that work for this kind of *one table* search? (there are no > joins, and will never be). I think it should, but: what do you think? > > Ildefonso. > > On Tue, Aug 17, 2010 at 9:51 PM, Jose Ildefonso Camargo Tolosa > wrote: >> Hi! >> >> I'm analyzing the possibility of using PostgreSQL to store a huge >> amount of data (around 1000M records, or so), and these, even >> though are short (each record just have a timestamp, and a string that >> is less than 128 characters in length), the strings will be matched >> against POSIX Regular Expressions (different regexps, and maybe >> complex). >> >> Because I don't have a system large enough to test this here, I have >> to ask you (I may borrow a medium-size server, but it would take a >> week or more, so I decided to ask here first). How is the performance >> of Regexp matching in PostgreSQL? Can it use indexes? My guess is: >> no, because I don't see a way of generally indexing to match regexp :( >> , so, tablescans for this huge dataset. >> >> What do you think of this? >> >> Sincerely, >> >> Ildefonso Camargo >> > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- To understand recursion, one must first understand recursion. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.
Hi, again, I just had this wacky idea, and wanted to share it: what do you think of having the dataset divided among several servers, and sending the query to all of them, and then just have the application "unify" the results from all the servers? Would that work for this kind of *one table* search? (there are no joins, and will never be). I think it should, but: what do you think? Ildefonso. On Tue, Aug 17, 2010 at 9:51 PM, Jose Ildefonso Camargo Tolosa wrote: > Hi! > > I'm analyzing the possibility of using PostgreSQL to store a huge > amount of data (around 1000M records, or so), and these, even > though are short (each record just have a timestamp, and a string that > is less than 128 characters in length), the strings will be matched > against POSIX Regular Expressions (different regexps, and maybe > complex). > > Because I don't have a system large enough to test this here, I have > to ask you (I may borrow a medium-size server, but it would take a > week or more, so I decided to ask here first). How is the performance > of Regexp matching in PostgreSQL? Can it use indexes? My guess is: > no, because I don't see a way of generally indexing to match regexp :( > , so, tablescans for this huge dataset. > > What do you think of this? > > Sincerely, > > Ildefonso Camargo > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question about POSIX Regular Expressions performance on large dataset.
On Tue, Aug 17, 2010 at 8:21 PM, Jose Ildefonso Camargo Tolosa wrote: > Hi! > > I'm analyzing the possibility of using PostgreSQL to store a huge > amount of data (around 1000M records, or so), and these, even > though are short (each record just have a timestamp, and a string that > is less than 128 characters in length), the strings will be matched > against POSIX Regular Expressions (different regexps, and maybe > complex). > > Because I don't have a system large enough to test this here, I have > to ask you (I may borrow a medium-size server, but it would take a > week or more, so I decided to ask here first). How is the performance > of Regexp matching in PostgreSQL? Can it use indexes? My guess is: > no, because I don't see a way of generally indexing to match regexp :( > , so, tablescans for this huge dataset. > > What do you think of this? Yes it can index such things, but it has to index them in a fixed way. i.e. you can create functional indexes with pre-built regexes. But for ones where the values change each time, you're correct, no indexes will be used. Could full text searching be used instead? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question about POSIX Regular Expressions performance on large dataset.
Hi! I'm analyzing the possibility of using PostgreSQL to store a huge amount of data (around 1000M records, or so), and these, even though are short (each record just have a timestamp, and a string that is less than 128 characters in length), the strings will be matched against POSIX Regular Expressions (different regexps, and maybe complex). Because I don't have a system large enough to test this here, I have to ask you (I may borrow a medium-size server, but it would take a week or more, so I decided to ask here first). How is the performance of Regexp matching in PostgreSQL? Can it use indexes? My guess is: no, because I don't see a way of generally indexing to match regexp :( , so, tablescans for this huge dataset. What do you think of this? Sincerely, Ildefonso Camargo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Domains, casts, and MS Access
On Wed, Aug 4, 2010 at 8:49 PM, Richard Broersma wrote: > On Wed, Aug 4, 2010 at 3:41 PM, Peter Koczan wrote: > >> Yep, that's the stumbling block we're running into. ODBC and these >> fields' assumptions of true/false are at odds. I'm trying a few other >> things with casts in the meantime to see if they'll work. > > Well there is a solution that I've been toying around with. In > PostgreSQL, there are many data-types that cannot be expressed > directly in an MS-Access Linked table. For example, composite types, > arrays, range types, hstores, postgis types et.al. However, most of > these types can be decomposed in to base types that can be express in > linked tables. > > The key is using update-able views to decompose the data for Access > and re-assemble it before it transmitted back to the base table. The > same can be done for boolean datatype. > > >> Does anyone know if another product, like OpenOffice Base with its >> native postgres driver, does any better? > > From my limited experience, I believe is does do better. The > following blogs as a few entries about using Base: > > http://www.postgresonline.com/journal/archives/167-Importing-data-into-PostgreSQL-using-Open-Office-Base-3.2.html The goal of this is to be as straight a port as possible (as part of a larger project that's a pretty straight port). The update-able views and using Open Office are good ideas for when we finally get around to redesigning the database, whenever that will happen. Anyway, we got this mostly working. There were a couple other quirks we found that we have since fixed. - Access does not like LongVarChar types to be primary keys. If you are keying on text types, set TextAsLongVarchar=0. It's probably not the best idea to have text as a primary key in general, but sometimes that's what the legacy gives you to work with. - To fix the incompatibility in the bit/boolean type, we mapped the drop-down menu input to have "Yes" == 1 instead of -1. We had to do that for each input. It was tedious, but workable. Again, this was necessary because of legacy and the other workarounds we put in to account for it. Thanks for all your help. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plpgsql out parameter with select into
Hi! My question is, how can I get the out parameter from a function with SELECT INTO by name? I mean: create function testfunc1(OUT _status integer) returns integer as $BODY$ _status := 0; $BODY$ language plpgsql; create function testfunc2() as declare status integer; $BODY$ select into status * from testfunc1(); $BODY$ language plpgsql; create function testfunc3() as declare status integer; $BODY$ select into status _status from testfunc1(); $BODY$ language plpgsql; testfunc2 works, testfunc3 not. Thanks in advance: Imre Horvath -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
