[SA 9.0.166 Pro] - PostgreSql - no arguments for some func

[SA 9.0.166 Pro] - PostgreSql - no arguments for some func

I found recently, SA doesn't suggest arguments for some functions/table functions.
This finding is valid in all places functin name can be extended by this information: hovering bubble over function name, inline function suggestion list, function list in object browser.

Currently I figured out no pattern of this behaviour. Doesn't matter arguments, types, defaults nor return type (saw for boolean, void, table as yet). But a lot of other functions are extended by argument information as expected.

Do you have any suggestion regarding point I could start from to better specify the reason?

Would you please provide a DDL of such function or functions so that we can try to reproduce the issue in our test environment?

I'll try

OK. here is.

	Code:
	CREATE FUNCTION "michalk"."add_log" ( query VARCHAR ) RETURNS void AS $$ INSERT INTO michalk.tab_log ( log_time, log_ip, log_user, log_query ) VALUES (now(), inet_client_addr(), SESSION_USER, $1); $$ LANGUAGE SQL;

I'm unable to reproduce this issue.

Can you please tell me if the function for which you don't see paramaters appears in information_schema.parameters and information_schema.routines system views.

I probably figured out what happens.
It seems :OBJECT_ID is treated as SIGNED INTEGER, while
following postgresql documentation you can read: The oid type is currently implemented as an unsigned four-byte integer.

Query executed in SQL editor:

	Quote:
	SELECT oid FROM pg_proc WHERE proname = 'add_log' ---- -1107746896

And the same query run in pgAdmin:

	Quote:
	SELECT oid FROM pg_proc WHERE proname = 'add_log' ---- 3187220400

Finally running "Arguments" query using 3187220400 as :OBJECT_ID I'm getting expected result.

with regards[/url]

Thank you so much for investigating this issue and getting to the bottom of it. I strongly suspect it's related to the other issue you reported earlier about negative integer numbers converted to zeroes when using the native LibPq interface; if you change your connection to ADO.NET or ODBC, then it should work for all functions.

Despite supposed dependency on already fixed 'negative integers' issue, I can confirm the issue reported in this thread has not been fixed in build 9.0.185.
The query still returns negative values for OID

So sorry about that. Apparently it's a different issue.

Can you suggest how this issue can be reproduced? Is there a way to update catalog with large OID or some other way to recreate that setup in a custom table that I can send to the development team. A SQL snippet would be most helpful.

Reproducing it using clean database can be a challenge because the database would generate too low values for new created objects.
I did some tests which proved that it is :OBJECT_ID which provide negative value.

Then I make sure I, that casting to BIGINT is enough:

	Code:
	SELECT oid AS oid_orig, oid::bigint AS oid_bigint FROM pg_proc WHERE proname = 'add_log' ------ oid_orig oid_bigint -1107746896 3187220400

Following this experiment I had added cast to BIGINT to all oids returned in Objects query.
But it didn't change anything. :OBJECT_ID remains negative.

Maybe I missed something or it's a matter of how :OBJECT_ID is handled internally by the program. Just a guess.

Thank you very much. This is very helpful indeed. You did say when using ODBC interface, the same works correctly, right? If that's the case it indicates that when using native driver, the OID data type is converted to INT instead of BIGINT.

There is an easy way to test that theory and a fix for that. Please modify the Objects (PostgreSQL) query in SQL Assistant Options and after each oid reference in the SELECT clause add ::BIGINT. Here is the modified query

Code:

SELECT relname,
CASE relkind
   WHEN 'r' THEN 'TA'
   WHEN 'f' THEN 'TA'
   WHEN 'S' THEN 'SQ'
   WHEN 'v' THEN 'VI'
   WHEN 'm' THEN 'MA'
END ,
oid::BIGINT,
NULL::TIMESTAMP,
NULL::TIMESTAMP
FROM pg_catalog.pg_class
WHERE relnamespace = :SCHEMA_ID
AND relkind IN ('r', 'f', 'v', 'S', 'm')

UNION ALL

SELECT p.proname,
CASE format_type (t.oid, NULL)
   WHEN 'trigger' THEN 'PC'
   WHEN 'void' THEN 'PR'
   WHEN 'record' THEN 'FT'
ELSE          'FU'
END ,
p.oid::BIGINT,
NULL::TIMESTAMP,
NULL::TIMESTAMP
FROM pg_catalog.pg_proc p,
pg_catalog.pg_type t,
pg_catalog.pg_namespace nt
WHERE pronamespace = :SCHEMA_ID
AND t.oid = p.prorettype
AND t.typnamespace = nt.oid

UNION ALL

SELECT t.tgname,
'TR',
t.oid::BIGINT,
NULL::TIMESTAMP,
NULL::TIMESTAMP
FROM pg_catalog.pg_trigger t,
pg_catalog.pg_class c
WHERE t.tgrelid = c.oid
AND c.relnamespace = :SCHEMA_ID

This is what I actually have done (mentioned in one of my last sentences). It didn't work though.
I didn't check it with ODBC. I'll try it tomorrow.

Hello again.
It's worse than I expected :(

With ODBC, my exemplary sql query:

	Code:
	SELECT oid AS oid_orig, oid::bigint AS oid_bigint FROM pg_proc WHERE proname = 'add_log' ------ oid_orig oid_bigint 3187220400 3187220400

Returns the same values. And it's the last good news.

Unfortunately Editor stopped to properly recognize schemas' content with ODBC connector. In database explorer I can see schemas, but no objects inside. Intelisense doesn's suggest objects (functions, tables) after entering a dot preceded by schema name. Forcing object list to open (by CTRL+SPACE), the list contains only objects from default space (public schema I believe). CTRL+LMB on function name doesn't show function DDL. Error message appears instead: '<some_name>' is not recognized as a valid object name in the current database, where <some_name> is name of function I wanted to show.

I checked this behaviour using default settings of SA by switching connector between native psql and odbc mutliple times.
used version:
postgresql: 9.2
SA: 9.0.185
please note, I don't know if broken ODBC appeared in recent version or it's present since the first release of 9.x. I'm using native connection only since it's available.

Right now I have to turn back to native connector, since it's more functional for obvious reasons.
I would like to help you more, but I literally have no enough time to pursue it. I hope you get rid of those problems soon.

EDit: Speaking about Object explorer. If I expand name of function which returns table, I can see table icon with [+] symbol, suggesting ability to expand returning it more, probably getting list of returned columns. However after expanding, no new items appear, [+] symbol disappear.

	Quote:
	In database explorer I can see schemas, but no objects inside. Intelisense doesn's suggest objects (functions, tables) after entering a dot preceded by schema name.

It's part of the same issue. The issue is not specific to functions, it impacts all database queries where OID values are passed as parameters in the WHERE clause. But the issue is a bit more complicated than that. The OBJECT_ID parameter used in queries is an INT4 which is the same across all supported database systems. The OID values appear to be UINT4, not BIGINT.

Good news, we are testing a fix supporting UINT4 based object identifiers. I hope this fix will make into the next public build tentatively planned for the end of this week.

This issue is fixed in the latest released build 9.0.194.

This issue is fixed in the latest released build 9.0.194.