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
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
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
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
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
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
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
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
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,
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)
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
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
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
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
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,
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
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 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
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
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
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
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
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
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
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
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
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
+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
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
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
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
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.
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:
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
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
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
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,
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
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.
What is inheritance+CE?
Benjamin
On Mar 21, 2007, at 9:10 AM, Oleg Bartunov wrote:
inheritance+CE
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
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
-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?
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
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
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
[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...
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
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
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?
--
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
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
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)
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
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
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
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
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
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
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:
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
[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:
-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...
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
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
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
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
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
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
83 matches
Mail list logo
