Hi,
We are currently having a problem with one of our tables containing far
too many dead rows. The table in question will have a few hundred
thousand inserts and deletes per day and usually builds up quite a large
dead row count that starts to affect the performance of the queries
select from
roopa perumalraja wrote:
Hi all,
As I am inserting 100million rows daily into partitioned tables
(daily wise), it is getting slower.
What is - the inserts? By how much? What tables? What indexes? How
are you inserting these rows?
I take my words back as 100million rows. The insert of
Jan Mura wrote:
Hello,
I would like to ask for a hint for a good SQL textbook. I don't want just a
reference manual but real textbook with a theoretical background so I could
to design my databases following the general rules as normal forms and so
on.
Try An Introduction to Database Systems
garry saddington wrote:
I have this query which essentially returns records that exist in one
table (studentclass) and not another (effortandattainment) to check when
teachers have entered their student grades.
select
from studentclass left join effortandattainment
and
Malcolm McLean wrote:
Hi,
We are currently having a problem with one of our tables containing far
too many dead rows. The table in question will have a few hundred
thousand inserts and deletes per day and usually builds up quite a large
dead row count that starts to affect the performance of
Richard Huxton wrote:
What is causing those dead rows to not get cleared even by a full
vacuum? Is there any way keep them low without having to run a
cluster
command as that is a locking statement and requires me to close all
java
applications that are connecting to that table before running
I was on here a couple of days ago with problems getting relational
data *into* some array variables (now solved thank you), but now I'm
here with problems getting data *out* again.
A week or so ago I did a little test page with a little test table
pulled from some example about postgres arrays.
On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote:
Something like this will help you:
execute immediate 'create temporary table test (a number) on commit drop';
PL/pgSQL doesn't recognize immediate and number isn't a PostgreSQL
type so the above yields a syntax error. Also, EXECUTE
Agreed :) I guess missed out some details from there as I just thought he
needed to drop a temp table inside a function like this:
CREATE OR REPLACE function tempfunc (int) returns int
AS
$$
begin
execute 'create temporary table test (a numeric) on commit drop';
execute 'INSERT INTO test
Malcolm McLean wrote:
Richard Huxton wrote:
What is causing those dead rows to not get cleared even by a full
vacuum? Is there any way keep them low without having to run a
cluster
command as that is a locking statement and requires me to close all
java
applications that are connecting to
Richard Huxton wrote:
Malcolm McLean wrote:
I tested this theory by stopping java applications that were
connected
to the database and all other connections that were using
transactions
and the full vacuum was still unable to remove the dead rows.
What I'm still wondering about, is why the
Malcolm McLean wrote:
Richard Huxton wrote:
Malcolm McLean wrote:
I tested this theory by stopping java applications that were
connected
to the database and all other connections that were using
transactions
and the full vacuum was still unable to remove the dead rows.
What I'm still
Richard Huxton dev@archonet.com writes:
Well, both cluster and vacuum full will require a lock on the table. But
they're more or less doing the same thing, so why the one should work
and the other not I don't know.
CLUSTER isn't MVCC-safe --- it'll drop rows that are committed dead
even if
OK I reread the array documentation and something that didn't stand
out before now does :).
By default, the lower bound index value of an array's dimensions is
set to one. If any of an array's dimensions has a lower bound index
not equal to one, an additional decoration that indicates the actual
Hi,
I am trying to write a script which will migrate all the data from a
particular database on version 7.2.1 and restore the same data along
with the full schema on an 8.1.5 database. The database itself will be
created and does not need to be created.
I tried dumping data in tar format using
Arindam [EMAIL PROTECTED] writes:
I tried dumping data in tar format using the pg_dump utility (with
version 7.2.1) and restoring it using pg_restore utility (with version
8.1.5). But on running this command:
pg_restore -d mydb -C -u my_dump_file
I get the following error:
pg_restore:
-
PostgreSQL New RPM Sets
2007-02-08
Versions: 8.2.3, 8.1.8, 8.0.12
Set label: 8.2.3-1PGDG, 8.1.8-1PGDG, 8.0.12-1PGDG
-
I'm looking at running PG under Red Hat AS, but within a virtual
machine. The host will be a Windows 2003 Enterprise server running
Windows Virtual Machine 2005 Enterprise. From the archives it seems
that people are doing this type of thing successfully, but I just wanted
to see if anyone had
On 2/8/07, Tom Lane [EMAIL PROTECTED] wrote:
Arindam [EMAIL PROTECTED] writes:
I tried dumping data in tar format using the pg_dump utility (with
version 7.2.1) and restoring it using pg_restore utility (with version
8.1.5). But on running this command:
pg_restore -d mydb -C -u my_dump_file
Arindam [EMAIL PROTECTED] writes:
On 2/8/07, Tom Lane [EMAIL PROTECTED] wrote:
Now you *should* be using the newer pg_dump to dump from the old
database --- that's considered good practice. But you need an
up-to-date pg_restore to read its output.
Well this time I got it dumped using 8.1.5
On 2/8/07, Tom Lane [EMAIL PROTECTED] wrote:
Arindam [EMAIL PROTECTED] writes:
On 2/8/07, Tom Lane [EMAIL PROTECTED] wrote:
Now you *should* be using the newer pg_dump to dump from the old
database --- that's considered good practice. But you need an
up-to-date pg_restore to read its
Matt Miller wrote:
I'm looking at running PG under Red Hat AS, but within a virtual
machine. The host will be a Windows 2003 Enterprise server running
Windows Virtual Machine 2005 Enterprise. From the archives it seems
that people are doing this type of thing successfully, but I just wanted
to
Hello,
I have installed Postgres 8.2 on a internal server having Windows Server
2003 (IIS 6) up and running.
- I have configure the hp_config file to: host
all 0.0.0.0./0
md5
- I have change the listening address to '*' in the postgres.conf file
- No Firewall
Hi,
I'm using postgresql 7.4. If I execute SQL Select statement, then the
records fold around lines.
I would like to turn this off, so that lines do not fold.
I know the option in less, which one has to set to -S. I have
exported the variable and it works with textfiles.
However, if I use psql
Richard Huxton wrote:
Jim C. wrote:
You probably want one of the mysql converter projects, e.g.
http://pgfoundry.org/projects/mysql2pgsql/
Also read the converting from other databases section here:
http://www.postgresql.org/docs/techdocs
I tried several conversion tools and did get
Hello,
I know this is probably a borderline Linux question but I think
someone with PostgreSQL expertise will better be able to answer it (I
already struck out on the Linux side and after much searching on the
Web).
I installed Postgres 8.1 on Ubuntu 6.10 using Synaptic. Postgres is
listed under
On Feb 8, 8:54 am, Hannes Dorbath [EMAIL PROTECTED] wrote:
On 07.02.2007 17:59, [EMAIL PROTECTED] wrote:
Is there any form of manual for DBI-link out there?
Any link is greatly appreciated!
I think all available documentation comes with it in various README
files. Do you have a specific
I installed 8.2 server on Windows XP
Today every instert to table causes error:
ERROR: attribute 13 has wrong type
DETAIL: Table has type character, but query expects character.
Any idea how to fix it ?
Andrus.
---(end of broadcast)---
TIP
Paul Lambert wrote:
Are
there any companies in the great land of Australia that offer Postgres
training courses?
I see a number listed for around the US on the postgresql.org website -
just curious if anything similar goes on down under.
Cheers,
P.
Fujitsu
--
Cheers
Tom Lane wrote:
Michael Guerin [EMAIL PROTECTED] writes:
Hmm, that makes it sound like a plain old data-corruption problem, ie,
trashed xmin or xmax in some tuple header. Can you do a select
count(*) from this table without getting the error?
no, select count(*) fails around 25
Michael Guerin [EMAIL PROTECTED] writes:
Ok, so I'm trying to track down the rows now (big table slow queries :(
) How does one zero out a corrupt row, plain delete? I see references
for creating the missing pg_clog file but I don't believe that's what
you're suggesting..
Zeroing out the
Andrus wrote:
I installed 8.2 server on Windows XP
Today every instert to table causes error:
ERROR: attribute 13 has wrong type
DETAIL: Table has type character, but query expects character.
Any idea how to fix it ?
You need to upgrade to 8.2.3. That was a rush release to fix a bug
am Wed, dem 07.02.2007, um 11:28:56 -0500 mailte Nicolas Gignac folgendes:
Hello,
I have installed Postgres 8.2 on a internal server having Windows Server 2003
(IIS 6) up and running.
- I have configure the hp_config file to: host
all
Nicolas Gignac wrote:
Hello,
I have installed Postgres 8.2 on a internal server having Windows Server
2003 (IIS 6) up and running.
- I have configure the hp_config file to: host
all 0.0.0.0./0
md5
Don't you mean pg_hba.conf?
- I have change the listening address
Mike wrote:
Hello,
I know this is probably a borderline Linux question but I think
someone with PostgreSQL expertise will better be able to answer it (I
already struck out on the Linux side and after much searching on the
Web).
I installed Postgres 8.1 on Ubuntu 6.10 using Synaptic. Postgres
Andrus [EMAIL PROTECTED] writes:
I installed 8.2 server on Windows XP
Today every instert to table causes error:
ERROR: attribute 13 has wrong type
DETAIL: Table has type character, but query expects character.
Any idea how to fix it ?
Update to 8.2.3.
regards,
Zeroing out the whole block containing it is the usual recipe. I forget
the exact command but if you trawl the archives for mention of dd and
/dev/zero you'll probably find it. Keep in mind you want to stop the
postmaster first, to ensure it doesn't have a copy of the bad block
cached in
Nicolas Gignac wrote:
I have installed Postgres 8.2 on a internal server having Windows
Server 2003 (IIS 6) up and running.
- I have configure the hp_config file to: host
all
0.0.0.0./0md5
^
Michael Guerin [EMAIL PROTECTED] writes:
You're suggesting to zero out the block in the underlying table files,
or creating the missing pg_clog file and start filling with zero's?
The former. Making up clog data is unlikely to help --- the bad xmin is
just the first symptom of what's probably
Zeroing out the whole block containing it is the usual recipe.
Something like this worked for me in the past:
% dd bs=8k count=X /dev/zero clog-file
I had to calculate X, because I usually had a situation with truncated
clog-file, and a failed attempt to read it from offset XYZ.
And I
Jan,
There are a lot of resources available. Each database program has a different
dialect of SQL, and I find the PostgreSQL on-line manual has an excellent
introduction of SQL and even some theoretical issues. I would start there (its
free and directly applicable to using PostgreSQL).
- Original Message -
From: Michael Schmidt
To: Jan Mura ; pgsql-general@postgresql.org
Sent: Thursday, February 08, 2007 12:54 PM
Subject: Re: [GENERAL] SQL textbook
Many books are available for download. Joe Celko is a well respected
author and just about anything with his name on
On Thu, 8 Feb 2007, Michael Schmidt wrote:
There are a lot of resources available. Each database program has a
different dialect of SQL, and I find the PostgreSQL on-line manual has an
excellent introduction of SQL and even some theoretical issues. I would
start there (its free and directly
On Thu, 8 Feb 2007, Ted Byers wrote:
Do you have any URLs for the books that are available for download?
Google is your friend.
Rich
--
Richard B. Shepard, Ph.D. |The Environmental Permitting
Applied Ecosystem Services, Inc.| Accelerator(TM)
Ted,
Google the publisher's name to get to its site.
mike
- Original Message -
From: Ted Byersmailto:[EMAIL PROTECTED]
To: Michael Schmidtmailto:[EMAIL PROTECTED] ;
pgsql-general@postgresql.orgmailto:pgsql-general@postgresql.org
Sent: Thursday, February 08, 2007 11:05 AM
Running 8.1.3 on FreeBSD 6.0 server and got the following (I'm most
interested in the PANIC entry):
2007-02-06 17:15:11 CST LOG: archived transaction log file
000104A30025
2007-02-06 17:15:27 CST LOG: archived transaction log file
000104A30026
2007-02-06 17:15:39
On Feb 7, 2007, at 10:05 AM, [EMAIL PROTECTED] wrote:
I was using the flag table to keep the flags consistent between all
the tables in the database that might use them. I didn't know about
CREATE DOMAIN which will do what I want perfectly
Note that DOMAIN support unfortunately isn't
Jim Nasby wrote:
BTW, 8.2.2 has a rather nasty bug; you'll want to go grab 8.2.3 or
8.2.1.
You don't want 8.2.1 either because it has a nasty security bug.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development,
andy rost [EMAIL PROTECTED] writes:
Running 8.1.3 on FreeBSD 6.0 server and got the following (I'm most
interested in the PANIC entry):
2007-02-06 17:22:22 CST PANIC: right sibling's left-link doesn't match
Any advice?
Update to 8.1.8. I'm not sure about this specific symptom, but I know
Jim Nasby [EMAIL PROTECTED] writes:
Note that DOMAIN support unfortunately isn't perfect; for example,
plpgsql doesn't enforce domain constraints (IIRC there's some other
bugs as well).
Fixed in 8.2 ... not that there aren't necessarily bugs left, but the
above as a blanket statement is
I need to add a system to our postgres pg_hba.conf file; the main server went
down a few days ago, and this system was mistakenly left off the list of
allowed systems when the server was brought back up. (Older version of the
.conf file; I managed to accidentally delete the more current one
Jim Nasby [EMAIL PROTECTED] wrote on 02/08/2007 12:12:00 PM:
Also, I suggest using char instead of just char. char is a
special data type that's limited to storing a single character; the
advantage is that it's much smaller and faster than a char.
If you do end up back at using foreign
Andrew Edson [EMAIL PROTECTED] writes:
Someone else added the IP address for the other system to the
pg_hba.conf file later, but since we had already started postgres by
then, it didn't take.
You don't need to shut down the server to adjust pg_hba.conf ... just
pg_ctl reload.
And now,
Andrew Edson wrote:
I need to add a system to our postgres pg_hba.conf file; the main server
went down a few days ago, and this system was mistakenly left off the
list of allowed systems when the server was brought back up. (Older
version of the .conf file; I managed to accidentally delete
On Thu, 8 Feb 2007, Andrew Edson wrote:
snip
Someone else added the IP address for the other system to the
pg_hba.conf file later, but since we had already started postgres by
then, it didn't take. And now, for some reason, postgres doesn't seem
to want to let me shut down.
Try telling
Okay, for some reason we still had a problem connecting after a pg_ctl reload,
but the shutdown went through this time, and everything's working now. Thanks
for the assistance.
Tom Lane [EMAIL PROTECTED] wrote: Andrew Edson writes:
Someone else added the IP address for the other system to
[EMAIL PROTECTED] wrote:
Jim Nasby [EMAIL PROTECTED] wrote on 02/08/2007 12:12:00 PM:
If you do end up back at using foreign keys, I suggest using either a
smallint or char... the savings across the number of fields you're
looking at would start to add up, especially if you start putting
Alvaro Herrera [EMAIL PROTECTED] wrote on 02/08/2007 01:58:20
PM:
The char type (including quotes) is a very different animal from all
those character types the manual you quote is talking about. char is
a single byte, while varchar(1) and all the rest are a single character,
meaning there
[EMAIL PROTECTED] wrote:
Using PGADMINIII I added a column of this type to my database but when I
try to update it I get an error.
column:
ALTER TABLE datatype ADD COLUMN charflag char[];
You added an array of char, which is not the same.
--
Alvaro Herrera
Alvaro Herrera [EMAIL PROTECTED] wrote on 02/08/2007 02:51:52
PM:
[EMAIL PROTECTED] wrote:
Using PGADMINIII I added a column of this type to my database but when
I
try to update it I get an error.
column:
ALTER TABLE datatype ADD COLUMN charflag char[];
You added an array of
Hello,
we have one table in database (db has over 200 tables), which has one
blob filed for storing user photos. We are making backup every night, but
it is now too large because of blobs. We do not need to make backup of
photos every night, but the question is how to backup database without
On 2/9/07, Tom Lane [EMAIL PROTECTED] wrote:
Jim Nasby [EMAIL PROTECTED] writes:
Note that DOMAIN support unfortunately isn't perfect; for example,
plpgsql doesn't enforce domain constraints (IIRC there's some other
bugs as well).
Fixed in 8.2 ... not that there aren't necessarily bugs left,
I need to convert my database to UTF8. Is there a way to do a SELECT ...
INSERT from the old database table to the new one? Would the INSERT
correct data errors between the two data types? I only have 10 tables and
the biggest has 8000 rows.
Running Version 8.1.4 on Redhat 9
*** *** *** ***
2007/2/8, [EMAIL PROTECTED] [EMAIL PROTECTED]:
I need to convert my database to UTF8. Is there a way to do a SELECT ...
INSERT from the old database table to the new one? Would the INSERT correct
data errors between the two data types? I only have 10 tables and the
biggest has 8000 rows.
Use
On 2/8/07, Clodoaldo [EMAIL PROTECTED] wrote:
Use pg_dump to dump the db and use iconv on the generated file:
iconv -f ASCII -t UTF-8 mydb.dump -o mydb_utf8.dump
Wouldn't it be adequate to set the client encoding to SQL_ASCII in the dump
file (if that was infact the encoding on the original
Hi all,
I've got a 'history' schema that records changes in the public schema
tables over time. I use a trigger and function to do this. What I would
like to do though, and this may not even be possible, is say something
like (pseudo-code) SELECT DIFF foo_name FROM history.foo WHERE
foo_id=X;
On Thu, Feb 08, 2007 at 08:22:40PM -0500, Chad Wagner wrote:
On 2/8/07, Clodoaldo [EMAIL PROTECTED] wrote:
Use pg_dump to dump the db and use iconv on the generated file:
iconv -f ASCII -t UTF-8 mydb.dump -o mydb_utf8.dump
Converting the data from ASCII to UTF-8 doesn't make much sense:
if
Hi all,
Saturday I changed a table to add a varchar(24) and a TEXT column.
It's used for some reporting purposes (small potatoe stuff really)
and the TEXT column remains mostly empty. However, this week
performance has gotten terrible. Queries joining against the
aforementioned table
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/08/07 20:50, Arturo Perez wrote:
Hi all,
Saturday I changed a table to add a varchar(24) and a TEXT column. It's
used for some reporting purposes (small potatoe stuff really) and the
TEXT column remains mostly empty. However, this week
69 matches
Mail list logo
