Kirill,
cursor does not provide a way to limit the fetch size based on the memory
consumption.
Imagine a table like (id int8, value jsonb).
If we use "fetch 1000", then it might require 1GiB on the client if every
row contains 1MiB json.
If the client plays defensively and goes for "fetch 10", it
Hi,
Applications often face an "out of memory" condition as they try to fetch
"N rows" from the database.
If N is small, then the execution becomes inefficient due to many
roundtrips.
If N is high, there's a risk that many rows would overflow the client's
memory.
Note: the client can't stop readi
Hi,
"select * from pg_prepared_xacts" might produce transactions created by a
different user, so the caller won't be able to issue "commit prepared".
I think there should be a view that returns only the transactions that the
caller can commit or rollback.
Is it something that can be implemented a
I reviewed the documentation for "direct ALPN connections' ', and it looks
like it could be improved.
Here's the link:
https://www.postgresql.org/docs/17/protocol-flow.html#PROTOCOL-FLOW-SSL
The currently suggested values for "sslnegotiations" are "direct" and
"postgres".
The project name is Postg
Jelte> If the Execute causes an error, is
Jelte> the Query still executed or not? And what about if the Query fails,
is
Jelte> the Execute before committed or rolled back?
Frankly, if there's a requirement from the backend, I would like it to
produce a corresponding error message.
What do you thi
>When splitting a multi insert statement you're going to duplicate some work
I do not understand why I am going to duplicate some work.
I assume the database does its best to perform all the needed preparation
when processing "parse" message,
and it should perform only the minimum required work wh
>Would performance suffer that much?
I have not benchmarked it much, however, the driver sends "autosave"
queries once (savepoint) or twice(savepoint+release) for every
user-provided query.
If we use extended queries (parse+bind+exec) for every savepoint, that
would result in 3 or 6 messages overh
>Is it possible for the JDBC
>driver to issue a Sync message before sending SAVEPOINT in simple
>query protocol?
Apparently, sending an extra message would increase the overhead of the
protocol, thus reducing the efficiency of the application.
What is the benefit of sending extra Sync?
https://ww
Andres> Of course, but I somehow feel a change of formatting should be
reviewable to
Andres> at least some degree
One way of reviewing the formatting changes is to compare the compiled
binaries.
If the binaries before and after formatting are the same, then there's a
high chance the behaviour is
>You could just hard code that servers newer than a
> specific version would have this support
Suppose PostgreSQL 21 implements "fast TLS"
Suppose pgjdbc 43 supports "fast TLS"
Suppose PgBouncer 1.17.0 does not support "fast TLS" yet
If pgjdbc connects to the DB via balancer, then the server woul
>I don't think it's worth implementing a code path in
> the server like this as it would then become cruft that would be hard
> to ever get rid of.
Do you think the server can de-support the old code path soon?
> I think you can do the same thing, more or less, in the client. Like
> if the driver
It would be great if PostgreSQL supported 'start with TLS', however, how
could clients activate the feature?
I would like to refrain users from configuring the handshake mode, and I
would like to refrain from degrading performance when a new client talks to
an old database.
What if the server tha
>ost readers are going to spend more time
>wondering what the difference is between "columns per table" and "columns
>per tuple"
"tuple" is already mentioned 10 times on "limits" page, so adding "columns
per tuple" is not really obscure.
The comment could be like "for instance, max number of expre
Hi,
Today I hit "ERROR: target lists can have at most 1664 entries", and I was
surprised the limit was not documented.
I suggest that the limit of "1664 columns per tuple" (or whatever is the
right term) should be added
to the list at https://www.postgresql.org/docs/current/limits.html e.g.
after
>Or, does the backend swallow the error, and return only the ReadyForQuery
(I hope not).
What is your backend version?
Here's a well-known case when the backend did swallow the error:
"Error on failed COMMIT"
https://www.postgresql.org/message-id/b9fb50dc-0f6e-15fb-6555-8ddb86f4aa71%40postgresfri
>Just add those files to the global gitignore on your machine
While global gitignore is a nice feature, it won't protect users who do not
know they need to create a global ignore file.
Adding explicit excludes for well-known temporary files into PostgreSQL
sources makes it easier to work with the
>Attached is v23 incorporating the 32-bit transition table, with the
necessary comment adjustments
32bit table is nice.
Would you please replace
https://github.com/BobSteagall/utf_utils/blob/master/src/utf_utils.cpp URL
with
https://github.com/BobSteagall/utf_utils/blob/6b7a465265de2f5fa6133d653
Just wondering, do you have the code in a GitHub/Gitlab branch?
>+ utf8_advance(s, state, len);
>+
>+ /*
>+ * If we saw an error during the loop, let the caller handle it. We treat
>+ * all other states as success.
>+ */
>+ if (state == ERR)
>+ return 0;
Did you mean state = utf8_advance(s, state
>I'm pretty confident this improvement is architecture-independent.
Thanks for testing it with different architectures.
It looks like the same utf8_advance function is good for both fast-path and
for the slow path.
Then pg_utf8_verifychar could be removed altogether along with the
corresponding I
Thank you,
It looks like it is important to have shrx for x86 which appears only when
-march=x86-64-v3 is used (see
https://github.com/golang/go/issues/47120#issuecomment-877629712 ).
Just in case: I know x86 wound not use fallback implementation, however,
the sole purpose of shift-based DFA is to
Have you considered shift-based DFA for a portable implementation
https://gist.github.com/pervognsen/218ea17743e1442e59bb60d29b1aa725 ?
Vladimir
>
Simon>It seems strange to me that we put this work onto the pooler, forcing
Simon>poolers to repeatedly issue the same command
What if poolers learn to manage connections and prepared statements better?
Then poolers won't have to reset the session every time, and everyone wins.
Simon>This has an
Andrew>It needs to contain a substantial implementation plan
Here's an implementation plan, again, quoted from the very same mail:
Vladimir>Of course both variations above fail to support streaming
Vladimir> (as in "need to process all the contents in order to get the last
character"), so it migh
Andrew>You and I clearly have a different idea from what constitutes a
concrete
Andrew>proposal. This is hardly the ghost of a proposal.
Can you please clarify what is a proposal from your point of view?
Is it documented?
I think I have read the relevant TODO items:
https://wiki.postgresql.org/wi
>100% compatible with the MySQL
It is hardly a justification for a feature or for a change request.
Vladimir
Andrew>To the best of my knowledge there is no concrete proposal for the
type
Andrew>of data type / interface you suggest.
To the best of my knowledge, one of the concrete proposals was in the very
first message.
Let me please copy it:
Vladimir> **Here goes the question**: do you think such an
Let me please bump the thread.
Just in case, I'm PgJDBC committer.
PgJDBC receives requests to "support CLOB" from time to time, however, I
believe it is impossible without the support from the database.
To my best knowledge, the database does not have APIs for "streaming large
text data".
The on
Kyotaro>It seems to me that that crash means Pgjdbc is initiating a logical
Kyotaro>replication connection to start physical replication.
Well, it used to work previously, so it might be a breaking change from the
client/application point of view.
Vladimir
Hi,
Pgjdbc test suite identified a SIGSEGV in the recent HEAD builds of
PostgreSQL, Ubuntu 14.04.5 LTS
Here's a call stack:
https://travis-ci.org/github/pgjdbc/pgjdbc/jobs/691794110#L7484
The crash is consistent, and it reproduces 100% of the cases so far.
The CI history shows that HEAD was good
Bruce, thanks for taking the time to summarize.
Bruce>Fourth, it is not clear how many applications would break if COMMIT
Bruce>started issuing an error rather than return success
None.
Bruce>applications that issue COMMIT and expect success after a transaction
Bruce>block has failed
An applica
Andy>1). The test cases may succeed locally but
Andy> may be failed
Andy> in CI for some reasons
Peter> This is not a problem
I would disagree. A patch might easily make the database incompatible with
clients like JDBC.
Do current PostgreSQL tests catch that?
I don't think so.
However, that can
But if the SQL is /*commit*/rollback, then the driver should not raise an
exception. The exception should be only for the case when the client asks
to commit and the database can't do that.
The resulting command tag alone is not enough.
Vladimir
Just one more data point: drivers do allow users to execute queries in a
free form.
Shat is the user might execute /*comment*/commit/*comment*/ as a free-form
SQL, and they would expect that the resulting
behaviour should be exactly the same as .commit() API call (==silent
rollback is converted to
Tom>I think we still end up concluding that altering this behavior has more
Tom>downside than upside.
What is the downside?
Applications, drivers, and poolers already expect that commit might produce
an error and terminate the transaction at the same time.
"The data is successfully committed to
Robert>Now, of course, it's also true that if what the server does makes
Robert>users sad, maybe the server should do something different
The server makes users sad as it reports the same end result (=="commit
failed") differently.
Sometimes the server produces ERROR, and sometimes the server prod
>do you think most common connection poolers would continue to
>work after making this change?
Of course, they should.
There are existing cases when commit responds with an error: deferrable
constraints.
There's nothing new except it is suggested to make the behavior of
commit/prepare failure (e.
Merlin>My biggest sense of alarm with the proposed change is that it could
Merlin>leave applications in a state where the transaction is hanging there
How come?
The spec says commit ends the transaction.
Can you please clarify where the proposed change leaves a hanging
transaction?
Just in case,
Shay> Asking drivers to do this at the client have the exact same breakage
impact as the server change, since the user-visible behavior changes in the
same way
+1
Dave>While we can certainly code around this in the client drivers I don't
believe they should be responsible for fixing the failings
Robert> This email thread is really short on clear demonstrations that X or Y
Robert> is useful.
It is useful when the whole database does **not** crash, isn't it?
Case A (==current PostgeSQL mode): syscache grows, then OOMkiller
chimes in, kills the database process, and it leads to the complete
Benjamin> A related and helpful patch would be to capture the access log and
Benjamin> provide anonymized traces.
The traces can be captured via DTrace scripts, so no patch is required here.
For instance:
https://www.postgresql.org/message-id/CAB%3DJe-F_BhGfBu1sO1H7u_XMtvak%3DBQtuJFyv8cfjGBRp7Q_y
>Curious what client is this that is violating the protocol.
I stand corrected: that is not a violation, however client might get
unexpected failure of query(begin) in a rare case of close(s1) or close(s2)
fail.
Vladimir
Tatsuo>responses of a simple query do not include CloseComplete
Tatsuo, where do you get the logs from?
I guess you are just confused by the PRINTED order of the messages in the
log.
Note: wire order do not have to be exactly the same as the order in the log
since messages are buffered, then might
Tom>Yes, we need either session open or reconnect it approach to find out
Tom>the whether server is read-write or read-only.
Just in case, pgjdbc has that feature for quite a while, and the behavior
there is to keep the connection until it fails or application decides to
close it.
pgjdbc uses thr
Mori>Was wondering if anyone has had luck getting these three set up for
any IDE or editor configuration?
Just a data point: CLion + CMake work just great.
Step by step (just checked in macOS):
1) "Check out from Version Control" -> Git ->
https://github.com/stalkerg/postgres_cmake.git -> clone
2
Merlin>The workaround is to simply not do that and you can get
Merlin>precise control of behavior
You are absolutely right.
On top of that, the whole concept of DB-drivers and libpq is useless.
Users should just simply exchange wire messages for precise control of
behavior.
Vladimir
David>JDBC driver or similar drivers to use the CALL command always from
PG11 on, then the meaning of {call f1(a, b)} will have changed and a
Note: technically speaking, JDBC has two flavours of syntax (however
standard does not clarify the distinction):
S1) {? := call my_proc(?,?) }
S2) { call my
Peter>AFAICT in no case does it involve allowing functions to be called as
procedures or vice versa.
Oracle DB uses the same way to execute both procedures and functions:
pl/sql block.
For instance:
procedure) begin my_proc(); end;
function) begin :result := my_fun(); end;
Call like begin my_fun
Shay>On the other hand, releasing *without* this also has its consequence,
namely setting in stone that the database-independent language API cannot
be used for invoking the new stored procedures
Shay>So I hope you at least consider going through the thought process
about allowing this.
+1
I wond
Peter>I am familiar with the Java {call} escape. But I think it's pretty
Peter>useless. You're not going to get any compatibility with anything from
Peter>it, since every SQL implementation does something different with it
Would you please be more specific?
{ call ... } converts the SQL to the
Shay>Npgsql currently always sends a describe as part of statement
execution (for server-prepared messages the describe is done only once, at
preparation-time). Vladimir, are you doing things differently here?
The same thing is for pgjdbc. It does use describe to identify result row
format.
Howeve
ecomes 2 bytes
always), however it would come at a cost of space usage.
**Here goes the question**: do you think such an implementation ("large
string stored in Large Objects" could be merged into the core eventually)?
Q2: any ideas/existing libraries for random access read-write large strings
stored as binary?
PS. Relevant pgjdbc PR is https://github.com/pgjdbc/pgjdbc/pull/1272
--
Regards,
Vladimir Sitnikov
>to compensate for the decisions made by the JDBC driver PostgreSQL
It is not a decision by PostgreSQL JDBC driver. It is a decision by JDBC
(Java Database Connectivity) specification itself.
pgjdbc just follows the specification there.
Well, there's an initiative for a new JDBC specification (AD
Andrew>does it fail on the latest 11-stable
1) Current "build from Git/master PostgreSQL" produces the data row for
both simple and extended queries.
2) Just a side note: `CALL my_proc()` is not suitable for functions. That
looks weird.
Is the client expected to lookup system catalogs in order to
David>I'm guessing that the Java port wouldn't be too
complicated. It's already well defined.
Is encoding defined somewhere for the "service file"?
I don't like the idea of using "a default" very much.
Vladimir
>I like the idea.
+1
>Also, there probably should be a GUC for this, defaulting to "off".
I think the feature could be on by default provided it can properly
identify "volatile" functions/tables hidden behind views.
Vladimir
> If any of the tables were modified, cache entries using the table must be
removed.
> (these are already implemented in Pgpool-II's in memory query cache)
How do you identify updates made from a pl/pgsql procedure?
Vladimir
>I have work loads that I can repeat, so I can help with testing.
That would be great.
Do you think you could use DTrace to capture the trace?
For instance, https://github.com/vlsi/pgsqlstat/blob/pgsqlio/pgsqlio
Vladimir
> Oh, looks like I'm inventing another kind of bicycle :-(
Do you think you could capture a trace or two from a more-or-less
representative application/database?
Discussion of algorithms makes little sense as we all lack traces to
compare/validate.
Vladimir
>I don't have time to check this out just now, but it seems like an
excellent idea
There are a couple of sad things:
1) DTrace probes seem to be disabled by default. At least, I had to build
PostgreSQL with --enable-dtrace in my macOS.
Does that (the requirement to enable dtrace within PostgreSQL)
>I mean Charles is free to choose the interface for the extension he
believes is right
I'm just trying to figure out what are the use cases for using that Thrift
extension.
For instance, it would be interesting if Thrift was an alternative way to
transfer data between client and the database. I g
>Personally I think raw data bytes are OK if functions for getting all
keys and values from this data are provided
What is the purpose of using Thrift "encoding" if it turns out to be a
simple wrapper for existing binary data?
Do you mean the goal is to have "get/set" functions to fetch data out
>If somebody produced a trace showing the buffer lookups in order
To get things moving, I've created a DTrace script that captures buffer
reads:
https://github.com/vlsi/pgsqlstat/blob/pgsqlio/pgsqlio
Is it something that can be used to capture live traces?
Sample output can be seen here:
https:/
>Development in built-in connection pooling will be continued in
https://github.com/postgrespro/postgresql.builtin_pool.git
The branch (as of 0020c44195992c6dce26baec354a5e54ff30b33f) passes pgjdbc
tests: https://travis-ci.org/vlsi/pgjdbc/builds/368997672
Current tests are mostly single-threaded,
Christopher>One of the things that they find likable is that by having the
connection
pool live
Christopher>in the framework alongside the application is that this makes
it easy to
attach
Christopher>hooks so that the pool can do intelligent things based on
application-aware
logic.
I'm afraid I do
Andres>I think the biggestoverhead here is that the executor startup
includes
Andres>too many indirect (linked lists) datastructured, that allocated each
Andres>round
The case is very common: batch inserts are popular in Java, and ORMs use
batch API automatically.
However, there's high per-backend
Damir>Wouldn’t that be protocol semantics? Framing is already taken care of
by the wire protocol.
Apparently I'm using the wrong word. I do mean protocol semantics.
Damir>But can you pull off grpc.. Would it be the only protocol supported?
Of course there will be lots of "older clients"
For inst
It could make sense to arrange a Google Hangouts conversation (or alike).
Hangouts allows to record sessions with up to 10 speakers and unlimited
listeners. The recording can be shared via YouTube.
Damir>Funny you agree with that—for someone having the experience of
writing a driver and having a l
Tom>But starting from the assumption that HTTP2 solves our problems seems
to me to be "Here's a hammer.
Agree.
Just a side note: if v4 is ever invented I wish client language support
is considered.
It does take resources to implement message framing, and data parsing (e.g.
int, timestamp, struct,
Damir>Postgres doesn’t know about grpc, s
I'm afraid you are missing the point.
I would say PostgreSQL doesn't know about HTTP/2.
It is the same as "PostgreSQL doesn't know about grpc".
Here's a quote from your pg_h2 repo:
>What we need is to really build a request object and correctly extract
>
Damir> * What are the criteria for getting this into the core?
Craig>Mine would be:
+1
There's a relevant list as well:
https://github.com/pgjdbc/pgjdbc/blob/master/backend_protocol_v4_wanted_features.md
Vladimir
Hi,
>If anyone finds the idea of Postgres speaking http2 appealing
HTTP/2 sounds interesting.
What do you think of https://grpc.io/ ?
Have you evaluated it?
It does sound like a ready RPC on top of HTTP/2 with support for lots of
languages.
The idea of reimplementing the protocol for multiple l
Tom>One concrete objection to the above is it'd obscure hierarchical
relationships in the context tree,
What is the problem with relationships? Context names are aligned as well
provided 8192 is justified to 6-7-8-9 (you pick) characters.
Tom>But given the lack of previous complaints
1) Here it
Tom>Well, as I said, you can do anything you want now in an extension.
That is true. However it basically means "everybody who cares to
troubleshoot the memory use of a production system should install an
extension".
Should
https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_us
It looks much better.
>While I didn't do anything about it here, I think it'd likely be a
>good idea for MemoryContextStats printout to truncate the context ID
>strings at 100 characters or so
It would be great if there was an option to show full sql.
For instance, current statistics is not sorte
Andres>The overhead required for it (in cycles, in higher memory usage due
to
additional bookeeping
Does that mean the memory contexts are unparseable? (there's not enough
information to enumerate contents)
What if memory dump is produced by walking the C structures?
For instance, I assume statam
Hi,
I investigate an out of memory-related case for PostgreSQL 9.6.5, and it
looks like MemoryContextStatsDetail + gdb are the only friends there.
MemoryContextStatsDetail does print some info, however it is rarely
possible to associate the used memory with business cases.
For insance:
CachedP
Hi,
I've got a problem with PostgreSQL 9.6.5: backend gets killed by OOM
killer, and it shuts the DB down.
Of course, the OOM case is to be investigated (MemoryContextStatsDetail,
etc), however I wonder if DB can be more robust.
The sad thing is a single backend crash results in the DB shutdown, s
Technically speaking, Thrift is "language-independent"
serialization-deserialization format with clean approach to backward
compatibility.
I think Thrift (or something like that) can be useful, as it can generate
serializers/deserializers for lots of languages.
PostgreSQL's "binary" format is tied
Konstantin>I do not have explanation of performance degradation in case of
this
particular workload.
A) Mongo Java Client uses a connection-pool of 100 connections by default.
That is it does not follow "connection per client" (in YCSB terms), but it
is capped by 100 connections. I think it can be
> config/pgjsonb-local.dat
Do you use standard "workload" configuration values?
(e.g. recordcount=1000, maxscanlength=100)
Could you share ycsb output (e.g. for workload a)?
I mean lines like
[TOTAL_GC_TIME], Time(ms), xxx
[TOTAL_GC_TIME_%], Time(%), xxx
>postgresql-9.4.1212.jar
Ok, you have re
Konstantin>I have not built YCSB myself, use existed installation.
Which pgjdbc version was in use?
Konstantin>One of the main problems of Postgres is significant degrade of
performance in case of concurrent write access by multiple transactions to
the same sows.
I would consider that a workload
Konstantin>I have obtained more results with YCSB benchmark and built-in
connection pooling
Could you provide more information on the benchmark setup you have used?
For instance: benchmark library versions, PostgreSQL client version,
additional/default benchmark parameters.
Konstantin>Postgres sh
Bruce>Well, we could have the connection pooler disconnect those, right?
I agree. Do you think we could rely on all the applications being
configured in a sane way?
A fallback configuration at DB level could still be useful to ensure the DB
keeps running in case multiple applications access it. It
Bruce>Yes, it would impact applications and you are right most applications
could not handle that cleanly.
I would disagree here.
We are discussing applications that produce "lots of idle" connections,
aren't we? That typically comes from an application-level connection pool.
Most of the connectio
>the slave replication server does not seem to be recognized correctly
Could you elaborate on what do you mean by "does not seem to be recognized
correctly"?
You might be hitting https://github.com/pgjdbc/pgjdbc/pull/844 , so would
you please check if pgjdbc 42.2.0-SNAPSHOT (e.g.
https://oss.sona
> committed
I'm afraid it causes regressions for pgjdbc.
Here's CI log: https://travis-ci.org/pgjdbc/pgjdbc/jobs/327327402
The errors are:
testMetaData[typeName = REF_CURSOR, cursorType =
2,012](org.postgresql.test.jdbc2.RefCursorTest) Time elapsed: 0.032 sec
<<< ERROR! org.postgresql.util.PSQL
86 matches
Mail list logo
