www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - dpeq - native PSQL extended query protocol client

reply Boris-Barboris <ismailsiege gmail.com> writes:
Hi! Couple of weeks ago I was approached with a task of writing 
simple microservice, and I decided to give D a chance. The 
problem with performance became apparent very soon, and I started 
to look for the cause. I tried all existing libraries, and noted 
pretty mature dpq2 and ddb (and it's hb-ddb fork) libs. I didn't 
like the first one, because I didn't want to leave vibe-d 
eventloop, and the second one was native (big plus for me), 
supported vibe-d sockets. I forked it, multiplied performance by 
hacking in write buffer, but then the whole philosophy behind 
it's code structure started to bother me, it incapsulated too 
much IMHO. I needed something low-level, with access to message 
nature of the protocol.

So, here's another one, uses native PSQL EQ TCP protocol:
https://github.com/Boris-Barboris/dpeq
https://www.postgresql.org/docs/9.5/static/protocol.html

Barely any type support, barely tested, but my little service 
with it's two tables actually responds to requests. It's 
something.

Maybe something good will happen and it will find it's use, maybe 
someone will come and write good generic ORM\DB tool for D, and 
we'll have a good low-level client to hack into it as an PSQL 
adapter, maybe it will perish. Who knows. It will definetly go, 
if I won't publish it though.

If anyone here has some insight on typical architectural errors 
wich make ORM writer's life hard, please share.
Sep 03 2017
next sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2017-09-04 00:57, Boris-Barboris wrote:
 I tried all existing libraries, and noted pretty mature dpq2 and 
 ddb (and it's hb-ddb fork) libs. I didn't like the first one, because I 
 didn't want to leave vibe-d eventloop, and the second one was native 
 (big plus for me), supported vibe-d sockets. I forked it, multiplied 
 performance by hacking in write buffer, but then the whole philosophy 
 behind it's code structure started to bother me, it incapsulated too 
 much IMHO. I needed something low-level, with access to message nature 
 of the protocol.
If would be great if you want to upstream your improvements. I think it's a bit unfortunate that everyone is rolling their own implementations in this community instead of working together. I would say that it's rare to need direct access to the messages of the protocol. But perhaps the library can be better structured in layers/levels, allowing the user to pick the most suitable level for his/her needs. -- /Jacob Carlborg
Sep 03 2017
parent reply Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 06:40:09 UTC, Jacob Carlborg wrote:
 If would be great if you want to upstream your improvements.

 I think it's a bit unfortunate that everyone is rolling their 
 own implementations in this community instead of working 
 together. I would say that it's rare to need direct access to 
 the messages of the protocol. But perhaps the library can be 
 better structured in layers/levels, allowing the user to pick 
 the most suitable level for his/her needs.
I did try (for example https://github.com/teamhackback/hb-ddb/pull/38, branch with the buffer: https://github.com/Boris-Barboris/hb-ddb/commits/buffered_sockets), up until the point I understood I dislike it so much it would be easier for me to rewrite it. Amount of possible message combinations made me write some hack methods like prepare_and_bind, bind_and_query, bind_and_execute... I kinda gave up. It is rare indeed, I would not recommend it to anyone sane, but abstractions leak, and when they do, they better not have everything marked private or belong to separate address space.
Sep 04 2017
next sibling parent reply Suliman <evermind live.ru> writes:
Could you give an example how to make connection object if I need 
access to it from several classes? Should it be global?
Sep 04 2017
parent Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 09:30:06 UTC, Suliman wrote:
 Could you give an example how to make connection object if I 
 need access to it from several classes? Should it be global?
"""Good way""" is probably to wrap it into some ConnectionPool (at least that's what I did). Snipper from what I use: final class PostgresStorage { private ConnectionPool!PostgresConection m_pool; ... /// PostgresConection factory used by vibe-d connection pool private PostgresConection spawn_connection() safe { return new PostgresConection(someparams); } /// Client code uses this to get connection auto get_connection() { log_debug("Trying to lock PSQL connection"); auto con = m_pool.lockConnection(); log_debug("PSQL connection locked"); return con; } this() { m_pool = new ConnectionPool!PostgresConection( &spawn_connection, CONF.storageConfig.psql.poolsize); } } alias PSQLConnT = PSQLConnection!(SocketT); /// PSQL connection that is user-friendly final class PostgresConection { private PSQLConnT m_con; // actual dpeq connection // here goes stuff you want to expose to client code (REST api handlers or anything else. ... some constructors and methods, for example: private this() { m_con = new PSQLConnT(m_conn_params); // from global parameters, taken from config } } Honestly, tastes differ, It's your wheel to invent. I use global Storage object, that can give out connections from connection pool, each connection has collection objects with methods to operate on persistent objects. Transactions are handled by connection, other stuff - from collections. It's not like there is some golden standard. If you are not writing anything concurrent, you can just make it global.
Sep 04 2017
prev sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2017-09-04 10:12, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 06:40:09 UTC, Jacob Carlborg wrote:
 If would be great if you want to upstream your improvements.

 I think it's a bit unfortunate that everyone is rolling their own 
 implementations in this community instead of working together. I would 
 say that it's rare to need direct access to the messages of the 
 protocol. But perhaps the library can be better structured in 
 layers/levels, allowing the user to pick the most suitable level for 
 his/her needs.
I did try (for example https://github.com/teamhackback/hb-ddb/pull/38, branch with the buffer: https://github.com/Boris-Barboris/hb-ddb/commits/buffered_sockets),
Ah, ok. I didn't know about hb-ddb until you started this thread. I'm currently one of the maintainers of ddb and I haven't seen anything upstreamed there.
 up until the point I understood I dislike it so much it would be easier 
 for me to rewrite it. Amount of possible message combinations made me 
 write some hack methods like prepare_and_bind, bind_and_query, 
 bind_and_execute... I kinda gave up.
Ok. -- /Jacob Carlborg
Sep 04 2017
next sibling parent Paolo Invernizzi <paolo.invernizzi gmail.com> writes:
On Monday, 4 September 2017 at 12:07:29 UTC, Jacob Carlborg wrote:

 Ah, ok. I didn't know about hb-ddb until you started this 
 thread. I'm currently one of the maintainers of ddb and I 
 haven't seen anything upstreamed there.
Me too... /P
Sep 04 2017
prev sibling parent Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 12:07:29 UTC, Jacob Carlborg wrote:
 Ah, ok. I didn't know about hb-ddb until you started this 
 thread. I'm currently one of the maintainers of ddb and I 
 haven't seen anything upstreamed there.
Haha, yeah, I guess I've developed a habit of looking at github network graph of every project I touch first, before diving in.
Sep 04 2017
prev sibling next sibling parent reply denizzzka <4d.eniz.zz gmail.com> writes:
On Sunday, 3 September 2017 at 22:57:39 UTC, Boris-Barboris wrote:

 I tried all existing libraries, and noted pretty mature dpq2 
 and ddb (and it's hb-ddb fork) libs. I didn't like the first 
 one, because I didn't want to leave vibe-d eventloop,
https://github.com/denizzzka/vibe.d.db.postgresql uses dpq2 backend
Sep 04 2017
parent reply Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 14:42:31 UTC, denizzzka wrote:
 https://github.com/denizzzka/vibe.d.db.postgresql uses dpq2 
 backend
https://github.com/denizzzka/vibe.d.db.postgresql/blob/master/source/vibe/db/postgresql/package.d#L92 Very interesting read, thank you. Making this: https://github.com/denizzzka/vibe.d.db.postgresql/blob/master/source/vibe/db/postgresql/package.d#L131 private is probably a crime against humanity though. I mostly bothered with EQ protocol because I wanted to send PARSE + BIND + EXEC + SYNC without blocking. Is that possible with socket blocking flag manipulation and moderate source code modification?
Sep 04 2017
parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:11:37 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 14:42:31 UTC, denizzzka wrote:
 https://github.com/denizzzka/vibe.d.db.postgresql uses dpq2 
 backend
https://github.com/denizzzka/vibe.d.db.postgresql/blob/master/source/vibe/db/postgresql/package.d#L92 Very interesting read, thank you.
?
 Making this:
 https://github.com/denizzzka/vibe.d.db.postgresql/blob/master/source/vibe/db/postgresql/package.d#L131
 private is probably a crime against humanity though.
What do you mean?
 I mostly bothered with EQ protocol because I wanted to send 
 PARSE + BIND + EXEC + SYNC without blocking. Is that possible 
 with socket blocking flag manipulation and moderate source code 
 modification?
Possible without any src modify.
Sep 04 2017
parent reply Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 15:28:11 UTC, denizzzka wrote:
 What do you mean?
It seems like a very generic method way to send batched messages, wich would give a lot of freedom.
 Possible without any src modify.
Will linux socket get a send called between them, or it will be buffered by libpq\dpq2?
Sep 04 2017
parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:32:51 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 15:28:11 UTC, denizzzka wrote:
 What do you mean?
It seems like a very generic method way to send batched messages, wich would give a lot of freedom.
This is just internal function. Don't try to call it from your application.
 Possible without any src modify.
Will linux socket get a send called between them, or it will be buffered by libpq\dpq2?
Oh... What is it: PARSE + BIND + EXEC + SYNC ?
Sep 04 2017
parent reply Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 15:40:47 UTC, denizzzka wrote:
 This is just internal function. Don't try to call it from your 
 application.
I guess it's the Python curse
 Oh... What is it:
 PARSE + BIND + EXEC + SYNC
 ?
Yeah, that's what I do currently, just write the whole command chain (same goes for transaction control statement, they take 15-20 bytes before\after actual commands, so no need to send them separately) when it's built, flush it to socket and block until all results come back, finalized by ReadyForQuery message.
Sep 04 2017
next sibling parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:47:04 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 15:40:47 UTC, denizzzka wrote:
 This is just internal function. Don't try to call it from your 
 application.
I guess it's the Python curse
 Oh... What is it:
 PARSE + BIND + EXEC + SYNC
 ?
Yeah, that's what I do currently, just write the whole command chain (same goes for transaction control statement, they take 15-20 bytes before\after actual commands, so no need to send them separately) when it's built, flush it to socket and block until all results come back, finalized by ReadyForQuery message.
I dare say that you are engaged in premature optimization.
Sep 04 2017
parent Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 15:53:30 UTC, denizzzka wrote:
 I dare say that you are engaged in premature optimization.
Quite possible, but it took tolerable amount of time. I definetly will play with dpq2 more some day, maybe there is indeed no need to reinvent libpq. But what's done is done.
Sep 04 2017
prev sibling next sibling parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:47:04 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 15:40:47 UTC, denizzzka wrote:
 This is just internal function. Don't try to call it from your 
 application.
I guess it's the Python curse
 Oh... What is it:
 PARSE + BIND + EXEC + SYNC
 ?
Yeah, that's what I do currently, just write the whole command chain (same goes for transaction control statement, they take 15-20 bytes before\after actual commands, so no need to send them separately) when it's built, flush it to socket and block until all results come back, finalized by ReadyForQuery message.
Premature optimization, IMHO non-blocking queries available in libpq, dpq2 and in vibe.d postgres supporting package too.
Sep 04 2017
next sibling parent denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:59:47 UTC, denizzzka wrote:

Sorry fot duplicate answer
Sep 04 2017
prev sibling parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:59:47 UTC, denizzzka wrote:

Sorry fot duplicate answer
Sep 04 2017
parent reply Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 16:05:17 UTC, denizzzka wrote:
 Sorry fot duplicate answer
Ok, just FYI, I guess what I described was implemented in libpq for version 9.6 (but works on any PSQL since 8.4, since they all implement extended query protocol i wrote the client for) in a form of https://2ndquadrant.github.io/postgres/libpq-batch-mode.html It was possible before, just not in libpq. Same goes for binding multiple portals to one prepared (parsed) statement. Since it's a quite new feature (1 year ago), derelict bindings should be updated. If I'll have a spare day, I'll try to do something with it.
Sep 04 2017
next sibling parent denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 17:56:04 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 16:05:17 UTC, denizzzka wrote:
 Sorry fot duplicate answer
Ok, just FYI, I guess what I described was implemented in libpq for version 9.6 (but works on any PSQL since 8.4, since they all implement extended query protocol i wrote the client for) in a form of https://2ndquadrant.github.io/postgres/libpq-batch-mode.html It was possible before, just not in libpq. Same goes for binding multiple portals to one prepared (parsed) statement. Since it's a quite new feature (1 year ago), derelict bindings should be updated. If I'll have a spare day, I'll try to do something with it.
After that I will add batch mode into dpq2.
Sep 04 2017
prev sibling parent reply denizzzka <4d.eniz.zz gmail.com> writes:
On Monday, 4 September 2017 at 17:56:04 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 16:05:17 UTC, denizzzka wrote:
 Sorry fot duplicate answer
Ok, just FYI, I guess what I described was implemented in libpq for version 9.6 (but works on any PSQL since 8.4, since they all implement extended query protocol i wrote the client for) in a form of https://2ndquadrant.github.io/postgres/libpq-batch-mode.html
Theese calls will be added in pq 10, not 9.6.
Sep 04 2017
parent Boris-Barboris <ismailsiege gmail.com> writes:
On Tuesday, 5 September 2017 at 03:22:37 UTC, denizzzka wrote:
 On Monday, 4 September 2017 at 17:56:04 UTC, Boris-Barboris 
 wrote:
 On Monday, 4 September 2017 at 16:05:17 UTC, denizzzka wrote:
 Sorry fot duplicate answer
Ok, just FYI, I guess what I described was implemented in libpq for version 9.6 (but works on any PSQL since 8.4, since they all implement extended query protocol i wrote the client for) in a form of https://2ndquadrant.github.io/postgres/libpq-batch-mode.html
Theese calls will be added in pq 10, not 9.6.
Oh, my bad, I thought 9.6beta was a beta for 9.6.
Sep 04 2017
prev sibling parent reply denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 15:47:04 UTC, Boris-Barboris wrote:
 On Monday, 4 September 2017 at 15:40:47 UTC, denizzzka wrote:
 This is just internal function. Don't try to call it from your 
 application.
I guess it's the Python curse
 Oh... What is it:
 PARSE + BIND + EXEC + SYNC
 ?
Yeah, that's what I do currently, just write the whole command chain (same goes for transaction control statement, they take 15-20 bytes before\after actual commands, so no need to send them separately) when it's built, flush it to socket and block until all results come back, finalized by ReadyForQuery message.
Probably, you don't need async socket operations here at all. Do not mix async sockets and async postgres operations. Batch mode was added because new postgres protocol prohibits statements with separation by ; symbol, IMHO
Sep 04 2017
parent denizzzka <4deni.zzz gmail.com> writes:
On Monday, 4 September 2017 at 18:37:40 UTC, denizzzka wrote:
 On Monday, 4 September 2017 at 15:47:04 UTC, Boris-Barboris 
 wrote:
 On Monday, 4 September 2017 at 15:40:47 UTC, denizzzka wrote:
 This is just internal function. Don't try to call it from 
 your application.
I guess it's the Python curse
 Oh... What is it:
 PARSE + BIND + EXEC + SYNC
 ?
Yeah, that's what I do currently, just write the whole command chain (same goes for transaction control statement, they take 15-20 bytes before\after actual commands, so no need to send them separately) when it's built, flush it to socket and block until all results come back, finalized by ReadyForQuery message.
Probably, you don't need async socket operations here at all.
Oops, my mistake. This is wrong.
Sep 04 2017
prev sibling parent reply denizzzka <4deni.zzz gmail.com> writes:
On Sunday, 3 September 2017 at 22:57:39 UTC, Boris-Barboris wrote:

 If anyone here has some insight on typical architectural errors 
 wich make ORM writer's life hard, please share.
Also, ORM is typical architecturial error. https://en.m.wikipedia.org/wiki/Object-relational_impedance_mismatch
Sep 04 2017
parent Boris-Barboris <ismailsiege gmail.com> writes:
On Monday, 4 September 2017 at 15:45:23 UTC, denizzzka wrote:
 Also, ORM is typical architecturial error.

 https://en.m.wikipedia.org/wiki/Object-relational_impedance_mismatch
It's a tool. Error is when you use wrong tool for your job. ORMs surely help many people, and do a lot of good.
Sep 04 2017