vacuum strategy

On Mon, 25 Nov 2002, CSN wrote:

What's the typical way to handle pg vacuuming? Have a
somewhat frequently called script do it periodically,
or just make a script for cron?

Also, isn't there a project related to this that
handles it automatically (if so please give the URL)?

The basic rule of thumb is to vacuum a table whenever it's had more than
about 25% turnover. Of course, on very large tables, as little as 5%
turnover could justify a vacuum. The same basic number applies for
analyzing.

Plain vacuums can be run as often as you'd like really, as they consume
little bandwidth and are non-blocking.

vacuum full should be run during off peak hours. While normal non-full
vacuums are good enough for most uses, it's occasionally necessary to run
a full vacuum to reclaim tuples that the normal vacuum couldn't free (if
there are a bunch freed between regular vacuums, it sometimes isn't
possible to free them.

Also, full vacuums are required every so often to keep the transaction id
from rolling over. It rolls over at 4 billion, so you don't have to run a
full vacuum all that often for that.

There is an autovacuum daemon in the works, you can find it on gborg at:

http://gborg.postgresql.org/project/pgavd/projdisplay.php

I haven't had a chance to play with it, but I did just download it and
plan on playing with it a bit.

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Also, full vacuums are required every so often to keep the transaction id
from rolling over.

Not so; a plain vacuum is fine for that. The critical point is that
*every* table in *every* database has to be vacuumed (plain or full)
at least once every billion transactions or so.

regards, tom lane

Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Also, full vacuums are required every so often to keep the transaction id
from rolling over.

Not so; a plain vacuum is fine for that. The critical point is that
*every* table in *every* database has to be vacuumed (plain or full)
at least once every billion transactions or so.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

dump question:
what means this exactly?
does this include the template1 database? So i have to vacuum the
tempate1 database too on a regular basis to avoid the rolling over of
the transaction id?

regards
tina

On 26 Nov 2002 at 9:56, Tina Messmann wrote:

dump question:
what means this exactly?
does this include the template1 database? So i have to vacuum the
tempate1 database too on a regular basis to avoid the rolling over of
the transaction id?

Why transaction id in template1 should roll? You aren't doing any transactions
on it, are you?

Or I misread the question?

Bye
Shridhar

--
Love sometimes expresses itself in sacrifice. -- Kirk, "Metamorphosis",
stardate 3220.3

Hi All,

I am importing data from text files in to postgres.

I would like to know if some field is not present in Text file,then how does
COPY command handle it ,specially for date field.
--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

On 26 Nov 2002 at 15:20, Savita wrote:

I would like to know if some field is not present in Text file,then how does
COPY command handle it ,specially for date field.

I am not very sure about this, but one thing you can always do is to insert a
blank field separator, making copy believe that the field value is NULL.. Some
sed/awk script should do the trick..

HTH

Bye
Shridhar

--
"We all know Linux is great...it does infinite loops in 5 seconds."(Linus
Torvalds about the superiority of Linux on the AmterdamLinux Symposium)

Hello All,

I would like to know if there are a way to see the lasts queries executed.
For example, if you work with php then apache interact with the postmaster
to run your queries. Then it's could be really interresting to intend to
find
the slowest queries of my web application.

I know that in Mysql there are a log file in which it is stored !

Best Regards del Kiki
Alias Areski

Hi,
I am able to insert the null field using \N in the text file,but while retiving
the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Shridhar Daithankar wrote:

On 26 Nov 2002 at 15:20, Savita wrote:

I would like to know if some field is not present in Text file,then how does
COPY command handle it ,specially for date field.

I am not very sure about this, but one thing you can always do is to insert a
blank field separator, making copy believe that the field value is NULL.. Some
sed/awk script should do the trick..

HTH

Bye
Shridhar

--
"We all know Linux is great...it does infinite loops in 5 seconds."(Linus
Torvalds about the superiority of Linux on the AmterdamLinux Symposium)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

Hi Savita,

--On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com>
wrote:

Hi,
I am able to insert the null field using \N in the text file,but while
retiving the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Nothing to do with copy :)
Just try:

select * from os_customer_master where isnull updated_by;

Regards
Tino

On Tuesday 26 Nov 2002 10:31 am, Areski wrote:

Hello All,

I would like to know if there are a way to see the lasts queries executed.
For example, if you work with php then apache interact with the postmaster
to run your queries. Then it's could be really interresting to intend to
find
the slowest queries of my web application.

I know that in Mysql there are a log file in which it is stored !

There are settings in postgresql.conf to control what gets logged - I
personally like to log using the system logger, but that's not your only
option.

See the administrator's manual - 3.4.2. Logging and Debugging
--
Richard Huxton

On 26 Nov 2002 at 16:02, Savita wrote:

Hi,
I am able to insert the null field using \N in the text file,but while retiving
the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Umm.. Is this a text field? What happens when you select for string length=0?

IMO for a string/char field, NULL will be different than empty string but I am
still puzzled why '' did not work...

Bye
Shridhar

--
QOTD: "Our parents were never our age."

Tina Messmann <tina.messmann@xinux.de> writes:

does this include the template1 database? So i have to vacuum the
tempate1 database too on a regular basis to avoid the rolling over of
the transaction id?

Only if you're in the habit of modifying template1.

If you make occasional one-time changes to template1 (like, say,
installing plpgsql in it) you can do a "vacuum freeze" afterward
to make sure all is well; then you needn't include template1 in
your list of things to vacuum regularly.

regards, tom lane

What about the date field????

Shridhar Daithankar wrote:

On 26 Nov 2002 at 16:02, Savita wrote:

Hi,
I am able to insert the null field using \N in the text file,but while retiving
the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Umm.. Is this a text field? What happens when you select for string length=0?

IMO for a string/char field, NULL will be different than empty string but I am
still puzzled why '' did not work...

Bye
Shridhar

--
QOTD: "Our parents were never our age."

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

On 26 Nov 2002 at 16:40, Savita wrote:

What about the date field????

ISNULL didn't work on date field? That's surprising..

Shridhar Daithankar wrote:

Umm.. Is this a text field? What happens when you select for string length=0?
IMO for a string/char field, NULL will be different than empty string but I am
still puzzled why '' did not work...

I am looking thr. documentation but I don't see any equivalent of char_length
for a timestamp.

Bye
Shridhar

--
One of the advantages of being a captain is being able to ask foradvice without
necessarily having to take it. -- Kirk, "Dagger of the Mind", stardate 2715.2

Hi Tino,

These query does not work.

Tino Wildenhain wrote:

Hi Savita,

--On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com>
wrote:

Hi,
I am able to insert the null field using \N in the text file,but while
retiving the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Nothing to do with copy :)
Just try:

select * from os_customer_master where isnull updated_by;

Regards
Tino

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

I think you need to take a step back and pause for a minute to clear your mind.

Now, try:

SELECT * FROM os_customer_master WHERE updated_by is null;

and then:

SELECT * FROM os_customer_master WHERE updated_by is not null;

One of these will show you the tuples you are looking for. If it's the first
then it was a simple misunderstanding. If it's the second there is something
else going on if there should be NULLs. Look for rows with nothing shown for
this column, if there are any they must have invalid data which can't be
converted to string form, or can be converted to string form but are converted
to a zero length string or one with spaces.

Well it's a starting point; if not entirely accurate it's probably accurate
enough for a now considering it's probably just a misunderstanding.

--
Nigel Andrews

On Tue, 26 Nov 2002, Savita wrote:

Hi Savita,

--On Dienstag, 26. November 2002 17:01 +0530 Savita <savita@india.hp.com>
wrote:

Hi Tino,

These query does not work.

Sorry, was a typo. But it schould at least serve as a hint, shouldnt it? ;)

From documentation:

---- excerpt ---
To check whether a value is or is not null, use the constructs

expression IS NULL
expression IS NOT NULL
or the equivalent, but nonstandard, constructs

expression ISNULL
expression NOTNULL

Do not write expression = NULL because NULL is not "equal to" NULL. (The
null value represents an unknown value, and it is not known whether two
unknown values are equal.)

Some applications may (incorrectly) require that expression = NULL returns
true if expression evaluates to the null value. To support these
applications, the run-time option transform_null_equals can be turned on
(e.g., SET transform_null_equals TO ON;). PostgreSQL will then convert x =
NULL clauses to x IS NULL. This was the default behavior in releases 6.5
through 7.1.
---- excerpt ---

Regards
Tino

Hi Thanks to all of you for the help this works now with IS NULL or IS NOT NULL.

"Nigel J. Andrews" wrote:

I think you need to take a step back and pause for a minute to clear your mind.

Now, try:

SELECT * FROM os_customer_master WHERE updated_by is null;

and then:

SELECT * FROM os_customer_master WHERE updated_by is not null;

One of these will show you the tuples you are looking for. If it's the first
then it was a simple misunderstanding. If it's the second there is something
else going on if there should be NULLs. Look for rows with nothing shown for
this column, if there are any they must have invalid data which can't be
converted to string form, or can be converted to string form but are converted
to a zero length string or one with spaces.

Well it's a starting point; if not entirely accurate it's probably accurate
enough for a now considering it's probably just a misunderstanding.

--
Nigel Andrews

On Tue, 26 Nov 2002, Savita wrote:

Hi Tino,

These query does not work.

Tino Wildenhain wrote:

Hi Savita,

--On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com>
wrote:

Hi,
I am able to insert the null field using \N in the text file,but while
retiving the data I am not able to retrive it.

After inserting data I tried this select statement

select * from os_customer_master where updated_by=' ';
and
select * from os_customer_master where updated_by=null;

but it gives 0 rows.then how will I select this values.

Nothing to do with copy :)
Just try:

select * from os_customer_master where isnull updated_by;

Regards
Tino

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
Best Regards
- Savita
----------------------------------------------------
Hewlett Packard (India)
+91 80 2051288 (Phone)
847 1288 (HP Telnet)
----------------------------------------------------

On Mon, 25 Nov 2002, Tom Lane wrote:

"scott.marlowe" <scott.marlowe@ihs.com> writes:

Also, full vacuums are required every so often to keep the transaction id
from rolling over.

Not so; a plain vacuum is fine for that. The critical point is that
*every* table in *every* database has to be vacuumed (plain or full)
at least once every billion transactions or so.

Really? Sorry for the misiniformation. I could have sworn that I read it
on this or the hackers mailing list that only full vacuums could reset the
transaction counter.

Thanks for the catch.