[GENERAL] Stemming not working with tsearch2() function
Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: postgresql version 8.2.4 (upgraded from 8.2.0 by rpm on Fedora Core 6 and prior to that from a 7.x version although i reinstalled tsearch2) SELECT * from pg_ts_cfg; ts_name | prs_name |locale -+--+-- default_russian | default | ru_RU.KOI8-R utf8_russian| default | ru_RU.UTF-8 simple | default | en_US.UTF-8 default | default | en_US.UTF-8 lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8
Re: [GENERAL] Stemming not working with tsearch2() function
On Mon, 30 Apr 2007, psql psql wrote: Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: it'is. Read http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes postgresql version 8.2.4 (upgraded from 8.2.0 by rpm on Fedora Core 6 and prior to that from a 7.x version although i reinstalled tsearch2) SELECT * from pg_ts_cfg; ts_name | prs_name |locale -+--+-- default_russian | default | ru_RU.KOI8-R utf8_russian| default | ru_RU.UTF-8 simple | default | en_US.UTF-8 default | default | en_US.UTF-8 lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 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 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] Temporal Units
On Mon, 30 Apr 2007, Brent Wood wrote: If I'm following this correctly, then interval extract timepart can be used to provide all the required functionality: Thanks, Brent. Your suggestions complete the approach I was considering. There is no need for real-time response, to checking after each shift or day -- or other time period -- will be sufficient. I wonder if a workweek/holiday calendar table for PostgreSQL already exists. If not I need to track down the procedure for creating one as Joe Celko references such a calendar in his books. I think that any schema that has temporal components needs such a table. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Selecting from a function(x,y) returning a row-type(sum, prod)
Hi, I'm trying to select data from a table, converting two values and return all four. Maybe this is best explained with an example: The function from the documentation on pl/pgsql: CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; And a table 'myvals' with x and y integer values: CREATE TABLE myvals (INT x, INT y); How can I do the following: select * from myvals, sum_n_product(myvals.x, myvals.y); Here I get an error: ERROR: function expression in FROM may not refer to other relations of same query level What I want is a view with the values: x | y | sum | prod | Best regards, Heiko ---(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] Temporal Units
Rich Shepard wrote: I wonder if a workweek/holiday calendar table for PostgreSQL already exists. If not I need to track down the procedure for creating one as Joe Celko references such a calendar in his books. I think that any schema that has temporal components needs such a table. There was a brief discussion of this just last week, with a few solutions suggested: http://archives.postgresql.org/pgsql-general/2007-04/msg01098.php - John D. Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Selecting from a function(x,y) returning a row-type(sum, prod)
am Mon, dem 30.04.2007, um 15:45:18 +0200 mailte Heiko Klein folgendes: Hi, I'm trying to select data from a table, converting two values and return all four. Maybe this is best explained with an example: The function from the documentation on pl/pgsql: CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; And a table 'myvals' with x and y integer values: CREATE TABLE myvals (INT x, INT y); How can I do the following: select * from myvals, sum_n_product(myvals.x, myvals.y); select x, y, sum_n_product(x,y) from myvals; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stemming not working with tsearch2() function
On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: it'is. Read http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes Thanks for the link. select * from pg_ts_cfg where oid=show_curcfg(); ts_name | prs_name | locale -+--+- simple | default | en_US.UTF-8 That's helped me understand that the default config used by the tsearch2() function is not 'default' but 'simple' but I still don't understand why 'simple' is not working when both default and simple have the same locale set in pg_ts_cfg (en_US.UTF-8). Am i missing something? postgresql version 8.2.4 (upgraded from 8.2.0 by rpm on Fedora Core 6 and prior to that from a 7.x version although i reinstalled tsearch2) SELECT * from pg_ts_cfg; ts_name | prs_name |locale -+--+-- default_russian | default | ru_RU.KOI8-R utf8_russian| default | ru_RU.UTF-8 simple | default | en_US.UTF-8 default | default | en_US.UTF-8 lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 Regards, Oleg __ phone: +007(495)939-16-83, +007(495)939-23-83
Re: [GENERAL] Selecting from a function(x,y) returning a row-type(sum, prod)
A. Kretschmer [EMAIL PROTECTED] writes: am Mon, dem 30.04.2007, um 15:45:18 +0200 mailte Heiko Klein folgendes: How can I do the following: select * from myvals, sum_n_product(myvals.x, myvals.y); select x, y, sum_n_product(x,y) from myvals; This is only part of the answer, however, because what you get is regression=# select *, sum_n_product(x,y) from myvals; x | y | sum_n_product ---+---+--- 1 | 2 | (3,2) (1 row) which is not the display he wanted. If you know a little bit about how PG deals with *-expansion you might think to try regression=# select *, (sum_n_product(x,y)).* from myvals; x | y | sum | prod ---+---+-+-- 1 | 2 | 3 |2 (1 row) which is the correct output --- but it turns out that what it's doing is effectively select *, (sum_n_product(x,y)).sum, (sum_n_product(x,y)).prod from myvals; ie the function is called twice per row. If that's a problem, what you have to do is resort to a two-level query: regression=# select x,y,(f).* from regression-# (select *, sum_n_product(x,y) as f from myvals offset 0) ss; x | y | sum | prod ---+---+-+-- 1 | 2 | 3 |2 (1 row) The offset 0 is an optimization fence to keep the planner from flattening this form into the form where the function is called twice. (As of 8.2, you can dispense with that if the function is marked volatile.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Server crash on postgresql 8.2.4 with tsearch2
Hi, I'm tryng to upgrade a 8.1.3 server to 8.2.4 and I have a problem with tsearch. I use french snowball package to compile a stemming lib - dict_fr.so Now if I do a query like this select to_tsvector('default', '... something with more than 200 chars'); - result ok but select to_tsvector('fr_FR', '... something with more than 200 chars'); - server crash Does anyone faced this bug ? Is there a fix ? Thank you for help ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Processing a work queue
Andrew - Supernews wrote: Anyone have any ideas on how to handle a work queue? Advisory locks (userlocks in pre-8.2). Can someone explain why these are a better fit than whatever locks SELECT FOR UPDATE acquires? Thanks. - John D. Burger MITRE ---(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] Stemming not working with tsearch2() function
On Mon, 30 Apr 2007, psql psql wrote: On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: it'is. Read http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes Thanks for the link. select * from pg_ts_cfg where oid=show_curcfg(); ts_name | prs_name | locale -+--+- simple | default | en_US.UTF-8 That's helped me understand that the default config used by the tsearch2() function is not 'default' but 'simple' but I still don't understand why 'simple' is not working when both default and simple have the same locale set in pg_ts_cfg (en_US.UTF-8). Am i missing something? at present, having several configurations matching the same locale leads to unpredictable results. Leave only one. In 8.3 we have special flag to mark fts config which could be selectable as default. http://www.sai.msu.su/~megera/postgres/fts/doc/fts-cfg.html postgresql version 8.2.4 (upgraded from 8.2.0 by rpm on Fedora Core 6 and prior to that from a 7.x version although i reinstalled tsearch2) SELECT * from pg_ts_cfg; ts_name | prs_name |locale -+--+-- default_russian | default | ru_RU.KOI8-R utf8_russian| default | ru_RU.UTF-8 simple | default | en_US.UTF-8 default | default | en_US.UTF-8 lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8 Regards, Oleg __ phone: +007(495)939-16-83, +007(495)939-23-83 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Server crash on postgresql 8.2.4 with tsearch2
Philippe Amelant [EMAIL PROTECTED] writes: I'm tryng to upgrade a 8.1.3 server to 8.2.4 and I have a problem with tsearch. I use french snowball package to compile a stemming lib - dict_fr.so Are you sure you used the same snowball version that tsearch2 uses? The snowball people have made incompatible changes from time to time ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] When the locially dropped column is also physically dropped
Hi, I have dropped a column (say column name is 'A') from the relation R. By setting the attisdropped as true in the pg_catalog.pg_attribute table. But the column is dropped locially not the physically. Can you please tell me when this column will be physically also dropped. Is this column is automatically physically dropped? or I have to run some command to dropped it physically. Thanks, Rupesh Bajaj
[GENERAL] tsearch2 issue
Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache as it seems the tsearch2() function used by triggers is not specifying default which means many words that are index are then subsequently not found. I figure this may be something to do with locale settings, other info: postgresql version 8.2.4 (upgraded from 8.2.0 by rpm on Fedora Core 6) SELECT * from pg_ts_cfg; ts_name | prs_name |locale -+--+-- default_russian | default | ru_RU.KOI8-R utf8_russian| default | ru_RU.UTF-8 simple | default | en_US.UTF-8 default | default | en_US.UTF-8 lc_collate | en_US.UTF-8 lc_ctype| en_US.UTF-8 lc_messages | en_US.UTF-8 lc_monetary | en_US.UTF-8 lc_numeric | en_US.UTF-8 lc_time | en_US.UTF-8
[GENERAL] Server crash on postgresql 8.2.4 with tsearch2
Hi, I'm tryng to upgrade a 8.1.3 server to 8.2.4 and I have a problem with tsearch. I use french snowball package to compile a stemming lib - dict_fr.so Now if I do a query like this select to_tsvector('default', '... something with more than 200 chars'); - result ok but select to_tsvector('fr_FR', '... something with more than 200 chars'); - server crash Does anyone faced this bug ? Is there a fix ? Thank you for help ---(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] pljava on mac
Hi, I finally could repair pljava sources to make it compile in Mac OS X 10.4.8. The problem is that XactListener.c (src/C/pljava) references XactEvent without including the library where it is declared. XactEvent is declared within xact.c and you can get this file from postgresql sources. What I did was 1) Copy xact.h src/C/include/pljava 2) Copy xact.c src/C/pljava 3) Open and edit src/C/pljava/XactListener.c, adding #include pljava/ xact.h And next I followed Pascal Pochet's instructions (http://osdir.com/ml/ db.postgresql.pljava/2004-11/msg00013.html). Thanks Pascal. And that's pretty much it. Hope it helps. Juan Rada. [EMAIL PROTECTED] http://jcrada.googlepages.com On Mar 4, 2:17 pm, [EMAIL PROTECTED] (Eddy D. Sanchez) wrote: Someone works with pljava under postgresql on mac? I'm trying to install on OSX 10.4.8 but I cant compile this. Is there somebody that can copy me its compiles libraries ? ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump without psql rights
config tables are like other user created tables, the same access policy. It's up to user grant access to them. True, but the tables should be created with the same owner as the database. I'm finding that they're created with owner postgres even though the database they're created in has a different owner. Regards, Vinay Sajip ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Temporal Units
On 4/28/07, Rich Shepard [EMAIL PROTECTED] wrote: I would like to store a temporal frequency as NUMERIC, without units, and have the application's front end (or middleware) transform the number to the appropriate interval name. I'm having difficulties figuring out how to do this. This is a common enough problem. Three factors come to mind: (1) Can all your intervals be expressed in absolute time units, such as number of days? Work shift is a human concept whose length is defined by context. (2) When expressed as absolute time units, are all intervals valid for your data, or do you only permit subsets of the total set of possible intervals? In other words, if your user interface allows 1 week today, but you remove this option in the future, is old data referring to this interval invalidated, or is that fine? (3) Do you need to refer to specific months or years? The length of these units vary according to month and leap year, and cannot be reliably encoded as n days. The exception is when working with native PostgreSQL intervals; see below. If possible, I recommend dealing with absolute units and avoiding #2 and #3 altogether. PostgreSQL does have an interval data type that was designed for this very problem: create table intervals (name text, value interval); insert into intervals ('day', '1 day'::interval), ('week', '1 week'::interval), ('month', '1 month'::interval); Calculations on intervals are internally consistent with the Gregorian calendar system: # select current_date; 2007-04-29 00:00:00 # select current_date + '1 month'::interval; 2007-05-29 00:00:00 # select current_date + '1 month'::interval * 3; 2007-07-29 00:00:00 Based on this, you could create the table above as a lookup table for symbolic constants. Alexander. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pljava for mac osx 10.4.8
Hi, I'm looking for it too, I have the same configuration you do. Have you found a solution? Thanks On Mar 2, 11:31 pm, [EMAIL PROTECTED] (Eddy D. Sanchez) wrote: I'm trying to install pljava on postgresql on macintosh I have postgres runing, but I have not got any manual for install pljava on OSX If someone have its pljava.so and other needed libraries compiled can you copy me please?? I use a MacBook with OSX 10.4.8 and java 1.5.0, postgresql 8.2.1 Thanks ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] Temporal Units
I am not sure I see why it would be good to do this using SQL, but I do know that I have used a number of Perl packages for this sort of thing. When I have done this in the past, I'd do the date and time calculations in Perl and feed the result to whatever RDBMS I happen to be using (PostgreSQL, MySQL, MS SQL Server 2005). I suppose that if it is nessary to do it within an SQL script, resort could be made to functions that in turm use the Perl packages. But a question: Why would any schema that includes temporal components need a calendar table? I use temporal components all the time and have yet to need a calendar table. In fact, some of my database applications are multitemporal, keeping track of edits to data that correct or update data, so that during an audit script, one can determine what a decision maker knew at the time he made a decision. This is so that a decision that was bad, but based on good data can be distinguished from a decision that had been based on bad data, but which would have been a good decision had the data been correct. The first option warrants correction of the decision maker while the latter warrants examination of the data entry process. I have found my Perl scripts adequate for those few instances where use of my tyemporal data depended on a calendar. I am not arguing with you. I just want to know in what circumstances my schemas can be improved by a calendar table, and how it provides a benefit over my more usual Perl functions. Cheers, Ted Rich Shepard [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, Brent Wood wrote: If I'm following this correctly, then interval extract timepart can be used to provide all the required functionality: Thanks, Brent. Your suggestions complete the approach I was considering. There is no need for real-time response, to checking after each shift or day -- or other time period -- will be sufficient. I wonder if a workweek/holiday calendar table for PostgreSQL already exists. If not I need to track down the procedure for creating one as Joe Celko references such a calendar in his books. I think that any schema that has temporal components needs such a table. Rich -- Richard B. Shepard, Ph.D. | The Environmental Permitting Applied Ecosystem Services, Inc. | Accelerator(TM) Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Temporal Units
On Mon, 30 Apr 2007, John D. Burger wrote: There was a brief discussion of this just last week, with a few solutions suggested: http://archives.postgresql.org/pgsql-general/2007-04/msg01098.php John, That thread asked how to find business days between any two specified dates. I would like to create a calendar table that includes business days, holidays, Julianized dates, and other interesting tid-bits that are of value in a business application. Then dates can be looked up in the table to learn their attributes and the calculations don't need to be done each time. A Google search with the terms sql calendar table returns 1.4 million hits. They're almost all SQL Server, T-SQL, and similar. I am really surprised at not finding a postgresql solution among all these (but I looked only at the first 40 hits). Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Server crash on postgresql 8.2.4 with tsearch2
Le lundi 30 avril 2007 à 11:51 -0400, Tom Lane a écrit : Philippe Amelant [EMAIL PROTECTED] writes: I'm tryng to upgrade a 8.1.3 server to 8.2.4 and I have a problem with tsearch. I use french snowball package to compile a stemming lib - dict_fr.so Are you sure you used the same snowball version that tsearch2 uses? The snowball people have made incompatible changes from time to time ... I use the lastest french package from http://www.snowball.tartarus.org/ I have no error at compile time. there was lots of errors on 8.2.3 so I suppose tsearch was updated on 8.2.4 (and it's in the changelog for 8.2.4 maybee it's related ?). thanks ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stemming not working with tsearch2() function
On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: it'is. Read http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes Thanks for the link. select * from pg_ts_cfg where oid=show_curcfg(); ts_name | prs_name | locale -+--+- simple | default | en_US.UTF-8 That's helped me understand that the default config used by the tsearch2() function is not 'default' but 'simple' but I still don't understand why 'simple' is not working when both default and simple have the same locale set in pg_ts_cfg (en_US.UTF-8). Am i missing something? at present, having several configurations matching the same locale leads to unpredictable results. Leave only one. In 8.3 we have special flag to mark fts config which could be selectable as default. http://www.sai.msu.su/~megera/postgres/fts/doc/fts-cfg.html Ah thanks. Is tsearch2() hard coded to use 'simple', or could i delete 'simple' and just use 'default' somehow? It's not a big issue if I have to use simple, I will just have to redeploy some code that is currently using 'default'. Matt.
Re: [GENERAL] Server crash on postgresql 8.2.4 with tsearch2
On Mon, 30 Apr 2007, philippe wrote: Hi, I'm tryng to upgrade a 8.1.3 server to 8.2.4 and I have a problem with tsearch. I use french snowball package to compile a stemming lib - dict_fr.so Now if I do a query like this select to_tsvector('default', '... something with more than 200 chars'); - result ok it doesn't uses french snowball stemmer but select to_tsvector('fr_FR', '... something with more than 200 chars'); - server crash Does anyone faced this bug ? Is there a fix ? have you reinstalled tsearch2 ? There was change in snowball stemmer api. Check mailing list archive for the same issue. Thank you for help ---(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 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] Temporal Units
On Mon, 30 Apr 2007, Ted Byers wrote: I am not sure I see why it would be good to do this using SQL, but I do know that I have used a number of Perl packages for this sort of thing. I am not arguing with you. I just want to know in what circumstances my schemas can be improved by a calendar table, and how it provides a benefit over my more usual Perl functions. Ted, Having never used such a table -- or having written an application that had such a heavy use of temporal data rather than scientific data -- I have no idea in what circumstances your schemas might be improved with a calendar table. I suspect, however, that a SQL table lookup may well be quicker than running a script (or compiled function) in another language, and the table is available for use in multiple apps. Isn't it faster or more efficient to run SELECT queries with table lookups rather then use stored procedures? For this web-based application, the UI and communications between client and server are being written in Ruby (with Rails) while the report generation is written in Python using ReportLab. If most of the queries can be done with SQL, I think it will be much easier to maintain, modify, and expand. Could be wrong, of course. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Disadvantages on having too many page slots?
On Apr 18, 2007, at 4:39 PM, Csaba Nagy wrote: Other than hard disk space, are there any disadvantages on having a large number of page slots? It's not using hard disk space, it's using shared memory, so you might want to adjust it to make that memory available for other purposes... AFAIK, the FSM is written out during a clean shutdown... so from that standpoint it does take disk space. But obviously the memory usage is the only real concern. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] permissions
On Apr 21, 2007, at 9:53 PM, Tom Allison wrote: I am not sure how the permissions work anymore. What I want to do is create a database and have a user create all the tables and rules. I created the database from user postgres. I then set the owner of the database to my userid my userid created a number of tables and rules. I then tried to migrate this database to a new user/owner. But I keep getting permission denied for relation user_token Which probably has to do with a relationship I have of referential integrity and a rule. The rule updates a second table, which I have update/insert rights to. It's got the be the rule. I can do everything else, including what the rule is supposed to be. how do a change the owner/permissions of the rule? We'll need more details than that. What exactly do you mean by 'tried to migrate this database to a new user/owner'? What exact command are you running and what's the exact error message are you getting? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] The directory of the postgresql source
On Apr 23, 2007, at 8:27 AM, shieldy wrote: where can I get the help of the directory explantation to the postgresql src? I have to do some work about the definition of the internal functions. who can help me ? thankyou! You should read the developers FAQ, which is in the developers section of the website. After that, I suggest asking on -hackers where the piece of code you're looking for is in the source tree if you still can't find it. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres on Windows: PAE and max memory
On Apr 24, 2007, at 8:50 PM, William Garrison wrote: I have a server running Windows Server 2003 32-bit that has 8GB of memory. Our system administrator installed PAE (Physical Address Extensions) which I know MS SQL Server will use, but I'm not sure if PostgreSQL will. Can PostgreSQL use the memory above 2GB and 4GB? Have you tried it? :) I don't know off-hand if it can or not, but keep in mind that unlike most databases, PostgreSQL tends to rely on the OS helping with caching, so you don't generally want to give all your memory to PostgreSQL to use. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Bitmap Scan Pages
On Apr 25, 2007, at 7:36 AM, Listmail wrote: Is there a way to know how many pages were hit by a Bitmap Heap scan ? For instance : Bitmap Heap Scan on posts (cost=56.71..295.24 rows=2123 width=67) (actual time=0.575..1.462 rows=2160 loops=1) I'd like to know if it hit 2160 pages (ie I should really run CLUSTER) or 50 pages (ie. my table is well clustered, everything cool). Since, when it's cached, it's so fast anyway I cant' tell the difference, but when it's not cached, it is important. This would give an interesting probe for EXPLAIN ANALYZE tuning... If you don't have anything else running in the database and you've got stats_block_level on, you could probably determine how many pages are being run by consulting the appropriate pg_statio_* view. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reg: bitmap index implementation
On Apr 25, 2007, at 4:13 PM, sangeetha k.s wrote: does postgre sql support bitmap indexing for indexing the datatables. will that be possible to get the source of that. Search the -hackers archives for more information about this; I know there's a patch floating around, but I don't remember the status of it. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Limiting user connnections on 7.4
I think you might be able to accomplish this by setting up a pgpool instance for just the read-only connections and limiting how many connections are allowed there. On Mar 26, 2007, at 3:20 PM, Saqib Awan wrote: Yes other than max_connections. I have a read-only user whose connections need to be limited to a number far less than max_connections. Unfortunately, I cannot upgrade to latest version of the portgres since the newer ones has the support. -Original Message- From: Jim Nasby [mailto:[EMAIL PROTECTED] Sent: Saturday, March 24, 2007 2:26 PM To: Saqib Awan Cc: pgsql-general@postgresql.org general Subject: Re: [GENERAL] Limiting user connnections on 7.4 Dropping -hackers On Mar 22, 2007, at 4:23 AM, Saqib Awan wrote: is there an existing mechanism to do user based connection controls in Postgres 7.4? Other than max_connections? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stemming not working with tsearch2() function
On Mon, 30 Apr 2007, psql psql wrote: On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: On 4/30/07, Oleg Bartunov [EMAIL PROTECTED] wrote: On Mon, 30 Apr 2007, psql psql wrote: Anyone know why to_tsvector('sausages') might return sausages while to_tsvector('default','sausages') correctly returns sausag? This is causing me a fairly major headache. I am guessing that the tsearch2() function used in my trigger is not specifying default when creating the tsvector since the words be put into the vector are not correctly stemmed (if that is the correct term). I figure this may be something to do with locale settings, other info: it'is. Read http://www.sai.msu.su/~megera/wiki/Tsearch_V2_Notes Thanks for the link. select * from pg_ts_cfg where oid=show_curcfg(); ts_name | prs_name | locale -+--+- simple | default | en_US.UTF-8 That's helped me understand that the default config used by the tsearch2() function is not 'default' but 'simple' but I still don't understand why 'simple' is not working when both default and simple have the same locale set in pg_ts_cfg (en_US.UTF-8). Am i missing something? at present, having several configurations matching the same locale leads to unpredictable results. Leave only one. In 8.3 we have special flag to mark fts config which could be selectable as default. http://www.sai.msu.su/~megera/postgres/fts/doc/fts-cfg.html Ah thanks. Is tsearch2() hard coded to use 'simple', or could i delete 'simple' and just use 'default' somehow? It's not a big issue if I have to use simple, I will just have to redeploy some code that is currently using 'default'. Matt. Matt, just update table to save simple cfg for future update pg_ts_cfg set locale='some_en_US.UTF-8' where ts_name='simple'; 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 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] When the locially dropped column is also physically dropped
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/27/07 07:01, rupesh bajaj wrote: Hi, I have dropped a column (say column name is 'A') from the relation R. By setting the attisdropped as true in the pg_catalog.pg_attribute table. But the column is dropped locially not the physically. Can you please tell me when this column will be physically also dropped. Is this column is automatically physically dropped? or I have to run some command to dropped it physically. Why didn't you drop the column the proper way? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGNiLVS9HxQb37XmcRAs/rAJ9KkXKlagXCe+RWnNd2824gZ0MTdQCgwHRy uA2IsmiFw7WjrexvEHeY7w8= =pqnT -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] Questions about TSearch2 and PG 8.2
Hello! I'm in the process of upgrading our PostgreSQL 8.1 databases to PostgreSQL 8.2.4. I have stumbled over a minor issue with the upgrade and some helpful suggestions here: http://people.planetpostgresql.org/xzilla/index.php?/archives/291-The-pain-that-is-tsearch2-8.1-8.2-upgrading.html I shall try tonight with an plain SQL dump, but as some of my DBs are quite large, I usually use the custom dump format. As I would like to move the tsearch2-stuff in ist own schema as suggested, I tried using a restore list. I'd like to report that everything works as expected, but I've got a slight problem with the custom schema part. I created the target-db, created a schema tsearch2 and installed the tsearch2-functions, operators, configuration and whatnot into this new schema. Then I edited the restore list so that the tsearch2-bits would not be created from the dump file again. However, the binary-dump tries to create the textindex-columns with a tsvector-type which explicitly references the public schema. Instead of CREATE TABLE someschema.article ( id integer, mytext text, idxfti tsvector ); it tries to create the table like this CREATE TABLE someschema.article ( id integer, mytext text, idxfti public.tsvector ); As the tsvector-type is defined in the tsearch2-schema, this is bound to fail, even with the search_path set to include the tsearch2-schema. I assume that this happens because the table article is not in the same schema as the original tsvector-type and the default search_path is being ignored on the dump in order to be on the safe side. This double-checking breaks the migration in my case, however, so is there some way that would allow me to change the table definition on restore from using just tsvector instead of the explicit public.tsvector? I already tried editing the binary dump, but that just resulted in a corrupted dump-file. I there's no other way, I'll go the plain dump route, of course, but I'd just like to check this issue. My second question concerns the new Gin (Generalized Inverted Index) index type. Is it stable enough for production yet and would it yield a high enough performance gain in comparison the GiST? Does it make much sense using a Gin-index alongside the GiST-one? Would we need to change anything in the application code in order to make use of Gin - like using where idxfti @ to_tsquery('default_german', 'Fundstück') instead of where idxfti @@ to_tsquery('default_german', 'Fundstück') ? The docs here http://www.sai.msu.su/~megera/wiki/Gin are still a bit thin, so any hint to some further examples would be greatly appreciated. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] function and bytea
Hi, In my PHP page i upload pictures into database. For that i wrote a simple function which control which user is logged and will upload his picture. here is the function : CREATE OR REPLACE FUNCTION sp_a_006(login character varying, photo bytea) RETURNS boolean AS $BODY$ DECLARE my_idINTEGER :=0; BEGIN select into my_id account_id from accounts where account_login = $1; IF (my_id != 0) THEN UPDATE users SET user_photo = $2 WHERE user_account_id = my_id; RETURN (TRUE); ELSE RETURN (FALSE); end if; END; when i call this function in PHP, i do the following : $my_query = select * from immense.sp_a_006 ('.$_SESSION[username].','{$escaped}'); $res_pic = pg_query(my_query); where $escaped = pg_escape_bytea($data); this inserts the picture only if i add E in front of '{$escaped}' and becomes E'{$escaped}'). why ? on another website i do not use function but a simple SQL query as following and it works : pg_query(INSERT INTO photo (photo_id,document_orientation_id, photo_date, photo_image) VALUES (nextval('photo_photo_id_seq'),.$orientation_id[0].,NOW(), '{$escaped}')); thanks a lot, -- Alain Windows XP SP2 PostgreSQL 8.1.4 Apache 2.0.58 PHP 5
Re: [GENERAL] Questions about TSearch2 and PG 8.2
On Mon, 30 Apr 2007, Markus Wollny wrote: Hello! I'm in the process of upgrading our PostgreSQL 8.1 databases to PostgreSQL 8.2.4. I have stumbled over a minor issue with the upgrade and some helpful suggestions here: http://people.planetpostgresql.org/xzilla/index.php?/archives/291-The-pain-that-is-tsearch2-8.1-8.2-upgrading.html I shall try tonight with an plain SQL dump, but as some of my DBs are quite large, I usually use the custom dump format. As I would like to move the tsearch2-stuff in ist own schema as suggested, I tried using a restore list. I'd like to report that everything works as expected, but I've got a slight problem with the custom schema part. I created the target-db, created a schema tsearch2 and installed the tsearch2-functions, operators, configuration and whatnot into this new schema. Then I edited the restore list so that the tsearch2-bits would not be created from the dump file again. However, the binary-dump tries to create the textindex-columns with a tsvector-type which explicitly references the public schema. Instead of CREATE TABLE someschema.article ( id integer, mytext text, idxfti tsvector ); it tries to create the table like this CREATE TABLE someschema.article ( id integer, mytext text, idxfti public.tsvector ); As the tsvector-type is defined in the tsearch2-schema, this is bound to fail, even with the search_path set to include the tsearch2-schema. I assume that this happens because the table article is not in the same schema as the original tsvector-type and the default search_path is being ignored on the dump in order to be on the safe side. This double-checking breaks the migration in my case, however, so is there some way that would allow me to change the table definition on restore from using just tsvector instead of the explicit public.tsvector? I already tried editing the binary dump, but that just resulted in a corrupted dump-file. I there's no other way, I'll go the plain dump route, of course, but I'd just like to check this issue. I think you need plain sql dump. My second question concerns the new Gin (Generalized Inverted Index) index type. Is it stable enough for production yet and would it yield a high enough performance gain in comparison the GiST? Does it make much sense using a Gin-index alongside the GiST-one? Would we need to change anything in the application code in order to make use of Gin - like using where idxfti @ to_tsquery('default_german', 'Fundst?ck') instead of where idxfti @@ to_tsquery('default_german', 'Fundst?ck') No, use @@ operator with Gin as well, you may need @@@ operator for Gin, if you use weights in tsquery. btw, read http://www.sai.msu.su/~megera/postgres/fts/doc for information about Gin+Gist index. It's true, that the best combination is GiST for online stuff and Gin for archived. Also, if you read russian there are some papers available http://www.sai.msu.su/~megera/postgres/talks/ ? The docs here http://www.sai.msu.su/~megera/wiki/Gin are still a bit thin, so any hint to some further examples would be greatly appreciated. See above Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: F?rth (HRB 8818) Vorstandsmitglieder: Johannes S. G?zalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: J?rg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ 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] NFS vs. PostgreSQL on Solaris
On Apr 26, 2007, at 6:51 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: 1. What aspect of postgres' memory usage would create an out of memory condition? I'm guessing you ran the box out of swap space --- look into what other processes got started as a result of adding the NFS mount, and how much memory they wanted to eat. 3. What would cause postgres to die from a signal 11? I've also got a core file if that's necessary for further forensics. Send gdb backtrace, please. regards, tom lane Unfortunately, the production build in question is lacking --enable- debug. :( -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pgsql and Mac OS X
I'm trying to find the binaries for pgsql (the client) for Mac OSX. Is there any way to get these without installing all of postgres on a computer? I'm not going to use postgres on my MacBook, just connect to it. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql and Mac OS X
On Apr 30, 2007, at 2:28 PM, Tom Allison wrote: I'm trying to find the binaries for pgsql (the client) for Mac OSX. Is there any way to get these without installing all of postgres on a computer? I'm not going to use postgres on my MacBook, just connect to it. If you have macports installed you can install the postgresql82 port (not postgresql82-server). erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r) ---(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] When the locially dropped column is also physically dropped
[EMAIL PROTECTED] (rupesh bajaj) writes: Hi, I have dropped a column (say column name is 'A') from the relation R. By setting the attisdropped as true in the pg_catalog.pg_attribute table. But the column is dropped locially not the physically. Can you please tell me when this column will be physically also dropped. Is this column is automatically physically dropped? or I have to run some command to dropped it physically. Ron's comment is well-suggested[1], but a bit of a red herring, as the column will *NEVER* be physically dropped. [2] What will happen is that new tuples will not have the column, but old tuples will continue to have the (invisible) column for as long as they live in the database. Footnotes: [1] Ron Johnson, Jr suggested Why didn't you drop the column the proper way? [2] When a column is dropped via ALTER TABLE DROP COLUMN, the effect in fact is much the same as what you did; the attribute is marked as dropped. -- let name=cbbrowne and tld=linuxdatabases.info in name ^ @ ^ tld;; http://linuxdatabases.info/info/wp.html MICROS~1: The People who Brought the Y2K Bug into Software Titling -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql and Mac OS X
I found psql in /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/psql which means that I can, as a user, access the database from a Mac. But I'm still unable to build the perl modules for DBD:Pg support. And this one seems a bit screwed up from default. Port is 5433, not 5432. pg_config shows it configured with a prefix path that doesn't exist: /System/Library/CoreServices/RemoteManagement/sqldb And I'm starting to think I'm way out of my league on how to get this working. On Apr 30, 2007, at 3:45 PM, Erik Jones wrote: On Apr 30, 2007, at 2:28 PM, Tom Allison wrote: I'm trying to find the binaries for pgsql (the client) for Mac OSX. Is there any way to get these without installing all of postgres on a computer? I'm not going to use postgres on my MacBook, just connect to it. If you have macports installed you can install the postgresql82 port (not postgresql82-server). erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r) ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgres on Windows: PAE and max memory
Jim Nasby wrote: On Apr 24, 2007, at 8:50 PM, William Garrison wrote: I have a server running Windows Server 2003 32-bit that has 8GB of memory. Our system administrator installed PAE (Physical Address Extensions) which I know MS SQL Server will use, but I'm not sure if PostgreSQL will. Can PostgreSQL use the memory above 2GB and 4GB? Have you tried it? :) I don't know off-hand if it can or not, but keep in mind that unlike most databases, PostgreSQL tends to rely on the OS helping with caching, so you don't generally want to give all your memory to PostgreSQL to use. PostgreSQL should be able to use it just fine for sort memory, as long as it's split between different backends. You can't use it all for shared memory. And as Jim says, leave a lot for the file cache. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql and Mac OS X
Tom == Tom Allison [EMAIL PROTECTED] writes: Tom /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/psql That's not on my mac. Must be some bolt-on you installed. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] NFS vs. PostgreSQL on Solaris
Thomas F. O'Connell [EMAIL PROTECTED] writes: On Apr 26, 2007, at 6:51 PM, Tom Lane wrote: Thomas F. O'Connell [EMAIL PROTECTED] writes: 3. What would cause postgres to die from a signal 11? I've also got a core file if that's necessary for further forensics. Send gdb backtrace, please. Unfortunately, the production build in question is lacking --enable- debug. :( Well, if it wasn't actually stripped then gdb could still get function names out of it, which might or might not be enough but it's sure more info than you provided so far. If you built with gcc, then a possible plan B is to recompile with all the same options plus --enable-debug, and hope that the resulting executables are bit-for-bit the same except for addition of debug symbols, so you could use them with the corefile. This theoretically should work, if nothing has changed in your build environment, though that assumption is obviously a bit shaky. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] When the locially dropped column is also physically dropped
Chris Browne [EMAIL PROTECTED] writes: Ron's comment is well-suggested[1], but a bit of a red herring, as the column will *NEVER* be physically dropped. [2] Check. What will happen is that new tuples will not have the column, but old tuples will continue to have the (invisible) column for as long as they live in the database. Actually, new tuples still have the column, it's just always NULL (and hence takes no space except for a bit in the nulls-bitmap). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql and Mac OS X
On Apr 30, 2007, at 16:20 , Randal L. Schwartz wrote: Tom == Tom Allison [EMAIL PROTECTED] writes: Tom /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/ psql That's not on my mac. Must be some bolt-on you installed. Apple Remote Desktop uses PostgreSQL as it's data store (at least through version 2). I believe it's PostgreSQL 7.3, so the psql binary isn't going to be much use in connecting to newer PostgreSQL servers, and in any event I'm pretty sure the Remote Desktop installation does not include the libraries necessary to build the Perl modules, even if they were up to date. Better just to install via MacPorts or even from source: it builds pretty easily on Mac OS X. Michael Glaesemann grzm seespotcode net ---(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] pgsql and Mac OS X
Tom Allison [EMAIL PROTECTED] writes: I found psql in /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/psql which means that I can, as a user, access the database from a Mac. But I'm still unable to build the perl modules for DBD:Pg support. And this one seems a bit screwed up from default. Port is 5433, not 5432. Yeah, Apple uses Postgres as a part of Remote Desktop, but I don't think they intend it for general use --- it deliberately uses a nonstandard port to avoid conflicting with a regular PG server. You could probably use that psql if you explicitly set the port parameter, but that's a bit of a pain. They very possibly didn't bother to enable command history in psql either, if it weren't intended to be used much, and that would be a real big pain. Lastly, if the header files aren't included (haven't checked but seems highly likely) then you'd not be able to use this installation to build any other PG-using code such as DBD:Pg. What you can do if you want to build PG from source is build normally but only install the client programs. The Fine Manual recommends gmake -C src/bin install gmake -C src/include install gmake -C src/interfaces install gmake -C doc install instead of the usual gmake install. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql and Mac OS X
On Apr 30, 2007, at 5:20 PM, Randal L. Schwartz wrote: Tom == Tom Allison [EMAIL PROTECTED] writes: Tom /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/ psql That's not on my mac. Must be some bolt-on you installed. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! So how did you get it working? (I have no idea how this file got there. Wouldn't know where to begin) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql and Mac OS X
That might be the thing to do. I'm wondering how Apple Remote Desktop got onto my machine and how to remove it. On Apr 30, 2007, at 5:38 PM, Michael Glaesemann wrote: On Apr 30, 2007, at 16:20 , Randal L. Schwartz wrote: Tom == Tom Allison [EMAIL PROTECTED] writes: Tom /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/ psql That's not on my mac. Must be some bolt-on you installed. Apple Remote Desktop uses PostgreSQL as it's data store (at least through version 2). I believe it's PostgreSQL 7.3, so the psql binary isn't going to be much use in connecting to newer PostgreSQL servers, and in any event I'm pretty sure the Remote Desktop installation does not include the libraries necessary to build the Perl modules, even if they were up to date. Better just to install via MacPorts or even from source: it builds pretty easily on Mac OS X. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pgsql and Mac OS X
That's not on my mac. Must be some bolt-on you installed. So how did you get it working? (I have no idea how this file got there. Wouldn't know where to begin) Coming in late so maybe someone already posted this, but I used this: http://www.postgresqlformac.com/ I wanted the server too, but looks like they have just hte client perhaps. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql and Mac OS X
Tom Allison [EMAIL PROTECTED] writes: I'm wondering how Apple Remote Desktop got onto my machine and how to remove it. There isn't any particular need to remove it; it won't conflict with a standard PG installation. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql and Mac OS X
Tom == Tom Lane [EMAIL PROTECTED] writes: Tom What you can do if you want to build PG from source is build normally Tom but only install the client programs. The Fine Manual recommends Tom gmake -C src/bin install Tom gmake -C src/include install Tom gmake -C src/interfaces install Tom gmake -C doc install Tom instead of the usual gmake install. The Randal Notebook recommends: fink install postgresql :-) Then you get automatic startup on boot, usernames added, etc. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql and Mac OS X
On Apr 30, 2007, at 5:03 PM, Tom Allison wrote: On Apr 30, 2007, at 5:38 PM, Michael Glaesemann wrote: On Apr 30, 2007, at 16:20 , Randal L. Schwartz wrote: Tom == Tom Allison [EMAIL PROTECTED] writes: Tom /System/Library/CoreServices/RemoteManagement/rmdb.bundle/ bin/psql That's not on my mac. Must be some bolt-on you installed. Apple Remote Desktop uses PostgreSQL as it's data store (at least through version 2). I believe it's PostgreSQL 7.3, so the psql binary isn't going to be much use in connecting to newer PostgreSQL servers, and in any event I'm pretty sure the Remote Desktop installation does not include the libraries necessary to build the Perl modules, even if they were up to date. Better just to install via MacPorts or even from source: it builds pretty easily on Mac OS X. Michael Glaesemann grzm seespotcode net That might be the thing to do. I'm wondering how Apple Remote Desktop got onto my machine and how to remove it. For now, I'd just ignore the one installed by Remote Desktop. Once you have Macports installed it will place the installation directory for installed ports before the system directories in your path, or maybe you'll have to do that, but it'll be spelled out one way or another in the Macports installation docs. erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgsql and Mac OS X
On Apr 30, 2007, at 16:39 , Tom Lane wrote: Yeah, Apple uses Postgres as a part of Remote Desktop, but I don't think they intend it for general use --- it deliberately uses a nonstandard port to avoid conflicting with a regular PG server. Really? I've had the Remote Desktop postgres instance prevent others from starting on the default port. Matter of fact, I see that it started up on 5432 just right now. I wonder if the Remote Desktop doesn't check if something else is running on 5432 on startup and use another port if it's already in use. Note that I don't think the Remote Desktop postgres instance starts on system startup; from observation it looks like Remote Desktop needs to be launched for its postgres server to start. Michael Glaesemann grzm seespotcode net ---(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] pgsql and Mac OS X
Please update the Randall Notebook to read: sudo fink install dbd-pg-unified-pm586 Perhaps this will be done in time for YAPC? On Apr 30, 2007, at 6:22 PM, Randal L. Schwartz wrote: Tom == Tom Lane [EMAIL PROTECTED] writes: Tom What you can do if you want to build PG from source is build normally Tom but only install the client programs. The Fine Manual recommends Tom gmake -C src/bin install Tom gmake -C src/include install Tom gmake -C src/interfaces install Tom gmake -C doc install Tom instead of the usual gmake install. The Randal Notebook recommends: fink install postgresql :-) Then you get automatic startup on boot, usernames added, etc. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql and Mac OS X
Michael Glaesemann [EMAIL PROTECTED] writes: On Apr 30, 2007, at 16:39 , Tom Lane wrote: Yeah, Apple uses Postgres as a part of Remote Desktop, but I don't think they intend it for general use --- it deliberately uses a nonstandard port to avoid conflicting with a regular PG server. Really? I've had the Remote Desktop postgres instance prevent others from starting on the default port. Matter of fact, I see that it started up on 5432 just right now. I wonder if the Remote Desktop doesn't check if something else is running on 5432 on startup and use another port if it's already in use. Note that I don't think the Remote Desktop postgres instance starts on system startup; from observation it looks like Remote Desktop needs to be launched for its postgres server to start. Hmm ... the default port wired into the executables definitely seems to be 5433: Mini:~ tgl$ /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /private/var/db/RemoteManagement/RMDB/.s.PGSQL.5433? Mini:~ tgl$ /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/postmaster --help /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/postmaster is the PostgreSQL server. ... -p PORT port number to listen on (default 5433) ... It's possible that Remote Desktop overrides that when starting the postmaster; although dynamically choosing the port doesn't seem very bright since you've got the problem of how do the clients know where to connect? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgsql and Mac OS X
You can get libpq (and psql) from the pgEdit distribution. Just right click on the application and choose Show Package Contents. You'll find these files in Contents/MacOS/bin John http://pgedit.com/public/pgedit/pgEdit_mac_1.3.dmg On Apr 30, 2007, at 3:28 PM, Tom Allison wrote: I'm trying to find the binaries for pgsql (the client) for Mac OSX. Is there any way to get these without installing all of postgres on a computer? I'm not going to use postgres on my MacBook, just connect to it. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] IF function?
Does postgresql have a built in IF function that allows one to eval a condition and return one or another value? Like: IIF(mybooleanfield = true, It's true, It's not true) -- View this message in context: http://www.nabble.com/IF-function--tf3673523.html#a10264910 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] IF function?
On Mon, Apr 30, 2007 at 10:51:36PM -0700, novnov wrote: Does postgresql have a built in IF function that allows one to eval a condition and return one or another value? Like: IIF(mybooleanfield = true, It's true, It's not true) It has CASE, as in CASE foo WHEN true THEN 'It''s true' ELSE 'It''s not true' END; Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/