Thanks for all the replies. As of right now I think I have it narrowed down to
checkpoints based on the iostat activity I see when the hangs occur as well as
the checkpoint_timeout defaulting to 5 min.
I've upped checkpoint_warnings to 3600 to confirm but also made a few other
changes. I move
Cantor wrote:
On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote:
I want to learn how a real database works. And I am about to
start reading the Postgre source code.
Are there any online documents that may document the code? Even
if it was a general guideline.
Any info
Adam Endicott <[EMAIL PROTECTED]> writes:
> Here's the output from explain analyze.
Wow, so the differential is all in the sort step.
8.2 does have improved sorting code, but I don't think that explains
the difference, especially not for a mere 16000 rows to be sorted.
Do you have comparable wor
Here's the output from explain analyze.
My desktop:
-
Unique (cost=6732.86..7380.50 rows=504 width=677) (actual
time=844.345..1148.705 rows=65 loops=1)
-> Sort (cost=6732.86..6773.34 rows=16191 width=677) (actual
time=844.341..1099.446 rows=16191 loops=1)
Sort Key:
On Thu, 9 Aug 2007, Scott Marlowe wrote:
Wouldn't that be the other way around, set checkpoint_warning to 1 so
it triggers every time the checkpoint happens?
The log message appears if the checkpoints happen more frequently than the
value, so setting to 1 would only trigger a warning if you g
On Jul 18, 11:50 am, [EMAIL PROTECTED] ("Jim C. Nasby") wrote:
> On Wed, Jul 18, 2007 at 11:30:48AM -0500, Scott Marlowe wrote:
> > EnterpriseDB, a commercially enhanced version of PostgreSQL can do
> > query parallelization, but it comes at a cost, and that cost is making
> > sure you have enough
On 8/9/07, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Tue, 7 Aug 2007, mr19 wrote:
>
> > I have a process that updates ~ 1500 rows in a table once a second. Every 5
> > minutes (almost exactly) the update takes ~ 15 seconds (normally < 1).
>
> Lots of updates will trigger checkpoints and, if you h
oops
On 8/9/07, Decibel! <[EMAIL PROTECTED]> wrote:
> You forgot the list. :)
>
> On Thu, Aug 09, 2007 at 05:29:18PM -0500, Scott Marlowe wrote:
> > On 8/9/07, Decibel! <[EMAIL PROTECTED]> wrote:
> >
> > > Also, a good RAID controller can spread reads out across both drives in
> > > each mirror on
On Aug 8, 2007, at 6:08 PM, Decibel! wrote:
Something else I like to look at is pg_stat_all_tables seq_scan and
seq_tup_read. If seq_scan is a large number and seq_tup_read/
seq_scan is
also large, that indicates that you could use an index on that table.
If seq_tup_read / seq_scan is large r
Greg Smith <[EMAIL PROTECTED]> writes:
> Try increasing checkpoint_warning in your postgresql.conf file to its
> maximum of 3600 and restart the server when you can tolerate a small
> service disruption;
You don't need a server restart to change checkpoint_warning --- SIGHUP
("pg_ctl reload") sh
mr19 <[EMAIL PROTECTED]> writes:
> I have a process that updates ~ 1500 rows in a table once a second. Every 5
> minutes (almost exactly) the update takes ~ 15 seconds (normally < 1).
Checkpoints?
> I have run htop/top on the machine during this time period and do not see
> anything unusual.
Tr
On Tue, 7 Aug 2007, mr19 wrote:
I have a process that updates ~ 1500 rows in a table once a second. Every 5
minutes (almost exactly) the update takes ~ 15 seconds (normally < 1).
Lots of updates will trigger checkpoints and, if you have auto-vacuum
turned on, regular vacuum activity--either
mr19 wrote:
> I have a process that updates ~ 1500 rows in a table once a second. Every 5
> minutes (almost exactly) the update takes ~ 15 seconds (normally < 1)
autovacuum_naptime perhaps?
Cheers,
Steve
---(end of broadcast)---
TIP 4: Have y
Adam Endicott <[EMAIL PROTECTED]> writes:
> When I run EXPLAIN ANALYZE on this query, it takes something like
> 1200ms on my desktop (Dual 2GHz G5 Mac - 1.5 GB RAM for reference) and
> about 14000ms on the production server (quad processor, 8 GB RAM,
> running Ubuntu). There are about 500 rows in t
Keep an eye on pg_stat_activity and pg_locks to see if any lock
contention is going on.
mr19 wrote:
I have a process that updates ~ 1500 rows in a table once a second. Every 5
minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). I
have run htop/top on the machine during thi
I want to learn how a real database works. And I am about to
start reading the Postgre source code.
Are there any online documents that may document the code? Even
if it was a general guideline.
Any information will be greatly appreciated.
Arturo Hernandez
--
On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote:
> I want to learn how a real database works. And I am about to
> start reading the Postgre source code.
>
> Are there any online documents that may document the code? Even
> if it was a general guideline.
>
> Any information
On Aug 7, 9:57 pm, [EMAIL PROTECTED] ("Simon Riggs") wrote:
> On Sun, 2007-08-05 at 03:45 -0700, Sergei Shelukhin wrote:
> > Is there any way to truncate WAL log in postgres?
> > We want to use full-backup strategy where we stop the server and copy
> > the data directory, however WAL log is taking
> If not, dump and restore the table.
Unfortunately we do not have adequate disk space, we wanted to reduce
the database size in order to back it up, cause there is no more space
for backups either 0_o
Is there any way to prevent
Dump & restore - you mean pg_dump?
---(en
Hello
My database is restored from a dump file every day. How I know that this
database is up to date (as it has no timestamp in any table).
If I create a file, I can know when I created it by seeing its property.
How I can do the same thing with a back up database.
Ta.
--
I've installed postgresql 8.2 on a windows vista machine and are trying to
connect to it from another one.
the server has ip 192.168.1.100 and the client 192.168.1.102
As I understand it, I should make some configuration changes in pg_hba.conf
to make this happen.
Both machines has both ipv4 and
Hi
I'm trying to set up a new webserver running php and pgsql. PHP was
connecting to postgres but I needed to install the php-gd module and now I
get the error...
"PHP Warning: pg_connect() [href='function.pg-connect'>function.pg-connect]: Unable to connect to
PostgreSQL server: could not
Thanks for the tip -- I'll check into it.
Sorry for top-posting but my email reader is challenged.
Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for
the sole use of the intended recipient(s) and may
For those who need to know the fields that a certain table has in a
postgresql database, here is the SQL statement:
SELECT DISTINCT attname, relname FROM pg_attribute pa, pg_class pc,
pg_tables pt WHERE pa.attrelid=pc.oid AND pc.relname=pt.tablename AND
pt.schemaname='public' AND attstattarget=-1
I have a process that updates ~ 1500 rows in a table once a second. Every 5
minutes (almost exactly) the update takes ~ 15 seconds (normally < 1). I
have run htop/top on the machine during this time period and do not see
anything unusual. I am running postgres 8.1.8 on a FC6 box.
Any type
I want to to know if these two are functionally equivalent. Is this:
Create table "users"
(
"userid" BigSerial NOT NULL,
"name" Varchar(20),
primary key ("userid")
) Without Oids;
Create table "sales"
(
"saleid" BigSerial NOT NULL,
"userid" Bigint NOT NULL,
On Aug 6, 3:44 pm, [EMAIL PROTECTED] (Ranieri Mazili) wrote:
> Hello,
>
> I have 2 questions.
> 1) Can I use a function that will return a string in a where clause like
> bellow?
>
> select *
> from table
> where my_function_making_where()
> and another_field = 'another_think'
>
> 2) Can
I have problem with permission, I need to use a user no SUPERUSER.
I use commands:
CREATE ROLE $USER LOGIN;
ALTER user $USER noCREATEDB NOCREATEROLE noCREATEUSER NOINHERIT;
ALTER USER $USER with password 'XX';
REVOKE create on SCHEMA public from public;
revoke all on schema PUBLIC FROM $USER;
Hi
I am having problems installing Postgressql 8.2 on Windows Vista.
The first problem I had was related to the UAC which I now have turned
off. But the last problem is that the installer stops when it can't runt
initdb. At this stage it rolls back an removes any possibilities to run
initdb m
I'm having an issue with a specific query, and I don't really know
where to start figuring out what's going on. I'm pretty new to
PostgreSQL in specific, and I'm not much of a database/SQL guru in
general. I've got one query that is consistently taking 10X longer to
run on a production machine than
On Aug 7, 1:26 pm, Arthernan <[EMAIL PROTECTED]> wrote:
> I want to learn how a real database works. And I am about to
> start reading the Postgre source code.
> Are there any online documents that may document the code? Even
> if it was a general guideline.
> Any information
clustering fail over... ala Oracle Parallel server
How can the server be setup in a cluster for load-balancing and failover
like perhaps OPS?
How does the Postges solution compare to an Oracle? MSSQL? MySQL solution?
Thank!
---(end of broadcast)-
On 8/9/07, Jonas Gauffin <[EMAIL PROTECTED]> wrote:
> I've installed postgresql 8.2 on a windows vista machine and are trying to
> connect to it from another one.
> the server has ip 192.168.1.100 and the client 192.168.1.102
Before people start wrecking their brains on the postgres end
have y
On Thu, Aug 09, 2007 at 04:16:15PM -0400, Woody Woodring wrote:
> My bad, the table I was looking (8.7) at had the first column as the
> decimal representation and I did notice that the numbers changed as they
> moved right.
>
> Is there a way for bytea to take a hex number, or do I need to conve
On Thu, Aug 09, 2007 at 06:14:43PM +0200, Leif B. Kristensen wrote:
> On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
> >You should probably use a trigger (a before one maybe) instead of a
> > rule.
>
> I tried that too, but I'm still quite shaky on how to write triggers,
> and the same
On Thu, Aug 09, 2007 at 10:22:57AM -0400, Vivek Khera wrote:
>
> On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote:
>
> >I have the times that it takes to to do a regular
> >vacuum on the clusters, will vacuum full take longer?
>
> almost certainly it will, since it has to move data to compact pa
Is there a way to get this to work remotely? IE: is there an indexing
part that can be run on the mail server that you'd connect to remotely?
On Thu, Aug 09, 2007 at 05:30:13PM +0900, Tatsuo Ishii wrote:
> Hi,
>
> I made a small demonstration for Sylph Searcher at Linux World at SF
> and was aske
Thanks,
My bad, the table I was looking (8.7) at had the first column as the
decimal representation and I did notice that the numbers changed as they
moved right.
Is there a way for bytea to take a hex number, or do I need to convert the
bit stream to octal numbers?
Thanks again,
Woody
-O
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Erik Jones wrote:
>
> On Aug 9, 2007, at 1:14 PM, Greg Smith wrote:
>
>> On Thu, 9 Aug 2007, Erik Jones wrote:
>>
>>> Perhaps we could have a nice, friendly discussion on using surrogate
>>> primary keys v. string based keys? Or, I think the body of
Kenneth Downs <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Implicit casts to text are evil, and are mostly going to be gone in 8.3.
>> So try not to rely on this behavior ...
> Based on general principle, or on specific bad things like unexpected or
> ill-defined results?
Both. Check the ar
On Aug 9, 2007, at 1:14 PM, Greg Smith wrote:
On Thu, 9 Aug 2007, Erik Jones wrote:
Perhaps we could have a nice, friendly discussion on using
surrogate primary keys v. string based keys? Or, I think the body
of the "nulls are bad" dead horse is collecting flies if anyone
wants to take
"Woody Woodring" <[EMAIL PROTECTED]> writes:
> Could someone explain why \208 is not a valid syntax for bytea?
Aren't those escapes octal?
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free
On Thu, 9 Aug 2007, Erik Jones wrote:
Perhaps we could have a nice, friendly discussion on using surrogate
primary keys v. string based keys? Or, I think the body of the "nulls
are bad" dead horse is collecting flies if anyone wants to take a swing
at it...
Following the handbook for dead h
Tom Lane wrote:
Kenneth Downs <[EMAIL PROTECTED]> writes:
Basically, it "knows what you mean" when you do substrings on dates and
numbers, doing an implicit cast for you.
Implicit casts to text are evil, and are mostly going to be gone in 8.3.
So try not to rely on this behavior ...
Kenneth Downs <[EMAIL PROTECTED]> writes:
> Basically, it "knows what you mean" when you do substrings on dates and
> numbers, doing an implicit cast for you.
Implicit casts to text are evil, and are mostly going to be gone in 8.3.
So try not to rely on this behavior ...
M S <[EMAIL PROTECTED]> writes:
> I can't think of any programs which would be locking the files (antivirus o=
> r other), but I'll have a look.
Since it's a temporary file, no other Postgres process would be touching
it. I strongly suspect an antivirus or similar tool is touching the
file just a
Jonas Gauffin wrote:
> I've installed postgresql 8.2 on a windows vista machine and are trying to
> connect to it from another one.
> the server has ip 192.168.1.100 and the client 192.168.1.102
>...
> Any suggestions?
Yes. Let us know what client you are using to connect and post the error
mess
On 8/9/07, Erik Jones <[EMAIL PROTECTED]> wrote:
>
> On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote:
> >
> > But this is a thoroughly dead horse, lets not beat it up again.
>
> Hah! Perhaps we could have a nice, friendly discussion on using
> surrogate primary keys v. string based keys?
On Aug 8, 2007, at 7:14 PM, Martijn van Oosterhout wrote:
But this is a thoroughly dead horse, lets not beat it up again.
Hah! Perhaps we could have a nice, friendly discussion on using
surrogate primary keys v. string based keys? Or, I think the body of
the "nulls are bad" dead horse i
On Wednesday 8. August 2007 15:12, Alban Hertroys wrote:
>You should probably use a trigger (a before one maybe) instead of a
> rule.
I tried that too, but I'm still quite shaky on how to write triggers,
and the same thing happened there: the inserted record was immediately
deleted. I solved the
Could someone explain why \208 is not a valid syntax for bytea?
I am getting the following:
test=> select E'\\207'::bytea;
bytea
---
\207
(1 row)
test=> select E'\\208'::bytea;
ERROR: invalid input syntax for type bytea
test=> select E'\\209'::bytea;
ERROR: invalid input syntax for type
=?ISO-8859-1?Q?H=E5kan_Jacobsson?= <[EMAIL PROTECTED]> writes:
> The table indexes aren't restored when I run this command:
> gunzip -c /filename/.gz | psql dbname
Since you haven't shown us what commands are in that file or what output
you get, it's impossible to make any intelligent response to
On 8/9/07, Louis-David Mitterrand
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> After our 7.4 to 8.2 upgrade using debian tools, we realized that some
> of our timestamps with tz had shifted:
>
> For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01'
> which is on a different month. Some of ou
I have reproduced this.
I'll upgrade to 8.2.4 and report back after my long weekend.
Cheers.
- Original Message
From: M S <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Thursday, 9 August, 2007 1:54:17 PM
Subject: Re: [GENERAL] failed to unlink, Permission denied
> no, but
Hi,
After our 7.4 to 8.2 upgrade using debian tools, we realized that some
of our timestamps with tz had shifted:
For example '2007-04-01 00:00:00+02' became '2007-03-31 23:00:00+01'
which is on a different month. Some of our applications were severely
disturbed by that.
Has anyone noticed th
Decibel! escreveu:
On Wed, Aug 08, 2007 at 08:56:47AM -0300, Andr? Volpato wrote:
Hello everybody,
I�m working with a small project to a client, using Postgres to
store data in a dimensional model, fact-oriented, e.g., a Datamart.
At this time, all I have is a populated database, w
On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote:
I have the times that it takes to to do a regular
vacuum on the clusters, will vacuum full take longer?
almost certainly it will, since it has to move data to compact pages
rather than just tagging the rows as reusable.
you can speed thing
Sure, we use a user interface widget called "Ajax Dynamic List" from
www.dhtmlgoodies.com. This replaces the HTML SELECT element.
When a user is sitting on a foreign-key field, such as a PATIENT or
CUSTOMER field, the user can just start typing letters or numbers. An
AJAX call is made to the
I have a couple of database clusters that need a vacuum full, and I
would like to estimate how long it will take, as it will need to be in a
maintenance window. I have the times that it takes to to do a regular
vacuum on the clusters, will vacuum full take longer?
--
Brad Nicholson 416-673-4106
> no, but (IMO) 8.2.4 is a required upgradeso you should be testing
that.
Understood, I'll try an upgrade after my repeat tests have finished.
> The server is unable to delete a file (specifically, a temporary
> table created for sorting). Have you considered any running services
> that may
Here is something cool that I did not realize postgres's substring()
could do.
Basically, it "knows what you mean" when you do substrings on dates and
numbers, doing an implicit cast for you. This is really nice if you
happen to be writing a generalized search system, as it makes the code
sig
On 8/9/07, M S <[EMAIL PROTECTED]> wrote:
> I left our app on soak test overnight, it ran fine for some time but after
> a few hours I noticed the following messages repeated in the log (the tmp
> filename changes, but the PlPgSql function which causes it does not).
> 2007-08-08 17:25:57 LOG:
Hello,
I'm fairly new to the more advanded functionality of PostgreSQL,
especially writing functions in PL/pgSQL and have something of a
design question, which doesn't seem to be answered anywhere I can
google to.
I've a view created in my schema, for which I'm adding rules for
updating and inse
Hi,
I left our app on soak test overnight, it ran fine for some time but after a
few hours I noticed the following messages repeated in the log (the tmp
filename changes, but the PlPgSql function which causes it does not).
2007-08-08 17:25:57 LOG: failed to unlink
"pg_tblspc/
Hi,
The table indexes aren't restored when I run this command:
gunzip -c /filename/.gz | psql dbname
/
Should I use another cmd? Or am I missing a parameter?
/regards, Håkan Jacobsson
---(end of broadcast)---
TIP 1: if posting/reading through U
Thanks for your response! Let me try to elaborate what I meant with my
original post.
If R is the set of words in the tsvector for a given table row and S is
the set of keywords to search for (entered by e.g. a website user) I
would like to receive all rows for which the intersection between R
Hi,
I made a small demonstration for Sylph Searcher at Linux World at SF
and was asked by Josh Berkus where he can download it. I would like to
share the info with PostgreSQL users. Here is the URL:
http://sylpheed.sraoss.jp/en/download.html#searcher
Those who are not familiar with Syph Searcher
I've installed postgresql 8.2 on a windows vista machine and are trying to
connect to it from another one.the server has ip 192.168.1.100 and the client
192.168.1.102As I understand it, I should make some configuration changes in
pg_hba.conf to make this happen.Both machines has both ipv4 and i
68 matches
Mail list logo
