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