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
objmodified TIMESTAMP 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!

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

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

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

http://archives.postgresql.org

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!

---------------------------(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

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

---------------------------(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

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

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

http://archives.postgresql.org

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

---------------------------(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

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
- setup temp.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

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

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

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

>>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


---------------------------(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

> 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


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

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