Re: [HACKERS] gettimeofday is at the end of its usefulness?
* Tom Lane: > Florian Weimer <f...@deneb.enyo.de> writes: >> * Tom Lane: >>> On Linux (RHEL6, 2.4GHz x86_64), I find that gettimeofday(), >>> clock_gettime(CLOCK_MONOTONIC), and clock_gettime(CLOCK_REALTIME) >>> all take about 40ns. Of course gettimeofday() only has 1us resolution, >>> but the other two have perhaps 10ns resolution (I get no duplicate >>> readings in a tight loop). > >> Isn't this very specific to kernel and glibc versions, depending on >> things like CONFIG_HZ settings and what level of vDSO support has been >> backported? > > No doubt, but I have yet to find a platform where clock_gettime() exists > but performs worse than gettimeofday(). Do you know of one? ppc64le with all the vDSO fixes for clock_gettime? glibc has some test cases which fail because clock_gettime gives inconsistent results. This has been fixed in current kernels, but I don't know if everyone uses them. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettimeofday is at the end of its usefulness?
* Tom Lane: > On Linux (RHEL6, 2.4GHz x86_64), I find that gettimeofday(), > clock_gettime(CLOCK_MONOTONIC), and clock_gettime(CLOCK_REALTIME) > all take about 40ns. Of course gettimeofday() only has 1us resolution, > but the other two have perhaps 10ns resolution (I get no duplicate > readings in a tight loop). Other documented clockids include > CLOCK_REALTIME_COARSE: about 10ns to read, but only 1ms resolution > CLOCK_MONOTONIC_COARSE: about 12ns to read, but only 1ms resolution > CLOCK_MONOTONIC_RAW: full resolution but very slow, ~145ns to read > So CLOCK_MONOTONIC seems to be the thing to use here. It won't buy > us anything speed-wise but the extra resolution will be nice. > However, we need to do more research to see if this holds true on > other popular distros. Isn't this very specific to kernel and glibc versions, depending on things like CONFIG_HZ settings and what level of vDSO support has been backported? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] B-tree index row size limit
The index row size limit reared its ugly head again. My current use of PostgreSQL is to load structured data into it but from sources I don't have control over, to support a wide range of queries whose precise nature is not yet known to me. (Is this called a data warehouse?) Anyway, what happens from time to time is that some data which has been processed successfully in the past suddenly failed to load because there happens to be a very long string in it. I know how to work around this, but it's still annoying when it happens, and the workarounds may make it much, much harder to write efficient queries. What it would it take to eliminate the B-tree index row size limit (or rather, increase it to several hundred megabytes)? I don't care about performance for index-based lookups for overlong columns, I just want to be able to load arbitrary data and index it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL renegotiation
On 02/23/2015 04:01 PM, Albe Laurenz wrote: I think you could remove renegotiation from PostgreSQL as long as you offer something better than RC4 in the TLS handshake. I'd say it is best to wait if and how OpenSSL change their API when they implement TLS 1.3. I'd vote against removing renegotiation. I'm just suggesting that the effort required to fix bugs in this part of PostgreSQL could be spent better elsewhere. If changing the encryption is so useless, whe did the TLS workgroup decide to introduce rekeying as a substitute for renegotiation? Theoretical considerations, mostly. If rekeying is strictly required after processing just a few petabytes, the cipher is severely broken and should no longer be used. -- Florian Weimer / Red Hat Product Security -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
* David G. Johnston: enables or disables data durability promise of ACID. ? “fsync = on” only works if the storage stack doesn't do funny things. Depending on the system, it might not be sufficient. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL renegotiation
On 02/22/2015 02:05 PM, Andres Freund wrote: On 2015-02-22 01:27:54 +0100, Emil Lenngren wrote: I honestly wonder why postgres uses renegotiation at all. The motivation that cryptoanalysis is easier as more data is sent seems quite far-fetched. I don't think so. There's a fair number of algorithms that can/could be much easier be attached with lots of data available. Especially if you can guess/know/control some of the data. Additionally renegotiating regularly helps to constrain a possible key leagage to a certain amount of time. With backend connections often being alive for weeks at a time that's not a bad thing. Renegotiation will be removed from future TLS versions because it is considered unnecessary with modern ciphers: https://github.com/tlswg/tls13-spec/issues/38 If ciphers require rekeying, that mechanism will be provided at the TLS layer in the future. I think you could remove renegotiation from PostgreSQL as long as you offer something better than RC4 in the TLS handshake. -- Florian Weimer / Red Hat Product Security -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes
* Ants Aasma: CRC has exactly one hardware implementation in general purpose CPU's I'm pretty sure that's not true. Many general purpose CPUs have CRC circuity, and there must be some which also expose them as instructions. and Intel has a patent on the techniques they used to implement it. The fact that AMD hasn't yet implemented this instruction shows that this patent is non-trivial to work around. I think you're jumping to conclusions. Intel and AMD have various cross-licensing deals. AMD faces other constraints which can make implementing the instruction difficult. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/05/2014 01:56 PM, Craig Ringer wrote: JDBC also has a statement batching interface. Right now PgJDBC just unwraps the batch and runs each query individually. Any async-support improvements server-side should probably consider the need of executing a batch. The batch might be one PreparedStatement with many different parameters, or it might be a series of unrelated statements. A way for PgJDBC to run the batch without syncing with the server after each query would be really helpful. The server already supports this because it supports pipelining. Anyway, I have now switched my application to COPY FROM STDIN where possible, and the increase in throughput is phenomenal—even though it is running completely locally. I could squeeze out more round-trips if I had an asynchronous query facility in libpq. Feedback in this thread was, we want something like this in libpq, but not the thing you proposed. But there have been no concrete counter-proposals, and some of the responses did not take into account the inherent complexities of round-trip avoidance. So I'm not sure how to move this topic forward. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/03/2014 06:06 PM, Claudio Freire wrote:
Per-query expectations could be such a thing. And it can even work with PQexec:
PQexec(con, SELECT nextval('a_id_seq') FROM generate_series(1,10););
--read--
PQexec(con, SELECT nextval('b_id_seq') FROM generate_series(1,10););
--read--
PQexec(con, INSERT INTO a (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO b (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO a (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO b (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO a (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO b (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
... 9 times...
PQexec(con, INSERT INTO a (...);, PQEXPECT_NO_RESULT | PQASYNC_CORK);
PQexec(con, INSERT INTO b (...);, PQEXPECT_NO_RESULT | PQASYNC);
do {
// do something useful
} while (PQflush());
Here, the PQASYNC flag would temporarily switch to non-blocking I/O,
and buffer what cannot be sent. PQASNC_CORK, would only buffer (only
send if the buffer is full). After any ASYNC call, PQflush would be
necessary (to flush the send queue and to consume the expected
responses), but I can imagine any synchronous call (PQexec,
PQsendQuery or whatever) could detect a non-empty buffer and just
blockingly flush right there.
How would you consume results once they arrive? I think this only
covers the no-result case, and it has since come to my attention that
the Node.js folks are looking for general async response processing.
(In order to implement your proposal, we'd have to add quite a few
additional APIs to include the flag argument, but that's a minor detail.)
--
Florian Weimer / Red Hat Product Security Team
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 04/22/2014 07:03 PM, Claudio Freire wrote: On Tue, Apr 22, 2014 at 8:19 AM, Florian Weimer fwei...@redhat.com wrote: Feedback in this thread was, we want something like this in libpq, but not the thing you proposed. But there have been no concrete counter-proposals, and some of the responses did not take into account the inherent complexities of round-trip avoidance. So I'm not sure how to move this topic forward. What exactly do you mean by not taking into account? A couple of them were along the lines let's just send batches of queries and sync between batches. This does not seem very helpful to me because sizing the batches is difficult, and the sizes could be quite large. I do not believe you responded to my proposed interface. I'm not requesting a change in roundtrip avoidance measures, just the interface. I've sent a reply now, I think I missed this part. Sorry. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] atexit_callback can be a net negative
On 03/07/2014 06:03 AM, Tom Lane wrote: In the bug thread I proposed making atexit_callback check whether getpid() still matches MyProcPid. If it doesn't, then presumably we inherited the atexit callback list, along with the value of MyProcPid, from some parent backend process whose elbow we should not joggle. Can anyone see a flaw in that? There's the PID reuse problem. Forking twice (with a delay) could end up with the same PID as MyProcPid. Comparing the process start time would protect against that. Checking getppid() would have the same theoretical problem. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] atexit_callback can be a net negative
On 03/07/2014 03:57 PM, Tom Lane wrote: I think Florian's right that there's a risk there, but it seems pretty remote, and I don't see any reliable way to detect the case anyhow. (Process start time? Where would you get that from portably?) I don't think there's a portable source for that. On Linux, you'd have to use /proc. It's not a reason not to do something about the much larger chance of this happening in a direct child process, which certainly won't have a matching PID. Indeed. Checking getppid() in addition might narrow things down further. On Linux, linking against pthread_atfork currently requires linking against pthread (although this is about to change), and it might incur the pthread-induced overhead on some configurations. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] atexit_callback can be a net negative
On 03/07/2014 04:10 PM, Tom Lane wrote: Florian Weimer fwei...@redhat.com writes: On 03/07/2014 03:57 PM, Tom Lane wrote: It's not a reason not to do something about the much larger chance of this happening in a direct child process, which certainly won't have a matching PID. Indeed. Checking getppid() in addition might narrow things down further. I don't think getppid adds much to the party. In particular, what to do if it returns 1? You can't tell if you're an orphaned backend (in which case you should still do normal shutdown) Oh. I didn't know that orphaned backends perform a normal shutdown. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/05/2014 03:11 PM, Greg Stark wrote: On Fri, Jan 3, 2014 at 3:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think Florian has a good point there, and the reason is this: what you are talking about will be of exactly zero use to applications that want to see the results of one query before launching the next. There are techniques for handling that actually. For a better explanation than I can do see http://kentonv.github.io/capnproto/rpc.html. It's mostly a language feature but it does require support from the protocol to be able to reference data in earlier responses in your subsequent requests. Purely from a perspective of latency avoidance, sub-queries, WITH or stored procedures can achieve the same thing, and work even if the intermediate result has to undergo some transformation. :-) -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/04/2014 04:39 PM, Martijn van Oosterhout wrote: Why switch between COPY commands, why could you not do it in one? For example: COPY table1(col1, col2, ...), table2(col1, col2, ...) FROM STDIN WITH (tableoids); tableoid1tabcol1tabcol2... tableoid2tab... ... \. My originally idea was to avoid any server-side changes, so that applications do not need fallback code for talking to old servers. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/02/2014 07:52 PM, Claudio Freire wrote: No, because this doesn't scale automatically with the bandwidth-delay product. It also requires that the client buffers queries and their parameters even though the network has to do that anyway. Why not? I'm talking about transport-level packets, btw, not libpq frames/whatever. Yes, the network stack will sometimes do that. But the it doesn't have to do it. It does it sometimes, which is not the same. The network inevitably buffers because the speed of light is not infinite. Here's a concrete example. Suppose the server is 100ms away, and you want to send data at a constant rate of 10 Mbps. The server needs to acknowledge the data you sent, but this acknowledgment arrives after 200 ms. As a result, you've sent 2 Mbits before the acknowledgment arrives, so the network appears to have buffered 250 KB. This effect can actually be used for data storage, called delay line memory, but it is somewhat out of fashion now. And buffering algorithms are quite platform-dependent anyway, so it's not the best idea to make libpq highly reliant on them. That is why I think libpq needs to keep sending until the first response from the server arrives. Batching a fixed number of INSERTs together in a single conceptual query does not achieve auto-tuning to the buffering characteristics of the path. So, trusting the network start to do the quick start won't work. For steady streams of queries, it will work. But not for short bursts, which will be the most heavily used case I believe (most apps create short bursts of inserts and not continuous streams at full bandwidth). Loading data into the database isn't such an uncommon task. Not everything is OLTP. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 01/03/2014 04:20 PM, Tom Lane wrote: I think Florian has a good point there, and the reason is this: what you are talking about will be of exactly zero use to applications that want to see the results of one query before launching the next. Which eliminates a whole lot of apps. I suspect that almost the *only* common use case in which a stream of queries can be launched without feedback is going to be bulk data loading. It's not clear at all that pipelining the PQexec code path is the way to better performance for that --- why not use COPY, instead? The data I encounter has to be distributed across multiple tables. Switching between the COPY TO commands would again need client-side buffering and heuristics for sizing these buffers. Lengths of runs vary a lot in my case. I also want to use binary mode as a far as possible to avoid the integer conversion overhead, but some columns use custom enum types and are better transferred in text mode. Some INSERTs happen via stored procedures, to implement de-duplication. These issues could be addressed by using temporary staging tables. However, when I did that in the past, this caused pg_shdepend bloat. Carefully reusing them when possible might avoid that. Again, due to the variance in lengths of runs, the staging tables are not always beneficial. I understand that pipelining introduces complexity. But solving the issues described above is no picnic, either. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: Async query processing
On 11/04/2013 02:51 AM, Claudio Freire wrote:
On Sun, Nov 3, 2013 at 3:58 PM, Florian Weimer fwei...@redhat.com wrote:
I would like to add truly asynchronous query processing to libpq, enabling
command pipelining. The idea is to to allow applications to auto-tune to
the bandwidth-delay product and reduce the number of context switches when
running against a local server.
...
If the application is not interested in intermediate query results, it would
use something like this:
...
If there is no need to exit from the loop early (say, because errors are
expected to be extremely rare), the PQgetResultNoWait call can be left out.
It doesn't seem wise to me making such a distinction. It sounds like
you're oversimplifying, and that's why you need modes, to overcome
the evidently restrictive limits of the simplified interface, and that
it would only be a matter of (a short) time when some other limitation
requires some other mode.
I need modes because I want to avoid unbound buffering, which means that
result data has to be consumed in the order queries are issued.
PGAsyncMode oldMode = PQsetsendAsyncMode(conn, PQASYNC_RESULT);
bool more_data;
do {
more_data = ...;
if (more_data) {
int ret = PQsendQueryParams(conn,
INSERT ... RETURNING ..., ...);
if (ret == 0) {
// handle low-level error
}
}
// Consume all pending results.
while (1) {
PGresult *res;
if (more_data) {
res = PQgetResultNoWait(conn);
} else {
res = PQgetResult(conn);
}
Somehow, that code looks backwards. I mean, really backwards. Wouldn't
that be !more_data?
No, if more data is available to transfer to the server, the no-wait
variant has to be used to avoid a needless synchronization with the server.
In any case, pipelining like that, without a clear distinction, in the
wire protocol, of which results pertain to which query, could be a
recipe for trouble when subtle bugs, either in lib usage or
implementation, mistakenly treat one query's result as another's.
We already use pipelining in libpq (see pqFlush, PQsendQueryGuts and
pqParseInput3), the server is supposed to support it, and there is a
lack of a clear tit-for-tat response mechanism anyway because of
NOTIFY/LISTEN and the way certain errors are reported.
Instead of buffering the results, we could buffer the encoded command
messages in PQASYNC_RESULT mode. This means that PQsendQueryParams would
not block when it cannot send the (complete) command message, but store in
the connection object so that the subsequent PQgetResultNoWait and
PQgetResult would send it. This might work better with single-tuple result
mode. We cannot avoid buffering either multiple queries or multiple
responses if we want to utilize the link bandwidth, or we'd risk deadlocks.
This is a non-solution. Such an implementation, at least as described,
would not remove neither network latency nor context switches, it
would be a purely API change with no externally visible behavior
change.
Ugh, why?
An effective solution must include multi-command packets. Without
knowing the wire protocol in detail, something like:
PARSE: INSERT blah
BIND: args
EXECUTE with DISCARD
PARSE: INSERT blah
BIND: args
EXECUTE with DISCARD
PARSE: SELECT blah
BIND: args
EXECUTE with FETCH ALL
All in one packet, would be efficient and error-free (IMO).
No, because this doesn't scale automatically with the bandwidth-delay
product. It also requires that the client buffers queries and their
parameters even though the network has to do that anyway.
In any case, I don't want to change the wire protocol, I just want to
enable libpq clients to use more of its capabilities.
--
Florian Weimer / Red Hat Product Security Team
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can we trust fsync?
On 11/21/2013 12:45 AM, Craig Ringer wrote: I'm really concerned by this post on Linux's fsync and disk flush behaviour: http://milek.blogspot.com.au/2010/12/linux-osync-and-write-barriers.html and seeking opinions from folks here who've been deeply involved in write reliability work. With ext4 and XFS on plain/LVM/md block devices, this issue should really be a thing of the past. I think the kernel folks would treat this as bugs nowadays, too. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Logging WAL when updating hintbit
On 11/14/2013 07:02 AM, Sawada Masahiko wrote: I attached patch adds new wal_level 'all'. Shouldn't this be a separate setting? It's useful for storage which requires rewriting a partially written sector before it can be read again. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] RFC: Async query processing
PQsendQueryParams multiple times without a PQgetResult call in-between. In PQASYNC_DISCARD mode, we could implicitly synchronize with the server when a synchronous API is used, reporting a pending error against the synchronous command. (I think this is what the delayed error reporting in X11 does.) Typically, that would be the COMMIT at the end of the transaction. With a result-buffering implementation of PQASYNC_RESULT, we could do that as well, but this might be too much magic. I thought I'd ask for comments before starting coding because this looks a bit more complicated than I expected. :) -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why the asprintf patch is still breaking the buildfarm
On 10/23/2013 03:05 AM, Noah Misch wrote: I would vote for choosing the standard we want vsnprintf() to follow (probably C99) and substituting a conforming implementation wherever configure detects that libc does not conform. We'll be shipping some replacement vsnprintf() in any case; we may as well use it to insulate the rest of our code from less-preferred variants. Do you care about the snprintf behavior on very large buffers (larger than INT_MAX)? Then there's further complication, and it's an area where glibc behavior is likely to change in the future (because it is claimed that C99 and POSIX conflict, and glibc implements neither behavior). -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reasons not to like asprintf
On 10/22/2013 11:06 PM, Tom Lane wrote: Attached is a draft, which compiles though I've not yet actually tested it. nprinted = vsnprintf(buf, len, fmt, args); Assert(buf[len - 1] == '\0'); The assert may fire if len INT_MAX and the system returns with errno == EOVERFLOW, as required by POSIX. It's probably better to move it after the error logging. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] get rid of SQL_ASCII?
On 09/06/2013 09:14 AM, Craig Ringer wrote: On 09/05/2013 08:47 PM, Peter Eisentraut wrote: Other ideas? Are there legitimate uses for SQL_ASCII? IMO people who want SQL_ASCII should actually be storing everything in `bytea`; that's a truer reflection of what they're actually storing, retrieving, and working with and how they're doing it. Practically speaking, the escaping gets in the way, and there isn't full feature parity with TEXT. Regular expression matching seems to be missing, for instance. But apart from that, yes, BYTEA would be the more appropriate choice. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enabling Checksums
* Greg Smith: The TCP/IP checksum spec is at https://tools.ietf.org/html/rfc793 ; its error detection limitations are described at http://www.noahdavids.org/self_published/CRC_and_checksum.html ; and a good article about optimizing its code is at http://www.locklessinc.com/articles/tcp_checksum/ I'll take a longer look at whether it's an improvement on the Fletcher-16 used by the current patch. The TCP checksum is too weak to be practical. Every now an then, I see data transfers where the checksum is valid, but the content contains bit flips. Anything that flips bits randomly at intervals which are multiples of 16 bits is quite likely to pass through checksum detection. In practice, TCP relies on checksumming on the sub-IP layers. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Floating point error
On 03/05/2013 07:23 PM, Tom Lane wrote: Maciek Sakrejda m.sakre...@gmail.com writes: Thank you: I think this is what I was missing, and what wasn't clear from the proposed doc patch. But then how can pg_dump assume that it's always safe to set extra_float_digits = 3? It's been proven (don't have a link handy, but the paper is at least a dozen years old) that 3 extra digits are sufficient to accurately reconstruct any IEEE single or double float value, given properly written conversion functions in libc. So that's where that number comes from. Now, if either end is not using IEEE floats, you may or may not get equivalent results --- but it's pretty hard to make any guarantees at all in such a case. There's also gdtoa, which returns the shortest decimal representation which rounds to the same decimal number. It would print 0.1 as 0.1, but 0.1 + 0.2 as 0.30004. -- Florian Weimer / Red Hat Product Security Team -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] posix_fadvise missing in the walsender
* Jeff Janes: Does the kernel really read a data block from disk into memory in order to immediately overwrite it? I would have thought it would optimize that away, at least if the writes are sized and aligned to 512 or 1024 bytes blocks (which WAL should be). With Linux, you'd have to use O_DIRECT to get that effect (but don't do that), otherwise writes happen in page size granularity, writing in 512 or 1024 byte blocks should really trigger a read-modify-write cycle. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous commit not... synchronous?
* Daniel Farina: The idea of canceling a COMMIT statement causing a COMMIT seems pretty strange to me. Canceling commits is inherently racy, so I'm not sure if this behavior so strange after all. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Covering Indexes
* Jeff Janes: I don't see the virtue of this in this case. Since the index is not unique, why not just put the index on (a,b,c,d) and be done with it? AFAICT, SQLite 4 encodes keys in a way that is not easily reversed (although the encoding is injective, so it's reversible in principle). Therefore, covered columns need to be listed separately, so they are not subject to the key encoding. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Documentation: remove confusing paragraph about backslash escaping
* Tom Lane: Hannes Frederic Sowa hs...@bfk.de writes: As with recent changes to `standard_conforming_strings' the paragraph about backslash escaping in the description of `LIKE' is only confusing. Thus I attached a patch to remove it. The para is still relevant if you don't have standard_conforming_strings on. And if you aren't using parametrized queries. The old text is a bit misleading even with standard_conforming_strings set to off. It is technically correct because it refers to the SQL statement parser which is run on parameters, but this seems a pretty fine distinction. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
* Alex Shulgin: Yeah, this is really appealing, however how do you tell if the part after the last slash is a socket directory name or a dbname? E.g: psql postgres:///path/to/different/socket/dir(default dbname) psql postgres:///path/to/different/socket/dir/other (dbname=other ?) The HTTP precent is to probe the file system until you find something. Most HTTP servers have something similar to the PATH_INFO variable which captures trailing path segments. It's ugly, but it's standard practice, and seems better than a separate -d parameter (which sort of defeats the purpose of URIs). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: URI connection string support for libpq
* Alex Shulgin: It's ugly, but it's standard practice, and seems better than a separate -d parameter (which sort of defeats the purpose of URIs). Hm, do you see anything what's wrong with ?dbname=other if you don't like a separate -d? It's not nice URI syntax, but it's better than an out-of-band mechanism. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] initdb and fsync
* Tom Lane: I wonder whether it wouldn't be sufficient to call sync(2) at the end, anyway, rather than cluttering the entire initdb codebase with fsync calls. We tried to do this in the Debian package mananger. It works as expected on Linux systems, but it can cause a lot of data to hit the disk, and there are kernel versions where sync(2) never completes if the system is rather busy. initdb is much faster with 9.1 than with 8.4. It's so fast that you can use it in test suites, instead of reusing an existing cluster. I think this is a rather desirable property. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page Checksums
I would chip in and say that I would prefer sticking to well-known proved filesystems like xfs/ext4 and let the application do the checksumming. Yes, that's a different way of putting my concern. If you want a proven file system with checksumming (and an fsck), options are really quite limited. And yes, I would for sure turn such functionality on if it were present. Same here. I already use page-level checksum with Berkeley DB. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Add minor version to v3 protocol to allow changes without breaking backwards compatibility
* Ants Aasma: I had a run in with this. JDBC driver versions 9.0 with the default configuration resulted in silent data corruption. The fix was easy, but not having an useful error was what really bothered me. Same for the DBD::Pg driver. In this particular case, I knew that the change was coming and could push updated Java and Perl client libraries well before the server-side change hit our internal repository, but I really don't want to have to pay attention to such details. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Add minor version to v3 protocol to allow changes without breaking backwards compatibility
* Robert Haas: In this particular case, I knew that the change was coming and could push updated Java and Perl client libraries well before the server-side change hit our internal repository, but I really don't want to have to pay attention to such details. But if we *don't* turn this on by default, then chances are very good that it will get much less use. That doesn't seem good either. If it's important enough to do it at all, then IMHO it's important enough for it to be turned on by default. We have never made any guarantee that the binary format won't change from release to release. The problem here are libpq-style drivers which expose the binary format to the application. The Java driver doesn't do that, but the Perl driver does. (However, both transparently decode BYTEA values received in text format, which led to the compatibility issue.) If you've version negotiation and you don't expose the binary format, then all clients can use the most efficient format automatically. If I understand things correctly, this is where the JDBC driver is heading (that is, automatic use of binary format). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page Checksums
* Robert Treat: Would it be unfair to assert that people who want checksums but aren't willing to pay the cost of running a filesystem that provides checksums aren't going to be willing to make the cost/benefit trade off that will be asked for? Yes, it is unfair of course, but it's interesting how small the camp of those using checksummed filesystems is. Don't checksumming file systems currently come bundled with other features you might not want (such as certain vendors)? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] checkpoint writeback via sync_file_range
* Greg Smith: One idea I was thinking about here was building a little hash table inside of the fsync absorb code, tracking how many absorb operations have happened for whatever the most popular relation files are. The idea is that we might say use sync_file_range every time N calls for a relation have come in, just to keep from ever accumulating too many writes to any one file before trying to nudge some of it out of there. The bat that keeps hitting me in the head here is that right now, a single fsync might have a full 1GB of writes to flush out, perhaps because it extended a table and then write more than that to it. And in everything but a SSD or giant SAN cache situation, 1GB of I/O is just too much to fsync at a time without the OS choking a little on it. Isn't this pretty much like tuning vm.dirty_bytes? We generally set it to pretty low values, and seems to help to smoothen the checkpoints. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] optimizing repeated MVCC snapshots
* Robert Haas: and (c) architectures (like 32-bit x86) where ordinary 64-bit operations aren't atomic but special instructions (cmpxchg8b) can be used to get that behavior. FILD and FIST are atomic, too, and are supported by more micro-architectures. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
* Florian Pflug: On Nov24, 2011, at 10:54 , Florian Weimer wrote: Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. That's unlikely to happen. Yes, with the type input/output functions tied to NUL-terminated strings, that seems indeed unlikely to happen. A more realistic approach would be to solve this only for UTF-8 encoded strings by encoding the NUL character not as a single 0 byte, but as sequence of non-0 bytes. 0xFF cannot occur in valid UTF-8, so that's one possibility. Java, for example, seems to use it to serialize Strings (which may contain NUL characters) to UTF-8. Only internally in the VM. UTF-8 produced by the I/O encoder/decoders produces and consumes NUL bytes. Should you try to add a new encoding which supports that, you might also want to allow CESU-8-style encoding of UTF-16 surrogate pairs. This means that code points representable by UTF-16 surrogate pairs may be encoded by separately encoding the two surrogate characters in UTF-8. I'm not sure if this is a good idea. The motivation behind CESU-8 is that it sorts byte-encoded strings in the same order as UTF-16, which is a completely separate concern. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Page Checksums + Double Writes
* David Fetter: The issue is that double writes needs a checksum to work by itself, and page checksums more broadly work better when there are double writes, obviating the need to have full_page_writes on. How desirable is it to disable full_page_writes? Doesn't it cut down recovery time significantly because it avoids read-modify-write cycles with a cold cache? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Large number of open(2) calls with bulk INSERT into empty table
* Robert Haas: I tried whacking out the call to GetPageWithFreeSpace() in RelationGetBufferForTuple(), and also with the unpatched code, but the run-to-run randomness was way more than any difference the change made. Is there a better test case? I think that if you want to exercise file system lookup performance, you need a larger directory, which presumably means a large number of tables. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Large number of open(2) calls with bulk INSERT into empty table
I noticed that a bulk INSERT into an empty table (which has been TRUNCATEd in the same transaction, for good measure) results in a curious number of open(2) calls for the FSM resource fork: open(base/657862/16554373_fsm, O_RDWR) = -1 ENOENT (No such file or directory) lseek(17, 0, SEEK_END) = 407584768 write(17, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 lseek(18, 0, SEEK_END) = 333119488 write(18, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 lseek(18, 0, SEEK_END) = 333127680 write(18, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 open(base/657862/16554373_fsm, O_RDWR) = -1 ENOENT (No such file or directory) lseek(17, 0, SEEK_END) = 407592960 write(17, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 lseek(18, 0, SEEK_END) = 333135872 write(18, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 open(base/657862/16554373_fsm, O_RDWR) = -1 ENOENT (No such file or directory) lseek(17, 0, SEEK_END) = 407601152 write(17, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 lseek(18, 0, SEEK_END) = 333144064 write(18, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 lseek(18, 0, SEEK_END) = 333152256 write(18, \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0..., 8192) = 8192 open(base/657862/16554373_fsm, O_RDWR) = -1 ENOENT (No such file or directory) I'm not sure if this result in a significant performance hit on Linux (because the dentry cache covers negative lookups, too), but I suspect that it could be an issue with other systems. This happens with PostgreSQL 9.1.0. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Wire protocol: type-specific opt-in to binary format
I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. This doesn't have to be a flag on the wire protocol, it could be a regular run-time parameter. Do you think this would make sense? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Making TEXT NUL-transparent
Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). I haven't even looked how difficult it would be to implement this. Do you think it's worth the trouble? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
* Pavel Stehule: Hello 2011/11/24 Florian Weimer fwei...@bfk.de: Occasionally, we get bitten by embedded NUL bytes in TEXT values. We take care of generating proper UTF-8, but this additional restriction sometimes slips by. It would be really helpful if PostgreSQL could store such TEXT fields as-is (at least if they are computed internally, or come from query parameters). and why you don't use bytea ? Text should be correct literal. It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown LINE 1: SELECT '\x006500'::bytea ~ 'A'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
* Alexander Shulgin: It's actually UTF-8 text, and some PostgreSQL functions are only available for TEXT, but not BYTEA, e.g.: bfk_int= SELECT '\x006500'::bytea ~ 'A'; ERROR: operator does not exist: bytea ~ unknown And how will those TEXT functions behave on a value with an embedded NUL? They need to be audited and fixed if necessary. I'm not saying that this would be a trivial change. Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making TEXT NUL-transparent
* Pavel Stehule: By the way, I refuse the notion that UTF-8 strings with embedded NULs are broken. I can't recall any other system which enforces UTF-8 well-formedness, but does not permit embedded NULs. I have a different question. What is reason for embedded NULs inside strings? The source system does not enforce that constraint, so from time to time, such data slips through. I don't know why it's there in the first place, and I have no control over the original data source. Usually, it's okay to silently strip NUL bytes; it's just a bit awkward. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Wire protocol: type-specific opt-in to binary format
* Florian Pflug: On Nov24, 2011, at 10:03 , Florian Weimer wrote: I would like to add functionality which allows a client to tell the server which types can be sent in binary format. The immediate goal is to suppress hex quoting for BYTEA values, but it seems to make sense to make this functionality more general. If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. Interesting. Doesn't this introduce another roundtrip? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
* Alvaro Herrera: I think we should just propose something that will not work in JDBC. I'm not sure if this is a good idea. 8-) I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Wire protocol: type-specific opt-in to binary format
* Florian Pflug: If you use the extended query protocol, the client can already choose text vs. binary representation on a per-column basis. You can query the result's column types by issuing a Describe message after the Parse message. For each column you can then decide whether you want textual or binary representation, and include that choice in the subsequent Bind message. Interesting. Doesn't this introduce another roundtrip? Yes. Maybe that's also something that should go on the next protocol version todo list. Okay. In any case, this is something that solely concerns the client library (libpq, JDBC, ...), not users of that library, and should thus be handled on the protocol level, not via a GUC. How is this different from the bytea_output GUC? libpq doesn't hide that at all, but the JDBC driver does---similar to the text/binary distinction. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
* Alvaro Herrera: Excerpts from Florian Weimer's message of jue nov 24 11:31:29 -0300 2011: * Alvaro Herrera: I think we should just propose something that will not work in JDBC. I'm not sure if this is a good idea. 8-) I plan to add UNIX Domain socket support to the JDBC driver. Eventually, the JDK will expose UNIX Domain sockets to Java code, too (they are already used internally for management functions). Well, in that case, the JDBC could simply adopt whatever syntax that libpq ends up adopting. I just meant something that will not work in JDBC *right now* (i.e. with no local socket support). Ah, okay, your proposal looked like something which couldn't work with JDBC *at all* because of invalid URI syntax (but admittedly, I haven't checked that yet). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Notes on implementing URI syntax for libpq
* Alexander Shulgin: This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb How would you specifiy a local port/UNIX domain socket? Would it be possible to add something like psql -d postgresql+ssh://fweimer@db5/var/run/postgresql/.s.PGSQL.5432 similar to what Subversion supports? (This might have security implications when used from untrusted PHP scripts.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strict aliasing
* Andres Freund: I don't gcc will ever be able to call all possible misusages. E.g. The List api is a case where its basically impossible to catch everything (as gcc won't be able to figure out what the ListCell.data.ptr_value pointed to originally in the general case). Correct, if code is not strict-aliasing-safe and you compile with -f-strict-aliasing, GCC may silently produce wrong code. (Same with -fwrapv, by the way.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to define global variable in postgresql
Hello. How can we define a global variable in postgresql? Do you mean session-private, but persistent across transactions? Configuration parameters can be abused for this purpose. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] synchronized snapshots
* Simon Riggs: I don't see the need to change the BEGIN command, which is SQL Standard. We don't normally do that. Some language bindings treat BEGIN specially, so it might be difficult to use this feature. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libedit memory stomp is apparently fixed in OS X Lion
* David Fetter: What is it about a GPLed psql client that is a non-starter for the installer? I'm not a fan of the GPL, but in this case, the effects of linking it in are quite limited in scope, i.e. they pertain to exactly one binary. And the usual showstopper, OpenSSL, might well fall under the system library exception on MacOS X. Curiously, the GPL favors proprietary operating systems in this area. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing the overhead of frequent table locks, v4
* Jeff Davis: Does this happen to be based on some academic research? I don't necessarily expect it to be; just thought I'd ask. Paul E. McKenney's thesis contains a few references. It's called asymmetrical reader-writer locking there, and Ingo Molnar implemented this as brlock in Linux 2.4. The earliest citation seems to be W.C. Hsiesh, W. E. Weihl, Scalable reader-writer locks for parallel systems., MIT-LCS-TR-521, published in 1991. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] POSIX question
* Florian Pflug: I think this breaks with strict overcommit settings (i.e. vm.overcommit_memory = 2 on linux). To fix that, you'd need a way to tell the kernel (or glibc) to simply reserve a chunk of virtual address space for further user. Not sure if there's a API for that... mmap with PROT_NONE and subsequent update with mprotect does this on Linux. (It's not clear to me what this is trying to solve, though.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] POSIX shared memory redux
* Tom Lane: Well, the fundamental point is that ignoring NFS is not the real world. We can't tell people not to put data directories on NFS, and even if we did tell them not to, they'd still do it. And NFS locking is not trustworthy, because the remote lock daemon can crash and restart (forgetting everything it ever knew) while your own machine and the postmaster remain blissfully awake. Is this still the case with NFSv4? Does the local daemon still keep the lock state? None of this is to say that an fcntl lock might not be a useful addition to what we do already. It is to say that fcntl can't just replace what we do already, because there are real-world failure cases that the current solution handles and fcntl alone wouldn't. If it requires NFS misbehavior (possibly in an older version), and you have to start postmasters on separate nodes (which you normally wouldn't do), doesn't this make it increasingly unlikely that it's going to be triggered in the wild? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Debian readline/libedit breakage
* Stephen Frost: * Greg Smith (g...@2ndquadrant.com) wrote: -GNU libreadine is certainly never going to add an OpenSSL exemption I really wish they would, that's just them being obnoxious- it's already LGPL, after all.. Source? I've only seen GPLed copies. We wouldn't face this issue with LGPL code. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types - efficiency
* Chris Browne:
The RangeType-based equivalent is the following:
rangetest@localhost- explain analyze select * from some_data where
'[2010-01-01,2010-02-01)'::daterange @ whensit;
QUERY PLAN
-
Seq Scan on some_data (cost=0.00..634.00 rows=1 width=8) (actual
time=1.045..111.739 rows=390 loops=1)
Filter: ('[ 2010-01-01, 2010-02-01 )'::daterange @ whensit)
Total runtime: 111.780 ms
(3 rows)
This, alas, reverts to a seq scan on the table, rather than restricting
itself to the tuples of interest.
This is quite similar to LIKE and regexp matches. The backend has a
kludge to use the index in such cases. It did not seem extensible to
me last time I looked, unfortunately.
--
Florian Weimerfwei...@bfk.de
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
* Jeff Davis: On Tue, 2011-01-04 at 14:18 +, Florian Weimer wrote: * Jeff Davis: 4. For the GiST penalty function, and perhaps some picksplit algorithms, it might be nice to know the length of a range, or do some other kinds of math. It introduces a lot of complexity to try to define math functions for each subtype, and try to make sure they behave sanely. So I was thinking that the user might need to specify a function that converts the subtype into a float that approximates a value's position in the total order. Doesn't the eqsel hint already provide this information? Can you clarify what you mean? I don't know what the eqsel hint is. Uhm, it's not not eqsel, but the RESTRICT clause on operators: http://www.postgresql.org/docs/8.4/static/xoper-optimization.html I'm wondering if one of these hint functions can be reused to compute range lengths. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
* Jeff Davis: 4. For the GiST penalty function, and perhaps some picksplit algorithms, it might be nice to know the length of a range, or do some other kinds of math. It introduces a lot of complexity to try to define math functions for each subtype, and try to make sure they behave sanely. So I was thinking that the user might need to specify a function that converts the subtype into a float that approximates a value's position in the total order. Doesn't the eqsel hint already provide this information? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Custom code int(32|64) = text conversions out of performance reasons
* Tom Lane: Yeah. You certainly don't want to do the division sequence twice, and a log() call wouldn't be cheap either, and there don't seem to be many other alternatives. What about a sequence of comparisons, and unrolling the loop? That could avoid the final division, too. It might also be helpful to break down the dependency chain for large input values. The int8 version should probably work in 1e9 chunks and use a zero-padding variant of the 32-bit code. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] crash-safe visibility map, take three
* Robert Haas: Those hint bit tests are a single machine instruction. It's tough to beat that. It's tough to get within two orders of magnitude. I'd like to, but I don't see how. For some scans, it might be possible to hoist the checks out of inner loops. (At least in principle, I'm not sure how much that would interfere with the executor architecture.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idle git question: how come so many objects?
* Tom Lane: $ git push Counting objects: 172, done. Compressing objects: 100% (89/89), done. Writing objects: 100% (89/89), 17.07 KiB, done. Total 89 (delta 80), reused 0 (delta 0) To ssh://g...@gitmaster.postgresql.org/postgresql.git 35a3def..8a6eb2e REL8_1_STABLE - REL8_1_STABLE cfb6ac6..b0e2092 REL8_2_STABLE - REL8_2_STABLE 301a822..0d45e8c REL8_3_STABLE - REL8_3_STABLE 61f8618..6bd3753 REL8_4_STABLE - REL8_4_STABLE 09425f8..0a85bb2 REL9_0_STABLE - REL9_0_STABLE c0b5fac..225f0aa master - master How does it get to 172? These are the number of objects git push (actually, git-send-pack, I think) needs to look at more closely, AFAIUI. It's a pretty arbitrary number. You see it sometimes during pull, too. And then where do the 89 and 80 numbers come from? 89 is the number of objects which need to be transmitted. Of those, 80 were compressed by diffing them to some other object (which might, in turn, be a diff). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Latches with weak memory ordering (Re: [HACKERS] max_wal_senders must die)
* Andres Freund: I was never talking about 'locking the whole cache' - I was talking about flushing/fencing it like a global read/write barrier would. And lock xchgb/xaddl does not imply anything for other cachelines but its own. My understanding is that once you've seen the result of an atomic operation on i386 and amd64, you are guaranteed to observe all prior writes performed by the thread which did the atomic operation, too. Explicit fencing is only necessary if you need synchronization without atomic operations. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)
* Terry Laurenzo: Agreed. BSON was born out of implementations that either lacked arbitrary precision numbers or had a strong affinity to an int/floating point way of thinking about numbers. I believe that if BSON had an arbitrary precision number type, it would be a proper superset of JSON. But JSON has only double-precision numbers!? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .gitignore files, take two
* James Cloos: I'm reading this a bit late, but... We (Xorg) found that ignoring: *~ *.bak *.patch in addition to the files generated by building is very helpful. I tend to put those into .git/info/exclude. They are somewhat developer-specific, after all. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF16 surrogate pairs in UTF8 encoding
* Tom Lane: I just noticed that we are now advertising the ability to insert UTF16 surrogate pairs in strings and identifiers (see section 4.1.2.2 in current docs, in particular). Is this really wise? I thought that surrogate pairs were specifically prohibited in UTF8 strings, because of the security hazards implicit in having more than one way to represent the same code point. There is relatively little risk because surrogate pairs cannot encode characters in the BMP, and presumably, most of the critical characters are located there. However, if this is converted to regular UTF-8, I really question the sense of this. Usually, people want CESU-8 to preserve ordering between languages such as C# and Java and their database, and conversion destroys this property. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] antisocial things you can do in git (but not CVS)
* Robert Haas: 1. Inability to cleanly and easily (and programatically) identify who committed what. With CVS, the author of a revision is the person who committed it, period. With git, the author string can be set to anything the person typing 'git commit' feels like. It's even more difficult than that. Git does not record at all who updated a particular branch to include a specific commit. This operation does not leave any metadata behind. It is possible to write a log file for audit purposes, but it's an out-of-band solution. My preference would be to stick to a style where we identify the committer using the author tag and note the patch author, reviewers, whether the committer made changes, etc. in the commit message. A single author field doesn't feel like enough for our workflow, and having a mix of authors and committers in the author field seems like a mess. It would be possible to enforce that on the server side, but it would interfere with merges. 3. Merge commits. I believe that we have consensus that commits should always be done as a squash, so that the history of all of our branches is linear. But it seems to me that someone could accidentally push a merge commit, either because they forgot to squash locally, or because of a conflict between their local git repo's master branch and origin/master. Can we forbid this? It's possible to do this with some scripting on the server side. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Invalid YAML output from EXPLAIN
* Greg Smith: Florian Weimer wrote: It has been claimed before that YAML is a superset of JSON, so why can't the YAML folks use the existing JSON output instead? Because JSON just crosses the line where it feels like there's so much markup that people expect a tool is necessary to read it, which has always been the issue with XML too--bad human readability. But YAML is not human-readable. There are human-readable subsets of it, but the general serializers do not produce them, and specific serializers are difficult to get right (as we've seen). EXPLAIN (FORMAT YAML) SELECT * FROM customers WHERE customerid1000 ORDER BY zip; QUERY PLAN - - Plan:+ Node Type: Sort+ Startup Cost: 4449.30 + Total Cost: 4496.80+ Plan Rows: 19000 + Plan Width: 268+ Sort Key: + - zip+ Plans: + - Node Type: Seq Scan+ Parent Relationship: Outer + Relation Name: customers + Alias: customers + Startup Cost: 0.00 + Total Cost: 726.00 + Plan Rows: 19000 + Plan Width: 268+ Filter: (customerid 1000) What does your parser do with this (equivalent but shorter) YAML output? - Plan: !!map 0 Node Type: Sort 1 Startup Cost: 4449.30 2 Total Cost: 4496.80 3 Plan Rows: 5 19000 4 Plan Width: 6 268 Sort Key: [zip] Plans: !!seq - *0: Seq Scan Parent Relationship: Outer Relation Name: 7 customers Alias: *7 *1: 0.00 *2: 726.00 *3: *5 *4: *6 Filter: (customerid 1000) Looking at the spec, it's rather difficult to come up with a readable subset which can parsed easily and is general in the sense that it can express empty strings, strings with embedded newlines, and so on. YAML's rules for dealing with whitespace are fairly complex, but are probably needed to get a more compact notation than JSON. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Invalid YAML output from EXPLAIN
* Tom Lane: Egad ... this is supposed to be an easily machine-generatable format? Perhaps you could surround all strings with in the generator, and escape all potentially special characters (which seems to include some whitespace even in quoted strings, unfortunately)? It has been claimed before that YAML is a superset of JSON, so why can't the YAML folks use the existing JSON output instead? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Confusion over Python drivers
* Jeff Davis:
Agreed. Ultimately, the conversion has to be done somewhere, but I don't
believe the driver is the place for it. Type conversions are always
going to be imperfect, and this has some important consequences:
* The type conversion system will be endlessly tweaked to improve it
* Developers will always run into problems with it in any complex
application, so we need to allow them to circumvent the system and do it
themselves when necessary.
The downside is that passing strings up to the application may have
distinctly worse performance characteristics than passing a number.
In ruby-pg, you can just do:
conn.exec(INSERT INTO foo VALUES($1), [Jeff])
And I think that's appropriate. What I'm saying is that there should
still exist some way to pass explicit types or formats (although that
should still be easier than it is in C ;). Here's the long form:
conn.exec(INSERT INTO foo VALUES($1),
[{:value = Jeff, :format = 0, :type = 0}])
Okay, this isn't too bad an API. I will use the same approach. In my
case, it means no transparent support for arrays, but per your own
guidelines, this is okay.
That copies value so that foo and bar have the same contents: a 4 byte
value \000. What would happen though, if val was transparently
decoded? It would decode it once in ruby, and again inside of postgres
(in byteain), leaving you with a one byte value in bar, even though foo
has a four-byte value.
I've never viewed it from this angle, and I agree that it makes sense.
Thanks for your observations and explanations, they were helpful.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Faster CREATE DATABASE by delaying fsync
* Tom Lane: Which options would that be? I am not aware that there any for any of the recent linux filesystems. Shouldn't journaling of metadata be sufficient? You also need to enforce ordering between the directory update and the file update. The file metadata is flushed with fsync(), but the directory isn't. On some systems, all directory operations are synchronous, but not on Linux. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Confusion over Python drivers
* Jeff Davis: I have written up a set of guidelines for driver development based on what I learned working on ruby-pg: http://wiki.postgresql.org/wiki/Driver_development Interesting, thanks. I'm contemplating to create a new language binding for libpq (or, to be more precise, turn an existing language binding into something that can be published). I've been agonizing a bit over how to create a bridge between the host language type system and the PostgreSQL type system. If I understand you correctly, you suggest to leave everything as strings. This solution has the appeal of being implemented easily. It also sidesteps a lot of issues revolving around different representation choices for numbers. Do you really suggest to preserve the PQexecParams API verbatim, that is, passing in three arrays containing type, value, and format? That seems to be a bit problematic. I suspect the common case will be to use unknown types, text format, and the default conversion from values to strings. Only for BYTEA values, something else is required, and I'm wondering how to encode that (the host language doesn't offer a distinction between text and binary strings). Conversely, for result sets, I'm tempted to transparently decode escaped BYTEA columns. Note that the ruby-pg driver doesn't 100% adhere to those standards (encoding is the primary problem, and that will be fixed). Lack of Unicode support means that I can punt that to application authors, I guess. By the way, the downside of using strings everywhere is that your binding API will most likely not work with SQLite (or any other SQL-like database which lacks column type information). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Confusion over Python drivers
* Andrew McNamara: Any other suggestions before I turn the above into a roadmap page on the wiki? I got sick of the constant stream of escaping bugs impacting on psycopg and pyPgSQL, and wrote my own DB-API driver, using the more modern libpq/binary/protocol 3 APIs where ever possible. The result is BSD licensed: http://code.google.com/p/ocpgdb/ I saw your note that you have to specify the types for date values etc. Is this really desirable or even necessary? Can't you specify the type as unknown (OID 705, I believe)? At work, we recently used to typelessness of Perl's DBD::Pg with great effect, introducing a more compact, type-safe representation for a few columns, without having to change all the existing Perl scripts accessing the database. That's why I'm wondering... (And we might be using Python instead of Perl today. Lack of a decent PostgreSQL module for Python meant it was very hard to argue against using Perl ...) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
* David Christensen: Currently, a session will look like the following: machack:machack:5485=# show tables; See: \d or \? for general help with psql commands machack:machack:5485=# Said formatting looks like it could use some improvement, open to suggestions, but something on a single line seems more useful. You should at least make clear that this is an error message due to an unsupported command. The output above looks broken. Something like this should be okay, I think: ERROR: unrecognized configuration parameter tables NOTICE: use \d to list tables, or \? for general help with psql commands ERROR: unrecognized configuration parameter databases NOTICE: use \l to list databases, or \? for general help with psql commands (I hope that this is less controversial, too.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Update on true serializable techniques in MVCC
* Florian Pflug: On 16.12.09 16:40 , Kevin Grittner wrote: Nicolas Barbiernicolas.barb...@gmail.com wrote: I am not sure whether the serialization failures that it may cause are dependent on the plan used. They are. But so are failures due to deadlocks even today, no? They are detected. In this context, serialization failure means that PostgreSQL generates a history which lacks one-copy serializability, without reporting any errors. (In the general case, the unique constraint violation which bugs me personally is a different beast and does result in an error.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Update on true serializable techniques in MVCC
* Albe Laurenz: That sounds like it should actually work. If you have got an index, yes. It seems to me that it would make locking behavior dependent on your query plan, too. BTW, PostgreSQL could raise a different error when a unique constraint violation is detected which involves a row which is not visible at the current snapshot. At least in my limited experience, that would allow applications to recover more easily if small transactions fail (similar to what you have to do on deadlock). Right now (well, at least with 8.3, haven't checked 8.4 yet), it's not possible to tell a unique constraint violation caused by a phantom from an application bug. (We currently faking this by retrying a fixed number of times and bailing out if the error returned by PostgreSQL looks like a unique constraint violation.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] YAML Was: CommitFest status/management
* Dimitri Fontaine: Well we have JSON and agreed it was a good idea to have it. Now JSON is a subset of YAML and some would prefer another YAML style (me included). YAML is rather difficult to parse, and most parsers assume a trusted document source because they support arbitrary class instantiations (and it's not possible to switch this off, or it's rather involved to do so). Plain JSON doesn't have this issue. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] YAML Was: CommitFest status/management
* Alvaro Herrera: Florian Weimer escribió: * Dimitri Fontaine: Well we have JSON and agreed it was a good idea to have it. Now JSON is a subset of YAML and some would prefer another YAML style (me included). YAML is rather difficult to parse, and most parsers assume a trusted document source because they support arbitrary class instantiations (and it's not possible to switch this off, or it's rather involved to do so). That's irrelevant because EXPLAIN YAML is already a trusted document source. Uhm, really? Do I have to expect code execution on the client when I connect to a database? I hope not. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Simon Riggs: * Put all hint bits in the block header to allow them to be excluded more easily from CRC checking. If we used 3 more bits from ItemIdData.lp_len (limiting tuple length to 4096) then we could store some hints in the item pointer. HEAP_XMIN_INVALID can be stored as LP_DEAD, since that will happen very quickly anyway. What about putting the whole visibility information out-of-line, into its own B-tree, indexed by page number? -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add YAML option to explain
* Greg Sabino Mullane: I thought the consensus was that we didn't want to get into supporting more formats. What does YAML provide that JSON does not? Readability and easy editing. All the power of JSON without the annoying quotes, braces, and brackets. But YAML is much more difficult to parse than JSON. Anybody who can afford a YAML parser can also afford a JSON parser, it is miniscule in comparison. 8-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Simon Riggs: On Thu, 2009-06-18 at 15:09 +0200, Stefan Kaltenbrunner wrote: the testcase discusses here is indeed CHAR(n) vs. VARCHAR. OK, thanks for pointing out my error. But I think your point still makes sense. Is it really necessary to determine the unpadded length for a query such as this one? SELECT c from sbtest where id between $1 and $2 order by c (See the start of the thread.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
* Simon Riggs: On Tue, 2009-06-09 at 13:40 +0200, Floris Bos / Maxnet wrote: fsync = off That's a bad plan if you care about your database. It shouldn't introduce this type of corruption, though. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
* Josh Berkus: Our documentation has been clear, back to version 7.0, that turning fsync=off carries the risk that you will have to recreate your entire database in the event of unexpected shutdown. That's not new. The documentation does not say this. Instead, there's the following rather explicit explanation that only OS crashes matter: | (Crashes of the database software itself are not a risk factor | here. Only an operating-system-level crash creates a risk of | corruption.) If it really matters how PostgreSQL is shut down in fsync = off mode (while the operating system keeps running), the documentation is seriously wrong here. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH v2] Add bit operations util header
* Jeremy Kerr: +#if defined(__GNUC__) \ + (defined(__ppc__) || defined(__powerpc__) || \ + defined(__ppc64__) || defined (__powerpc64__)) If you require GCC anyway, you can use __builtin_clz instead. (It's been available since GCC 4.1 at least.) -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH v2] Add bit operations util header
* Jeremy Kerr: Florian, +#if defined(__GNUC__) \ + (defined(__ppc__) || defined(__powerpc__) || \ + defined(__ppc64__) || defined (__powerpc64__)) If you require GCC anyway, you can use __builtin_clz instead. (It's been available since GCC 4.1 at least.) Because now we have to test the compiler *and* the version as well? This builtin is not architecture-specific, so you'd save the architecture check. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Managing multiple branches in git
* Tom Lane: I wondered for a second about symlinking .git from several checkout directories to a common master, but AFAICT .git stores both the repository and status information about the current checkout, so that's not gonna work. git clone --reference stores just a reference and does not copy the history. It's not going to help in the long run because history accumulating on the HEAD will be duplicated in your release branches. This is not a problem if you never merge stuff into them, but I don't know how much (recent) history browsing you want to do from your release checkouts. At the same time, I don't really buy the theory that relating commits on different branches via merges will work. In my experience it is very seldom the case that a patch applies to each back branch with no manual effort whatever, which is what I gather the merge functionality could help with. So maybe there's not much help to be had on this ... Correct. Merging doesn't work if you pick individual patches. This is a difficult problem, and few VCS seem to have tackled it. Working with a single tree and ccache would be another alternative (ccache still runs the preprocessor and hashes its output, so it doesn't care about file modification times). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renaming storage parameters
* Alvaro Herrera: Euler Taveira is arguing in an autovacuum thread that we should give storage parameters a different name; his argument is that autovacuum_enabled is not really a parameter that relates to storage. He is proposing relation parameters. They also apply to indices, right? I think it's a bit odd to call those relations (but there's precedent inside PostgreSQL), so it's just replacing one strange terminology with another. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Greg Stark: Wal logged changes are safe because of full_page_writes. Hint bits are safe because either the old or the new value will be on disk and we don't care which. Is this really true with all disks? IBM's DTLA disks didn't behave that way (an interrupted write could zero a sector), and I think the text book algorithms don't assume this behavior, either. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Gregory Stark: I've also seen single-bit errors caused by bad memory in a network interface. *Twice*. Particularly nasty since the CRC on TCP/IP packets is only 16-bit so a large enough ftp transfer would eventually finish despite the packet loss but with the occasional bits flipped. In these days of SAN/NAS and SCSI over IP that's pretty scary... I've seen double-bit errors in Internet routing which canceled each other out (the Internet checksum is just a sum, not a CRC, so this happens with some probability once you've got bit errors with a multiple-of-16 periodicity). -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Heikki Linnakangas: Currently, hint bit updates are not WAL-logged, and thus no full page write is done when only hint bits are changed. Imagine what happens if hint bits are updated on a page, but there's no other changes, and we crash so that only one half of the new page version makes it to disk (= torn page). The CRC would not match, even though the page is actually valid. The non-logged hint bit writes are somewhat dangerous anyway. Maybe it's time to get rid of this peculiarity, despite the performance impact? -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
* Tom Lane: No, not at all. Block-level checksums would be an order of magnitude more expensive: they're on bigger chunks of data and they'd be done more often. For larger blocks, checksumming can be parallelized at the instruction level, especially if the block size is statically known. And for large blocks, Adler32 isn't that bad compared to CRC32 from a error detection POV, so maybe you could use that. I've seen faults which were uncovered by page-level checksumming, so I'd be willing to pay the performance cost. 8-/ -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is mdextend really safe?
* Gregory Stark: On Unix that creates a sparse file where the intervening blocks are not allocated. When we later write out those blocks the filesystem then has to allocate space for them. This seems to happen relatively rarely. Creating temporary holes like this usually results in heavily fragmented files on the file systems I use, and I don't see this with PostgreSQL. (It's one of my gripes with Berkeley DB.) However, I looked at the code recently and couldn't figure out *why* PostgreSQL's observed behavior is this way. 8-( -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Protection from SQL injection
* Thomas Mueller: What do you think about it? Do you think it makes sense to implement this security feature in PostgreSQL as well? Can't this be implemented in the client library, or a wrapper around it? A simple approximation would be to raise an error when you encounter a query string that isn't contained in some special configuration file. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cached Query Plans
* Dawid Kuroczko: Right now the only way of getting such information from PostgreSQL is by logging all queries and analyzing logs. The current_query column of pg_stat_activity is useless as the (prepared) queries are usually so short lived that you will see one execution out of thousands happening. If the cached plans are kept a bit longer than actually necessary, it might also be possible to see the query plan of a query that involves temporary tables, something that is somewhat convoluted to do otherwise (ptop uses the query string from pg_stat_activity and tacks an EXPLAIN in front of it, which breaks with temporary tables). -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Read-ahead and parallelism in redo recovery
* Pavan Deolasee: The current redo-recovery is a single threaded, synchronous process. The XLOG is read sequentially, each log record is examined and replayed if required. This requires reading disk blocks in the shared buffers and applying changes to the buffer. The reading happens synchronously and that would usually make the redo process very slow. Are you sure that it's actually slow for that reason? Sequential I/O on the log is typically quite fast, and if the pages dirtied since the last checkpoint fit into the cache (shared buffers or OS cache), even that part of recovery does not result in lots of random I/O (with 8.3 and full page writes active; this is a relatively recent change). In the end, I wouldn't be surprised if for most loads, cache warming effects dominated recovery times, at least when the machine is not starved on RAM. -- Florian Weimer[EMAIL PROTECTED] BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
