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@fetter.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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