PostgreSQLDatabase truncates/munges queries

Databases
1

Every now and then, our Xojo-written middleware instances will start reporting errors when connecting to the PostgreSQL server, usually because a query longer than, say 20 characters gets truncated. (I don’t know the exact length.) Once it happens, every connection from that instance exhibits that behavior and the only solution is to restart the instance.

Note that the instance, gets and handles requests from the GUI client in threads. Each thread gets and holds a new connection to the database until it’s finished, at which point the connection is closed.

Has anyone else seen this or something like it?

2

negative, and most of my queries are longer. But I usually (not always though) use the MBS SQL plugin.

3

See:

<https://xojo.com/issue/63252>

4

I really don’t have any behavior like that on my many postgres desktop apps.

xojo or mbs database calls ?

5

All native Xojo. And mind you, this happens very infrequently.

6

how did you manage to see that it truncates somewhere if it is very unfrequent ?

7

Can you switch on logging and trace the SQLs to feed William with more information? You probably know that Postgres supports dynamic tracing, that might help you too.

8

i an test enviroment
i would try it with other postgresql version or may updated.
also test without thread and just simulation class that just use methods.
put the sql query before the where part into a string for loggin.
was postgresql server also rebooted?
is there a router between that need a reboot?
just to be sure its xojo,thread,hardware(router),server,database issue.
running at different pc to exclude this device.
maybe create a test app that run 24/7 to find the reason.
…
also check/log this Runtime. properties

9

GUI returns a syntax error, and the corresponding server-side logs show the query as truncated.

We first noticed this years ago with PSQL 9, then on 10, and today on 11. We have included defensive code to test for this periodically and reboot the instance when it happens, so it might be more frequent than we think. We do know that it’s not a server issue.

1 Like
10

Could it be by any means triggered by some “special” char or Unicode? Or just query length?

11

Did you try a copy of the project with SQLDatabaseMBS class instead of the built-in PostgreSQL database class?

12

These are simple queries that are reused, i.e., not formed on the fly, usually through prepared statements.

For example, something like:

SELECT
	id,
	tag_name,
	procedure_code,
	revenue_code,
	billing_provider_taxonomy_code,
	site_of_care_provider_taxonomy_code,
	any_provider_taxonomy_code,
	billing_provider_state,
	site_of_care_provider_state,
	any_provider_state,
	type_of_bill
FROM g.claim_tag_config
ORDER BY tag_name ASC

This one resulted in a “syntax error near ORDER BY” this morning. But this query is using during login and runs without issue dozens of times a day, at least.

13

So maybe the problem may lie in the reuse. At some point, it breaks due to some (Xojo/Postgresql) bug.

14

Except it’s every query thereafter on every connection from that instance.

15

We use the native PSQL plugin. The project is a massive, multi-developer affair, so rewriting around your plugin is not an option.

16

The broken state probably is tied to the connection.

17

Connections are not reused. Is that what you mean?

18

Xojo Inc may appreciate if you can reproduce the bug in a small project to fix it.

19

I can’t even reproduce it in our big project. :slight_smile:

3 Likes
20

Not sure about the Xojo internals. Internal connection pools, caches, machine states, or whatever retaining some broken state. Connection factory (new) inheriting a bugged condition afterwards, something like that.