Invalid input syntax for type inet

[wawilation]

In the gather_report.sql file, this error happens: psql:gather_report.sql:275: ERROR: invalid input syntax for type inet: "/"

It happens on the tblhba WITH code block, at this point: (addr||'/'||cidr_mask)::inet network_block

My workaround was: set_masklen(addr::inet, masklen(mask::inet)) AS network_block

[jobinau]

Thank you @wawilation for reporting the problem and suggesting the fix.
if you don't mind, could you please share a sample pg_hba rule which can trigger this error ?

[jobinau]

@wawilation
Please share a sample pg_hba rule to reproduce the problem, if you can.
Thanks in advance.

[wawilation]

Hello, sorry for the delay in responding.

Please allow me to provide the sample in a few days.

[jobinau]

@wawilation , Looking forward to see that test case.

Hello, sorry for the delay in responding.

No worries. Thank you for responding.
Cheers!

[wawilation]

Hi @jobinau ,

I tested on Postgres CE 17 and I didn't encounter the error.

I failed to mention that my prior test was on EDB Advanced Server 15.

I tested again today on EDB Advanced Server 17, and the error recurred:

[wawilation]

pg_hba.conf of Postgres CE 17:

pg_hba.conf of EDB Advanced Server 17:

[jobinau]

Ahh... I see, EDB Server handles NULL differently than PostgreSQL

SELECT NULL || '/';

On EDB server returns "/". Where as PostgreSQL returns NULL, which is right in my opinion.
Let me think/check what we can do about it.

[jobinau]

Hi @wawilation ,
I was checking with one of my friend who is well experienced in EDB Server.
As per him, EDB server will be behaving like Oracle database rather than PostgreSQL, by default. which is called "Redwood" mode.
Its possible to switch to PostgreSQL behavior by switching off the Redwood mode.
I guess you have a dedicated database pg_gather_results for pg_gather data, if yes, switching off the Redwood mode for that database alone would be good fix, because generally I won't be testing for Oracle behavior.

ALTER DATABASE pg_gather_results SET edb_redwood_strings = off;

But at the same time, I will be thinking about you suggested solution/workaround.
Thanks a lot, I shall keep you updated.

[jobinau]

Hi @wawilation ,
Meanwhile I think, I didn't explain why I couldn't accept the workaround you suggested. sorry about missing the explanation.

My workaround was: set_masklen(addr::inet, masklen(mask::inet)) AS network_block

Even though the error vanishes, the calculation goes wrong with that approach. For example

SELECT set_masklen('192.168.1.12'::inet, masklen('225.225.0.0'::inet)) AS network_block;

We would be expecting result 192.168.1.12/16 . Right ?. But PostgreSQL returns a value with mask 32 like 192.168.1.12 :(

Following may clarify why this happening.

postgres=# SELECT masklen('225.225.255.255'::inet);
 masklen
---------
      32
(1 row)

postgres=# SELECT masklen('225.225.255.0'::inet);
 masklen
---------
      32
(1 row)

postgres=# SELECT masklen('225.225.0.0'::inet);
 masklen
---------
      32
(1 row)

So the masklen won't help there. it returns 32 with every mask :)

Meanwhile, I admit that I too don't like the SQL which I have written. With all string manipulation it looks complex and ugly.
I assure you that I will work for a much elegant SQL for next release (Planned for Feb end) which also address the EDB server compatibility. Currently I am doing some testing of new way to approach this problem.

As a temporary workaround, could you please try either of the following options.

1. Make the database which is used for pg_gather to PostgreSQL compatible mode

ALTER DATABASE pg_gather_results SET edb_redwood_strings = off;

2. Pass the option to psql while generating the report generation, so that the setting will last only for the session.

psql -d pg_gather_results -c "SET edb_redwood_strings=off" -X -f gather_report.sql > GatherReport.html

I shall keep you updated about a better SQL.
Thank you.

[wawilation]

@jobinau thank you very much for your dedication to pg_gather and its avid users.

Looking forward to the new release.

More power to you.

[jobinau]

@wawilation ,
As I promised, New version (33) is released released today with a completely rewritten SQL statement which fixes this problem.

[wawilation]

@jobinau thank you so much for your great work!