[I'm not going to even try to work out that mess to quote it]
The following works for me. You can even do it without dynamic sql (see
fun_orderreport1).
begin;
-- dummy up some tables for self contained example
create table orders (ordersid int, initiated date, company int, event int);
create
aravind chandu wrote:
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: poin
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
sathis
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
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 unique
> -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 constra
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 pr
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
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');*
Possib
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 D
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 p
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, 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 leve
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 that
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
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 i
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 how
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
> th
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 s
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
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 t
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
sugge
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
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 tup
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, to
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
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, but
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 postg
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: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.
>>
>>
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 s
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 curre
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: fte
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 R
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
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"
> Date: Tuesday, August 12, 2008, 3:13 PM
> Hello all
>
> [[[ while dealing with a disk si
"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:
http://a
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 needed
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
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 n
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
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; pgsql-general@postgresql.
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 i
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
>Iam getting just the first record from the recordset
That's because you use SELECT INTO, you should use FOR rec IN 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 test
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 Gildse
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 VACUU
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.
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_databas
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
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 , yo
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,
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
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;
C
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 the
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
-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 b
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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)"
-
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 view
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 va
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
62 matches
Mail list logo
