Обсуждение: Data grid: fetching/scrolling data on user demand

Hello,

Data grid is populated without any limit by default,
it could be a problem with very big datasets ...

To avoid this, it is possible to limit the number of rows retrieved,
but that limit is fixed, even if user tryes to scroll more data ...

Is scrolling data on demand supported ?

Thanks in advance for your answer.
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Sorry, why is

*select * from information_schema.columns a,information_schema.columns b
*
on a newly created db is never ending ?

when 
*select * from information_schema.columns a,information_schema.columns b
limit 1000*
takes less than one second ?

Is pgadmin4 really fetching only the 1000 first records ?

PAscal
SQLeo project manager



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

maybe this behavior is related to fetching records using a server-side cursor
?

https://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL#Fetch_Records_using_a_Server-Side_Cursor

I met the same problem using pgjdbc with Oracle SQL developer as descibed
here

https://stackoverflow.com/questions/46066882/autocommit-always-turns-on-in-oracle-sqldeveloper-when-connecting-to-postgresql
or
https://github.com/pgjdbc/pgjdbc/issues/976



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

How long does it take in your environnment
to fetch the 1000 first records from

select * from information_schema.columns a,information_schema.columns b 





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Pgadmin doesn't have to Wait for all the data,
As he should only load/fetch the first 1000 rows.

Loading all the data in memory will not be possible for big datasets.

This is a design error at my point of view.

PAscal
SQLeo projection manager





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

1000 first rows are available in less than zone second.
See query with limit 1000.

Monitoring memory usage or PG_stat_activity shows that all the data is
fetched.




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-support-f2191615.html

Hi,

> As I mentioned in my previous email that we do not use server side cursor, so it won't add any
> limit on query.
>
> The delay is from database driver itself, it has nothing to do with pgAdmin4.
> Try executing the same query in 'psql', 'pgAdmin3' and third party tool which use libpq library as
> backend, you will observe the same behaviour.

It is not exactly truth... In v3 the query is executed, fetched and all rows are displayed, in v4 query is executed,
fetchedbut only 1000 records are displayed. 

For me this idea of "load on demand" (which in reality is "display on demand") is pointless. It is done only because
themain lag of v4 comes from interface. I don't see any other purpose for it... If You know (and You do) that v4 can't
handlebig results add pagination like every other webapp... 

And by the way You have a big leak in query tool - execute query with several thousands rows, scroll above 1000 mark
fewtimes, execute the same query, scroll above 1000 mark few times - repeat until You run out of memory or v4
crashes...

--
Tomek

Hi,

>> It is not exactly truth... In v3 the query is executed, fetched and all rows are displayed,
>
> No they're not, though they are all transferred to the client which is why it's slower.

They are not what? What is slower - is the "display" part in both versions. You have data from server and than You push
itto display. 
I've done quick test - table 650000 rows / 45 columns, query SELECT * from table limit 100000.
With default ON_DEMAND_RECORD_COUNT around 5 seconds, with ON_DEMAND_RECORD_COUNT = 100000 25 seconds...
It is 20 seconds spent only on displaying...

>> For me this idea of "load on demand" (which in reality is "display on demand") is pointless. It
> is done only because the main lag of v4 comes from interface. I don't see any other purpose for
> it... If You know (and You do) that v4 can't handle > big results add pagination like every other
> webapp...
>
> We did that in the first beta, and users overwhelmingly said they didn't like or want pagination.
>
> What we have now gives users the interface they want, and presents the data to them quickly - far
> more quickly than pgAdmin 3 ever did when working with larger resultsets.
>
> If that's pointless for you, then that's fine, but other users appreciate the speed and
> responsiveness.

I don't know of any users (we are the users) who are happy that selecting 10000 rows requires dragging scrollbar five
timesto see 5001 record... 

Saying pointless I meant that if I want 10000 rows I should get 10000 rows, if I want to limit my data I'll use LIMIT.
Butif the ui can't handle big results just give me easiest/fastest way to get to may data. 

--
Tomek

Hi,

>>>> It is not exactly truth... In v3 the query is executed, fetched and all rows are displayed,
>>>
>>> No they're not, though they are all transferred to the client which is why it's slower.
>>
>> They are not what?
>
> The handling of rows in pgAdmin 3 is not as you described.

So please tell me how is it done.

>> What is slower - is the "display" part in both versions. You have data from server and than You
>> push it to display.
>> I've done quick test - table 650000 rows / 45 columns, query SELECT * from table limit 100000.
>> With default ON_DEMAND_RECORD_COUNT around 5 seconds, with ON_DEMAND_RECORD_COUNT = 100000 25
>> seconds...
>> It is 20 seconds spent only on displaying...
>
> So? No human can read that quickly.

I get the joke but it doesn't add anything to this discussion...

>>>> For me this idea of "load on demand" (which in reality is "display on demand") is pointless. It
>>> is done only because the main lag of v4 comes from interface. I don't see any other purpose for
>>> it... If You know (and You do) that v4 can't handle > big results add pagination like every other
>>> webapp...
>>>
>>> We did that in the first beta, and users overwhelmingly said they didn't like or want pagination.
>>>
>>> What we have now gives users the interface they want, and presents the data to them quickly - far
>>> more quickly than pgAdmin 3 ever did when working with larger resultsets.
>>>
>>> If that's pointless for you, then that's fine, but other users appreciate the speed and
>>> responsiveness.

Part of the data...
Please explain to me what is the point o requesting 100000 and getting 1000 without possibility to access the rest?

>> I don't know of any users (we are the users) who are happy that selecting 10000 rows requires
>> dragging scrollbar five times to see 5001 record...
>
>> Saying pointless I meant that if I want 10000 rows I should get 10000 rows, if I want to limit my
>> data I'll use LIMIT. But if the ui can't handle big results just give me easiest/fastest way to get
>> to may data.
>
> Then increase ON_DEMAND_RECORD_COUNT to a higher value if that suits the way you work. Very few
> people scroll as you suggest - if you know you want to see the 5001 record, it's common to use
> limit/offset. If you don't know, then you're almost certainly going to scroll page by page or
> similar, reading the results as you go - in which case, the batch loading will speed things up for
> you as you'll have a much quicker "time to view first page".

You can read yes? I wrote about LIMIT earlier... I'll put it in simpler terms "If you don't know" what I mean.

You understand that if a user selects data, user wants to get data? - that is why he is doing select.
You understand that to get the data is to see the data? - that is why he is doing select.
You understand that data can take more than 1000 records?
You understand that You hide the data without possibility to access it (at least quickly)?
You understand that in this example user didn't get the data?

I hope that above clarifies pointlessness of "display on demand" without some other option to speed up the browsing
process. 

As for increasing ON_DEMAND_RECORD_COUNT - I would gladly do it but then displaying 100000 records takes more time and
about500 Mb of memory (v3 used around 80)... What is more in v4 I can't get 300000 records even with default
ON_DEMAND_RECORD_COUNT(canceled query after 5 minutes) while v3 managed to do it in 80 seconds. 

I understand that large results are rare but this is a management tool not some office app... It should do what I
want...

And again You are telling me what I want and what I need, and how I should do it... You decided to make this tool in a
waythat no other db management works, You cut out a lot of v3 features, You purposely limit usability to account for
poorperformance and call it a feature... 
It reminds me of some big software company in old days...

--
Tomek

Hi,

> On Tue, Oct 17, 2017 at 1:44 PM, Tomek <tomek@apostata.org> wrote:
>
>> Hi,
>>
>>>>>> It is not exactly truth... In v3 the query is executed, fetched and all rows are displayed,
>>>>>
>>>>> No they're not, though they are all transferred to the client which is why it's slower.
>>>>
>>>> They are not what?
>>>
>>> The handling of rows in pgAdmin 3 is not as you described.
>>
>> So please tell me how is it done.
>
> You can find the code here:
> https://git.postgresql.org/gitweb/?p=pgadmin3.git;a=tree;h=216f52a5135db3124268a32c553b812547d4b918;
> b=216f52a5135db3124268a32c553b812547d4b918
You are using libpq, You fetch data, You display data. I don't know how to describe it simpler...
>> Part of the data...
>> Please explain to me what is the point o requesting 100000 and getting 1000 without possibility to
>> access the rest?
>
> Of course you can get all 100000, you just scroll down. Saying that you can only get 1000 "without
> possibility to access the rest" is 100% factually incorrect.

Right I forgot to add "at least quickly" as I later did.

> No - I'm telling you what nearly 20 years of interacting with pgAdmin users has indicated they
> likely want. If it's not what you want, then you are free to look at other tools. We do our best to
> meet the requirements of the majority of our users, but it's obviously impossible to meet everyones
> requirements.
>
>> You cut out a lot of v3 features,
>
> Yes. Did you ever create an operator family? Or use the graphical query designer that crashed all
> the time? Those and many other features were intentionally removed. Some have been put back in
> cases where it became clear they were needed and others will still be added in the future - but the
> vast majority of things we left out will likely remain gone because they add complexity and
> maintenance costs for zero or near zero value.

This is old - a lot o people told You already what is missing - and not some depreciated stuff but important stuff like
forexample possibility to edit table data without PRIMARY key and OIDS=TRUE... 

>> You purposely limit usability to account for poor performance and call it a feature...
>
> You haven't (as far as I can see) described how we limited usability, except by making claims that
> are provably wrong.

I know - almost every suggestion/problem with v4 in comparison to v3 in this list was dismissed by You, by telling us
Usersthat we do not know what we want and the only one who do is You. 
> I'm not going to spend any more of my free time on this thread. I have little enough as it is.

Of course, because I'm being paid for talking to the wall...

--
Tomek