Some more information_schema issues
I looked through all the information_schema stuff, and found a few more
nits.
The CHECK_CONSTRAINTS view should use pg_get_constraintdef() function
rather than consrc, for the same reasons as psql should (I haven't fixed
the latter yet, but will soon).
There are several views that display pg_type.typname directly. I wonder
whether any of these ought to be using format_type() instead. It won't
matter for the views that only show domains, but several could
potentially show standard types. Don't we want the output to be
"character" rather than "bpchar"?
It would be a small efficiency boost to use UNION ALL rather than UNION
where possible.
"READ COMMITED" should be "READ COMMITTED" in sql_implementation_info.
In sql_sizing, MAXIMUM COLUMNS IN SELECT should be 1664
(MaxTupleAttributeNumber).
Several views get fixed pg_class OIDs like this:
AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
This is unsafe --- suppose a user creates a table named pg_class in one
of his own schemas? The SELECT would return multiple rows, causing a
runtime error. What I would recommend is coding these like
AND d.refclassid = 'pg_catalog.pg_class'::regclass
which is schema-safe and also rather more efficient, since the planner
will see this as a simple constant instead of a sub-query.
The ELEMENT_TYPES view doesn't work --- it returns zero rows. After
some fooling around I think it's a simple typo: the line
AND (n.nspname, x.objname, x.objtype, x.objtypeid) IN
should be
AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
regards, tom lane
I looked through all the information_schema stuff, and found a few more
nits.
I notice that most of the references in the information_schema.sql are
not schema-qualfied.
eg:
FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid
Shouldn't that be:
FROM (pg_catalog.pg_namespace ncon INNER JOIN pg_catalog.pg_constraint ..
Because what I'm concerned about is that if my personal schema (chriskl)
has a table called 'pg_catalog' or a function called '_pg_keysequal',
then if I go 'SELECT * FROM INFORMATIONS_SCHEMA.someview', then it will
break?
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
I notice that most of the references in the information_schema.sql are
not schema-qualfied.
They don't need to be, because the references will be resolved when the
views are parsed during initdb.
regards, tom lane
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
I notice that most of the references in the information_schema.sql are
not schema-qualfied.
They don't need to be, because the references will be resolved when the
views are parsed during initdb.
On second thought, you do have a point with regard to those newly-added
SQL functions. Names used within the texts of those functions need to
be fully qualified for safety.
regards, tom lane
Tom Lane writes:
The CHECK_CONSTRAINTS view should use pg_get_constraintdef() function
rather than consrc, for the same reasons as psql should (I haven't fixed
the latter yet, but will soon).
True. Btw., is there a particular value in pg_get_constraintdef always
printing double pairs of parentheses for CHECK constraints?
There are several views that display pg_type.typname directly. I wonder
whether any of these ought to be using format_type() instead. It won't
matter for the views that only show domains, but several could
potentially show standard types. Don't we want the output to be
"character" rather than "bpchar"?
typname is used in those contexts where the type name appears together
with a schema name. In those cases you cannot use the result of
format_type.
It would be a small efficiency boost to use UNION ALL rather than UNION
where possible.
Good idea.
"READ COMMITED" should be "READ COMMITTED" in sql_implementation_info.
In sql_sizing, MAXIMUM COLUMNS IN SELECT should be 1664
(MaxTupleAttributeNumber).Several views get fixed pg_class OIDs like this:
AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
This is unsafe
OK.
The ELEMENT_TYPES view doesn't work --- it returns zero rows. After
some fooling around I think it's a simple typo: the line
AND (n.nspname, x.objname, x.objtype, x.objtypeid) IN
should be
AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
OK.
--
Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
True. Btw., is there a particular value in pg_get_constraintdef always
printing double pairs of parentheses for CHECK constraints?
No, but it will require some restructuring of the code to get rid of it
safely (where "safely" is defined as "never omitting any parentheses
that *are* necessary"). For the moment I'm willing to live with the
ugliness. You could consider pretty-printing (pass true to
pg_get_constraintdef) if you think visual appeal is better than
assured correctness.
There are several views that display pg_type.typname directly. I wonder
whether any of these ought to be using format_type() instead.
typname is used in those contexts where the type name appears together
with a schema name. In those cases you cannot use the result of
format_type.
Okay, fair enough.
regards, tom lane
True. Btw., is there a particular value in pg_get_constraintdef always
printing double pairs of parentheses for CHECK constraints?No, but it will require some restructuring of the code to get rid of it
safely (where "safely" is defined as "never omitting any parentheses
that *are* necessary"). For the moment I'm willing to live with the
ugliness. You could consider pretty-printing (pass true to
pg_get_constraintdef) if you think visual appeal is better than
assured correctness.
We could check the first character of the definition, and if it isn't a
left parenthesis, then we add parentheses.
Chris