Multi Select and Script tables gives exception in PostgreSQL 8.2
To reproduce:
1. Open ADS and navigate to postgresql 8.2.4 server at 10.168.11.37
2. Go to the ivan schema, expand the Tables node. Multi select a few tables to Query Window and you get the below exception
3. Same error if you multi select and script Views
org.postgresql.util.PSQLException: ERROR: syntax error at or near "oid"
Position: 408
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:372)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252)
at \\...\\ .\\हिñçêČάй語简??한\\.sꄎꌚ᠗⢸ 2 for.executeQuery(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.lꋙᚾꄮꐋ.a(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.lꋙᚾꄮꐋ.f(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.Aꐧꄨꈒ1 Boolean.a(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.Aꐧꄨꈒ1 Boolean.a(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.Mᚯᡓꁾ double.a(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.d⣖̍ꌯ⣨ volatile 5.c(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.d⣖̍ꌯ⣨ volatile 5.b(Unknown Source)
at \\...\\ .\\हिñçêČάй語简??한\\.d⣖̍ꌯ⣨ volatile 5.run(Unknown Source)
also get '"org.postgresql.util.PSQLException: ERROR: syntax error at or near "oid"
Position: 343'
for
Users
Groups
with Script Object to Window ( single or multiselect )
also get it if using right click "Table Properties" on a table and then click on the Permissions tab of the Table properties
my SQL Log shows this query as the one that generated the error
SELECT ((grantee.name)::character varying) AS grantee, ((nc.nspname)::character varying) AS table_schema, ((c.relname)::character varying) AS table_name, (pr."type") AS privilege_type, ( CASE WHEN aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr. "type", true)) THEN 'YES'::text ELSE 'NO'::text END) AS is_grantable, ('NO') AS with_hierarchy FROM pg_class c, pg_namespace nc, ( select usesysid oid from pg_user union select grosysid oid from pg_group) u_grantor, (( select usesysid oid, usename rolname from pg_user union select grosysid oid, groname rolname from pg_group) UNION ALL SELECT (0)::oid AS oid, 'PUBLIC' ) grantee(oid, name) , ((((((((((((( SELECT 'SELECT') UNION ALL ( SELECT 'DELETE'))) UNION ALL ( SELECT 'INSERT'))) UNION ALL ( SELECT 'UPDATE'))) UNION ALL ( SELECT 'REFERENCES'))) UNION ALL ( SELECT 'RULE'))) UNION ALL ( SELECT 'TRIGGER'))) pr("type") WHERE (( ((c.relnamespace = nc.oid) AND (c.relkind = 'r'::"char") ) AND aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr."type", false))) AND (nc.nspname = 'public' AND c.relname = 'jontable'));
also get it if using right click "Table Properties" on a table and then click on the Permissions tab of the Table properties
my SQL Log shows this query as the one that generated the error
SELECT ((grantee.name)::character varying) AS grantee, ((nc.nspname)::character varying) AS table_schema, ((c.relname)::character varying) AS table_name, (pr."type") AS privilege_type, ( CASE WHEN aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr. "type", true)) THEN 'YES'::text ELSE 'NO'::text END) AS is_grantable, ('NO') AS with_hierarchy FROM pg_class c, pg_namespace nc, ( select usesysid oid from pg_user union select grosysid oid from pg_group) u_grantor, (( select usesysid oid, usename rolname from pg_user union select grosysid oid, groname rolname from pg_group) UNION ALL SELECT (0)::oid AS oid, 'PUBLIC' ) grantee(oid, name) , ((((((((((((( SELECT 'SELECT') UNION ALL ( SELECT 'DELETE'))) UNION ALL ( SELECT 'INSERT'))) UNION ALL ( SELECT 'UPDATE'))) UNION ALL ( SELECT 'REFERENCES'))) UNION ALL ( SELECT 'RULE'))) UNION ALL ( SELECT 'TRIGGER'))) pr("type") WHERE (( ((c.relnamespace = nc.oid) AND (c.relkind = 'r'::"char") ) AND aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr."type", false))) AND (nc.nspname = 'public' AND c.relname = 'jontable'));
extractobjectpermissions sql statement for postgresql had incompatibilities for older versions. fixed sql
extractobjectpermissions sql statement for postgresql had incompatibilities for older versions. fixed sql
Issue #9092 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build 13.0.1-12 |
No time estimate |
also get '"org.postgresql.util.PSQLException: ERROR: syntax error at or near "oid"
Position: 343'
for
Users
Groups
with Script Object to Window ( single or multiselect )