Re: Pet Peeves?

On Tue, Feb 03, 2009 at 05:48:51PM +0000, Greg Stark wrote:
> On Thu, Jan 29, 2009 at 5:43 PM, David Fetter <david(at)fetter(dot)org> wrote:
> >>
> >> > * CTEs not yet integrated into the adjacency lists in pg_catalog,
> >> > etc.
> >>
> >> I'm not sure what you're referring to here either.
> >
> > The DAG structures in pg_depend leap to mind. There's no view that
> > shows the actual dependencies, except in the sense of, "Here's the
> > edges. Figure it out for yourself."
>
> I'm trying to write some recursive queries for pg_depend and pg_locks.
> I think if we come up with some nice ones we might want to add them to
> the system views.

Would this be a good time to revisit the idea of a pg_system_views
schema?

> pg_depend is actually pretty boring, you would see the same stuff if
> you just did a DROP foo RESTRICT after all.

Ass-u-me'ing that you have DDL permissions, which the vast majority of
roles should not.

> I am finding that I'm really wanting depth first searches which
> would be easier to read. That would be interesting direction to
> head.

Depth-first searches are pretty easy to arrange with arrays. :)

> pg_locks would be a *lot* more interesting imho. It's awfully hard
> to decipher the pg_locks table and find the important information
> buried in lots of extraneous minor locks which aren't blocking
> anything.
>
> However I'm finding it really hard to write anything useful for
> pg_locks. It seems we're missing a lot of basic info in pg_locks
> and basic infrastructure to make sense of it.
>
> Notably, there's no indication of which lock wait queue the
> ungranted locks are in. That means to find out what's blocking a
> lock would require comparing every other lock to it and deciding
> whether it conflicts.

Interesting :)

> I haven't thought hard about the pros and cons of adding more info
> to pg_locks versus implementing redundant logic in SQL to mirror C
> code. Neither seems terribly enticing offhand.
>
> I wonder if anybody else has already implemented something like
> lock_conflicts()?

Dunno. Could such a thing live in userland, or would it have to be
compiled in?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate