Re: [HACKERS] foreign key locks, 2nd attempt
On Sat, Mar 17, 2012 at 10:45 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Here is v11. This version is mainly updated to add pg_upgrade support, as discussed. It also contains the README file that was posted earlier (plus wording fixes per Bruce), a couple of bug fixes, and some comment updates. The main thing we're waiting on are the performance tests to confirm the lack of regression. You are working on that, right? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Gsoc2012 Idea --- Social Network database schema
Hello, Here is something we'd like to have: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00650.php As we are quite busy and this issue hasn't a high priority, we haven't followed it until now :-( I'm only a Postgres user, not a hacker, so I don't have the knowledge to help on this nor to evaluate if this is might be a good Gssoc project. Just an idea for the case you are looking for another topic. best regards, Marc Mamin From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Qi Huang Sent: Samstag, 24. März 2012 05:20 To: cbbro...@gmail.com; kevin.gritt...@wicourts.gov Cc: pgsql-hackers@postgresql.org; and...@anarazel.de; alvhe...@commandprompt.com; neil.con...@gmail.com; dan...@heroku.com; j...@agliodbs.com Subject: Re: [HACKERS] Gsoc2012 Idea --- Social Network database schema Date: Thu, 22 Mar 2012 13:17:01 -0400 Subject: Re: [HACKERS] Gsoc2012 Idea --- Social Network database schema From: cbbro...@gmail.com To: kevin.gritt...@wicourts.gov CC: pgsql-hackers@postgresql.org On Thu, Mar 22, 2012 at 12:38 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, the standard syntax apparently aims to reduce the number of returned rows, which ORDER BY does not. Maybe you could do it with ORDER BY .. LIMIT, but the idea here I think is that we'd like to sample the table without reading all of it first, so that seems to miss the point. I think actually the traditional locution is more like WHERE random() constant where the constant is the fraction of the table you want. And yeah, the presumption is that you'd like it to not actually read every row. (Though unless the sampling density is quite a bit less than 1 row per page, it's not clear how much you're really going to win.) It's all going to depend on the use cases, which I don't think I've heard described very well yet. I've had to pick random rows from, for example, a table of disbursements to support a financial audit. In those cases it has been the sample size that mattered, and order didn't. One interesting twist there is that for some of these financial audits they wanted the probability of a row being selected to be proportional to the dollar amount of the disbursement. I don't think you can do this without a first pass across the whole data set. This one was commonly called Dollar Unit Sampling, though the terminology has gradually gotten internationalized. http://www.dummies.com/how-to/content/how-does-monetary-unit-sampling-work.html What the article doesn't mention is that some particularly large items might wind up covering multiple samples. In the example, they're looking for a sample every $3125 down the list. If there was a single transaction valued at $3, that (roughly) covers 10 of the desired samples. It isn't possible to do this without scanning across the entire table. If you want repeatability, you probably want to instantiate a copy of enough information to indicate the ordering chosen. That's probably something that needs to be captured as part of the work of the audit, so not only does it need to involve a pass across the data, it probably requires capturing a fair bit of data for posterity. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? The discussion till now has gone far beyond my understanding. Could anyone explain briefly what is the idea for now? The designing detail for me is still unfamiliar. I can only take time to understand while possible after being selected and put time on it to read relevant material. For now, I'm still curious why Neil's implementation is no longer working? The Postgres has been patched a lot, but the general idea behind Neil's implementation should still work, isn't it? Besides, whether this query is needed is still not decided . Seems this is another hard to decide point. Is it that this topic is still not so prepared for the Gsoc yet? If really so, I think I still have time to switch to other topics. Any suggestion? Thanks. Best Regards and Thanks Huang Qi Victor Computer Science of National University of Singapore
Re: [HACKERS] Regarding column reordering project for GSoc 2012
On 19 mar 2012, at 22.45, Merlin Moncure wrote: one pl/java based project that IMO is just screaming to be done is a pl/java based FDW (wrapping JDBC) that would approximately reproduce dblink...maybe with some extra features geared towards ETL type jobs like a row based callback for transformations in java. Personally I'd love a type 2 JDBC driver for PostgreSQL. It should be feasible as a summer project. It's somewhere deep down on my TODO list so I'd be happy to help. Cheers, Claes -- 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] Error trying to compile a simple C trigger
Il giorno mar, 20/03/2012 alle 11.16 +, Peter Geoghegan ha scritto: On 20 March 2012 10:53, Marco Nenciarini marco.nenciar...@2ndquadrant.it wrote: alert.c: In function ‘dbms_alert_defered_signal’: alert.c:839:33: error: dereferencing pointer to incomplete type make: *** [alert.o] Error 1 I've also tried the example at http://www.postgresql.org/docs/devel/static/trigger-example.html and the result is exactly the same. trigtest.c: In function ‘trigf’: trigtest.c:44:36: error: dereferencing pointer to incomplete type make: *** [trigtest.o] Error 1 I'd say this is an unintended consequence of a pgrminclude run. Try adding this: #include access/tupdesc.h It doesn't work. The error is stil the same. Regards, Marco -- Marco Nenciarini - System manager @ Devise.IT marco.nenciar...@devise.it | http://www.devise.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] Regarding column reordering project for GSoc 2012
On 20 mar 2012, at 13.08, Heikki Linnakangas wrote: On 20.03.2012 11:10, Claes Jakobsson wrote: Personally I'd love a type 2 JDBC driver for PostgreSQL. Why? listen/notify over SSL for example unless that's been fixed in the JDBC driver recently. And I'm sure there are other things in libpq that would be nice to have. As mainly a Perl dude which uses libpq via DBD::Pg I find it odd that the Java people doesn't do the same instead of reimplementing everything. Cheers, /Claes -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] how can i see the log..?
i want see the log of INSERT, SELECT, DELETE... within postgresql v9.1.3 i'm so sorry, my english level... how can i see that. transaction log?... or other thing...? -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-can-i-see-the-log-tp5583083p5583083.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Weak-memory specific problem in ResetLatch/WaitLatch (follow-up analysis)
Hi,
[...]
Placing a sync (i.e., the strongest Power barrier) accordingly would,
however,
still be insufficient for the second problem, as it would only fix the
reordering of read-read pairs by Worker 1 and the store atomicity issue from
Worker 0. But the writes on Worker 0 could still be reordered (problem
number
2). One possible fix consists of placing a sync between the two writes on
Worker
0, and an address dependency between the two reads on Worker 1. Clearly,
however, these are changes that cannot any longer be hidden behind the
ResetLatch/WaitLatch interface, but rather go in the code using these.
[...]
However, your example is enough unlike the actual code that the
conclusion you state following the word clearly isn't actually clear
to me. According to latch.h, the correct method of using a latch is
like this:
* for (;;)
* {
* ResetLatch();
* if (work to do)
* Do Stuff();
* WaitLatch();
* }
Meanwhile, anyone who is creating additional work to do should add the
work to the queue and then set the latch.
When writing the above statement, including the clearly, we were possibly too
much thinking of the above usage hint, which just uses ResetLatch and WaitLatch.
As you say, ...
So it seems to me that we could potentially fix this by inserting
barriers at the end of ResetLatch and at the beginning of SetLatch and
WaitLatch. Then the latch has to get reset before we check whether
there's work to do; and we've got to finish checking for work before
we again try to wait for the latch. Similarly, any work that was in
progress before SetLatch was called will be forced to be committed to
memory before SetLatch does anything else. Adding that many barriers
might not be very good for performance but it seems OK from a
correctness point of view, unless I am missing something, which is
definitely possible. I'd appreciate any thoughts you have on this, as
this is clearly subtle and tricky to get exactly right.
... placing another barrier in SetLatch could just do the trick. We will apply
our tools to actually prove this and come back with the conclusive answer.
Best,
Michael
pgpYu5lX5GiTq.pgp
Description: PGP signature
[HACKERS] pg_upgrade incorrectly equates pg_default and database tablespace
Hi, while working on a support case I stumbled upon a bug in pg_upgrade. Upgrade fails with No such file or directory when a database is moved to a non-default tablespace and contains a table that is moved to pg_default. The cause seems to be that the following test incorrectly equates empty spclocation with database tablespace: tblspace = PQgetvalue(res, relnum, i_spclocation); /* if no table tablespace, use the database tablespace */ if (strlen(tblspace) == 0) tblspace = dbinfo-db_tblspace; Patch to fix this is attached. Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index 36683fa..3914403 100644 --- a/contrib/pg_upgrade/info.c +++ b/contrib/pg_upgrade/info.c @@ -253,6 +253,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) char *nspname = NULL; char *relname = NULL; int i_spclocation, +i_spcoid, i_nspname, i_relname, i_oid, @@ -269,7 +270,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) snprintf(query, sizeof(query), SELECT c.oid, n.nspname, c.relname, - c.relfilenode, %s + c.relfilenode, t.oid AS spcoid, %s FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t @@ -306,6 +307,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) i_nspname = PQfnumber(res, nspname); i_relname = PQfnumber(res, relname); i_relfilenode = PQfnumber(res, relfilenode); + i_spcoid = PQfnumber(res, spcoid); i_spclocation = PQfnumber(res, spclocation); for (relnum = 0; relnum ntups; relnum++) @@ -325,7 +327,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) tblspace = PQgetvalue(res, relnum, i_spclocation); /* if no table tablespace, use the database tablespace */ - if (strlen(tblspace) == 0) + if (atooid(PQgetvalue(res, relnum, i_spcoid)) == InvalidOid) tblspace = dbinfo-db_tblspace; strlcpy(curr-tablespace, tblspace, sizeof(curr-tablespace)); } diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h index c1925cf..234ca99 100644 --- a/contrib/pg_upgrade/pg_upgrade.h +++ b/contrib/pg_upgrade/pg_upgrade.h @@ -109,7 +109,8 @@ typedef struct char relname[NAMEDATALEN]; /* relation name */ Oid reloid; /* relation oid */ Oid relfilenode; /* relation relfile node */ - char tablespace[MAXPGPATH]; /* relations tablespace path */ + /* relations tablespace path, empty for pg_default */ + char tablespace[MAXPGPATH]; } RelInfo; typedef struct -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] who's familiar with the GSOC application process
Someone has approached the JDBC list to do some work on the driver as a GSOC project. I need to know what is involved in mentoring and how to get the project approved Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Never convert n_distinct 2 values to a ratio when computing stats
This is a bit of a corner case in all honesty, but if you have a short
table (under 20 rows), the 10% heuristic used that decides whether
distinct values scale with the row count will result in rather odd
values for stadistinct in pg_statistic, such as '-0.2' or '-0.67',
rather than the expected '2'. Additionally, this can cause only one of
{t, f} to appear in the most common values array.
Does this actually affect query planning in any way? Probably not, but
it is extremely odd to look at pg_stats for these columns, and the
solution seems easy.
---
The only other minor changes included here were to make it clear when we were
comparing float values, so use 0.0 instead of 0.
Example stats output from the database I noticed this on:
archweb=# SELECT c.relname, a.attname, pg_stat_get_live_tuples(c.oid) AS
n_live_tup, stadistinct, stanullfrac, stawidth, stavalues1, stanumbers1 FROM
pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON
c.oid = a.attrelid AND a.attnum = s.staattnum LEFT JOIN pg_namespace n ON n.oid
= c.relnamespace JOIN pg_type t ON t.oid = a.atttypid WHERE NOT a.attisdropped
AND nspname = 'public' AND t.typname = 'bool' ORDER BY stadistinct, n_live_tup;
relname|attname| n_live_tup | stadistinct |
stanullfrac | stawidth | stavalues1 | stanumbers1
---+---++-+-+--++---
mirrors_mirrorprotocol| is_download | 3 | -0.67 |
0 |1 | {t}| {0.67}
arches| agnostic | 3 | -0.67 |
0 |1 | {f}| {0.67}
repos | staging | 10 |-0.2 |
0 |1 | {f,t} | {0.7,0.3}
repos | testing | 10 |-0.2 |
0 |1 | {f,t} | {0.7,0.3}
devel_pgpsignature| valid |264 | 1 |
0 |1 | {t}| {1}
packages_flagrequest | is_spam |415 | 1 |
0 |1 | {f}| {1}
donors| visible |716 | 1 |
0 |1 | {t}| {1}
auth_user | is_superuser | 95 | 2 |
0 |1 | {f,t} | {0.957895,0.0421053}
user_profiles | notify| 95 | 2 |
0 |1 | {t,f} | {0.957895,0.0421053}
auth_user | is_active | 95 | 2 |
0 |1 | {t,f} | {0.621053,0.378947}
auth_user | is_staff | 95 | 2 |
0 |1 | {f,t} | {0.873684,0.126316}
releng_iso| active|158 | 2 |
0 |1 | {f,t} | {0.89,0.106667}
mirrors_mirror| isos |180 | 2 |
0 |1 | {t,f} | {0.972678,0.0273224}
mirrors_mirror| active|180 | 2 |
0 |1 | {t,f} | {0.672131,0.327869}
mirrors_mirror| public|180 | 2 |
0 |1 | {t,f} | {0.978142,0.0218579}
mirrors_mirrorurl | has_ipv6 |379 | 2 |
0 |1 | {f,t} | {0.709763,0.290237}
mirrors_mirrorurl | has_ipv4 |379 | 2 |
0 |1 | {t}| {0.997361}
packages_flagrequest | is_legitimate |415 | 2 |
0 |1 | {t,f} | {0.992754,0.00724638}
packages_signoffspecification | enabled | 1130 | 2 |
0 |1 | {t,f} | {0.977578,0.0224215}
packages_signoffspecification | known_bad | 1130 | 2 |
0 |1 | {f,t} | {0.993722,0.00627803}
mirrors_mirrorlog | is_success| 12715 | 2 |
0 |1 | {t,f} | {0.953345,0.0466552}
package_depends | optional | 28592 | 2 |
0 |1 | {f,t} | {0.880322,0.119678}
package_files | is_directory | 225084 | 2 |
0 |1 | {f,t} | {0.829933,0.170067}
(23 rows)
src/backend/commands/analyze.c | 18 +-
1 file changed, 9 insertions(+), 9 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 9cd6e67..995ed9d 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -2110,7 +2110,7 @@ compute_minimal_stats(VacAttrStatsP stats,
* least 2 instances in the sample.
*/
if (track_cnt track_max
[HACKERS] PostgreSQL optimisations on Linux machines with more than 24 cores
Hello all ... again ! SHORT INTRODUCTION === Remember me? A couple of years ago (maybe 10) I created the PgAccess package for managing PostgreSQL! Since than I have done many things, all of them related to PostgreSQL, of course! Our company is managing the National Animal Tracking and Registering Database in Romania, a pretty big database on a 24 core Intel Xeon machine with 32 Gb RAM THE PROBLEM After many months of optimisation and fine tuning, getting the most out of our machine, I just found an article regarding Linux scalability on many cores, including an analysis of other different programs, including PostgreSQL: http://pdos.csail.mit.edu/papers/linux:osdi10.pdf This paper analyzes the scalability of seven system applications (Exim, memcached, Apache, PostgreSQL, gmake, Psearchy, and MapReduce) running on Linux on a 48-core computer. Except for gmake, all applications trigger scalability bottlenecks inside a recent Linux kernel. Using mostly standard parallel programming techniques— this paper introduces one new technique, sloppy counters—these bottlenecks can be removed from the kernel or avoided by changing the applications slightly. Modifying the kernel required in total 3002 lines of code changes. A speculative conclusion from this analysis is that there is no scalability reason to give up on traditional operating system organizations just yet. There are a couple of recommendations there for PostgreSQL, small changes in PostgreSQL source (ex. increasing the number of mutexes from 16 to 1024, row and table-level locking) other in Linux kernel (modified lseek) that seems to be effective for scaling beyond 24 cores. THE QUESTION = The article says that the tests were done using a PostgreSQL 8.3.9 version. Is the new 9.1.3 PostgreSQL using some of the patches recommended in that article that would enhance multi-core scalability? Thank you very much for your attention, Constantin Teodorescu
Re: [HACKERS] PostgreSQL optimisations on Linux machines with more than 24 cores
On Sun, Mar 25, 2012 at 7:14 AM, Constantin Teodorescu braila...@gmail.com wrote: This paper analyzes the scalability of seven system applications (Exim, memcached, Apache, PostgreSQL, gmake, Psearchy, and MapReduce) running on Linux on a 48-core computer. Except for gmake, all applications trigger scalability bottlenecks inside a recent Linux kernel. Using mostly standard parallel programming techniques— this paper introduces one new technique, sloppy counters—these bottlenecks can be removed from the kernel or avoided by changing the applications slightly. Modifying the kernel required in total 3002 lines of code changes. A speculative conclusion from this analysis is that there is no scalability reason to give up on traditional operating system organizations just yet. There are a couple of recommendations there for PostgreSQL, small changes in PostgreSQL source (ex. increasing the number of mutexes from 16 to 1024, row and table-level locking) other in Linux kernel (modified lseek) that seems to be effective for scaling beyond 24 cores. Linux 3.2 includes lseek modifications to improve scalability. PostgreSQL 9.2 will include significant lock manager changes; we did not adopt their proposal of just bumping up the number of lock manager locks, as that doesn't help in common cases, such as when lots of concurrent backends are accessing the same table, and it has other downsides, such as slowing down operations that must touch multiple lock manager partitions. Rather, we introduced a fast path for common cases that bypasses the main lock manager altogether; thus, traffic on the lock manager locks will be dramatically reduced in PostgreSQL 9.2. -- 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] [PATCH] Never convert n_distinct 2 values to a ratio when computing stats
On Sat, Mar 24, 2012 at 12:17 AM, Dan McGee d...@archlinux.org wrote:
This is a bit of a corner case in all honesty, but if you have a short
table (under 20 rows), the 10% heuristic used that decides whether
distinct values scale with the row count will result in rather odd
values for stadistinct in pg_statistic, such as '-0.2' or '-0.67',
rather than the expected '2'. Additionally, this can cause only one of
{t, f} to appear in the most common values array.
Does this actually affect query planning in any way? Probably not, but
it is extremely odd to look at pg_stats for these columns, and the
solution seems easy.
But the stats aren't there to be looked at, but rather to guide query
planning. If at execution time there are 100 rows in the table,
should we still assume that there are only 2 distinct values in the
table, or that it's gone up to about 50 distinct values? It's hard to
say, but there's no apparent reason to think that the number of
distinct values will scale up for a large table but not a small table.
The bit about maybe not getting both t and f as MCVs on a Boolean does
seem a little worrying, but I'm not sure whether it actually affects
query planning in a materially negative way. Can you demonstrate a
case where it matters?
--
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] Gsoc2012 Idea --- Social Network database schema
On Sun, Mar 25, 2012 at 6:11 AM, Marc Mamin m.ma...@intershop.de wrote: Hello, Here is something we'd like to have: http://archives.postgresql.org/pgsql-hackers/2012-01/msg00650.php As we are quite busy and this issue hasn't a high priority, we haven't followed it until now :-( I'm only a Postgres user, not a hacker, so I don't have the knowledge to help on this nor to evaluate if this is might be a good Gssoc project. Just an idea for the case you are looking for another topic. Good idea. If anyone want so pursue it, I'd strongly suggest building it as a contrib module rather than dedicated syntax, because I'm not sure there'd be any consensus on adding syntax for it to core. Actually, though, I wonder how much faster it would be than CREATE TABLE AS? Block-level copy should be faster than tuple-level copy, but I'm not sure whether it would be a lot faster or only slightly faster. -- 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] [PATCH] Never convert n_distinct 2 values to a ratio when computing stats
Robert Haas robertmh...@gmail.com writes: The bit about maybe not getting both t and f as MCVs on a Boolean does seem a little worrying, but I'm not sure whether it actually affects query planning in a materially negative way. Can you demonstrate a case where it matters? If we were trying to force that to happen it would be wrong anyway. Consider a column that contains *only* t, or at least has so few f's that f appears never or only once in the selected sample. (IIRC there is a clamp that prevents selecting anything as an MCV unless it appears at least twice in the sample.) Like Robert, I'm not convinced whether or not this is a reasonable change, but arguing for it on the basis of boolean columns doesn't seem very sound. 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
[HACKERS] occasional startup failures
Every so often buildfarm animals (nightjar and raven recently, for example) report failures on starting up the postmaster. It appears that these failures are due to the postmaster not creating the pid file within 5 seconds, and so the logic in commit 0bae3bc9be4a025df089f0a0c2f547fa538a97bc kicks in. Unfortunately, when this happens the postmaster has in fact sometimes started up, and the end result is that subsequent buildfarm runs will fail when they detect that there is already a postmaster listening on the port, and without manual intervention to kill the rogue postmaster this continues endlessly. I can probably add some logic to the buildfarm script to try to detect this condition and kill an errant postmaster so subsequent runs don't get affected, but that seems to be avoiding a problem rather than fixing it. I'm not sure what we can do to improve it otherwise, though. Thoughts? 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
Re: [HACKERS] query cache
Thanks.. I'll keep those issues in mind. On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii is...@postgresql.org wrote: Well, you'd have to start by demonstrating the benefit of it. The advantage of query caches in proxies and clients is well-known, because you can offload some of the work of the database onto other servers, this increasing capacity. Adding a query cache to the database server would require the query identity recognition of the cache to be far cheaper (as in 10X cheaper) than planning and running the query, which seems unlikely at best. I figured I'd create the md5 digest of the sourceText of a query, and then look that up in a hash. I don't think that will be very expensive. I'll have another hash to keep track of which queries are dependent on which relations, so that when a relation is changed somehow (and committed), the query is then invalidated and removed from the query hash. From the experience of implementing query cache in pgool-II there are some suggestions: - A query result cache should not be created if the transaction including the SELECT is not committed. - Since a transaction could have many SELECTs, you need to keep those query results somewhere in a temporary storage. You could either discard or register them to the query cache storage depending on the transaction's fate, either aborted or committed. - If a SELECT has non-immutable functions, then the query result should not be cached. - If a SELECT uses temporary tables, then the query result should not be cached. - If a SELECT uses unlogged tables, then the query result should not be cached because their data could vanish after crash recovery. Of course this is only applied if you plan to use cache storage which does not survive after crash. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Re: [HACKERS] occasional startup failures
Andrew Dunstan and...@dunslane.net writes: Every so often buildfarm animals (nightjar and raven recently, for example) report failures on starting up the postmaster. It appears that these failures are due to the postmaster not creating the pid file within 5 seconds, and so the logic in commit 0bae3bc9be4a025df089f0a0c2f547fa538a97bc kicks in. Unfortunately, when this happens the postmaster has in fact sometimes started up, and the end result is that subsequent buildfarm runs will fail when they detect that there is already a postmaster listening on the port, and without manual intervention to kill the rogue postmaster this continues endlessly. I can probably add some logic to the buildfarm script to try to detect this condition and kill an errant postmaster so subsequent runs don't get affected, but that seems to be avoiding a problem rather than fixing it. I'm not sure what we can do to improve it otherwise, though. Yeah, this has been discussed before. IMO the only real fix is to arrange things so that the postmaster process is an immediate child of pg_ctl, allowing pg_ctl to know its PID directly and not have to rely on the pidfile appearing before it can detect whether the postmaster is still alive. Then there is no need for a guesstimated timeout. That means not using system() anymore, but rather fork/exec, which mainly implies having to write our own code for stdio redirection. So that's certainly doable if a bit tedious. I have no idea about the Windows side of it though. 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] occasional startup failures
On Sun, Mar 25, 2012 at 18:59, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Every so often buildfarm animals (nightjar and raven recently, for example) report failures on starting up the postmaster. It appears that these failures are due to the postmaster not creating the pid file within 5 seconds, and so the logic in commit 0bae3bc9be4a025df089f0a0c2f547fa538a97bc kicks in. Unfortunately, when this happens the postmaster has in fact sometimes started up, and the end result is that subsequent buildfarm runs will fail when they detect that there is already a postmaster listening on the port, and without manual intervention to kill the rogue postmaster this continues endlessly. I can probably add some logic to the buildfarm script to try to detect this condition and kill an errant postmaster so subsequent runs don't get affected, but that seems to be avoiding a problem rather than fixing it. I'm not sure what we can do to improve it otherwise, though. Yeah, this has been discussed before. IMO the only real fix is to arrange things so that the postmaster process is an immediate child of pg_ctl, allowing pg_ctl to know its PID directly and not have to rely on the pidfile appearing before it can detect whether the postmaster is still alive. Then there is no need for a guesstimated timeout. That means not using system() anymore, but rather fork/exec, which mainly implies having to write our own code for stdio redirection. So that's certainly doable if a bit tedious. I have no idea about the Windows side of it though. We already do something like this on Win32 - at least one reason being dealing with restricted tokens. Right now we just close the handles to the child, but we could easily keep those around for doing this type of detection. -- 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] Command Triggers patch v18
On Friday, March 23, 2012 04:32:02 PM Dimitri Fontaine wrote: I would like to get back on code level review now if at all possible, and I would integrate your suggestions here into the next patch revision if another one is needed. Ok, I will give it another go. Btw I just wanted to alert you to being careful when checking in the expect files ;) NOTICE: snitch: BEFORE any DROP TRIGGER -ERROR: unexpected name list length (3) +NOTICE: snitch: BEFORE DROP TRIGGER NULL foo_trigger +NOTICE: snitch: AFTER any DROP TRIGGER create conversion test for 'utf8' to 'sjis' from utf8_to_sjis; j you had an apparerently un-noticed error in there ;) 1. if (!HeapTupleIsValid(tup)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), errmsg(command trigger \%s\ does not exist, skipping, trigname))); The skipping part looks like a copy/pasto... 2. In PLy_exec_command_trigger youre doing a PG_TRY() which looks pointless in the current incarnation. Did you intend to add something in the catch? I think without doing a decref of pltdata both in the sucess and the failure path youre leaking memory. 3. In plpython: Why do you pass objectId/pltobjectname/... as NULL instead of None? Using a string for it seems like a bad from of in-band signalling to me. 4. Not sure whether InitCommandContext is the best place to suppress command trigger usage for some commands. That seems rather unintuitive to me. But perhaps the implementation-ease is big enough... Thats everything new I found... Not bad I think. After this somebody else should take a look at I think (commiter or not). The only point yet to address from last round from Andres is about the API around CommandFiresTrigger() and the Memory Context we use here. We're missing an explicit Reset call, and to be able to have we need to have a more complex API, because of the way RemoveObjects() and RemoveRelations() work. We would need to add no-reset APIs and an entry point to manually reset the memory context, which currently gets disposed at the same time as its parent context, the current one that's been setup before entering standard_ProcessUtility(). Not sure if youre expecting further input from me about that? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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 can i see the log..?
lekon wrote: i want see the log of INSERT, SELECT, DELETE... within postgresql v9.1.3 The pgsql-hackers list is for discussions about development of the PostgreSQL datatbase system; discussions about how to use it should go to another list. The particular question would be appropriate for pgsql-general or pgsql-admin. See this page descriptions of the various listings to help decide where to post future questions: http://www.postgresql.org/community/lists/ Regarding the question itself, see the log_statement configuration setting: http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT -Kevin -- 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] who's familiar with the GSOC application process
On 3/23/12 1:58 PM, Dave Cramer wrote: Someone has approached the JDBC list to do some work on the driver as a GSOC project. I need to know what is involved in mentoring and how to get the project approved I've approved you as a mentor. I suggest also joining the pgsql-students mailing list. You should get the student to submit an application on Monday. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] checkpoint patches
On 3/23/12 7:38 AM, Robert Haas wrote: And here are the latency results for 95th-100th percentile with checkpoint_timeout=16min. ckpt.master.13: 1703, 1830, 2166, 17953, 192434, 43946669 ckpt.master.14: 1728, 1858, 2169, 15596, 187943, 9619191 ckpt.master.15: 1700, 1835, 2189, 22181, 206445, 8212125 The picture looks similar here. Increasing checkpoint_timeout isn't *quite* as good as spreading out the fsyncs, but it's pretty darn close. For example, looking at the median of the three 98th percentile numbers for each configuration, the patch bought us a 28% improvement in 98th percentile latency. But increasing checkpoint_timeout by a minute bought us a 15% improvement in 98th percentile latency. So it's still not clear to me that the patch is doing anything on this test that you couldn't get just by increasing checkpoint_timeout by a few more minutes. Granted, it lets you keep your inter-checkpoint interval slightly smaller, but that's not that exciting. That having been said, I don't have a whole lot of trouble believing that there are other cases where this is more worthwhile. I wouldn't be too quick to dismiss increasing checkpoint frequency (ie: decreasing checkpoint_timeout). On a high-value production system you're going to care quite a bit about recovery time. I certainly wouldn't want to run our systems with checkpoint_timeout='15 min' if I could avoid it. Another $0.02: I don't recall the community using pg_bench much at all to measure latency... I believe it's something fairly new. I point this out because I believe there are differences in analysis that you need to do for TPS vs latency. I think Robert's graphs support my argument; the numeric X-percentile data might not look terribly good, but reducing peak latency from 100ms to 60ms could be a really big deal on a lot of systems. My intuition is that one or both of these patches actually would be valuable in the real world; it would be a shame to throw them out because we're not sure how to performance test them... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] COPY / extend ExclusiveLock
On 3/22/12 2:13 PM, Stephen Frost wrote: Greetings, I've recently become a bit annoyed and frustrated looking at this in top: 23296 postgres 20 0 3341m 304m 299m S 12 0.9 1:50.02 postgres: sfrost gis [local] COPY waiting 24362 postgres 20 0 3353m 298m 285m D 12 0.9 1:24.99 postgres: sfrost gis [local] COPY 24429 postgres 20 0 3340m 251m 247m S 11 0.8 1:13.79 postgres: sfrost gis [local] COPY waiting 24138 postgres 20 0 3341m 249m 244m S 10 0.8 1:28.09 postgres: sfrost gis [local] COPY waiting 24153 postgres 20 0 3340m 246m 241m S 10 0.8 1:24.44 postgres: sfrost gis [local] COPY waiting 24166 postgres 20 0 3341m 318m 313m S 10 1.0 1:40.52 postgres: sfrost gis [local] COPY waiting 24271 postgres 20 0 3340m 288m 283m S 10 0.9 1:34.12 postgres: sfrost gis [local] COPY waiting 24528 postgres 20 0 3341m 290m 285m S 10 0.9 1:21.23 postgres: sfrost gis [local] COPY waiting 24540 postgres 20 0 3340m 241m 236m S 10 0.7 1:15.91 postgres: sfrost gis [local] COPY waiting Has anyone been working on or considering how to improve the logic around doing extends on relations to perhaps make larger extensions for larger tables? Or make larger extensions when tables are growing very quickly? I haven't looked at the code, but I'm guessing we extend relations when they're full (that part makes sense..), but we extend them an itty-bitty bit at a time, which very quickly ends up being not fast enough for the processes that want to get data into the table. My gut feeling is that we could very easily and quickly improve this situation by having a way to make larger extensions, and then using that method when we detect that a table is growing very quickly. I know that there's been discussion around this. Way back in the day we extended relations one page at a time. I don't remember if that was changed or not. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] COPY / extend ExclusiveLock
Jim Nasby j...@nasby.net writes: On 3/22/12 2:13 PM, Stephen Frost wrote: Has anyone been working on or considering how to improve the logic around doing extends on relations to perhaps make larger extensions for larger tables? Or make larger extensions when tables are growing very quickly? I know that there's been discussion around this. Way back in the day we extended relations one page at a time. I don't remember if that was changed or not. No, it's still on the to-do list. One issue is that you probably don't want individual insertion queries taking big response-time hits to do large extensions themselves --- so somehow the bulk of the work needs to be pushed to some background process. 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] patch for parallel pg_dump
On Fri, Mar 23, 2012 at 11:11 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Are you going to provide a rebased version? Rebased version attached, this patch also includes Robert's earlier suggestions. parallel_pg_dump_5.diff.gz Description: GNU Zip compressed data -- 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
On Sat, 2012-03-17 at 17:48 +0100, Cédric Villemain wrote: I agree with Andres. I believe we should use sync_file_range (_before?) with linux. And we can use posix_fadvise_dontneed on other kernels. OK, updated patch attached. sync_file_range() is preferred, posix_fadvise() is a fallback. Regards, Jeff Davis initdb-fsync-20120325.patch.gz Description: GNU Zip compressed data -- 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] Command Triggers, v16
On Friday, March 16, 2012 10:40:46 AM Dimitri Fontaine wrote: This will have the effect of calling triggers outside of alphabetic order. I don't think thats a good idea even if one part is ANY and the other a specific command. I don't think there is any reason anymore to separate the two? The only callsite seems to look like: The idea is to have a predictable ordering of command triggers. The code changed in the patch v16 (you pasted code from git in between v15 and v16, I cleaned it up) and is now easier to read: case CMD_TRIGGER_FIRED_BEFORE: whenstr = BEFORE; procs[0] = cmd-before_any; procs[1] = cmd-before; break; case CMD_TRIGGER_FIRED_AFTER: whenstr = AFTER; procs[0] = cmd-after; procs[1] = cmd-after_any; break; So it's BEFORE ANY then BEFORE command then AFTER command then AFTER ANY. That's an arbitrary I made and we can easily reconsider. Triggers are called in alphabetical order in each “slot” here. In my mind it makes sense to have ANY triggers around the specific triggers, but it's hard to explain why that feels better. I still think this would be a mistake. I don't have a hard time imagining usecases where a specific trigger should be called before or after an ANY trigger because e.g. it wants to return a more specific error or doesn't want to check all preconditions already done by the ANY trigger... All that would be precluded by enforcing a strict ordering between ANY and specific triggers. I don't see a use-case that would benefit from the current behaviour... Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
