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

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 -

[image: Screenshot 2023-01-06 at 11.14.58 AM.png]

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
>
>
>
>
>
>