On Thu, 2007-04-26 at 00:13 +0200, Listmail wrote:
By the way, about indexes :
When you have a small table (say, for a website, maybe a few
tens of
megabytes max...) reindexing it takes just a few seconds, maybe
10-20
seconds.
It could be interesting, performance-wise,
Richard Huxton wrote:
Did you try pg_last_error()?
pg_last_error() does not seem to work. It requires connection as
parameter, so if pg_connect() fails - it has nothing to operate on.
Or am I missing something?
Are you logging connection attempts/failures? Details in the manuals.
On Wed, 2007-04-25 at 17:09 -0700, [EMAIL PROTECTED] wrote:
It is mentioned in postgresql-8.2.3\src\include\catalog\pg_statistic.h
file that the values between 100-199 are reserved for assignment by the
PostGIS project. Is PostgreSQL reserving these values? Do I did to
reserve values like
Owen Hartnett wrote:
Hi:
I'm a new user of Postgresql (8.2.3), and I'm very happy with both the
performance and operation of the system. My compliments to you the many
authors who keep this database running and useful.
My question is:
I want to freeze a snapshot of the database
Jonathan Vanasco wrote:
On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote:
Owen Hartnett wrote:
I want to freeze a snapshot of the database every year (think of
end of year tax records). However, I want this frozen version (and
all the previous frozen versions) available to the database
[EMAIL PROTECTED] wrote:
Richard Huxton wrote:
Did you try pg_last_error()?
pg_last_error() does not seem to work. It requires connection as
parameter, so if pg_connect() fails - it has nothing to operate on.
Or am I missing something?
No, I was. I've gone back and re-read your original
Richard Huxton wrote:
Try some code like this:
OK I'll try it now and write back.
Thanks!
Iv
---(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
Hello,
I'm searching for instructions on installing PostgreSQL 8.1.4 on SLES 9 SP2.
Can someone please point me to a web-site / document that has the proper set
of instructions?
Cheers
_
Tried the new MSN Messenger? ItÂ’s cool!
Hello!
Do you know which could be the reasons that could conduce an application to
not release the shared buffers, even after the application was shut down?
I noticed that only if a pg_ctl restart command is issued some of the
buffers are set free.
Thank you very much
With best regards,
Sorin
Dear,
We are facing performance tuning problem while using PostgreSQL Database
over the network on a linux OS.
Our Database consists of more than 500 tables with an average of 10K
records per table with an average of 20 users accessing the database
simultaneously over the network. Each table
Please try to post to one list at a time.
I've replied to this on the -performance list.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
For example:
select to_tsvector('cat,dog apple/orange');
to_tsvector
--
'cat':1 'dog':2 'apple/orange':3
(1 row)
Is there a setting that allows me to specify that strings containing
the '/' should be parsed into separate words? As is, I can't find
In response to Sorin N. Ciolofan [EMAIL PROTECTED]:
Hello!
Do you know which could be the reasons that could conduce an application to
not release the shared buffers, even after the application was shut down?
I noticed that only if a pg_ctl restart command is issued some of the
buffers
Anton,
Wrong mailing list. You should send this type of query to
pgsql-general@postgresql.org in the future. The documentation is confusing,
though. Try This:
CREATE OR REPLACE FUNCTION database_correction()
RETURNS double precision AS
$BODY$
DECLARE
mycursor CURSOR FOR select
I don't know the algorithm on which Postgre uses the shared buffers but I'd
like to find the principles behind it. Let's assume the following scenario:
I've set shared_buffers=3000
At the starting of Postgres there are 115 buffers used by database A
After the execution of some processing caused
Is there any function to count the number of query run
in one sentence?
i.e.
select * from mytable;
return 1
select * from mytable;select * from mytable2;
return 2
-BEGIN GEEK CODE BLOCK-
Version: 3.12
GSC d- s:++ a- C++ UL/B+++$ !P L+++
E--- W+ N o-- K? w++ O? M- V- PS PE++(-) Y+
In response to Sorin N. Ciolofan [EMAIL PROTECTED]:
I don't know the algorithm on which Postgre uses the shared buffers but I'd
like to find the principles behind it. Let's assume the following scenario:
I've set shared_buffers=3000
At the starting of Postgres there are 115 buffers used by
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 04/25/07 21:52, Bill Moran wrote:
[snip]
If you switch to FreeBSD, you can easily have this done automatically
with existing tools.
...
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system
At 9:23 AM +0100 4/26/07, Richard Huxton wrote:
Jonathan Vanasco wrote:
On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote:
Owen Hartnett wrote:
I want to freeze a snapshot of the database every year (think
of end of year tax records). However, I want this frozen version
(and all the
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.
J
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
After a reboot (and usually after an OS patch) on our HP-UX 11.23
64-bit Itanium DB servers, our libpq/DBD::Pg libraries cease to
work. Instead, they give the standard message you get when the
DB cluster is not running. But we *know* it is running and all
access paths are working. We have
On Thursday 26 April 2007 8:50 am, Ed L. wrote:
After a reboot (and usually after an OS patch) on our HP-UX
11.23 64-bit Itanium DB servers, our libpq/DBD::Pg libraries
cease to work. Instead, they give the standard message you
get when the DB cluster is not running. But we *know* it is
On Thu, 26 Apr 2007, John DeSoi wrote:
For example:
select to_tsvector('cat,dog apple/orange');
to_tsvector
--
'cat':1 'dog':2 'apple/orange':3
(1 row)
Is there a setting that allows me to specify that strings containing the '/'
should be parsed
On 4/26/2007, Chris [EMAIL PROTECTED] wrote:
tom wrote:
In pgsql I have to modify this a bit with 'cast (s_msgs as double
precision)' or 'cast(s_msgs as real)' in order to get floating point math.
( cast(s_msgs as double precision)/S_msgs) and so on...
Question: Is there a better way to
Ed L. [EMAIL PROTECTED] writes:
After a reboot (and usually after an OS patch) on our HP-UX 11.23
64-bit Itanium DB servers, our libpq/DBD::Pg libraries cease to
work. Instead, they give the standard message you get when the
DB cluster is not running.
Try ktrace'ing the client to see what
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Is there a setting that allows me to specify that strings containing
the '/' should be parsed into separate words? As is, I can't find
'apple' or 'orange'.
No setting, I think you would have to mess with tsearch2 dictionaries. A
far
Unlike my previous upgrade from the 7.4.x series to the 8.1.x series, I
now have all data in /var/lib/pgsql/data. Currently running 8.1.4 on my
Slackware system and want to upgrade to 8.2.4, so I'm checking my procedure
before inadvertently creating major problems for myself.
What I believe
On Thursday 26. April 2007 17:10, Joshua D. Drake wrote:
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.
Gentoo is still on version
Hello!
I would do the following (in that order):
1.) Check for a performant application logic and application design (e.g.
degree of granularity of the Java Hibernate Mapping, are there some
object iterators with hundreds of objects, etc.)
2.) Check the hibernate generated queries and whether
Hi -
I have a table of words and a table linking words in various ways:
create table allWords (
wordIDserial PRIMARY KEY,
word text
);
create unique index ix_allwords_word ON allwords (word);
create table allWordRelations (
word1ID integer references allWords,
Leif B. Kristensen wrote:
On Thursday 26. April 2007 17:10, Joshua D. Drake wrote:
Actually, I've a feeling that it would be trivial to do with just
about any existing packaging system ...
Yes pretty much every version of Linux, and FreeBSD, heck even Solaris
if you are willing to run 8.1.
On Fri, 2007-04-20 at 15:52, Jonathan Vanasco wrote:
I need a certain unique constraint in pg that i can't figure out.
Given:
create table test_a (
id serial ,
name_1 varchar(32) ,
name_2 varchar(32)
);
I need name_1 and name_2 to
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.
A simplistic approach that counts a business day as being Monday
through Friday would be something like
Joshua D. Drake wrote:
Ron Mayer wrote:
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not running
the newest minor version for a branch. ...
uhmmm gah, errm no... e why? :)
Mostly because it seems like a near FAQ here
- -- I need to get the a total number of business days (from monday to
- -- friday) between two dates.
- -- Someone can help me please.
A simplistic approach that counts a business day as being Monday
through Friday would be something like this:
However, you quickly run into the problem of
On 4/25/07, Ron Mayer [EMAIL PROTECTED] wrote:
Carlos Moreno wrote:
Tom Lane wrote:
Well, if you can't update major versions that's understandable; that's
why we're still maintaining the old branches. But there is no excuse
for not running a reasonably recent sub-release within your branch.
DEBUG_GEOMETRY_STATS is specific to POSTGIS Geographic Implementation System
package..Have you tried their discussion group at
[EMAIL PROTECTED]
M--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message
Dear,
We are facing performance tuning problem while using PostgreSQL Database
over the network on a linux OS.
Our Database consists of more than 500 tables with an average of 10K
records per table with an average of 20 users accessing the database
simultaneously over the network. Each table
On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote:
Owen Hartnett wrote:
I want to freeze a snapshot of the database every year (think of
end of year tax records). However, I want this frozen version
(and all the previous frozen versions) available to the database
user as read-only. My
I'm trying to build PostgreSQL 8.2 on Windows XP PRO. I've already
downloaded and configured all the dependencies, including bison and flex.
However, when I try to make it I receive the following error: WARNING
Bison install not found, or unsupported Bison version.
Attempting to build without.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 04/26/07 13:38, Ron Mayer wrote:
Joshua D. Drake wrote:
Ron Mayer wrote:
How about if PostgreSQL periodically check for updates on the
internet and log WARNINGs as soon as it sees it's not
running the newest minor version for a branch. ...
On Thursday 26 April 2007 9:47 am, Rich Shepard wrote:
Unlike my previous upgrade from the 7.4.x series to the 8.1.x series, I
now have all data in /var/lib/pgsql/data. Currently running 8.1.4 on my
Slackware system and want to upgrade to 8.2.4, so I'm checking my procedure
before
Hello!
I'd like to learn more about PostgreSQL (8.x) internal architecture so as to
build C extensions and Stored Procedures in C. I think that I nice way to
start is trying to compile PostgreSQL from the source. I'm on Windows XP
PRO. I've found this article:
Marcelo de Moraes Serpa wrote:
Hello!
I'd like to learn more about PostgreSQL (8.x) internal architecture so
as to build C extensions and Stored Procedures in C. I think that I nice
way to start is trying to compile PostgreSQL from the source. I'm on
Windows XP PRO. I've found this article:
John D. Burger [EMAIL PROTECTED] writes:
I have two queries for looking up related words which I think should
be equivalent, but 7.4.8 comes up with very different plans.
They're not at all equivalent:
explain analyze select w2.word from allwords w1 join allwordrelations
as r on
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
However, you quickly run into the problem of holidays. While you
could construct a helper table listing all the holidays, ones that
don't fall on the same day every year (e.g. Easter) will trip
you up.
Er, isn't Easter usually on a
I've got an interesting case study that I don't fully understand from
a postgres perspective, and I'm hoping that someone in the community
might help me understand what led to the outcome and whether it's
easily prevented or not.
The setup: PostgreSQL 8.2.3 on Solaris 10 x86
On Thu, 26 Apr 2007, Adrian Klaver wrote:
Generally it is a better idea to dump the old version with the new
versions pg_dump,pg_dumpall commands. The new versions know more about the
old versions of the database than the other way around.
Hi, Adrian!
I wondered about this.
I generally
Anyone have any ideas on how to handle a work queue? I've been thinking
about optimizing this process for quite a while.
Basically, my queue table consists of a few-hundred-thousand records
describing things to do. To pare things to the minimum, a queue record
can be considered to have a status
On 4/27/07, Steve Crawford [EMAIL PROTECTED] wrote:
Anyone have any ideas on how to handle a work queue? I've been thinking
about optimizing this process for quite a while.
I have been using PostgreSQL for the exact same thing, except I have
not yet reached the stage where I need to process
Thomas F. O'Connell [EMAIL PROTECTED] writes:
1. What aspect of postgres' memory usage would create an out of
memory condition?
I'm guessing you ran the box out of swap space --- look into what other
processes got started as a result of adding the NFS mount, and how much
memory they wanted to
Try this:
select column_name from information_schema.columns where table_name =
'tablename' order by ordinal_position;
On Apr 26, 2007, at 6:11 PM, stephen wrote:
Thanks, that worked ;-( but is there any way of ordering the column
names by their natural order in the table - same as the
On 4/27/07, Steve Crawford [EMAIL PROTECTED] wrote:
Anyone have any ideas on how to handle a work queue? I've been thinking
about optimizing this process for quite a while.
Basically, my queue table consists of a few-hundred-thousand records
describing things to do. To pare things to the
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
On 4/27/07, Merlin Moncure [EMAIL PROTECTED] wrote:
how about this:
create table job(job_id int, [...])
create sequence worker;
couple typos: here is an example that works:
create table job(job_id serial);
create sequence worker;
-- get next available job
create function next_job() returns
Martijn van Oosterhout wrote:
On Wed, Mar 14, 2007 at 08:09:24PM -0400, Kenneth Downs wrote:
What I have noticed is that once the innermost instance exits, none of
the outer instances execute any further, suggesting that the plperl
routine is not re-entrant (if I am using that term
Hello,
Forgive me if this has been discussed before (or if it sounds absurd)
Upgrading large postgres databases (100GB+) takes awfully long time when
doing dump/restore. I was wondering if this process can be optimized by
directly dumping to a new version of Pg database directly on another
On Apr 26, 2007, at 20:09 , CAJ CAJ wrote:
Upgrading large postgres databases (100GB+) takes awfully long time
when doing dump/restore. I was wondering if this process can be
optimized by directly dumping to a new version of Pg database
directly on another server without having to dump to
Upgrading large postgres databases (100GB+) takes awfully long time
when doing dump/restore. I was wondering if this process can be
optimized by directly dumping to a new version of Pg database
directly on another server without having to dump to the filesystem
and then restore it.
From the
Tom Lane replied:
I have two queries for looking up related words which I think should
be equivalent, but 7.4.8 comes up with very different plans.
They're not at all equivalent:
If there are duplicate word1id,word2id entries in allwordrelations,
the
first query will produce duplicate
John D. Burger [EMAIL PROTECTED] writes:
Tom Lane replied:
But the alternative is probably even worse: without that
allwordrelations has to be joined to w1 and w2 simultaneously, meaning
that the unconstrained cartesian product of w1 and w2 has to be formed
first.
Hmm, but wouldn't it at
Steve Crawford wrote:
Anyone have any ideas on how to handle a work queue? I've been
thinking
about optimizing this process for quite a while.
I use a variant of The Tom Lane Solution previously pointed to, your
Plan 1 is very similar.
This does not produce desirable results. In the
see pg_migrator project which could help you.
Oleg
On Thu, 26 Apr 2007, CAJ CAJ wrote:
Hello,
Forgive me if this has been discussed before (or if it sounds absurd)
Upgrading large postgres databases (100GB+) takes awfully long time when
doing dump/restore. I was wondering if this process can
63 matches
Mail list logo
