Hi Tom Alvaro,
Hey,
They are all under 200 million
Weird
Could you fetch from pg_stat_activity the table it's processing, and its
pg_class row and that of its toast table (if any)?
Sorry for the delay. Required details are at
http://pastebin.com/pastebin.php?dl=fd699fbb
Did
Hi,
Why is it that
SELECT 1+null
evaluates to NULL, but
SELECT sum(foo) FROM (VALUES(1), (NULL)) AS v(foo)
evaluates to 1 ?
WBL
Willy-Bas Loos wrote:
Hi,
Why is it that
SELECT 1+null
evaluates to NULL, but
SELECT sum(foo) FROM (VALUES(1), (NULL)) AS v(foo)
evaluates to 1 ?
SUM(x) ignores null input, like COUNT(x) etc. It's the sum of all
non-null instances of x.
There's some useful explanation of the various
Greg Smith wrote:
On Thu, 7 Aug 2008, Cyril SCETBON wrote:
What's the way to count the read/write bytes of the checkpoint
process before 8.3 (no pg_stat_bgwriter view :-[ ) I want to
distinguish bytes written by checkpoints and others written by the
background process
The reason that
reproduced it on:
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3
(Ubuntu 4.2.3-2ubuntu7)
3 rows with index, 2 rows without.
can not reproduce it on:
- PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3
20070831 (prerelease) (Ubuntu 4.1.2-16ubuntu1)
-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Try:
select * from pg_proc where lower(prosrc) like '%previous_charge%';
Sim
Rob Richardson wrote:
Greetings!
Sometimes I need to track down how something happens in the database our
application relies on, but whatever's happening may be
On Mon, 2008-08-11 at 12:43 -0700, Vlad Kosilov wrote:
./data/ you may want to exclude those. I find this query useful for
something like this as well:
select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ;
Hello Vlad,
I ran your query and I got the 9Gigas!
I guess it
Hello Greg, Vlad, Scott and all,
thanks for the feedback.
O forgot to mention that I execute REINDEX on all tables and INDEXes
every week (right after executing VACUUM FULL).
Is this enough to eliminate the possibility of index bloat ?
and, yes, my database has some crazy indexes. I use
Hi,
Iam new to Postgresql, now i need to create a Dynamic SQL Query for
returning the record set
based on my Input Parameters. I looked up some of the documents and worked
out some more ...
MY Postgresql Version In Local: 7.4
MY Postgresql Version In Development: 8.2
-- DROP TYPE ORDERREPORT;
Am Thursday, 7. February 2008 schrieb Lawrence Oluyede:
PostgreSQL 8.3 instead doesn't allow the insertion of XML with doctype
in its new native data type returning this error message:
ERROR: invalid XML content
DETAIL: Entity: line 2: parser error : StartTag: invalid element name
On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
I'm finding it very strange that my pg takes 9Giga on disk but
pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
yesterday.
If you've been running VACUUM FULL, it's
Dear murali,
We use psql for our ERP software .We found CREATE TYPE is useful in creating
new data type similar to creating domain.
For eg
CREATE TYPE date_condition (
condition_id int,
from_date date,
to_datedate);
Instead ,
Hi Sathish,
Thanks for your reply.
But I have created the type to return the record set from my join query
using a stored function.
I cannot able to create a table with that details .. Since those details
will be already available from different tables.
One more thing .. I am clear
In response to Joao Ferreira gmail [EMAIL PROTECTED]:
On Mon, 2008-08-11 at 12:43 -0700, Vlad Kosilov wrote:
./data/ you may want to exclude those. I find this query useful for
something like this as well:
select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ;
On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:
It's likely you've got index bloat. If you reload a pg_dump of the
database in question into another server how much space does that take
up?
right. just loaded the dump into a clean database and everything came
down about 10 times...
Joao Ferreira gmail wrote:
On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
I'm finding it very strange that my pg takes 9Giga on disk but
pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
yesterday.
If you've been running
Joao Ferreira gmail wrote:
On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:
If I try cluster, I'm guessing I'll choose the big index and forget
about the smaller ones... is this right ?
CLUSTER will sort out all the indexes, even though you're just
clustering on on.
--
Tommy
Iam getting just the first record from the recordset
That's because you use SELECT INTO, you should use FOR rec IN query LOOP
Here's sample code from
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote:
reproduced it on:
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu7)
3 rows with index, 2 rows without.
can not reproduce it on:
- PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
I'll see if I can repro a case like it to see if the syslogger prevents
the shared mem from going away when I get back to a dev box. Should be
enough to just stick a sleep preventing it from stopping, right?
The syslogger isn't
Please understand.
I know I have to use FOR . LOOP for my query. But it is not a normal one .I
use to build that one dynamically.
From: Willy-Bas Loos [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 12, 2008 5:46 PM
To: [EMAIL PROTECTED]
Cc: Sathish Duraiswamy;
On Mon, Aug 11, 2008 at 10:35:26PM -0500, Matthew Dennis wrote:
In reference to the script below (I know it can be rewritten, that's
not the point), I get 3 rows if the referenced index exists but only
two rows if it does not. This is observable and repeatable just by
dropping/creating the
On Aug 12, 8:17 am, [EMAIL PROTECTED] (ries van Twisk) wrote:
On Aug 12, 2008, at 3:53 AM, Willy-Bas Loos wrote:
reproduced it on:
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC)
4.2.3 (Ubuntu 4.2.3-2ubuntu7)
3 rows with index, 2 rows without.
can not reproduce it
Hi,
I have changed my procedure like below,
CREATE OR REPLACE FUNCTION fun_orderreport(pmorderid integer, pmcompanyid
integer, pmeventid integer)
RETURNS SETOF orderreport AS
$BODY$
DECLARE
vResult ORDERREPORT%ROWTYPE;
vSql TEXT = '
SELECT
ORDR.ORDERSID
Hello all
[[[ while dealing with a disk size problem I realised my REINDEX cron
script was not really being called every week :( so... ]]]
I executed REINDEX by hand and the disk ocupation imediatelly dropped 6
Giga...!!!
is there a way to configure postgres to automatically execute the
Matthew Dennis [EMAIL PROTECTED] writes:
In reference to the script below (I know it can be rewritten, that's not the
point), I get 3 rows if the referenced index exists but only two rows if it
does not.
I don't see any failure in 8.3 branch tip. I think the bug was fixed
here:
you can use a cron job
--- On Tue, 8/12/08, Joao Ferreira gmail [EMAIL PROTECTED] wrote:
From: Joao Ferreira gmail [EMAIL PROTECTED]
Subject: [GENERAL] automatic REINDEX-ing
To: pgsql-general pgsql-general@postgresql.org
Date: Tuesday, August 12, 2008, 3:13 PM
Hello all
[[[ while
Joao Ferreira gmail [EMAIL PROTECTED] writes:
I executed REINDEX by hand and the disk ocupation imediatelly dropped 6
Giga...!!!
is there a way to configure postgres to automatically execute the needed
REINDEXING (on indexes and tables) for a given database
Generally speaking, there
so use EXECUTE:
CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);
CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS
$$
DECLARE
rec
Our IT administrator ran a pg_dump and received the following error:
.
.
.
pg_dump: dumping contents of table history
pg_dump: [custom archiver] WARNING: ftell mismatch with expected
position -- ftell used
pg_dump: dumping contents of table history_archive
pg_dump: [custom archiver] WARNING:
On Tue, 2008-08-12 at 11:53 -0400, Tom Lane wrote:
TW, more aggressive routine vacuuming does NOT mean use vacuum
full.
Vacuum full tends to make index bloat worse, not better.
regards, tom lane
Ok. so what does it mean ?
I'm a bit lost here. I'm currently
William Garrison [EMAIL PROTECTED] writes:
Our IT administrator ran a pg_dump and received the following error:
pg_dump: [custom archiver] WARNING: ftell mismatch with expected
position -- ftell used
What platform, and exactly what version of pg_dump? Is it possible
you ran out of disk space
On Tue, Aug 12, 2008 at 10:04 AM, Joao Ferreira gmail
[EMAIL PROTECTED] wrote:
On Tue, 2008-08-12 at 11:53 -0400, Tom Lane wrote:
TW, more aggressive routine vacuuming does NOT mean use vacuum
full.
Vacuum full tends to make index bloat worse, not better.
regards,
Tom Lane wrote:
Joao Ferreira gmail [EMAIL PROTECTED] writes:
I executed REINDEX by hand and the disk ocupation imediatelly dropped 6
Giga...!!!
is there a way to configure postgres to automatically execute the needed
REINDEXING (on indexes and tables) for a given database
On Tue, Aug 12, 2008 at 10:09 AM, William Garrison
[EMAIL PROTECTED] wrote:
Tom Lane wrote:
Joao Ferreira gmail [EMAIL PROTECTED] writes:
I executed REINDEX by hand and the disk ocupation imediatelly dropped 6
Giga...!!!
is there a way to configure postgres to automatically execute the
I'm embarrassed to say it is 8.2.3 :( I'm not sure why they haven't
upgraded our production servers to the latest 8.2 yet. It's running on
Windows Server 2003, and it looks like there is plenty of disk space.
I googled this and found someone reported defect 2461 for this, some
time ago,
Hi guys,
If found the reason for all this problem.
explanation: vacuum reindex cron scripts were not being executed.
I executed the operations by hand and the values became normal.
thank you all for the fine discussion.
joao
On Tue, 2008-08-12 at 13:49 +0200, Tommy Gildseth wrote:
Joao
In response to Joao Ferreira gmail [EMAIL PROTECTED]:
On Tue, 2008-08-12 at 11:53 -0400, Tom Lane wrote:
TW, more aggressive routine vacuuming does NOT mean use vacuum
full.
Vacuum full tends to make index bloat worse, not better.
regards, tom lane
Ok. so
Hello,
The following is the procedure to calculate the disk space occupied by
postgresql from a flat file.
In this I didn't understood some terms
24 bytes: each row header (approximate)
24 bytes: one int field and one text field
+ 4 bytes: pointer on page to
This is almost certainly the bug fixed in 8.2.4 and listed in the
release notes as:
Allow pg_dump to do binary backups larger than two gigabytes on Windows
(Magnus)
If it happens to be that your dump could approach the 2Gb limit, I
suggest you upgrade to 8.2.9 and see if it goes away.
As this
yay!
Thank you.
Magnus Hagander wrote:
This is almost certainly the bug fixed in 8.2.4 and listed in the
release notes as:
Allow pg_dump to do binary backups larger than two gigabytes on Windows
(Magnus)
If it happens to be that your dump could approach the 2Gb limit, I
I am setting up a new server and I am using OpenSuse. OpenSuse only has
8.3.1 in the repositories so I am wondering just how critical is the
need to update? I checked out the changed and there looks like a lot of
them in 8.3.2. so I am wondering if I should just install from source or
live with
William Garrison [EMAIL PROTECTED] writes:
I'm embarrassed to say it is 8.2.3 :( I'm not sure why they haven't
upgraded our production servers to the latest 8.2 yet. It's running on
Windows Server 2003, and it looks like there is plenty of disk space.
Hmm. There was an 8.2.4 bug fix for
The dump is over 3GB. So there's no question this is it. I had a
feeling this would all come down to not being on the latest version.
Thanks to both Tom and Magnus for your help.
Tom Lane wrote:
William Garrison [EMAIL PROTECTED] writes:
I'm embarrassed to say it is 8.2.3 :( I'm not
On Tue, Aug 12, 2008 at 12:51 PM, David Siebert [EMAIL PROTECTED] wrote:
I am setting up a new server and I am using OpenSuse. OpenSuse only has
8.3.1 in the repositories so I am wondering just how critical is the
need to update? I checked out the changed and there looks like a lot of
them in
Hi there ...
I recently discovered that there is a hard cap on the # of columns, being
at 1600. I also understand that it is generally unfathomable that anyone
would ever feel limited by that number ... however I've managed to bump
into it myself and was looking to see if anyone had advice on
On Aug 12, 2008, at 1:15 PM, Jeff Gentry wrote:
Hi there ...
I recently discovered that there is a hard cap on the # of columns,
being
at 1600. I also understand that it is generally unfathomable that
anyone
would ever feel limited by that number ... however I've managed to
bump
into
On Tue, Aug 12, 2008 at 2:15 PM, Jeff Gentry [EMAIL PROTECTED] wrote:
Hi there ...
I recently discovered that there is a hard cap on the # of columns, being
at 1600. I also understand that it is generally unfathomable that anyone
would ever feel limited by that number ... however I've
I do agree and really like Centos but I don't want to have to have to
admin this box myself. Our network admin likes OpenSuse and doesn't want
to have to deal with anything else.
I tried Ubuntu server a while ago and was really not impressed. It was
lacking a lot of packages that I wanted but
On Tue, 12 Aug 2008, Steve Atkins wrote:
What operations do you perform on the data? If it's just store and
retrieve, can you serialize them into a bytea (or xml) field?
Store retrieve although we take advantage of the fact that it's in a DB
to allow for subsetting (done at the postgres
On Tue, 12 Aug 2008, Scott Marlowe wrote:
The generic solution without making too much work is to store similar
data types in an arrayed type in the db.
That's a good idea. I'll have to play w/ this one. Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
On Tue, Aug 12, 2008 at 3:03 PM, David Siebert [EMAIL PROTECTED] wrote:
I do agree and really like Centos but I don't want to have to have to
admin this box myself. Our network admin likes OpenSuse and doesn't want
to have to deal with anything else.
I've found that adminning a dedicated pgsql
On Aug 12, 2008, at 2:11 PM, Jeff Gentry wrote:
On Tue, 12 Aug 2008, Steve Atkins wrote:
What operations do you perform on the data? If it's just store and
retrieve, can you serialize them into a bytea (or xml) field?
Store retrieve although we take advantage of the fact that it's in
a
Here is the solution about on the fly ALTER ENUM:
http://en.dklab.ru/lib/dklab_postgresql_enum/
Usage:
*-- Add a new element to the ENUM on the fly.
SELECT enum.enum_add('my_enum', 'third');*
*-- Remove an element from the ENUM on the fly.
SELECT enum.enum_del('my_enum', 'first');*
Possibly
On Tue, Aug 12, 2008 at 5:40 PM, Dmitry Koterov [EMAIL PROTECTED] wrote:
Here is the solution about on the fly ALTER ENUM:
http://en.dklab.ru/lib/dklab_postgresql_enum/
Usage:
-- Add a new element to the ENUM on the fly.
SELECT enum.enum_add('my_enum', 'third');
-- Remove an element from
If I have a primary key constraint defined in the database do I also
need to create an index on that field for fast lookup?
The documentation on the web seems to imply that the contraint is not
an index. Is that right?
What the difference between creating a unique, not null index and
setting a
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Tim Uckun
Sent: Tuesday, August 12, 2008 7:18 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Is the primary key constraint also an index?
If I have a primary key constraint
Tim Uckun wrote:
If I have a primary key constraint defined in the database do I also
need to create an index on that field for fast lookup?
No. Declaring field(s) as the primary key automatically adds a UNIQUE
constraint on those fields. PostgreSQL implements unique constraints
using a
On Tue, 2008-08-12 at 08:38 -0700, Lennin Caro wrote:
you can use a cron job
I have my cron setup to do database wide vacuums each night and it
usually takes ~between 4-6 hours on ~200G DB size.
On days where there is huge activity, it can drag on for like 15+ hours.
I've recently dropped
Murali,
Tried the same method using FOR --LOOP with EXECUTE command similar function
you described and got the same error message.
When i used raise info to check the function , i get the set of records as
result .But finally , it throws same error
Someone can help on this issue..
Regrds
61 matches
Mail list logo
