Re: [GENERAL] PostgreSQL's hashing function?
Joshua D. Drake wrote: On Fri, 22 Feb 2008 16:49:10 -0500 "Kynn Jones" <[EMAIL PROTECTED]> wrote: Hi! Does PostgreSQL expose its hash function? I need a fast way to hash a string to a short code using characters in the set [A-Za-z0-9_]. Is md5 good enough? :) Probably not. He said fast. You could use hashtext() which is Postgres's internal hash function. There's some possibility it could change in future versions of Postgres though. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ts_headline
As it turns out, all I needed was in the doco but the key element - the first config arg to ts_headline - was not in any of the examples so I missed it. Would it be possible for ts_headline to work with the pre-parsed ts_vector? I see references to future plans for phrase searching in ts. Is there a date for this? Cheers and thanks, Stephen Davies On Friday 22 February 2008 22:54, Oleg Bartunov wrote: > On Fri, 22 Feb 2008, Stephen Davies wrote: > > H! > > I think I now understand the ts position better, thank you. > > > > Part of my problem has been that I am used to the functionality of Open > > Text's LCS (aka BASIS) product which handles text differently. > > > > It includes the position (and context) information in the index and does > > "remember" how the text was parsed so does not need to reparse to insert > > hit navigation tags nor need pointers as to how to parse queries. (It > > also supports phrase searching.) > > > > Now that I have a better understanding of ts, I think I will be able to > > make it do at least most of what I hoped for. > > I'm wondering if it was not described in the text search documentation :) > > > Thank you again for your help with this. > > > > Cheers, > > Stephen Davies > > > > On Friday 22 February 2008 20:45, Richard Huxton wrote: > >> Stephen Davies wrote: > >>> Unfortunately, my link to the box with the test database is down due to > >>> lack of maintenance by our local telco (Telstra) but I think that I > >>> also missed the optional config arg to ts_headline. > >>> > >>> The lack of link also means that I cannot confirm your findings but > >>> your logic looks good. > >> > >> Looks like ALTER DATABASE SET default_text_config='english' is what you > >> need. > >> > >>> It begs the question, however, as to why ts-headline needs to reparse > >>> the raw text. > >> > >> It needs to line up tsvector lexemes with actual characters in the text. > >> The tsvector is missing punctuation, any stopwords (the, it, a) as well > >> as being stemmed (if your dictionary does that). > >> > >> Also, it's looking for a short span of words that provide the best > >> match. That might not be a complete match of course, and is different to > >> how you'd normally look to use a tsvector. > >> > >>> At least in my case, I am using a trigger to parse the combination of > >>> Title and Abstract to a ts_vector field in the table row (as suggested > >>> in 12.2.2 and 12.4.3 in the doco) so that the ts_vector is already > >>> available to ts_headline. > >>> > >>> If ts_headline had the ability to use that pre-parsed ts_vector, my > >>> problem would never have arisen - and the performance of ts_headline > >>> would be improved. > >> > >> Maybe. It would still have to parse the text to some degree though, just > >> to get the original words & punctuation into the headline. > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 -- This email is for the person(s) identified above, and is confidential to the sender and the person(s). No one else is authorised to use or disseminate this email or its contents. Stephen Davies ConsultingVoice: 08-8177 1595 Adelaide, South Australia. Fax: 08-8177 0133 Computing & Network solutions. Mobile:0403 0405 83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Queries w/ "computed" table names? (eval in Pg?)
On Feb 22, 2008, at 3:40 PM, Kynn Jones wrote: Hi. Suppose I have a database that contains a "meta table" that holds the names of other the tables in the database, keyed by human- readable but longish strings. I would like to write queries that first "compute" the names of some tables (i.e. by looking them up in "meta table"), and after that they execute subqueries using these computed table names. The following invalid SQL illustrates the kind of maneuver I'd like to do: SELECT x, y, z FROM [ SELECT table_name FROM meta_table WHERE human_readable_key = 'some vry long and unwieldy string' ]; The stuff in [ brackets ] is not meant to be valid SQL, but rather to suggest that the name of the table for the "outer" query corresponds to the string returned by the "inner" (bracketed) query. Some programming languages allow the run-time evaluation of a string representing some code in the language. One way to do what I'd like to do is based on this idea: I would construct the source code for the desired subquery as a string (including the name of the table obtained at run-time from meta_table), and "somehow" evaluate this string. This "somehow" is what I'm missing. Is there a way in PostgreSQL to evaluate a string as SQL? You can do it from within pl/pgsql - see http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN I don't think there's any way to do it from plain sql, but you could probably create a small pl/pgsql wrapper function to do it. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Queries w/ "computed" table names? (eval in Pg?)
On Fri, 2008-02-22 at 18:40 -0500, Kynn Jones wrote: > Hi. Suppose I have a database that contains a "meta table" that holds > the names of other the tables in the database, keyed by human-readable > but longish strings. I would like to write queries that first > "compute" the names of some tables (i.e. by looking them up in "meta > table"), and after that they execute subqueries using these computed > table names. The following invalid SQL illustrates the kind of You can create a PL/pgSQL function and use "EXECUTE". Depending on how you want to use it, you may need to make it a set- returning function (a.k.a. table function). Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Queries w/ "computed" table names? (eval in Pg?)
Hi. Suppose I have a database that contains a "meta table" that holds the names of other the tables in the database, keyed by human-readable but longish strings. I would like to write queries that first "compute" the names of some tables (i.e. by looking them up in "meta table"), and after that they execute subqueries using these computed table names. The following invalid SQL illustrates the kind of maneuver I'd like to do: SELECT x, y, z FROM [ SELECT table_name FROM meta_table WHERE human_readable_key = 'some vry long and unwieldy string' ]; The stuff in [ brackets ] is not meant to be valid SQL, but rather to suggest that the name of the table for the "outer" query corresponds to the string returned by the "inner" (bracketed) query. Some programming languages allow the run-time evaluation of a string representing some code in the language. One way to do what I'd like to do is based on this idea: I would construct the source code for the desired subquery as a string (including the name of the table obtained at run-time from meta_table), and "somehow" evaluate this string. This "somehow" is what I'm missing. Is there a way in PostgreSQL to evaluate a string as SQL? TIA! kynn
Re: [GENERAL] PostgreSQL's hashing function?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 22 Feb 2008 16:49:10 -0500 "Kynn Jones" <[EMAIL PROTECTED]> wrote: > Hi! > Does PostgreSQL expose its hash function? I need a fast way to hash a > string to a short code using characters in the set [A-Za-z0-9_]. > (I'm not sure yet how long this code needs to be, but I think even > something as short as length 2 may be enough.) Is md5 good enough? :) select md5() > > TIA! > > Kynn - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHv0VjATb/zqfZUUQRAqUNAJ9zejT/pN1uIDtQYEQ7TQvt0LqFjACfelSy wNa9NNUN8rAfmeqwrKCvUb0= =H8DS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PostgreSQL's hashing function?
Hi! Does PostgreSQL expose its hash function? I need a fast way to hash a string to a short code using characters in the set [A-Za-z0-9_]. (I'm not sure yet how long this code needs to be, but I think even something as short as length 2 may be enough.) TIA! Kynn
Re: [GENERAL] Unique indicies
oops yeah... must have read it clearly rgds, dotyet On 2/22/08, Erik Jones <[EMAIL PROTECTED]> wrote: > > > On Feb 22, 2008, at 8:38 AM, Dot Yet wrote: > > > that would mean > > > > Every row in foo for column f1 has to be unique > > and > > Every row in foo for column f2 has to be unique > > The OP was asking about maintaining normal indexes on each in > addition to the unique index on (f1, f2), not separate unique indexes. > > Erik Jones > > DBA | Emma(R) > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > >
Re: [GENERAL] Understanding ps -ef "command" column
On Fri, 22 Feb 2008, David Jaquay wrote: In my case, I've got a connection that's hanging around after my code should have closed it, which means almost certainly that I've got problems in my code, but I'd love to be able to get that "57413" number from my jdbc object and write it to my logs to troubleshoot this. Any ideas? The JDBC driver has an option logUnclosedConnections[1] that can be used to find where you've neglected to close things. Any connection that gets cleaned up by the garbage collector logs the stacktrace of its creation, so you can see where it got built from. Kris Jurka [1] http://jdbc.postgresql.org/documentation/83/connect.html#connection-parameters ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Understanding ps -ef "command" column
On 2/22/08, David Jaquay <[EMAIL PROTECTED]> wrote: > Yeah, kinda guessed that. > > So there's no way (that you know of) to, say, cast my JDBC connection object > to something Postgresql'y and peer into its internals? The docs and the source code for the PG JDBC driver are freely available. Worst case you could add a method for fetching the Socket object and recompile the driver. -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Understanding ps -ef "command" column
On 23/02/2008, David Jaquay <[EMAIL PROTECTED]> wrote: > When I do a ps -ef, in the command column, I see: > > postgres: postgres dbname 10.170.1.60(57413) idle This doesn't resemble any "ps -ef" output I've ever seen. What OS is this on, what's the version of ps? Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Selecting large objects stored as bytea
On Fri, 22 Feb 2008, Daniel Verite wrote: > You could use the function below that breaks a bytea value into > pieces of 'chunksize' length and returns them as a set of rows. When you do this then make sure that this column has external storage: alter column [column_name] set storage external; Then getting this chunks will be fast. But be aware that external storage does not compress data, so you would need more space for database if your data is compressible. Also you'd need to dump, truncate and restore this table, as storage option is only honored for new rows. Maybe just "cluster [table_name]" will do, I don't know. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function problem
I solved my problem :)
the problem is in the query that calls my function:
select * from calcolo_inventario('26','2008-02-22','05')
where giacenza > 0
because the resulset has a negative row that doesn't appear on screen
Thank you very much to all of you
Enrico
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] joining on concatonation?
I have a query that is driving me nuts. In one table we have data that is split between two columns and I'm trying to pull in all values from another table where that column is represented by one piece of data. Also, all the info in column2 is unique but not in col 1. table1 col1_pfx col2_number col3 Table2 col1 col3 update table1 set col3=(select col3 from table2 where table2.col1=( table1.col1_pfx || table1.col2_number)); I've tried using a join and all other methods and nothing seems to work. Table2 has a primary key on col1 yet when I do explain posgres still seems to do a sqential scan on that column. Any thoughts? Thanks!
Re: [GENERAL] Function problem
Enrico wrote: Hi all, I have this piece of code DECLARE minv my_inv; r record; totale numeric(20,5); valore numeric(20,5); BEGIN [.] totale := 0; for r in select * from tminv loop [.] valore := r.prezzo*r.giacenza; totale := totale+valore; minv.totale = totale; return next minv; end loop; return; [..] So the first record has valore=98 and totale=0 for the first time of the loop, but my first result of totale is -298 instead of +98. minv.totale := totale; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Understanding ps -ef "command" column
On Feb 22, 2008, at 10:28 AM, Douglas McNaught wrote: On 2/22/08, David Jaquay <[EMAIL PROTECTED]> wrote: When I do a ps -ef, in the command column, I see: postgres: postgres dbname 10.170.1.60(57413) idle I get all of this, except the "57413". What does this mean, and more importantly, how can I tie that number back to a connection that I've acquired via JDBC? At a guess, it's the ephemeral port number used by the client connection. It might be hard to track back in Java because I don't think the JDBC driver gives you access to the underlying Socket object (which you could query to find out its local port). See the lsof unix tool for a good way to track which processes are communicating via that port number. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Function problem
On Fri, 22 Feb 2008 11:51:01 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Enrico <[EMAIL PROTECTED]> writes: > > the first record has valore=98 and totale=0 for the first time of > > the loop, but my first result of totale is -298 instead of +98. > > Hmm, that's a bit hard to believe. Yes you right, it's hard to believe for me too :) > Could we see the whole example > not just a fragment? This is my code : - CREATE TYPE my_inv AS (codice_art character(15), codice_agg character(20), descr character varying(60), gruppo smallint, giacenza numeric(20,5), prezzo_lis numeric(20,5), prezzo numeric(20,5), valore numeric(20,5), totale numeric (20,5)); ALTER TYPE my_inv OWNER TO postgres; CREATE OR REPLACE FUNCTION calcolo_inventario(bpchar, date, bpchar) RETURNS SETOF my_inv AS $BODY$ DECLARE minv my_inv; r record; flag int4; tot numeric(20,5); valore numeric(20,5); BEGIN create temp table tminv ( codice_art char(13), codice_agg char(20), descr char(60), gruppo int4, giacenza numeric(20,5), prezzo_lis numeric(20,5), prezzo numeric(20,5) ); insert into tminv (codice_art,descr,gruppo,giacenza,prezzo_lis,prezzo) select distinct S.codice_art, A.descr,A.gruppo,A.giacenza,L.prezzo as prezzo_lis,avg(S.importo) as prezzo from scrittura_magazzino S inner join anag_art A on (S.codice_art=A.codice_art) left join ean13 E on (S.codice_art=E.codice_art) inner join listini L on (s.codice_art=L.codice_art) where S.c_s='C' and L.listino = $1 and data_doc <= $2 group by 1,2,3,4,5; update tminv set codice_agg = CA.codice_agg from codici_aggiuntivi CA where tminv.codice_art = CA.codice_art and CA.tipo_cod = $3; tot := 0; for r in select * from tminv loop minv.codice_art := r.codice_art; minv.codice_agg := r.codice_agg; minv.descr := r.descr; minv.gruppo := r.gruppo; minv.giacenza := r.giacenza; minv.prezzo_lis := r.prezzo_lis; minv.prezzo := r.prezzo; valore := r.prezzo*r.giacenza; minv.valore := valore; tot := tot+valore; minv.totale := tot; return next minv; end loop; return; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; --- There is then another question if I write [] for r in select * from tminv loop tot := 0; [] the result is correct, but if I want to sum the results with [] tot := 0; for r in select * from tminv loop [] the result is not correct Thanks for your time :) Enrico -- One small feel for man, one giant ass for mankind (Dr. Gregory House) Enrico Pirozzi Web: http://www.enricopirozzi.info E-Mail: [EMAIL PROTECTED] Skype: sscotty71 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Understanding ps -ef "command" column
On the one hand, that's pretty cool. I keep forgetting that's out there. On the other hand, I know what process is holding the connection; it's the only one on the box connecting to that server. So lsof doesn't let me connect a process on the server to a connection object (one of many) on the client. Thanks just the same, tho, Dave On Fri, Feb 22, 2008 at 11:55 AM, Erik Jones <[EMAIL PROTECTED]> wrote: > > On Feb 22, 2008, at 10:28 AM, Douglas McNaught wrote: > > > On 2/22/08, David Jaquay <[EMAIL PROTECTED]> wrote: > >> When I do a ps -ef, in the command column, I see: > >> > >> postgres: postgres dbname 10.170.1.60(57413) idle > >> > >> I get all of this, except the "57413". What does this mean, and more > >> importantly, how can I tie that number back to a connection that I've > >> acquired via JDBC? > > > > At a guess, it's the ephemeral port number used by the client > > connection. It might be hard to track back in Java because I don't > > think the JDBC driver gives you access to the underlying Socket object > > (which you could query to find out its local port). > > See the lsof unix tool for a good way to track which processes are > communicating via that port number. > > Erik Jones > > DBA | Emma(R) > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > >
Re: [GENERAL] Function problem
Enrico <[EMAIL PROTECTED]> writes: > the first record has valore=98 and totale=0 for the first time of > the loop, but my first result of totale is -298 instead of +98. Hmm, that's a bit hard to believe. Could we see the whole example not just a fragment? Usually, when you can't understand the problem, it's because you're looking in the wrong place --- so a partial example is tough to debug. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Understanding ps -ef "command" column
Yeah, kinda guessed that. So there's no way (that you know of) to, say, cast my JDBC connection object to something Postgresql'y and peer into its internals? Thanks, Dave On Fri, Feb 22, 2008 at 11:28 AM, Douglas McNaught <[EMAIL PROTECTED]> wrote: > On 2/22/08, David Jaquay <[EMAIL PROTECTED]> wrote: > > When I do a ps -ef, in the command column, I see: > > > > postgres: postgres dbname 10.170.1.60(57413) idle > > > > I get all of this, except the "57413". What does this mean, and more > > importantly, how can I tie that number back to a connection that I've > > acquired via JDBC? > > At a guess, it's the ephemeral port number used by the client > connection. It might be hard to track back in Java because I don't > think the JDBC driver gives you access to the underlying Socket object > (which you could query to find out its local port). > > -Doug >
Re: [GENERAL] text and bytea
Martijn van Oosterhout escribió: > The most surprising this is that to_ascii won't accept a bytea. TBH the > whole to_ascii function seems somewhat half-baked. If what you're > trying to do is remove accents, there are perl functions around that do > that. Basically, the switch to a different normal form then drop all > the accent characters. Yeah, it's been a common suggestion to use convert() in combination with to_ascii on UTF-8 databases, and I didn't notice that the convert() shuffling would take that ability away :-( I don't think requiring plperl is nice however. Perhaps we could get around the problem by using byteaout/textin. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Understanding ps -ef "command" column
On 2/22/08, David Jaquay <[EMAIL PROTECTED]> wrote: > When I do a ps -ef, in the command column, I see: > > postgres: postgres dbname 10.170.1.60(57413) idle > > I get all of this, except the "57413". What does this mean, and more > importantly, how can I tie that number back to a connection that I've > acquired via JDBC? At a guess, it's the ephemeral port number used by the client connection. It might be hard to track back in Java because I don't think the JDBC driver gives you access to the underlying Socket object (which you could query to find out its local port). -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Understanding ps -ef "command" column
When I do a ps -ef, in the command column, I see: postgres: postgres dbname 10.170.1.60(57413) idle I get all of this, except the "57413". What does this mean, and more importantly, how can I tie that number back to a connection that I've acquired via JDBC? In my case, I've got a connection that's hanging around after my code should have closed it, which means almost certainly that I've got problems in my code, but I'd love to be able to get that "57413" number from my jdbc object and write it to my logs to troubleshoot this. Any ideas? Thanks, Dave
Re: [GENERAL] need some help on figuring out how to write a query
I'm not after small code i'm after maintainable code where the date range for this report would be always changing. So if i can get it to one select statement i would on have to pass in some variables and it would create the moving average. Plus what if the sales people decide they want to change moving average from 10 weeks to 5 weeks or change it to 15 weeks. People drive me nuts with i want it to do this or that , Of course they have no idea how complicated it sometimes to get what they want. Thanks you for your ideas Dean and Jorge gives me some ideas to play with. Jorge Godoy wrote: Em Thursday 21 February 2008 18:37:47 Justin escreveu: Now i could write a function to do this or do it in C++ program that creates query with all kinds of unions. I'm wondering if there is a way to create this in a single select statement?? I can't think of a way to do it??? Why you need it in one query? Think of maintenability not on code size. Solve the problem in parts, calculating it for one week -- or ten, you can use the interval type -- and then moving on... The function would look like: WHILE start_date + '10 weeks'::interval < today: SELECT INTO weeks_avg avg(value) FROM table WHERE date BETWEEN start_date AND start_date+'10 weeks'::interval; start_date:=start_date + '1 week'::interval; END WHILE; Start from that and you'll have it done. (Of course, above is pseudo code and untested...)
[GENERAL] Function problem
Hi all, I have this piece of code DECLARE minv my_inv; r record; totale numeric(20,5); valore numeric(20,5); BEGIN [.] totale := 0; for r in select * from tminv loop [.] valore := r.prezzo*r.giacenza; totale := totale+valore; minv.totale = totale; return next minv; end loop; return; [..] So the first record has valore=98 and totale=0 for the first time of the loop, but my first result of totale is -298 instead of +98. Any suggestion? Thanks Regards Enrico -- One small feel for man, one giant ass for mankind (Dr. Gregory House) Enrico Pirozzi Web: http://www.enricopirozzi.info E-Mail: [EMAIL PROTECTED] Skype: sscotty71 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Selecting large objects stored as bytea
Ludger Zachewitz wrote: 'ResultSet rs = statement.executeQuery(selectClause);' After increase of HEAP in java it works, but also the java needs much memory, as I don't expect it. I have also tried to substitute this command line by prepared-statement like 'PreparedStatement ps = this.dbConnection.prepareStatement(selectClause);' Do have anyone a solution for that problem? You could use the function below that breaks a bytea value into pieces of 'chunksize' length and returns them as a set of rows. Syntax of call: SELECT * FROM chunks((SELECT subquery that returns one bytea column), 1024*1024) CREATE OR REPLACE FUNCTION chunks (contents bytea,chunksize int) RETURNS SETOF bytea AS $$ DECLARE length int; current int; chunk bytea; BEGIN IF contents IS NULL THEN RETURN NEXT NULL; RETURN; END IF; SELECT octet_length(contents) INTO length; current:=1; LOOP SELECT substring(contents FROM current FOR chunksize) INTO chunk; RETURN NEXT chunk; current:=current+chunksize; EXIT WHEN current>=length; END LOOP; RETURN; END; $$ language 'plpgsql'; Another option would be not to use that function, but instead implement its logic in your client-side code (multiple SELECTs in a loop). I expect this would lessen the server-side memory consumption. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] /var/lib/postgres on Hitachi E5K160 60 GByte (Raid-1 + Hotfix)
Hello, I am building a new small PostgreSQL Server for my Solar-Powered Mobil- Office based on a Mini-ITX "VIA EPIA LN1EAG" with 1 GByte of memory where the OS (Debian GNU/Linux) is in three Hitachi TravelStar 20 GByte (in a Raid-1 + Hotfix). I am normaly working alone, but maybe the server would have connections from up to 8 Workstations which are not realy heavy... The controller is a 3Ware 3w8500S-8LP And now it comes: Does anyone has an experience on the special drives which can run 24/7 like the "Hitachi TravelStar E5K160 60 GByte"? I like to put three of them into a Raid-1 + Hotfix. The server is already running but /var/lib/postgres is currently on three Western Digital Raptor WD740 which consuming over 45 Watt andare laud liek an Combat-Aircraft... Three E5K160 would only consume arround 18 Watt. Any Experiences? Thanks, Greetings and nice Day Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Querying the schema for column widths - what syntax do I use?
On Fri, Feb 22, 2008 at 11:38:33AM +, Howard Wilkinson wrote: > The package is a DNS server originally hosted on top of a MySQL data > base. I am extending it in a number of ways, but this particular need > arises as I need to store some data (binary in nature) in a field that > is part of the key for an index. The data is < 65536 in length. However, > most of the time it is <<65536 e.g. 4 bytes. I also need to support > backwards compatibility with the previous releases which had limited > storage capacity in this field. Just remember that if you're going to index the data that a btree index will handle a maximum of 2700 *bytes* which, depending on your encoding might be many less characters. This is independant of the maximum size of the datatype. How you deal with this depends on why exactly you want it indexed. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Mac ordering with locales
On Feb 22, 2008, at 10:16 AM, Martijn van Oosterhout wrote: On Thu, Feb 21, 2008 at 11:14:58AM -0800, Jeff Davis wrote: I have looked for a standard related to the locale behavior and I was surprised that I couldn't find one. Is a given locale, e.g. en_US, supposed to have identical behavior on any platform for which it's available? If there is a standard of some kind, is apple violating it? Nope. If there were we could complain about it. All we have now is many different implementations. The most commonly used ones are Java, ICU, glibc and Windows. AIUI all except Windows understand the xx_XX format. Java and ICU are essentially the same. I found a note that both Perl6 and PHP6 may use ICU. That would be an interesting change. Darwin also uses ICU extensively. Is it that time of year again to discuss using/linking against it? Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unique indicies
On Feb 22, 2008, at 8:38 AM, Dot Yet wrote: that would mean Every row in foo for column f1 has to be unique and Every row in foo for column f2 has to be unique The OP was asking about maintaining normal indexes on each in addition to the unique index on (f1, f2), not separate unique indexes. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] text and bytea
On Thu, Feb 21, 2008 at 02:34:15PM -0200, hernan gonzalez wrote: > (After dealing a while with this, and learning a little, I though of > post this as comment in the docs, but perhaps someone who knows better > can correct or clarify) It seems to me that postgres is trying to do as you suggest: text is characters and bytea is bytes, like in Java. You don't indicate what version you are using, this area was rejigged recently. The most surprising this is that to_ascii won't accept a bytea. TBH the whole to_ascii function seems somewhat half-baked. If what you're trying to do is remove accents, there are perl functions around that do that. Basically, the switch to a different normal form then drop all the accent characters. Essentially, Postgres supports UTF-8, but doesn't understand Unicode characters much at all. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] current_query pg_stat_activity column
On Fri, Feb 22, 2008 at 11:50:51AM -0300, Carlos H. Reimer wrote: > Hi, > > I´ve noticed in my Fedora Core 6 box running PG 8.2.3 that column > current_query of pg_stat_activity view is not showing the complete query. Yeah, it can't show very long queries. I forget what the length restriction is, but I know there is one. BTW, you likely want to upgrade to the latest stable version in the 8.2 series, which is 8.2.6. A ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] configure build flags
On Fri, Feb 22, 2008 at 03:29:31PM +0100, [EMAIL PROTECTED] wrote: > Hi All. > Anyone knows if rebuilding the postgresql sources with the flag > --without-tcl --without-perl in ./configure filecan prevent the correct use > of the triggers wrote in plpgsql language? PL/pgSQL has nothing to do with TCL or Perl. It will make no difference in that respect. A ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Function parameters change when update to 8.3
tuanhoanganh <[EMAIL PROTECTED]> writes: > I write script in Query of pgAdminIII > ... > In functions, I have a new function test. But when I choose "CREATE > script" with function test the script change to > -- Function: test(ud_stt_rec(24)) > -- DROP FUNCTION test(ud_stt_rec(24)); > CREATE OR REPLACE FUNCTION test(ud_stt_rec(24)) I'd say this is a pgadmin bug, which you should report on the pgadmin mailing lists. But first, are you using the latest version of pgadmin? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Mac ordering with locales
On Thu, Feb 21, 2008 at 11:14:58AM -0800, Jeff Davis wrote: > I have looked for a standard related to the locale behavior and I was > surprised that I couldn't find one. Is a given locale, e.g. en_US, > supposed to have identical behavior on any platform for which it's > available? > > If there is a standard of some kind, is apple violating it? Nope. If there were we could complain about it. All we have now is many different implementations. The most commonly used ones are Java, ICU, glibc and Windows. AIUI all except Windows understand the xx_XX format. Java and ICU are essentially the same. I found a note that both Perl6 and PHP6 may use ICU. That would be an interesting change. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Unique indicies
Naz Gassiep <[EMAIL PROTECTED]> writes: > If you have an index like this: > CREATE UNIQUE INDEX foo ON tablename (f1, f2); > Is there any value in having independent indicies on f1 and f2 as well > or are they unnecessary? See http://www.postgresql.org/docs/8.3/static/indexes.html particularly sections 11.3 and 11.5 regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] current_query pg_stat_activity column
Hi, I´ve noticed in my Fedora Core 6 box running PG 8.2.3 that column current_query of pg_stat_activity view is not showing the complete query. The complete query: _ SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS, (SELECT DESMAR FROM TT_MAR MAR WHERE INT.FILMAR = MAR.FILMAR AND INT.CODMAR = MAR.CODMAR AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR, INT.DESCREVE, CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade') THEN INT.PROMOC ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X'),'.' ,','),'X','.')) FROM TT_PRE PRE WHERE PRE.FILPRE = '001' AND PRE.CODPRE = '1' AND PRE.FILMAT = INT.FILMAT AND PRE.CODMAT = INT.CODMAT) ELSE 'Ver Grade' END END AS PRECO, CASE WHEN INT.PROMOC = 'Ver Grade' THEN 'Ver Grade' WHEN INT.PROMOC = '0' THEN 'Não' ELSE 'Sim' END AS PROMOC FROM ( SELECT PRO.DESMAT, PRO.ESPMAT, PRO.MONTAR, PRO.DECIMA, PRO.REFBAS, PRO.FILMAR, PRO.CODMAR, PRO.ITEMAR, PRO.FILMAT,PRO.CODMAT,PRO.CODGRA, COALESCE(RPad(PRO.DESMAT,30),'') || COALESCE(RPad(PRO.ESPMAT,30),'') || COALESCE(RPad(PRO.REFBAS,15),'') AS DESCREVE, COALESCE(CASE WHEN PRO.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(CPM.PRECOP,'9,999,999.99'),',','X'),'.', ','),'X','.')) FROM TT_CPM CPM INNER JOIN TT_PRE PRE ON CPM.FILPRE=PRE.FILPRE AND CPM.SEQPRE=PRE.SEQUEN AND COALESCE(CPM.FILPRO,'003') = '003' WHERE PRE.FILMAT = PRO.FILMAT AND PRE.CODMAT = PRO.CODMAT AND PRE.FILPRE = '001' AND PRE.CODPRE = '1' AND CPM.DATVAL >= TRUNC( AGORA()) AND (( CPM.FILPGT = '001' AND CPM.CODPGT = ' 1') OR (CPM.FILPGT IS NULL)) ) ELSE 'Ver Grade' END,'0') AS PROMOC FROM TT_PRO PRO WHERE PRO.SIGNAT >= 0 AND PRO.FILMAT = PRO.FILMAT AND PRO.CODMAT = PRO.CODMAT AND EXISTS (SELECT 1 AS OK FROM TT_GRA GRA WHERE GRA.FILMAT = PRO.FILMAT AND GRA.CODMAT = PRO.CODMAT AND GRA.GRAATI = 'T') ORDER BY PRO.DESMAT, PRO.ESPMAT, PRO.REFBAS ) INT _ The output from the select current_query from pg_stat_activity: _ SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS, (SELECT DESMAR FROM TT_MAR MAR WHERE INT.FILMAR = MAR.FILMAR AND INT.CODMAR = MAR.CODMAR AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR, INT.DESCREVE, CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade') THEN INT.PROMOC ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X '),'.',','),'X','.')) FROM TT_PRE PRE WHERE PRE.FILPRE = '001' AND PRE.CODPRE = '2' AND PRE.FILMAT = INT.FILMAT AND PRE.CODMAT = INT.CODMAT) ELSE 'Ver Grade' END END AS PRECO, CASE WHEN INT.PROMOC = 'Ver Grade' THEN ' _ Am I missing anything here? Thank you in advance! Reimer
Re: [GENERAL] Unique indicies
that would mean Every row in foo for column f1 has to be unique and Every row in foo for column f2 has to be unique Say for example: create table test1 ( a int, b int ) ; create unique index idx_t1_a on test1(a) ; create unique index idx_t1_b on test1(b) ; insert into test1 values (1,1) ; -- OK insert into test1 values (1,2) ; -- FAIL insert into test1 values (2,2) ; -- OK insert into test1 values (2,1) ; -- FAIL This is because the each record is composed to two unique columns, and the uniqueness is broken down to each column, rather than the record as a whole. In case when you are creating the index as: create unique index idx_t1_ab on test1(a, b) ; insert into test1 values (1,1) ; -- OK insert into test1 values (1,2) ; -- OK insert into test1 values (2,2) ; -- OK insert into test1 values (2,1) ; -- OK This is because, each combination of column a and column b is unique in it's entirety. hth, dotyet On Fri, Feb 22, 2008 at 6:53 AM, Naz Gassiep <[EMAIL PROTECTED]> wrote: > If you have an index like this: > > CREATE UNIQUE INDEX foo ON tablename (f1, f2); > > Is there any value in having independent indicies on f1 and f2 as well > or are they unnecessary? > > Thanks > - Naz. > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings >
[GENERAL] configure build flags
Hi All. Anyone knows if rebuilding the postgresql sources with the flag --without-tcl --without-perl in ./configure filecan prevent the correct use of the triggers wrote in plpgsql language? Wich behaviour I've to expect in postgres using these flags?Thanks in advance.LucaEmail.it, the professional e-mail, gratis per te: clicca qui Sponsor:Polizze per vacanze e settimane bianche. Acquista online! Clicca qui -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: RILASSATINAVIGA CON NOI SU UN MARE DI OFFERTE E NOVITA'!!! www.grandinettisport.com Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=7509&d=20080222
[GENERAL]
Hi All. Anyone knows if rebuilding the postgresql sources with the flag --without-tcl --without-perl in ./configure filecan prevent the correct use of the triggers wrote in plpgsql language? Wich behaviour I've to expect in postgres using these flags?Thanks in advance.Luca -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Polizze per vacanze e settimane bianche. Acquista online! Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=7598&d=20080222
Re: [GENERAL] ts_headline
On Fri, 22 Feb 2008, Stephen Davies wrote: H! I think I now understand the ts position better, thank you. Part of my problem has been that I am used to the functionality of Open Text's LCS (aka BASIS) product which handles text differently. It includes the position (and context) information in the index and does "remember" how the text was parsed so does not need to reparse to insert hit navigation tags nor need pointers as to how to parse queries. (It also supports phrase searching.) Now that I have a better understanding of ts, I think I will be able to make it do at least most of what I hoped for. I'm wondering if it was not described in the text search documentation :) Thank you again for your help with this. Cheers, Stephen Davies On Friday 22 February 2008 20:45, Richard Huxton wrote: Stephen Davies wrote: Unfortunately, my link to the box with the test database is down due to lack of maintenance by our local telco (Telstra) but I think that I also missed the optional config arg to ts_headline. The lack of link also means that I cannot confirm your findings but your logic looks good. Looks like ALTER DATABASE SET default_text_config='english' is what you need. It begs the question, however, as to why ts-headline needs to reparse the raw text. It needs to line up tsvector lexemes with actual characters in the text. The tsvector is missing punctuation, any stopwords (the, it, a) as well as being stemmed (if your dictionary does that). Also, it's looking for a short span of words that provide the best match. That might not be a complete match of course, and is different to how you'd normally look to use a tsvector. At least in my case, I am using a trigger to parse the combination of Title and Abstract to a ts_vector field in the table row (as suggested in 12.2.2 and 12.4.3 in the doco) so that the ts_vector is already available to ts_headline. If ts_headline had the ability to use that pre-parsed ts_vector, my problem would never have arisen - and the performance of ts_headline would be improved. Maybe. It would still have to parse the text to some degree though, just to get the original words & punctuation into the headline. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] need some help on figuring out how to write a query
Em Thursday 21 February 2008 18:37:47 Justin escreveu: > Now i could write a function to do this or do it in C++ program that > creates query with all kinds of unions. I'm wondering if there is a way > to create this in a single select statement?? > I can't think of a way to do it??? Why you need it in one query? Think of maintenability not on code size. Solve the problem in parts, calculating it for one week -- or ten, you can use the interval type -- and then moving on... The function would look like: WHILE start_date + '10 weeks'::interval < today: SELECT INTO weeks_avg avg(value) FROM table WHERE date BETWEEN start_date AND start_date+'10 weeks'::interval; start_date:=start_date + '1 week'::interval; END WHILE; Start from that and you'll have it done. (Of course, above is pseudo code and untested...) -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL 8.3.0 RPMs are available for download
Em Thursday 21 February 2008 14:33:03 Julio Cesar Leiva escreveu: > I found the RPMs for postgresql8.3 suse here > > http://software.opensuse.org/search > > just type postgresql and choose your O.S But then, there are no PL and contrib and whatever packages to go with it. It is still just the client and server, without the bells and whistles :-) If you use other packages that depend on PG you can't use these. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ts_headline
H! I think I now understand the ts position better, thank you. Part of my problem has been that I am used to the functionality of Open Text's LCS (aka BASIS) product which handles text differently. It includes the position (and context) information in the index and does "remember" how the text was parsed so does not need to reparse to insert hit navigation tags nor need pointers as to how to parse queries. (It also supports phrase searching.) Now that I have a better understanding of ts, I think I will be able to make it do at least most of what I hoped for. Thank you again for your help with this. Cheers, Stephen Davies On Friday 22 February 2008 20:45, Richard Huxton wrote: > Stephen Davies wrote: > > Unfortunately, my link to the box with the test database is down due to > > lack of maintenance by our local telco (Telstra) but I think that I also > > missed the optional config arg to ts_headline. > > > > The lack of link also means that I cannot confirm your findings but your > > logic looks good. > > Looks like ALTER DATABASE SET default_text_config='english' is what you > need. > > > It begs the question, however, as to why ts-headline needs to reparse the > > raw text. > > It needs to line up tsvector lexemes with actual characters in the text. > The tsvector is missing punctuation, any stopwords (the, it, a) as well > as being stemmed (if your dictionary does that). > > Also, it's looking for a short span of words that provide the best > match. That might not be a complete match of course, and is different to > how you'd normally look to use a tsvector. > > > At least in my case, I am using a trigger to parse the combination of > > Title and Abstract to a ts_vector field in the table row (as suggested in > > 12.2.2 and 12.4.3 in the doco) so that the ts_vector is already available > > to ts_headline. > > > > If ts_headline had the ability to use that pre-parsed ts_vector, my > > problem would never have arisen - and the performance of ts_headline > > would be improved. > > Maybe. It would still have to parse the text to some degree though, just > to get the original words & punctuation into the headline. -- This email is for the person(s) identified above, and is confidential to the sender and the person(s). No one else is authorised to use or disseminate this email or its contents. Stephen Davies ConsultingVoice: 08-8177 1595 Adelaide, South Australia. Fax: 08-8177 0133 Computing & Network solutions. Mobile:0403 0405 83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Unique indicies
If you have an index like this: CREATE UNIQUE INDEX foo ON tablename (f1, f2); Is there any value in having independent indicies on f1 and f2 as well or are they unnecessary? Thanks - Naz. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Querying the schema for column widths - what syntax do I use?
Tino Wildenhain wrote: Hi Howard, Howard Wilkinson wrote: I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold. In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" command. And then parse the result for the length in the type column of the row returned. How would I do a similar function using PostgreSQL - I have tried to find this in the manuals and in this mailing list but not found any pointers to get me started. Apologies for asking such a simple question but I am being a bit lazy as I want to get on with releasing the MyDNS code. beside the correct answers you got relating the informational_schema, since I do not know what MyDNS is and what you are doing with the maximum size of the column, are you aware that postgresql bails out if you put in a string which exceeds the column size (so you can just try rather then check beforehand if thats what you do) or you also get the description in the cursor when you do the select on a table. Also, text type could be used to hold potentially large strings without harm (so if the string is short, its no difference but you can easily go up to over a gig) Regards Tino The package is a DNS server originally hosted on top of a MySQL data base. I am extending it in a number of ways, but this particular need arises as I need to store some data (binary in nature) in a field that is part of the key for an index. The data is < 65536 in length. However, most of the time it is <<65536 e.g. 4 bytes. I also need to support backwards compatibility with the previous releases which had limited storage capacity in this field. I have therefore chosen to detect when the field overflows the maximum storage capacity for the 'data' column and split the data into 'data' (truncated) and 'edata' (the rest). As I do not know what size the 'data' field is I needed to detect it dynamically and do the split before storing the data/edata. I have defined a further column 'edatakey' which takes an MD5 has of 'edata' when present and is included in the index on the table. It all seems to be working on MySQL 5.0.46 - have not tested the MD5 code yet - and I hope will with PostgreSQL when somebody tries it. Howard. ---(end of broadcast)--- TIP 1: 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] Selecting large objects stored as bytea
Ludger Zachewitz wrote: Hello, I have the following problem accessing postgres (version: 8.2.X) from java 1.6_03 using driver (currently version: postgresql-8.2-508.jdbc4.jar) and hope someone can help me. I have created an table 'files' with the columns 'file_id', 'filename' and 'file_content'. 'file_content'is of type bytea and should store large binary files. A method for storing files in that table also exists and work fine. Now I am trying to select a stored 'file_content' from the table and store it directly on the filesystem. Because the files could be very large, i don't want to hold them in the main memory -- so I am using Input- and Outputstreams (see source code below). If you don't want the whole field at once, you don't want a bytea. The large-object interface offers lo_read/lo_write to let you grab data in smaller chunks. See the manuals for details, I'm afraid I'm not sure of the procedure for accessing lo_read from JDBC. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Selecting large objects stored as bytea
Hello,
I have the following problem accessing postgres (version: 8.2.X) from java
1.6_03 using driver (currently version: postgresql-8.2-508.jdbc4.jar) and hope
someone can help me. I have created an table 'files' with the columns
'file_id', 'filename' and 'file_content'. 'file_content'is of type bytea and
should store large binary files. A method for storing files in that table also
exists and work fine.
Now I am trying to select a stored 'file_content' from the table and store it
directly on the filesystem. Because the files could be very large, i don't want
to hold them in the main memory -- so I am using Input- and Outputstreams (see
source code below).
When i am now selecting a file_content (of only 50 MByte -- according to the
pgsql documentation files of 1 GByte are supported) the running of the source
code below results in a 'Out of Memory Exception' at the command line
'ResultSet rs = statement.executeQuery(selectClause);'
After increase of HEAP in java it works, but also the java needs much memory,
as I don't expect it. I have also tried to substitute this command line by
prepared-statement like
'PreparedStatement ps = this.dbConnection.prepareStatement(selectClause);'
Do have anyone a solution for that problem?
Thanks for any help.
Regards,
Ludger Zachewitz
SOURCE-CODE:
public void selectLargeObject(String selectClause, File file)
throws SQLException, IOException {
InputStream in = null;
this.dbConnection.setAutoCommit(false);
Statement statement = this.dbConnection.createStatement(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = statement.executeQuery(selectClause);
if (rs != null) {
while (rs.next()) {
in = rs.getBinaryStream('file_content');
}
FileOutputStream fos = new FileOutputStream(file);
int readBytes = 0;
byte[] value = new byte[1024];
while ((readBytes = in.read(value)) > 0) {
fos.write(value, 0, readBytes);
}
fos.flush();
fos.close();
rs.close();
}
statement.close();
}
--
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: 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] selective backup and restore
Hi, [EMAIL PROTECTED] wrote: Hi All. Is it possible in postgreSQL 8.2.4, using pg_dump.exe, make a backup/restore conditioned to the key of some tables (respecting existing constraints)? You can either use the -t option or with a full backup in custom format you can pick a few objects (tables, views, ...) by creating the list with pg_restore -l and edit it in a text editor and then use -L to extract the DDL/DML for these objects. See: http://www.postgresql.org/docs/8.3/static/app-pgdump.html -t table --table=table for pg_dump and http://www.postgresql.org/docs/8.3/static/app-pgrestore.html -F format --format=format (use t or c) and -l --list List the contents of the archive. The output of this operation can be used with the -L option to restrict and reorder the items that are restored. -L list-file --use-list=list-file Restore elements in list-file only, and in the order they appear in the file. Lines can be moved and can also be commented out by placing a ; at the start of the line. (See below for examples.) Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ts_headline
Stephen Davies wrote: Unfortunately, my link to the box with the test database is down due to lack of maintenance by our local telco (Telstra) but I think that I also missed the optional config arg to ts_headline. The lack of link also means that I cannot confirm your findings but your logic looks good. Looks like ALTER DATABASE SET default_text_config='english' is what you need. It begs the question, however, as to why ts-headline needs to reparse the raw text. It needs to line up tsvector lexemes with actual characters in the text. The tsvector is missing punctuation, any stopwords (the, it, a) as well as being stemmed (if your dictionary does that). Also, it's looking for a short span of words that provide the best match. That might not be a complete match of course, and is different to how you'd normally look to use a tsvector. At least in my case, I am using a trigger to parse the combination of Title and Abstract to a ts_vector field in the table row (as suggested in 12.2.2 and 12.4.3 in the doco) so that the ts_vector is already available to ts_headline. If ts_headline had the ability to use that pre-parsed ts_vector, my problem would never have arisen - and the performance of ts_headline would be improved. Maybe. It would still have to parse the text to some degree though, just to get the original words & punctuation into the headline. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Querying the schema for column widths - what syntax do I use?
Hi Howard, Howard Wilkinson wrote: I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold. In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" command. And then parse the result for the length in the type column of the row returned. How would I do a similar function using PostgreSQL - I have tried to find this in the manuals and in this mailing list but not found any pointers to get me started. Apologies for asking such a simple question but I am being a bit lazy as I want to get on with releasing the MyDNS code. beside the correct answers you got relating the informational_schema, since I do not know what MyDNS is and what you are doing with the maximum size of the column, are you aware that postgresql bails out if you put in a string which exceeds the column size (so you can just try rather then check beforehand if thats what you do) or you also get the description in the cursor when you do the select on a table. Also, text type could be used to hold potentially large strings without harm (so if the string is short, its no difference but you can easily go up to over a gig) Regards Tino ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ts_headline
Unfortunately, my link to the box with the test database is down due to lack
of maintenance by our local telco (Telstra) but I think that I also missed
the optional config arg to ts_headline.
The lack of link also means that I cannot confirm your findings but your logic
looks good.
It begs the question, however, as to why ts-headline needs to reparse the raw
text.
At least in my case, I am using a trigger to parse the combination of Title
and Abstract to a ts_vector field in the table row (as suggested in 12.2.2
and 12.4.3 in the doco) so that the ts_vector is already available to
ts_headline.
If ts_headline had the ability to use that pre-parsed ts_vector, my problem
would never have arisen - and the performance of ts_headline would be
improved.
Cheers and thanks,
Stephen
On Friday 22 February 2008 20:00, Richard Huxton wrote:
> Stephen Davies wrote:
> > Not quite:-(
> >
> > It is the ts_headline with the explicit "english" configuration that
> > "fails" rather than the implicit "simple".
>
> Hmm... arse.
>
> > That's what is so weird.
> >
> > As you say, the ts_vector has "databas" so the "english" version of
> > ts_headline should work - but it doesn't. The "simple" version does;
> > despite the above.
>
> [goes away, tests some more]
>
> OK, so:
>
> set default_text_search_config = 'simple';
> SELECT ts_headline('my database is a database', to_tsquery('database'));
> SELECT ts_headline('my database is a database', to_tsquery('simple',
> 'database'));
> SELECT ts_headline('my database is a database', to_tsquery('english',
> 'database'));
>
> The first two work, the last one doesn't.
>
> set default_text_search_config = 'english';
> SELECT ts_headline('my database is a database', to_tsquery('database'));
> SELECT ts_headline('my database is a database', to_tsquery('simple',
> 'database'));
> SELECT ts_headline('my database is a database', to_tsquery('english',
> 'database'));
>
> The middle one doesn't work.
>
> Note that there are no indexes involved here, we're just running against
> the raw text.
>
> [light goes on over sluggish London-based database chap]
>
> When the ts_headline function is working on the text, it needs to
> convert it from varchar/text type to tsvector so that it can use the
> tsquery to find words to highlight.
>
> When it converts the text to a tsvector, it's doing it based on
> default_text_search_config - we've not told it otherwise. In an ideal
> world, it would look "inside" the tsquery and see what config that was
> using, but it can't (or at least doesn't).
>
> Of course, if to_tsquery()'s config doesn't match to_tsheadline()'s then
> we get a problem.
>
> And, if I actually bother to read an up-to-date copy of the manual,
> rather than the beta version I've got linked on my desktop I can see
> there's a parameter for ts_headline. So...
>
> set default_text_search_config = 'simple';
> SELECT ts_headline('english', 'my database is a database',
>to_tsquery('english','database')
> );
>
> set default_text_search_config = 'english';
> SELECT ts_headline('simple', 'my database is a database',
>to_tsquery('simple','database')
> );
>
>
> These all work fine. Phew!
--
This email is for the person(s) identified above, and is confidential to
the sender and the person(s). No one else is authorised to use or
disseminate this email or its contents.
Stephen Davies ConsultingVoice: 08-8177 1595
Adelaide, South Australia. Fax: 08-8177 0133
Computing & Network solutions. Mobile:0403 0405 83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] ts_headline
Stephen Davies wrote:
Not quite:-(
It is the ts_headline with the explicit "english" configuration that "fails"
rather than the implicit "simple".
Hmm... arse.
That's what is so weird.
As you say, the ts_vector has "databas" so the "english" version of
ts_headline should work - but it doesn't. The "simple" version does; despite
the above.
[goes away, tests some more]
OK, so:
set default_text_search_config = 'simple';
SELECT ts_headline('my database is a database', to_tsquery('database'));
SELECT ts_headline('my database is a database', to_tsquery('simple',
'database'));
SELECT ts_headline('my database is a database', to_tsquery('english',
'database'));
The first two work, the last one doesn't.
set default_text_search_config = 'english';
SELECT ts_headline('my database is a database', to_tsquery('database'));
SELECT ts_headline('my database is a database', to_tsquery('simple',
'database'));
SELECT ts_headline('my database is a database', to_tsquery('english',
'database'));
The middle one doesn't work.
Note that there are no indexes involved here, we're just running against
the raw text.
[light goes on over sluggish London-based database chap]
When the ts_headline function is working on the text, it needs to
convert it from varchar/text type to tsvector so that it can use the
tsquery to find words to highlight.
When it converts the text to a tsvector, it's doing it based on
default_text_search_config - we've not told it otherwise. In an ideal
world, it would look "inside" the tsquery and see what config that was
using, but it can't (or at least doesn't).
Of course, if to_tsquery()'s config doesn't match to_tsheadline()'s then
we get a problem.
And, if I actually bother to read an up-to-date copy of the manual,
rather than the beta version I've got linked on my desktop I can see
there's a parameter for ts_headline. So...
set default_text_search_config = 'simple';
SELECT ts_headline('english', 'my database is a database',
to_tsquery('english','database')
);
set default_text_search_config = 'english';
SELECT ts_headline('simple', 'my database is a database',
to_tsquery('simple','database')
);
These all work fine. Phew!
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] ts_headline
Not quite:-(
It is the ts_headline with the explicit "english" configuration that "fails"
rather than the implicit "simple".
That's what is so weird.
As you say, the ts_vector has "databas" so the "english" version of
ts_headline should work - but it doesn't. The "simple" version does; despite
the above.
Weird!
Stephen
On Friday 22 February 2008 19:33, Richard Huxton wrote:
> Stephen Davies wrote:
> > OK. The first level explanation is that my default config is "simple".
>
> Aha! Actually, that's the whole explanation.
>
> > This explains the different query results as "english" reduces "database"
> > to "databas" while "simple does not reduce it at all.
>
> Exactly.
>
> > The "document" is parsed/indexed using "english" explicitly so my queries
> > nedd to be explicit also (not an issue as all "real" queries are
> > generated rather than typed).
>
> Or change your default configuration to match the one you're using.
>
> > However, I still cannot see a reason for the ts_headline results. If
> > anything, they should be the other way around.
> >
> > I suspect that ts_headline may only work properly when no configuration
> > is specified - regardless of the default setting.
>
> No. What's happening is that your tsvector representation of the
> document (which gets indexed) contains lexemes processed by your
> "english" config. So, it will have something like:
>... databas: 123, 129, 200 ...
> Of course, when you do a tsquery search with "simple" configuration it
> checks doesn't do any stemming so is actually looking for a lexeme
> called "database" which it can't find.
>
> Since it can't find anything, it falls back to displaying just the start
> of the document. Since the alternative would be to display nothing, that
> makes a certain amount of sense.
>
> To check this, try: ts_headline(t, to_tsquery('simple','databas')) and
> you should get your database results.
>
>
> Moral of the story: if you specify a configuration, always specify it.
>
> Thanks for working through this Stephen - good question specification btw.
--
This email is for the person(s) identified above, and is confidential to
the sender and the person(s). No one else is authorised to use or
disseminate this email or its contents.
Stephen Davies ConsultingVoice: 08-8177 1595
Adelaide, South Australia. Fax: 08-8177 0133
Computing & Network solutions. Mobile:0403 0405 83
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ts_headline
Stephen Davies wrote:
OK. The first level explanation is that my default config is "simple".
Aha! Actually, that's the whole explanation.
This explains the different query results as "english" reduces "database" to
"databas" while "simple does not reduce it at all.
Exactly.
The "document" is parsed/indexed using "english" explicitly so my queries nedd
to be explicit also (not an issue as all "real" queries are generated rather
than typed).
Or change your default configuration to match the one you're using.
However, I still cannot see a reason for the ts_headline results. If anything,
they should be the other way around.
I suspect that ts_headline may only work properly when no configuration is
specified - regardless of the default setting.
No. What's happening is that your tsvector representation of the
document (which gets indexed) contains lexemes processed by your
"english" config. So, it will have something like:
... databas: 123, 129, 200 ...
Of course, when you do a tsquery search with "simple" configuration it
checks doesn't do any stemming so is actually looking for a lexeme
called "database" which it can't find.
Since it can't find anything, it falls back to displaying just the start
of the document. Since the alternative would be to display nothing, that
makes a certain amount of sense.
To check this, try: ts_headline(t, to_tsquery('simple','databas')) and
you should get your database results.
Moral of the story: if you specify a configuration, always specify it.
Thanks for working through this Stephen - good question specification btw.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
