Recent

Author Topic: [Solved] Are there any limits on query results?  (Read 1044 times)

loaded

  • Hero Member
  • *****
  • Posts: 586
[Solved] Are there any limits on query results?
« on: June 24, 2022, 02:59:33 pm »
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?
« Last Edit: June 28, 2022, 08:32:42 am by loaded »
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

MarkMLl

  • Hero Member
  • *****
  • Posts: 4728
Re: Are there any limits on query results?
« Reply #1 on: June 24, 2022, 03:11:32 pm »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

loaded

  • Hero Member
  • *****
  • Posts: 586
Re: Are there any limits on query results?
« Reply #2 on: June 24, 2022, 03:19:10 pm »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

MarkMLl

  • Hero Member
  • *****
  • Posts: 4728
Re: Are there any limits on query results?
« Reply #3 on: June 24, 2022, 04:10:27 pm »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

loaded

  • Hero Member
  • *****
  • Posts: 586
Re: Are there any limits on query results?
« Reply #4 on: June 24, 2022, 04:44:06 pm »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

Zvoni

  • Hero Member
  • *****
  • Posts: 1264
Re: Are there any limits on query results?
« Reply #5 on: June 27, 2022, 09:02:46 am »
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.  
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MarkMLl

  • Hero Member
  • *****
  • Posts: 4728
Re: Are there any limits on query results?
« Reply #6 on: June 27, 2022, 09:30:10 am »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

Zvoni

  • Hero Member
  • *****
  • Posts: 1264
Re: Are there any limits on query results?
« Reply #7 on: June 27, 2022, 09:47:22 am »
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....
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MarkMLl

  • Hero Member
  • *****
  • Posts: 4728
Re: Are there any limits on query results?
« Reply #8 on: June 27, 2022, 09:59:15 am »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

loaded

  • Hero Member
  • *****
  • Posts: 586
Re: Are there any limits on query results?
« Reply #9 on: June 27, 2022, 06:09:26 pm »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

MarkMLl

  • Hero Member
  • *****
  • Posts: 4728
Re: Are there any limits on query results?
« Reply #10 on: June 27, 2022, 10:39:39 pm »
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
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

loaded

  • Hero Member
  • *****
  • Posts: 586
Re: Are there any limits on query results?
« Reply #11 on: June 28, 2022, 08:32:25 am »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

marsupilami79

  • New Member
  • *
  • Posts: 29
Re: Are there any limits on query results?
« Reply #12 on: June 29, 2022, 05:27:10 pm »
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
Zeos developer

loaded

  • Hero Member
  • *****
  • Posts: 586
Re: Are there any limits on query results?
« Reply #13 on: June 30, 2022, 08:37:06 am »
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.
If Ide=Lazarus 2.0.10 32 Bit and Os=Win 10 Home 64 Bit then Get up and do something useful! Because God is the helper of those who start again;

miab3

  • Full Member
  • ***
  • Posts: 144
Re: Are there any limits on query results?
« Reply #14 on: June 30, 2022, 10:13:28 am »
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ł
« Last Edit: June 30, 2022, 10:15:36 am by miab3 »

 

TinyPortal © 2005-2018