Thread: 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
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> We could check the first character of the definition, and if it isn't a
> left parenthesis, then we add parentheses.
And we would be wrong. Consider(a < 0) and (b > 0)
regards, tom lane