Thread: Libpq question
It appears that some developers (Davart) are by-passing the standard client library, “libpq.dll”, and directly accessing the server using Delphi or FPC. I am not sure of the advantage here. All libpq.dll functions can be called from Delphi or FPC by simply using the following example pascal coding: const LIBPQ_PATH = 'C:\PG\libpq.dll'; // or wherever type PGconn = Pointer; PPGresult = Pointer; var Conn: PGconn; ResultSet: PPGresult; function PQconnectdbParams(keywords, values: PChar; expand_dbname: integer): PGconn; cdecl; external LIBPQ_PATH name 'PQconnectdbParams'; function PQconnectdb(conninfo: PChar): PGConn; cdecl; external LIBPQ_PATH name 'PQconnectdb'; function PQsetdbLogin(Host, Port, Options, Tty, Db, User, Passwd: PChar): PGconn; cdecl; external LIBPQ_PATH name 'PQsetdbLogin'; There are translation programs like “h2pas” that will do most of the work for you, seeing that you may have to translate 160+ c functions. Nonetheless, it shouldn’t take long, and one would have a nice module, (unit) to use for building a PostgreSQL database application using Delphi or FPC. Using the module one could build components as many have done. *** So...the question: Is there a good reason why you might want to NOT use libpq.dll, and just directly access the server through direct function calls? *** Btw, I just compiled libpq.dll with Visual C++ 2008. The resulting compilation (32bit) was 109 KB as opposed to 165 KB which was downloaded (Enterprise site). I use C++ very little, so this is probably due to compiler settings / flags, or perhaps just the use of just another compiler. Thanks John Townsend
On Thu, May 17, 2012 at 1:21 AM, John Townsend <jtownsend@advancedformulas.com> wrote: > *** So...the question: Is there a good reason why you might want to NOT use > libpq.dll, and just directly access the server through direct function > calls? *** I don't know what you mean by function calls, but the Pike Postgres module directly speaks and interprets the socket protocol. It's faster than having an extra layer in there. http://pike.ida.liu.se/generated/manual/modref/ex/predef_3A_3A/Sql/pgsql.html ChrisA
On Wed, May 16, 2012 at 10:21 AM, John Townsend <jtownsend@advancedformulas.com> wrote: > It appears that some developers (Davart) are by-passing the standard client > library, “libpq.dll”, and directly accessing the server using Delphi or FPC. > I am not sure of the advantage here. All libpq.dll functions can be called > from Delphi or FPC by simply using the following example pascal coding: > const > > LIBPQ_PATH = 'C:\PG\libpq.dll'; // or wherever > > type > > PGconn = Pointer; > PPGresult = Pointer; > > var > Conn: PGconn; > ResultSet: PPGresult; > > > function PQconnectdbParams(keywords, values: PChar; expand_dbname: integer): > PGconn; cdecl; > > external LIBPQ_PATH name 'PQconnectdbParams'; > > function PQconnectdb(conninfo: PChar): PGConn; cdecl; > > external LIBPQ_PATH name 'PQconnectdb'; > > function PQsetdbLogin(Host, Port, Options, Tty, Db, User, Passwd: PChar): > PGconn; cdecl; > > external LIBPQ_PATH name 'PQsetdbLogin'; > > There are translation programs like “h2pas” that will do most of the work > for you, seeing that you may have to translate 160+ c functions. > Nonetheless, it shouldn’t take long, and one would have a nice module, > (unit) to use for building a PostgreSQL database application using Delphi or > FPC. Using the module one could build components as many have done. > > *** So...the question: Is there a good reason why you might want to NOT use > libpq.dll, and just directly access the server through direct function > calls? *** Not really. I'm skeptical that Delphi is in fact bypassing libpq. Delphi has an abstract database connection object (TDatabase etc) that requires a driver to run. Most of the drivers I've seen (Zeos, etc) wrap libpq. Some client drivers, notably JDBC, do bypass libpq. This is not 'direct function calls' but a library that implements the postgres protocol. I generally think it's a bad idea to bypass libpq, especially if your client stack has good compatibility with C libraries, which ObjectPascal does. merlin
John Townsend wrote: > It appears that some developers (Davart) are by-passing the standard > client library, “libpq.dll”, and directly accessing the server using > Delphi or FPC. I am not sure of the advantage here. All libpq.dll I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. Those who bypass libpq probably uses odbc connections or similar. zeljko
On Sun, May 20, 2012 at 02:51:42PM +0200, zeljko wrote: > John Townsend wrote: > > > It appears that some developers (Davart) are by-passing the standard > > client library, ???libpq.dll???, and directly accessing the server using > > Delphi or FPC. I am not sure of the advantage here. All libpq.dll > > I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. > Those who bypass libpq probably uses odbc connections or similar. The PostgreSQL-ODBC drivers that I know of use libpq as well. There are other implementations of of the libpq protocol, the Java lib being the major one. There are pure perl/python implementations but AFAIK they are not widely used. It's not common to not use libpq. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment
By by-passing the "dll" (or "so" on Linux) library I mean you write function or procedure calls to the server that is running as a service on Windows. You don't use the library with its 160 exported functions. You connect directly to the server thus saving one layer of protocols. To do this, you have to translate all the c functions you need (not just the headers or ".h" files) into pascal. Not a trivial task! Would it be worth it? Depends, but for most situations would not give you more speed. With FPC comes a nice utility called h2pas.exe that does a decent job and can save you a lot of time. You still have to clean some translations. Look at the *.c & *.h \src\interfaces\libpq directory that comes with the postgres source. John On 5/20/2012 7:51 AM, zeljko wrote: > John Townsend wrote: > >> It appears that some developers (Davart) are by-passing the standard >> client library, “libpq.dll”, and directly accessing the server using >> Delphi or FPC. I am not sure of the advantage here. All libpq.dll > I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. > Those who bypass libpq probably uses odbc connections or similar. > > zeljko > >
On 05/20/12 12:52 PM, John Townsend wrote: > By by-passing the "dll" (or "so" on Linux) library I mean you write > function or procedure calls to the server that is running as a service > on Windows. You don't use the library with its 160 exported functions. > You connect directly to the server thus saving one layer of > protocols. To do this, you have to translate all the c functions you > need (not just the headers or ".h" files) into pascal. Not a trivial > task! the database service is a completely separate collection of processes. you can't just 'call' between processes, you need a RPC mechanism. sockets are as good a mechanism as any. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Mon, May 21, 2012 at 6:12 AM, John R Pierce <pierce@hogranch.com> wrote: > On 05/20/12 12:52 PM, John Townsend wrote: >> >> By by-passing the "dll" (or "so" on Linux) library I mean you write >> function or procedure calls to the server that is running as a service on >> Windows. You don't use the library with its 160 exported functions. You >> connect directly to the server thus saving one layer of protocols. To do >> this, you have to translate all the c functions you need (not just the >> headers or ".h" files) into pascal. Not a trivial task! > > the database service is a completely separate collection of processes. you > can't just 'call' between processes, you need a RPC mechanism. sockets are > as good a mechanism as any. In that case, yes, there are such implementations around. Martijn mentioned a few, and I mentioned the Pike one, all of which do indeed bypass libpq and talk directly to the server. It is, as I understand it, an open and stable protocol, so it's no different from writing a program that connects to port 25 and talks SMTP rather than dropping to sendmail. Chris Angelico
On 2012-05-16, John Townsend <jtownsend@advancedformulas.com> wrote: > *** So...the question: Is there a good reason why you might want to NOT > use libpq.dll, and just directly access the server through direct > function calls? *** libpq binds you to using NUL terminated C strings, and, no doubt, other C idioms. if you do it directly you can use the native object model of your chosen platform instead of neding to wrap the libpq allocator in your native object model etc... ther's probably other reasons too. > Btw, I just compiled libpq.dll with Visual C++ 2008. The resulting > compilation (32bit) was 109 KB as opposed to 165 KB which was downloaded > (Enterprise site). I use C++ very little, so this is probably due to > compiler settings / flags, or perhaps just the use of just another compiler. It could be the difference between stripped and non-stripped. I have not looked closely at the provided libpq. -- ⚂⚃ 100% natural
In that case, yes, there are such implementations around. Martijn mentioned a few, and I mentioned the Pike one, all of which do indeed bypass libpq and talk directly to the server. It is, as I understand it, an open and stable protocol, so it's no different from writing a program that connects to port 25 and talks SMTP rather than dropping to sendmail. =================== I downloaded PIKE. The "PostgreSQL direct network module for Pike", pgsql.pike (and the other modules), shows how it was done. Many thanks for the tip. I rarely step out of Delphi, so I was unaware of the power and versatility of Pike. The Sql.pmod reveals the other databases to which you can connect. I think any PostGreSQL user or programmer ought to try Pike. jt
On Mon, May 21, 2012 at 9:05 PM, John Townsend <jtownsend@advancedformulas.com> wrote: > I downloaded PIKE. The "PostgreSQL direct network module for Pike", > pgsql.pike (and the other modules), shows how it was done. > > Many thanks for the tip. I rarely step out of Delphi, so I was unaware of > the power and versatility of Pike. The Sql.pmod reveals the other databases > to which you can connect. > > I think any PostGreSQL user or programmer ought to try Pike. I absolutely agree! Pike is awesome. Actually, I've picked up something of a reputation at work and various places for advocating Pike for everything from quick scripts to embedded servers to database manipulators/versioners to interactive calculators... its main boasting-feature, though, being the ability to easily reload altered code without shutting the application down. I have a server that's been running for 96 weeks and a smidge so far; nary a glitch and plenty of code changes. The Pike development community is small but very helpful. ChrisA
On Sun, May 20, 2012 at 2:52 PM, John Townsend <jtownsend@advancedformulas.com> wrote: > By by-passing the "dll" (or "so" on Linux) library I mean you write function > or procedure calls to the server that is running as a service on Windows. > You don't use the library with its 160 exported functions. You connect > directly to the server thus saving one layer of protocols. To do this, you > have to translate all the c functions you need (not just the headers or ".h" > files) into pascal. Not a trivial task! It is possible to write functions and procedures on the server but they must still be invoked from the client side. Currently the only way to use any server side features is through the frontend/backend protocol. libpq is a library that handles the frontend side of the processing -- that's why the header is named libpq-fe.h: it's for the front end. You can bypass libpq but that would mean you'd have to write your own client side handler for the protocol -- definitely a non-trivial project and you'd probably end up with something worse than libpq unless you are an expert programmer. In other words, you can bypass libpq itself but not the protocol. To bypass the protocol, start moving your code into server functions. > Would it be worth it? Depends, but for most situations would not give you > more speed. With FPC comes a nice utility called h2pas.exe that does a > decent job and can save you a lot of time. You still have to clean some > translations. I believe Zeos has an already converted .pas file for libpq. merlin
Jasen Betts <jasen@xnet.co.nz> writes:
> On 2012-05-16, John Townsend <jtownsend@advancedformulas.com> wrote:
>> *** So...the question: Is there a good reason why you might want to NOT
>> use libpq.dll, and just directly access the server through direct
>> function calls? ***
> libpq binds you to using NUL terminated C strings, and, no doubt, other C
> idioms.
Note that bypassing libpq will not get you around that one, because the
server (and indeed the wire protocol) also use nul-terminated strings.
Generally the places where people have chosen to reimplement the
protocol from scratch are where they *couldn't* use libpq for some
reason or other; for instance the JDBC driver doesn't use libpq because
non-Java code is painful to deal with in a Java environment. I'm pretty
doubtful that it's worth anybody's trouble to reimplement just to save a
layer of function calls.
regards, tom lane
On 5/21/2012 7:56 AM, Merlin Moncure wrote:
But to be a good you need to know both languages very well, that is C++ and pascal. Translating the header files is easy, that's why most developers use libpq.dll or .so. One uses only about 20 or so functions - that is - for most projects. Translating the C++ function body (some are pretty long and complex) takes more knowledge, not only about C but about PostGreSQL.
Also, you must then translate all the internal functions that libpq uses. There are currently 160 functions exported.
This kind of a project was done all the time by Borland programmers in the past.
Yes, they have translated the most used functions, the c header files, that is. My count shows ~ 70 out of 160.
Thanks for your reply.
John
Therein lies the problem, - I am not an expert. I know quite a bit about Delphi & SQL, but that's it.On Sun, May 20, 2012 at 2:52 PM, John Townsend <jtownsend@advancedformulas.com> wrote:By by-passing the "dll" (or "so" on Linux) library I mean you write function or procedure calls to the server that is running as a service on Windows. You don't use the library with its 160 exported functions. You connect directly to the server thus saving one layer of protocols. To do this, you have to translate all the c functions you need (not just the headers or ".h" files) into pascal. Not a trivial task!It is possible to write functions and procedures on the server but they must still be invoked from the client side. Currently the only way to use any server side features is through the frontend/backend protocol. libpq is a library that handles the frontend side of the processing -- that's why the header is named libpq-fe.h: it's for the front end. You can bypass libpq but that would mean you'd have to write your own client side handler for the protocol -- definitely a non-trivial project and you'd probably end up with something worse than libpq unless you are an expert programmer.
But to be a good you need to know both languages very well, that is C++ and pascal. Translating the header files is easy, that's why most developers use libpq.dll or .so. One uses only about 20 or so functions - that is - for most projects. Translating the C++ function body (some are pretty long and complex) takes more knowledge, not only about C but about PostGreSQL.
Also, you must then translate all the internal functions that libpq uses. There are currently 160 functions exported.
This kind of a project was done all the time by Borland programmers in the past.
Using pascal (Delphi or FPC). How would this be done? This is the ultimate goal.In other words, you can bypass libpq itself but not the protocol. To bypass the protocol, start moving your code into server functions.
Would it be worth it? Depends, but for most situations would not give you more speed. With FPC comes a nice utility called h2pas.exe that does a decent job and can save you a lot of time. You still have to clean some translations.I believe Zeos has an already converted .pas file for libpq.
Yes, they have translated the most used functions, the c header files, that is. My count shows ~ 70 out of 160.
Thanks for your reply.
John
On Mon, May 21, 2012 at 9:54 AM, John Townsend <jtownsend@advancedformulas.com> wrote: > On 5/21/2012 7:56 AM, Merlin Moncure wrote: > > On Sun, May 20, 2012 at 2:52 PM, John Townsend > <jtownsend@advancedformulas.com> wrote: > > By by-passing the "dll" (or "so" on Linux) library I mean you write function > or procedure calls to the server that is running as a service on Windows. > You don't use the library with its 160 exported functions. You connect > directly to the server thus saving one layer of protocols. To do this, you > have to translate all the c functions you need (not just the headers or ".h" > files) into pascal. Not a trivial task! > > It is possible to write functions and procedures on the server but > they must still be invoked from the client side. Currently the only > way to use any server side features is through the frontend/backend > protocol. libpq is a library that handles the frontend side of the > processing -- that's why the header is named libpq-fe.h: it's for the > front end. You can bypass libpq but that would mean you'd have to > write your own client side handler for the protocol -- definitely a > non-trivial project and you'd probably end up with something worse > than libpq unless you are an expert programmer. > > Therein lies the problem, - I am not an expert. I know quite a bit about > Delphi & SQL, but that's it. > > But to be a good you need to know both languages very well, that is C++ and > pascal. Translating the header files is easy, that's why most developers > use libpq.dll or .so. One uses only about 20 or so functions - that is - for > most projects. Translating the C++ function body (some are pretty long and > complex) takes more knowledge, not only about C but about PostGreSQL. But why would you want to do that? C and ObjectPascal have compatible linkage so translating function body internals has no useful value that I can see. You wouldn't be optimizing anything since you'd have to write something else to replace it. There is no downside to calling into C functions from pascal. Just use the .pas from Zeos and move on. There's nothing to optimize here; you're looking in the wrong place. >> In other words, you can bypass libpq itself but not the protocol. To >> bypass the protocol, start moving your code into server functions. > > Using pascal (Delphi or FPC). How would this be done? This is the ultimate > goal. Simple: just figure out how to group queries together so that they can be chained on the server. Typically, client library and protocol overhead are only significant when your application is making lots of very simple queries. Start thinking relationally and make your queries larger and smarter and that makes the overhead go away. merlin
zeljko wrote: > John Townsend wrote: > >> It appears that some developers (Davart) are by-passing the standard >> client library, “libpq.dll”, and directly accessing the server using >> Delphi or FPC. I am not sure of the advantage here. All libpq.dll > > I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. > Those who bypass libpq probably uses odbc connections or similar. Apologies for my rather late comment. ODBC, JDBC, Zeos etc. are all appropriate if you want an app to be able to contact different types of backend. However, the further you move from native connections, the more difficult it becomes to use PostgreSQL-specific functionality such as the listen/notify mechanism: I've used this to good effect in an FPC/Lazarus program and while I believe it can be hacked in via ODBC the result is hardly pretty. In addition, while it is in principle possible to "roll your own" interface library, the libpq+pg combination has been in use for 10+ years, is actively maintained, and (hopefully) is bug- and backdoor-free. Reinventing this particular wheel is definitely not something that should be approached casually. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]