Just a note from me - I also agree this thread evolved (or rather devolved)
in a rather unproductive and strange way.
One important note that came out, though, is that adding a new client
message does have a backwards compatibility issue - intelligent proxies
such as pgbouncer/pgpool will probably
On 2016-08-23 11:42:53 -0400, Robert Haas wrote:
> I think this could possibly be done, but it seems a lot better to me
> to just bite the bullet and add a new protocol message. That was
> proposed by Tom Lane on July 31st and I think it's still by far the
> best and easiest idea proposed, except
On Tue, Aug 16, 2016 at 4:48 PM, Andres Freund wrote:
> One approach to solving this, without changing the protocol, would be to
> "fuse" parse/bind/execute/sync together, by peeking ahead in the
> protocol stream. When that combination is seen looking ahead (without
> blocking), optimize it by ha
> Tatsuo>understanding it always uses unnamed portal even if the SQL is like
> "BEGIN" or "COMMIT" (no parameters). They are too often used. Why not
> doing like this?
>
> Does it actually work?
>
> The documentation says named portals last till the end of the transaction:
>
> https://www.postgr
Tatsuo>understanding it always uses unnamed portal even if the SQL is like
"BEGIN" or "COMMIT" (no parameters). They are too often used. Why not
doing like this?
Does it actually work?
The documentation says named portals last till the end of the transaction:
https://www.postgresql.org/docs/9.5/
BTW, there seem to be a room to enhance JDBC driver performance. In my
understanding it always uses unnamed portal even if the SQL is like
"BEGIN" or "COMMIT" (no parameters). They are too often used. Why not
doing like this?
Prepare(stmt=S1,query="BEGIN")
Bind(stmt=S1,portal=P1)
Execute(portal=P1
On 2016-08-16 21:40:32 -0400, Tom Lane wrote:
> Andres Freund writes:
> > On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
> >> Yeah. The extended query protocol was designed to offer a lot of
> >> functionality that people had asked for, like plan re-use and
> >> introspection of the data types ass
Andres Freund writes:
> On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
>> Yeah. The extended query protocol was designed to offer a lot of
>> functionality that people had asked for, like plan re-use and
>> introspection of the data types assigned to query parameters, but that
>> doesn't come at z
On 2016-07-31 17:57:12 -0400, Tom Lane wrote:
> Andres Freund writes:
> > FWIW, I've observed the same with (a bit) more complicated queries. A
> > part of this is that the extended protocol simply does
> > more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
> > simply more wor
Halfway through this mail I suddenly understood something, please read all
the way down before responding...
On Tue, Aug 16, 2016 at 2:16 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Shay> your analogy breaks down. Of course L2 was invented to improve
> performance,
> Shay> but t
Shay> your analogy breaks down. Of course L2 was invented to improve
performance,
Shay> but that doesn't mean that all caches are the same. More precisely, what I
Shay> find objectionable about your approach is not any caching - it's the
Shay> implicit or automatic preparation of statements. This p
>
> I'm not going to respond to the part about dealing with prepared
>> statements errors, since I think we've already covered that and there's
>> nothing new being said. I don't find automatic savepointing acceptable, and
>> a significant change of the PostgreSQL protocol to support this doesn't
>
>
>
> I'm not going to respond to the part about dealing with prepared
> statements errors, since I think we've already covered that and there's
> nothing new being said. I don't find automatic savepointing acceptable, and
> a significant change of the PostgreSQL protocol to support this doesn't
>
On Mon, Aug 15, 2016 at 3:16 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Vladimir>> Yes, that is what happens.
> Vladimir>> The idea is not to mess with gucs.
>
> Shay:> Wow... That is... insane...
>
> Someone might say that "programming languages that enable side-effects
> are i
Vladimir>> Yes, that is what happens.
Vladimir>> The idea is not to mess with gucs.
Shay:> Wow... That is... insane...
Someone might say that "programming languages that enable side-effects
are insane".
Lots of connection pools work by sharing the connections and it is up
to developer
if he can b
On Sat, Aug 13, 2016 at 11:20 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Tatsuo>Interesting. What would happen if a user changes some of GUC
> parameters? Subsequent session accidentally inherits the changed GUC
> parameter?
>
> Yes, that is what happens.
> The idea is not to me
Shay> What? I really didn't understand your point here. All the doc is saying is
Shay> that if the driver doesn't support prepared statements, then using them
Please read again. PreparedStatement is the only way to execute statements
in JDBC API. There's no API that allows user to specify "use
ser
Apologies, I accidentally replied off-list, here's the response I sent.
Vladimir, I suggest you reply to this message with your own response...
On Sat, Aug 13, 2016 at 6:32 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Shay>To be honest, the mere idea of having an SQL parser insid
> Tatsuo>Interesting. What would happen if a user changes some of GUC
> parameters? Subsequent session accidentally inherits the changed GUC
> parameter?
>
> Yes, that is what happens.
Ouch.
> The idea is not to mess with gucs.
>
> Tatsuo>There's nothing wrong with DICARD ALL
> Tatsuo>"DISCARD
Tatsuo>Interesting. What would happen if a user changes some of GUC
parameters? Subsequent session accidentally inherits the changed GUC
parameter?
Yes, that is what happens.
The idea is not to mess with gucs.
Tatsuo>There's nothing wrong with DICARD ALL
Tatsuo>"DISCARD ALL" is perfect for this g
> Shay>I don't know much about the Java world, but both pgbouncer and pgpool
> (the major pools?)
>
> In Java world, https://github.com/brettwooldridge/HikariCP is a very good
> connection pool.
> Neither pgbouncer nor pgpool is required.
> The architecture is: application <=> HikariCP <=> pgjdbc
Shay>To be honest, the mere idea of having an SQL parser inside my driver
makes me shiver.
Same for me.
However I cannot wait for PostgreSQL 18 that does not need client-side
parsing.
Shay>We did, you just dismissed or ignored them
Please prove me wrong, but I did provide a justified answer to b
Vladimir wrote:
Shay>I don't know much about the Java world, but both pgbouncer and pgpool
> (the major pools?)
>
> In Java world, https://github.com/brettwooldridge/HikariCP is a very good
> connection pool.
> Neither pgbouncer nor pgpool is required.
> The architecture is: application <=> Hikar
On 11 August 2016 at 10:18, Shay Rojansky wrote:
>
>
> On Thu, Aug 11, 2016 at 1:22 PM, Vladimir Sitnikov <
> sitnikov.vladi...@gmail.com> wrote:
>
> 2) The driver can use safepoints and autorollback to the good "right
>> before failure" state in case of a known failure. Here's the
>> implementat
Shay>I don't know much about the Java world, but both pgbouncer and pgpool
(the major pools?)
In Java world, https://github.com/brettwooldridge/HikariCP is a very good
connection pool.
Neither pgbouncer nor pgpool is required.
The architecture is: application <=> HikariCP <=> pgjdbc <=> PostgreSQ
On Thu, Aug 11, 2016 at 1:22 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
2) The driver can use safepoints and autorollback to the good "right before
> failure" state in case of a known failure. Here's the implementation:
> https://github.com/pgjdbc/pgjdbc/pull/477
>
As far as I can
Shay>As I said, an error is going to kill the ongoing transaction, how can
this be solved without application logic?
1) At least, some well-defined error code should be created for that kind
of matter.
2) The driver can use safepoints and autorollback to the good "right before
failure" state in c
On Thu, Aug 11, 2016 at 8:39 AM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Shay:
>
>> Prepared statements can have very visible effects apart from the speedup
>> they provide (e.g. failure because of schema changes) It's not that
>> these effects can't be worked around - they can
Shay:
> Prepared statements can have very visible effects apart from the speedup
> they provide (e.g. failure because of schema changes) It's not that these
> effects can't be worked around - they can be - but programmers can be
> surprised by these effects, which can cause difficult-to-diagnose
Vladimir wrote:
Shay> As Tom said, if an application can benefit from preparing, the
> developer has the responsibility (and also the best knowledge) to manage
> preparation, not the driver. Magical behavior under the hood causes
> surprises, hard-to-diagnose bugs etc.
>
> Why do you do C# then?
>
Shay> it's important to note that query parsing and rewriting isn't an
"inevitable evil".
Ok, I stay corrected. ADO.NET have raw mode in the API. That's interesting.
Let's say "lots of heavily used languages do have their own notion of bind
placeholders".
And for the reset, it is still not that h
Some comments...
For the record, I do find implicit/transparent driver-level query
preparation interesting and potentially useful, and have opened
https://github.com/npgsql/npgsql/issues/1237 to think about it - mostly
based on arguments on this thread. One big question mark I have is whether
this
Stephen> While it may have good results in many cases, it's not accurate to
say that using prepared statements will always be faster than not.
There's no silver bullet. <-- that is accurate, but it is useless for
end-user applications
I've never claimed that "server prepared statement" is a silver
* Vladimir Sitnikov (sitnikov.vladi...@gmail.com) wrote:
> 3) "suddently get slow the 6th time" is a PostgreSQL bug that both fails to
> estimate cardinality properly, and it does not provide administrator a way
> to disable the feature (generic vs specific plan).
Dropping and recreating the prepa
Stephen>I encourage you to look through the archives
The thing is pl/pgsql suffers from exactly the same problem.
pl/pgsql is not a typical language of choice (e.g. see Tiobe index and
alike), so the probability of running into "prepared statement issues" was
low.
As more languages would use serv
Robert>But that makes it the job of every driver to implement some
sort of cache, which IMHO isn't a very reasonable position
Let's wait what Shay decides on implementing query cache in npgsql ?
Here's the issue: https://github.com/npgsql/npgsql/issues/1237
He could change his mind when it comes
* Vladimir Sitnikov (sitnikov.vladi...@gmail.com) wrote:
> It works completely transparent to the application, and it does use
> server-prepared statements even though application builds "brand new" sql
> text every time.
And is the source of frequent complaints on various mailing lists along
the
On Wed, Aug 10, 2016 at 11:50 AM, Tom Lane wrote:
> Robert Haas writes:
>> Sure, but I don't want the application to have to know about that, and
>> I don't really think the driver should need to know about that either.
>> Your point, as I understand it, is that sufficiently good query
>> caching
Robert Haas writes:
> Sure, but I don't want the application to have to know about that, and
> I don't really think the driver should need to know about that either.
> Your point, as I understand it, is that sufficiently good query
> caching in the driver can ameliorate the problem, and I agree wi
On Tue, Aug 9, 2016 at 5:07 PM, Vladimir Sitnikov
wrote:
> I do not buy that "dynamically generated queries defeat server-prepared
> usage" argument. It is just not true (see below).
>
> Do you mean "in language X, where X != Java it is impossible to implement a
> query cache"?
> That is just ridi
Robert Haas:
> but for some reason you can't use prepared statements, for example because
> the queries are dynamically generated and . That case is analogous to -M
> extended, not -M prepared. And -M extended is well-known to be SLOWER
>
I do not buy that "dynamically generated queries defeat
On Tue, Aug 9, 2016 at 4:50 AM, Vladimir Sitnikov
wrote:
> I've tried pgbench -M prepared, and it is way faster than pgbench -M simple.
That's true, but it's also testing something completely different from
what Shay is concerned about. -M prepared creates a prepared
statement once, and then exe
On Tue, Aug 9, 2016 at 3:42 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Shay>But here's the more important general point. We're driver
> developers, not application developers. I don't really know what
> performance is "just fine" for each of my users, and what is not worth
> opt
Shay>But here's the more important general point. We're driver developers,
not application developers. I don't really know what performance is "just
fine" for each of my users, and what is not worth optimizing further. Users
may follow best practices, or they may not for various reasons.
Of course
On Tue, Aug 9, 2016 at 8:50 AM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> Shay>There are many scenarios where connections are very short-lived
> (think about webapps where a pooled connection is allocated per-request and
> reset in between)
>
> Why the connection is reset in betwee
Shay>There are many scenarios where connections are very short-lived (think
about webapps where a pooled connection is allocated per-request and reset
in between)
Why the connection is reset in between in the first place?
In pgjdbc we do not reset per-connection statement cache, thus we easily
reu
On Mon, Aug 8, 2016 at 6:44 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
>
> The problem with "empty statement name" is statements with empty name can
>> be reused (for instance, for batch insert executions), so the server side
>> has to do a defensive copy (it cannot predict how man
>> On the other hand, usage of some well-defined statement name to trigger
>> the special case would be fine: all pgbouncer versions would pass those
>> parse/bind/exec message as if it were regular messages.
>
> I do not accept this idea that retroactively defining special semantics
> for certain
I'm sorry, we are discussing technical details with no real-life use case
to cover that.
I do not want to suck time for no reason. Please accept my sincere
apologies for not asking the real-life case earlier.
Shay, can you come up with a real-life use case when those "I claim the
statement will be
Shay Rojansky :
> Ah, I understand the proposal better now - you're not proposing encoding a
> new message type in an old one, but rather a magic statement name in Parse
> which triggers an optimized processing path in PostgreSQL, that wouldn't go
> through the query cache etc.
>
Exactly.
> If
Shay Rojansky writes:
> Ah, I understand the proposal better now - you're not proposing encoding a
> new message type in an old one, but rather a magic statement name in Parse
> which triggers an optimized processing path in PostgreSQL, that wouldn't go
> through the query cache etc.
> If so, isn
Vladimir wrote:
> On the other hand, usage of some well-defined statement name to trigger
>>> the special case would be fine: all pgbouncer versions would pass those
>>> parse/bind/exec message as if it were regular messages.
>>>
>>
>> Can you elaborate on what that means exactly? Are you proposi
Vladimir Sitnikov writes:
> The point is "adding a message to current v3 protocol is not a backward
> compatible change".
> The problem with adding new message types is not only "client support", but
> deployment issues as well: new message would require simultaneous upgrade
> of both backend, cli
Shay Rojansky :
> That's definitely a valid point. But do you think it's a strong enough
> argument to avoid ever adding new messages?
>
The point is "adding a message to current v3 protocol is not a backward
compatible change".
The problem with adding new message types is not only "client suppor
>
> We could call this "protocol 3.1" since it doesn't break backwards
>> compatibility (no incompatible server-initiated message changes, but it
>> does include a feature that won't be supported by servers which only
>> support 3.0. This could be a sort of "semantic versioning" for the protocol
>>
Shay Rojansky :
>
> That sounds right to me. As you say, the server version is sent early in
> the startup phase, before any queries are sent to the backend, so frontends
> know which server they're communicating with.
>
> We could call this "protocol 3.1" since it doesn't break backwards
> compat
On Sun, Aug 7, 2016 at 7:46 PM, Shay Rojansky wrote:
> We could call this "protocol 3.1" since it doesn't break backwards
> compatibility (no incompatible server-initiated message changes, but it does
> include a feature that won't be supported by servers which only support 3.0.
> This could be a
On Sun, Aug 7, 2016 at 6:11 PM, Robert Haas wrote:
> I'm glad reducing the overhead of out-of-line parameters seems like an
> > important goal. FWIW, if as Vladimir seems to suggest, it's possible to
> > bring down the overhead of the v3 extended protocol to somewhere near the
> > simple protocol
On Fri, Aug 5, 2016 at 8:07 PM, Shay Rojansky wrote:
>> > I really don't get what's problematic with posting a message on a
>> > mailing
>> > list about a potential performance issue, to try to get people's
>> > reactions,
>> > without diving into profiling right away. I'm not a PostgreSQL
>> > de
>
> > I really don't get what's problematic with posting a message on a mailing
> > list about a potential performance issue, to try to get people's
> reactions,
> > without diving into profiling right away. I'm not a PostgreSQL developer,
> > have other urgent things to do and don't even spend mos
On Tue, Aug 2, 2016 at 2:00 PM, Shay Rojansky wrote:
>> Shay, why don't you use a profiler? Seriously.
>> I'm afraid "iterate the per-message loop in PostgresMain five times not
>> once" /"just discussing what may or may not be a problem..." is just
>> hand-waving.
>>
>> Come on, it is not that h
On Wed, Aug 3, 2016 at 7:35 PM, Bruce Momjian wrote:
> On Wed, Aug 3, 2016 at 10:02:39AM -0400, Tom Lane wrote:
>> Bruce Momjian writes:
>> > On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
>> >> In hindsight it seems clear that what a lot of apps want out of extended
>> >> protocol is
On Wed, Aug 3, 2016 at 10:02:39AM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
> >> In hindsight it seems clear that what a lot of apps want out of extended
> >> protocol is only the ability to send parameter values out-of-line inste
Tatsuo Ishii :
> Doesn't this patch break an existing behavior of unnamed statements?
> That is, an unnamed statement shall exist until next parse message
> using unnamed statement received. It is possible to use the same
> unnamed statement multiple times in a transaction.
>
>Doesn't this patch
Tom Lane :
> Bruce Momjian writes:
> > On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
> >> In hindsight it seems clear that what a lot of apps want out of extended
> >> protocol is only the ability to send parameter values out-of-line
> instead
> >> of having to quote/escape them into
Bruce Momjian writes:
> On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
>> In hindsight it seems clear that what a lot of apps want out of extended
>> protocol is only the ability to send parameter values out-of-line instead
>> of having to quote/escape them into SQL literals. Maybe an
On Sun, Jul 31, 2016 at 05:57:12PM -0400, Tom Lane wrote:
> In hindsight it seems clear that what a lot of apps want out of extended
> protocol is only the ability to send parameter values out-of-line instead
> of having to quote/escape them into SQL literals. Maybe an idea for the
> fabled V4 pro
Doesn't this patch break an existing behavior of unnamed statements?
That is, an unnamed statement shall exist until next parse message
using unnamed statement received. It is possible to use the same
unnamed statement multiple times in a transaction.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Ja
>
> I really don't get what's problematic with posting a message on a mailing
> list about a potential performance issue, to try to get people's reactions,
> without diving into profiling right away
>
"Benchmark data is a perfect substitute for benchmarking results. Data is
easy to misinterpret, s
On Mon, Aug 1, 2016 at 12:12 PM, Vladimir Sitnikov <
sitnikov.vladi...@gmail.com> wrote:
> The attached patch passes `make check` and it gains 31221 -> 33547
improvement for "extended pgbench of SELECT 1".
>
> The same version gains 35682 in "simple" mode, and "prepared" mode
achieves 46367 (just
Greg wrote:
> I think you're looking at this the wrong way around. 30% of what?
> You're doing these simple read-only selects on a database that
> obviously is entirely in RAM. If you do the math on the numbers you
> gave above the simple protocol took 678 microseconds per transaction
> and the ext
Andres Freund writes:
> FWIW, I've observed the same with (a bit) more complicated queries. A
> part of this is that the extended protocol simply does
> more. PQsendQueryGuts() sends Parse/Bind/Describe/Execute/Sync - that's
> simply more work and data over the wire than a single Q message.
Yeah.
On 2016-07-31 22:26:00 +0100, Greg Stark wrote:
> I think you're looking at this the wrong way around. 30% of what?
> You're doing these simple read-only selects on a database that
> obviously is entirely in RAM. If you do the math on the numbers you
> gave above the simple protocol took 678 micros
On Sun, Jul 31, 2016 at 4:05 PM, Shay Rojansky wrote:
> I'm well aware of how the extended protocol works, but it seems odd for a
> 30% increase in processing time to be the result exclusively of processing 5
> messages instead of just 1 - it doesn't seem like that big a deal (although
> I may be
>
> Shay Rojansky :
>
>> I'm well aware of how the extended protocol works, but it seems odd for a
>> 30% increase in processing time to be the result exclusively of processing
>> 5 messages instead of just 1 - it doesn't seem like that big a deal
>> (although I may be mistaken). I was imagining th
Shay Rojansky :
> I'm well aware of how the extended protocol works, but it seems odd for a
> 30% increase in processing time to be the result exclusively of processing
> 5 messages instead of just 1 - it doesn't seem like that big a deal
> (although I may be mistaken). I was imagining that there'
>
> Without re-using prepared statements or portals, extended protocol is
> always slow because it requires more messages exchanges than simple
> protocol. In pgbench case, it always sends parse, bind, describe,
> execute and sync message in each transaction even if each transaction
> involves iden
> Hi all. I know this has been discussed before, I'd like to know what's the
> current position on this.
>
> Comparing the performance of the simple vs. extended protocols with pgbench
> yields some extreme results:
>
> $ ./pgbench -T 10 -S -M simple -f /tmp/pgbench.sql pgbench
> tps = 14739.8032
Hi all. I know this has been discussed before, I'd like to know what's the
current position on this.
Comparing the performance of the simple vs. extended protocols with pgbench
yields some extreme results:
$ ./pgbench -T 10 -S -M simple -f /tmp/pgbench.sql pgbench
tps = 14739.803253 (excluding co
79 matches
Mail list logo