pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedReleased updated Tcl interfaces: pgin.tcl-2.1.0, pgtclng-1.5.1
4+ messages / 3 participants
[nested] [flat]
* Released updated Tcl interfaces: pgin.tcl-2.1.0, pgtclng-1.5.1
@ 2004-06-21 01:05 L J Bayuk <ljb220@mindspring.com>
0 siblings, 1 reply; 4+ messages in thread
From: L J Bayuk @ 2004-06-21 01:05 UTC (permalink / raw)
To: pgsql-interfaces
Updated versions of these two implementations of the Tcl interface to
PostgreSQL were released recently:
pgin.tcl, the "pure-TCL" implementation, at:
http://gborg.postgresql.org/project/pgintcl/
Currently available for download:
+ pgintcl-2.1.0.tgz
pgtcl-ng, the libpq-based "next generation" implementation, at:
http://gborg.postgresql.org/project/pgtclng/
Currently available for download:
+ pgtcl1.5.1.tar.gz (Source distribution)
+ pgtcl1.5.1-win32.zip (Binary distribution for Windows)
+ pgtcldocs-20040609.zip (A Pgtcl Reference Manual)
You can look at the manual online, until it finds a better home, at:
http://lbayuk.home.mindspring.com/pgtcldocs/
Pgtclng is the "next generation" libpq-based Tcl interface to PostgreSQL.
It adds many new commands and features which take advantage of new
capabilities in the PostgreSQL-7.4 protocol and libpq client library.
Pgin.tcl is a Tcl interface to PostgreSQL which is written entirely in Tcl.
It is mostly but not entirely command-compatible with pgtclng. Both of
these implementations are only for use with PostgreSQL 7.4 and up.
New features in these releases include:
* Get command status tag with: pg_result -cmdStatus (suggested by levanj)
* Parse, bind, and execute SQL statement with: pg_exec_params,
complements pg_exec_prepared; both are binary safe.
* Extended pg_exec to support optional parameters (idea from karl's
implementation in Gborg pgtcl CVS); this is a text-format-only version
of pg_exec_params.
* Bug fix (pgin.tcl) and improvement (pgtclng) to pg_execute error handling.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Released updated Tcl interfaces: pgin.tcl-2.1.0,
@ 2004-06-22 22:39 Nigel J. Andrews <nandrews@investsystems.co.uk>
parent: L J Bayuk <ljb220@mindspring.com>
0 siblings, 1 reply; 4+ messages in thread
From: Nigel J. Andrews @ 2004-06-22 22:39 UTC (permalink / raw)
To: L J Bayuk <ljb220@mindspring.com>; +Cc: pgsql-interfaces
Whoa, I'll be off getting that [pgin.tcl] soon, I've been playing with the
previous version a little.
However, since making some changes to my code I've not had time to do the
investigation so I just thought I'd ask if there's been any reports on the
relative speed of normal sql execution against prepared statements.
The situation is that I had a proof of concept type implementation of a db
interface in an application that used plain SQL execution with pg_execute (I
think that's the one) and taking it closer to a production level system I
restructured my code a little and switched to using prepared statements. As I
say I haven't had time to investigate if it's my code changes or the use of
prepared statements but I think my newer version runs noticibly slower. I've
got this idea in my head that it's the binary packing that's slowing things,
coupled with the fact that the statements are pretty simple insert
commands. Transactions shouldn't be an issue as at worst this newer version
uses the same number of inserts per transaction as the older version and at
best uses twice the number (basically I can't remember if the earlier version
used transactions at all).
So to summarise, I'm thinking the packing into the prepared statement execution
protocol is perhaps taking significantly longer than the db server is to parse
the plain SQL for such small, simple inserts but do not have any experimental
results to actually show that yet.
Other information: the db server runs on a separate box on a gigabit network,
the client is a TCL app with some extensions, which may have an effect on the
main TCL loop (takes 5 minutes to start with 100% (near enough) cpu usage to
load data from another remote system (probably not more than 100MB) and this
data load definitely could not be IO bound.
BTW, thanks a bunch for this interface. Because of the above extensions I
really wasn't happy about pluging in another extension for PG but knew of this
TCL only implementation so went straight for it. It proved the concept and it's
only not in a financial data system now because my interface was under-designed
for a production system and there's a perception that the PgSQL idea needs to
be proved capable of handling the load. Which is fair enough, although I do
keep saying that I see no reason for it not to handle a couple of dozen
clients logging user interactions, even running on the play linux box it's
currently running on. Trouble with that is this noticible slow down (noticed
when doing quite a few inserts in response to a single user action).
Hmmm...someone should shoot me for writing long rambling emails to these lists.
--
Nigel Andrews
On Sun, 20 Jun 2004, L J Bayuk wrote:
> Updated versions of these two implementations of the Tcl interface to
> PostgreSQL were released recently:
>
> pgin.tcl, the "pure-TCL" implementation, at:
> http://gborg.postgresql.org/project/pgintcl/
> Currently available for download:
> + pgintcl-2.1.0.tgz
>
> pgtcl-ng, the libpq-based "next generation" implementation, at:
> http://gborg.postgresql.org/project/pgtclng/
> Currently available for download:
> + pgtcl1.5.1.tar.gz (Source distribution)
> + pgtcl1.5.1-win32.zip (Binary distribution for Windows)
> + pgtcldocs-20040609.zip (A Pgtcl Reference Manual)
> You can look at the manual online, until it finds a better home, at:
> http://lbayuk.home.mindspring.com/pgtcldocs/
>
> Pgtclng is the "next generation" libpq-based Tcl interface to PostgreSQL.
> It adds many new commands and features which take advantage of new
> capabilities in the PostgreSQL-7.4 protocol and libpq client library.
> Pgin.tcl is a Tcl interface to PostgreSQL which is written entirely in Tcl.
> It is mostly but not entirely command-compatible with pgtclng. Both of
> these implementations are only for use with PostgreSQL 7.4 and up.
>
> New features in these releases include:
> * Get command status tag with: pg_result -cmdStatus (suggested by levanj)
> * Parse, bind, and execute SQL statement with: pg_exec_params,
> complements pg_exec_prepared; both are binary safe.
> * Extended pg_exec to support optional parameters (idea from karl's
> implementation in Gborg pgtcl CVS); this is a text-format-only version
> of pg_exec_params.
> * Bug fix (pgin.tcl) and improvement (pgtclng) to pg_execute error handling.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Released updated Tcl interfaces: pgin.tcl-2.1.0,
@ 2004-06-22 23:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-06-22 23:08 UTC (permalink / raw)
To: Nigel J. Andrews <nandrews@investsystems.co.uk>; +Cc: L J Bayuk <ljb220@mindspring.com>; pgsql-interfaces
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> However, since making some changes to my code I've not had time to do the
> investigation so I just thought I'd ask if there's been any reports on the
> relative speed of normal sql execution against prepared statements.
You can lose if the prepared statements are parameterized in such a way
that the planner can't see critical information. This has been hashed
over many a time in the pgsql-performance archives (though mostly in the
context of queries in plpgsql functions). Try EXPLAIN EXECUTE and see
if you get a materially different plan than with the straight query.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Released updated Tcl interfaces: pgin.tcl-2.1.0,
@ 2004-06-30 22:29 Nigel J. Andrews <nandrews@investsystems.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Nigel J. Andrews @ 2004-06-30 22:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: L J Bayuk <ljb220@mindspring.com>; pgsql-interfaces
On Tue, 22 Jun 2004, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > However, since making some changes to my code I've not had time to do the
> > investigation so I just thought I'd ask if there's been any reports on the
> > relative speed of normal sql execution against prepared statements.
>
> You can lose if the prepared statements are parameterized in such a way
> that the planner can't see critical information. This has been hashed
> over many a time in the pgsql-performance archives (though mostly in the
> context of queries in plpgsql functions). Try EXPLAIN EXECUTE and see
> if you get a materially different plan than with the straight query.
Cheeers Tom, I must admit that it only fleetingly crossed my mind that it could
be a server/planning issue. However, I did go away and check; eventually. As
the two plans are boringly similar, only difference being the param vs const
parts in verbose info with both showing type 25 (text I hope), I'll not even
attach them here. Also the timings were much the same.
I did then go and do some checks running from the TCL client
app; eventually. These results I show below:
% DBCore::_Prepare contriblog aaa {text} {select current_timestamp, $1}
0
% time {DBCore::_ExecPrepared contriblog aaa hello} 100
39370 microseconds per iteration
% time {DBCore::_Execute contriblog {select current_timestamp, 'hello'}} 100
1250 microseconds per iteration
% time {pg_exec_prepared $DBCore::_contriblog_conn aaa T {T} {hello}} 100
39220 microseconds per iteration
[I used only 100 iterations since using 1000 for the prepared statements tends
to block the application main loop long enough for the other components to be
affected, which means there's a big nasty delay as they reinitialise after the
test finishes and I wanted to sure of not including that time in the test
results.]
This is not one of the queries the app. is using, surprisingly, but the results
are in line with the results from a real query. The _ExecPrepared behaves a
little like pg_execute and _Execute is just a small wrapper around
pg_execute. Just to check that the extra code in _ExecPrepared isn't
significant I ran the pg_exec_prepared directly, and although it seems it adds
200usec to the execution time it's not really significant.
Obviously the prepared query execution is an order of magnitude slower and it's
the TCL interface that's making it so.
This is still the older version of pgintcl I hasten to add, took me this long
to run these few simple tests no chance of updating the package yet! Also, it
doesn't detract from the usefulness of this pure TCL interface for me, I've
switched the code path taken back to using plain SQL via pg_execute and it's
not noticable in the application once more.
--
Nigel Andrews
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-06-30 22:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-21 01:05 Released updated Tcl interfaces: pgin.tcl-2.1.0, pgtclng-1.5.1 L J Bayuk <ljb220@mindspring.com>
2004-06-22 22:39 ` Re: Released updated Tcl interfaces: pgin.tcl-2.1.0, Nigel J. Andrews <nandrews@investsystems.co.uk>
2004-06-22 23:08 ` Re: Released updated Tcl interfaces: pgin.tcl-2.1.0, Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 22:29 ` Re: Released updated Tcl interfaces: pgin.tcl-2.1.0, Nigel J. Andrews <nandrews@investsystems.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox