News for hackers. Stuff that matters.
Re:Thinking Psycopg3 by skeletal88 on Wednesday March 11, 2020 @06:37PM (#22551576)
The one thing I dislike about the Python DBAPi spec is the "cursor". Maybe the idea was good when the creators of the spec came up with it, but no other database API exposes a cursor so upfront to the users. When someone new to databases first learns about the Python DBAPI then he will probably think that cursors are something really important, but really - they aren't. Developers just want to fire queries at the database, the cursor is just something between the db connection and the query result. Maybe it is needed to keep the state of query objects, but it should have some other name.
Re:Thinking Psycopg3 by quietbritishjim on Wednesday March 11, 2020 @06:52PM (#22551668)
I don't understand this comment. A cursor is just an iterator over the results. Would you prefer "iterator" as a name? A concept like it exists in basically every database API (for row-oriented databases) so that you can start processing results as soon as they start coming in rather than having to load all results into application memory first.
Re:Thinking Psycopg3 by luhn on Wednesday March 11, 2020 @08:33PM (#22552317)
I think OP has a point. He's not complaining about the functionality (of course a DB client needs to iterate through results) but that there's no reason a user needs to understand cursors for running simple DB queries. I should be able to do:

    results = conn.execute("select * from mytable").fetchall()
or

    query = conn.execute("select * from mytable")
    for row in query:
        print(row.id)
You can do the same with cursors, but it's extra mental overhead and a somewhat awkward API.

Plus the name conflicts with Postgres' CURSOR, which confused me when I first started. So, yes, I would prefer "iterator" :)

Re:Thinking Psycopg3 by skeletal88 on Thursday March 12, 2020 @07:23AM (#22555634)
Yes, that's what I meant, it doesn't have to be exposed to the user, I just want to execute a query and get the results. If needed then maybe I should be able to create a cursor to go through the results. It's just that other database access APIs don't expose and force the developers using the API to use a cursor, so it's mildly annoying in Python.
Re:Thinking Psycopg3 by quietbritishjim on Thursday March 12, 2020 @06:33AM (#22555375)
You are right and I had forgotten about that. I mostly use SQLite from Python which actually does allow .execute() on the connection as a non-standard extension to DBAPI, so it supports exactly the two snippets you posted. It is a bit awkward that you have to create your cursor before you execute the query, I don't quite see the reason for that.
Re:Thinking Psycopg3 by adamcharnock on Thursday March 12, 2020 @05:00AM (#22554981)
This seems non obvious to me, purely because one has to create the cursor before ever executing any queries [1]. It therefore think it feels like a "sub-connection", or something the database is aware of at any rate.

It would be much clearer to me that the cursor is an iterator if it was actually returned as the result of executing a query.

[1] https://www.psycopg.org/docs/usage.html

Re:Thinking Psycopg3 by quietbritishjim on Thursday March 12, 2020 @06:37AM (#22555401)
> one has to create the cursor before ever executing any queries

You are right, that is a bit weird, although only a very slight annoyance IMO. As I said in reply to the sibling comment, I had forgotten this as I mostly use SQLite from Python and it doesn't actually require this: you can call .execute() on the connection object and it returns the cursor, which does seem a lot cleaner.

Re:Thinking Psycopg3 by skeletal88 on Thursday March 12, 2020 @07:25AM (#22555648)
Yes, but with other database access APIs I can just get a connection and execute a statement on it. Other APIs don't have an additional abstraction between the connection and the execute() method.

It's especially useless when executing anything besides SELECT. An UPDATE or INSERT shouldn't need a cursor.

Re:Thinking Psycopg3 by paulddraper on Wednesday March 11, 2020 @07:05PM (#22551758)
Though except with very large result sets (100k), there is little performance gain from having a cursor.
Re:Thinking Psycopg3 by orf on Wednesday March 11, 2020 @07:33PM (#22551947)
That's objectively not true though, so I'm not sure why you would say something like that. Ignorance?

A hugely significant portion of database workloads involve iterating over the rows returned from a query. Exposing a cursor/iterator/whatever in the spec is the only sensible way of handling this - your code works regardless of if the results are fetched in a single bulk operation or streamed to the client, or if you're returning 1 row or 1,000,000.

It's the same reason you wouldn't do "for i in list(range(100_000))`, you'd just do `for i in range(100_00)` - iterating over a generator (which is what streaming results from the database really is) is far more efficient than creating a huge structure upfront and _then_ iterating over it.

Also 100k rows isn't very large.

Re:Thinking Psycopg3 by aeyes on Wednesday March 11, 2020 @08:37PM (#22552336)
One could argue that pumping huge amounts of data with psycopg2 is slow in any case due to the blocking nature of the network calls.

I recently copied some big tables (100M+ rows) into a different system, when I switched the simple script to asyncpg I gained 5x over psycopg2. Experimenting with the amount of rows cursor.fetchmany returns changed absolutely nothing.

I wonder how much gain there is from using a cursor, for CRUD applications you most likely need the full result set before being able to do anything with it. Having a cursor could be made optional for when you actually want to iterate over the resultset.

The cursor interface could also be hidden, I just want to iterate over the resultset - I don't care what happens in the background.

Re:Thinking Psycopg3 by ghostwriter on Wednesday March 11, 2020 @08:52PM (#22552414)
> when I switched the simple script to asyncpg I gained 5x over psycopg2.

This should really be a comparison between asyncpg and psycopg2+gevent+psycogreen to be technically aligned, as otherwise it's a comparison between two scripts with non-blocking and blocking IO.

Re:Thinking Psycopg3 by 1st1 on Wednesday March 11, 2020 @11:22PM (#22553387)
Here's a benchmark [1] against aiopg, which is psycopg2+asyncio.

[1] http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...

Re:Thinking Psycopg3 by ghostwriter on Thursday March 12, 2020 @06:46AM (#22555435)
Unless aiopg patches psycopg2 around its C extension with available callback hooks (see [1] and [2] - psycogreen uses it to register gevent callbacks), this comparison is still between blocking and non-blocking IO scripts. Quick GitHub search for `set_wait_callback` didn't return any result in the aiopg repository - https://github.com/aio-libs/aiopg/search?q=set_wait_callback...

[1] https://www.psycopg.org/docs/extensions.html#coroutines-supp...

[2] https://www.psycopg.org/docs/advanced.html#green-support

Re:Thinking Psycopg3 by ddorian43 on Thursday March 12, 2020 @04:29AM (#22554830)
You can't scale that. Adding clients will have a lot of overhead (ex: 100K clients to 1 db server). Only way is autocommit with proxy (pgbouncer etc).
Re:Thinking Psycopg3 by wnoise on Thursday March 12, 2020 @02:53AM (#22554441)
The point is to compare the blocking vs non-blocking though.
Re:Thinking Psycopg3 by ghostwriter on Thursday March 12, 2020 @06:34AM (#22555384)
I was addressing OP's impression that psycopg2 was slow "in any case" and that it cannot be used in non-blocking fashion.

> psycopg2 is slow in any case due to the blocking nature of the network calls.

Re:Thinking Psycopg3 by somacert on Wednesday March 11, 2020 @08:19PM (#22552240)
That vaguely touches (warning going off topic here, my apologies) my main problem with python. dict.keys() returns an iterator, this is all fine and good, very efficient I suppose, however, the only reason I ever use dict.keys() is so I can sort the keys and you can't sort an iterator so every single dict.keys() in my code base is in the form list(dict.keys()). And it is not even like you need the iterator. A dict works perfectly fine as a key iterator on it's own. /rant
Re:Thinking Psycopg3 by orf on Wednesday March 11, 2020 @08:23PM (#22552261)
> sorted(my_dict.keys())

Or just:

> sorted(my_dict)

Sorting keys like that is also weird. Keys are insertion ordered, utilize that property where you can and avoid needlessly re-sorting.

Re:Thinking Psycopg3 by somacert on Wednesday March 11, 2020 @08:56PM (#22552432)
Glorious, Thank you kind soul for listening to my slightly raving rant.

Now I feel several sorts of ashamed at missing what is kind of a core function.

salutes

Re:Thinking Psycopg3 by hansvm on Wednesday March 11, 2020 @09:38PM (#22552662)
Fwiw, make sure you're using a newish python version. They weren't always sorted by default.
Re:Thinking Psycopg3 by ledauphin on Wednesday March 11, 2020 @10:36PM (#22553075)
3.6+
Re:Thinking Psycopg3 by marcosdumay on Wednesday March 11, 2020 @08:36PM (#22552331)
> he only reason I ever use dict.keys() is so I can sort the keys

Hum... I use dict.keys() every time I use dictionaries to describe some unspecified data set, what happens way more frequently than any use case that requires sorting the keys. It's even incentivized by the language with that kwargs construct and a mainstream usage within libraries.

I do agree that the result of dict.keys() should have a sort method. There is no reason not to, but from there it doesn't follow that that making it an iterator is a minor gain.

That's the same situation as the GP asking for the removal of a main feature of the library just because he doesn't work with a kind of software that uses it. Congratulations, remove cursors from the library and suddenly Python is a lot less useful for data science.

Re:Thinking Psycopg3 by orf on Wednesday March 11, 2020 @08:42PM (#22552356)
It doesn’t and shouldn’t have a sort method because python prefers functions that act on objects rather than methods attached to objects.

That’s why you use “len(obj)” rather than “obj.len()”, and why you use “sorted(dict_keys)” rather than adding a “sort” method to random iterables.

Re:Thinking Psycopg3 by marcosdumay on Wednesday March 11, 2020 @10:39PM (#22553102)
Oh, ok. I was comparing it with 'list.sort()', but yeah, this one mutates the list and returns None (just checked it), so 'sorted' is the expected way to sort a dictionary keys, and my post is just wrong.
Re:Thinking Psycopg3 by orf on Friday March 13, 2020 @04:43PM (#22570749)
'list.sort()' is one of those leftovers from "Python before it was really Python" and is not one that's particularly worth it to change because it's useful sometimes.

In general Python prefers builtins that operate on protocols. You use "len(x)" because it's consistent, otherwise you might call "x.len()", "x.length", "x.size()" or any other number of possibilities. And if an object doesn't define it? You're out of luck. With this any object that supports the protocol (__len__) can be passed to length.

The same applies to "sorted()". list.sort() is useful in some specific circumstances where "sorted()" is not adequate, but in general the answer to "how do I sort something" is to use sorted.

Re:Thinking Psycopg3 by yingw787 on Wednesday March 11, 2020 @07:15PM (#22551830)
Wait...are you saying 100,000 records is a lot? If so, I disagree. Any serious enterprise workload is millions, usually tens, hundreds of millions, and oftentimes billions or tens of billions or more over the lifetime of any given production UNIX process. Hundreds of thousands is dev testing range. I've dealt up to single-digit billions, and I can say having a cursor is absolutely a requirement when working with production data.
Re:Thinking Psycopg3 by paulddraper on Wednesday March 11, 2020 @09:11PM (#22552491)
Yes, I'm claiming that retrieving 100,000 records in a single query is a lot.

If thousands of records weren't a lot, HN wouldn't have to manually break up comment threads anytime a U.S. president is elected.

Re:Thinking Psycopg3 by stefan_ on Wednesday March 11, 2020 @07:31PM (#22551940)
For the database. If your application is retrieving millions of rows, you might want to reconsider what using a database with a complex query protocol is giving you and whether you are using it correctly.
Re:Thinking Psycopg3 by marcosdumay on Wednesday March 11, 2020 @08:40PM (#22552346)
If my application is retrieving millions of rows, it may be because I need to pass a window function over millions of rows. Or it may be that I need to copy millions of rows from one place to another. Or I may need to compare them with other millions of rows from another source somewhere else. Or I may need to transform those millions of rows on my application because I can scale it better than the database...

Just because you didn't think of some valid usage for a feature, it doesn't mean that there isn't any.

Re:Thinking Psycopg3 by tomnipotent on Wednesday March 11, 2020 @10:02PM (#22552850)
> it may be because I need to pass a window function over millions of rows

Every major RDBMS supports window functions (even MySQL, though recent). Why would you bring remote data into a local process to do something the remote process can do with its own local data vastly more efficiently?

Re:Thinking Psycopg3 by ben509 on Thursday March 12, 2020 @01:15AM (#22553993)
Probably related to why the database has millions and millions of rows in the first place. Maybe someone read about denormalization in "Super Webscale SQL!" and blew up a trivial database. Or they're hoovering up vast quantities of garbage data and running it through a BI tract to produce bullshit.

I worked at a company that bilked the government for vast sums of money by doing all this and charging them for the hardware. The guvvies knew what was going on, but their little empires were more important if they got a bigger budget, so they went along with it.

Re:Thinking Psycopg3 by marcosdumay on Thursday March 12, 2020 @06:23PM (#22562550)
Oh, God forgive if a company actually has a few million sales it must account for, or if a government body actually wants to control and verify what it does. No it's all because of stupid webscale! developers, because nobody ever does anything that isn't a web or finance startup! Ever!
Re:Thinking Psycopg3 by vosper on Wednesday March 11, 2020 @07:27PM (#22551909)
I don't really understand your point. You and the person you replied to are both agreeing that cursors are useful for large datasets. Whatever defines "large" isn't that interesting and is dependent on context: got a resource-constrained DB? Or very wide tables? Constraints on the client side for how much data it can consume and process? Then, maybe your "large" is a lot different to mine.
Re:Thinking Psycopg3 by ben509 on Thursday March 12, 2020 @01:01AM (#22553938)
> Whatever defines "large" isn't that interesting

Some people get excited about huge datasets; I don't get it myself, but "big data" is a successful buzzword because it pushes people's buttons.

Re:Thinking Psycopg3 by AlphaSite on Thursday March 12, 2020 @12:56AM (#22553919)
They are arguing it’s useful for smaller datasets as well.
Re:Thinking Psycopg3 by djrobstep on Wednesday March 11, 2020 @07:32PM (#22551944)
If you're exporting data en mass from the database then this is a concern, but for the typical CRUD application use case, it's never an issue.

If there's enough results that loading them is causing performace problems, there's enough results that you need to be paging them in your UI.