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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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 Content-Type Size
describe-sequence-owner-84a.patch text/x-patch 1.2 KB

Responses

Browse pgsql-hackers by date

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