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

Hi Steve,

Ideally once you update/install a new version, old files should be
overwritten. Not sure why there are some old files with your installation.
Yes. You should take up a base of sqlite database file.
Alternatively you can export server data using import/export servers from
the tools menu which creates a json file containing all server data.

Thanks,
Yogesh Mahajan
EnterpriseDB

On Mon, Jan 9, 2023 at 8:20 PM Stephen Todd Morrow <stm96(at)cornell(dot)edu>
wrote:

> Hi Yogesh. We’re running 6.18:
>
>
>
> 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
>
>
>
> Right, noted the missing typecast.
>
>
>
> Maybe artifacts from prior versions of pgAdmin are lingering on my syste
> and clashing with the current version? What I haven’t tried was a complete
> removal of everything, but I didn’t want to lose all my server connection
> details. But if I preserve the sqlite pgAdmin database file and restore it
> after a new install, I should be good to go, right?
>
>
>
> Thanks,
>
> Steve
>
>
>
> *From:* Yogesh Mahajan <yogesh(dot)mahajan(at)enterprisedb(dot)com>
> *Sent:* Monday, January 9, 2023 3:12 AM
> *To:* Stephen Todd Morrow <stm96(at)cornell(dot)edu>
> *Cc:* Akshay Joshi <akshay(dot)joshi(at)enterprisedb(dot)com>;
> 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 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
>
>
>
>
>
>