Thread: Immodest Proposal: pg_catalog.pg_ddl

Immodest Proposal: pg_catalog.pg_ddl

From
David Fetter
Date:
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!


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Tom Lane
Date:
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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
David Fetter
Date:
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!


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Tom Lane
Date:
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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Andrew Dunstan
Date:

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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Tom Lane
Date:
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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Richard Huxton
Date:
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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Andreas Pflug
Date:
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


Re: Immodest Proposal: pg_catalog.pg_ddl

From
Christopher Kings-Lynne
Date:
>>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



Re: Immodest Proposal: pg_catalog.pg_ddl

From
Christopher Kings-Lynne
Date:
> 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



Re: Immodest Proposal: pg_catalog.pg_ddl

From
Bruce Momjian
Date:
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