[Solved] Are there any limits on query results?

[loaded]

Hi All,
In my postgresql database I use, I have columns of bytea type in a table I created. In these columns, I store pdf-like large and small files.
There was no problem so far, but after the last records I added, the queries are now inconclusive.
It doesn't give any error. But it does not bring the records.
In the query, if I exclude the columns where I keep the Files, the query completes without any problems.
As I understand there is a limit to the query, is it possible to increase it?

[MarkMLl]

I was not aware of one, unless you explicitly put a limit clause in the query.

You might need to check your server logs to see what query is actually being generated (I think this is a good use case for the discussion a couple of weeks ago about being able to access actual queries on the client).

MarkMLl

[loaded]

Thank you very much MarkMLl for the answer.

I was not aware of one, unless you explicitly put a limit clause in the query.

Yes, that's how I knew it too.

You might need to check your server logs to see what query is actually being generated

That's a good idea, let's see what's going on under the hood.

[MarkMLl]

That's a good idea, let's see what's going on under the hood.

I assume that if SSL is in use then something like Wireshark won't show you anything useful. I've definitely been able to examine actual queries in PostgreSQL's server log in the past, but I can't remember whether I needed to tweak a configuration file to make it so.

Also if you've got FPC/RTL/FCL built with debugging options then Lazarus's debugger should be able to take you as deep as you need. However broadly speaking this is why I prefer to build queries myself: the scripting that drives the business can display a query in its entirely if there's a syntax error, I can examine complex joins in detail, and in extremis I can use Postgres's EXPLAIN to weed out details which would e.g. force a sequential scan rather than indexed access.

MarkMLl

[loaded]

SSL... Wireshark....debugging....Postgres's EXPLAIN to weed out details which would e.g. force a sequential scan rather than indexed access.

Good ideas. Master MarkMLl, you are like a lighthouse.

[Zvoni]

In the query, if I exclude the columns where I keep the Files, the query completes without any problems.

Don't pull BLOBs along other columns from a Table. Period!
Only pull it if user explicitly demands it

Code: SQL  [Select][+][-]

1. SELECT ID, Description, SomeOtherNonBlobs FROM MyTable;
2. .
3. .
4. //USER selects a specific Record
5. SELECT BLOB FROM MyTable WHERE ID=:SelectedID
6.  

[MarkMLl]

Don't pull BLOBs along other columns from a Table. Period!
Only pull it if user explicitly demands it

I'm inclined to agree, but it's still unfortunate if the query doesn't complete in some determinate manner.

@loaded: what is the behaviour of psql if given the same query? Does it return the file content (even if in as raw bytes) or does it explicitly separate blob and non-blob columns?

MarkMLl

[Zvoni]

Mark,
OP writes "columns" as in plural.
Not knowing how "large" and "small" his pdf's are in MB, multiplied with count of resulting records.....
.... should make an interesting read how many MB/GB he wants to push through the Wire of his LAN....

[MarkMLl]

Mark,
OP writes "columns" as in plural.
Not knowing how "large" and "small" his pdf's are in MB, multiplied with count of resulting records.....
.... should make an interesting read how many MB/GB he wants to push through the Wire of his LAN....

Hmm. In OP he says "It doesn't give any error. But it does not bring the records."... @loaded: are you confident that all activity has completed when you reached that conclusion, i.e. nothing more being moved over the LAN and all local memory thrashing resolved? Did the debugger show that the routine had returned a status, or did it remain hung if you left it e.g. overnight?

I've long been critical of the way that database frontends effectively fell over if asked to look at a large table, and this could of course be a variant of that sort of problem.

MarkMLl

[loaded]

Thank you very much Zvoni for the reply.

Don't pull BLOBs along other columns from a Table. Period! Only pull it if user explicitly demands it

Yes, I have adopted this way of working now.

@loaded: what is the behaviour of psql if given the same query? Does it return the file content (even if in as raw bytes) or does it explicitly separate blob and non-blob columns?

Yes I can try this on pgadmin. If I'm available tomorrow, I'll do it

Hmm. In OP he says "It doesn't give any error. But it does not bring the records."... @loaded: are you confident that all activity has completed when you reached that conclusion, i.e. nothing more being moved over the LAN and all local memory thrashing resolved? Did the debugger show that the routine had returned a status, or did it remain hung if you left it e.g. overnight?

To tell the truth, we solved the problem by removing the blob fields from the query directly as above, since our work was in a hurry. We've never had a chance to look at the debugger or other methods. I will try the methods you recommend at a convenient time and let you know the results.

[MarkMLl]

Yes I can try this on pgadmin. If I'm available tomorrow, I'll do it

NOT PGADMIN! PSQL!!

psql is the reference client implementation from the PostgreSQL project, i.e. written by people with an intimate understanding of the API and protocol. If /they/ separate blobs, then it's a strong indication that the API and protocol expect it. While well-respected, pgadmin is not the reference implementation.

MarkMLl

[loaded]

NOT PGADMIN! PSQL!!

Master MarkMLl, this is the first time I've heard of Psql from you. In the meantime, I realized that there is much more to learn from you.
But, for now, instead of something I don't know, I experimented with Pgadmin that I know (You're going to be mad at me for that) and the query was completed in like 6 seconds.
As far as I understand, there is no problem on the database side, it's a case of the Zeos I'm using.
Thanks to you, what I have gained is enough for me for now. I don't want to tire you anymore. Respects.

[marsupilami79]

Hello loaded,

As far as I understand, there is no problem on the database side, it's a case of the Zeos I'm using.

as a Zeos developer I would like to know more about this:
- Which version of Zeos do you use?
- Can you give any hints on how to recreate the situation you had? Like how many records did you try to get from the database and what were the blob sizes in the bytea fields?

Best regards,

Jan

[loaded]

Hi marsupilami79, first of all, thank you very much for your interest and also for the Zeos Package.

- Which version of Zeos do you use?

I'm using Zeos version 7.2.14 and Postgresql 9.6 along with Lazarus 2.2.0.

- Can you give any hints on how to recreate the situation you had? Like how many records did you try to get from the database and what were the blob sizes in the bytea fields?

In fact, the number of records is not very many, only 243. But in each record, there are 7 columns to store 'bytea' type documents. On average, files between 1 Mb and 7 Mb are stored. Some may be empty. But 7 Mb recording is available in all of them.

[miab3]

In fact, the number of records is not very many, only 243. But in each record, there are 7 columns to store 'bytea' type documents. On average, files between 1 Mb and 7 Mb are stored. Some may be empty. But 7 Mb recording is available in all of them.

7Mb or 7MB(x243)? Because if the latter, then you are dangerously approaching the border of 32-bit systems

Michał