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 )