Thread: Immodest Proposal: pg_catalog.pg_ddl
Folks, I've been looking into ways to have a better idea--automatically--of what's been going on in a database from a DDL level, and here's what I've come up with so far. I'd much appreciate hearing suggestions and/or brickbats on this. The idea is to make a new table in pg_catalog called pg_ddl. This would have the structure CREATE TABLE pg_catalog.pg_ddl ( objoid oid NOT NULL, -- object's oid objddl TEXT NOT NULL, -- raw text of DDL objmodifiedTIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP -- pretty obvious ;) ); For any given object, it would contain all the DDL successfully executed + a timestamp, so it would be possible to get a history on any or all DB objects (modulo DROP/CREATE). How hard would it be to get the raw text of the DDL and hold onto it until the DDL either succeeds or fails? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
David Fetter <david@fetter.org> writes:
> The idea is to make a new table in pg_catalog called pg_ddl.
This seems rather poorly thought out --- I can't even tell whether your
intention is to make a log of past operations, or to provide a uniform
way to extract the current definition of every object. If the latter,
recording text won't do it. If the former, the notion that all DDL can
be uniquely keyed to one object OID is bogus, and I don't even see the
argument for doing it via a table rather than via the postmaster log.
regards, tom lane
On Tue, Dec 13, 2005 at 11:33:20PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > The idea is to make a new table in pg_catalog called pg_ddl. > > This seems rather poorly thought out --- I can't even tell whether > your intention is to make a log of past operations, Yes. > or to provide a uniform way to extract the current definition of > every object. If the latter, recording text won't do it. If the > former, the notion that all DDL can be uniquely keyed to one object > OID is bogus, What could it be keyed to, then? > and I don't even see the argument for doing it via a table rather > than via the postmaster log. Simple. Postmaster logs can roll over or otherwise be lost without damaging the DB. This would provide a non-volatile log of DDLs. It occurs to me that the creator's or in the case of ALTER, the modifier's, rolename and oid should be along. Thanks for the feedback :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 415 235 3778 Remember to vote!
David Fetter <david@fetter.org> writes:
> On Tue, Dec 13, 2005 at 11:33:20PM -0500, Tom Lane wrote:
>> and I don't even see the argument for doing it via a table rather
>> than via the postmaster log.
> Simple. Postmaster logs can roll over or otherwise be lost without
> damaging the DB. This would provide a non-volatile log of DDLs.
In that case you have to provide a pretty strong argument why everyone
should be forced to have a non-volatile log of DDLs. Or will there be
a way to turn it off? What about applications that, say, create and
delete tens of thousands of temp tables every day? What about security
issues (ie, who gets to read the log, or determine what gets into it)?
Is the log local to each database or global, and if local how do you
deal with DDL for shared objects (eg, tablespaces)? What features will
you provide for the inevitable need to truncate the log to keep its
space consumption within bounds?
I think the idea is pretty much a non-starter; it does little or nothing
that can't be done with the existing postmaster-log facility, and it
will add a whole lot of mechanism to do it. I also don't see why it
should be specifically tied to DDL. The idea of logging into a table
has come up before, but it's always been proposed in the form of putting
everything that goes to the postmaster log into a table. That seems to
me to be trying to build on the work we've already done, rather than
duplicating it.
regards, tom lane
Tom Lane wrote: > >>Simple. Postmaster logs can roll over or otherwise be lost without >>damaging the DB. This would provide a non-volatile log of DDLs. >> >> > >In that case you have to provide a pretty strong argument why everyone >should be forced to have a non-volatile log of DDLs. > > Conversely, why *only* DDL. As soon as we had this there would be a very strong demand to log DML. Maybe we need an optional asynch logging process as yet another member of our growing band of specialist background processes. What I would like to see is some builtin functions that give me the table's DDL, just as pg_dump does. Extra nice would be complementary functions that also give me skeleton select statements for each table or view. I used to use such facilities a lot in years gone by, along with c&p - maybe I'm just old-fashioned. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> What I would like to see is some builtin functions that give me the
> table's DDL, just as pg_dump does. Extra nice would be complementary
> functions that also give me skeleton select statements for each table or
> view.
Yeah, what I first thought David was proposing was a consolidated view
similar to pg_indexes, that could give you an up-to-date DDL definition
for anything in the system. This has been proposed in the past as a way
to migrate pg_dump functionality into the backend. I don't think it
will actually work for that (pg_dump needs more understanding of what
it's doing than just blindly copying complete CREATE commands) --- but
it still seems potentially useful for manual operations.
regards, tom lane
Tom Lane wrote: > David Fetter <david@fetter.org> writes: >> On Tue, Dec 13, 2005 at 11:33:20PM -0500, Tom Lane wrote: >>> and I don't even see the argument for doing it via a table rather >>> than via the postmaster log. > >> Simple. Postmaster logs can roll over or otherwise be lost without >> damaging the DB. This would provide a non-volatile log of DDLs. > > In that case you have to provide a pretty strong argument why everyone > should be forced to have a non-volatile log of DDLs. Or will there be > a way to turn it off? What about applications that, say, create and > delete tens of thousands of temp tables every day? What about system-event-driven triggers as a mechanism for this? That should make it simple for people to extend how they wish - e.g. - setup default preferences when new users are added - setuptemp.y tables at session start - monitor ddl (as David wanted) Now that we have sub-transactions, we could wrap the call to the trigger function so that errors didn't abort the user setup/login etc. There's been demand for this sort of thing fairly regularly - I'd probably use it myself. -- Richard Huxton Archonet Ltd
Tom Lane wrote: > David Fetter <david@fetter.org> writes: > >>On Tue, Dec 13, 2005 at 11:33:20PM -0500, Tom Lane wrote: >> >>>and I don't even see the argument for doing it via a table rather >>>than via the postmaster log. > > >>Simple. Postmaster logs can roll over or otherwise be lost without >>damaging the DB. This would provide a non-volatile log of DDLs. > > > In that case you have to provide a pretty strong argument why everyone > should be forced to have a non-volatile log of DDLs. Or will there be > a way to turn it off? What about applications that, say, create and > delete tens of thousands of temp tables every day? There were quite some proposals about additional triggers (on connect/disconnnect) around, I wonder if some kind of schema/database-level trigger could be used for DDL logging. Very vague idea, please rant now :-) Regards, Andreas
>>What I would like to see is some builtin functions that give me the >>table's DDL, just as pg_dump does. Extra nice would be complementary >>functions that also give me skeleton select statements for each table or >>view. > > > Yeah, what I first thought David was proposing was a consolidated view > similar to pg_indexes, that could give you an up-to-date DDL definition > for anything in the system. This has been proposed in the past as a way > to migrate pg_dump functionality into the backend. I don't think it > will actually work for that (pg_dump needs more understanding of what > it's doing than just blindly copying complete CREATE commands) --- but > it still seems potentially useful for manual operations. We have many pg_get_blahdef() functions already, but we really should flesh them all out so that they are available for every database object, eg: pg_get_tabledef() pg_get_domaindef() pg_get_functiondef() etc. That would also be cool because then I'd have an easy way of dumping individual objects from phpPgAdmin, or pgAdmin ,etc. Chris
> There were quite some proposals about additional triggers (on > connect/disconnnect) around, I wonder if some kind of > schema/database-level trigger could be used for DDL logging. Or, "global triggers" where you can have a trigger that is executed upon ANY DML or DDL... Chris
Christopher Kings-Lynne wrote: > >>What I would like to see is some builtin functions that give me the > >>table's DDL, just as pg_dump does. Extra nice would be complementary > >>functions that also give me skeleton select statements for each table or > >>view. > > > > > > Yeah, what I first thought David was proposing was a consolidated view > > similar to pg_indexes, that could give you an up-to-date DDL definition > > for anything in the system. This has been proposed in the past as a way > > to migrate pg_dump functionality into the backend. I don't think it > > will actually work for that (pg_dump needs more understanding of what > > it's doing than just blindly copying complete CREATE commands) --- but > > it still seems potentially useful for manual operations. > > We have many pg_get_blahdef() functions already, but we really should > flesh them all out so that they are available for every database object, eg: > > pg_get_tabledef() > pg_get_domaindef() > pg_get_functiondef() > > etc. > > That would also be cool because then I'd have an easy way of dumping > individual objects from phpPgAdmin, or pgAdmin ,etc. Functions added to existing TODO entry. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073