This page provides information about late-binding views in the database, on a per-column basis, or it would, if the system table function providing this information worked correctly, either in terms of performance or functionality.
The function which provides information about the columns in
late-binding views,
pg_catalog.pg_get_late_binding_view_cols(), returns rows
only for valid late-binding views and so validates views, and in doing
so, turns out to have fatal performance problems (for that, read
“freezes up”) when the number and complexity of views becomes high
enough.
Also, anyway, the function ignores syslog unrestricted
and the new Redshift-style privilege
access system table, and so users can see only their own
rows, or, if they are superuser, all rows, so although I can see all
late-binding views in pg_class, if I’m not admin (and the
user I use is not) then I can only see rows for my own late-binding
views anyway, and the user I use had none :-)
As such, this page is currently a placeholder; I’m hoping one day at least the permissions issue is fixed and so the page can work.
If you’re desperate to see column information, you need to make your Redshift Workbench user a super user, or you need to log into the Redshift Workbench as the owner of the views you wish to examine.
| Name | Type |
|---|---|
| schema_id | int8 |
| schema | varchar |
| view_id | int8 |
| view | varchar |
| column_ordinal | int2 |
| column | varchar |
| data_type_id | int8 |
| base_data_type_name | varchar |
| data type | varchar |
The schema ID. This column is emitted in CSV exports only.
The schema name.
The view ID. This column is emitted in CSV exports only.
Surprisingly, view IDs turn out to be unique across all databases.
The view name.
The column ordinal. This column is emitted in CSV exports only.
Column ordinals are unique to a single table.
In pg_attribute, ordinals for user columns count from 1,
system columns are negative.
In stv_blocklist, ordinals for user columns count from
0, and system columns have a rather extraordinary numbering, in that
they are the numbers after the final user column, which is to
say, the system column numbers vary by table.
(I believe stv_blocklist internally is using unsigned
variables, in part from this numbering issue, but also because I’ve been
able to make num_values overflow, and when it overflows, it
looks like an unsigned value which has wrapped and is then having its
bit pattern interpreted as a signed value, where Redshift data types are
all signed.)
In pg_attribte, you see quite a number of system
columns, but in fact most vestigial, left-overs from Postgres, and do
not exist on disk. Only three system columns are actually in use, which
have the ordinals -2, -8 and -9, being the row ID, MVCC insert
transaction ID and MVCC delete transaction ID, although offhand I cannot
remember which is which (although I do recall it’s not the order you’d
expect - I have a feeling -8 is the row ID).
The column name.
The data type ID. This column is emitted in CSV exports only.
This is the base data type name, which is to say, the data type name but sans adornment; no length, or precision, or scale. Just the name.
This is the full data type name, which is to say, the data type name with adornment; showing length, or precision and scale.