Thanks to input Bruce M., figured out my performance problems - had to do
with a few QUERIES FROM HELL! After running EXPLAIN a few times I fine
tuned some of the worst ones, mostly over use of sub queries. Still
combing through my query log.
Getting there...
-r
---
Outgoing mail is certi
>
> > Just put a note in the installation docs that the place where the
>database
> > is initialised to should be on a non-Reiser, non-XFS mount...
>
>Sure, we can do that now.
I still think this is not necessarily the right approach either. One
major purpose of using a journaling fs is for fast
> A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all*
> constraints named "foo" on the target table.
Then it should probably be a good thing to avoid the automatic generation of
duplicate names? I might take a look at that, actually...
Chris
---(end o
On Thu, 3 May 2001, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > If I read the spec correctly, table constraint names are supposed to be
> > unique across a schema.
>
> That's what the spec says, but I doubt we should enforce it. For one
> thing, what do you do with inherited
Rachit Siamwalla wrote:
> Thanks a lot for your total and complete description of the process. (i
> should have checked out the sprm first before asking). I empathize with
> what you said about packaging not being a simple task, i have been through
> the agony.
Empathize is appropriate if you've
Stephan Szabo <[EMAIL PROTECTED]> writes:
> If I read the spec correctly, table constraint names are supposed to be
> unique across a schema.
That's what the spec says, but I doubt we should enforce it. For one
thing, what do you do with inherited constraints? Invent a random name
for them? No
I left it unsaid that, in fact, all constraint names should be unique.
Unnamed column constraints as far as I can tell get a '$n' automatically
assigned name.
Maybe the create table function should process named constraints first, and
then the unnamed ones to prevent such a problem?
Chris
-
Bruce Momjian wrote:
>
> > Just put a note in the installation docs that the place where the database
> > is initialised to should be on a non-Reiser, non-XFS mount...
>
> Sure, we can do that now. What do we do when these are the default file
> systems for Linux? We can tell them to create ot
If I read the spec correctly, table constraint names are supposed to be
unique across a schema. So technically the constraint name should also
not conflict with the name of an fk constraint, or a unique index. In
addition, generated constraint names are supposed to follow the same
syntax rules
Lamar Owen <[EMAIL PROTECTED]> writes:
> Ryan Mahoney wrote:
> > Any input would be helpful! If you need additional info let me know.
>
> > BTW, Thanks to Lamar for some great tips today!
>
> You're more than welcome.
>
> I forgot a basic tip, which leads to a question:
> How often are you r
> cvsup -L 2 postgres.cvsup
> Parsing supfile "postgres.cvsup"
> Connecting to postgresql.org
> Cannot connect to postgresql.org: Connection refused
> Will retry at 22:31:23
...
Me too. Marc, could you take a peek at it? cvsupd seems to be gone or
port blocked or ??
- Thom
> Well, arguably if you're setting up a database server then a reasonable DBA
> should think about such things...
Yes, but people have trouble installing PostgreSQL. I can't imagine
walking them through a newfs.
>
> (My 2c)
>
> Chris
>
> -Original Message-
> From: Bruce Momjian [mai
Thanks a lot for your total and complete description of the process. (i
should have checked out the sprm first before asking). I empathize with
what you said about packaging not being a simple task, i have been through
the agony.
About putting your stuff into the postgres tree, i believe it wou
> Just put a note in the installation docs that the place where the database
> is initialised to should be on a non-Reiser, non-XFS mount...
Sure, we can do that now. What do we do when these are the default file
systems for Linux? We can tell them to create other types of file
systems, but tha
Hi,
I have noticed that it is possible to create duplicate CHECK (haven't tried
other) constraints in 7.0.3 by doing something like this:
CREATE TABLE "test" (
"a" int4,
CHECK (a < 400),
CONSTRAINT "$1" CHECK (a > 5)
);
I was just fiddling around with trying to implement the 'DROP CONS
Well, arguably if you're setting up a database server then a reasonable DBA
should think about such things...
(My 2c)
Chris
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Friday, 4 May 2001 9:42 AM
To: Christopher Kings-Lynne
Cc: mlw; Hackers List
Subject: Re: [
Ryan Mahoney wrote:
> Any input would be helpful! If you need additional info let me know.
> BTW, Thanks to Lamar for some great tips today!
You're more than welcome.
I forgot a basic tip, which leads to a question:
How often are you running VACUUM ANALYZE?
If this were PostgreSQL 7.0.3, we
I hope you have those postmasters listening on different ports.
> Here is some output from top...
>
> 9:20pm up 40 min, 1 user, load average: 3.77, 3.12, 3.74
> 41 processes: 36 sleeping, 5 running, 0 zombie, 0 stopped
> CPU states: 99.2% user, 0.7% system, 0.0% nice, 0.0% idle
> Mem: 5
There might be a problem, but if no one mentions it to the maintainers of
those
fs's, it will not get fixed...
Regards
John
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Trond Eivind Glomsrød wrote:
>
> Rachit Siamwalla <[EMAIL PROTECTED]> writes:
>
> > Also i never got a response on who actually packages those linux init
> > scripts that appear in the RPM but not on the pgsql cvs tree. (i am also
> > curious on why it is different, and how the RPM is built).
>
Here is some output from top...
9:20pm up 40 min, 1 user, load average: 3.77, 3.12, 3.74
41 processes: 36 sleeping, 5 running, 0 zombie, 0 stopped
CPU states: 99.2% user, 0.7% system, 0.0% nice, 0.0% idle
Mem: 515664K av, 303712K used, 211952K free, 37476K shrd, 39552K buff
Swap: 5
Rachit Siamwalla wrote:
> oh btw, i completely forgot to mention the minor fixes to the linux init
> scripts i mentioned earlier (about 2 weeks ago) for things that perhaps
> should be in the 7.1.1 release. (someone sent out a mail that they were
> branching 7.1.1)
> Also i never got a response o
!! I haven't ran VACUUM ANALYZE since last night. Just ran it -
performance has improved significantly. I think I am going to have to run
it hourly during this high traffic time. Postmasters are still utilizing
about 100% of the CPU. Is this normal? I am considering increasing the
shmmax
Just put a note in the installation docs that the place where the database
is initialised to should be on a non-Reiser, non-XFS mount...
Chris
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of mlw
Sent: Thursday, 3 May 2001 8:09 PM
To: Bruce Momjian; Hacke
I am running Postgresql 7.1 on a dedicated Redhat 7.0 box with 512meg ram
and an IDE hard drive.
All day long queries that usually seem to execute instantaneously have been
taking up to 10 second to run! I generally have about 6 postmasters
running, utilizing anywhere from 1% to 96% CPU utili
Rachit Siamwalla <[EMAIL PROTECTED]> writes:
> Also i never got a response on who actually packages those linux init
> scripts that appear in the RPM but not on the pgsql cvs tree. (i am also
> curious on why it is different, and how the RPM is built).
Lamar Owen and I.
--
Trond Eivind Glomsrø
Not sure on their status. Are they listed on the outstanding patches
page at the bottom of the developers page? Probably too late for 7.1.1
now.
[ Charset ISO-8859-1 unsupported, converting... ]
> oh btw, i completely forgot to mention the minor fixes to the linux init
> scripts i mentioned
oh btw, i completely forgot to mention the minor fixes to the linux init
scripts i mentioned earlier (about 2 weeks ago) for things that perhaps
should be in the 7.1.1 release. (someone sent out a mail that they were
branching 7.1.1)
Also i never got a response on who actually packages those linu
On Thu, 3 May 2001, mlw wrote:
> This behavior raises the question about file system usage in Postgres. Many
> databases, such as Oracle, create table space files and operate directly on the
> raw blocks, bypassing the file system altogether.
>
> On one hand, Postgres is easy to use and maintain
On Thu, 3 May 2001, Magnus Naeslund(f) wrote:
> I have a table:
>
> create table forsamling (
> id SERIAL,
> for_id int4 unique not null,
> kund_flag int8 not null default 1,
> online smallint default 0,
> klar
> explain select * from forsamling where klar = 1;
Try SELECT * FROM forsampling WHERE klar = 1::int2
-Mitch
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Thu, 3 May 2001, bpalmer wrote:
> I'm starting to throw together a web site relating to postgresql
> replication, trying to bring together the ideas we have thrown around so
> far. If anyone has any good docs (on replication not relating to
> postgresq too), please send me the links.
Colla
I've hacked together a metaphone function from an existing metaphone
implementation and the example provided by the soundex() function in
contrib.
I'd like to send this out to the general list, in the hopes that people
will find it useful, but I wanted to wave it in front of the -hackers and
-pa
Bruce Momjian writes:
> I have completed branding 7.1.1. HISTORY file shows changes as:
>
> Fix for numeric MODULO operator (Tom)
> pg_dump fixes (Philip)
Should probably point out that pg_dump is now able to dump 7.0 databases
as well.
> readline 4.2 fixes (Peter E)
>
> Bruce Momjian writes:
>
> > I have completed branding 7.1.1. HISTORY file shows changes as:
> >
> > Fix for numeric MODULO operator (Tom)
> > pg_dump fixes (Philip)
>
> Should probably point out that pg_dump is now able to dump 7.0 databases
> as well.
Done:
pg_dump can dump
cvsup -L 2 postgres.cvsup
Parsing supfile "postgres.cvsup"
Connecting to postgresql.org
Cannot connect to postgresql.org: Connection refused
Will retry at 22:31:23
--
# This file represents the standard CVSup distribution file
# for the PostgreSQL ORDBMS project
#
OK, Oleg, I am applying this on your word only. I don't understand its
purpose, but you sent it with a 7.1.1 subject so I assume you want it in
there. This is not a critical area of our code.
> Please,
>
> apply a little patch:
>
> --- src/test/locale/test-ctype.cTue Sep 1 08:40:33
I have a table:
create table forsamling (
id SERIAL,
for_id int4 unique not null,
kund_flag int8 not null default 1,
online smallint default 0,
klarsmallint default 0,
);
create index forsamling_idx on forsa
> > This behavior raises the question about file system usage in Postgres. Many
> > databases, such as Oracle, create table space files and operate directly on the
> > raw blocks, bypassing the file system altogether.
>
> OK, we have considered this, but frankly, the new, modern file systems
> lik
Please,
apply a little patch:
--- src/test/locale/test-ctype.cTue Sep 1 08:40:33 1998
+++ /u/megera/app/locale/test/test-ctype.c Fri Sep 15 19:12:06 2000
@@ -39,7 +39,7 @@
void
describe_char(int c)
{
- charcp = c,
+ unsigned char cp = c,
Tom Lane <[EMAIL PROTECTED]> writes:
> No. I'm not sure whether or not I believe the comment about Unix
> accounts; Postgres does not care about Unix accounts, and never has
> to my knowledge. But it has always used the usesysid as owner
> identification for database objects (tables etc). If t
I'm starting to throw together a web site relating to postgresql
replication, trying to bring together the ideas we have thrown around so
far. If anyone has any good docs (on replication not relating to
postgresq too), please send me the links.
Thanks.
- Brandon
b. palmer, [EMAIL PROTECTED]
> > kernel to do it for us. Reimplementing a filesystem doesn't strike me
> > as a profitable use of our time.)
> Ditto. The database is complicated enough.
Maybe some kind of recommendation would be a good thing. That is, if the
PostgreSQL community has enough knowledge.
A section in the doc
I have completed branding 7.1.1. HISTORY file shows changes as:
Fix for numeric MODULO operator (Tom)
pg_dump fixes (Philip)
readline 4.2 fixes (Peter E)
JOIN fixes (Tom)
AIX, MSWIN, VAX,N32K fixes (Tom)
Multibytes fixes (Tom)
Unicode fixes
Bruce Momjian wrote:
>> The attached patch implements a method of connection authentication for
>> Unix sockets that support SCM_CREDENTIALS. This includes Linux kernels
>> 2.2 and 2.4 at least; I don't know what other implementations support
>> it.
>
>Are SCM_CREDENTIALS supported by
> Matthew Kirkwood <[EMAIL PROTECTED]> writes:
> > From some stracing of 7.1, the most common syscall issued by
> > postgres is an lseek() to the end of the file, presumably to
> > find its length, which seems to happen up to about a dozen
> > times per (pgbench) transaction.
>
> > Tablespaces wo
I am starting to package 7.1.1, and I see I did not brand 7.1 properly.
I forgot the date in the HISTORY file, and didn't update register.txt.
I will do all those now for 7.1.1.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-
> Is it possible to use pg_dump 7.1 on a 7.0 database?
Tried. Nope.
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- T
> Kovacs Zoltan <[EMAIL PROTECTED]> writes:
> >> Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?
>
> > Hmmm. That was the problem. Thanks! By the way, could you please define a
> > unique constraint on column 'usesysid' in future in PostgreSQL?
>
> Yup, there should be one
Bruce Momjian writes:
> Really? We are removing usesysid? Seems the admin will no longer be
> able to choose the users id, right?
Not that this was ever useful.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)
Tom Lane wrote:
> Current CVS pg_dump (grab the nightly snapshot if you don't use CVS,
> or wait for 7.1.1 in a day or two) is alleged to be able to work
> against a 7.0 database. Give it a try.
That would be great, I had plans to wait for 7.1.1 anyway.
Thanks
--
Alessio F. Bragadini
> [apologies if this appears twice; I thought I had sent it but it hasn't
> appeared anywhere]
> The attached patch implements a method of connection authentication for
> Unix sockets that support SCM_CREDENTIALS. This includes Linux kernels
> 2.2 and 2.4 at least; I don't know what other impleme
> > I know xfs and reiser are both log based. Do we need to be concerned
> > about PostgreSQL performance on these file systems? I use BSD FFS with
> > soft updates here, so it doesn't affect me.
>
> I did see poor performance on reiserfs, I have not as yet ventured into using
> xfs.
>
> I occ
Alessio Bragadini <[EMAIL PROTECTED]> writes:
>> Is it possible to use pg_dump 7.1 on a 7.0 database?
> Tried. Nope.
Current CVS pg_dump (grab the nightly snapshot if you don't use CVS,
or wait for 7.1.1 in a day or two) is alleged to be able to work
against a 7.0 database. Give it a try.
Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I hope FTS for postgresql doesn't start looking like Oracle's
> > Context/Intermedia... Proprietary interfaces == "lock in" == "ick".
> >
>
> This is what I was hoping... Something to make it automatic.
>
Well, I would love to see full text indexi
> Kovacs Zoltan writes:
>
> > By the way, could you please define a unique constraint on column
> > 'usesysid' in future in PostgreSQL?
>
> The usesysid column will be removed and the oid column will be used
> instead. That one tends to be unique, but an index will still be added.
Really? We
Alessio Bragadini <[EMAIL PROTECTED]> writes:
> It should work fine if rows would be dumped according to oid. Can this
> be considered a bug?
No; or at least, that solution would be equally buggy. It's not much
harder than your given example to construct cases where dumping the rows
in OID order
Title: perlDBD::pg error! Please help (have all details in post)
Have attempted to install DBI and DBD, but DBD “make test” fails.
Below is my complete procedure , starting from:
Accessing PostgreSQL from the command line, (successful)
creating a database, populating. (successful)
Expandi
Tom Lane wrote:
> It's not much
> harder than your given example to construct cases where dumping the rows
> in OID order would be wrong too (just takes some UPDATEs).
Yes, I figured out myself quickly. :-(
> like you may have some pre-release copy of pg_dump that gets this wrong
> (the commen
Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes:
> The usesysid was originally intended to map pg users to unix accounts.
> I do not see why it should not be possible to map different pg users
> to a single unix account. The above imho stems from an improper use of this
> column which needs to
Kovacs Zoltan writes:
> By the way, could you please define a unique constraint on column
> 'usesysid' in future in PostgreSQL?
The usesysid column will be removed and the oid column will be used
instead. That one tends to be unique, but an index will still be added.
--
Peter Eisentraut [EM
Oliver Elphick writes:
> Since it is not universally supported, I have included a configure test.
> autoconf needs to be run after installing the patch.
You don't need Autoconf tests for cpp symbols. You can just write #ifdef
WEIRD_SYMBOL in the code.
Btw., never ever use AC_EGREP_*.
--
Pete
> >> Is it possible that 1060 and 1092 have the same usesysid
> in pg_shadow?
>
> > Hmmm. That was the problem. Thanks! By the way, could you
> please define a
> > unique constraint on column 'usesysid' in future in PostgreSQL?
>
> Yup, there should be one (and one on usename, too). Not sure
Matthew Kirkwood <[EMAIL PROTECTED]> writes:
> From some stracing of 7.1, the most common syscall issued by
> postgres is an lseek() to the end of the file, presumably to
> find its length, which seems to happen up to about a dozen
> times per (pgbench) transaction.
> Tablespaces would solve this
Oleg:
I concur with this, and would like to collaborate as possible.
[You may recall my messages from a couple weeks back. I'm looking at
ways of implementing 'concurrent' PostGres on a NUMA machine that has
both local memory and (a bit more costly) access to a shared memory].
Bettina K
Kovacs Zoltan <[EMAIL PROTECTED]> writes:
>> Is it possible that 1060 and 1092 have the same usesysid in pg_shadow?
> Hmmm. That was the problem. Thanks! By the way, could you please define a
> unique constraint on column 'usesysid' in future in PostgreSQL?
Yup, there should be one (and one on u
On Thu, 3 May 2001, mlw wrote:
> I would bet it is a huge amount of work to use a "table space" system
> and no one wants that.
>From some stracing of 7.1, the most common syscall issued by
postgres is an lseek() to the end of the file, presumably to
find its length, which seems to happen up to
Bruce Momjian wrote:
>
> I was talking to a Linux user yesterday, and he said that performance
> using the xfs file system is pretty bad. He believes it has to do with
> the fact that fsync() on log-based file systems requires more writes.
>
> With a standard BSD/ext2 file system, WAL writes ca
[apologies if this appears twice; I thought I had sent it but it hasn't
appeared anywhere]
The attached patch implements a method of connection authentication for
Unix sockets that support SCM_CREDENTIALS. This includes Linux kernels
2.2 and 2.4 at least; I don't know what other implementations s
I have a table with a FK on itself: in fact a record may depend on
another table ("pig's ear" :-) I may run into a problem
dumping/restoring using pg_dump, PostgreSQL 7.1.0.
Here's a simplification of the table:
provo=# SELECT version();
version
---
On Wed, 2 May 2001, Tom Lane wrote:
> Kovacs Zoltan <[EMAIL PROTECTED]> writes:
> > tir=> \c - 1060
> > You are now connected as new user 1060.
> > tir=> select user;
> > current_user
> > --
> > 1092
> > (1 row)
>
> Is it possible that 1060 and 1092 have the same usesysid in pg_sha
Hi,
here is an answer from Bettina Kemme - author of
Postgres-R - consisent replcation engine based on Postgres 6.4
(URL of paper - http://citeseer.nj.nec.com/330257.html).
While I don't see much activity on replication topic I think it's
worth to discuss with Bettina design issue and co-ordinat
72 matches
Mail list logo