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