On Sat, 2006-06-03 at 13:43 -0400, Michael Bayer wrote:
> we've had a lot of issues with Postgres and using
> information_schema. whereas MySQL's information_schema support was
> pointless, Postgres mostly works, but performs very slowly unless you
> VACUUM your database every hour. we've had reports of primary keys
> not being reflected for tables across user names as well.
The information schema is implemented as a set of views on
system relations. I suspect vacuuming every hour is not really
the issue, unless you are doing a lot of metadata changes.
A "VACUUM FULL ANALYZE" by a database superuser is probably
a good thing once your table metadata settles down. This
is something the DBA would take care of, one of the reasons
that implementation programmers aren't usually permitted
to create ad hock changes to tables.
As to the reason that some attributes are only visible to
relation owners look for the "u.usesysid = c.relowner" and
the subsequent criteria in the definition of the view.
So it is on purpose. I would guess that it meets some security
criteria in the standard since they actually went to the trouble
of adding it in. One question to ask might be are people relying
on being DB superusers or were specific grants made? (examine
the output of \z from psql). The information schema doesn't
take into account DB superusers:
sat=# \d information_schema.columns
View "information_schema.columns"
Column | Type |
Modifiers
--------------------------+------------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
ordinal_position | information_schema.cardinal_number |
column_default | information_schema.character_data |
is_nullable | information_schema.character_data |
data_type | information_schema.character_data |
character_maximum_length | information_schema.cardinal_number |
character_octet_length | information_schema.cardinal_number |
numeric_precision | information_schema.cardinal_number |
numeric_precision_radix | information_schema.cardinal_number |
numeric_scale | information_schema.cardinal_number |
datetime_precision | information_schema.cardinal_number |
interval_type | information_schema.character_data |
interval_precision | information_schema.character_data |
character_set_catalog | information_schema.sql_identifier |
character_set_schema | information_schema.sql_identifier |
character_set_name | information_schema.sql_identifier |
collation_catalog | information_schema.sql_identifier |
collation_schema | information_schema.sql_identifier |
collation_name | information_schema.sql_identifier |
domain_catalog | information_schema.sql_identifier |
domain_schema | information_schema.sql_identifier |
domain_name | information_schema.sql_identifier |
udt_catalog | information_schema.sql_identifier |
udt_schema | information_schema.sql_identifier |
udt_name | information_schema.sql_identifier |
scope_catalog | information_schema.sql_identifier |
scope_schema | information_schema.sql_identifier |
scope_name | information_schema.sql_identifier |
maximum_cardinality | information_schema.cardinal_number |
dtd_identifier | information_schema.sql_identifier |
is_self_referencing | information_schema.character_data |
View definition:
SELECT current_database()::information_schema.sql_identifier AS
table_catalog, nc.nspname::information_schema.sql_identifier AS
table_schema, c.relname::information_schema.sql_identifier AS
table_name, a.attname::information_schema.sql_identifier AS column_name,
a.attnum::information_schema.cardinal_number AS ordinal_position,
CASE
WHEN u.usename = "current_user"() THEN ad.adsrc
ELSE NULL::text
END::information_schema.character_data AS column_default,
CASE
WHEN a.attnotnull OR t.typtype = 'd'::"char" AND
t.typnotnull THEN 'NO'::text
ELSE 'YES'::text
END::information_schema.character_data AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = -1 THEN
'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN
format_type(t.typbasetype, NULL::integer)
ELSE 'USER-DEFINED'::text
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = -1 THEN
'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN
format_type(a.atttypid, NULL::integer)
ELSE 'USER-DEFINED'::text
END
END::information_schema.character_data AS data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
t.*), information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS numeric_scale,
information_schema._pg_datetime_prec
ision(information_schema._pg_truetypid(a.*, t.*),
information_schema._pg_truetypmod(a.*,
t.*))::information_schema.cardinal_number AS datetime_precision,
NULL::information_schema.character_data::information_schema.character_data AS
interval_type,
NULL::information_schema.character_data::information_schema.character_data AS
interval_precision,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
character_set_catalog,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
character_set_schema,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
character_set_name,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
collation_catalog,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
collation_schema,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::"char" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name,
current_database()::information_schema.sql_identifier AS udt_catalog,
COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS
udt_schema, COALESCE(bt.typname,
t.typname)::information_schema.sql_identifier AS udt_name,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
scope_catalog,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
scope_schema,
NULL::information_schema.sql_identifier::information_schema.sql_identifier AS
scope_name,
NULL::information_schema.cardinal_number::information_schema.cardinal_number AS
maximum_cardinality, a.attnum::information_schema.sql_identifier AS
dtd_identifier,
'NO'::information_schema.character_data::information_schema.character_data AS
is_self_referencing
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum =
ad.adnum, pg_class c, pg_namespace nc, pg_user u, pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype =
'd'::"char" AND t.typbasetype = bt.oid
WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND u.usesysid =
c.relowner AND nc.oid = c.relnamespace AND a.attnum > 0 AND NOT
a.attisdropped AND (c.relkind = 'r'::"char" OR c.relkind = 'v'::"char")
AND (u.usename = "current_user"() OR has_table_privilege(c.oid,
'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR
has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid,
'REFERENCES'::text));
For column usage information only the table owner is permitted.
(note r.relowner = u.usesysid AND u.usename = "current_user"()):
sat=# \d information_schema.key_column_usage
View "information_schema.key_column_usage"
Column | Type | Modifiers
--------------------+------------------------------------+-----------
constraint_catalog | information_schema.sql_identifier |
constraint_schema | information_schema.sql_identifier |
constraint_name | information_schema.sql_identifier |
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
ordinal_position | information_schema.cardinal_number |
View definition:
SELECT current_database()::information_schema.sql_identifier AS
constraint_catalog, nc.nspname::information_schema.sql_identifier AS
constraint_schema, c.conname::information_schema.sql_identifier AS
constraint_name, current_database()::information_schema.sql_identifier
AS table_catalog, nr.nspname::information_schema.sql_identifier AS
table_schema, r.relname::information_schema.sql_identifier AS
table_name, a.attname::information_schema.sql_identifier AS column_name,
pos.n::information_schema.cardinal_number AS ordinal_position
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
pg_constraint c, pg_user u, information_schema._pg_keypositions() pos(n)
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND r.oid =
c.conrelid AND nc.oid = c.connamespace AND c.conkey[pos.n] = a.attnum
AND NOT a.attisdropped AND (c.contype = 'p'::"char" OR c.contype =
'u'::"char" OR c.contype = 'f'::"char") AND r.relkind = 'r'::"char" AND
r.relowner = u.usesysid AND u.usename = "current_user"();
> what does the list think of dumping information_schema usage
> altogther and using PG's own schema tables for reflection ?
The problem with this of course will be that it can/will
change from release to release. And since you'll still be
accessing the metadata tables it will still require the
"VACUUM FULL ANALYZE" step after many metadata operations.
Our approach was to write a python program, using DBAPI, to
create the python files with the class definitions.
The program is only run by the database owner which is
always different than any applications that perform
data operations.
The DB tables are always and only defined by an external
entity diagram (logical data model and physical schema).
Users of the tables, particularly web applications, are
only granted the minimum SQL privileges necessary for their
tasks. The capability to execute DDL operations would
specifically be prohibited. It is only in small personal
type projects or, at most, initial development that that type
of interaction would occur. For most deployed applications
the "db.create(table)" would/should never (for security
reasons) be possible.
HTH,
William.
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
