Hi,
We're working since 4 years on Postgres for a e-crm web based
application that deals with emails.
As our customers bases keep growing (more than one million rows in
sereval tables) we started to face performance issue with 7.2 and
decided to upgrade to 7.4 since one month.
The upgrade
Hi Mike,
If your foreign keys are all single-attribute, you can solve the
problem easily by only using the first element of each key.
I like to use the following view to examine foreign keys:
CREATE VIEW fkey AS
SELECT t.relname AS tab, c.attname AS col, ft.relname AS ftab,
fc.attname AS
Christophe Musielak wrote:
Hi,
We're working since 4 years on Postgres for a e-crm web based
application that deals with emails.
A few immediate observations:
1. Try not to reply to an existing message when posting a new question,
it can mess up threaded views.
2. The performance list is
Just to cover the ovbious solution ...
How often do you run vacuum analyze on this database? If the data has been
around for a long time, have you considered using CLUSTER to physically reorder
the tables?
Christophe Musielak [EMAIL PROTECTED] wrote:
Hi,
We're working since 4 years on
Thanks!
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 11:49 PM
To: David Parker
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] building 7.4.3 on Solaris 9/Intel
OK, I have fixed this so the configure test happens with the proper
thread
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?
Regards,
Jernej Kos.
On Wednesday 16 of June 2004 08:12, Martijn van Oosterhout wrote:
On Wed, Jun 16, 2004 at 07:12:26AM +0200, Jernej Kos wrote:
Christophe Musielak [EMAIL PROTECTED] writes:
- Seq Scan on threads t
(cost=0.00..19431.23
rows=82667 width=583)
Filter: (((mailbox_id = 2) OR
(mailbox_id
= 3) OR (mailbox_id = 5) OR (mailbox_id = 20) OR (mailbox_id = 21) OR
Jernej Kos [EMAIL PROTECTED] writes:
Yes i tried that already - and as you said, it works. But i need to have one
column sorted DESC and one ASC. Is there any way this could be done ?
Not easily. You could look into building a reverse sort operator
class for one index column or the other.
Jernej Kos [EMAIL PROTECTED] writes:
Well, writing a C function is not a problem ;) So where could i find any
documentation regarding this matter ?
Read the Interfacing Extensions To Indexes docs chapter. A crude
example for integers would go like
regression=# create function revcmp(int,int)
On Wed, 16 Jun 2004, Joolz wrote:
In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table, one of
them to the medical rows, the other one to the none-medical rows. I
would like to make the reference with a view, like
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them. I have a ton of select queries
(but none for
Actually, you *can* write your SQL to be ignorant of schemas (while
still using them because they are good).
In postgres, you specifiy a search path for the user you connect as and
make sure that have the appropriate access rights to objects in other
schemas, and that the other schemas exist
Hi Ben,
Check this mailing list for foreign keys and deadlock.
Short info:
Postgres exclusively locks the referenced records of a foreign key
relationship when the child record is updated, so multiple runs (in
different transactions) of one insert query could cause deadlock if they
update rows
Ben [EMAIL PROTECTED] writes:
I'm doing a bunch of data mining against a postgres database and have
run into an interesting problem with deadlocks. The problem is,
postgres is detecting them and then wacking the offending process, and
I can't figure out what's causing them.
The processes
Thanks for the quick reply (and summary!).
According to the messages I've found on the list, basically the answer
seems to be, don't do this. On the other hand, pretty much every
message on the subject is pre-7.4. There is some mention of using
deferred foreign keys to reduce the chance for a
Hello,
Deferred checks can greatly reduce the deadlock chance because of the
timing of the foreign key check. I won't say it can eliminate them,
and I don't think anyone here would suggest that you don't use Foreign keys.
Sincerely,
Joshua D. Drake
Ben wrote:
Thanks for the quick reply (and
So is everybody simply accepting the chance of deadlocks, thanks to
their foreign keys? Given what I know about why this problem exists, it
doesn't seem to have an easy solution but from my naive perspective
it seems like something that we shouldn't have to just live with,
either.
On Jun
[ BCC to hackers.]
SRA America, based in New York City, is looking for a summer intern or
part-time consultant to assist Bruce Momjian in developing training
classes and certification tests. The training materials already exist,
but it needs to be reorganized and exams created.
The applicant
hi tom,
here the results of
EXPLAIN ANALYZE SELECT * FROM basiseintrag WHERE kundennummer LIKE
'0101%' AND firma LIKE 'top con%';
run on all three servers:
main server:
QUERY PLAN
[EMAIL PROTECTED] ([EMAIL PROTECTED]) writes:
is there a way to do backup and restore just with the use of jdbc?
You could presumably reimplement pg_dump in Java; that seems redundant
in view of the fact that pg_dump is already implemented in C.
--
let name=cbbrowne and tld=cbbrowne.com in name
1: Is index capable of being used on the slaves, or are they just making
bad decisions? Try disabling seq scans on the slave servers for testing
and see if they can use the index. Use set enable_seqscan = FALSE;
If they can't use the index then, then they are likely in the wrong
locale, and
I have plperl installed my PostgreSQL 7.4.2 server, but from what I
understand in chapter 39.3 of the docs, you cannot access the databases
without DBD::PgSPI. According to the readme for that module, it will
only run on the untrusted plperlu.
Is this the only way to run queries (SELECT, INSERT,
[Stephan Szabo schreef op 16-06-2004 07:57 -0700]
On Wed, 16 Jun 2004, Joolz wrote:
In my db I have a table type_of_action, fields code varchar, name
varchar, medical boolean. Two other tables refer to this table,
one of them to the medical rows, the other one to the none-medical
Hey Bruce,
I assume this is the position we were talking about on the phone. I
sent you my resume that day, did you get it? Just wanted to confirm
since I didn't heard from you.
Matthew
On Wed, 2004-06-16 at 14:42, Bruce Momjian wrote:
[ BCC to hackers.]
SRA America, based in New York
hi,
To see the query results in native language see
http://database.sarang.net/?inc=readaid=5368criteria=pgsqlsubcrit=qnaid=limit=20keyword=page=1
the simpler url is
http://database.sarang.net/?criteria=pgsql
becase there is no korean postgresql list. poster is joesp
basically the
On 16/06/2004 21:27 Robert Fitzpatrick wrote:
I have plperl installed my PostgreSQL 7.4.2 server, but from what I
understand in chapter 39.3 of the docs, you cannot access the databases
without DBD::PgSPI. According to the readme for that module, it will
only run on the untrusted plperlu.
Is this
This is part of the Columns View, if you add a numeric field to your table
and don't provide any Length or Precision then :
numeric_precision is returned as 65535
numeric_scale is returned as 65531
Is this what you'd expect, and what does it mean to create a column with
no Length or Precision,
On Tue, 15 Jun 2004 13:06:40 -0700, Shane Wegner
[EMAIL PROTECTED] wrote:
The index I created reads:
create index books_idx1 on books(publisher_id,place_id,illustrator_id,
edition_id,type_id,category_id,binding_id,id);
This index is useless, drop it. Is there an index on books(id)?
The other
hello,
thank you for your short - but informative - answers!
i don't know why this very very important thing isn't implemented by
the jdbc (and other...) driver. it shouldn't only be managable by those
sitting in front of the machine but also via my program to handle some
critical situation
29 matches
Mail list logo
