PostgreSQL SELECT : "unexpected: Downlink disconnected"

Thanks to all the advice I got in my previous post (Slow data loading from data table to app), I’m up and running on ElephantSQL. However I still need to get all 26,000 rows from my PostgreSQL table in order to multiply specified fields by weights and sort the whole table. When I try
cur.execute("SELECT * FROM streets") I get "unexpected: Downlink disconnected". It seems I’m timing out or something. I think I’m doing everything right, but is there anything I can do better to make this work? When I try the same thing on my own machine, it only takes 3-5 seconds to get the psycopg2 results into a list. If I do less fields, like only 2-3, it usually works on Anvil. But the full 20 fields keep throwing the error

I don’t know about the error specifically, but more generally …

Can you explain (again probably, sorry) what you are trying to do with the data when you download it all? In my mind, sorting is for display in which case you can probably use some variant of ORDER BY, and updating fields can also often be done in the SQL itself rather than loading 26k rows, processing them then re-saving them all.

I can probably help with the SQL, but I’m not quite clear in my head what you’re trying to do.

Hi Chris,

Sorry for the unhelpful error message - we’ll have that improved in our next release. In fact in this case it was triggered because you tried to return more than 15Mb of data from a server function. We currently only support payloads up to 4Mb, but we’re working on features to support more.

In the meantime, you should be able to do the data processing you require directly in the function where you call cur.execute. Just don’t return your entire dataset to the client!

Hope that helps. We’ll make sure to keep you updated with new features in this area.

For example, I have a few scoring fields that I want to apply different weighting to to reorder my table of assets. So I need to multiply an entire column of scores by a user-defined number between 1 and 10. According to @daviesian I can do this operation directly in the sql database within my cur.execute function? That would be awesome. What I found online said to get the data into a Numpy array. Any resources or tips on how to do math inside a Postgres DB? Thanks so much!

That isn’t quite what I meant. By all means load the data from the DB, that should work fine (in a server function). Just don’t try to return all that data from a server function to the client. Does that make sense?

Ok understood. How else can I apply a multiplication operation on every entry of a column? I could probably get away with just loading the one column I need to apply a weight to plus the primary key column and still be under 4MB. But if my database grows to 100,000 records, I might run into the same problem. Plus I get the feeling I’m just not following proper database convention with what I’m trying to do. But I haven’t dug up a way to do operations like this without loading my data into Python. StackOverflow seems to be split on this: https://softwareengineering.stackexchange.com/questions/171024/never-do-in-code-what-you-can-get-the-sql-server-to-do-well-for-you-is-this/171033

@chris - in SQL (assuming again I understand you correctly) you would do something like this :

 UPDATE yourtable SET yourcolumn=yourcolumn*yourmultiplicationfactor 

That would update that one column on every single row. To limit the rows you would use a WHERE clause, and to update multiple columns you would do something like :

  UPDATE table SET col1=col1*val1, col2=col2*val2, ... etc.

The database would tell you how many rows were affected by your update.

Does that make sense and/or help? If so, I can probably help you with the actual SQL for your table.

1 Like

@david.wylie That was it! Lo and behold, now that I know where to look, this is explicitly stated in the PostgreSQL documentation. Thanks for the help!