Re: [HACKERS] PATCH: regular logging of checkpoint progress
On 08/27/2011 12:01 AM, Noah Misch wrote: On Fri, Aug 26, 2011 at 10:46:33AM +0200, Tomas Vondra wrote: 1. collect pg_stat_bgwriter stats 2. run pgbench for 10 minutes 3. collect pg_stat_bgwriter stats (to compute difference with (1)) 4. kill the postmaster The problem is that when checkpoint stats are collected, there might be a checkpoint in progress and in that case the stats are incomplete. In some cases (especially with very small db blocks) this has significant impact because the checkpoints are less frequent. Could you remove this hazard by adding a step 2a. psql -c CHECKPOINT? That's what I do in pgbench-tools, and it helps a lot. It makes it easier to identify when the checkpoint kicks in if you know it's approximately the same time after each test run begins, given similar testing parameters. That said, it's hard to eliminate all of the edge conditions here. For example, imagine that you're consuming WAL files such that you hit checkpoint_segments every 4 minutes. In a 10 minute test run, a checkpoint will start at 4:00 and finish at around 6:00 (with checkpoint_completion_target=0.5). The next will start at 8:00 and should finish at around 10:00--right at the end of when the test ends. Given the variation that sync timing and rounding issues in the write phase adds to things, you can expect that some test runs will include stats from 2 checkpoints, while others will end the test just before the second one finishes. It does throw the numbers off a bit. To avoid this when it pops up, I normally aim to push up to where there are =4 checkpoints per test run, just so whether I get n or n-1 of them doesn't impact results as much. But that normally takes doubling the length of the test to 20 minutes. As it will often take me days of test time to plow through exploring just a couple of parameters, I'm sympathetic to Tomas trying to improve accuracy here without having to run for quite so long. There's few people who have this problem to worry about though, it's a common issue with benchmarking but not many other contexts. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] tsvector concatenation - backend crash
On 2011-08-26 23:02, Tom Lane wrote: AFAICT this is a red herring: the bug exists all the way back to where tsvector_concat was added, in 8.3. I think the reason that your test case happens to not crash before this commit is that it changed the sort ordering rules for lexemes. As you can see from my minimal example above, we might need different numbers of pad bytes depending on how the lexemes sort relative to each other. Anyway, patch is committed; thanks for the report! I've just confirmed the fix.. thanks for your prompt action. -- Jesper -- 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] pg_restore --no-post-data and --post-data-only
Andrew Dunstan and...@dunslane.net writes: For anything more fine-grained, I'm inclined to say that people need to roll their own. pg_restore's --list and --use-list give you extremely fine-grained control. I have working scripts which use these for example to filter out londiste and pgq objects, certain large tables, audit objects and Which is exactly the core features of pg_staging, that builds schema whitelist and schema_nodata options on top of pg_restore listing. The only complex thing here is to be able to filter out triggers using a function defined in a schema you're filtering out, but pg_staging has support for that. http://tapoueh.org/pgsql/pgstaging.html https://github.com/dimitri/pg_staging http://tapoueh.org/blog/2011/03/29-towards-pg_staging-10.html And you can also only use the pg_restore listing commands of pg_staging without having to do the full installation of its features. Will write some article about how to use it for only catalog listing purpose, without its infrastructure for fetching backups and managing dev staging environments. I don't have anything in principle against your '--sections=foo bar' suggestion, but it would be more work to program. Simpler, and probably more consistent with how we do other things, would be allowing multiple --section options, if we don't want to have named options such as I have provided. +1 for --section foo --section bar. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Inputting relative datetimes
On 27 August 2011 02:32, Robert Haas robertmh...@gmail.com wrote:
Frankly, our current date parsing code is pretty darn strange and
flaky...
So Robert and Merlin both expressed concerns that the existing
datetime string parsing code is so complicated that adding to it would
likely just introduce more bugs.
My first thought was 'how hard can it be?' - famous last words :-)
Anyway I knocked up the attached POC patch implementing my originally
proposed syntax. I haven't tested it much, so it may well have bugs,
but the separation of the new code seems pretty clean, so it shouldn't
break any existing parsing logic.
Here are a few examples of what it allows:
SELECT str, str::timestamptz result FROM (VALUES
('2011-08-27'),
('today'),
('now'),
('today minus 5 days'),
('now plus 2 hours'),
('tomorrow plus 1 month'),
('minus 30 minutes'),
('25/12/2011 plus 6 weeks')
) AS x(str);
str |result
-+---
2011-08-27 | 2011-08-27 00:00:00+01
today | 2011-08-27 00:00:00+01
now | 2011-08-27 12:11:46.245659+01
today minus 5 days | 2011-08-22 00:00:00+01
now plus 2 hours| 2011-08-27 14:11:46.245659+01
tomorrow plus 1 month | 2011-09-28 00:00:00+01
minus 30 minutes| 2011-08-27 11:41:46.245659+01
25/12/2011 plus 6 weeks | 2012-02-05 00:00:00+00
(8 rows)
(I decided not to implement 'Christmas plus three fortnights' ;-)
I don't have a feel for how widely useful this is, and I'm not
particularly wedded to this syntax, but if nothing else it has been a
fun exercise figuring out how the datetime string parsing code works.
Regards,
Dean
relative-timestamps.patch
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] limit in subquery causes poor selectivity estimation
This is an artificial test case shrunk down from a much larger real
query.
CREATE TABLE test1 (
sha1 bytea PRIMARY KEY,
something text
);
CREATE TABLE test2 (
sha1 bytea PRIMARY KEY,
blah text
);
Fill those with 1000 random rows each, e.g.,
for i in $(seq 1 1000); do sha1=$(echo $i | sha1sum | cut -f1 -d' '); psql -d
test -c INSERT INTO test1 VALUES (decode('$sha1','hex'), 'blah$i$i'); done
for i in $(seq 500 1500); do sha1=$(echo $i | sha1sum | cut -f1 -d' '); psql -d
test -c INSERT INTO test2 VALUES (decode('$sha1','hex'), 'foo$i'); done
(Doesn't really matter whether the key values are the same or
overlapping or not. Just to make it interesting.)
ANALYZE;
EXPLAIN SELECT * FROM test1 WHERE sha1 in (SELECT sha1 FROM test2);
QUERY PLAN
--
Hash Semi Join (cost=30.52..61.27 rows=1000 width=27)
Hash Cond: (test1.sha1 = test2.sha1)
- Seq Scan on test1 (cost=0.00..17.00 rows=1000 width=27)
- Hash (cost=18.01..18.01 rows=1001 width=21)
- Seq Scan on test2 (cost=0.00..18.01 rows=1001 width=21)
That's OK. Apparently it can tell that joining two tables on their
primary keys cannot result in more rows than the smaller table. (Or
can it?)
EXPLAIN SELECT * FROM test1 WHERE sha1 in (SELECT sha1 FROM test2 LIMIT 200);
QUERY PLAN
--
Hash Join (cost=10.60..33.35 rows=500 width=27)
Hash Cond: (test1.sha1 = test2.sha1)
- Seq Scan on test1 (cost=0.00..17.00 rows=1000 width=27)
- Hash (cost=8.10..8.10 rows=200 width=32)
- HashAggregate (cost=6.10..8.10 rows=200 width=32)
- Limit (cost=0.00..3.60 rows=200 width=21)
- Seq Scan on test2 (cost=0.00..18.01 rows=1001
width=21)
Here, however, it has apparently not passed this knowledge through the
LIMIT.
The problem is that in the real query, instead of the 500 up there it
estimates about 30 million (which might be a reasonable estimate for a
join between two unkeyed columns), when it should in fact be 200.
(And again, this is part of a larger query, which is then completely
messed up because of this misestimation.)
So what's up with that? Just a case of, we haven't thought about
covering this case yet, or are there larger problems?
--
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] Inputting relative datetimes
On 27 August 2011 12:29, Dean Rasheed dean.a.rash...@gmail.com wrote: ... if nothing else it has been a fun exercise figuring out how the datetime string parsing code works. While looking through the current code, I spotted the following oddity: select timestamp 'yesterday 10:30'; timestamp - 2011-08-26 10:30:00 which is what you'd expect, however: select timestamp '10:30 yesterday'; timestamp - 2011-08-26 00:00:00 Similarly today and tomorrow reset any time fields so far, but ISTM that they should really be preserving the hour, min, sec fields decoded so far. Regards, Dean -- 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] -Wformat-zero-length
On tor, 2011-07-07 at 18:09 -0400, Tom Lane wrote: I wrote: Peter Eisentraut pete...@gmx.net writes: I was adding gcc printf attributes to more functions in obscure places, and now I'm seeing this in pg_upgrade: relfilenode.c:72:2: warning: zero-length gnu_printf format string [-Wformat-zero-length] Shouldn't it be prep_status(\n)? If not, why not? On closer inspection, it appears to me that prep_status should never be called with a string containing a newline, period, and the test it contains for that case is just brain damage. The only reason to call it at all is to produce a line like message .. where something more is expected to be added to the line later. Calls that are meant to produce a complete line could go directly to pg_log. This in turn implies that transfer_all_new_dbs's use of the function is broken and needs to be rethought. I think this got a bit besides the point. There is probably some bogosity in the logging implementation, but I think the prep_status() call is correct and purposeful at that point, namely to clear the line. The question is, do we consider empty format strings a bug worth warning about, or should we shut off the warning? -- 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] Inputting relative datetimes
On lör, 2011-08-27 at 12:29 +0100, Dean Rasheed wrote: So Robert and Merlin both expressed concerns that the existing datetime string parsing code is so complicated that adding to it would likely just introduce more bugs. My first thought was 'how hard can it be?' - famous last words :-) Maybe you can find an existing Perl or Python module that has already implemented this. Then the solution might be 5 lines of wrapping this into a PostgreSQL function. -- 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] Inputting relative datetimes
Greetings, On Thursday, August 25, 2011 05:39:09 AM Dean Rasheed wrote: As background, I have an app that accepts user text input and casts it to a timestamp in order to produce reports. I use PostgreSQL's timestamp input conversion for this, since it gives a lot of flexibility, and can parse pretty much anything the users throw at it. It is also handy that it recognizes special case values like now, today, tomorrow and yesterday. However, I can't see any way of entering more general relative timestamps like 5 days ago or 2 hours from now. Years ago I wrapped 'getdate.y' from the CVS source code and made it into a python extension. It handles +2 hours or next week, etc. I don't know much of anything about making pg contrib modules, but it should not be hard to do. The way it works is you pass in a string and it returns the unix timestamp. [...snipped...] Thoughts? Better ideas? Regards, Dean Regards, J -- 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: regular logging of checkpoint progress
On 27 Srpen 2011, 6:01, Noah Misch wrote: On Fri, Aug 26, 2011 at 10:46:33AM +0200, Tomas Vondra wrote: 1. collect pg_stat_bgwriter stats 2. run pgbench for 10 minutes 3. collect pg_stat_bgwriter stats (to compute difference with (1)) 4. kill the postmaster The problem is that when checkpoint stats are collected, there might be a checkpoint in progress and in that case the stats are incomplete. In some cases (especially with very small db blocks) this has significant impact because the checkpoints are less frequent. Could you remove this hazard by adding a step 2a. psql -c CHECKPOINT? I already do that, but it really does not solve the issue. It just aligns the first expected 'timed' checkpoint, it does not solve the problem with in-progress checkpoints unless the runs behave exactly the same (and that's the boring case). Tomas -- 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] [COMMITTERS] pgsql: Avoid locale dependency in expected output.
Avoid locale dependency in expected output. We'll have to settle for just listing the extensions' data types, since function arguments seem to sort differently in different locales. Per buildfarm results. This could probably be worked around by using COLLATE C in psql \df and \do. That would probably make sense, because the argument types are by themselves of type name, so a list of them should perhaps sort like name as well. -- 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] Cryptic error message in low-memory conditions
On Sat, Aug 27, 2011 at 01:59, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina dan...@heroku.com writes: Some Postgres servers will error out for a while with the following error message: expected authentication request from server, but received c [ and this seems to be triggered by fork failures in the server ] spockFascinating./spock Agreed. snip The reason for this is that that same bit of code supposes that any E response must mean that the postmaster didn't recognize NEGOTIATE_SSL_CODE. It doesn't (and of course shouldn't) pay any attention to the actual textual error message. Now, seeing as how NEGOTIATE_SSL_CODE has been understood by every build since PG 7.0, I believe that this is dead code and we could remove it; it seems exceedingly unlikely that any modern build of libpq will ever be used to talk to a server that responds to that with E. What will be the result if you do use the modern libpq against that? Anyway - that's 5 *unsupported* versions back. So even if people do use that, I say they have to downgrade libpq as well ;) +1 for removing it. In fact, when do we reach the point that we can remove all the support for the v2 protocol completely? (this would obviously not be as a bugfix, but perhaps in 9.2)? Is there any particular reason we need to support both anymore? At least in the client? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] Inputting relative datetimes
On 27 August 2011 14:14, Peter Eisentraut pete...@gmx.net wrote:
On lör, 2011-08-27 at 12:29 +0100, Dean Rasheed wrote:
So Robert and Merlin both expressed concerns that the existing
datetime string parsing code is so complicated that adding to it would
likely just introduce more bugs.
My first thought was 'how hard can it be?' - famous last words :-)
Maybe you can find an existing Perl or Python module that has already
implemented this. Then the solution might be 5 lines of wrapping this
into a PostgreSQL function.
Ah now that's an interesting idea.
Python's dateutil module seems to come highly recommended, although I
don't find this too encouraging:
dateutil.parser.parse('today', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)
dateutil.parser.parse('tomorrow', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)
dateutil.parser.parse('foobar', fuzzy=True)
datetime.datetime(2011, 8, 27, 0, 0)
Still, there might be something better out there...
Cheers,
Dean
--
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] Inputting relative datetimes
On 27 August 2011 14:29, Jeff MacDonald j...@zoidtechnologies.com wrote: Greetings, On Thursday, August 25, 2011 05:39:09 AM Dean Rasheed wrote: As background, I have an app that accepts user text input and casts it to a timestamp in order to produce reports. I use PostgreSQL's timestamp input conversion for this, since it gives a lot of flexibility, and can parse pretty much anything the users throw at it. It is also handy that it recognizes special case values like now, today, tomorrow and yesterday. However, I can't see any way of entering more general relative timestamps like 5 days ago or 2 hours from now. Years ago I wrapped 'getdate.y' from the CVS source code and made it into a python extension. It handles +2 hours or next week, etc. I don't know much of anything about making pg contrib modules, but it should not be hard to do. The way it works is you pass in a string and it returns the unix timestamp. [...snipped...] That sounds like a pretty good approach, and a contrib module might well be the way to go. I'm not sure how best to handle timezones though, since it's hard-coded list probably won't match the timezones PostgreSQL knows about. Maybe that doesn't matter, I'm not sure. Regards, Dean -- 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] Questions and experiences writing a Foreign Data Wrapper
2011/8/26 Albe Laurenz laurenz.a...@wien.gv.at: I wrote: I wrote a FDW for Oracle to a) learn some server coding and b) see how well the FDW API works for me. I have released the software on PgFoundry: http://oracle-fdw.projects.postgresql.org/ Would it make sense to mention that in chapter 5.10 of the documentation? Let's share it on PGXN! There are already three FDWs, and I'm gonig to add one more. Thanks, -- Hitoshi Harada -- 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] [COMMITTERS] pgsql: Avoid locale dependency in expected output.
Peter Eisentraut pete...@gmx.net writes: Avoid locale dependency in expected output. We'll have to settle for just listing the extensions' data types, since function arguments seem to sort differently in different locales. Per buildfarm results. This could probably be worked around by using COLLATE C in psql \df and \do. That would probably make sense, because the argument types are by themselves of type name, so a list of them should perhaps sort like name as well. I did consider that, but the PITA factor looked too large. Currently the SQL code is like 'ORDER BY 1,2,4'. We can't do '4 COLLATE C' for syntactical reasons, so we'd have to repeat the column expression. There's also the fact that psql couldn't use COLLATE with a pre-9.1 server. So I wasn't going to propose that just to make a regression test a bit more complete. OTOH, if people think that locale-independent ordering of the results is a good thing in itself, maybe it's worth the trouble. regards, tom lane -- 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] Cryptic error message in low-memory conditions
Magnus Hagander mag...@hagander.net writes: On Sat, Aug 27, 2011 at 01:59, Tom Lane t...@sss.pgh.pa.us wrote: Now, seeing as how NEGOTIATE_SSL_CODE has been understood by every build since PG 7.0, I believe that this is dead code and we could remove it; it seems exceedingly unlikely that any modern build of libpq will ever be used to talk to a server that responds to that with E. What will be the result if you do use the modern libpq against that? I'll check it after I write the patch, but what I'd expect to happen is that libpq would fail the connection and report the server's error message, which would be something like unrecognized protocol version number. Anybody who did complain of this could be told to use sslmode=disable when talking to the ancient server. Anyway - that's 5 *unsupported* versions back. More to the point, it's been a very very long time since we've heard of anyone using a server older than 7.2. (And there's a good reason for that, which is that 7.2 was the first version that wouldn't go belly-up at 4 billion transactions.) In fact, when do we reach the point that we can remove all the support for the v2 protocol completely? (this would obviously not be as a bugfix, but perhaps in 9.2)? Is there any particular reason we need to support both anymore? At least in the client? Fair question. We *have* still heard of people using 7.2/7.3, I think. Another point here is that there are JDBC people intentionally forcing protocol version 2 as a means of controlling prepared-statement plan lifespan. I hope that the auto-replan code that I intend to get into 9.2 will provide a better answer for those folks, but removing the workaround at the same time might be a tad premature. So my feeling is not quite yet, maybe in a couple more years. regards, tom lane -- 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] limit in subquery causes poor selectivity estimation
Peter Eisentraut pete...@gmx.net writes: EXPLAIN SELECT * FROM test1 WHERE sha1 in (SELECT sha1 FROM test2); QUERY PLAN -- Hash Semi Join (cost=30.52..61.27 rows=1000 width=27) Hash Cond: (test1.sha1 = test2.sha1) - Seq Scan on test1 (cost=0.00..17.00 rows=1000 width=27) - Hash (cost=18.01..18.01 rows=1001 width=21) - Seq Scan on test2 (cost=0.00..18.01 rows=1001 width=21) That's OK. Apparently it can tell that joining two tables on their primary keys cannot result in more rows than the smaller table. (Or can it?) More like it knows that a semijoin can't produce more rows than the lefthand input has. But I think it is actually applying stats for both columns here. EXPLAIN SELECT * FROM test1 WHERE sha1 in (SELECT sha1 FROM test2 LIMIT 200); Here, however, it has apparently not passed this knowledge through the LIMIT. The LIMIT prevents the subquery from being flattened entirely, ie we don't have just test1 SEMI JOIN test2 but test1 SEMI JOIN (SELECT * FROM test2 LIMIT 200). If you look at examine_variable in selfuncs.c you'll note that it punts for Vars coming from unflattened subqueries. So what's up with that? Just a case of, we haven't thought about covering this case yet, or are there larger problems? The larger problem is that if a subquery didn't get flattened, it's often because it's got LIMIT, or GROUP BY, or some similar clause that makes it highly suspect whether the statistics available for the table column are reasonable to use for the subquery outputs. It wouldn't be that hard to grab the stats for test2.sha1, but then how do you want to adjust them to reflect the LIMIT? regards, tom lane -- 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] tab stop in README
On mån, 2011-08-22 at 04:09 +, YAMAMOTO Takashi wrote: i know that postgresql uses ts=4 for C source code. but how about documatation? I'd say ideally don't use any tabs at all. src/backend/access/transam/README seems to have both of ts=4 and ts=8 mixed. It appears to be geared for ts=4. Could you send a patch or other indication for what you think needs changing? -- 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] Cryptic error message in low-memory conditions
I wrote: Lastly, I noticed that if I tried this repeatedly on a Unix socket, I sometimes got psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. could not send startup packet: Broken pipe rather than the expected results. I think what is happening here is a race condition, such that if the postmaster closes the socket without having read the startup packet, the client might not have actually gotten to its send() yet, and then it will get EPIPE from send() before it gets to the point of reading the error response. I tried to fix this by having report_fork_failure_to_client eat any pending data before responding: I've applied patches for the other two issues, but I'm having second thoughts about trying to hack around this one. The proposed patch doesn't really eliminate the problem, and in any case the message is not totally off base: the server did close the connection unexpectedly. It'd be nicer if users didn't have to look in the server log to find out why, but we can't guarantee that. However, I've developed a second concern about report_fork_failure_to_client, which is its habit of sending the fork failure message formatted according to 2.0 protocol. This causes libpq (and possibly other clients) to suppose that it's talking to a pre-7.4 server and try again in 2.0 protocol. So if the fork failure is transient, you have the problem of being unexpectedly and silently downgraded to 2.0 protocol. We could fix that by changing the function to send the message in 3.0 protocol always --- it would take more code but it's certainly doable. The trouble with that is that a pre-7.4 libpq would see the error message as garbage; and I'm not sure how pleasantly the JDBC driver handles it either, if it is trying to use 2.0 protocol. A more long-range point about it is that the next time we make a protocol version bump that affects the format of error messages, the problem comes right back. It'd be better if the message somehow indicated that the server hadn't made any attempt to match the client protocol version. I guess if we went up to 3.0 protocol, we could include a SQLSTATE value in the message and libpq could test that before making assumptions. Thoughts? regards, tom lane -- 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] Cryptic error message in low-memory conditions
I wrote: Magnus Hagander mag...@hagander.net writes: On Sat, Aug 27, 2011 at 01:59, Tom Lane t...@sss.pgh.pa.us wrote: Now, seeing as how NEGOTIATE_SSL_CODE has been understood by every build since PG 7.0, I believe that this is dead code and we could remove it; it seems exceedingly unlikely that any modern build of libpq will ever be used to talk to a server that responds to that with E. What will be the result if you do use the modern libpq against that? I'll check it after I write the patch, but what I'd expect to happen is that libpq would fail the connection and report the server's error message, which would be something like unrecognized protocol version number. Anybody who did complain of this could be told to use sslmode=disable when talking to the ancient server. Just for the archives' sake, what happens with the committed patch is either a successful non-SSL connection: $ psql dbname=template1 sslmode=prefer host=localhost NOTICE: Unrecognized variable client_encoding psql (9.2devel, server 6.5.3) WARNING: psql version 9.2, server version 6.5. Some psql features might not work. Type help for help. or if you tried to force SSL usage, you get this: $ psql dbname=template1 sslmode=require host=localhost psql: Unsupported frontend protocol. Unsupported frontend protocol.$ The reason for the repeated message is that libpq tries twice and appends the error messages to its buffer both times. I didn't think this was important enough to try to fix; and anyway I seem to recall that it's intentional that we append the messages from multiple connection attempts. BTW, this response starting with U, together with the buffer flush bug, seems to explain some of the old reports in the archives, such as http://archives.postgresql.org/pgsql-hackers/2005-09/msg01106.php regards, tom lane -- 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] Cryptic error message in low-memory conditions
On Sat, Aug 27, 2011 at 1:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: A more long-range point about it is that the next time we make a protocol version bump that affects the format of error messages, the problem comes right back. It'd be better if the message somehow indicated that the server hadn't made any attempt to match the client protocol version. I guess if we went up to 3.0 protocol, we could include a SQLSTATE value in the message and libpq could test that before making assumptions. Hmm. What do you think about the way ssh handles support and negotiation of cipher methods between client and server: in that case, I believe both client and server advertise what ciphers they are willing to accept. Changing the format of that negotiation procedure would be a pain, but hopefully that could be gotten pretty much right the first time. Right now, my understanding is libpq sends a version to the server, but doesn't really receive a list of supported formats in response, from this thread it seems what happens is if an old-style message is returned then it's presumed the server can't handle the new versions, which is not necessarily true. The server seems to written with the sensible assumption that it should use the oldest sensible message format to get the idea across to libpq of as many versions as possible, but libpq is taking a byproduct of the *way* the message/error is sent to learn something about the server's supported versions, and using that to affect more behavior. So perhaps one solution for the distant future is to respond with protocol versions so that libpq need not rely on guessing based on a particular message style it receives. Alternatively, could it make sense to just always report fork failure in the newest libpq version and broadcast failures in each protocol version's format, one after another? This presumes that old libpqs are forward-compatible to skip un-understood messages forever, though, which is saying a lot about the future...and that seems to be affecting ancient libpqs, as per your message. Please correct me; my understandings of the of the startup process have been hastily acquired from the documentation. -- fdr -- 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] Cryptic error message in low-memory conditions
Daniel Farina dan...@heroku.com writes: On Sat, Aug 27, 2011 at 1:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: A more long-range point about it is that the next time we make a protocol version bump that affects the format of error messages, the problem comes right back. It'd be better if the message somehow indicated that the server hadn't made any attempt to match the client protocol version. I guess if we went up to 3.0 protocol, we could include a SQLSTATE value in the message and libpq could test that before making assumptions. Hmm. What do you think about the way ssh handles support and negotiation of cipher methods between client and server: in that case, I believe both client and server advertise what ciphers they are willing to accept. The difficulty here is that, having failed to fork off a subprocess from the postmaster, we really can't do much of anything beyond blasting out a predetermined failure message. We can *not* have the postmaster wait to see what protocol the client asked for, or we risk denial-of-service problems from malfunctioning or malicious clients. So there's basically no solution to be found by altering what it is that the client sends. Alternatively, could it make sense to just always report fork failure in the newest libpq version and broadcast failures in each protocol version's format, one after another? This presumes that old libpqs are forward-compatible to skip un-understood messages forever, though, which is saying a lot about the future...and that seems to be affecting ancient libpqs, as per your message. Yeah. We could possibly hope that the current format of error messages is sufficiently general that it'll never be obsoleted, or at least will always be a valid subset of future formats. regards, tom lane -- 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] pg_restore --no-post-data and --post-data-only
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Well, notwithstanding my well known love of perl, that strikes me as spending a pound to save a penny. And custom format dumps rock ;-) Also, your recipe above is buggy, BTW. A CREATE INDEX statement might well not be the first item in the post-data section. But we could also add these switches to pg_dump too if people feel it's worthwhile. I haven't looked but the logic should not be terribly hard. A big +1 to --pre-data and --post-data, but until we get there, or if you have an existing dump file (schema *or* schema+data) that needs parsing, there is an existing tool: http://blog.endpoint.com/2010/01/splitting-postgres-pgdump-into-pre-and.html Once these new flags and the ability to custom format dump pg_dumpall is done, I'll have very little left to complain about with pg_dump :) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201108271855 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk5ZdfwACgkQvJuQZxSWSsipDQCgpmNtD/I/2gfAzm2b3jouD8nS qhgAn33t5VLiF8HeslBwCqyMzQJy6VN5 =PfK7 -END PGP SIGNATURE- -- 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] Inputting relative datetimes
Greetings, On Saturday, August 27, 2011 11:36:13 AM Dean Rasheed wrote: I'm not sure how best to handle timezones though, since it's hard-coded list probably won't match the timezones PostgreSQL knows about. Maybe that doesn't matter, I'm not sure. It'll matter when the expression has a result that crosses the DST date. Does Postgres have a library that could be used by the parser? Regards, Dean Regards, Jeff -- 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] Cryptic error message in low-memory conditions
On Sat, Aug 27, 2011 at 3:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Daniel Farina dan...@heroku.com writes: On Sat, Aug 27, 2011 at 1:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: A more long-range point about it is that the next time we make a protocol version bump that affects the format of error messages, the problem comes right back. It'd be better if the message somehow indicated that the server hadn't made any attempt to match the client protocol version. I guess if we went up to 3.0 protocol, we could include a SQLSTATE value in the message and libpq could test that before making assumptions. Hmm. What do you think about the way ssh handles support and negotiation of cipher methods between client and server: in that case, I believe both client and server advertise what ciphers they are willing to accept. The difficulty here is that, having failed to fork off a subprocess from the postmaster, we really can't do much of anything beyond blasting out a predetermined failure message. We can *not* have the postmaster wait to see what protocol the client asked for, or we risk denial-of-service problems from malfunctioning or malicious clients. So there's basically no solution to be found by altering what it is that the client sends. Hmm. Well, for this requirement I don't think it's necessary to have the postmaster wait, luckily: the missing information on the client side is what protocols does this postmaster support, and it's sniffing that out of the format a received message. Instead, the postmaster could send something as simple as a quite small static string burned in at compile-time that advertises to the receiving libpq what is supported, that way it doesn't have to guess. Other than a tiny amount of outbound traffic per bogus connection attempt (which I think is spent anyway to say authentication denied or in some cases out of memory for example) I think this avoids a problem with malicious clients. Such a versions-supported string probably has a smaller complexity that is vulnerable to change vs. the error protocol, and is explicit: libpq guessworking the supported messages on the server based on traffic sniffing seems pretty tortured. SSL might throw a small wrench into everything, though: does one want to send the supported protocol information when the channel is susceptible to MITM attacks, and *then* move to a secure channel? This seems like a pretty slim vulnerability (the ability for an attacker to poke at the protocol versions supported string, which presumably would give them all sorts of other nasty power already) as long as all other communications are SSLified. One could probably get around this by sending the version information twice, once before and once after SSL negotiation so the client has an opportunity to spot icky things going on. (Embellishment: if one has policy in pg_hba.conf as to what protocols are supported it may need to be a dynamic string influenced by the conf on startup or whatever, but I think this is a comparatively small detail.) -- fdr -- 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] Inputting relative datetimes
On Sat, Aug 27, 2011 at 7:43 AM, Dean Rasheed dean.a.rash...@gmail.com wrote: On 27 August 2011 12:29, Dean Rasheed dean.a.rash...@gmail.com wrote: ... if nothing else it has been a fun exercise figuring out how the datetime string parsing code works. While looking through the current code, I spotted the following oddity: select timestamp 'yesterday 10:30'; timestamp - 2011-08-26 10:30:00 which is what you'd expect, however: select timestamp '10:30 yesterday'; timestamp - 2011-08-26 00:00:00 Similarly today and tomorrow reset any time fields so far, but ISTM that they should really be preserving the hour, min, sec fields decoded so far. Sounds right to me. Want to send a patch? BTW, this is exactly the sort of thing that makes me a bit skeptical about further extending this... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] spinlocks on HP-UX
I was able to obtain access to a 32-core HP-UX server. I repeated the
pgbench -S testing that I have previously done on Linux, and found
that the results were not too good. Here are the results at scale
factor 100, on 9.2devel, with various numbers of clients. Five minute
runs, shared_buffers=8GB.
1:tps = 5590.070816 (including connections establishing)
8:tps = 37660.233932 (including connections establishing)
16:tps = 67366.099286 (including connections establishing)
32:tps = 82781.624665 (including connections establishing)
48:tps = 18589.995074 (including connections establishing)
64:tps = 16424.661371 (including connections establishing)
And just for comparison, here are the numbers at scale factor 1000:
1:tps = 4751.768608 (including connections establishing)
8:tps = 33621.474490 (including connections establishing)
16:tps = 58959.043171 (including connections establishing)
32:tps = 78801.265189 (including connections establishing)
48:tps = 21635.234969 (including connections establishing)
64:tps = 18611.863567 (including connections establishing)
After mulling over the vmstat output for a bit, I began to suspect
spinlock contention. I took a look at document called Implementing
Spinlocks on the Intel Itanium Architecture and PA-RISC, by Tor
Ekqvist and David Graves and available via the HP web site, which
states that when spinning on a spinlock on these machines, you should
use a regular, unlocked test first and use the atomic test only when
the unlocked test looks OK. I tried implementing this in two ways,
and both produced results which are FAR superior to our current
implementation. First, I did this:
--- a/src/include/storage/s_lock.h
+++ b/src/include/storage/s_lock.h
@@ -726,7 +726,7 @@ tas(volatile slock_t *lock)
typedef unsigned int slock_t;
#include ia64/sys/inline.h
-#define TAS(lock) _Asm_xchg(_SZ_W, lock, 1, _LDHINT_NONE)
+#define TAS(lock) (*(lock) ? 1 : _Asm_xchg(_SZ_W, lock, 1, _LDHINT_NONE))
#endif /* HPUX on IA64, non gcc */
That resulted in these numbers. Scale factor 100:
1:tps = 5569.911714 (including connections establishing)
8:tps = 37365.364468 (including connections establishing)
16:tps = 63596.261875 (including connections establishing)
32:tps = 95948.157678 (including connections establishing)
48:tps = 90708.253920 (including connections establishing)
64:tps = 100109.065744 (including connections establishing)
Scale factor 1000:
1:tps = 4878.332996 (including connections establishing)
8:tps = 33245.469907 (including connections establishing)
16:tps = 56708.424880 (including connections establishing)
48:tps = 69652.232635 (including connections establishing)
64:tps = 70593.208637 (including connections establishing)
Then, I did this:
--- a/src/backend/storage/lmgr/s_lock.c
+++ b/src/backend/storage/lmgr/s_lock.c
@@ -96,7 +96,7 @@ s_lock(volatile slock_t *lock, const char *file, int line)
int delays = 0;
int cur_delay = 0;
- while (TAS(lock))
+ while (*lock ? 1 : TAS(lock))
{
/* CPU-specific delay each time through the loop */
SPIN_DELAY();
That resulted in these numbers, at scale factor 100:
1:tps = 5564.059494 (including connections establishing)
8:tps = 37487.090798 (including connections establishing)
16:tps = 66061.524760 (including connections establishing)
32:tps = 96535.523905 (including connections establishing)
48:tps = 92031.618360 (including connections establishing)
64:tps = 106813.631701 (including connections establishing)
And at scale factor 1000:
1:tps = 4980.338246 (including connections establishing)
8:tps = 33576.680072 (including connections establishing)
16:tps = 55618.677975 (including connections establishing)
32:tps = 73589.442746 (including connections establishing)
48:tps = 70987.026228 (including connections establishing)
Note sure why I am missing the 64-client results for that last set of
tests, but no matter.
Of course, we can't apply the second patch as it stands, because I
tested it on x86 and it loses. But it seems pretty clear we need to
do it at least for this architecture...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] cheaper snapshots redux
On Sat, Aug 27, 2011 at 1:38 AM, Gokulakannan Somasundaram gokul...@gmail.com wrote: First i respectfully disagree with you on the point of 80MB. I would say that its very rare that a small system( with 1 GB RAM ) might have a long running transaction sitting idle, while 10 million transactions are sitting idle. Should an optimization be left, for the sake of a very small system to achieve high enterprise workloads? With the design where you track commit-visbility sequence numbers instead of snapshots, you wouldn't need 10 million transactions that were all still running. You would just need a snapshot that had been sitting around while 10 million transactions completed meanwhile. That having been said, I don't necessarily think that design is doomed. I just think it's going to be trickier to get working than the design I'm now hacking on, and a bigger change from what we do now. If this doesn't pan out, I might try that one, or something else. Second, if we make use of the memory mapped files, why should we think, that all the 80MB of data will always reside in memory? Won't they get paged out by the operating system, when it is in need of memory? Or do you have some specific OS in mind? No, I don't think it will all be in memory - but that's part of the performance calculation. If you need to check on the status of an XID and find that you need to read a page of data in from disk, that's going to be many orders of magnitude slower than anything we do with s snapshot now. Now, if you gain enough elsewhere, it could still be a win, but I'm not going to just assume that. As I play with this, I'm coming around to the conclusion that, in point of fact, the thing that's hard about snapshots has a great deal more to do with memory than it does with CPU time. Sure, using the snapshot has to be cheap. But it already IS cheap. We don't need to fix that problem; we just need to not break it. What's not cheap is constructing the snapshot - principally because of ProcArrayLock, and secondarily because we're grovelling through fairly large amounts of shared memory to get all the XIDs we need. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] pg_restore --no-post-data and --post-data-only
On 08/27/2011 06:56 PM, Greg Sabino Mullane wrote: Once these new flags and the ability to custom format dump pg_dumpall is done, I'll have very little left to complain about with pg_dump :) It's off topic. But I think custom format would require a major mangling to be able to handle a complete cluster. This isn't just a simple matter of programming, IMNSHO. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
