Re: pgAdmin error with new PG15 servers: argument of type 'Response' is not iterable

Hi Steve,

Could you please confirm the pgAdmin version where you are getting the
above error?
The Query pasted by you does not typecast 'a.deftype'. However the latest
pgAdmin uses 'a.deftype' value only after typecasting. Changes had been
done in 6.10 release.

Thanks,
Yogesh Mahajan
EnterpriseDB

On Fri, Jan 6, 2023 at 10:07 PM Stephen Todd Morrow <stm96(at)cornell(dot)edu>
wrote:

> Hi Akshay. Hopefully my prior response might give more insight. But here’s
> the output from one of our PG15 schemas:
>
>
>
> nsptyp | 3
>
> name | reftest
>
> oid | 18071
>
> acl | ref=UC/ref, ref test=UC/ref, =U/ref,
> select_any_table_role=U/ref, ro_any_table=U/ref, db_rundeck=U/ref,
> repl_role=U/ref
>
> namespaceowner | ref
>
> description |
>
> can_create | t
>
> tblacl | select_any_table_role=r/lab_dba
>
> seqacl |
>
> funcacl |
>
> typeacl |
>
> seclabels |
>
>
>
> Thanks,
>
> Steve
>
>
>
> *From:* Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>
> *Sent:* Friday, January 6, 2023 1:21 AM
> *Cc:* Stephen Todd Morrow <stm96(at)cornell(dot)edu>;
> pgadmin-support(at)postgresql(dot)org
> *Subject:* Re: pgAdmin error with new PG15 servers: argument of type
> 'Response' is not iterable
>
>
>
> Hi Stephen
>
>
>
> I am not able to reproduce the issue as well. Can you please provide the
> output of the below query:
>
>
>
> SELECT
> CASE
> WHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1
> WHEN (nspname LIKE E'pg\\_%') THEN 0
> ELSE 3 END AS nsptyp,
> nsp.nspname AS name,
> nsp.oid,
> pg_catalog.array_to_string(nsp.nspacl::text[], ', ') as acl,
> r.rolname AS namespaceowner, description,
> pg_catalog.has_schema_privilege(nsp.oid, 'CREATE') AS can_create,
> (SELECT pg_catalog.array_to_string(ARRAY(
> SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')
> FROM pg_catalog.pg_default_acl
> WHERE defaclobjtype = 'r' AND defaclnamespace = nsp.oid
> ), ', ')) AS tblacl,
> (SELECT pg_catalog.array_to_string(ARRAY(
> SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')
> FROM pg_catalog.pg_default_acl
> WHERE defaclobjtype = 'S' AND defaclnamespace = nsp.oid
> ), ', ')) AS seqacl,
> (SELECT pg_catalog.array_to_string(ARRAY(
> SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')
> FROM pg_catalog.pg_default_acl
> WHERE defaclobjtype = 'f' AND defaclnamespace = nsp.oid
> ), ', ')) AS funcacl,
> (SELECT pg_catalog.array_to_string(ARRAY(
> SELECT pg_catalog.array_to_string(defaclacl::text[], ', ')
> FROM pg_catalog.pg_default_acl
> WHERE defaclobjtype = 'T' AND defaclnamespace = nsp.oid
> ), ', ')) AS typeacl,
> (SELECT pg_catalog.array_agg(provider || '=' || label) FROM
> pg_catalog.pg_seclabels sl1 WHERE sl1.objoid=nsp.oid) AS seclabels
> FROM
> pg_catalog.pg_namespace nsp
> LEFT OUTER JOIN pg_catalog.pg_description des ON
> (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
> LEFT JOIN pg_catalog.pg_roles r ON (r.oid = nsp.nspowner)
> WHERE
> nsp.oid=*{OID}*::oid AND
> nspname NOT LIKE E'pg\\_%'
>
> ORDER BY 1, nspname;
>
>
>
> Replace the OID in bold with the appropriate OID of the schema.
>
>
>
> On Fri, Jan 6, 2023 at 11:17 AM Yogesh Mahajan <
> yogesh(dot)mahajan(at)enterprisedb(dot)com> wrote:
>
> Hi Steve,
>
>
>
> I am not able to reproduce the issue with the above DDL.
>
> Here is screen shot for pgadmin with similar DDL I tried -
>
>
>
>
>
>
>
> From where did you install pgAdmin?
>
> Also what is the operating system for postgresql server?
>
>
>
> Thanks,
>
> Yogesh Mahajan
>
> EnterpriseDB
>
>
>
>
>
> On Thu, Jan 5, 2023 at 10:09 PM Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> wrote:
>
> Sure thing Yogesh. This is extracted from a schema-only dump of schema
> p_atlas_mn:
>
>
>
> CREATE SCHEMA p_atlas_mn;
>
> ALTER SCHEMA p_atlas_mn OWNER TO p_atlas_mn;
>
> GRANT USAGE ON SCHEMA p_atlas_mn TO select_any_table_role;
>
> GRANT USAGE ON SCHEMA p_atlas_mn TO ro_any_table;
>
> ALTER DEFAULT PRIVILEGES FOR ROLE lab_dba IN SCHEMA p_atlas_mn GRANT
> SELECT ON TABLES TO select_any_table_role;
>
>
>
> Here’s the database DDL from pgAdmin (no problem getting this here):
>
>
>
> -- Database: sw2
>
>
>
> -- DROP DATABASE IF EXISTS sw2;
>
>
>
> CREATE DATABASE sw2
>
> WITH
>
> OWNER = postgres
>
> ENCODING = 'UTF8'
>
> LC_COLLATE = 'C'
>
> LC_CTYPE = 'C'
>
> TABLESPACE = pg_default
>
> CONNECTION LIMIT = -1
>
> IS_TEMPLATE = False;
>
>
>
> ALTER DATABASE sw2
>
> SET search_path TO "$user", public, ref, cur, map_layer_other, postgis;
>
>
>
> GRANT ALL ON DATABASE sw2 TO postgres;
>
>
>
> GRANT TEMPORARY, CONNECT ON DATABASE sw2 TO PUBLIC;
>
>
>
> GRANT CONNECT ON DATABASE sw2 TO pgwatch2;
>
>
>
> ALTER DEFAULT PRIVILEGES FOR ROLE
>
> GRANT ALL ON TABLES TO lab_dba;
>
>
>
> ALTER DEFAULT PRIVILEGES FOR ROLE
>
> GRANT SELECT ON TABLES TO ro_any_table;
>
>
>
> Thanks,
>
> Steve
>
>
>
> *From:* Yogesh Mahajan <yogesh(dot)mahajan(at)enterprisedb(dot)com>
> *Sent:* Thursday, January 5, 2023 10:24 AM
> *To:* Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> *Cc:* pgadmin-support(at)postgresql(dot)org
> *Subject:* Re: pgAdmin error with new PG15 servers: argument of type
> 'Response' is not iterable
>
>
>
> Hi Steve,
>
>
>
> Could you please provide DDL for schema & database?
>
> I could not reproduce the issue. Here is my schema DDL -
>
>
>
> CREATE SCHEMA IF NOT EXISTS test123
> AUTHORIZATION postgres;
>
> GRANT CREATE ON SCHEMA test123 TO postgres;
> GRANT USAGE ON SCHEMA test123 TO postgres WITH GRANT OPTION;
>
> GRANT CREATE ON SCHEMA test123 TO yogesh;
>
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA test123
> GRANT INSERT, SELECT ON TABLES TO postgres;
>
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA test123
> GRANT USAGE ON TYPES TO yogesh;
>
>
>
>
>
> Thanks,
>
> Yogesh Mahajan
>
> EnterpriseDB
>
>
>
>
>
> On Thu, Jan 5, 2023 at 8:34 PM Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> wrote:
>
> Hi Yogesh. Should’ve clarified that this is for any & all schemas.
>
>
>
> Not sure if this image attachment will come through, but if you click on
> any of the 79 schemas for this database in the browser when the SQL tab is
> active, you get the error:
>
>
>
>
>
> Ditto if you right click on the schema and select “CREATE Script”.
>
>
>
> This goes for any schema in any other database on this PG15 server, and
> our other PG15 servers. (I asked our dbeaver users to do grab the schema
> DDL using that tool, and they had no problem.) So at this point it seems
> limited to pgAdmin.
>
>
>
> And as I check now, I see that right clicking on the schema and selecting
> “Properties” returns an “Object of type Response is not JSON serializable”.
> Again, for any schema, and only specific when connected to PG15 servers.
> Not sure if this is related, or something else. Again, we do not get this
> error when connecting to PG13 or 14 servers.
>
>
>
> Query output:
>
>
>
> SELECT defaclobjtype, pg_catalog.aclexplode(defaclacl) as acl FROM
> pg_catalog.pg_namespace nsp LEFT OUTER JOIN pg_catalog.pg_default_acl dacl
> ON (dacl.defaclnamespace = nsp.oid) WHERE
> nsp.oid='p_atlas_mn'::regnamespace::oid;
>
>
>
> defaclobjtype | acl
>
> --------------+-------------------------
>
> r | (25142,3324345,SELECT,f)
>
>
>
> \do ||
>
>
>
> List of operators
>
> Schema | Name | Left arg type | Right arg type | Result
> type | Description
>
>
> -----------+------+--------------------+--------------------+--------------------+------------------------------------
>
> pg_catalog | || | anycompatible | anycompatiblearray |
> anycompatiblearray | prepend element onto front of array
>
> pg_catalog | || | anycompatiblearray | anycompatible |
> anycompatiblearray | append element onto end of array
>
> pg_catalog | || | anycompatiblearray | anycompatiblearray |
> anycompatiblearray | concatenate
>
> pg_catalog | || | anynonarray | text |
> text | concatenate
>
> pg_catalog | || | bit varying | bit varying | bit
> varying | concatenate
>
> pg_catalog | || | bytea | bytea |
> bytea | concatenate
>
> pg_catalog | || | jsonb | jsonb |
> jsonb | concatenate
>
> pg_catalog | || | text | anynonarray |
> text | concatenate
>
> pg_catalog | || | text | text |
> text | concatenate
>
> pg_catalog | || | tsquery | tsquery |
> tsquery | OR-concatenate
>
> pg_catalog | || | tsvector | tsvector |
> tsvector | concatenate
>
>
>
> Please let me know if you need more details.
>
>
>
> Thanks,
>
> Steve
>
>
>
>
>
>
>
> *From:* Yogesh Mahajan <yogesh(dot)mahajan(at)enterprisedb(dot)com>
> *Sent:* Thursday, January 5, 2023 4:22 AM
> *To:* Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> *Cc:* pgadmin-support(at)postgresql(dot)org
> *Subject:* Re: pgAdmin error with new PG15 servers: argument of type
> 'Response' is not iterable
>
>
>
> Hi,
>
>
>
> Could you please provide DDL for schema? and Can you please share output
> for below queries -
>
>
>
> 1.SELECT defaclobjtype, pg_catalog.aclexplode(defaclacl) as acl FROM
> pg_catalog.pg_namespace nsp LEFT OUTER JOIN pg_catalog.pg_default_acl dacl
> ON (dacl.defaclnamespace = nsp.oid) WHERE nsp.oid=<SCHEMA_OID>::oid
>
> 2.\do ||
>
>
>
> Thanks,
>
> Yogesh Mahajan
>
> EnterpriseDB
>
>
>
>
>
> On Thu, Jan 5, 2023 at 1:45 AM Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> wrote:
>
> Hello All and Happy 2023.
>
>
>
> We’re in the process of upgrading our Postgres instances from 13/14 to
> 15.1, and have done a few already. However, pgAdmin is tripping this error
> when attempting to extract DDL for a schema…but only in the new PG15
> databases. It works just fine with the other versions. This seems to be the
> only gotcha we’ve encountered at this point.
>
>
>
> So far this has eluded me, and searches have been unsuccessful.
>
>
>
> Details:
>
>
>
> Version 6.18
>
> Application Mode Desktop
>
> NW.js Version 0.71.0
>
> Browser Chromium 108.0.5359.95
>
> Operating System Windows-10-10.0.19044-SP0
>
>
>
> The error shows up in the pgAdmin log as such:
>
>
> ------------------------------
>
>
>
> 2023-01-04 14:47:36,313: ERROR pgadmin: argument of type 'Response'
> is not iterable
>
> Traceback (most recent call last):
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\python\lib\site-packages\flask\app.py", line 1517, in
> full_dispatch_request
>
> rv = self.dispatch_request()
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\python\lib\site-packages\flask\app.py", line 1503, in dispatch_request
>
> return
> self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\python\lib\site-packages\flask\views.py", line 84, in view
>
> return current_app.ensure_sync(self.dispatch_request)(*args, **kwargs)
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\web\pgadmin\browser\utils.py", line 311, in dispatch_request
>
> return method(*args, **kwargs)
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\web\pgadmin\browser\server_groups\servers\databases\schemas\__init__.py",
> line 233, in wrap
>
> return f(*args, **kwargs)
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\web\pgadmin\browser\server_groups\servers\databases\schemas\__init__.py",
> line 930, in sql
>
> self.format_request_acls(data)
>
> File "C:\Users\stm\AppData\Local\Programs\pgAdmin
> 4\v6\web\pgadmin\browser\server_groups\servers\databases\schemas\__init__.py",
> line 355, in format_request_acls
>
> if aclcol in data:
>
> TypeError: argument of type 'Response' is not iterable
>
>
> ------------------------------
>
>
>
> And it shows up in the PG log as such (some details obfuscated):
>
>
>
> 2023-01-04 14:47:36.319
> EST,"<role>","<instance>",53334,"<ip>",63b5d7d7.d056,4,"SELECT",2023-01-04
> 14:47:35 EST,113/106,0,ERROR,42725,"operator is not unique: unknown ||
> ""char""",,"Could not choose a best candidate operator. You might need to
> add explicit type casts.",,,,"SELECT
>
>
>
> I feel like I’m missing something basic here, but it hasn’t hit me yet.
> Hoping somebody here can set me straight. Please do let me know if you need
> more details.
>
>
>
> Appreciate it,
>
> Steve
>
>
>
>
>
>