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
Index: src/bin/psql/describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.188
diff -r1.188 describe.c
917c917
< seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values));
---
> seq_values = pg_malloc_zero((SEQ_NUM_COLS+2) * sizeof(*seq_values));
922a923,939
>
> printfPQExpBuffer(&buf,
> "SELECT d.refobjid::regclass\n"
> "FROM pg_catalog.pg_depend d\n"
> "WHERE d.deptype = 'a' AND d.objid = '%s'",
> oid);
>
> result = PSQLexec(buf.data, false);
> if (!result)
> goto error_return;
>
> if (PQntuples(result))
> seq_values[10] = pg_strdup(PQgetvalue(result, 0, 0));
> else
> seq_values[10] = "";
>
> PQclear(result);
940c957,966
< appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
---
> /* For sequences we'll 'fake' an additional column to show the owning relation */
> if (tableinfo.relkind == 'S')
> {
> appendPQExpBuffer(&buf, "\nUNION SELECT 'owned_by', 'regclass', NULL, true, 11");
> if (verbose)
> appendPQExpBuffer(&buf, ", 'p', 'Owning relation'");
> appendPQExpBuffer(&buf, "\nORDER BY attnum");
> }
> else
> appendPQExpBuffer(&buf, "\nORDER BY a.attnum");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
