Professional Web Applications Themes

Problems with information_schema - PostgreSQL / PGSQL

Hi all I just copied a rather complex application database together with all tables and triggers to 8.0.0RC1 running under windows (the original 7.4 database still runs on linux). I migrated everyting using pg_dump and then executing the resulting scripts via pgsql. Most things work as expected until now, except for the following problem. My application uses some functions that use the information_schema. Now these functions seem to fail. Further ysis reveals that some of the dictionary views (e.g. information_schema.table_constraints) always give empty result sets. I can see several differences between 7.4 and 8.0RC1, mainly with schema-qualifiyng all names. Below ...

  1. #1

    Default Problems with information_schema

    Hi all

    I just copied a rather complex application database together with all tables and triggers to 8.0.0RC1 running under windows (the original 7.4 database still runs on linux). I migrated everyting using pg_dump and then executing the resulting scripts via pgsql.

    Most things work as expected until now, except for the following problem.

    My application uses some functions that use the information_schema. Now these functions seem to fail. Further ysis reveals that some of the dictionary views (e.g. information_schema.table_constraints) always give empty result sets.

    I can see several differences between 7.4 and 8.0RC1, mainly with schema-qualifiyng all names. Below are the definitions I find in the view "table_constraints".

    Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet?

    Best regards
    --Marcel


    example of diffs in view definition (as reported by pgadmin III 1.2.0 final, Nov 29, 2004):

    In 8.0.0RC1:

    CREATE OR REPLACE VIEW information_schema.table_constraints AS
    SELECT current_database()::information_schema.sql_identif ier 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_identif ier AS table_catalog, nr.nspname::information_schema.sql_identifier AS table_schema, r.relname::information_schema.sql_identifier AS table_name,
    CASE c.contype
    WHEN 'c'::"char" THEN 'CHECK'::text
    WHEN 'f'::"char" THEN 'FOREIGN KEY'::text
    WHEN 'p'::"char" THEN 'PRIMARY KEY'::text
    WHEN 'u'::"char" THEN 'UNIQUE'::text
    ELSE NULL::text
    END::information_schema.character_data AS constraint_type,
    CASE
    WHEN c.condeferrable THEN 'YES'::text
    ELSE 'NO'::text
    END::information_schema.character_data AS is_deferrable,
    CASE
    WHEN c.condeferred THEN 'YES'::text
    ELSE 'NO'::text
    END::information_schema.character_data AS initially_deferred
    FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u
    WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"();

    ALTER TABLE information_schema.table_constraints OWNER TO postgres;
    GRANT ALL ON TABLE information_schema.table_constraints TO postgres;
    GRANT SELECT ON TABLE information_schema.table_constraints TO public;

    in 7.4:

    CREATE OR REPLACE VIEW information_schema.table_constraints AS
    SELECT current_database()::character varying::sql_identifier AS constraint_catalog, nc.nspname::character varying::sql_identifier AS constraint_schema, c.conname::character varying::sql_identifier AS constraint_name, current_database()::character varying::sql_identifier AS table_catalog, nr.nspname::character varying::sql_identifier AS table_schema, r.relname::character varying::sql_identifier AS table_name,
    CASE
    WHEN c.contype = 'c'::"char" THEN 'CHECK'::text
    WHEN c.contype = 'f'::"char" THEN 'FOREIGN KEY'::text
    WHEN c.contype = 'p'::"char" THEN 'PRIMARY KEY'::text
    WHEN c.contype = 'u'::"char" THEN 'UNIQUE'::text
    ELSE NULL::text
    END::character_data AS constraint_type,
    CASE
    WHEN c.condeferrable THEN 'YES'::text
    ELSE 'NO'::text
    END::character_data AS is_deferrable,
    CASE
    WHEN c.condeferred THEN 'YES'::text
    ELSE 'NO'::text
    END::character_data AS initially_deferred
    FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u
    WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"();

    ALTER TABLE information_schema.table_constraints OWNER TO postgres;
    GRANT ALL ON TABLE information_schema.table_constraints TO postgres WITH GRANT OPTION;
    GRANT SELECT ON TABLE information_schema.table_constraints TO public;


    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Marcel Gsteiger Guest

  2. #2

    Default Re: Problems with information_schema

    "Marcel Gsteiger" <Marcel.Gsteigermilprog.ch> writes:
    > Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet?
    AFAIK all the information_schema changes since 7.4 are quite
    intentional. Rather than pointing out that it's changed, you need to
    show us an example where you think the new behavior is wrong.

    (And please do so ASAP, because if 8.0 goes final next week, it'll be
    quite hard to fix later ...)

    regards, tom lane

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Tom Lane Guest

Similar Threads

  1. div problems ?
    By Martin Bean in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: January 3rd, 08:57 AM
  2. Problems when using Net::MSN 1.022
    By hansyin@gmail.com in forum PERL Modules
    Replies: 1
    Last Post: May 5th, 02:58 AM
  3. View definition truncated in information_schema
    By Eric E in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 27th, 07:28 PM
  4. Replies: 0
    Last Post: September 22nd, 02:18 PM
  5. I having problems with IIS
    By Terry Murray in forum ASP Database
    Replies: 9
    Last Post: August 24th, 04:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139