Kevin Grittner wrote:
> Peter Brant, a consultant working with us, has written code which is
> working for this under both Linux and Windows. [...] For Linux, he
> used statvfs.

statvfs(2) is standardized, but doesn't seem portable: it isn't
available on OSX 10.3, NetBSD 2.0 or OpenBSD, for example. Perhaps you
can fallback to statfs(2) when it is available?

This seems an area where providing consistent cross-platform behavior
might be difficult. Do we actually need this functionality inside the
DBMS in the first place?

> (2) If not, is it acceptable for a source file to contain that much
> #if code for Windows?

Let's see the code first...

> (3) What data type should be used to report total space and free space
> for each volume?

int64?

-Neil


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

http://www.postgresql.org/docs/faq

Neil Conway <neilc@samurai.com> writes:
> Do we actually need this functionality inside the
> DBMS in the first place?

I think that is the $64 question. My immediate instinct is "no".
See the knock-down-drag-out fights we had last summer about whether
to expose any filesystem access in built-in standard functions at all.
There will be what the Supreme Court would call "strict scrutiny"
concerning the need for this, possible security risks, etc.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Thu, Feb 23, 2006 at 11:32:05PM -0500, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Do we actually need this functionality inside the
> > DBMS in the first place?
>
> I think that is the $64 question. My immediate instinct is "no".
> See the knock-down-drag-out fights we had last summer about whether
> to expose any filesystem access in built-in standard functions at all.
> There will be what the Supreme Court would call "strict scrutiny"
> concerning the need for this, possible security risks, etc.

Isn't this something that could be accomplished entirely within a
function? I suppose it might have to be an untrusted language, but that
still seems cleaner than putting it in the backend. Plus, ISTM that
something like perl is more likely to have a cross-platform means of
accomplishing this.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

The code in question is written as C extension functions. I think we
were thinking it might be something for contrib (although perhaps that
would be too much of an official blessing too?)

Pete

>>> "Jim C. Nasby" <jnasby@pervasive.com> 02/24/06 8:04 am >>>
Isn't this something that could be accomplished entirely within a
function? I suppose it might have to be an untrusted language, but
that
still seems cleaner than putting it in the backend. Plus, ISTM that
something like perl is more likely to have a cross-platform means of
accomplishing this.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>>> On Thu, Feb 23, 2006 at 8:43 pm, in message
<43FE72DB.8070701@samurai.com>,
Neil Conway <neilc@samurai.com> wrote:
> Kevin Grittner wrote:
>> Peter Brant, a consultant working with us, has written code which
is
>> working for this under both Linux and Windows. [...] For Linux, he
>> used statvfs.
>
> statvfs(2) is standardized, but doesn't seem portable: it isn't
> available on OSX 10.3, NetBSD 2.0 or OpenBSD, for example. Perhaps
you
> can fallback to statfs(2) when it is available?
>
> This seems an area where providing consistent cross- platform
behavior
> might be difficult. Do we actually need this functionality inside the

> DBMS in the first place?

It sounds like we should probably just shelve the idea of sharing this
code. It is very useful to us, since it allows a single database
connection to monitor the health of the database and detect impending
failure. Having our monitoring software need to know the operating
system of each database, with security to log into the OS of each
machine, would be far worse from a security standpoint, more fragile,
and more work than adding these functions to the database. But no
response so far has indicated any interest in it from any other quarter,
and it sounds like the portability issues are more than we want to deal
with here.

In response to a few other posts -- it is implemented with three
functions, which I was bundling to target the contrib level of
submission. One function returns a list of volumes (i.e., drive letters
in Windows, mount points in Linux), one returns available space for a
given volume, and one returns total space for a given volume.

Since we have what we need to get our work done, and the community at
large doesn't seem interested, I'll shelve the idea of submitting
anything.

Thanks for the responses.

-Kevin


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> > This seems an area where providing consistent cross- platform
> behavior
> > might be difficult. Do we actually need this functionality inside the
>
> > DBMS in the first place?
>
> It sounds like we should probably just shelve the idea of sharing this
> code. It is very useful to us, since it allows a single database
> connection to monitor the health of the database and detect impending
> failure. Having our monitoring software need to know the operating
> system of each database, with security to log into the OS of each
> machine, would be far worse from a security standpoint, more fragile,
> and more work than adding these functions to the database. But no

Why don't you have a process on the machine update a set of values in
the database that you then read from remote?

You don't need to know the free diskspace in real time. A 2 minute old
value is probably just as good.


--


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kevin Grittner wrote:

>Since we have what we need to get our work done, and the community at
>large doesn't seem interested, I'll shelve the idea of submitting
>anything.
>
>
>

I think you have misinterpreted. By all means share the code. Put it on
your website or start a pgfoundry project. Even if it doesn't go into
the core code some people might very well be interested to use it.

cheers

andrew

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

>>> On Fri, Feb 24, 2006 at 9:34 am, in message
<1140795253.5092.122.camel@home>,
Rod Taylor <pg@rbt.ca> wrote:
>
> You don't need to know the free diskspace in real time. A 2 minute
old
> value is probably just as good.

Not really, this sort of monitoring has kept us from crashing under our
old database product when a poorly written query starts filling
available space by populating a temporary table. A green light turns
orange (indicating impending failure) on a big board which monitors the
health of about 200 servers and about 3000 workstations. A support
person drills down through the "LED" on a version of the "big board" in
their browser, which shows the supporting detail. A runaway query can
be identified and killed, protecting the rest of the users. A two
minute delay could cause an embarrassing interruption in service for
hundreds of our users.

Your suggestion would work for the gradual "outgrowing" of space, but
we need to cover both.

-Kevin





---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Fri, 2006-02-24 at 09:48 -0600, Kevin Grittner wrote:
> >>> On Fri, Feb 24, 2006 at 9:34 am, in message
> <1140795253.5092.122.camel@home>,
> Rod Taylor <pg@rbt.ca> wrote:
> >
> > You don't need to know the free diskspace in real time. A 2 minute
> old
> > value is probably just as good.
>
> Not really, this sort of monitoring has kept us from crashing under our
> old database product when a poorly written query starts filling
> available space by populating a temporary table. A green light turns

I see. It is annoying that you cannot easily (takes a patch to PG
sources) segregate users temporary workspaces into per-user tablespaces
with filesystem quotas.

PostgreSQL seems to deal with out of diskspace situations pretty well
when it impacts a tablespace (global stuff like WAL or subtransactions
have issues -- but they grow slowly) as far as only interrupting service
for the individual actions that ran out.

You may wish to look at funding toggles that can configure the maximum
memory usage and maximum temporary diskspace (different tablespaces with
filesystem quotas) on a per user basis similar to the statement_timeout
limitations in place today.


I'm curious as to how you monitor for total transaction time length to
ensure that vacuum is able to do its thing, particularly when the
transaction is active (not IDLE).

--


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

>>> On Fri, Feb 24, 2006 at 10:57 am, in message
<1140800266.5092.144.camel@home>,
Rod Taylor <pg@rbt.ca> wrote:
>
> PostgreSQL seems to deal with out of diskspace situations pretty
well
> when it impacts a tablespace (global stuff like WAL or
subtransactions
> have issues -- but they grow slowly) as far as only interrupting
service
> for the individual actions that ran out.

We haven't used tablespace features yet, as 3 of the 4 databases
running PostgreSQL so far are on Windows. We have run out of space a
couple times, and it seems like it handles it well in terms of not
corrupting the database, and resuming OK once some space is freed. The
messages are not that clear -- some sort of generic I/O write error, as
I recall, instead of "out of disk space" being clearly stated.

> You may wish to look at funding toggles that can configure the
maximum
> memory usage and maximum temporary diskspace (different tablespaces
with
> filesystem quotas) on a per user basis similar to the
statement_timeout
> limitations in place today.

That wouldn't help because the vast majority of the work is done
through a middle tier which uses a connection pool shared by all users.
It does take some human review and judgment to ensure that a query which
is running long and/or using a lot of temp table space is really a
problem as opposed to one of our larger legitimate processes.

> I'm curious as to how you monitor for total transaction time length
to
> ensure that vacuum is able to do its thing, particularly when the
> transaction is active (not IDLE).

We run a database vacuum nightly and review it the next day.
(Something will need to be done to automate this with summaries and
exception lists when we get more than a few databases on PostgreSQL. We
can't have a person reviewing 100 of these every day.) We've not had
any nightly vacuum fail to finish. They did start running a tad long
until we did some aggressive maintenance at one point.

Our autovacuum is configured with fairly aggressive parameters,
compared to the default; but, even so, only a few small tables with high
update rates normally reach the thresholds. I haven't noticed the
autovacuum getting held up on these.

-Kevin



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend