Re: [GENERAL] recovery after interrupt in the middle of a previous recovery

[Or Kroyzer]

Thanks.

2010/5/26 Tom Lane 

> Or Kroyzer  writes:
> > I am using postgres 8.3.1,
>
> ... you really ought to be using 8.3.something-recent ...
>
> > and have implemented warm standby very much like
> > the one described in the high availability documentation on this site.
> > It seems to work well except for this problem: I've had a case where the
> > postgresql server was interrupted while in recovery (I think it was a
> user
> > interrupt, the log sais:
>
> >  . LOG:  received fast shutdown request
> > LOG:  archive recovery complete
> > FATAL:  terminating connection due to administrator command
> > LOG:  startup process (PID 6033) exited with exit code 1
> > LOG:  aborting startup due to startup process failure
>
> > And after that, pg doesn't go through the recovery script provided in
> > recovery.conf, and doesn't manage to come up. The log sais:
>
> > LOG:  database system was interrupted while in recovery at log time
> > 2010-05-26 02:00:03 IDT
> > HINT:  If this has occurred more than once some data might be corrupted
> and
> > you might need to choose an earlier recovery target.
> > LOG:  could not open file "pg_xlog/00CA000A006D" (log file
> 10,
> > segment 109): No such file or directory
> > LOG:  invalid primary checkpoint record
> > LOG:  could not open file "pg_xlog/00CA000A006D" (log file
> 10,
> > segment 109): No such file or directory
> > LOG:  invalid secondary checkpoint record
> > PANIC:  could not locate a valid checkpoint record
> > LOG:  startup process (PID 8081) was terminated by signal 6: Aborted
> > LOG:  aborting startup due to startup process failure
>
> Hmm.  Try putting back your recovery.conf file --- it will have been
> renamed at the point where "archive recovery complete" was printed.
> This example suggests that we might be doing that too early.
>
>regards, tom lane
>

Re: [GENERAL] Auto vacuum configuration in postgres.

[Nilesh Govindarajan]

On Mon, May 24, 2010 at 6:23 PM, venu madhav  wrote:
> Hi All,
>       In my application we are using postgres which runs on an
> embedded box. I have configured autovacuum to run once for every one
> hour. It has 5 different databases in it. When I saw the log messages,
> I found that it is running autovacuum on one database every hour. As a
> result, on my database autovacuum is run once in 5 hours. Is that an
> expected behavior, Is there any way to make it run it every hour.
>
>
> Thank you,
> Venu
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Increase autovacuum workers. More the workers, the job will be done
parallely. Anybody with more knowledge is requested to correct me if
I'm wrong.

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

[Nilesh Govindarajan]

On Tue, May 25, 2010 at 7:48 PM, Gary Fu  wrote:
> Hi,
>
> On my mandriva linux, I don't have problem to build pgpool 2.3.3 with
> postgresql 8.4.2.  But when I upgraded to 8.4.4, I cannot build pgpool again
> due to the libdl.so is not required in libpq.so (from the ldd command).  Do
> you know how to build the 8.4.4 so that libdl.so is required in libpq.so ?
>
> 10:16am 32 g...@nppdist:~/postgres/postgresql-8.4.4> locate libdl
> /lib/libdl-2.6.1.so
> /lib/libdl.so.2
>
> 10:16am 33 g...@nppdist:~/postgres/postgresql-8.4.4> ldd
> ./src/interfaces/libpq/libpq.so
>        linux-gate.so.1 =>  (0xe000)
>        libcrypt.so.1 => /lib/libcrypt.so.1 (0x40026000)
>        libc.so.6 => /lib/i686/libc.so.6 (0x40054000)
>        /lib/ld-linux.so.2 (0x8000)
>
>
> Thanks,
> Gary
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Could you please explain a little more what you want to do actually ?

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Enforcing unique column with triggers and hash

[Data Growth Pty Ltd]

I have a large table (200 million rows) with a column ( 'url' character
varying(255)) that I need to be unique.

Currently I do this via a UNIQUE btree index on (lower(url::text))

The index is huge, and I would like to make it much smaller.  Accesses to
the table via this key are a tiny portion of the total (<1%), mainly being
INSERTs which are not time critical.  SELECTs very rarely use this column in
WHERE, and never for time-critical queries.

I would also like to partition the table, but this column and index is not a
good choice for the partitioning criteria.

I thought I might be able to create another column ('url_hash'), being a say
64-bit hash of lower(url::text) with a UNIQUE constraint.  64 bits should
give me 36 bits of randomness over my 2^28 rows, making the probability of a
false match sufficiently rare (probably much rarer than the risk of a
programming bug causing a false match).

Alternatively, I could use an even shorter hash (say 32 bits), and allow for
the possibility of hash collisions.

Does anybody know of any reference to using a hash in postgresql as a
substitute for a unique btree?  I would like to avoid re-inventing the wheel
if possible.

Stephen

Re: [GENERAL] conditional rules VS 1 unconditional rule with multiple commands?

[Alban Hertroys]

On 27 May 2010, at 12:36, Davor J. wrote:

> I just wonder whether the two are equivalent from user perspective: As far 
> as I see, you can always rewrite a multi-command rule as a conditional rule 
> and vice versa. Further more, Postgres seems to execute all the conditional 
> rules, just as if it would execute all the commands in the one unconditional 
> rule. So, I just wonder if there is any use in using conditionals in rules 
> over one multi-command rule?


I think if you want any meaningful answers you should start by explaining what 
exactly you mean by . An example would help.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bff0ec010411146380094!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Download

[Devrim GÜNDÜZ]

On Thu, 2010-05-27 at 10:44 +0200, Faiyaz Allie wrote:
> 
> I would like to find out where I can download Postgresql 8.1.7 rpm for
> Redhat 5.5. I'm trying to load an application that requires it 

I'm not sure that you will be able to find 8.1.7 around. 8.1.21 is the
latest on 8.1, and I think your app will work against it, too.

If you still need 8.1.17:

ftp://ftp-archives.postgresql.org/pub/source/v8.1.7/postgresql-8.1.7.tar.bz2

Download this first. Then, checkout this one:

http://svn.pgrpms.org/repo/rpm/redhat/8.1/postgresql/EL-5/

Edit spec file, and go back to 8.1.7 -- and build your own RPM.

-HTH.
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] EXECUTE and FOUND

[Pavel Stehule]

2010/5/25 Bogdan Gusiev :
> I am not sure if EXECUTE 'SELECT * FROM table" correctly sets FOUND
> variable.
>

no - it doesn't it

> EXECUTE 'SELECT * FROM ' || quote_ident(stmt.tablename) || ' limit 1';
> if found then
>  
> end if;
>
> Is there other way to check if EXECUTE 'SELECT ...' found something or
> not?
>

yes - see on GET DIAGNOSTICS statement in plpgsql docs

regards

Pavel Stehule

> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] No lidbl.so in libpq.so (postgresql 8.4.4)

[Gary Fu]

Hi,

On my mandriva linux, I don't have problem to build pgpool 2.3.3 with 
postgresql 8.4.2.  But when I upgraded to 8.4.4, I cannot build pgpool 
again due to the libdl.so is not required in libpq.so (from the ldd 
command).  Do you know how to build the 8.4.4 so that libdl.so is 
required in libpq.so ?


10:16am 32 g...@nppdist:~/postgres/postgresql-8.4.4> locate libdl
/lib/libdl-2.6.1.so
/lib/libdl.so.2

10:16am 33 g...@nppdist:~/postgres/postgresql-8.4.4> ldd 
./src/interfaces/libpq/libpq.so

linux-gate.so.1 =>  (0xe000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x40026000)
libc.so.6 => /lib/i686/libc.so.6 (0x40054000)
/lib/ld-linux.so.2 (0x8000)


Thanks,
Gary

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] UPDATE ... RETURNING atomicity

[Lew]

On 05/23/2010 02:15 AM, rihad wrote:

In this query:
UPDATE foo
SET allocated_to=?
WHERE id=(SELECT MIN(id) FROM foo WHERE allocated_to IS NULL)
AND allocated_to IS NULL
RETURNING id


Isn't the "AND allocated_to IS NULL" clause redundant?

--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] EXECUTE and FOUND

[Bogdan Gusiev]

I am not sure if EXECUTE 'SELECT * FROM table" correctly sets FOUND
variable.

EXECUTE 'SELECT * FROM ' || quote_ident(stmt.tablename) || ' limit 1';
if found then
  
end if;

Is there other way to check if EXECUTE 'SELECT ...' found something or
not?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 110,000,000 rows

[Torsten Zühlsdorff]

John Gage schrieb:

Herbert Simon must be spinning in his grave...or smiling wisely.  What 
does a human do with a petabyte of data?


for example i have a private search-engine for my most often used sites. 
google and the other ones always know just a part of the whole site, my 
own one knowns all. its a good research-tool (and mirror) and support a 
lot more filter-posibilities than google. there are many great internet 
sites out there, which have no search. after waiting for crawling this 
is no longer a problem for me.


another big example in my private use is a neural network for figuring 
out relations between news and stock-prices. or statistical data of 
website usage. oh - analyse of the behavior of google is also a great 
fun with much data. or a database for typical games like chess or poker 
or something like this. i also have some databases with geo-data or free 
avaiable data like statistics about birthnumbers in germany, a list of 
all germany citys with its habitants (grouped by gender) and so on.


or calculating a list of prim-numbers on your own just to make some 
implementation tests. sometime this databases just grow because you want 
to see how long it can take to get x results and forgot to disable the 
test after reaching the border :D


But when a desktop machine for $1700 retail has a terabyte of storage, 
the unix operating system, 4 gigs of memory, and an amazing 27 inch 
display, I guess hardware isn't the problem (and I know one could put 
together the same machine on Linux etc. for much less).


yes and for private use you can use such a desktop machine as 
database-server. it can work while you're on work ;)


Greetings from Germany,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] conditional rules VS 1 unconditional rule with multiple commands?

[Davor J.]

I just wonder whether the two are equivalent from user perspective: As far 
as I see, you can always rewrite a multi-command rule as a conditional rule 
and vice versa. Further more, Postgres seems to execute all the conditional 
rules, just as if it would execute all the commands in the one unconditional 
rule. So, I just wonder if there is any use in using conditionals in rules 
over one multi-command rule?

Regards,
Davor 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Auto vacuum configuration in postgres.

[venu madhav]

Hi All,
   In my application we are using postgres which runs on an
embedded box. I have configured autovacuum to run once for every one
hour. It has 5 different databases in it. When I saw the log messages,
I found that it is running autovacuum on one database every hour. As a
result, on my database autovacuum is run once in 5 hours. Is that an
expected behavior, Is there any way to make it run it every hour.


Thank you,
Venu

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 110,000,000 rows

[Torsten Zühlsdorff]

John Gage schrieb:

Please forgive this intrusion, and please ignore it, but how many 
applications out there have 110,000,000 row tables?  I recently 
multiplied 85,000 by 1,400 and said now way Jose.


I have two private applications with about 250,000,000 rows a table. I 
could cluster them, but the performance is good enough.


Greetings from Germany,
Torsten

--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] UPDATE...RETURNING col INTO var - what is returned?

[Alban Hertroys]

On 27 May 2010, at 18:12, Rainer Pruy wrote:

> Hi all,
> I just got quite confused on the exact semantics
> of "RETURNING expressions INTO target" with an update statement.
> 
> And while trying to resolve failed to find an answer in the  docs.
> 
> Question was whether - in case "expression" is involving a column that is 
> assigned by the update itself -
> the value returned is based on the "old" value or the "new" value".
> 
> While I'm no back to assuming is has to be the "old" one,
> I did not find a reference to that fact with the docs.

I'm pretty sure it's the new value, since that's what INSERT...RETURNING 
returns - very convenient if the value you inserted was generated somehow (by a 
sequence for example).
Since you can also UPDATE some column using a generated value, it'd make sense 
if it would behave the same way.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4bfea81210411699814628!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] UPDATE...RETURNING col INTO var - what is returned?

[Rainer Pruy]

Hi all,
I just got quite confused on the exact semantics
of "RETURNING expressions INTO target" with an update statement.

And while trying to resolve failed to find an answer in the  docs.

Question was whether - in case "expression" is involving a column that is 
assigned by the update itself -
the value returned is based on the "old" value or the "new" value".

While I'm no back to assuming is has to be the "old" one,
I did not find a reference to that fact with the docs.

Where should I have looked?
Or is it actually missing?
Then, could one add this, e.g. to the description of RETURNING .. INTO 
(Executing a query with a single row result)
just for clarification, to dispel doubts.


Thanks
Rainer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Tim Landscheidt]

Nikolas Everett  wrote:

> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
> NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '12 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval
> '11 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '10 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval
> '9 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '8 hours');


> I want to write a query that spits out:
>  state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'

> Standard grouping destroys the third row so that's out.  No grouping at all
> gives repeats of state1.  Is this what partitioning is for?

Partitioning usually means splitting data across several
tables for faster access which is probably not what you want
here.

  A simple solution would be to use LAG() and discard rows
where the current value is equal to the preceding value:

| SELECT state1, timestamp
|   FROM
| (SELECT id,
| state1,
| state2,
| LAG(state1) OVER (ORDER BY timestamp) AS prevstate1,
| timestamp FROM test) AS SubQuery
|   WHERE state1 IS DISTINCT FROM prevstate1
|   ORDER BY timestamp;

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[erobles]

On 05/27/2010 10:29 AM, Craig Ringer wrote:

On 27/05/2010 10:42 PM, erobles wrote:

hi,
i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7


Possibly stupid question: Why?

Do you need the Pg server to run on SCO OpenServer?

Yes,  i need  it  :-P

Before i have running pg 7.2 after we migrate to 8.3.1   so i  want to 
compile the newest postgres , which has all the fixes until now.





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

Got it:
SELECT state1, timestamp
   FROM (SELECT state1, timestamp, lag(state1) OVER (ORDER BY timestamp)
FROM test) as foo
 WHERE state1 != lag OR lag IS NULL
ORDER BY timestamp;
 state1 | timestamp
+
  1 | now() - interval '12 hours'
  2 | now() - interval '9 hours'
  1 | now() - interval '8 hours'

Without lag IS NULL I miss the first row.

On Thu, May 27, 2010 at 11:44 AM, Nikolas Everett  wrote:

> The 10 and 11 hour interval are being skipped because I'm only interested
> in the transitions of state 1. State 1 only transitioned three times at now
> - 12, now - 9 and now - 8.
>
> The table has both transitions in it because I frequently care about them
> both together.  I just don't in this case.
>
>
> On Thu, May 27, 2010 at 12:36 PM, Justin Graf  wrote:
>
>>  On 5/27/2010 9:45 AM, Nikolas Everett wrote:
>>
>> Sorry.  Here is the setup:
>> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2
>> INT NOT NULL, timestamp TIMESTAMP);
>>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '12 hours');
>>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() -
>> interval '11 hours');
>>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '10 hours');
>>  INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() -
>> interval '9 hours');
>>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
>> interval '8 hours');
>>
>>
>>  I want to write a query that spits out:
>>   state1 | timestamp
>> +
>>   1 | now() - interval '12 hours'
>>   2 | now() - interval '9 hours'
>>   1 | now() - interval '8 hours'
>>
>>
>>  Have a question what makes  these values different other than the
>> timestamp???
>>
>>
>> 1, 1, now() - interval '12 hours'
>> *1, 1, now() - interval '10 hours'*
>>
>> The reason i ask, is because you show *1, 1, now() - interval '8 hours'*
>> in the desired output.   What logic keeps the 8 hour and 12 hour but not the
>> 10hour interval???
>>
>> Its kinda hard to understand why the 10hour interval is being skipped???
>>
>>
>> All legitimate Magwerks Corporation quotations are sent in a .PDF file
>> attachment with a unique ID number generated by our proprietary quotation
>> system. Quotations received via any other form of communication will not be
>> honored.
>>
>> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
>> legally privileged, confidential or other information proprietary to
>> Magwerks Corporation and is intended solely for the use of the individual to
>> whom it addresses. If the reader of this e-mail is not the intended
>> recipient or authorized agent, the reader is hereby notified that any
>> unauthorized viewing, dissemination, distribution or copying of this e-mail
>> is strictly prohibited. If you have received this e-mail in error, please
>> notify the sender by replying to this message and destroy all occurrences of
>> this e-mail immediately.
>> Thank you.
>>
>
>

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

The 10 and 11 hour interval are being skipped because I'm only interested in
the transitions of state 1. State 1 only transitioned three times at now -
12, now - 9 and now - 8.

The table has both transitions in it because I frequently care about them
both together.  I just don't in this case.

On Thu, May 27, 2010 at 12:36 PM, Justin Graf  wrote:

>  On 5/27/2010 9:45 AM, Nikolas Everett wrote:
>
> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2
> INT NOT NULL, timestamp TIMESTAMP);
>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '12 hours');
>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() -
> interval '11 hours');
>   INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '10 hours');
>  INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() -
> interval '9 hours');
>  INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '8 hours');
>
>
>  I want to write a query that spits out:
>   state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'
>
>
>  Have a question what makes  these values different other than the
> timestamp???
>
>
> 1, 1, now() - interval '12 hours'
> *1, 1, now() - interval '10 hours'*
>
> The reason i ask, is because you show *1, 1, now() - interval '8 hours'*
> in the desired output.   What logic keeps the 8 hour and 12 hour but not the
> 10hour interval???
>
> Its kinda hard to understand why the 10hour interval is being skipped???
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to
> Magwerks Corporation and is intended solely for the use of the individual to
> whom it addresses. If the reader of this e-mail is not the intended
> recipient or authorized agent, the reader is hereby notified that any
> unauthorized viewing, dissemination, distribution or copying of this e-mail
> is strictly prohibited. If you have received this e-mail in error, please
> notify the sender by replying to this message and destroy all occurrences of
> this e-mail immediately.
> Thank you.
>

Re: [GENERAL] Please help me write a query

[Justin Graf]

On 5/27/2010 9:45 AM, Nikolas Everett wrote:
> Sorry.  Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, 
> state2 INT NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '12 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - 
> interval '11 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '10 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - 
> interval '9 hours');
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - 
> interval '8 hours');
>
>
> I want to write a query that spits out:
>  state1 | timestamp
> +
>   1 | now() - interval '12 hours'
>   2 | now() - interval '9 hours'
>   1 | now() - interval '8 hours'
>
>
Have a question what makes  these values different other than the 
timestamp???

1, 1, now() - interval '12 hours'
*1, 1, now() - interval '10 hours'*

The reason i ask, is because you show *1, 1, now() - interval '8 
hours'*  in the desired output.   What logic keeps the 8 hour and 12 
hour but not the 10hour interval???

Its kinda hard to understand why the 10hour interval is being skipped???


All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[Craig Ringer]

On 27/05/2010 10:42 PM, erobles wrote:

hi,
i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7


Possibly stupid question: Why?

Do you need the Pg server to run on SCO OpenServer? Or just a client? 
Have you considered running your (presumably SCO-based) client with a 
network connection to a Pg server running on something a bit more modern 
and maintainable?


If so, is your goal to get a newer libpq built for SCO? Because getting 
libpq to build might be a whole lot easier than getting the whole Pg 
client+server to build and run.


I maintain a SCO OpenServer 5.0.5 box (thankfully now as a VM) and I'd 
never, ever, ever run any service I didn't have to on it. It's a fossil, 
and while I have to keep it around for one app (for now) I cannot 
imagine voluntarily deploying anything new on it under any circumstances.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Download

[Craig Ringer]

On 27/05/2010 4:44 PM, Faiyaz Allie wrote:

Hi There

I would like to find out where I can download Postgresql 8.1.7 rpm for
Redhat 5.5. I’m trying to load an application that requires it. I’ve
tried to download it from your site but the link times out.


What download link are you using? Which site?

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[Tom Lane]

erobles  writes:
> i have been using   gcc  2.95.2 to compile

At least get onto 2.95.3 ;-).  I've been using that version on HPPA
for quite awhile and haven't tripped across any bugs.  But in any case
these are stone-age versions.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Justin Graf]

On 5/27/2010 9:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1.  I guess it would look
> something like
>
> SELECT state1, FIRST(timestamp)
> FROM table
>
> but I have no idea how to aggregate just the repeated state1 rows.

if i understand what your  after

Select distinct transitinable_id, state1,  min(timestamp) from table 
group by  transitinable_id, state1















All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[erobles]

i have been using   gcc  2.95.2 to compile

On 05/27/2010 10:02 AM, Tom Lane wrote:

erobles  writes:
   

i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7, but ...
when   the xlog.o is being compiled  i have the next error:
 
   

/usr/tmp/ccihgiYL.s: 1113: syntax error at name f
/usr/tmp/ccihgiYL.s: 1113: syntax error at integer constant: 1
 

Looks like a compiler bug.  Get a newer compiler from SCO if you can,
or consider switching to gcc.

regards, tom lane

   


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[Tom Lane]

erobles  writes:
> i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7, but ... 
> when   the xlog.o is being compiled  i have the next error:

> /usr/tmp/ccihgiYL.s: 1113: syntax error at name f
> /usr/tmp/ccihgiYL.s: 1113: syntax error at integer constant: 1

Looks like a compiler bug.  Get a newer compiler from SCO if you can,
or consider switching to gcc.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML index

[Chris Roffler]

Changed the create index statement to :   USING hash  and it seems to work.

Any idea why btree does not work ?

Thanks
Chris

On Thu, May 27, 2010 at 3:47 PM, Chris Roffler wrote:

> Tried that  same thing
>
>
> On Thu, May 27, 2010 at 1:53 PM, Thom Brown  wrote:
>
>> On 27 May 2010 12:22, Chris Roffler  wrote:
>> > I have a table with an xml column, created an index as follows:
>> > CREATE INDEX xml_index
>> >   ON test
>> >   USING btree
>> >   (((xpath('//*/ChangedBy/text()'::text,
>> external_attributes))[1]::text));
>> > And here is my select statement:
>> > Select uuid from t
>> >   where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
>> > 'User';
>> > I then insert 100rows into this table, then do a select with the above
>> > statement.
>> > Explain shows that the query is using the xml_index.
>> > Now I insert 5000 more rows and Explain shows that the query does not
>> use
>> > the xml_index anymore.
>> > However, if I drop the index and re create it, then Explain tells me
>> that
>> > it's using the index again.
>> > Any ideas what is going on here ?
>> > Thanks
>> > Chris
>> >
>>
>> I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
>> out of date.
>>
>> Thom
>>
>
>

Re: [GENERAL] Statement Pooling

[Joshua Tolley]

On Tue, May 25, 2010 at 05:28:10PM +0200, Janning wrote:
> Our hibernate stack uses prepared statements. Postgresql is caching the 
> execution plan. Next time the same statement is used, postgresql reuses the 
> execution plan. This saves time planning statements inside DB.

It only uses the cached plan if you prepare the statement and run that
prepared statement. Running "SELECT foo FROM bar" twice in a row without any
preparing will result in the query being parsed, planned, and executed twice.
On the other hand, doing something like this:

p = conn.prepareStatement("SELECT foo FROM bar");

...and then repeatedly executed p, parsing and planning for the query would
occur only once, at the time of the prepareStatement call.

> Additionally c3p0 can cache java instances of "java.sql.PreparedStatement" 
> which means it is caching the java object. So when using   
> c3p0.maxStatementsPerConnection  =   100 it caches at most 100 different  
> objects. It saves time on creating objects, but this has nothing to do with 
> the postgresql database and its prepared statements.
> 
> Right?

That's the idea.

> As we use about 100 different statements I would set 
>   c3p0.maxStatementsPerConnection  =   100
> 
> Is this reasonable? Is there a real benefit activating it? 

Answering that question for your situation really requires benchmarking with
and without statement caching turned on. Your best bet is probably to set it
to a value that seems decent, and revisit it if you find a performance
bottleneck you need to resolve which looks like it's related to statement
caching.

> I remember postgresql 8.4 is replanning prepared statements when statistics 
> change occur, but I didn't find it in the release notes. It is just saying  
> "Invalidate cached plans when referenced schemas, functions, operators, or 
> operator classes are modified". Does PG replans prepared statements from time 
> to time if underlying data statistics change?

I don't think so, though I may be wrong. The change you refer to replans such
things when the actual objects change, such as when you remove a column or
something that would make the plan fail to execute.


--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [GENERAL] hi, trying to compile postgres 8.3.11

[Merlin Moncure]

On Thu, May 27, 2010 at 10:42 AM, erobles  wrote:
> hi,
> i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7, but ... when
> the xlog.o is being compiled  i have the next error:
>
> /usr/tmp/ccihgiYL.s: 1113: syntax error at name f
> /usr/tmp/ccihgiYL.s: 1113: syntax error at integer constant: 1
>
> i dont have copy of that  assembly file  because  was deleted...
>
> What can i do  to solve this? by the way  i've compiled postgres 8.3.1  and
> i don't have problem.

What assembler are you using?  One possibility is to try and upgrade
gas if you using it or using gas if you are using the stock assembler.
 I rate chance of success at about 25%.  What compiler are you using?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Nikolas Everett]

Sorry.  Here is the setup:
CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
NOT NULL, timestamp TIMESTAMP);
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'12 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interval
'11 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'10 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (2, 1, now() - interval
'9 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'8 hours');


I want to write a query that spits out:
 state1 | timestamp
+
  1 | now() - interval '12 hours'
  2 | now() - interval '9 hours'
  1 | now() - interval '8 hours'

Standard grouping destroys the third row so that's out.  No grouping at all
gives repeats of state1.  Is this what partitioning is for?

Nik

On Thu, May 27, 2010 at 10:20 AM, Ozz Nixon  wrote:

> Lost me a bit, do you mean DISTINCT?
>
> select distinct state1, first(timestamp) from table
>
> On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:
>
> > Say I have a table that stores state transitions over time like so:
> > id, transitionable_id, state1, state2, timestamp
> >
> > I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1.  I guess it would look
> something like
> >
> > SELECT state1, FIRST(timestamp)
> > FROM table
> >
> > but I have no idea how to aggregate just the repeated state1 rows.
>
>

[GENERAL] hi, trying to compile postgres 8.3.11

[erobles]

hi,
i tried to compile postgres 8.3.11 on SCO OpenServer 5.0.7, but ... 
when   the xlog.o is being compiled  i have the next error:


/usr/tmp/ccihgiYL.s: 1113: syntax error at name f
/usr/tmp/ccihgiYL.s: 1113: syntax error at integer constant: 1

i dont have copy of that  assembly file  because  was deleted...

What can i do  to solve this? by the way  i've compiled postgres 8.3.1  
and i don't have problem.



Regards,erobles.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please help me write a query

[Ozz Nixon]

Lost me a bit, do you mean DISTINCT?

select distinct state1, first(timestamp) from table

On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:

> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
> 
> I'm trying to write a query that coalesces changes in state2 away to produce 
> just a list of transitions of state1.  I guess it would look something like
> 
> SELECT state1, FIRST(timestamp)
> FROM table
> 
> but I have no idea how to aggregate just the repeated state1 rows.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Please help me write a query

[Nikolas Everett]

Say I have a table that stores state transitions over time like so:
id, transitionable_id, state1, state2, timestamp

I'm trying to write a query that coalesces changes in state2 away to produce
just a list of transitions of state1.  I guess it would look something like

SELECT state1, FIRST(timestamp)
FROM table

but I have no idea how to aggregate just the repeated state1 rows.

Re: [GENERAL] 110,000,000 rows

[Thom Brown]

On 27 May 2010 14:48, Nikolas Everett  wrote:
> I've had a reporting database with just about a billion rows.  Each row
> was horribly large because the legacy schema had problems.  We partitioned
> it out by month and it ran about 30 million rows a month.  With a reasonably
> large box you can get that kind of data into memory and indexes are
> almost unnecessary.  So long as you have constraint exclusion and a good
> partition scheme you should be fine.  Throw in a well designed schema and
> you'll be cooking well into the tens of billions of rows.
> We ran self joins of that table reasonably consistently by the way:
> SELECT lhs.id, rhs.id
> FROM bigtable lhs, bigtable rhs
> WHERE lhs.id > rhs.id
>      AND '' > lhs.timestamp AND lhs.timestamp >= ''
>      AND '' > rhs.timestamp AND rhs.timestamp >= ''
>      AND lhs.timestamp = rhs.timestamp
>      AND lhs.foo = rhs.foo
>      AND lhs.bar = rhs.bar
> This really liked the timestamp index and we had to be careful to only do it
> for a few days at a time.  It took a few minutes each go but it was
> definitely doable.
> Once you get this large you do have to be careful with a few things though:
> *It's somewhat easy to write super long queries or updates.  This can lots
> of dead rows in your tables.  Limit your longest running queries to a day or
> so.  Note that queries are unlikely to take that long but updates with
> massive date ranges could.  SELECT COUNT(*) FROM bigtable too about 30
> minutes when the server wasn't under heavy load.
> *You sometimes get bad plans because:
> **You don't or can't get enough statistics about a column.
> **PostgreSQL doesn't capture statistics about two columns together.
>  PostgreSQL has no way of knowing that columnA = 'foo' implies columnB =
> 'bar' about 30% of the time.
> Nik

What's that middle bit about?

>  AND '' > lhs.timestamp AND lhs.timestamp >= ''
>  AND '' > rhs.timestamp AND rhs.timestamp >= ''

If blank is greater than the timestamp?  What is that doing out of curiosity?

Thom

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 110,000,000 rows

[Nikolas Everett]

I've had a reporting database with just about a billion rows.  Each row
was horribly large because the legacy schema had problems.  We partitioned
it out by month and it ran about 30 million rows a month.  With a reasonably
large box you can get that kind of data into memory and indexes are
almost unnecessary.  So long as you have constraint exclusion and a good
partition scheme you should be fine.  Throw in a well designed schema and
you'll be cooking well into the tens of billions of rows.

We ran self joins of that table reasonably consistently by the way:
SELECT lhs.id, rhs.id
FROM bigtable lhs, bigtable rhs
WHERE lhs.id > rhs.id
 AND '' > lhs.timestamp AND lhs.timestamp >= ''
 AND '' > rhs.timestamp AND rhs.timestamp >= ''
 AND lhs.timestamp = rhs.timestamp
 AND lhs.foo = rhs.foo
 AND lhs.bar = rhs.bar

This really liked the timestamp index and we had to be careful to only do it
for a few days at a time.  It took a few minutes each go but it was
definitely doable.

Once you get this large you do have to be careful with a few things though:
*It's somewhat easy to write super long queries or updates.  This can lots
of dead rows in your tables.  Limit your longest running queries to a day or
so.  Note that queries are unlikely to take that long but updates with
massive date ranges could.  SELECT COUNT(*) FROM bigtable too about 30
minutes when the server wasn't under heavy load.
*You sometimes get bad plans because:
**You don't or can't get enough statistics about a column.
**PostgreSQL doesn't capture statistics about two columns together.
 PostgreSQL has no way of knowing that columnA = 'foo' implies columnB =
'bar' about 30% of the time.

Nik

On Thu, May 27, 2010 at 5:58 AM, Massa, Harald Armin  wrote:

> Dann,
>
> There really are domains that big, so that there is no more normalization
>> or other processes to mitigate the problem.
>>
>> Examples:
>> Microsoft's registered customers database (all MS products bought by any
>> customer, including operating systems)
>> Tolls taken on the New Jersey road system for FY 2009
>> DNA data from the Human Genome Project
>>
>> .
>
> please also think of ouer most risk exposed users, the ones using Poker /
> Roulette simulation and analyzing software with an PostgrSQL database below.
> There are so many rounds of Poker to play  :)
>
> Harald
>
>
> --
> GHUM Harald Massa
> persuadere et programmare
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
> no fx, no carrier pigeon
> -
> Using PostgreSQL is mostly about sleeping well at night.
>

Re: [GENERAL] XML index

[Chris Roffler]

Tried that  same thing

On Thu, May 27, 2010 at 1:53 PM, Thom Brown  wrote:

> On 27 May 2010 12:22, Chris Roffler  wrote:
> > I have a table with an xml column, created an index as follows:
> > CREATE INDEX xml_index
> >   ON test
> >   USING btree
> >   (((xpath('//*/ChangedBy/text()'::text,
> external_attributes))[1]::text));
> > And here is my select statement:
> > Select uuid from t
> >   where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
> > 'User';
> > I then insert 100rows into this table, then do a select with the above
> > statement.
> > Explain shows that the query is using the xml_index.
> > Now I insert 5000 more rows and Explain shows that the query does not use
> > the xml_index anymore.
> > However, if I drop the index and re create it, then Explain tells me that
> > it's using the index again.
> > Any ideas what is going on here ?
> > Thanks
> > Chris
> >
>
> I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
> out of date.
>
> Thom
>

[GENERAL] Download

[Faiyaz Allie]

Hi There

 

I would like to find out where I can download Postgresql 8.1.7 rpm for
Redhat 5.5. I'm trying to load an application that requires it. I've
tried to download it from your site but the link times out.

 

Any help will be appreciated.

 

Regards

 

Faiyaz Allie

Operations Manager
Managed Services 

 

   +27 11 790 2500

  +27 11 790 2706 (Direct)
   +27 82 450 9495 (Cell)

   +27 11 790 2599 (Fax)
   faiyaza@  securedata.co.za

 

The contents and any attachments are subject to the following
disclaimer:
http://www.securedataholdings.co.za/disclaimer.php
  

 

 

 

 

<><><><><><>

Re: re[GENERAL] ducing postgresql disk space

[paladine]

> alter a table column to its own type, like this:
> alter table foo alter column my_counter type integer; -- my_counter
> is already an integer

Is that really reclaim disk space and how ??
For example; if 'my_counter' column is already integer, 
why do I alter this column to integer again ?
 


Vick Khera wrote:
> 
> On Wed, May 26, 2010 at 10:16 AM, paladine  wrote:
>> Anyone know another method ?
>>
> 
> options to reclaim disk space:
> 
> vacuum full
> dump/restore (sometimes faster than vacuum full)
> cluster (not mvcc safe as far as i know)
> alter a table column to its own type, like this:
>  alter table foo alter column my_counter type integer; -- my_counter
> is already an integer
> 
> sometimes all you need to do is reindex the table (or just the larger
> indexes on the table selectively)
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 

-- 
View this message in context: 
http://old.nabble.com/reducing-postgresql-disk-space-tp28681415p28690159.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: re[GENERAL] ducing postgresql disk space

[paladine]

Firstly, thanks for your explanations...

> Are you attempting a one-time space reduction or are you having general 
> bloat issues?

Unfortunately, I have growing bloat issues so I want to reduce space as it
filled up.
Thus I wrote a script but as I said before it doesn't reclaim disk space. 

> make sure you have upgraded and that autovacuum is enabled and correctly
> tuned

In my pg configuration, ' autovacuum = off ' 
but I run a script ( daily cronjob ) that controls the number of table row
and 
if it expires a determined limit, run ' delete command ' and then run '
vacuum analyse verbose '
In your opinion, Is autovacuuming more efficient way ?





Steve Crawford wrote:
> 
> On 05/26/2010 07:16 AM, paladine wrote:
>> Hi all,
>>
>> How can I reduce disk space postgresql used ?
>> I tried to delete many rows from my database and
>> I am running ' vacuum analyze reindexdb ' commands regularly
>> but my disk space on my linux machine didn't reduce.
>>
>> I know that ' vacuum full ' command can do that but I don't want to use
>> that command because of the disadvantages.
>>
>> Anyone know another method ?
>>
> Are you attempting a one-time space reduction or are you having general 
> bloat issues?
> 
> It is important to understand what is happening behind the scenes. Due 
> to MVCC (multi-version concurrency control), when you update a record, 
> PostgreSQL keeps the old one available until the transaction commits. 
> When no transaction needs the old record, it is not physically removed 
> but it is marked as dead. The basic vacuum process does not free 
> disk-space but rather identifies space within the files that hold the 
> table that has become available for reuse.
> 
> In a modern version of PostgreSQL with autovacuum running and set 
> appropriately for your workload, bloat should stay reasonably under 
> control (i.e. make sure you have upgraded and that autovacuum is enabled 
> and correctly tuned). But there are some things that can cause excess 
> table bloat like updates that hit all rows (this will roughly double the 
> size of a clean table) or deletes of substantial portions of a table. 
> Vacuum will allow this space to be reclaimed eventually, but you may 
> want to reduce disk-space sooner.
> 
> Your options:
> 
> Dump/restore. Not useful on a live, running database but can be useful 
> when you have yourself wedged in a corner on a machine out-of-space as 
> you can dump to another machine then do a clean restore back to your 
> server. Depending on your situation (especially foreign-key 
> constraints), you *may* be able to dump/restore just a specific 
> offending table.
> 
> Vacuum full. Reclaims the space, but is typically slw and requires 
> an exclusive table lock. IIRC, should be followed by a reindex of the 
> table. But vacuum-full runs "in-place" so it can be of use when you have 
> little free-space remaining on your device.
> 
> Cluster. Reclaims free-space and reindexes. Also reorders the table-data 
> to match the specified index which is often useful. Cluster must be run 
> on a table-by-table basis. Cluster also requires an exclusive lock but 
> is *way* faster than vacuum-full. Cluster requires enough free-space to 
> fully create the new clean copy of the table. This means a table can 
> require as much as double it's original space for clustering though a 
> heavily bloated table may require far less.
> 
> Both cluster and vacuum full are safe. If you are in a tight place, you 
> can carefully choose the method to use on a table-by-table basis: 
> vacuum-full if your hand is forced and cluster when you have made enough 
> free-space available.
> 
> Once things are cleaned up, examine how they got bad to begin with so 
> you aren't bitten again.
> 
> Cheers,
> Steve
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 

-- 
View this message in context: 
http://old.nabble.com/reducing-postgresql-disk-space-tp28681415p28690348.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: re[GENERAL] ducing postgresql disk space

[paladine]

command output for mentioned db :  ' my_db_name | 42 GB '

I don't print query results to logfile. I restore my system logs to db.
I have a lot of live ( growing ) logs on my machine and I register these
logs to db.
My essential question is that why don't I reclaim disk space though I run
this command 
'delete from db ' ?




Thom Brown wrote:
> 
> On 26 May 2010 15:50, paladine  wrote:
>>
>> It is default value ( #checkpoint_segments = 3    # in logfile segments,
>> min
>> 1, 16MB each )
>> Many of my database configurations are default values. (plain TOAST  etc)
>> my database is a log database so, some tables of db grow everytime.
>> My ' /base ' directory contains a lot of compressed object (1GB size)
>> These are maybe normal operations but I don't understand that
>> although I delete many rows from my db and regularly vacuum , reindexing
>> operations,
>> how doesn't postgresql give back that deleted areas for reusing.
>>
> 
> I'm just wondering if you're still building up the initial set of WAL
> files which will begin to plateau if the data in your database in
> roughly consistent in size over time.
> 
> Try:
> 
> select datname, pg_size_pretty(pg_database_size(datname)) from
> pg_database order by datname;
> 
> That should give you the actual sizes of each database.  Also, how
> verbose is the database logging?  If you're logging every query to a
> log file that may also account for it.
> 
> Regards
> 
> Thom
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 

-- 
View this message in context: 
http://old.nabble.com/reducing-postgresql-disk-space-tp28681415p28690076.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] XML index

[Thom Brown]

On 27 May 2010 12:22, Chris Roffler  wrote:
> I have a table with an xml column, created an index as follows:
> CREATE INDEX xml_index
>   ON test
>   USING btree
>   (((xpath('//*/ChangedBy/text()'::text, external_attributes))[1]::text));
> And here is my select statement:
> Select uuid from t
>   where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
> 'User';
> I then insert 100rows into this table, then do a select with the above
> statement.
> Explain shows that the query is using the xml_index.
> Now I insert 5000 more rows and Explain shows that the query does not use
> the xml_index anymore.
> However, if I drop the index and re create it, then Explain tells me that
> it's using the index again.
> Any ideas what is going on here ?
> Thanks
> Chris
>

I'd run an ANALYZE after inserting 5000 more rows.  The stats will be
out of date.

Thom

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] XML index

[Chris Roffler]

I have a table with an xml column, created an index as follows:

*CREATE INDEX xml_index*
*  ON test*
*  USING btree*
*  (((xpath('//*/ChangedBy/text()'::text, external_attributes))[1]::text));*

And here is my select statement:

*Select uuid from t *
*  where  (xpath('//*/ChangedBy/text()', external_attributes))[1]::text =
'User';*
*
*
I then insert 100rows into this table, then do a select with the above
statement.
*Explain *shows that the query is using the xml_index.

Now I insert 5000 more rows and *Explain *shows that the query does not use
the xml_index anymore.
However, if I drop the index and re create it, then *Explain *tells me that
it's using the index again.

Any ideas what is going on here ?

Thanks
Chris
*
*

Re: [GENERAL] 110,000,000 rows

[Massa, Harald Armin]

Dann,

There really are domains that big, so that there is no more normalization or
> other processes to mitigate the problem.
>
> Examples:
> Microsoft's registered customers database (all MS products bought by any
> customer, including operating systems)
> Tolls taken on the New Jersey road system for FY 2009
> DNA data from the Human Genome Project
>
> .

please also think of ouer most risk exposed users, the ones using Poker /
Roulette simulation and analyzing software with an PostgrSQL database below.
There are so many rounds of Poker to play  :)

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
Using PostgreSQL is mostly about sleeping well at night.

Re: [GENERAL] 110,000,000 rows

[John Gage]

Herbert Simon must be spinning in his grave...or smiling wisely.  What  
does a human do with a petabyte of data?


But when a desktop machine for $1700 retail has a terabyte of storage,  
the unix operating system, 4 gigs of memory, and an amazing 27 inch  
display, I guess hardware isn't the problem (and I know one could put  
together the same machine on Linux etc. for much less).


I sort of understood that the Amazon's of the world had this amount of  
data, but it looks like the phenomenon is much, much more widespread.


Thanks for the instruction.  It will come in handy.

John



On May 27, 2010, at 12:18 AM, da...@gardnerit.net wrote:


At work I have one table with 32 million rows, not quite the size you
are talking about, but to give you an idea of the performance, the
following query returns 14,659 rows in 405ms:

SELECT * FROM farm.frame
WHERE process_start > '2010-05-26';

process_start is a timestamp without time zone column, and is  
covered by
an index. Rows are reletively evenly distributed over time, so the  
index

performs quite well.

A between select also performs well:
SELECT * FROM farm.frame
WHERE process_start
 BETWEEN '2010-05-26 08:00:00'
   AND '2010-05-26 09:00:00';

fetches 1,350 rows at 25ms.

I also have a summary table that is maintained by triggers, which is a
bit of denormalization, but speeds up common reporting queries.

On 22:29 Wed 26 May , John Gage wrote:

Please forgive this intrusion, and please ignore it, but how many
applications out there have 110,000,000 row tables?  I recently
multiplied 85,000 by 1,400 and said now way Jose.

Thanks,

John Gage

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general