Skip site navigation (1) Skip section navigation (2)

Quick patch: Display sequence owner

From: Josh Williams <joshwilliams(at)ij(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Quick patch: Display sequence owner
Date: 2008-12-07 03:56:52
Message-ID: 1228622212.10877.59.camel@godzilla.local.scalefeather.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi folks,

Was recently poked and reminded that this patch may be of interest to
the community.  It was mostly done as an academic exercise, just to see
how it works, and so it has a rather hackish feel.  The patch adds the
sequence owner, if available, to psql's \d <sequence> output, as
suggested in a recent thread:

http://archives.postgresql.org/pgsql-general/2008-11/msg01300.php

The patch adds a query against pg_depend, then fakes an extra column
"owned_by" in the output:

# \d tablename_columnname_seq
     Sequence "public.tablename_columnname_seq"
    Column     |   Type   |          Value           
---------------+----------+--------------------------
 sequence_name | name     | tablename_columnname_seq
 last_value    | bigint   | 1
 start_value   | bigint   | 1
 increment_by  | bigint   | 1
 max_value     | bigint   | 9223372036854775807
 min_value     | bigint   | 1
 cache_value   | bigint   | 1
 log_cnt       | bigint   | 1
 is_cycled     | boolean  | f
 is_called     | boolean  | f
 owned_by      | regclass | tablename


Now for the snags and additional thoughts:

The query against pg_depend looks for relations for which the sequence
is auto-dependent.  It wouldn't make any sense, but is it at all
possible for a sequence to auto-depend on something else?

An earlier version of the patch pulled the owning table and schema names
directly, rather than casting to regclass, so the schema name was always
shown.  Would this be preferable, in case there's some ambiguity in
similarly named tables between schemas?

I'd pondered briefly whether there should be a real attribute to
represent the sequence owner, just for display purposes.  But I'm
assuming that would present a big concurrency issue, as other
transactions would see the change on the sequence immediately while
pg_depend wouldn't be seen to change until committed.  That, and
ROLLBACK wouldn't work at all...

The column info query is getting messy.  Could probably clean that up a
bit if anyone thinks it'd be worth it?

- Josh Williams


Attachment: describe-sequence-owner-84a.patch
Description: text/x-patch (1.2 KB)

Responses

pgsql-hackers by date

Next:From: Asko OjaDate: 2008-12-07 08:09:30
Subject: Re: user-based query white list
Previous:From: Bruce MomjianDate: 2008-12-07 03:26:13
Subject: Re: BUG #4186: set lc_messages does not work

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group