Re: [HACKERS] Interface for pg_autovacuum
Robert Treat wrote: Dave: How does PgAdmin handle setting table-specific autovacuum parameters? (Does it?) Yes, it adds/removes/edits rows in pg_autovacuum as required. We do this in phppgadmin too, although I also added a screen that show alist of entries with schema and table names (rather than vacrelid) since otherwise it is too much pita to keep things straight. My intent is also to add controls at the table level (where we'll know the vacrelid anyway) though it will probably be put off until there is more demand for it. The actual user interface is at table level in pgAdmin - there's an extra tab on the table properties dialogue that allows you to tweak the values or leave them at system default. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interface for pg_autovacuum
On Thursday 21 December 2006 10:57, Dave Page wrote:
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.
We do this in phppgadmin too, although I also added a screen that show alist
of entries with schema and table names (rather than vacrelid) since otherwise
it is too much pita to keep things straight. My intent is also to add
controls at the table level (where we'll know the vacrelid anyway) though it
will probably be put off until there is more demand for it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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
Re: [HACKERS] Interface for pg_autovacuum
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
Re: [HACKERS] Interface for pg_autovacuum
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
Re: [HACKERS] Interface for pg_autovacuum
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
Re: [HACKERS] Interface for pg_autovacuum
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Interface for pg_autovacuum
Jim Nasby [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Interface for pg_autovacuum
Jim Nasby wrote: How about... ALTER TABLE ... ALTER AUTOVACUUM [ THRESHOLD | SCALE | COST DELAY | COST LIMIT ] ALTER AUTOANALYZE [ THRESHOLD | SCALE ] Given these remarks from Tom: 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. doesn't making language level changes seem more than somewhat premature? Or have we finished experimenting? cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interface for pg_autovacuum
Gregory Stark wrote: Jim Nasby [EMAIL PROTECTED] 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... Might it not be cleaner to treat them as scoped configuration values? ALTER TABLE foo SET autovacuum.threshold = ... Presumably it's not going to be the last such setting, and would give you a common format for setting all manner of system-object related things: - column statistics - fill-factor - comment - per-column locale (when we get it) - any module-related tuning (tsearch2? slony?) That way the parser just needs to treat the next thing after SET as a (possibly compound) identifier. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Interface for pg_autovacuum
On Dec 21, 2006, at 1:28 PM, Andrew Dunstan wrote: Jim Nasby wrote: How about... ALTER TABLE ... ALTER AUTOVACUUM [ THRESHOLD | SCALE | COST DELAY | COST LIMIT ] ALTER AUTOANALYZE [ THRESHOLD | SCALE ] Given these remarks from Tom: 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. doesn't making language level changes seem more than somewhat premature? Or have we finished experimenting? Well, the only one I could possibly see removing would be threshold, but the reality is that these parameters have been kicking around since 7.4, so... But I do like Richard Huxton's suggestion for syntax... that looks a lot more flexible than what I proposed. The only other thought that comes to mind is that such syntax will make it a *lot* more verbose to set all the options for a table. But I don't know how often people feel the need to set *all* of them at once... Still, it might be worth continuing to support people poking values directly into the table; I just don't think we want to make that the official interface. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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
Re: [HACKERS] Interface for pg_autovacuum
Jim Nasby [EMAIL PROTECTED] writes: The only other thought that comes to mind is that such syntax will make it a *lot* more verbose to set all the options for a table. Which should surely make you wonder whether setting these options per-table is the most important thing to do... Arguing about syntax details is pretty premature, in my humble opinion. We don't have agreement yet about what options we need or what scope they should apply over. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interface for pg_autovacuum
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
Re: [HACKERS] Interface for pg_autovacuum
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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Interface for pg_autovacuum
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 [EMAIL PROTECTED] 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
Re: [HACKERS] Interface for pg_autovacuum
Jim Nasby [EMAIL PROTECTED] 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
[HACKERS] Interface for pg_autovacuum
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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
