Re: [GENERAL] Configure default for sorting of null-values?
Does anyone know if this is configurable? Or should I file a feature request? Rob On Wed, Sep 1, 2010 at 10:05 AM, Rob Gansevles wrote: > Hi, > > From the docs I see that you can control null values being sorted > before or after all non-null-values using 'NULLS LAST' or 'NULLS > FIRST' in the order by clause. > > The default behaviour is to act as though nulls are larger than non-nulls. > > My question is, is there a way to configure this default, at > connection level or at server level? > > Thanks, > > Rob > -- 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] Configure default for sorting of null-values?
Le 07/09/2010 09:57, Rob Gansevles a écrit : > Does anyone know if this is configurable? > You can only add that clause in the order by. There's no way to have a "default value" at connection time or as a server configuration parameter. -- Guillaume http://www.postgresql.fr http://dalibo.com -- 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] Configure default for sorting of null-values?
Rob Gansevles writes: > Does anyone know if this is configurable? > Or should I file a feature request? It is not, and a feature request is probably going to go nowhere. The reason configurability seems like a bad idea is that null sort direction is wired into things like index contents. If a user were to flip the default null sort direction locally to his session, then suddenly most of the indexes in the system would become unusable to him. No doubt there are things that could be done to ameliorate that, but overall it's just not apparent that the cost/benefit ratio is reasonable for such a change. regards, tom lane -- 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] Forcing the right queryplan
On Fri, Sep 03, 2010 at 09:20:39AM +0200, Yeb Havinga wrote: > > If the index is useless anyway, you might consider dropping it. > Otherwise, increasing random_page_cost might help in choosing the > otherplan, but on the other hand that plan has index scanning too, > so I'm not to sure there. > > If that doesn't help, it would be interesting to see some output > of vmstat 1 (or better: iostat -xk 1) to see what is the > bottleneck during execution of the first plan. If it is IO bound, > you might want to increase RAM or add spindles for increased > random io performance. If it is CPU bound, it is probably because > of executing the to_tsvector function. In that case it might be > interesting to see if changing ts_vectors cost (see ALTER FUNCTION Hi Yeb, Thanks for your answer. Dropping the (pkey) index is not an option. iostat suggest the thing is CPU bound (%iowait remaining 11% but cpu rizing from 1 to 13 %) However, I'm reluctant to changing the to_tsvector costs. (besides not knowing how the find out the current value). The pkey is also used for queries like this one, which also results in the wrong queryplan: syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM systemevents WHERE ( ( to_tsvector('english', message) @@ to_tsquery ( 'error')) ) AND id <= 26689837 ; QUERY PLAN --- Index Scan using systemevents_pkey on systemevents (cost=0.00..27302.74 rows=2 174 width=158) Index Cond: (id <= 26689837) Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('error'::te xt)) (3 rows) So I'm afraid that not being able to force a plan is a showstopper for using postgresql with full text search for this project. Regards, -- Henk van Lingen, ICT-SC Netwerk & Telefonie, (o- -+ Universiteit Utrecht, Jenalaan 18a, room 0.12 /\| phone: +31-30-2538453 v_/_ | http://henk.vanlingen.net/ http://www.tuxtown.net/netiquette/ -- 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] does record_eq() ignore user-defined operators?
Kurt writes: >> i'm trying to replicate tables containing XML-fields using Pg 8.4.4 and >> 9.0B4 with Bucardo and got: >> DBD::Pg::st execute failed: ERROR: could not identify an equality >> operator for type xml Tom Lane writes: > You would need to create a default btree or hash opclass for xml in > order to persuade record_eq that the type has an equality operator. > It does not care about operator names Thanks Tom, it worked with a btree operator class wz -- 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] Configure default for sorting of null-values?
On 7/09/2010 10:32 PM, Tom Lane wrote: Rob Gansevles writes: Does anyone know if this is configurable? Or should I file a feature request? It is not, and a feature request is probably going to go nowhere. The reason configurability seems like a bad idea is that null sort direction is wired into things like index contents. It'd probably have to be done at CREATE DATABASE time, like the setup of the database encoding is. Personally I can't say I really see the point. I suspect database designs that rely on the sort order of NULL anyway - they usually seem to be trying to use null as a concrete value rather than an "unset/unknown". Even if that's not the case here, I suspect (OP) that you'd have to produce a viable patch for this feature if you want it supported, as I doubt there'll be enough interest for anyone else to want to implement it. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- 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] Forcing the right queryplan
Sorry for not replying earlier, I've been quite busy. On 31 Aug 2010, at 16:50, Henk van Lingen wrote: > syslog=# \d systemevents > Table "public.systemevents" > Column |Type | > Modi > fiers > +-+- > -- > id | integer | not null default > nextval('sy (...) > message| text| (...) > Indexes: >"systemevents_pkey" PRIMARY KEY, btree (id) >"fromhost_idx" btree (fromhost) >"msgs_idx" gin (to_tsvector('english'::regconfig, message)) > > The GIN index is to do text searching (via LogAnalyzer). > > Now there are two types of query plans: Do you have output of explain analyse for these queries as well? It's hard to see what is actually going on with just the explain - we can't see which part of the query is more expensive than the planner expected, for starters. > syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, > syslogtag, infounitid, message FROM systemevents WHERE ( ( > to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY > id DESC LIMIT 100; QUERY > PLAN > > > - > Limit (cost=0.00..10177.22 rows=100 width=159) > -> Index Scan Backward using systemevents_pkey on systemevents > (cost=0.00.. > 1052934.86 rows=10346 width=159) > Filter: (to_tsvector('english'::regconfig, message) @@ > to_tsquery('131. > 211.112.9'::text)) > (3 rows) > > This one is useless (takes very long). However this one: > > syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, > syslogtag, infounitid, message FROM systemevents WHERE ( ( > to_tsvector('english', message) @@ to_tsquery ( '131.211.112.9')) ) ORDER BY > id DESC LIMIT 500; >QUERY PLAN > > > > --- > Limit (cost=40928.89..40930.14 rows=500 width=159) > -> Sort (cost=40928.89..40954.76 rows=10346 width=159) > Sort Key: id > -> Bitmap Heap Scan on systemevents (cost=2898.06..40413.36 > rows=1034 > 6 width=159) > Recheck Cond: (to_tsvector('english'::regconfig, message) @@ > to_t > squery('131.211.112.9'::text)) > -> Bitmap Index Scan on msgs_idx (cost=0.00..2895.47 > rows=10346 > width=0) > Index Cond: (to_tsvector('english'::regconfig, message) > @@ > to_tsquery('131.211.112.9'::text)) > (7 rows) > > works acceptable. Odd that more records and a more complicated plan gives faster results... That's why I think we'd really want to see explain analyse output. I'm guessing that there are a lot of records matching your search string and that you've found the cut-off point where the planner thinks you're throwing away enough rows that it's not very useful to first select all the matching records before sorting the results. I think it decided to just start searching backwards along the id and returning the rows that match that IP (and are visible to your transaction) would be faster than trying to work with all the rows that match that IP. This probably means it misjudged the costs of sorting your index backwards, which indicates that your planning statistics are off, or that your cost parameters aren't appropriate for your system. One thing I do notice is that the first plan uses the index on id instead of the ts_vector one. For queries like those you could try to use a combined index like this: CREATE INDEX msgs_idx (to_tsvector('english'::regconfig, message), id) ON systemevents USING (gin); Whether to use gist or gin there I really don't know. I'd probably go for gist, I seem to recall that gin is fairly heavy to use. > How to use the right plan regardless of the 'LIMIT-size'? You could try turning off planner-options, but that's probably a fairly bad idea. Other options are to use a prepared statement or a stored procedure with the IP as a parameter, which force the planner to use a more general plan because it doesn't know which values you're going to search for before it plans the query. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c8675d010409863511634! -- 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] Forcing the right queryplan
On Tue, Sep 7, 2010 at 8:48 AM, Henk van Lingen wrote: > > Thanks for your answer. Dropping the (pkey) index is not an option. > iostat suggest the thing is CPU bound (%iowait remaining 11% but cpu > rizing from 1 to 13 %) How man cores that server have? If you've got 8 cores and one IO bound on this query it'll hit 12%.. IOBound. rely on iostat -xd instead. -- 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] On-disk size of db increased after restore
On Fri, 2010-09-03 at 11:29 -0400, Tom Lane wrote: > > I'm confused. I'm still seeing a bug in here: I cannot restore a > dump > > effectively... Running CLUSTER or VACUUM FULL does not make any > sense to > > me in here. > > Oh, wait. What you need is this patch: > > 2010-06-06 23:01 itagaki For the records, this patch fixed my issue. Just a quick note for the archives/regular users: The client machine that runs pg_dump also needs this patch. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Empty SELECT result at simultaneous calls
Hello guys, I have a problem with my web application and postgres. I have several servlets on a page which results in severeal simultaneous data base calls kind of: SELECT d.id, d.aa, d.ab, ts.ac, d.ad, d.af, d.ag, d.ah, d.ai, d.aj, d.ak, d.al, d.am, d.an, d.ao, d.ap, d.ar, d.as, d.at, d.au, d.av, d.ax, d.ay, d.az, d.ba, d.bb, d.bc FROM c_depots d INNER JOIN c_aa ts ON d.bd_id=ts.id INNER JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON cx.id_users=u.id WHERE d.id=13 Even though I have "d.id 13" the SELECT result is empty. When I'm working with delays in the servlets, everything works fine. The calling instance itself is realized as single instance: public static DbManager getInstance() { if(instance == null) { instance = new DbManager(); } return instance; } So I'm wondering why there are such problems and how can I resolve them? My posgres version is (still) 8.2. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Find \ in text
I have a character field in a table that contains either a file name or a full path and file name. I need to pick out the ones that have no full path. I do this by looking for no \. This is what I am doing: select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\%' -this gives me all records no matter what has a \ or not select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\%' -this gives me nothing again no matter what has a \ or not I even tried this select MM_PATH_FILE from MULTI_MEDIA Where position('\' in MM_PATH_FILE)=0 -this gives me an error Any other suggestions? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com -- 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] Find \ in text
On 09/07/2010 02:04 PM, Christine Penner wrote: I have a character field in a table that contains either a file name or a full path and file name. I need to pick out the ones that have no full path. I do this by looking for no \. This is what I am doing: select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\%' -this gives me all records no matter what has a \ or not select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\%' -this gives me nothing again no matter what has a \ or not I even tried this select MM_PATH_FILE from MULTI_MEDIA Where position('\' in MM_PATH_FILE)=0 -this gives me an error Any other suggestions? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%%' From here: http://www.postgresql.org/docs/8.4/interactive/functions-matching.html#FUNCTIONS-LIKE "Note that the backslash already has a special meaning in string literals, so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used, see Section 4.1.2.1). Thus, writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character with ESCAPE; then a backslash is not special to LIKE anymore. (But backslash is still special to the string literal parser, so you still need two of them to match a backslash.) " -- Adrian Klaver adrian.kla...@gmail.com -- 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] Find \ in text
On 09/07/2010 02:04 PM, Christine Penner wrote: I have a character field in a table that contains either a file name or a full path and file name. I need to pick out the ones that have no full path. I do this by looking for no \. This is what I am doing: select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\%' -this gives me all records no matter what has a \ or not select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\%' -this gives me nothing again no matter what has a \ or not I even tried this select MM_PATH_FILE from MULTI_MEDIA Where position('\' in MM_PATH_FILE)=0 -this gives me an error Any other suggestions? Actually, to expand on my prior answer, there are many ways of doing this. For instance, you can turn off the escape mechanism: ...like E'%\\%' escape '' Your basic problem is that by default the \ is being used as an escape character in your string literal so the %\% is becoming %% before being used in the "like" clause while %\\% is becoming %\% which, when used in the like, is the equivalent of searching for a literal percent-sign. The E'%%' literal becomes %\\% which is interpreted as a single \ in the like pattern match. See http://www.postgresql.org/docs/8.4/static/functions-matching.html Cheers, Steve -- 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] Find \ in text
On 09/07/2010 02:04 PM, Christine Penner wrote: I have a character field in a table that contains either a file name or a full path and file name. I need to pick out the ones that have no full path. I do this by looking for no \. This is what I am doing: select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\\%' -this gives me all records no matter what has a \ or not select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE '%\%' -this gives me nothing again no matter what has a \ or not I even tried this select MM_PATH_FILE from MULTI_MEDIA Where position('\' in MM_PATH_FILE)=0 -this gives me an error Any other suggestions? ... like E'%%' Cheers, Steve -- 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] Find \ in text
On 09/07/10 2:44 PM, Steve Crawford wrote: Any other suggestions? ... like E'%%' and, for extra fun, if that SQL statement is a constant string in a C or similar programming language, you may well need to double up those \'s again so that SQL sees them as the C/C++/etc parser itself does \ escaping. sql_command = "select MM_PATH_FILE from MULTI_MEDIA Where MM_PATH_FILE NOT ILIKE E'%%';" Seriously, MS Windows programmers really really should use / for paths... the windows API's are all perfectly happy with these. only the command parser insists on \ as a path delimiter. -- 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] Empty SELECT result at simultaneous calls
Stefan Wild writes: > I have a problem with my web application and postgres. I have several > servlets on a page which results in severeal simultaneous data base calls > kind of: > SELECT d.id, d.aa, d.ab, ts.ac, d.ad, d.af, d.ag, d.ah, d.ai, d.aj, d.ak, > d.al, d.am, d.an, d.ao, d.ap, d.ar, d.as, d.at, d.au, d.av, d.ax, d.ay, d.az, > d.ba, d.bb, d.bc FROM c_depots d INNER JOIN c_aa ts ON d.bd_id=ts.id INNER > JOIN cx_users_depots cx ON cx.id_depots=d.id INNER JOIN c_users u ON > cx.id_users=u.id WHERE d.id=13 > Even though I have "d.id 13" the SELECT result is empty. Um, well, that's not obviously wrong. If that d row doesn't have join partners in all those other tables, you won't get anything out of the inner joins. Maybe you want to left-join, instead? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "private" installation of postgres
Assume I want to install an instance of postgres such that it cannot interfere with another install, or even be visible to other apps. Is this all I need to do: - install pg somewhere within my own directory - init the db somewhere within my own directory - disallow IP connections - set the socket directory somewhere within my own directory Or is there a doc on this somewhere? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] "private" installation of postgres
On 09/07/10 5:15 PM, Scott Ribe wrote: Assume I want to install an instance of postgres such that it cannot interfere with another install, or even be visible to other apps. Is this all I need to do: - install pg somewhere within my own directory - init the db somewhere within my own directory - disallow IP connections - set the socket directory somewhere within my own directory Or is there a doc on this somewhere? I'm pretty sure the socket directory is hard coded in the source, I don't think you can override it even with ./configure -- 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] "private" installation of postgres
On Tuesday 07 September 2010, John R Pierce elucidated thus: > On 09/07/10 5:15 PM, Scott Ribe wrote: > > Assume I want to install an instance of postgres such that it > > cannot interfere with another install, or even be visible to other > > apps. Is this all I need to do: > > > > - install pg somewhere within my own directory > > - init the db somewhere within my own directory > > - disallow IP connections > > - set the socket directory somewhere within my own directory > > > > Or is there a doc on this somewhere? > > I'm pretty sure the socket directory is hard coded in the source, I > don't think you can override it even with ./configure postgresql.conf: unix_socket_directory = '/var/run/postgresql' j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com - Fairbanks, AK PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- 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] "private" installation of postgres
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Assume I want to install an instance of postgres such > that it cannot interfere with another install, > or even be visible to other apps. Is this all I need to do: > > - install pg somewhere within my own directory > - init the db somewhere within my own directory > - disallow IP connections > - set the socket directory somewhere within my own directory Yep, that should do it. I do this all the time for testing various programs. Set the socket with pg_ctl -o "-k newsocketdir" - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201009072027 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkyG2JsACgkQvJuQZxSWSsg8igCg+CkN8v3NeKtQzIobEG6wP1gD k08AnAivO5e77TMOMcTlitPNXNyhg5wr =hgpw -END PGP SIGNATURE- -- 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] "private" installation of postgres
On 09/07/10 5:24 PM, Joshua J. Kugler wrote: I'm pretty sure the socket directory is hard coded in the source, I don't think you can override it even with ./configure postgresql.conf: unix_socket_directory = '/var/run/postgresql' for some reason, I'm remembering that last time I looked, there were places where this value wasn't being used. but then again, maybe I'm thinking of something else entirely different and am just confused. wouldn't be the first time. -- 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] "private" installation of postgres
It is defined in postgresql.conf unix_socket_directory = '/var/run/postgresql' On Tue, 07 Sep 2010 17:20:24 -0700 John R Pierce wrote: > On 09/07/10 5:15 PM, Scott Ribe wrote: > > Assume I want to install an instance of postgres such that it > > cannot interfere with another install, or even be visible to other > > apps. Is this all I need to do: > > > > - install pg somewhere within my own directory > > - init the db somewhere within my own directory > > - disallow IP connections > > - set the socket directory somewhere within my own directory > > > > Or is there a doc on this somewhere? > > I'm pretty sure the socket directory is hard coded in the source, I > don't think you can override it even with ./configure > > > > > signature.asc Description: PGP signature
Re: [GENERAL] "private" installation of postgres
On Sep 7, 2010, at 6:30 PM, John R Pierce wrote: > for some reason, I'm remembering that last time I looked, there were places > where this value wasn't being used. but then again, maybe I'm thinking of > something else entirely different and am just confused. wouldn't be the > first time. Well, unless I hear definitively, I'll consider that something I need to test. Of course there's also an option to specify the default when building the source, I expect *that* should work, and as far as I'm concerned there would be no problem doing it that way. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] "private" installation of postgres
On Sep 7, 2010, at 6:28 PM, Greg Sabino Mullane wrote: > Yep, that should do it. I do this all the time for testing > various programs. Set the socket with pg_ctl -o "-k newsocketdir" Thanks :-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] "private" installation of postgres
John R Pierce writes: > On 09/07/10 5:24 PM, Joshua J. Kugler wrote: >>> I'm pretty sure the socket directory is hard coded in the source, I >>> don't think you can override it even with ./configure >> postgresql.conf: >> unix_socket_directory = '/var/run/postgresql' > for some reason, I'm remembering that last time I looked, there were > places where this value wasn't being used. The client side is obviously not going to know what is in the server's config file, so you're going to have to specify that path every time you connect. Depending on what you are doing, you might want to build a version of libpq that defaults to assuming the nondefault socket location rather than /tmp. If so, edit DEFAULT_PGSOCKET_DIR in src/include/pg_config_manual.h. Personally, though, I think it's easier to change the default port number and not worry about sharing /tmp. Changing the port is a good idea anyway to avoid any possible conflicts on shared memory IDs. regards, tom lane -- 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] "private" installation of postgres
On Sep 7, 2010, at 7:09 PM, Tom Lane wrote: > Personally, though, I think it's easier to change the default port > number and not worry about sharing /tmp. Changing the port is a good > idea anyway to avoid any possible conflicts on shared memory IDs. I was planning on changing the default port number (I forgot to state that in my email), but also moving the socket dir just to be really sure. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] joins with text search
hi- supposing i have the following table structure: vendors - id int - name varchar products - product_list_id int - vendor_id int references vendors - part_number varchar - description varchar - price - textsearch how can i execute some type of join and get the name of the vendor to be included in the textsearch column of the products table? i am currently populating the textsearch column with the following command: UPDATE products SET textsearch=setweight(to_tsvector('english', description), 'A') || setweight(to_tsvector('english', part_number, 'B') WHERE product_list_id=3 Is there a way I can join products.vendor_id with vendors.id and get the vendor.name in the textsearch column as well? Thanks, Clark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 32bit on Windows 64bit, related components
We're about to purchase a new server for our Postgres 8.4 database. We'd like to go with Windows 64bit for possible future developments, but are happy to stick with 32bit Postgres + Npgsql, ODBC, OpenSSL, slony2 and libxml2, libpq. I understand that Postgres 32bit runs fine in Windows 64bit, but I'm concerned about whether all of the extra components will. I don't want to run into any compatibility issues, and I certainly don't want to have to recompile any components manually. Happy to run all components in 32 bit. Can anyone confirm whether an 32bit installation of all these components will work together successfully in a 64bit Windows environment? Thanks in advance, -Brendan
[GENERAL] pgfoundry news section
Hi, It seems the news section on http://pgfoundry.org wasn't updated for a while. I thought this is automatically done if each project put a news. pgpool-II project put news several times, but it never appeas on the page. Does anybody know who is able to fix this? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Postgres 32bit on Windows 64bit, related components
> We're about to purchase a new server for our Postgres 8.4 database. > We’d like to go with Windows 64bit for possible future developments, > but are happy to stick with 32bit Postgres + Npgsql, ODBC, OpenSSL, > slony2 and libxml2, libpq. Any specific reasons for choosing Windows? Once in a while, I see someone posting issues about PostgreSQL on windows and replies which go 'Windows? hmmm...not sure about that ..". Of course, those were not questions about queries/query tuning etc. Those get answered. These questions mostly deal with crashes/recovery/security related and so on. DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general