Re: [GENERAL] cannot get build (initdb) to work after trying for a week

[Magnus Hagander]

Eric wrote:
> Thanks for responding Magnus.
> 
> I did reinstall both msys and mingw, as well as blow away the source
> and start over from scratch.  Maybe there is a stray file or directory
> that's not getting deleted on the clean.  Also, does clean just clean
> up the compile environment, or does it also clean up the install?  I
> did manually remove directories too but I suppose I could have missed
> something.

Clean cleans the build environment only,


> Finally, as a newcomer to PG, I'm in search of general development
> resources.  Any good places to go besides the PG documentation and the
> pgsql newsgroups?

http://archives.postgresql.org/pgsql-hackers

//Magnus

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Configuring phpPgAdmin and pg_ctl reload

[Ashish Karalkar]

Try

pg_ctl  -D /path to pg data home

e.g. 


pg_ctl -D /usr/local/pgsql/data

Hope this will help


- Original Message - 
From: "John Meyer" <[EMAIL PROTECTED]>

To: "postgresql-general" 
Sent: Thursday, March 22, 2007 8:31 AM
Subject: [GENERAL] Configuring phpPgAdmin and pg_ctl reload


I'm setting up phpPgAdmin and I finally get to the point where you 
reconfigure pg_hba.conf  One of the lines says to reload the values, 
type the command pg_ctl reload.  I try it as super user, no go, I su 
into postgres, it complains:


pg_ctl reload
pg_ctl: no database directory specified and environment variable PGDATA 
unset

Try "pg_ctl --help" for more information.


Keeping in mind that I typed just what pga_hba.conf said, was there a 
step I was missing (I ended up restarting the server and that worked).


---(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


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] PostgreSQL 7.3.4 using Cygwin on a Windows 2003 Server R2 SP2

[Magnus Hagander]

Candy Gutierrez wrote:
> Hi,
> 
> I just want to know if it is possible to install PostgreSQL 7.3.4 using
> Cygwin on a Windows 2003 Server R2 SP2 platform?
> I tried it several times but I couldn't create a database.  But when I
> installed it on Windows 2003 Server (no SPs), it was
> successful.  I have set all the required user privileges on both test
> scenarios.

Why, really, do you want 7.3.4 and why on earth do you want cygwin?

First, if you really want 7.3, you want 7.3.18.

That said, you really don't want 7.3. You want 8.2. Or at least 8.1. 7.3
is ancient, and no longer fully maintained. Especially the windows parts.

And finally, why do you really want cygwin? Since 8.0 we've had a native
Windows port, which works a lot better than the cygwin one. It's a lot
faster, it's a lot more stable, it's actually maintained etc.

Bottom line - if you can, get 8.2.3 native windows version.

//Magnus

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Configuring phpPgAdmin and pg_ctl reload

[Devrim GÜNDÜZ]

Hi,

On Wed, 2007-03-21 at 21:01 -0600, John Meyer wrote:
> 
> pg_ctl reload
> pg_ctl: no database directory specified and environment variable
> PGDATA unset 

You should use

pg_ctl -D /path/to/your/data/dir reload 

See man page for details.

Regards,
-- 
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/




signature.asc
Description: This is a digitally signed message part

[GENERAL] A request for your input.

[lmth]

Hello

My name is Lara Thynne and I am a PhD candidate at Deakin University
Australia.  I am currently researching the boundary between work and
leisure activities directly related to the open source community and
open source program development.

As part of this I am running a survey at the following address.

https://dcarf.deakin.edu.au/surveys/oss/

The survey is completely confidential and looks at your views and
motivations to use Open Source software and to participate in the
community.

It will only take a five to ten minutes to complete and your contact
details will not be recorded. You can withdraw your participation at
any stage.

I sincerely apologize for the spammish nature of this e-mail - I
don't mean to abuse this list.  I am trying to collect responses
from as many open source developers and users as possible and a
mailing list like can be the only way to reach many developers.

Thanks again

Lara

P.S The program that I am using is open source, of course
(www.phpsurveyor.org)!



---(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 query design

[Ottavio Campana]

Hi,

I'm trying to implement some stored procedures but I'm having some
doubts, and I'd like to ask you if I'm doing well or not.

Here's an example of what I'm doing: I have a table like

create table (
  id serial,
  description text not null,
  active boolean default true);

What I want to do is a function inserting a new item into the table
ensuring that there is only one record in the table having a particular
description and at the same time the active field set to true (it might
seem stupid, but the application requires it).

My first solution was a function executing a select on the table
checking for a record with the passed description and the active field
set to true. If a record is found, then the function fails.

This function works, but I don't think it's thread safe, since two
functions could be executed at the same time, so that they pass the test
and insert twice the record. To solve the problem, I tried to put a
constraint on the table, but I didn't figure how to do it. How can I add
the constraint "description is unique among all the record having active
set to true"?

I think that having this constraint would assure me that one of the two
function will fail, so I'll be able to handle the exception. Am I right?

I also have a second small question. In faq 4.11.3 they say that
currval() doesn't lead to race conditions. How does it work? I can't
really understand the meaning of "currval() returns the current value
assigned by your session, not by all sessions".

Thank you.

-- 
Non c'e' piu' forza nella normalita', c'e' solo monotonia.



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

Are there any examples of dblink being used in commercial  
environments.  I am curious to understand how it deals with node  
failures and etc.


Benjamin

On Mar 21, 2007, at 9:35 AM, Oleg Bartunov wrote:


On Wed, 21 Mar 2007, Benjamin Arai wrote:

Can't you implement something similar to google by aggregating  
results for TSearch2 over many machines?


tsearch2 doesn't use any global statistics, so, in principle, you
should be able to run fts on several machines and combine them using
dblink (contrib/dblink).



Benjamin
On Mar 21, 2007, at 8:59 AM, Teodor Sigaev wrote:

I'm afraid that fulltext search on multiterabytes set of  
documents can not be implemented on any RDBMS, at least on single  
box. Specialized fulltext search engines (with exact matching and  
time to search about one second) has practical limit near 20  
millions of docs, cluster - near 100 millions. Bigger collections  
require engines like a google.

--
Teodor Sigaev   E-mail:  
[EMAIL PROTECTED]
  WWW: http:// 
www.sigaev.ru/



---(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: oleg@sai.msu.su, 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/

[GENERAL] PostgreSQL 7.3.4 using Cygwin on a Windows 2003 Server R2 SP2

[Candy Gutierrez]

Hi,

I just want to know if it is possible to install PostgreSQL 7.3.4 using
Cygwin on a Windows 2003 Server R2 SP2 platform?
I tried it several times but I couldn't create a database.  But when I
installed it on Windows 2003 Server (no SPs), it was
successful.  I have set all the required user privileges on both test
scenarios.

Thanks,
kendz

[GENERAL] Configuring phpPgAdmin and pg_ctl reload

[John Meyer]

I'm setting up phpPgAdmin and I finally get to the point where you 
reconfigure pg_hba.conf  One of the lines says to reload the values, 
type the command pg_ctl reload.  I try it as super user, no go, I su 
into postgres, it complains:


pg_ctl reload
pg_ctl: no database directory specified and environment variable PGDATA 
unset

Try "pg_ctl --help" for more information.


Keeping in mind that I typed just what pga_hba.conf said, was there a 
step I was missing (I ended up restarting the server and that worked).


---(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] foreign key constraints with inhertiance, hack suggestions?

[George Nychis]

Hi,

First, yes I have read the "5.8.1. Caveats" section that this support does not 
exist.

I agree with the document that this is "a serious limitation of the inheritance 
feature"

Has there been any effort to support this in the near future versions of postgresql?  I 
searched the mailing lists and didn't quite find a straight answer on that, only that 
there will be some sort of stronger inheritance in 8.2


Second, I was wondering if anyone has any hack suggestions at doing some 
integrity checks?

Basically I have a master 'flows' table which is partitioned and has non-overlapping CHECK 
constraints on each partition.  Each record in the partitions have a unique pair of 
attributes:  interval, flow_id


When inserting in to another table 'flow_labels', these two attributes are given, I want 
to ensure that there exists a single flow in some partition in the 'flows' table that has 
these two values.


I'm no DB expert, but could there be some sort of rule or trigger on insert into 
'flow_labels' that does a count() where these two values are exact to ensure there exists 
1 flow?  Problem is my 'flows' table is on the order of billions of flows, each partition 
having hundreds of thousands.


Suggestions?

Thanks!
George

---(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] Postgres Hot Standby. How or when does the recovery db move recovery.conf to recovery.done?

[Dhaval Shah]

From one of Tom's reply to a different poster, I found that one can run


pg_resetxlog. http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html,
to make the db recover and startup.

Appears not for the faint hearted!

Dhaval

On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote:

I looked at the pg_standby utility and would have liked to use it,
however there are some customer driven extraneous issues in using
that.

What I am looking at it is this:

1. I can detect that the primary has gone down and return a non-zero
for the standby to recover.

2. Since I can detect that I am out of standby mode, I can shutdown
the postgres, move the recovery.conf file to recovery.done manually.
And then restart the db.

Even if I do step 2, I still get the following in the server log:

=
Main: Triggering Recovery!!!  <- my script is returning a non-zero code here ...

PANIC:  could not open file "pg_xlog/0001001B" (log
file 0, segment 27): No such file or directory
LOG:  startup process (PID 32167) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2007-03-20 13:04:28 PDT
HINT:  If this has occurred more than once some data may be corrupted
and you may need to choose an earlier recovery target.
LOG:  could not open file "pg_xlog/00010006" (log file
0, segment 6): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "pg_xlog/00010005" (log file
0, segment 5): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 4676) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2007-03-20 13:04:28 PDT


The question I have is how do I get out of the above mode and ensure
that the db is up and ready? What I need to clear? A previous cache or
something? Am I missing something here? I went to the docs and it says
the following:

"Start the postmaster. The postmaster will go into recovery mode and
proceed to read through the archived WAL files it needs. Upon
completion of the recovery process, the postmaster will rename
recovery.conf to recovery.done (to prevent accidentally re-entering
recovery mode in case of a crash later) and then commence normal
database operations."

And I do not see the recovery.conf go to recovery.done automatically.

Dhaval


On 3/21/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote:
> > Resending.
> >
> > I have a "hot" standby. Now, if the primary fails
> > how do I tell the secondary that come out of recovery mode and move
> > the recovery.conf to recovery.done and start the db. I mean, what
> > error code shall I return?
>
> did you look at pg_standby utility? it has kill file mechanism that
> automates this for you.
>
> merlin
>


--
Dhaval Shah




--
Dhaval Shah

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joris Dobbelsteen]

>-Original Message-
>From: [EMAIL PROTECTED] 
>[mailto:[EMAIL PROTECTED] On Behalf Of Brandon Aiken
>Sent: woensdag 21 maart 2007 15:25
>To: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Lifecycle of PostgreSQL releases 
>
[snip]
>Software *always* has bugs.

Sorry, couldn't resist...

Software Engineer: "My salary payments didn't get through this month, I
still haven't received any of you".
Accountant (former Software Engineer): "Sorry, our systems indicate that
your payment tripped a fatal bug in our payout software. That can
happen, you know, software has bugs after all. It's not problem, its
only a minor one. Well, we'll try again next month, maybe that works."

Could people for once treat bugs as unacceptable instead an accepted
thing?
(Especially people writing software for validating the software we are
writing is correct.)

As I said, I couldn't resist. Sorry...

- Joris

[snip]


---(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] Insert fail: could not open relation with OID 3221204992

[Tom Lane]

[EMAIL PROTECTED] writes:
> I am inserting 783159 records but the insert is failing after 634314
> records. I am getting "ERROR:  could not open relation with OID
> 3221204992" message. I am using

> 1- PostgreSQL: 8.2.3
> 2- OS: Red Hat Enterprise Linux AS release 3.
> 3- Logfile output:
> ERROR:  XX000: could not open relation with OID 3221204992
> LOCATION:  relation_open, heapam.c:700
> STATEMENT:  INSERT INTO parcel (OBJECTID, shape) VALUES ( $1,  $2 )

Please provide more detail, like what the table schema is, what indexes
and foreign keys it has, and exactly what the insert process is
(eg do you have all these inserts wrapped in a BEGIN?).  Also what is
the client-side software?

Is the OID mentioned in the complaint the same every time you try it?

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] best way to kill long running query?

[Martin Gainty]

Bill/Magnus/Tom

No query should be running on ad inifinitum
Take a look at
http://euler.slu.edu/~goldwasser/courses/slu/csa341/2003_Fall/lectures/oracle_optimizer/#optimizer
99% of the queries I see I can optimise by application of these simple rules
do an explain plan
understand everything that this tells you
Make the appropriate adjustments (even it involves building an index)

Take some advice from the undisputed king of oracle optimisation
http://www.dba-oracle.com/articles.htm

Keep me apprised,
Martin--
--- 
This e-mail message (including attachments, if any) is intended for the use of 
the individual or entity to which it is addressed and may contain information 
that is privileged, proprietary , confidential and exempt from disclosure. If 
you are not the intended recipient, you are notified that any dissemination, 
distribution or copying of this communication is strictly prohibited.
--- 
Le présent message électronique (y compris les pièces qui y sont annexées, le 
cas échéant) s'adresse au destinataire indiqué et peut contenir des 
renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le 
destinataire de ce document, nous vous signalons qu'il est strictement interdit 
de le diffuser, de le distribuer ou de le reproduire.
- Original Message - 
From: "Bill Eaton" <[EMAIL PROTECTED]>
To: "Magnus Hagander" <[EMAIL PROTECTED]>
Cc: "Tom Lane" <[EMAIL PROTECTED]>; 
Sent: Wednesday, March 21, 2007 4:04 PM
Subject: Re: [GENERAL] best way to kill long running query?


 I want to allow some queries for my users to run for a
 prescribed period of time and kill them if they go over
 time. Is there a good way to do this?
> 
>>> set statement_timeout perhaps?
>> I don't think you can set GUC parameters from the ODBC driver. Your
>> options are:
>>
>> * postgresql.conf. Will apply to all sessions to the databse.
>>
>> * database. Use ALTER DATABLASE foo SET statement_timeout=.
>> Will then affect all connections to that database.
>>
>> * user. Use ALTER ROLE foo SET statement_timeout=. Will then
>> affect all connections from that user.
>>
>> * change your application to issue a "SET statement_timeout="
>> query before anything else it sends.
>>
> 
> The last option is almost exactly what I wanted. It works quite nicely The
> only downside to this approach is that an error is raised if the timeout is
> exceeded. Which, when I think about it, is probably good behavior --> it
> allows me to distinguish between (1) a query that completed and returned no
> records and (2) a query that aborted because it exceeded the timeout.
> 
> Thanks to all for the assist.
> 
> Bill Eaton
> 
> 
> 
> ---(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
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] can't trace error!!!

[Martijn van Oosterhout]

On Wed, Mar 21, 2007 at 09:02:37AM -0700, Pranjal Karwal wrote:
> greeting...
> 
> i'm trying to execute this phtml file which contains a connection to 
> postgresql : http://rafb.net/p/xUOtZO49.html
> 
> it uses a function astar which is an add-on module for postgis defined in 
> this file: http://rafb.net/p/XF3SaO30.html
> 
> when i execute this file i receive many errors: 
> http://rafb.net/p/vQbxR525.html

Seems at least to be a MapServer or a PostGIS problem. I'd suggest asking
there...

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] Using PostgreSQL to archive personal email

[Holger Hoffstaette]

On Tue, 20 Mar 2007 18:48:15 -0700, [EMAIL PROTECTED] wrote:

> Does anyone know of any apps using PostgreSQL to archive their personal
> email and make it searchable?  And that runs on Mac OS X?

http://www.dbmail.org/ ?:)

-h



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] best way to kill long running query?

[Vivek Khera]

On Mar 21, 2007, at 3:09 PM, Bill Eaton wrote:

I want to allow some queries for my users to run for a prescribed  
period

of
time and kill them if they go over time. Is there a good way to  
do this?



set statement_timeout perhaps?


Ooh. I like that. It would be absolutely brilliant if I could  
figure out how
to get it to work with ADO and the Windoze ODBC driver. I've tried  
appending

statement_timeout to my connection string i.e.
  ConnString = "DRIVER={PostgreSQL
Unicode};SERVER=MYSERVER;DATABASE=MYDB;UID=client;set_timeout=1"
but it has no effect on a SELECT statement that takes at least 3 or 4
seconds to execute and only returns 184 (out of 600,000) records.

I've also tried different syntaxes to pass the parameter
  set_timeout=1
  set_timeout='1'
  set_timeout=(1)
  set_timeout=('1')


that doesn't look like "statement_timeout" to me, but then my glasses  
might be out of date.


try this as postgres superuser for your user:

alter user foobar set statement_timeout=1;

where foobar is the user you connect as.

then this user's default statement_timeout is set he can override  
it at will, though.




smime.p7s
Description: S/MIME cryptographic signature

Re: [GENERAL] best way to kill long running query?

[Bill Eaton]

>>> I want to allow some queries for my users to run for a
>>> prescribed period of time and kill them if they go over
>>> time. Is there a good way to do this?

>> set statement_timeout perhaps?
> I don't think you can set GUC parameters from the ODBC driver. Your
> options are:
>
> * postgresql.conf. Will apply to all sessions to the databse.
>
> * database. Use ALTER DATABLASE foo SET statement_timeout=.
> Will then affect all connections to that database.
>
> * user. Use ALTER ROLE foo SET statement_timeout=. Will then
> affect all connections from that user.
>
> * change your application to issue a "SET statement_timeout="
> query before anything else it sends.
>

The last option is almost exactly what I wanted. It works quite nicely The
only downside to this approach is that an error is raised if the timeout is
exceeded. Which, when I think about it, is probably good behavior --> it
allows me to distinguish between (1) a query that completed and returned no
records and (2) a query that aborted because it exceeded the timeout.

Thanks to all for the assist.

Bill Eaton



---(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] invalid byte sequence for encoding "UTF8"

[Martijn van Oosterhout]

On Wed, Mar 21, 2007 at 09:54:41AM -0700, Alan Hodgson wrote:
> iconv needs to read the whole file into RAM.  What you can do is use the 
> UNIX split utility to split the dump file into smaller segments, use iconv 
> on each segment, and then cat all the converted segments back together into 
> a new dump file.  iconv is I think your best option for converting the dump 
> to a valid encoding.

The guys at openstreetmap have written a UTF-8 cleaner that doesn't
read the whole file into memory:

http://trac.openstreetmap.org/browser/utils/planet.osm/C

Definitly more convenient for large files.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] best way to kill long running query?

[Magnus Hagander]

Bill Eaton wrote:
>>> I want to allow some queries for my users to run for a prescribed period
> of
>>> time and kill them if they go over time. Is there a good way to do this?
> 
>> set statement_timeout perhaps?
> 
> Ooh. I like that. It would be absolutely brilliant if I could figure out how
> to get it to work with ADO and the Windoze ODBC driver. I've tried appending
> statement_timeout to my connection string i.e.
>   ConnString = "DRIVER={PostgreSQL
> Unicode};SERVER=MYSERVER;DATABASE=MYDB;UID=client;set_timeout=1"
> but it has no effect on a SELECT statement that takes at least 3 or 4
> seconds to execute and only returns 184 (out of 600,000) records.
> 
> I've also tried different syntaxes to pass the parameter
>   set_timeout=1
>   set_timeout='1'
>   set_timeout=(1)
>   set_timeout=('1')

I don't think you can set GUC parameters from the ODBC driver. Your
options are:

* postgresql.conf. Will apply to all sessions to the databse.

* database. Use ALTER DATABLASE foo SET statement_timeout=.
Will then affect all connections to that database.

* user. Use ALTER ROLE foo SET statement_timeout=. Will then
affect all connections from that user.

* change your application to issue a "SET statement_timeout="
query before anything else it sends.


Note that statement timeout will cancel the whole command. It won't
return "as many rows as it has reached by the timeout", it will return
nothing at all.

//Magnus

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] best way to kill long running query?

[Bill Eaton]

>> I want to allow some queries for my users to run for a prescribed period
of
>> time and kill them if they go over time. Is there a good way to do this?

> set statement_timeout perhaps?

Ooh. I like that. It would be absolutely brilliant if I could figure out how
to get it to work with ADO and the Windoze ODBC driver. I've tried appending
statement_timeout to my connection string i.e.
  ConnString = "DRIVER={PostgreSQL
Unicode};SERVER=MYSERVER;DATABASE=MYDB;UID=client;set_timeout=1"
but it has no effect on a SELECT statement that takes at least 3 or 4
seconds to execute and only returns 184 (out of 600,000) records.

I've also tried different syntaxes to pass the parameter
  set_timeout=1
  set_timeout='1'
  set_timeout=(1)
  set_timeout=('1')



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] to_tsvector in 8.2.3

[Teodor Sigaev]

postgres=# select to_tsvector('test text');
  to_tsvector
---
 'test text':1
(1 row)
Ok. that's related to 
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/tsearch2/wordparser/parser.c.diff?r1=1.11;r2=1.12;f=h
commit. Thomas pointed that it can be non-breakable space (0xa0) and that commit 
assumes any character with C locale and multibyte encoding and > 0x7f is alpha.

To check theory, pls, apply attached patch.

If so, I'm confused, we can not assume that 0xa0 is a space symbol in any 
multibyte encoding, even in Windows.




--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/
*** ./contrib/tsearch2/wordparser/parser.c.orig Wed Mar 21 20:41:23 2007
--- ./contrib/tsearch2/wordparser/parser.c  Wed Mar 21 21:10:39 2007
***
*** 124,130 
--- 124,134 
 * with C-locale is an alpha character
 */
if ( c > 0x7f )
+   {
+   if ( c == 0xa0 )
+   return 0;
return 1;
+   }
  
return isalnum(0xff & c);
}
***
*** 157,163 
--- 161,171 
 * with C-locale is an alpha character
 */
if ( c > 0x7f )
+   {
+   if ( c == 0xa0 )
+   return 0;
return 1;
+   }
  
return isalpha(0xff & c);
}

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] best way to kill long running query?

[Tom Lane]

"Bill Eaton" <[EMAIL PROTECTED]> writes:
> I want to allow some queries for my users to run for a prescribed period of
> time and kill them if they go over time. Is there a good way to do this?

set statement_timeout perhaps?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] best way to kill long running query?

[Bill Eaton]

I want to allow some queries for my users to run for a prescribed period of
time and kill them if they go over time. Is there a good way to do this? Or
is this a bad idea?

I've been struggling with trying to figure out the best way to allow users
to browse through large tables. For example, I have one table with about
600,000 rows and growing at about 100,000/month.

I want to allow users to browse through this table, but only if their
effective SELECT statement only generates 100 or maybe 1000 rows. There are
several fields that can be used in the WHERE clause, such as user, date,
model, etc. It will be difficult for me to predict how large a result set is
a priori. So I want to allow the query to run for a prescribed period of
time, then kill it.

I'll probably be using ADO --> ODBC at the client. So I could probably kill
the Connection/Recordset. I just don't know the best way to do it. pgAdmin
allows queries to be killed. How does it do it?

Thanks in advance,

Bill Eaton
Thousand Oaks, CA



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] to_tsvector in 8.2.3

[Magnus Hagander]

On Wed, Mar 21, 2007 at 04:25:30PM +0300, Teodor Sigaev wrote:
> I can't reproduce your problem, but I have not Windows box, can anybody 
> reproduce that?
> 
> 
> contrib_regression=# select version();
>  version 
> 
>  PostgreSQL 8.2.3 on i386-unknown-freebsd6.2, compiled by GCC gcc (GCC) 
>  3.4.6 [FreeBSD] 20060305
> (1 row)
> contrib_regression=#  show server_encoding ;
>  server_encoding
> -
>  UTF8
> (1 row)
> 
> contrib_regression=# show lc_collate;
>  lc_collate
> 
>  C
> (1 row)
> 
> contrib_regression=# show lc_ctype;
>  lc_ctype
> --
>  C
> (1 row)
> 
> contrib_regression=# select to_tsvector('test text');
> to_tsvector
> ---
>  'test':1 'text':2
> (1 row)

postgres=# select version();show lc_collate;show lc_ctype;
   version


-
 PostgreSQL 8.3devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.2 (mi
ngw-special)
(1 row)

 lc_collate

 C
(1 row)

 lc_ctype
--
 C
(1 row)

postgres=# select to_tsvector('test text');
  to_tsvector
---
 'test text':1
(1 row)


(encoding is UTF8 on a fresh database)

//Magnus


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] SoC Ideas for people looking for projects

[Chris Browne]

[EMAIL PROTECTED] (Benjamin Arai) writes:
> If you are looking for a SoC idea, I have listed a couple below.  I
> am not sure how good of an idea they are but I have ran into the
> following limitations and probably other people have as well in the
> past.

Actually, I have a thought on a SoC idea...

The general notion would be to try to come up with some more rational
information on setting the default column statistics width.

http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
http://www.postgresql.org/docs/8.2/interactive/planner-stats.html

Now, the default value has long been 10.  There are cases where people
find they need to set it higher; that has always been pretty
trial-and-error.

My suspicion is that:

a) The default should probably be a bit higher than 10

b) Some analysis of stats and schema on an individual table could
perhaps provide more specific values for specific columns.

 - Data type might provide guidance; there's little need for >3 values on
   a binary column, for instance.

 - If there is a NOT NULL UNIQUE constraint on a column, that might
   suggest > 10 values

 - If the column is known to have 150 unique values, that might
   suggest SET STATISTICS 150

   It might be worth looking at the *least* frequently occuring
   values, and set stats high enough to make it likely that at least
   one such value would be pulled in...

 - Some kinds of values (dates, floats) are sorta continuous in value;
   having 10 bins may be pretty OK for such

There are probably some other heuristics to be had; this is just some
ideas off the top of my head.

Nobody has gone through any sort of real analysis of this; there
likely is merit to doing so...
-- 
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/finances.html
Where do you  *not* want to go today?  "Confutatis maledictis, flammis
acribus addictis" (

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] invalid byte sequence for encoding "UTF8"

[Alan Hodgson]

On Wednesday 21 March 2007 04:17, "Fuzzygoth" <[EMAIL PROTECTED]> 
wrote:
> I've searched the forums and found people with similar problems but
> not much
> on a way to remedy it. I did try using iconv which was suggested in a
> thread
> but it returned an error saying even the 22GB file was too large to
> work on.

iconv needs to read the whole file into RAM.  What you can do is use the 
UNIX split utility to split the dump file into smaller segments, use iconv 
on each segment, and then cat all the converted segments back together into 
a new dump file.  iconv is I think your best option for converting the dump 
to a valid encoding.

-- 
"None are more hopelessly enslaved than those who falsely believe they are
free." -- Johann W. Von Goethe


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] multi terabyte fulltext searching

[Joshua D. Drake]

Benjamin Arai wrote:
> 24.

I can think of a couple of things.

1. Increase your spindle count.
2. Push your gist indexes off to another array entirely (with separate
controllers)
3. Split your actual tables between other arrays

Or... by a SAN (but then again, I just replaced a million dollar SAN
with a split array/controller enviroment that cost 10% of the cost and
the multiple controller solution is kicking its butt.)


Joshua D. Drake


> 
> Benjamin
> 
> On Mar 21, 2007, at 9:09 AM, Joshua D. Drake wrote:
> 
>> Benjamin Arai wrote:
>>> True, but what happens when my database reaches 100 terabytes? Is 5
>>> seconds ok? How about 10?  My problem is that I do not believe the
>>> performance loss I am experiencing as the data becomes large is (log the
>>> # of records).  This worries me because I could be doing something
>>> wrong.  Or I might be able to do something better.
>>
>> Well a couple of things you could do, especially if you have the ability
>> to throw hardware at it.
>>
>> How many spindles do you have?
>>
>> J
>>
>>
>>>
>>> Benjamin
>>>
>>> On Mar 21, 2007, at 8:49 AM, Joshua D. Drake wrote:
>>>
 Benjamin Arai wrote:
> Hi,
>
> I have been struggling with getting fulltext searching for very large
> databases.  I can fulltext index 10s if gigs without any problem but
> when I start geting to hundreds of gigs it becomes slow.  My current
> system is a quad core with 8GB of memory.  I have the resource to
> throw
> more hardware at it but realistically it is not cost effective to
> buy a
> system with 128GB of memory.  Is there any solutions that people have
> come up with for indexing very large text databases?

 GIST indexes are very large.

> Essentially I have several terabytes of text that I need to index. 
> Each
> record is about 5 paragraphs of text.  I am currently using TSearch2
> (stemming and etc) and getting sub-optimal results.  Queries take more
> than a second to execute.

 you are complaining about more than a second with a terabyte of text?


>  Has anybody implemented such a database using
> multiple systems or some special add-on to TSearch2 to make things
> faster?  I want to do something like partitioning the data into
> multiple
> systems and merging the ranked results at some master node.  Is
> something like this possible for PostgreSQL or must it be a software
> solution?
>
> Benjamin
>
> ---(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
>


 --  === The PostgreSQL Company: Command Prompt, Inc. ===
 Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
 Providing the most comprehensive  PostgreSQL solutions since 1997
  http://www.commandprompt.com/

 Donate to the PostgreSQL Project:
 http://www.postgresql.org/about/donate
 PostgreSQL Replication: http://www.commandprompt.com/products/

>>>
>>>
>>> ---(end of broadcast)---
>>> TIP 5: don't forget to increase your free space map settings
>>>
>>
>>
>> -- 
>>   === The PostgreSQL Company: Command Prompt, Inc. ===
>> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>> Providing the most comprehensive  PostgreSQL solutions since 1997
>>  http://www.commandprompt.com/
>>
>> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>> PostgreSQL Replication: http://www.commandprompt.com/products/
>>
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] multi terabyte fulltext searching

[Oleg Bartunov]

On Wed, 21 Mar 2007, Benjamin Arai wrote:

Can't you implement something similar to google by aggregating results for 
TSearch2 over many machines?


tsearch2 doesn't use any global statistics, so, in principle, you
should be able to run fts on several machines and combine them using
dblink (contrib/dblink).



Benjamin
On Mar 21, 2007, at 8:59 AM, Teodor Sigaev wrote:

I'm afraid that fulltext search on multiterabytes set of documents can not 
be implemented on any RDBMS, at least on single box. Specialized fulltext 
search engines (with exact matching and time to search about one second) 
has practical limit near 20 millions of docs, cluster - near 100 millions. 
Bigger collections require engines like a google.



--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
  WWW: 
http://www.sigaev.ru/





---(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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Anyone still using the sql_inheritance parameter?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/21/07 11:13, Tom Lane wrote:
> Ron Johnson <[EMAIL PROTECTED]> writes:
>> On 03/21/07 09:49, Tom Lane wrote:
>>> Is anybody still using the ability to set sql_inheritance to OFF?
> 
>> Shouldn't features be deprecated for a version before removal?
> 
> Effectively, that feature's been deprecated since 7.1 ...

But not explicitly.



-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGAWJHS9HxQb37XmcRAtciAKC6IYE3EsdcMe4Y6mKKu143URsXzgCffjOY
EqkoNv0cOMfQShN2WZcQ0Ro=
=CPNn
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PHP] Re: [GENERAL] phpPgAdmin - prior version available?

[Tijnema !]

On 3/21/07, Robert Treat <[EMAIL PROTECTED]> wrote:

On Sunday 18 March 2007 12:41, Bob Hartung wrote:
> Hi all,
>I have been struggling with phpPgAdmin 4.1 - login failures.  There
> does not yet seem to be a fix.  Where can I find a prior version for FC6
> - rpm, tar.gz etc.
>

Can you be a bit more specific on the problem you're seeing?


I already solved his problem, he replied to that, but not to the list...
his message:

got it,  Thanks again!

Bob


--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] sql indexing suggestions needed

[Angva]

On Mar 20, 2:54 pm, [EMAIL PROTECTED] (Jonathan Vanasco) wrote:
> can anyone suggest an indexing approach that might get pg to use the
> indexes ?  this is driving me crazy.

Have you tried an expression-based index?
http://www.postgresql.org/docs/8.1/interactive/indexes-expressional.html

I'm assuming the right side of each equals sign is constant for you.
Basically, you could create an expression-based index on all these
conditions, which would evaluate to true or false for every row in
your table.

Caveat: If you have heavy updates/inserts this might be a bad idea.
Also, the planner may still decide to seq scan, based on your
configuration and resultset size.

Mark


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] sql indexing suggestions needed

[Jonathan Vanasco]

i'm going crazy trying to optimize this select.

The table has ~25 columns, the select is based on 10.   There are  
approx 5 million records in the table and growing.


No matter how I index + analyze this table, including making an index  
of every related column on the search, pg keeps doing a sequential  
scan and never includes an index -- which takes ~2minutes to do.  I  
really need to cut this down.


SELECT
*
FROM
table_a
WHERE
( bool_a = False )
AND
( bool_b= False )
AND
( int_c IS NOT NULL )
AND
( int_c <= 10 )
AND
( bool_d = False )
AND
( bool_e= True )
AND
( timestamp_f IS NULL )
AND
( bool_g IS False )
AND
( int_h= 1 )
AND
( bool_i = False )
ORDER BY
id ASC
LIMIT 100

can anyone suggest an indexing approach that might get pg to use the  
indexes ?  this is driving me crazy.


thanks.




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

Hi,

I have been struggling with getting fulltext searching for very large  
databases.  I can fulltext index 10s if gigs without any problem but  
when I start geting to hundreds of gigs it becomes slow.  My current  
system is a quad core with 8GB of memory.  I have the resource to  
throw more hardware at it but realistically it is not cost effective  
to buy a system with 128GB of memory.  Is there any solutions that  
people have come up with for indexing very large text databases?


Essentially I have several terabytes of text that I need to index.   
Each record is about 5 paragraphs of text.  I am currently using  
TSearch2 (stemming and etc) and getting sub-optimal results.  Queries  
take more than a second to execute.  Has anybody implemented such a  
database using multiple systems or some special add-on to TSearch2 to  
make things faster?  I want to do something like partitioning the  
data into multiple systems and merging the ranked results at some  
master node.  Is something like this possible for PostgreSQL or must  
it be a software solution?


Benjamin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Using PostgreSQL to archive personal email

[[EMAIL PROTECTED]]

Hi,

Does anyone know of any apps using PostgreSQL to archive their
personal email and make it searchable?  And that runs on Mac OS X?

thanks,
matt


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] invalid byte sequence for encoding "UTF8"

[Fuzzygoth]

Hi,

I am trying currently trying to setup our new database sever, we have
upgraded
to PostgreSQL 8.1.8. When I try to restore the backup (which is stored
as a set
of SQL statements that my restore script feeds into PSQL to execute)
it returns
the following error.

psql:/mnt/tmp/app/application_data.sql:97425: ERROR:  invalid byte
sequence for encoding "UTF8": 0xff

HINT:  This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".

along other byte sequences eg: 0xa1, 0xac, the two remaining schemas
are
roughly 22GB and 66GB in size and is read into postgres from flat
cobol
datafiles.

our data has progressed as displayed below
PostgreSQL 7.?.? Stored in SQL-ASCII (Old configuration)
PostgreSQL 8.1.3 Stored in UTF8 (current conguration)
PostgreSQL 8.1.8 Stored in UTF8 (our future configuration)

The encoding type set on the server was changed to UTF8 from SQL-ASCII
after
we moved to version 8.1.3 for purposes of globalisation.

I've searched the forums and found people with similar problems but
not much
on a way to remedy it. I did try using iconv which was suggested in a
thread
but it returned an error saying even the 22GB file was too large to
work on.

any help would be gratfully appreciated.

Many Thanks
David P


---(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

[GENERAL] SoC Ideas for people looking for projects

[Benjamin Arai]

Hi,

If you are looking for a SoC idea, I have listed a couple below.  I  
am not sure how good of an idea they are but I have ran into the  
following limitations and probably other people have as well in the  
past.


1. Can user based priorities be implemented as a summer project?  To  
some extent it has already been implemented in research (http:// 
www.cs.cmu.edu/~bianca/icde04.pdf), so it is definitely possible and  
scalable.


2. Distributed full-text indexing.  This one I am really not sure how  
possible it is but  (TSearch2) very scalable (cannot do multi  
terabyte fulltext indexes).  Maybe some sort system could be devised  
to perform fulltext searches over multiple systems and merge the  
ranked results at some root node.


Benjamin

On Mar 20, 2007, at 10:07 AM, Josh Berkus wrote:


Students & Professors,

There are only 5 days left to submit your PostgreSQL Google Summer  
of Code Project:

http://www.postgresql.org/developer/summerofcode.html

If you aren't a student, but know a CS student interested in  
databases, testing, GUIs, or any other OSS coding, please point  
them to our SoC page and encourage them to apply right away!


If you are a student, and you've been trying to perfect your  
application, please go ahead and submit it ... we can't help you if  
you miss the deadline, but we can help you fix an incomplete  
application.


--Josh Berkus

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] shell script to SQL statement: `pg_dump | psql -U`

[filippo]

On 20 Mar, 16:47, [EMAIL PROTECTED] (Tom Lane) wrote:
> "filippo" <[EMAIL PROTECTED]> writes:
> > The problem with the current implementation is that sometime pd_dump
> > and psql ask for password but I want to create the database copy
> > without any user typing (it is a cron script).
>
> Create a ~/.pgpass file for the user that runs the cron job.

thanks Tom and Richard, actually it works now. However I wonder if
there is a SQL way, I'm partially in a Windows enviroment and I don't
trust very much the microsoft security.

Thanks and regards,

Filippo


---(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

[GENERAL] multi-row check constraints?

[Angva]

Dear Postgres fans,

Hi, I was wondering what is the best way to achieve a multi-row check
constraint. For example, you have a table with two columns: ID and
percent, no primary key. The goal is to enforce that all values of
percent, per ID, add up to exactly 100%. I come from an Oracle
background, and what you would probably do on Oracle is create a
materialized view with the sum(percent) grouped by ID, then put a
constraint on the sum column. This problem is also solvable using
triggers, but it's messy and imposes a lot of serialization. Not to
mention easy to get wrong.

So, I've come across this problem in Postgres and was hoping someone
could steer me in the right direction.

Thank you,
Mark


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Limiting user connnections on 7.4

[Saqib Awan]

is there an existing mechanism to do user based connection controls in 
Postgres 7.4? 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] multi terabyte fulltext searching

[Oleg Bartunov]

On Wed, 21 Mar 2007, Benjamin Arai wrote:


What is inheritance+CE?


Hmm,

http://www.postgresql.org/docs/8.2/static/ddl-inherit.html
http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html




Benjamin
On Mar 21, 2007, at 9:10 AM, Oleg Bartunov wrote:


inheritance+CE





Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(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] multi terabyte fulltext searching

[Teodor Sigaev]

I am currently using GIST indexes because I receive about 10GB of new 
data a week (then again I am not deleting any information).  The do not 
expect to be able to stop receiving text for about 5 years, so the data 
is not going to become static any time soon.  The reason I am concerned 
with performance is that I am providing a search system for several 
newspapers since essentially the beginning of time.  Many bibliographer 
etc would like to use this utility but if each search takes too long I 
am not going to be able to support many concurrent users.


Use GiST and GIN indexes together: any data older than one month (which doesn't 
change) with GIN index and new data with GiST. And one time per month moves data 
from GiST to GIN.


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

What is inheritance+CE?

Benjamin
On Mar 21, 2007, at 9:10 AM, Oleg Bartunov wrote:


inheritance+CE

Re: [GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

Can't you implement something similar to google by aggregating  
results for TSearch2 over many machines?


Benjamin
On Mar 21, 2007, at 8:59 AM, Teodor Sigaev wrote:

I'm afraid that fulltext search on multiterabytes set of documents  
can not be implemented on any RDBMS, at least on single box.  
Specialized fulltext search engines (with exact matching and time  
to search about one second) has practical limit near 20 millions of  
docs, cluster - near 100 millions.  Bigger collections require  
engines like a google.



--
Teodor Sigaev   E-mail:  
[EMAIL PROTECTED]
   WWW: http:// 
www.sigaev.ru/





---(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] Anyone still using the sql_inheritance parameter?

[Tom Lane]

"codeWarrior" <[EMAIL PROTECTED]> writes:
> What is the potential impact for the "ONLY" qualifier ??? None I would 
> expect, as the "ONLY" qualifier effectively sets SQL_INHERITANCE = off for 
> that specific query -- 

> What about "decorated" table names: i.e: "SELECT * FROM cities*" ??? Do we 
> get to keep this feature ?

Sure, I see no need to muck with either of those syntaxes.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Anyone still using the sql_inheritance parameter?

[Tom Lane]

Ron Johnson <[EMAIL PROTECTED]> writes:
> On 03/21/07 09:49, Tom Lane wrote:
>> Is anybody still using the ability to set sql_inheritance to OFF?

> Shouldn't features be deprecated for a version before removal?

Effectively, that feature's been deprecated since 7.1 ...

regards, tom lane

---(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] multi terabyte fulltext searching

[Benjamin Arai]

24.

Benjamin

On Mar 21, 2007, at 9:09 AM, Joshua D. Drake wrote:


Benjamin Arai wrote:

True, but what happens when my database reaches 100 terabytes? Is 5
seconds ok? How about 10?  My problem is that I do not believe the
performance loss I am experiencing as the data becomes large is  
(log the

# of records).  This worries me because I could be doing something
wrong.  Or I might be able to do something better.


Well a couple of things you could do, especially if you have the  
ability

to throw hardware at it.

How many spindles do you have?

J




Benjamin

On Mar 21, 2007, at 8:49 AM, Joshua D. Drake wrote:


Benjamin Arai wrote:

Hi,

I have been struggling with getting fulltext searching for very  
large
databases.  I can fulltext index 10s if gigs without any problem  
but
when I start geting to hundreds of gigs it becomes slow.  My  
current
system is a quad core with 8GB of memory.  I have the resource  
to throw
more hardware at it but realistically it is not cost effective  
to buy a
system with 128GB of memory.  Is there any solutions that people  
have

come up with for indexing very large text databases?


GIST indexes are very large.

Essentially I have several terabytes of text that I need to  
index.  Each
record is about 5 paragraphs of text.  I am currently using  
TSearch2
(stemming and etc) and getting sub-optimal results.  Queries  
take more

than a second to execute.


you are complaining about more than a second with a terabyte of  
text?




 Has anybody implemented such a database using
multiple systems or some special add-on to TSearch2 to make things
faster?  I want to do something like partitioning the data into  
multiple

systems and merging the ranked results at some master node.  Is
something like this possible for PostgreSQL or must it be a  
software

solution?

Benjamin

---(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




--
  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/ 
donate

PostgreSQL Replication: http://www.commandprompt.com/products/




---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/ 
donate

PostgreSQL Replication: http://www.commandprompt.com/products/




---(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] multi terabyte fulltext searching

[Oleg Bartunov]

On Wed, 21 Mar 2007, Benjamin Arai wrote:


Hi Oleg,

I am currently using GIST indexes because I receive about 10GB of new data a 
week (then again I am not deleting any information).  The do not expect to be 
able to stop receiving text for about 5 years, so the data is not going to 
become static any time soon.  The reason I am concerned with performance is 
that I am providing a search system for several newspapers since essentially 
the beginning of time.  Many bibliographer etc would like to use this utility 
but if each search takes too long I am not going to be able to support many 
concurrent users.




GiST is ok for your feed, but archive part should use GIN index. 
inheritance+CE should help your life.



Benjamin

On Mar 21, 2007, at 8:42 AM, Oleg Bartunov wrote:


Benjamin,

as one of the author of tsearch2 I'd like to know more about your setup.
tsearch2 in 8.2 has GIN index support, which scales much better than old
GiST index.

Oleg

On Wed, 21 Mar 2007, Benjamin Arai wrote:


Hi,

I have been struggling with getting fulltext searching for very large 
databases.  I can fulltext index 10s if gigs without any problem but when 
I start geting to hundreds of gigs it becomes slow.  My current system is 
a quad core with 8GB of memory.  I have the resource to throw more 
hardware at it but realistically it is not cost effective to buy a system 
with 128GB of memory.  Is there any solutions that people have come up 
with for indexing very large text databases?


Essentially I have several terabytes of text that I need to index.  Each 
record is about 5 paragraphs of text.  I am currently using TSearch2 
(stemming and etc) and getting sub-optimal results.  Queries take more 
than a second to execute.  Has anybody implemented such a database using 
multiple systems or some special add-on to TSearch2 to make things faster? 
I want to do something like partitioning the data into multiple systems 
and merging the ranked results at some master node.  Is something like 
this possible for PostgreSQL or must it be a software solution?


Benjamin

---(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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
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: oleg@sai.msu.su, 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] multi terabyte fulltext searching

[Joshua D. Drake]

Benjamin Arai wrote:
> True, but what happens when my database reaches 100 terabytes? Is 5
> seconds ok? How about 10?  My problem is that I do not believe the
> performance loss I am experiencing as the data becomes large is (log the
> # of records).  This worries me because I could be doing something
> wrong.  Or I might be able to do something better.

Well a couple of things you could do, especially if you have the ability
to throw hardware at it.

How many spindles do you have?

J


> 
> Benjamin
> 
> On Mar 21, 2007, at 8:49 AM, Joshua D. Drake wrote:
> 
>> Benjamin Arai wrote:
>>> Hi,
>>>
>>> I have been struggling with getting fulltext searching for very large
>>> databases.  I can fulltext index 10s if gigs without any problem but
>>> when I start geting to hundreds of gigs it becomes slow.  My current
>>> system is a quad core with 8GB of memory.  I have the resource to throw
>>> more hardware at it but realistically it is not cost effective to buy a
>>> system with 128GB of memory.  Is there any solutions that people have
>>> come up with for indexing very large text databases?
>>
>> GIST indexes are very large.
>>
>>> Essentially I have several terabytes of text that I need to index.  Each
>>> record is about 5 paragraphs of text.  I am currently using TSearch2
>>> (stemming and etc) and getting sub-optimal results.  Queries take more
>>> than a second to execute.
>>
>> you are complaining about more than a second with a terabyte of text?
>>
>>
>>>  Has anybody implemented such a database using
>>> multiple systems or some special add-on to TSearch2 to make things
>>> faster?  I want to do something like partitioning the data into multiple
>>> systems and merging the ranked results at some master node.  Is
>>> something like this possible for PostgreSQL or must it be a software
>>> solution?
>>>
>>> Benjamin
>>>
>>> ---(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
>>>
>>
>>
>> -- 
>>   === The PostgreSQL Company: Command Prompt, Inc. ===
>> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>> Providing the most comprehensive  PostgreSQL solutions since 1997
>>  http://www.commandprompt.com/
>>
>> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>> PostgreSQL Replication: http://www.commandprompt.com/products/
>>
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [HACKERS] [GENERAL] Remove add_missing_from_clause?

[Joshua D. Drake]

> 
>> Oh, I know it is still in use, that is my complaint :). Perhaps stating
>> that this will be the last release of the feature?
> 
> Stating it doesn't make it so ;-)

O.k. that is certainly true :)


> If we remove add_missing_from then some people will be unable to migrate
> forward from pre-8.1 releases because their views won't load.  Seeing
> that you have elsewhere been arguing for the wisdom of skipping
> releases, I can hardly think that you'd be in favor of that.

Oh, sure, throw that at me ;). I concede the point.

Sincerely,

Joshua D. Drake

> 
>   regards, tom lane
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] can't trace error!!!

[Pranjal Karwal]

greeting...

i'm trying to execute this phtml file which contains a connection to postgresql 
: http://rafb.net/p/xUOtZO49.html

it uses a function astar which is an add-on module for postgis defined in this 
file: http://rafb.net/p/XF3SaO30.html

when i execute this file i receive many errors: http://rafb.net/p/vQbxR525.html

a snapshot of the db table i'm using is here http://rafb.net/p/oKIR1a92.html



i've tried to trace the problem but could'nt get to the root.

does anyone know how to get to the root of such a prob.

i'm using pgRouting module (www.postlbs.org), mapserver, postgis-1.2.1
on ubuntu


thanks for any help. 

PranjaL





 

Now that's room service!  Choose from over 150,000 hotels
in 45,000 destinations on Yahoo! Travel to find your fit.
http://farechase.yahoo.com/promo-generic-14795097

Re: [GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

By the way, what is the largest TSearch2 database that you know of  
and how fast does it return results?  Maybe my expectations are  
unrealistic.


Benjamin

On Mar 21, 2007, at 8:42 AM, Oleg Bartunov wrote:


Benjamin,

as one of the author of tsearch2 I'd like to know more about your  
setup.
tsearch2 in 8.2 has GIN index support, which scales much better  
than old

GiST index.

Oleg

On Wed, 21 Mar 2007, Benjamin Arai wrote:


Hi,

I have been struggling with getting fulltext searching for very  
large databases.  I can fulltext index 10s if gigs without any  
problem but when I start geting to hundreds of gigs it becomes  
slow.  My current system is a quad core with 8GB of memory.  I  
have the resource to throw more hardware at it but realistically  
it is not cost effective to buy a system with 128GB of memory.  Is  
there any solutions that people have come up with for indexing  
very large text databases?


Essentially I have several terabytes of text that I need to  
index.  Each record is about 5 paragraphs of text.  I am currently  
using TSearch2 (stemming and etc) and getting sub-optimal  
results.  Queries take more than a second to execute.  Has anybody  
implemented such a database using multiple systems or some special  
add-on to TSearch2 to make things faster?  I want to do something  
like partitioning the data into multiple systems and merging the  
ranked results at some master node.  Is something like this  
possible for PostgreSQL or must it be a software solution?


Benjamin

---(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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [GENERAL] Remove add_missing_from_clause?

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> No, AFAICT it's still in active use --- you need not look back far in
>> the mailing lists to find questions answered by "see add_missing_from".

> Oh, I know it is still in use, that is my complaint :). Perhaps stating
> that this will be the last release of the feature?

Stating it doesn't make it so ;-)

I actually don't see a big problem with add_missing_from.  It's not a
security hole because (a) it doesn't affect properly written queries and
(b) the worst you can do to an improperly written query is make it fail
in an obvious way.  So I'm willing to leave it around for legacy apps
for a good while; certainly as long as we left sql_inheritance in there,
which will have been 6+ years if it goes away in 8.3.  The clock on
add_missing_from can only be said to have started running when we made
it false by default, which was 8.1 --- so it's got about five years left...

Also, I see in the 8.1 release notes

 It may be necessary to set add_missing_from to true in order to
 load an existing dump file, if the dump contains any views or rules
 created using the implicit-FROM syntax. This should be a one-time
 annoyance, because PostgreSQL 8.1 will convert such views and rules
 to standard explicit-FROM syntax. Subsequent dumps will therefore
 not have the problem.

If we remove add_missing_from then some people will be unable to migrate
forward from pre-8.1 releases because their views won't load.  Seeing
that you have elsewhere been arguing for the wisdom of skipping
releases, I can hardly think that you'd be in favor of that.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] multi terabyte fulltext searching

[Teodor Sigaev]

I'm afraid that fulltext search on multiterabytes set of documents can not be 
implemented on any RDBMS, at least on single box. Specialized fulltext search 
engines (with exact matching and time to search about one second) has practical 
limit near 20 millions of docs, cluster - near 100 millions.  Bigger collections 
require engines like a google.



--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

---(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] multi terabyte fulltext searching

[Benjamin Arai]

True, but what happens when my database reaches 100 terabytes? Is 5  
seconds ok? How about 10?  My problem is that I do not believe the  
performance loss I am experiencing as the data becomes large is (log  
the # of records).  This worries me because I could be doing  
something wrong.  Or I might be able to do something better.


Benjamin

On Mar 21, 2007, at 8:49 AM, Joshua D. Drake wrote:


Benjamin Arai wrote:

Hi,

I have been struggling with getting fulltext searching for very large
databases.  I can fulltext index 10s if gigs without any problem but
when I start geting to hundreds of gigs it becomes slow.  My current
system is a quad core with 8GB of memory.  I have the resource to  
throw
more hardware at it but realistically it is not cost effective to  
buy a

system with 128GB of memory.  Is there any solutions that people have
come up with for indexing very large text databases?


GIST indexes are very large.

Essentially I have several terabytes of text that I need to  
index.  Each

record is about 5 paragraphs of text.  I am currently using TSearch2
(stemming and etc) and getting sub-optimal results.  Queries take  
more

than a second to execute.


you are complaining about more than a second with a terabyte of text?



 Has anybody implemented such a database using
multiple systems or some special add-on to TSearch2 to make things
faster?  I want to do something like partitioning the data into  
multiple

systems and merging the ranked results at some master node.  Is
something like this possible for PostgreSQL or must it be a software
solution?

Benjamin

---(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




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/ 
donate

PostgreSQL Replication: http://www.commandprompt.com/products/




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] multi terabyte fulltext searching

[Benjamin Arai]

Hi Oleg,

I am currently using GIST indexes because I receive about 10GB of new  
data a week (then again I am not deleting any information).  The do  
not expect to be able to stop receiving text for about 5 years, so  
the data is not going to become static any time soon.  The reason I  
am concerned with performance is that I am providing a search system  
for several newspapers since essentially the beginning of time.  Many  
bibliographer etc would like to use this utility but if each search  
takes too long I am not going to be able to support many concurrent  
users.


Benjamin

On Mar 21, 2007, at 8:42 AM, Oleg Bartunov wrote:


Benjamin,

as one of the author of tsearch2 I'd like to know more about your  
setup.
tsearch2 in 8.2 has GIN index support, which scales much better  
than old

GiST index.

Oleg

On Wed, 21 Mar 2007, Benjamin Arai wrote:


Hi,

I have been struggling with getting fulltext searching for very  
large databases.  I can fulltext index 10s if gigs without any  
problem but when I start geting to hundreds of gigs it becomes  
slow.  My current system is a quad core with 8GB of memory.  I  
have the resource to throw more hardware at it but realistically  
it is not cost effective to buy a system with 128GB of memory.  Is  
there any solutions that people have come up with for indexing  
very large text databases?


Essentially I have several terabytes of text that I need to  
index.  Each record is about 5 paragraphs of text.  I am currently  
using TSearch2 (stemming and etc) and getting sub-optimal  
results.  Queries take more than a second to execute.  Has anybody  
implemented such a database using multiple systems or some special  
add-on to TSearch2 to make things faster?  I want to do something  
like partitioning the data into multiple systems and merging the  
ranked results at some master node.  Is something like this  
possible for PostgreSQL or must it be a software solution?


Benjamin

---(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: oleg@sai.msu.su, 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] Anyone still using the sql_inheritance parameter?

[codeWarrior]

+1;

Tom:

I regularly use the inheritance features of postgreSQL -- Probably 25% of my 
schemas rely on it for the techiques I use such as: history tables, 
recursion tables [parent-child and trees], among others.

What is the potential impact for the "ONLY" qualifier ??? None I would 
expect, as the "ONLY" qualifier effectively sets SQL_INHERITANCE = off for 
that specific query -- 

What about "decorated" table names: i.e: "SELECT * FROM cities*" ??? Do we 
get to keep this feature ? [my understanding:  table_name* is the reverse 
alternative to "ONLY" in that it queries all tables in the inheritance tree]

My vote would be to have the global setting become settable in the runtime 
environment only by a superuser, or as specified in the global settings 
[postgresql.conf] --> assuming that the ONLY qualifier and decorated table 
names continue to work as they currently do...

The manual encourages the use of "ONLY" -- [see sql_inheritance -- section 
17.12.1].



"Tom Lane" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Is anybody still using the ability to set sql_inheritance to OFF?
> I'm considering removing the parameter in PG 8.3, so that the current
> default behavior (sql_inheritance = ON) would be the only behavior.
> sql_inheritance was created in 7.1 to allow existing applications to
> not be broken when we changed the default behavior, but I have not
> heard of anyone using it recently.
>
> The argument for removing it is basically that user-settable parameters
> that affect fundamental query semantics are dangerous.  As an example,
> setting sql_inheritance to OFF causes silent malfunctioning of
> partitioned tables that are built using the currently-recommended
> approach.  You could even argue that this is a security hole, because
> an unprivileged user could cause a security-definer function to fail
> to operate as intended --- okay, that's a bit of a stretch, but the
> scenario is not out of the question.
>
> We've recently been discussing the possibility that the search_path
> parameter could be used to force misbehavior of security-definer
> functions.  There seems to be consensus in favor of adding language
> features to let creators of functions nail down the search_path to be
> used by their functions (though there's not a specific proposal yet).
> I don't really want to go through similar pushups for sql_inheritance;
> it doesn't seem worth it.
>
> So: would anyone cry if sql_inheritance disappeared in 8.3?
>
> If there are a lot of complaints, a possible compromise is to keep the
> variable but make it SUSET, ie, only changeable by superusers.  This
> would still allow the setting to be turned off for use by legacy
> applications (probably by means of ALTER USER) while removing the
> objection that non-privileged users could break things.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>   http://archives.postgresql.org/
> 



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] select progressive total

[brian]

Merlin Moncure wrote:

On 3/21/07, brian <[EMAIL PROTECTED]> wrote:


 From the "I thought this would be trivially easy" dept:

I have a table holding member data for an organisation

CREAT table member (
id  SERIAL PRIMARY KEY,
applied date,
...

and i'd like to plot the growth in memberships. I'd prefer to hand off
the data already totaled, if possible.

'2006-02-01', 452
'2006-02-17', 453
...

It semed to me that a self join was the way to go, but that route
produced some flaky results. Here's my latest attempt:

SELECT m1.applied AS date_applied, count(m2.id) AS num_applications
FROM member m1, member m2 WHERE m1.applied <= m2.applied
GROUP BY m1.applied ORDER BY date_applied ASC;

The thing is, some applications fell on the same day, so i'm not even
sure that this approach would work. Can anyone see where i'm going wrong
here? And how can i handle applications that occurred on the same day?
Can this even be done in a SELECT, or should i create a function?



you can do this with a subquery:

select
 applied_date,
 count(*),
 (select count(*) from member m2 where m2.applied_date <
m1.applied_date) as total
 from member m1
 group by 1;



Almost. The total column is less than the correct amount by whatever 
count returns.



performance is going to suck for large tables because the subquery
will run once for every row returned by the group.



Yeah, i realise. In the end, i think i'll do the totalling outside of 
Postgres. When plotting the data, i'll be sending it as an assoc. array, 
like: '2006-05-27'=>3049 so i need to do some post-processing in any case.


b

---(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] multi terabyte fulltext searching

[Joshua D. Drake]

Benjamin Arai wrote:
> Hi,
> 
> I have been struggling with getting fulltext searching for very large
> databases.  I can fulltext index 10s if gigs without any problem but
> when I start geting to hundreds of gigs it becomes slow.  My current
> system is a quad core with 8GB of memory.  I have the resource to throw
> more hardware at it but realistically it is not cost effective to buy a
> system with 128GB of memory.  Is there any solutions that people have
> come up with for indexing very large text databases?

GIST indexes are very large.

> Essentially I have several terabytes of text that I need to index.  Each
> record is about 5 paragraphs of text.  I am currently using TSearch2
> (stemming and etc) and getting sub-optimal results.  Queries take more
> than a second to execute.

you are complaining about more than a second with a terabyte of text?


>  Has anybody implemented such a database using
> multiple systems or some special add-on to TSearch2 to make things
> faster?  I want to do something like partitioning the data into multiple
> systems and merging the ranked results at some master node.  Is
> something like this possible for PostgreSQL or must it be a software
> solution?
> 
> Benjamin
> 
> ---(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
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] to_tsvector in 8.2.3

[Oleg Bartunov]

On Wed, 21 Mar 2007, richardcraig wrote:



As an update, I've just copied the tsvector.dll file from an 8.2.1 computer
to the 8.2.3 computer and it's working OK now. I suspect a change in the dll
is causing the problem.


Interesting.



Richard



richardcraig wrote:


Oleg

Thanks for the quick response.

On 8.2.1 I get the same result as yourself, however on 8.2.3 I get
(2,"test text")

Configuration?
Both databases are UTF8 encoded with language set to C
Standard windows installation except for tsearch2
I also have another machine with SQL_ASCII encoding on 8.2.1 and that's OK

As far as I can tell they are identical except for the postgresql
versions.

It's been a long time since I did much with tsearch2 so I'm suspecting a
config problem with it that's I've long forgotten about.

Richard


Oleg Bartunov wrote:


What parse returns ? 8.1.5 and 8.3 return

www=# select parse('test text');
   parse
--
  (1,test)
  (12," ")
  (1,text)
(3 rows)

Also, what is your configuration ?


On Wed, 21 Mar 2007, richardcraig wrote:



It may solve my query if anybody can tell me if anything has changes in
tsearch2 recently? otherwise...

I have two installations of Postgres on Windows machines, one is 8.2.1
and
the other is 8.2.3

Both installed the same way, selecting tsearch2 during the installation
and
restoring the same backup database file.

In 8.2.1
 select to_tsvector('test text')
returns
 'test':1 'text':2
as expected

In 8.2.3 the same code returns
 'test text':1

Can anybody help?



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(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: oleg@sai.msu.su, 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] Anyone still using the sql_inheritance parameter?

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 03/21/07 09:49, Tom Lane wrote:
> Is anybody still using the ability to set sql_inheritance to OFF?
> I'm considering removing the parameter in PG 8.3, so that the current
> default behavior (sql_inheritance = ON) would be the only behavior.
> sql_inheritance was created in 7.1 to allow existing applications to
> not be broken when we changed the default behavior, but I have not
> heard of anyone using it recently.
> 
[snip]
> 
> So: would anyone cry if sql_inheritance disappeared in 8.3?
> 
> If there are a lot of complaints, a possible compromise is to keep the
> variable but make it SUSET, ie, only changeable by superusers.  This
> would still allow the setting to be turned off for use by legacy
> applications (probably by means of ALTER USER) while removing the
> objection that non-privileged users could break things.

Shouldn't features be deprecated for a version before removal?


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGAVKxS9HxQb37XmcRAmTbAKDfcRX1zP5NWqVjiiAb/p5KL8vdPACePdip
HCIWBGMMbZVkUuO92h+fOos=
=sVjt
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Postgres Hot Standby. How or when does the recovery db move recovery.conf to recovery.done?

[Dhaval Shah]

I looked at the pg_standby utility and would have liked to use it,
however there are some customer driven extraneous issues in using
that.

What I am looking at it is this:

1. I can detect that the primary has gone down and return a non-zero
for the standby to recover.

2. Since I can detect that I am out of standby mode, I can shutdown
the postgres, move the recovery.conf file to recovery.done manually.
And then restart the db.

Even if I do step 2, I still get the following in the server log:

=
Main: Triggering Recovery!!!  <- my script is returning a non-zero code here ...

PANIC:  could not open file "pg_xlog/0001001B" (log
file 0, segment 27): No such file or directory
LOG:  startup process (PID 32167) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2007-03-20 13:04:28 PDT
HINT:  If this has occurred more than once some data may be corrupted
and you may need to choose an earlier recovery target.
LOG:  could not open file "pg_xlog/00010006" (log file
0, segment 6): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file "pg_xlog/00010005" (log file
0, segment 5): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 4676) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time
2007-03-20 13:04:28 PDT


The question I have is how do I get out of the above mode and ensure
that the db is up and ready? What I need to clear? A previous cache or
something? Am I missing something here? I went to the docs and it says
the following:

"Start the postmaster. The postmaster will go into recovery mode and
proceed to read through the archived WAL files it needs. Upon
completion of the recovery process, the postmaster will rename
recovery.conf to recovery.done (to prevent accidentally re-entering
recovery mode in case of a crash later) and then commence normal
database operations."

And I do not see the recovery.conf go to recovery.done automatically.

Dhaval


On 3/21/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:

On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote:
> Resending.
>
> I have a "hot" standby. Now, if the primary fails
> how do I tell the secondary that come out of recovery mode and move
> the recovery.conf to recovery.done and start the db. I mean, what
> error code shall I return?

did you look at pg_standby utility? it has kill file mechanism that
automates this for you.

merlin




--
Dhaval Shah

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] multi terabyte fulltext searching

[Oleg Bartunov]

Benjamin,

as one of the author of tsearch2 I'd like to know more about your setup.
tsearch2 in 8.2 has GIN index support, which scales much better than old
GiST index.

Oleg

On Wed, 21 Mar 2007, Benjamin Arai wrote:


Hi,

I have been struggling with getting fulltext searching for very large 
databases.  I can fulltext index 10s if gigs without any problem but when I 
start geting to hundreds of gigs it becomes slow.  My current system is a 
quad core with 8GB of memory.  I have the resource to throw more hardware at 
it but realistically it is not cost effective to buy a system with 128GB of 
memory.  Is there any solutions that people have come up with for indexing 
very large text databases?


Essentially I have several terabytes of text that I need to index.  Each 
record is about 5 paragraphs of text.  I am currently using TSearch2 
(stemming and etc) and getting sub-optimal results.  Queries take more than a 
second to execute.  Has anybody implemented such a database using multiple 
systems or some special add-on to TSearch2 to make things faster?  I want to 
do something like partitioning the data into multiple systems and merging the 
ranked results at some master node.  Is something like this possible for 
PostgreSQL or must it be a software solution?


Benjamin

---(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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(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: [HACKERS] [GENERAL] Remove add_missing_from_clause?

[Joshua D. Drake]

Tom Lane wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>> Following Tom's lead... should we also remove this? We have had it for a
>> couple of releases, and really all we are doing is protecting the use of
>> bad queries.
> 
> No, AFAICT it's still in active use --- you need not look back far in
> the mailing lists to find questions answered by "see add_missing_from".
> I haven't seen a question about sql_inheritance in a long while.

Oh, I know it is still in use, that is my complaint :). Perhaps stating
that this will be the last release of the feature?

Sincerely,

Joshua D. Drake


> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] multi terabyte fulltext searching

[Benjamin Arai]

Hi,

I have been struggling with getting fulltext searching for very large  
databases.  I can fulltext index 10s if gigs without any problem but  
when I start geting to hundreds of gigs it becomes slow.  My current  
system is a quad core with 8GB of memory.  I have the resource to  
throw more hardware at it but realistically it is not cost effective  
to buy a system with 128GB of memory.  Is there any solutions that  
people have come up with for indexing very large text databases?


Essentially I have several terabytes of text that I need to index.   
Each record is about 5 paragraphs of text.  I am currently using  
TSearch2 (stemming and etc) and getting sub-optimal results.  Queries  
take more than a second to execute.  Has anybody implemented such a  
database using multiple systems or some special add-on to TSearch2 to  
make things faster?  I want to do something like partitioning the  
data into multiple systems and merging the ranked results at some  
master node.  Is something like this possible for PostgreSQL or must  
it be a software solution?


Benjamin

---(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] to_tsvector in 8.2.3

[Teodor Sigaev]

8.2 has fully rewritten text parser based on POSIX is* functions.

Thomas Pundt wrote:

On Wednesday 21 March 2007 14:25, Teodor Sigaev wrote:
| I can't reproduce your problem, but I have not Windows box, can anybody
| reproduce that?

just a guess in the wild; I once had a similar phenomen and tracked it down
to a "non breaking space character" (0xA0). Since then I'm patching the
tsearch2 lexer:

--- postgresql-8.1.5/contrib/tsearch2/wordparser/parser.l
+++ postgresql-8.1.4/contrib/tsearch2/wordparser/parser.l
@@ -78,8 +78,8 @@
 /* cyrillic koi8 char */
 CYRALNUM   [0-9\200-\377]
 CYRALPHA   [\200-\377]
-ALPHA  [a-zA-Z\200-\377]
-ALNUM  [0-9a-zA-Z\200-\377]
+ALPHA  [a-zA-Z\200-\237\241-\377]
+ALNUM  [0-9a-zA-Z\200-\237\241-\377]
 
 
 HOSTNAME   ([-_[:alnum:]]+\.)+[[:alpha:]]+

@@ -307,7 +307,7 @@
return UWORD; 
 }
 
-[ \r\n\t]+ {

+[ \240\r\n\t]+ {
token = tsearch2_yytext;
tokenlen = tsearch2_yyleng;
return SPACE;


Ciao,
Thomas



--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Remove add_missing_from_clause?

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Following Tom's lead... should we also remove this? We have had it for a
> couple of releases, and really all we are doing is protecting the use of
> bad queries.

No, AFAICT it's still in active use --- you need not look back far in
the mailing lists to find questions answered by "see add_missing_from".
I haven't seen a question about sql_inheritance in a long while.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

Tom Lane wrote:
> Naz Gassiep <[EMAIL PROTECTED]> writes:
>> Joshua D. Drake wrote:
>>> Example discussion with customer:
>> ...
>> Finally, in the absence of security concerns or performance issues (and 
>> I mean the "we can't afford to buy better hardware" type edge of the 
>> envelope type issues) there is zero *need* to upgrade.
> 
> This line of argument ignores the fact that newer versions often contain
> fixes for data-loss-grade bugs.  Now admittedly that is usually an
> argument for updating to x.y.z+1 rather than x.y+1, but I think it
> destroys any reasoning on the basis of "if it ain't broke".

I think that we call pretty much assume that this whole thread is based
around the theory that we are all running the latest stable dot release
of whatever version. Which in fact does, mean "if it ain't broke, don't
fix it."


Sincerely,

Joshua D. Drake


> 
>   regards, tom lane
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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

[GENERAL] Remove add_missing_from_clause?

[Joshua D. Drake]

Hello,

Following Tom's lead... should we also remove this? We have had it for a
couple of releases, and really all we are doing is protecting the use of
bad queries.

Can we remove it?

Joshua D. Drake

-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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] Anyone still using the sql_inheritance parameter?

[Joshua D. Drake]

> So: would anyone cry if sql_inheritance disappeared in 8.3?

+1

Joshua D. Drake



-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(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

[GENERAL] Anyone still using the sql_inheritance parameter?

[Tom Lane]

Is anybody still using the ability to set sql_inheritance to OFF?
I'm considering removing the parameter in PG 8.3, so that the current
default behavior (sql_inheritance = ON) would be the only behavior.
sql_inheritance was created in 7.1 to allow existing applications to
not be broken when we changed the default behavior, but I have not
heard of anyone using it recently.

The argument for removing it is basically that user-settable parameters
that affect fundamental query semantics are dangerous.  As an example,
setting sql_inheritance to OFF causes silent malfunctioning of
partitioned tables that are built using the currently-recommended
approach.  You could even argue that this is a security hole, because
an unprivileged user could cause a security-definer function to fail
to operate as intended --- okay, that's a bit of a stretch, but the
scenario is not out of the question.

We've recently been discussing the possibility that the search_path
parameter could be used to force misbehavior of security-definer
functions.  There seems to be consensus in favor of adding language
features to let creators of functions nail down the search_path to be
used by their functions (though there's not a specific proposal yet).
I don't really want to go through similar pushups for sql_inheritance;
it doesn't seem worth it.

So: would anyone cry if sql_inheritance disappeared in 8.3?

If there are a lot of complaints, a possible compromise is to keep the
variable but make it SUSET, ie, only changeable by superusers.  This
would still allow the setting to be turned off for use by legacy
applications (probably by means of ALTER USER) while removing the
objection that non-privileged users could break things.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] CHAR data type

[Michael Fuhr]

On Wed, Mar 21, 2007 at 11:29:54AM -0300, Leticia wrote:
> If I use "char(8000)" instead of "varchar(8000)" why there is no padding and
> these three tuples are inside the same page?

http://www.postgresql.org/docs/8.2/interactive/datatype-character.html

"The storage requirement for data of these types is 4 bytes plus
the actual string, and in case of character plus the padding. Long
strings are compressed by the system automatically, so the physical
requirement on disk may be less. Long values are also stored in
background tables so they do not interfere with rapid access to the
shorter column values."

See also the TOAST documentation:

http://www.postgresql.org/docs/8.2/interactive/storage-toast.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Joshua D. Drake]

> All that being said, the older the version you are running, the higher
> the weight that should be attributed to the "upgrading is a good idea
> just coz" argument. After a point, upgrading is just a good idea "just
> coz". I wouldn't recommend anyone continue to run 7.2.x merely because
> it was working for them.

Certainly, but 7.2 is considered EOL by the community and that is where
I would draw the line. When we fully EOL 7.3, we will make all customers
upgrade from that as well.

> 
> Just my 2c (adjusted for inflation).
> 

1.7mil?

Joshua D. Drake


> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] CHAR data type

[Leticia]

I am using PostgreSQL 8.2.3. with the default page size of 8K

 

I created the following table:

 

CREATE TABLE mystate( ID integer, name char(8000) );

 

I inserted three tuples:

 

INSERT INTO mystate VALUES (3, 'a3');

INSERT INTO mystate VALUES (5, 'a5');

INSERT INTO mystate VALUES (6, 'a6');

 

And then I ran the following query:

 

vacuum analyze mystate;   

select relpages, reltuples from pg_class where relname='mystate';

 

 

but the result was:

1 3

 

 

If I use "char(8000)" instead of "varchar(8000)" why there is no padding and
these three tuples are inside the same page?

 

Thanks in advance,

Leticia

 

 

___
postgis-users mailing list
[EMAIL PROTECTED]
http://postgis.refractions.net/mailman/listinfo/postgis-users

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Brandon Aiken]

Not if you're not affected by the bugs.  Software *always* has bugs.
And new code in your environment is *untested* code in your environment.

If I am not affected by bugs, if I'm under a support contract to correct
any bugs that I *am* affected by (as was the case in Josh's original
argument with RHEL), and no new features are required, then all
upgrading will do is take me from a state of known bugs that don't
affect my systems to unknown bugs or undocumented/unintended changes
that *might* affect my systems.

The PostgreSQL community supports latest release.  Here, "upgrade to
most recent" exactly means "upgrade to the version we know has all the
fixes we've already done".  We ask people to upgrade here so we don't
have to reinvent the wheel just because someone wants to use 7.4.1.
Resources are tight enough just supporting the most recent codebase.
Including every codebase back to the beginning of time would require an
enormous number of people.

Support contracts with, for example, RHEL, don't necessarily work that
way.  They typically say "use our most recent packages; anything else is
not covered and you're on your own".  Because support contracts say
this, they have to maintain the codebase themselves to a fair extent.
Granted, they can just take the changes from -- in this case --
PostgreSQL's source code, but they are the people responsible for the
security of the code base and compatibility of the code base.  That's
*exactly* what you buy when you buy the support contract.

Look at it this way:
The benefits to any upgrade are "bug fix" and "new feature".
The caveats to any upgrade are "new bug" and "feature change".  (PHP and
MySQL are notorious for the latter.)

If "bug fix" is 100% handled by support contract, and "new feature" is
100% not useful, what is my impetus?  

For a direct example, why should a business upgrade their desktops from
Windows XP to Windows Vista before 2011 if *none* of the new features
are needed?

--
Brandon Aiken
CS/IT Systems Engineer

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Wednesday, March 21, 2007 9:29 AM
To: Naz Gassiep
Cc: Joshua D. Drake; Erik Jones; CAJ CAJ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Lifecycle of PostgreSQL releases 

Naz Gassiep <[EMAIL PROTECTED]> writes:
> Joshua D. Drake wrote:
>> Example discussion with customer:
> ...
> Finally, in the absence of security concerns or performance issues
(and 
> I mean the "we can't afford to buy better hardware" type edge of the 
> envelope type issues) there is zero *need* to upgrade.

This line of argument ignores the fact that newer versions often contain
fixes for data-loss-grade bugs.  Now admittedly that is usually an
argument for updating to x.y.z+1 rather than x.y+1, but I think it
destroys any reasoning on the basis of "if it ain't broke".

regards, tom lane

---(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

** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and 
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged, 
confidential or proprietary information. If you are not the intended 
recipient(s), or the employee or agent responsible for delivery of 
this message to the intended recipient(s), you are hereby notified 
that any dissemination, distribution or copying of this e-mail 
message is strictly prohibited. If you have received this message in 
error, please immediately notify the sender and delete this e-mail 
message from your computer.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Postgres Hot Standby. How or when does the recovery db move recovery.conf to recovery.done?

[Merlin Moncure]

On 3/21/07, Dhaval Shah <[EMAIL PROTECTED]> wrote:

Resending.

I have a "hot" standby. Now, if the primary fails
how do I tell the secondary that come out of recovery mode and move
the recovery.conf to recovery.done and start the db. I mean, what
error code shall I return?


did you look at pg_standby utility? it has kill file mechanism that
automates this for you.

merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] to_tsvector in 8.2.3

[Thomas Pundt]

On Wednesday 21 March 2007 14:25, Teodor Sigaev wrote:
| I can't reproduce your problem, but I have not Windows box, can anybody
| reproduce that?

just a guess in the wild; I once had a similar phenomen and tracked it down
to a "non breaking space character" (0xA0). Since then I'm patching the
tsearch2 lexer:

--- postgresql-8.1.5/contrib/tsearch2/wordparser/parser.l
+++ postgresql-8.1.4/contrib/tsearch2/wordparser/parser.l
@@ -78,8 +78,8 @@
 /* cyrillic koi8 char */
 CYRALNUM   [0-9\200-\377]
 CYRALPHA   [\200-\377]
-ALPHA  [a-zA-Z\200-\377]
-ALNUM  [0-9a-zA-Z\200-\377]
+ALPHA  [a-zA-Z\200-\237\241-\377]
+ALNUM  [0-9a-zA-Z\200-\237\241-\377]
 
 
 HOSTNAME   ([-_[:alnum:]]+\.)+[[:alpha:]]+
@@ -307,7 +307,7 @@
return UWORD; 
 }
 
-[ \r\n\t]+ {
+[ \240\r\n\t]+ {
token = tsearch2_yytext;
tokenlen = tsearch2_yyleng;
return SPACE;


Ciao,
Thomas

-- 
Thomas Pundt <[EMAIL PROTECTED]>  http://rp-online.de/ 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] phpPgAdmin - prior version available?

[Robert Treat]

On Sunday 18 March 2007 12:41, Bob Hartung wrote:
> Hi all,
>I have been struggling with phpPgAdmin 4.1 - login failures.  There
> does not yet seem to be a fix.  Where can I find a prior version for FC6
> - rpm, tar.gz etc.
>

Can you be a bit more specific on the problem you're seeing? 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Lifecycle of PostgreSQL releases

[Tom Lane]

Naz Gassiep <[EMAIL PROTECTED]> writes:
> Joshua D. Drake wrote:
>> Example discussion with customer:
> ...
> Finally, in the absence of security concerns or performance issues (and 
> I mean the "we can't afford to buy better hardware" type edge of the 
> envelope type issues) there is zero *need* to upgrade.

This line of argument ignores the fact that newer versions often contain
fixes for data-loss-grade bugs.  Now admittedly that is usually an
argument for updating to x.y.z+1 rather than x.y+1, but I think it
destroys any reasoning on the basis of "if it ain't broke".

regards, tom lane

---(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] to_tsvector in 8.2.3

[Teodor Sigaev]

I can't reproduce your problem, but I have not Windows box, can anybody 
reproduce that?



contrib_regression=# select version();
 version 

 PostgreSQL 8.2.3 on i386-unknown-freebsd6.2, compiled by GCC gcc (GCC) 3.4.6 
[FreeBSD] 20060305

(1 row)
contrib_regression=#  show server_encoding ;
 server_encoding
-
 UTF8
(1 row)

contrib_regression=# show lc_collate;
 lc_collate

 C
(1 row)

contrib_regression=# show lc_ctype;
 lc_ctype
--
 C
(1 row)

contrib_regression=# select to_tsvector('test text');
to_tsvector
---
 'test':1 'text':2
(1 row)

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] to_tsvector in 8.2.3

[richardcraig]

As an update, I've just copied the tsvector.dll file from an 8.2.1 computer
to the 8.2.3 computer and it's working OK now. I suspect a change in the dll
is causing the problem.

Richard



richardcraig wrote:
> 
> Oleg
> 
> Thanks for the quick response.
> 
> On 8.2.1 I get the same result as yourself, however on 8.2.3 I get 
> (2,"test text")
> 
> Configuration?
> Both databases are UTF8 encoded with language set to C
> Standard windows installation except for tsearch2
> I also have another machine with SQL_ASCII encoding on 8.2.1 and that's OK
> 
> As far as I can tell they are identical except for the postgresql
> versions.
> 
> It's been a long time since I did much with tsearch2 so I'm suspecting a
> config problem with it that's I've long forgotten about.
> 
> Richard
> 
> 
> Oleg Bartunov wrote:
>> 
>> What parse returns ? 8.1.5 and 8.3 return
>> 
>> www=# select parse('test text');
>>parse
>> --
>>   (1,test)
>>   (12," ")
>>   (1,text)
>> (3 rows)
>> 
>> Also, what is your configuration ?
>> 
>> 
>> On Wed, 21 Mar 2007, richardcraig wrote:
>> 
>>>
>>> It may solve my query if anybody can tell me if anything has changes in
>>> tsearch2 recently? otherwise...
>>>
>>> I have two installations of Postgres on Windows machines, one is 8.2.1
>>> and
>>> the other is 8.2.3
>>>
>>> Both installed the same way, selecting tsearch2 during the installation
>>> and
>>> restoring the same backup database file.
>>>
>>> In 8.2.1
>>>  select to_tsvector('test text')
>>> returns
>>>  'test':1 'text':2
>>> as expected
>>>
>>> In 8.2.3 the same code returns
>>>  'test text':1
>>>
>>> Can anybody help?
>>>
>> 
>>  Regards,
>>  Oleg
>> _
>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>> Sternberg Astronomical Institute, Moscow University, Russia
>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> phone: +007(495)939-16-83, +007(495)939-23-83
>> 
>> ---(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
>> 
>> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/to_tsvector-in-8.2.3-tf3440132.html#a9592812
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] to_tsvector in 8.2.3

[richardcraig]

Oleg

Thanks for the quick response.

On 8.2.1 I get the same result as yourself, however on 8.2.3 I get 
(2,"test text")

Configuration?
Both databases are UTF8 encoded with language set to C
Standard windows installation except for tsearch2
I also have another machine with SQL_ASCII encoding on 8.2.1 and that's OK

As far as I can tell they are identical except for the postgresql versions.

It's been a long time since I did much with tsearch2 so I'm suspecting a
config problem with it that's I've long forgotten about.

Richard


Oleg Bartunov wrote:
> 
> What parse returns ? 8.1.5 and 8.3 return
> 
> www=# select parse('test text');
>parse
> --
>   (1,test)
>   (12," ")
>   (1,text)
> (3 rows)
> 
> Also, what is your configuration ?
> 
> 
> On Wed, 21 Mar 2007, richardcraig wrote:
> 
>>
>> It may solve my query if anybody can tell me if anything has changes in
>> tsearch2 recently? otherwise...
>>
>> I have two installations of Postgres on Windows machines, one is 8.2.1
>> and
>> the other is 8.2.3
>>
>> Both installed the same way, selecting tsearch2 during the installation
>> and
>> restoring the same backup database file.
>>
>> In 8.2.1
>>  select to_tsvector('test text')
>> returns
>>  'test':1 'text':2
>> as expected
>>
>> In 8.2.3 the same code returns
>>  'test text':1
>>
>> Can anybody help?
>>
> 
>   Regards,
>   Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
> 
> ---(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
> 
> 

-- 
View this message in context: 
http://www.nabble.com/to_tsvector-in-8.2.3-tf3440132.html#a9592631
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] to_tsvector in 8.2.3

[Oleg Bartunov]

What parse returns ? 8.1.5 and 8.3 return

www=# select parse('test text');
  parse
--
 (1,test)
 (12," ")
 (1,text)
(3 rows)

Also, what is your configuration ?


On Wed, 21 Mar 2007, richardcraig wrote:



It may solve my query if anybody can tell me if anything has changes in
tsearch2 recently? otherwise...

I have two installations of Postgres on Windows machines, one is 8.2.1 and
the other is 8.2.3

Both installed the same way, selecting tsearch2 during the installation and
restoring the same backup database file.

In 8.2.1
 select to_tsvector('test text')
returns
 'test':1 'text':2
as expected

In 8.2.3 the same code returns
 'test text':1

Can anybody help?



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(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] to_tsvector in 8.2.3

[richardcraig]

It may solve my query if anybody can tell me if anything has changes in
tsearch2 recently? otherwise...

I have two installations of Postgres on Windows machines, one is 8.2.1 and
the other is 8.2.3

Both installed the same way, selecting tsearch2 during the installation and
restoring the same backup database file.

In 8.2.1 
  select to_tsvector('test text') 
returns
  'test':1 'text':2
as expected

In 8.2.3 the same code returns
  'test text':1

Can anybody help?
-- 
View this message in context: 
http://www.nabble.com/to_tsvector-in-8.2.3-tf3440132.html#a9592230
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Bulk Data Entry

[Richard Huxton]

Richard Huxton wrote:

Check the psql man-page for "ON_ERROR_STOP":
psql ... -v 'ON_ERROR_STOP=' ...


Sorry - typo
  psql ... -v 'ON_ERROR_STOP=1' ...

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Bulk Data Entry

[Richard Huxton]

Naz Gassiep wrote:

psql blogbogdev -f ./blogbog_tables.sql > ./blogbog_tables_inserted.log
psql blogbogdev -f ./blogbog_data.sql > ./blogbog_data_inserted.log
psql blogbogdev -f ./blogbog_constraints.sql > 
./blogbog_constraints_applied.log


I really would prefer psql to halt on error instead of just continuing 
to plow right ahead, but IIRC there was a discussion about this and it 
was decided that continuing was the best behavior.


Check the psql man-page for "ON_ERROR_STOP":
psql ... -v 'ON_ERROR_STOP=' ...

I have grepped the .log files that the script outputs for "ERROR" but 
there is none. How would one go about finding where the error in an SQL 
script is?


You're not redirecting STDERR, just STDOUT

psql  >insert.log 2>insert.err

--
  Richard Huxton
  Archonet Ltd

---(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] Lifecycle of PostgreSQL releases

[Naz Gassiep]

Joshua D. Drake wrote:

Example discussion with customer:

Customer: CMD, should we update to 8.2.3
CMD: Is there something in 8.2.3 that will benefit you?
Customer: We don't know
CMD: Are you having problems with 8.1? (We try to push all customers to
at least 8.1)
Customer: No, it is just that 8.2 is the current release
CMD: True, but 8.3 is due out in the summer and 8.3 is a standard cycle
release
Customer: Oh... o.k. let's wait.
CMD: I think that is probably prudent.
  
That's how it is with me. I upgraded to 8.1 from 7.4 because there was 
nothing in 8.0 that I *needed* and performance was already more than 
sufficient on my ridiculous overkill hardware. I recently upgraded from 
8.1.x to 8.2.3 only because of the DST updates in Western Australia. I 
would not have otherwise. If it ain't broke, don't fix it.


Furthermore, upgrading is inherently risky. There is always the chance 
of human error induced downtime, and so doing it "just coz" is not a 
prudent policy.


Finally, in the absence of security concerns or performance issues (and 
I mean the "we can't afford to buy better hardware" type edge of the 
envelope type issues) there is zero *need* to upgrade. Sure, it may be 
better to use a new and shiny version, however I always favor a 
realistic and honest assessment of *needs* over *perceived needs*.


All that being said, the older the version you are running, the higher 
the weight that should be attributed to the "upgrading is a good idea 
just coz" argument. After a point, upgrading is just a good idea "just 
coz". I wouldn't recommend anyone continue to run 7.2.x merely because 
it was working for them.


Just my 2c (adjusted for inflation).


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Approximate join on timestamps

[Alban Hertroys]

Phil Endecott wrote:
> Dear Experts,
> 
> I have two tables containing chronological data, and I want to join them
> using the timestamps.  The challenge is that the timestamps only match
> approximately.
> 
> My first attempt was something like
> 
>   t1 join t2 on (abs(t1.t-t2.t)<'1 min'::interval)
> 
> Of course there is no "abs" for intervals, and I couldn't think of
> anything better than this
> 
>   t1 join t2 on (t1.t-t2.t<'1 min'::interval and t2.t-t1.t<'1
> min'::interval)

What about:
 t1 join t2
 on ((t1.t - interval '30s', t1.t + interval '30s') overlaps (t2.t -
interval '30s', t2.t + interval '30s')) 

No need for abs(interval) or repeating conditions that way. My first
attempt was using 'between' instead of 'overlaps', but I don't think
that'll work correctly.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Bulk Data Entry

[Naz Gassiep]

This problem is to do with bulk loading of data. I use the following 
scripts to take data from a live DB and put it into a testing DB with 
the current version of the schema:


# SCRIPT 1
pg_dump blogbog -a -D -f blogbog_data.sql
dropdb blogbogtemp
createdb blogbogtemp
psql blogbogtemp -f /www/htdocs/mrnaz.com/sql_tables.sql
pg_dump blogbogtemp -D -f blogbog_tables.sql
cp blogbog_tables.sql blogbog_constraints.sql


I edit the blogbog_tables.sql file to remove the constraints and the 
blogbog_constraints.sql file to remove the tables. I then run the 
following script:



# SCRIPT 2
dropdb blogbogdev
createdb blogbogdev
psql blogbogdev -f ./blogbog_tables.sql > ./blogbog_tables_inserted.log
psql blogbogdev -f ./blogbog_data.sql > ./blogbog_data_inserted.log
psql blogbogdev -f ./blogbog_constraints.sql > 
./blogbog_constraints_applied.log



Somewhere in the insertion of the data (4th line of script 2) there is a 
failure, as no data appears in the blogbogdev database. This is likely 
due to a column in the live data somewhere that has been deprecated from 
the schema causing an insert failure on a table causing failure on all 
tables referring to it and so on cascading down the reference paths.


I really would prefer psql to halt on error instead of just continuing 
to plow right ahead, but IIRC there was a discussion about this and it 
was decided that continuing was the best behavior.


I have grepped the .log files that the script outputs for "ERROR" but 
there is none. How would one go about finding where the error in an SQL 
script is?

Bulk

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq