SEO

I'm teaching a class this week and a student asked me about OIDs. He
related the story of how in Sybase, if you moved a database from one
server from another, permissions got all screwed up because user IDs
no longer matched. I explained that exposing something like an
integer ID in a user interface or an API is just a bad idea and
PostgreSQL doesn't do that.

Then I got to pg_autovacuum....

So... is there any reason there isn't a prescribed interface to
pg_autovacuum that doesn't expose vacrelid? Can we get that added to
TODO?

Also, in the meantime, it would make things a lot easier if the
fields in pg_autovacuum had appropriate defaults... vacrelid should
stay as-is with no default, enabled should default to true, and the
remaining fields should default to -1 so they use the system settings.

Also, I don't see a TODO about dumping pg_autovacuum; it seems that
should be added. Of course, we wouldn't want to just dump the table
itself since vacrelid would become invalid, but once there is a means
to alter vacuum settings for a table by name presumably it should be
relatively easy to add a section to pg_dump that outputs the
appropriate code to change the settings in pg_autovacuum.
--
Jim Nasby jim.nasby@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)




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

Jim Nasby wrote:
> I'm teaching a class this week and a student asked me about OIDs. He
> related the story of how in Sybase, if you moved a database from one
> server from another, permissions got all screwed up because user IDs no
> longer matched. I explained that exposing something like an integer ID
> in a user interface or an API is just a bad idea and PostgreSQL doesn't
> do that.
>
> Then I got to pg_autovacuum....
>
> So... is there any reason there isn't a prescribed interface to
> pg_autovacuum that doesn't expose vacrelid? Can we get that added to TODO?

Wouldn't it be sufficient to change the type of vacrelid from oid
to regclass? Then just dumping and restoring pg_autovacuum like any
other table should Just Work.

greetings, Florian Pflug

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

http://archives.postgresql.org

On Dec 20, 2006, at 7:56 AM, Florian G. Pflug wrote:
> Jim Nasby wrote:
>> I'm teaching a class this week and a student asked me about OIDs.
>> He related the story of how in Sybase, if you moved a database
>> from one server from another, permissions got all screwed up
>> because user IDs no longer matched. I explained that exposing
>> something like an integer ID in a user interface or an API is just
>> a bad idea and PostgreSQL doesn't do that.
>> Then I got to pg_autovacuum....
>> So... is there any reason there isn't a prescribed interface to
>> pg_autovacuum that doesn't expose vacrelid? Can we get that added
>> to TODO?
>
> Wouldn't it be sufficient to change the type of vacrelid from oid
> to regclass? Then just dumping and restoring pg_autovacuum like any
> other table should Just Work.

I think that would work, though as I mentioned we'd also want to set
reasonable defaults on the table if we decide to keep that as our
interface.

On the other hand, this would be the only part of the system where
the official interface/API is a system catalog table. Do we really
want to expose the internal representation of something as our API?
That doesn't seem wise to me...

Additionally, AFAIK it is not safe to go poking data into catalogs
willy-nilly. Having one table where this is the interface to the
system seems like it could lead to some dangerous confusion.
--
Jim Nasby jim.nasby@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)




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

Jim Nasby wrote:
> On Dec 20, 2006, at 7:56 AM, Florian G. Pflug wrote:
>> Jim Nasby wrote:
>>> I'm teaching a class this week and a student asked me about OIDs. He
>>> related the story of how in Sybase, if you moved a database from one
>>> server from another, permissions got all screwed up because user IDs
>>> no longer matched. I explained that exposing something like an
>>> integer ID in a user interface or an API is just a bad idea and
>>> PostgreSQL doesn't do that.
>>> Then I got to pg_autovacuum....
>>> So... is there any reason there isn't a prescribed interface to
>>> pg_autovacuum that doesn't expose vacrelid? Can we get that added to
>>> TODO?
>>
>> Wouldn't it be sufficient to change the type of vacrelid from oid
>> to regclass? Then just dumping and restoring pg_autovacuum like any
>> other table should Just Work.
>
> I think that would work, though as I mentioned we'd also want to set
> reasonable defaults on the table if we decide to keep that as our
> interface.
>
> On the other hand, this would be the only part of the system where the
> official interface/API is a system catalog table. Do we really want to
> expose the internal representation of something as our API? That
> doesn't seem wise to me...
>
> Additionally, AFAIK it is not safe to go poking data into catalogs
> willy-nilly. Having one table where this is the interface to the
> system seems like it could lead to some dangerous confusion.
I thought the plan was to change the ALTER TABLE command to allow vacuum
settings to be set. I may be totally away from the mark. But if this
was the case it would mean that dumps would just need an alter table
statement to maintain autovacuum information. There is an advantage
that if you only dump some tables, their autovac settings would go with
them. But is that a good thing?

Reagrds

Russell Smith
> --
> Jim Nasby jim.nasby@enterprisedb.com
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>


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

"Jim Nasby" <jim.nasby@enterprisedb.com> writes:
> On the other hand, this would be the only part of the system where
> the official interface/API is a system catalog table.

I don't think it was ever intended by anyone that that would be the
long-term solution. Where we are currently at is experimenting to find
out what autovacuum's control knobs ought to be. The catalog table was
a suitably low-effort way to expose a first cut at the knobs. The fact
that pg_dump doesn't dump the settings is entirely deliberate: that's to
avoid locking us into a forward compatibility commitment before we're
ready. Once we are happy with the control design, we can think about
what the long-term API ought to be.

regards, tom lane

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

Russell Smith wrote:
> I thought the plan was to change the ALTER TABLE command to allow vacuum
> settings to be set.


That is my understanding too.

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

On Wed, 2006-12-20 at 09:47 -0500, Jim Nasby wrote:

> On the other hand, this would be the only part of the system where
> the official interface/API is a system catalog table. Do we really
> want to expose the internal representation of something as our API?
> That doesn't seem wise to me...

Define and agree the API (the hard bit) and I'll code it (the easy bit).

We may as well have something on the table, even if it changes later.

Dave: How does PgAdmin handle setting table-specific autovacuum
parameters? (Does it?)

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Simon Riggs wrote:
> On Wed, 2006-12-20 at 09:47 -0500, Jim Nasby wrote:
>
>> On the other hand, this would be the only part of the system where
>> the official interface/API is a system catalog table. Do we really
>> want to expose the internal representation of something as our API?
>> That doesn't seem wise to me...
>
> Define and agree the API (the hard bit) and I'll code it (the easy bit).
>
> We may as well have something on the table, even if it changes later.
>
> Dave: How does PgAdmin handle setting table-specific autovacuum
> parameters? (Does it?)
>

Yes, it adds/removes/edits rows in pg_autovacuum as required.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

How about...

ALTER TABLE ...
ALTER AUTOVACUUM [ THRESHOLD | SCALE | COST DELAY | COST LIMIT ]
ALTER AUTOANALYZE [ THRESHOLD | SCALE ]

.... or would that create a whole bunch of reserved words?

On Dec 21, 2006, at 10:04 AM, Simon Riggs wrote:

> On Wed, 2006-12-20 at 09:47 -0500, Jim Nasby wrote:
>
>> On the other hand, this would be the only part of the system where
>> the official interface/API is a system catalog table. Do we really
>> want to expose the internal representation of something as our API?
>> That doesn't seem wise to me...
>
> Define and agree the API (the hard bit) and I'll code it (the easy
> bit).
>
> We may as well have something on the table, even if it changes later.
>
> Dave: How does PgAdmin handle setting table-specific autovacuum
> parameters? (Does it?)
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

--
Jim Nasby jim.nasby@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Jim Nasby" <jim.nasby@enterprisedb.com> writes:

> How about...
>
> ALTER TABLE ...
> ALTER AUTOVACUUM [ THRESHOLD | SCALE | COST DELAY | COST LIMIT ]
> ALTER AUTOANALYZE [ THRESHOLD | SCALE ]
>
> ... or would that create a whole bunch of reserved words?

The way to predict when you're going to run into conflicts in a case like this
is to ask what happens if you have a column named "autovacuum" or
"autoanalyze"...

Sometimes the parser can look ahead to the next keyword to determine which
production to use but usually you're best off just looking for a grammatical
construct that doesn't look ambiguous even to a naive human reader.


--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly