Re: Pet Peeves?

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.

pg_depend is actually pretty boring, you would see the same stuff if
you just did a DROP foo RESTRICT after all. I am finding that I'm
really wanting depth first searches which would be easier to read.
That would be interesting direction to head.

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.

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

--
greg