Re: [HACKERS] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 04:59, Andrew Dunstan and...@dunslane.net wrote: Also, couldn't we just set up the cvsserver on its own VM with a limited amount of disk space, and not worry too much about any DOS threat? If somebody does do this, block them and reinitialize that server. We could do that, but that could end up fighting a losing battle in case some bot hits it. I don't like deploying something with a known issue on it, sandboxed or not. Thinking about this some more, how about we do non-anonymous CVS over SSH access to the git-cvsserver for the few buildfarm members that can't currently handle using git (e.g. spoonbill)? Well, if we do that centrally, we are back to a dedicated VM (hint: we're most certainly not adding non-personal no-password accounts to one of the VMs used for critical services - it's bad enough we have Bruce's account there :P). I assume most buildfarm clients are off static IPs (at least as seen from the servers - they may be behind a NAT device, but that one having static out)? If so, it seems simply easier to use pserver... I'm not sure if that would handle other requirements, such as Peter's, but I hope the residual requirements for CVS support will be pretty rare. Just to be sure - do we have any other requirements for CVS *beyond* buildfarm and NLS that we're not thinking of here? -- 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] top-level DML under CTEs
2010/9/23 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi: On 2010-09-17 4:48 AM, Hitoshi Harada wrote: 2010/9/15 Hitoshi Haradaumi.tan...@gmail.com: Well, I didn't know it is allowed. That would look like the way to go. I made changes to the previous version, so that it avoids to resolve CTE name duplication. This patch still doesn't address the issue Tom raised here: http://archives.postgresql.org/pgsql-hackers/2010-09/msg00753.php For WITH .. INSERT .. WITH .. SELECT ..; this patch works OK, but not so much for VALUES: =# CREATE RULE barrule AS ON UPDATE TO bar DO INSTEAD -# WITH RECURSIVE t AS (SELECT -1) -# INSERT INTO bar -# WITH t AS (SELECT 1) -# VALUES((SELECT * FROM t)); CREATE RULE =# \d bar Table public.bar Column | Type | Modifiers +-+--- a | integer | Rules: barrule AS ON UPDATE TO bar DO INSTEAD WITH RECURSIVE t AS ( SELECT 1 ), t AS ( SELECT (-1) ) INSERT INTO bar (a) WITH RECURSIVE t AS ( SELECT 1 ), t AS ( SELECT (-1) ) VALUES (( SELECT t.?column? FROM t)) I ran the sql and recognized what is wrong. In VALUES case, the WITH table t is copied in one list and shown up in the both of INSERT-level WITH and SELECT-level WITH. Since the transformation of WITH clause to cheat postgres is in the parser stage currently, I wonder if this should be done in the rewriter or the planner stage. Thanks for the report. Next time, please point the clear problem in English aside the sample. Regards, -- 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] Configuring synchronous replication
On Mon, 2010-09-20 at 18:24 -0400, Robert Haas wrote: I feel like that's really nice and simple. There are already 5 separate places to configure to make streaming rep work in a 2 node cluster (master.pg_hba.conf, master.postgresql.conf, standby.postgresql.conf, standby.recovery.conf, password file/ssh key). I haven't heard anyone say we would be removing controls from those existing areas, so it isn't clear to me how adding a 6th place will make things nice and simple. Put simply, Standby registration is not required for most use cases. If some people want it, I'm happy that it can be optional. Personally, I want to make very sure that any behaviour that involves waiting around indefinitely can be turned off and should be off by default. ISTM very simple to arrange things so you can set parameters on the master OR on the standby, whichever is most convenient or desirable. Passing parameters around at handshake is pretty trivial. I do also understand that some parameters *must* be set in certain locations to gain certain advantages. Those can be documented. I would be happier if we could separate the *list* of control parameters we need from the issue of *where* we set those parameters. I would be even happier if we could agree on the top 3-5 parameters so we can implement those first. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] Easy way to verify gitignore files?
Hi, On Thursday 23 September 2010 02:54:19 Tom Lane wrote: Is there any automated sanity check that we can run to find this sort of problem? I suspect that we probably have got some errors in the .gitignore files, particularly in the back branches, and it would be nice to find them now before they get in the way of normal development. git clean -nx shows you all ignored files that are not checked if thats what you want... Andres -- 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: Triggers on VIEWs
On 23 September 2010 00:26, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
On 2010-09-23 1:16 AM, Bernd Helmle wrote:
INSERT INTO vfoo VALUES('helmle', 2) RETURNING *;
text | id
+
helmle | 2
(1 row)
SELECT * FROM vfoo;
text | id
---+
bernd | 2
(1 row)
This is solvable by a properly designed trigger function, but maybe we
need
to do something about this?
I really don't think we should limit what people are allowed to do in the
trigger function.
Besides, even if the RETURNING clause returned 'bernd' in the above case, I
think it would be even *more* surprising. The trigger function explicitly
returns NEW which has 'helmle' as the first field.
Yes, I agree. To me this is the least surprising behaviour. I think a
more common case would be where the trigger computed a value (such as
the 'last updated' example). The executor doesn't have any kind of a
handle on the row inserted by the trigger, so it has to rely on the
function return value to support RETURNING.
I can confirm the latest Oracle (11g R2 Enterprise Edition) does not
support RETURNING INTO with INSTEAD OF triggers (although it does work
with its auto-updatable views), presumably because it's triggers don't
return values, but I think it would be a shame for us to not support
it.
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] snapshot generation broken
On ons, 2010-09-22 at 12:29 +0300, Peter Eisentraut wrote: On ons, 2010-09-22 at 10:33 +0200, Stefan Kaltenbrunner wrote: It seems that the git move has broken the generation of the automatic snapshot tarballs - has anybody yet looked into what it would take to move those to fetching from git? Depends on what's broken about it, but I notice that the developer docs and the NLS builds are also not updating. Perhaps something wrong with the anoncvs service. Developer docs are now building again. -- 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] Per-column collation, work in progress
Hello I am playing with your patch now. I found a few issues: 1. It's doesn't work with SQL 92 rules for sortby list. I can understand so explicit COLLATE using doesn't work, but the implicit using doesn't work too: CREATE TABLE foo(a text, b text COLLATE cs_CZ.UTF8) SELECT * FROM foo ORDER BY 1 -- produce wrong order 2. Why default encoding for collate is static? There are latin2 for czech, cs_CZ and cs_CZ.iso88592. So any user with UTF8 has to write encoding explicitly. But the more used and preferred encoding is UTF8 now. I am thinking so cs_CZ on utf8 database should mean cs_CS.UTF8. 3. postgres=# select to_char(current_date,'tmday') collate cs_CZ.utf8; to_char ── thursday -- bad result (1 row) 4. is somewhere ToDo for collation implementation? 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? like you cannot to use a different encoding. This collate is in pg_collates table. -- patch was applied cleanly and works in very well. Thank you. Regards Pavel Stehule 2010/9/15 Peter Eisentraut pete...@gmx.net: Following up on my previous patch [0], here is a fairly complete implementation of this feature. The general description and implementation outline of the previous message still apply. This patch contains documentation and regression tests, which can serve as further explanations. As this patch touches pretty much everything in the system, there are probably countless bugs and bogosities, some of which I have marked with FIXME, TODO, etc. But all the functionality is basically there, so it's time someone else gives this a serious examination. Note: As previously, regression tests only work with make check MULTIBYTE=UTF8 and the feature overall only works on Linux/glibc. [0] http://archives.postgresql.org/message-id/1279045531.32647.14.ca...@vanquo.pezone.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Per-column collation, work in progress
On Thu, Sep 23, 2010 at 5:12 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 3. postgres=# select to_char(current_date,'tmday') collate cs_CZ.utf8; to_char ── thursday -- bad result (1 row) COLLATE means collation rather than locale, no? 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? I hope Postgres automatically detects the omitted encoding because it knows the database encoding is UTF8. -- Itagaki Takahiro -- 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] Per-column collation, work in progress
2010/9/23 Itagaki Takahiro itagaki.takah...@gmail.com: On Thu, Sep 23, 2010 at 5:12 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 3. postgres=# select to_char(current_date,'tmday') collate cs_CZ.utf8; to_char ── thursday -- bad result (1 row) COLLATE means collation rather than locale, no? ok. 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? I hope Postgres automatically detects the omitted encoding because it knows the database encoding is UTF8. I know what this issue means, but it needs some detail or hint I think Regards Pavel -- Itagaki Takahiro -- 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] Configuring synchronous replication
On 23/09/10 11:34, Csaba Nagy wrote: In the meantime our DBs are not able to keep in sync via WAL replication, that would need some kind of parallel WAL restore on the slave I guess, or I'm not able to configure it properly - in any case now we use slony which is working. It would be interesting to debug that case a bit more. Was bottlenecked by CPU or I/O, or network capacity perhaps? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Per-column collation, work in progress
On tor, 2010-09-23 at 10:12 +0200, Pavel Stehule wrote: 1. It's doesn't work with SQL 92 rules for sortby list. I can understand so explicit COLLATE using doesn't work, but the implicit using doesn't work too: CREATE TABLE foo(a text, b text COLLATE cs_CZ.UTF8) SELECT * FROM foo ORDER BY 1 -- produce wrong order I can't reproduce that. Please provide more details. 2. Why default encoding for collate is static? There are latin2 for czech, cs_CZ and cs_CZ.iso88592. So any user with UTF8 has to write encoding explicitly. But the more used and preferred encoding is UTF8 now. I am thinking so cs_CZ on utf8 database should mean cs_CS.UTF8. That's tweakable. One idea I had is to strip the .utf8 suffix from locale names when populating the pg_collation catalog, or create both versions. I agree that the current way is a bit cumbersome. 3. postgres=# select to_char(current_date,'tmday') collate cs_CZ.utf8; to_char ── thursday -- bad result (1 row) As was already pointed out, collation only covers lc_collate and lc_ctype. (It could cover other things, for example an application to the money type was briefly discussed, but that's outside the current mandate.) As a point of order, what you wrote above attaches a collation to the result of the function call. To get the collation to apply to the function call itself, you have to put the collate clause on one of the arguments, e.g., select to_char(current_date,'tmday' collate cs_CZ.utf8); 4. is somewhere ToDo for collation implementation? At the moment it's mostly in the source code. I have a list of notes locally that I can clean up and put in the wiki once we agree on the general direction. 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? like you cannot to use a different encoding. This collate is in pg_collates table. That can surely be polished. -- 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] Per-column collation, work in progress
On tor, 2010-09-23 at 17:29 +0900, Itagaki Takahiro wrote: On Thu, Sep 23, 2010 at 5:12 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? I hope Postgres automatically detects the omitted encoding because it knows the database encoding is UTF8. I would rather not build too many expectations into this yet. The collation names are chosen by the user, the locale names are from the operating system. There is not necessarily a correspondence. The best fix is probably what I described earlier, populate the pg_collation table with the .utf8 suffix stripped. -- 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] top-level DML under CTEs
On 2010-09-23 9:12 AM +0300, Hitoshi Harada wrote: Thanks for the report. Next time, please point the clear problem in English aside the sample. I apologize. The problem was exactly the one pointed out in the email I referred to, so I assumed that further explanation was not necessary. I will try to be more clear in the future. Regards, Marko Tiikkaja -- 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] Git cvsserver serious issue
On 09/23/2010 02:09 AM, Magnus Hagander wrote: On Thu, Sep 23, 2010 at 04:59, Andrew Dunstanand...@dunslane.net wrote: Also, couldn't we just set up the cvsserver on its own VM with a limited amount of disk space, and not worry too much about any DOS threat? If somebody does do this, block them and reinitialize that server. We could do that, but that could end up fighting a losing battle in case some bot hits it. I don't like deploying something with a known issue on it, sandboxed or not. Thinking about this some more, how about we do non-anonymous CVS over SSH access to the git-cvsserver for the few buildfarm members that can't currently handle using git (e.g. spoonbill)? Well, if we do that centrally, we are back to a dedicated VM (hint: we're most certainly not adding non-personal no-password accounts to one of the VMs used for critical services - it's bad enough we have Bruce's account there :P). I assume most buildfarm clients are off static IPs (at least as seen from the servers - they may be behind a NAT device, but that one having static out)? If so, it seems simply easier to use pserver... Yes, I think we should have a VM. Is that so hard to do in these days of Xen etc? I'm surprised we can't run up a VM pretty much at the drop of a hat. I was suggesting that the accounts would be protected using ssh keys. Password and IP address protection seem pretty weak to me. Passwords can be sniffed or attacked using brute force. IP addresses can be spoofed. But you're the SA, not me. 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] Standby registration
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, that situation can arise if there's a network glitch which leads the standby to disconnect, but the master still considers the connection as alive. When the standby reconnects, the master will see two simultaneous connections from the same standby. In that scenario, you clearly want to disconnect the old connetion in favor of the new one. Is there a scenario where you'd want to keep the old connection instead and refuse the new one? Protection against spoofing? If connecting with the right IP is all it takes… Regards, -- dim -- 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] Standby registration
On 23/09/10 12:32, Dimitri Fontaine wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: Hmm, that situation can arise if there's a network glitch which leads the standby to disconnect, but the master still considers the connection as alive. When the standby reconnects, the master will see two simultaneous connections from the same standby. In that scenario, you clearly want to disconnect the old connetion in favor of the new one. Is there a scenario where you'd want to keep the old connection instead and refuse the new one? Protection against spoofing? If connecting with the right IP is all it takes… You also need to authenticate with a valid username and password, of course. As the patch stands, that needs to be a superuser, but we should aim for smarter authorization than that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] wip: functions median and percentile
Hello 2010/9/22 Hitoshi Harada umi.tan...@gmail.com: 2010/9/22 Pavel Stehule pavel.steh...@gmail.com: Hello I found probably hard problem in cooperation with window functions :( tuplesort_begin_datum creates child context inside aggcontext. This context is used for tuplestore. But when this function is called from WindowAgg_Aggregates context - someone drops all child context every iteration, and then tuplestore state is invalid. For this moment we can block using a median function as window function, but it should be solved better - if it is possible - I don't see inside window function implementation. Does it happen when the window frame starts from not UNBOUNDED PRECEDING? In those cases, nodeWindowAgg tries to discard all aggregate contexts and to initialize the aggregate state. AFAIK the memory context is brand-new although it was reset and its children deleted, so if the function is well-formed and initializes its state on NULL input, it doesn't cause a problem. maybe I was confused. I found a other possible problems. The problem with median function is probably inside a final function implementation. Actually we request possibility of repetitive call of final function. But final function call tuplesort_end function and tuplesort_performsort. These function changes a state of tuplesort. The most basic question is who has to call tuplesort_end function and when? Regards Pavel Stehule Regards, -- 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] Standby registration
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Having mulled through all the recent discussions on synchronous replication, ISTM there is pretty wide consensus that having a registry of all standbys in the master is a good idea. Even those who don't think it's *necessary* for synchronous replication seem to agree that it's nevertheless a pretty intuitive way to configure it. And it has some benefits even if we never get synchronous replication. Yeah it's nice to have, but I disagree with it being a nice way to configure it. I still think that in the long run it's more hassle than a distributed setup to maintain. The consensus seems to be use a configuration file called standby.conf. Let's use the ini file format for now [1]. What about automatic registration of standbys? That's not going to fly with the unique global configuration file idea, but that's good news. Automatic registration is a good answer to both your points A) monitoring and C) wal_keep_segments, but needs some more thinking wrt security and authentication. What about having a new GRANT privilege for replication, so that any standby can connect with a non-superuser role as soon as the master's setup GRANTS replication to the role? You still need HBA setup to be accepting the slave, too, of course. Regards, -- dim -- 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 11:27, Andrew Dunstan and...@dunslane.net wrote: On 09/23/2010 02:09 AM, Magnus Hagander wrote: On Thu, Sep 23, 2010 at 04:59, Andrew Dunstanand...@dunslane.net wrote: Also, couldn't we just set up the cvsserver on its own VM with a limited amount of disk space, and not worry too much about any DOS threat? If somebody does do this, block them and reinitialize that server. We could do that, but that could end up fighting a losing battle in case some bot hits it. I don't like deploying something with a known issue on it, sandboxed or not. Thinking about this some more, how about we do non-anonymous CVS over SSH access to the git-cvsserver for the few buildfarm members that can't currently handle using git (e.g. spoonbill)? Well, if we do that centrally, we are back to a dedicated VM (hint: we're most certainly not adding non-personal no-password accounts to one of the VMs used for critical services - it's bad enough we have Bruce's account there :P). I assume most buildfarm clients are off static IPs (at least as seen from the servers - they may be behind a NAT device, but that one having static out)? If so, it seems simply easier to use pserver... Yes, I think we should have a VM. Is that so hard to do in these days of Xen etc? I'm surprised we can't run up a VM pretty much at the drop of a hat. In the new infrastructure, it is. The main resource that's limited really is IP addresses ;) I was suggesting that the accounts would be protected using ssh keys. Password and IP address protection seem pretty weak to me. Passwords can be sniffed or attacked using brute force. IP addresses can be spoofed. But you're the SA, not me. I prefer keys. But I don't want those users on the same VM as important services, because passphrase-less keys are a lot more likely to be compromised than the keys of say a regular committer... -- 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] Per-column collation, work in progress
2010/9/23 Peter Eisentraut pete...@gmx.net: On tor, 2010-09-23 at 10:12 +0200, Pavel Stehule wrote: 1. It's doesn't work with SQL 92 rules for sortby list. I can understand so explicit COLLATE using doesn't work, but the implicit using doesn't work too: CREATE TABLE foo(a text, b text COLLATE cs_CZ.UTF8) SELECT * FROM foo ORDER BY 1 -- produce wrong order I can't reproduce that. Please provide more details. sorry, it is ok - I was confused 2. Why default encoding for collate is static? There are latin2 for czech, cs_CZ and cs_CZ.iso88592. So any user with UTF8 has to write encoding explicitly. But the more used and preferred encoding is UTF8 now. I am thinking so cs_CZ on utf8 database should mean cs_CS.UTF8. That's tweakable. One idea I had is to strip the .utf8 suffix from locale names when populating the pg_collation catalog, or create both versions. I agree that the current way is a bit cumbersome. yes. now almost all databases are in utf8 3. postgres=# select to_char(current_date,'tmday') collate cs_CZ.utf8; to_char ── thursday -- bad result (1 row) As was already pointed out, collation only covers lc_collate and lc_ctype. (It could cover other things, for example an application to the money type was briefly discussed, but that's outside the current mandate.) ook As a point of order, what you wrote above attaches a collation to the result of the function call. To get the collation to apply to the function call itself, you have to put the collate clause on one of the arguments, e.g., select to_char(current_date,'tmday' collate cs_CZ.utf8); I am thinking, collates can be used for this purpose too. I see some impacts - this syntax changes a stable function to immutable and it cannot be simple to solve. 4. is somewhere ToDo for collation implementation? At the moment it's mostly in the source code. I have a list of notes locally that I can clean up and put in the wiki once we agree on the general direction. 5. postgres=# create table xy(a text, b text collate cs_CZ); ERROR: collation cs_CZ for current database encoding UTF8 does not exist can be there some more friendly message or hint ? like you cannot to use a different encoding. This collate is in pg_collates table. That can surely be polished. Regards Pavel Stehule -- 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] ask for review of MERGE
On 23 September 2010 11:31, Boxuan Zhai bxzhai2...@gmail.com wrote: Dear All, I have just generate a new patch of MERGE command. One main change in this edition is the removal of RASIE ERROR action from MEREG, because its semantics is not well defined yet. I also rewrote the regress test file merge.sql, to make it consistent with the examples I used in my wiki page. Some little (error and warning) bugs are fixed. In this patch, all the main features of MERGE (sublinks, explain, rule and trigger, inheritance) are not changed. And so is the DO NOTHING action. I do wish the MERGE command can be added into psql 9.1. And I wonder what improvement should be made on current edition. Could you please have a review on this patch, if you have time and interest? Your feedback will be highly appreciated. Thanks Yours Boxuan A few corrections: in src/backend/executor/nodeModifyTable.c: s/excute/execute/ s/orignial/original/ in src/backend/optimizer/plan/planner.c s/expreesions/expressions/ s/So,we/So, we/ s/comand/command/ s/fileds/fields/ in src/backend/optimizer/prep/preptlist.c: s/aggresive/aggressive/ in src/backend/optimizer/util/var.c s/targe/target/ -- this appears twice s/sourse/source/ in src/backend/parser/analyze.c s/takend/taken/ s/relaion/relation/ s/targe/target/ -- this appears twice s/consitency/consistency/ s/commond/command/ s/seperate/separate/ in src/backend/rewrite/rewriteHandler.c s/acton/action/ in src/include/nodes/execnodes.h s/meger/merge/ in src/include/nodes/parsenodes.h s/proecess/process/ s/allwo/allow/ s/elments/elements/ in src/test/regress/expected/merge.out s/qulifications/qualifications/ -- this appears twice s/suceeds/succeeds/ -- this appears twice -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] Standby registration
On 23/09/10 12:49, Dimitri Fontaine wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: The consensus seems to be use a configuration file called standby.conf. Let's use the ini file format for now [1]. What about automatic registration of standbys? That's not going to fly with the unique global configuration file idea, but that's good news. Automatic registration is a good answer to both your points A) monitoring and C) wal_keep_segments, but needs some more thinking wrt security and authentication. What about having a new GRANT privilege for replication, so that any standby can connect with a non-superuser role as soon as the master's setup GRANTS replication to the role? You still need HBA setup to be accepting the slave, too, of course. There's two separate concepts here: 1. Automatic registration. When a standby connects, its information gets permanently added to standby.conf file 2. Unregistered standbys. A standby connects, and its information is not in standby.conf. It's let in anyway, and standby.conf is unchanged. We'll need to support unregistered standbys, at least in asynchronous mode. It's also possible for synchronous standbys, but you can't have the if the standby is disconnected, don't finish any commits until it reconnects and catches up behavior without registration. I'm inclined to not do automatic registration, not for now at least. Registering a synchronous standby should not be taken lightly. If the standby gets accidentally added to standby.conf, the master will have to keep more WAL around and might delay all commits, depending on the options used. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] ask for review of MERGE
On 2010-09-23 1:31 PM +0300, Boxuan Zhai wrote: I have just generate a new patch of MERGE command. I haven't followed the discussion very closely, but this part in the regression tests caught my attention: +-- we now have a duplicate key in Buy, so when we join to +-- Stock we will generate 2 matching rows, not one. +-- According to standard this command should fail. +-- But it suceeds in PostgreSQL implementation by simply ignoring the second It doesn't seem like a very good idea to go against the standard here. The second row is not well defined in this case so the results are unpredictable. The patch is also missing a (trivial) change to explain.c. Regards, Marko Tiikkaja -- 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: Triggers on VIEWs
--On 23. September 2010 08:59:32 +0100 Dean Rasheed dean.a.rash...@gmail.com wrote: Yes, I agree. To me this is the least surprising behaviour. I think a more common case would be where the trigger computed a value (such as the 'last updated' example). The executor doesn't have any kind of a handle on the row inserted by the trigger, so it has to rely on the function return value to support RETURNING. I didn't mean to forbid it altogether, but at least to document explicitely, that the trigger returns a VIEW's NEW tuple, not the one of the base table (and may modify it). But you've already adressed this in your doc patches, so nothing to worry about further. -- Thanks Bernd -- 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] Needs Suggestion
Actually, I used palloc() to set the stack base address. And I am trying to create only a single thread, then also it is causing problem. Actually, I created all the data-structures using palloc(), then I am passing these to the child thread. Even if I make these variables global then also it is not working. But if I don't set its stack address then it is working. But I need to do that because when my thread body is big then it is causing stack fault. So if I cannot set its stack address then Can I increase the stack depth limit to a large value ? -- Thank You, Subham Roy. On 22/09/10 11:14, sub...@cse.iitb.ac.in wrote: How can I increase the stack depth limit ? Is it only by modifying the postgres.conf file, but there I cannot increase the stack depth beyond 4 MB. Actually, my problem is that, when I set the stack base address of the child thread using the POSIX library function pthread_setstackaddr(), I am unable to access the memory contents of its parent. The data-structures in the parent are either getting destroyed or unaccessible when moving to the context of the child thread. It is not a good idea to use threads in server code. PostgreSQL server code is not thread-safe, things will break. Assuming that you're not actually doing that but using threads in the client instead, max_stack_depth should have no effect on you as it only affects the server. But you really should find another way to communicate between threads. Stacks should be treated as thread-private. Use malloc() or something and global variables. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Needs Suggestion
So, Can I increase the stack depth limit to a large value ? On 22/09/10 12:03, sub...@cse.iitb.ac.in wrote: Actually, I used palloc() to set the stack base address. And I am trying to create only a single thread, then also it is causing problem. Actually, I created all the data-structures using palloc(), then I am passing these to the child thread. Even if I make these variables global then also it is not working. But if I don't set its stack address then it is working. But I need to do that because when my thread body is big then it is causing stack fault. So if I cannot set its stack address then Can I increase the stack depth limit to a large value ? It's not clear what you're trying to do, but it's just not going to work. The backend code is not thread-safe, so you can't safely create any threads in server code. Not even a single one. And even if you could, you should not mess with stack base addresses. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Thank You, Subham Roy. -- 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] BUG #5661: The character encoding in logfile is confusing.
Hi Craig, Almost Japanese software emit log files by encoding of the server the software running on. I'm not sure it is the best way or not, but Japanese users taking it for granted. So I feel that Japanese users would hope that postgre server has same style with other software, cause many administrators in Japan are familiar and experienced for the way. On Unix, user can specify default character encoding at installing. Software can get it to refer the environment value $LANG e.g. % echo $LANG ja_JP.eucJP On Japanese Windows, default encoding is MS-932(or cp-932 or Windows-31J). This is fixed. MS-932 is almost same as Shift-JIS but very few characters has different character code between MS-932 and Shit-JIS. And Shift-JIS doesn't have some characters in MS-932. This is very important issue. This issue has been making a lot of related bugs e.g. below: http://bugs.mysql.com/bug.php?id=7607 And if postgre could be configured to emit a log file by row English messages, some users will choice it if the translating messages to Japanese has some costs. Some administrators in Japan don't hate reading English messages. (Many software are not user friendly for not English users. Many Japanese users are wondering and impressed with postgre emits Japanese messages in log file.) Thank you. =Mikio -- tkbysh2...@yahoo.co.jp On Wed, 22 Sep 2010 19:25:47 +0800 Craig Ringer cr...@postnewspapers.com.au wrote: On 22/09/2010 5:45 PM, Peter Eisentraut wrote: On ons, 2010-09-22 at 16:25 +0800, Craig Ringer wrote: A single log file should obviously be in a single encoding, it's the only sane way to do things. But which encoding is it in? And which *should* it be in? We need to produce the log output in the server encoding, because that's how we need to send it to the client. That doesn't mean it can't be recoded for writing to the log file, though. Perhaps it needs to be. It should be reasonably practical to detect when the database and log encoding are the same and avoid the transcoding performance penalty, not that it's big anyway. If you have different databases with different server encodings, you will get inconsistently encoded output in the log file. I don't think that's an OK answer, myself. Mixed encodings with no delineation in one file = bug as far as I'm concerned. You can't even rely on being able to search the log anymore. You'll only get away with it when using languages that mostly stick to the 7-bit ASCII subset, so most text is still readable; with most other languages you'll get logs full of what looks to the user like garbage. Conceivably, we could create a configuration option that specifies the encoding for the log file, and strings a recoded from whatever gettext() produces to the specified encoding. initdb could initialize that option suitably, so in most cases users won't have to do anything. Yep, I tend to think that'd be the right way to go. It'd still be a bit of a pain, though, as messages written to stdout/stderr by the postmaster should be in the system encoding, but messages written to the log files should be in the encoding specified for logs, unless logging is being done to syslog, in which case it has to be in the system encoding after all... And, of course, the postmaster still doesn't know how to log anything it might emit before reading postgresql.conf, because it doesn't know what encoding to use. I still wonder if, rather than making this configurable, the right choice is to force logging to UTF-8 (with BOM) across the board, right from postmaster startup. It's consistent, all messages in all other encodings can be converted to UTF-8 for logging, it's platform independent, and text editors etc tend to understand and recognise UTF-8 especially with the BOM. Unfortunately, because many unix utilities (grep etc) aren't encoding aware, that'll cause problems when people go to search log files. For (eg) 広告掲載 The log files will contain the utf-8 bytes: \xe5\xba\x83\xe5\x91\x8a\xe6\x8e\xb2\xe8\xbc\x89 but grep on a shift-jis system will be looking for: \x8d\x4c\x8d\x90\x8cf\x8d\xda so it won't match. Ugh. If only we could say PostgreSQL requires a system locale with a UTF-8 encoding. Alas, I don't think that'd go down very well with packagers or installers. [Insert rant about how stupid it is that *nix systems still aren't all UTF-8 here]. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Latch implementation
Hi,
I've been playing around with measuring the latch implementation in 9.1,
and here are the results of a ping-pong test with 2 processes signalling
and waiting on the latch. I did three variations (linux 2.6.18, nehalem
processor).
One is the current one.
The second is built on native semaphors on linux. This one cannot
implement WaitLatchOrSocket, there's no select involved.
The third is an implementation based on pipe() and poll. Note: in its
current incarnation it's essentially a hack to measure performance, it's
not usable in postgres, this assumes all latches are created before any
process is forked. We'd need to use mkfifo to sort that out if we really
want to go this route, or similar.
- Current implementation: 1 pingpong is avg 15 usecs
- Pipe+poll: 9 usecs
- Semaphore: 6 usecs
The test program modified unix_latch.c is attached, you can compile it
like gcc -DPIPE -O2 sema.c or gcc -DLINUX_SEM -O2 sema.c or gcc -O2
sema.c.
Thanks,
--Ganesh
#include sys/file.h
#include sys/stat.h
#include sys/ipc.h
#include sys/shm.h
#include sys/time.h
#include sys/stat.h
#include sys/ipc.h
#include sys/sem.h
#include unistd.h
#include stdlib.h
#include signal.h
#include errno.h
int nIter = 100;
#define LATCH_TIMEOUT 5000L
#include latch.h
#include unix_latch.c
int selfLatch, otherLatch;
Latch *latchArray;
#ifdef LINUX_SEM
int semId = -1;
#endif
void DoWork(int selfLatch);
void
sigusr1handler(int n)
{
DEBUG(sigusr handler\n);
latch_sigusr1_handler();
}
main()
{
int child;
int i;
int shmid;
for (i = 0; i 1000; i++) {
shmid = shmget(6500 + i, sizeof(Latch) * 2, IPC_CREAT | IPC_EXCL|0x1ff);
if (shmid 0) {
DEBUG(shmget error %d %d\n, shmid, errno);
if (i == 999) {
printf(Can't get shm, aborting test\n);
exit (1);
}
} else {
break;
}
}
latchArray = shmat(shmid, NULL, 0);
if ((long)latchArray 0) {
printf(shmat error %ld %d\n, (long)latchArray, errno);
exit(1);
}
InitSharedLatch(latchArray[0]);
InitSharedLatch(latchArray[1]);
child = fork();
if (child 0) {
printf(fork error %d %d\n, child, errno);
exit(1);
}
MyProcPid = getpid();
signal(SIGUSR1, sigusr1handler);
DoWork(child != 0);
}
static int
WaitForOther(int latch)
{
int ret;
Latch *l = latchArray[latch];
DEBUG(Wait %p\n, l);
ret = WaitLatch(l, LATCH_TIMEOUT);
ResetLatch(l);
return ret;
}
static void
SignalOther(int latch)
{
Latch *l = latchArray[latch];
DEBUG(Signal %p\n, l);
SetLatch(l);
}
void
DoWork(int l)
{
int i;
struct timeval tv1, tv2;
struct timezone tz;
float diff;
selfLatch = l, otherLatch = selfLatch ^ 1;
Latch *self = latchArray[selfLatch];
Latch *other = latchArray[otherLatch ^ 1];
OwnLatch(self);
sleep(2); /* Cheat: pseudo-barrier. */
printf(Start Test:);
#if defined(LINUX_SEM)
printf(Using semaphores\n);
#elif defined(PIPE)
printf(Using pipe\n);
#else
printf(Using signal\n);
#endif
/* Set one of the latches in the beginning. */
if (selfLatch == 0) {
SignalOther(otherLatch);
}
gettimeofday(tv1, tz);
for (i = 0; i nIter; i++) {
if (WaitForOther(selfLatch) != 1) {
printf(BUG BUG BUG\n);
exit(1);
}
SignalOther(otherLatch);
}
gettimeofday(tv2, tz);
diff = (tv2.tv_sec - tv1.tv_sec) * 100 + (tv2.tv_usec - tv1.tv_usec);
printf(%ld iterations took %.2f sec (%.2f usec/iter)\n,
nIter, diff/100, diff/nIter);
}
/*-
*
* latch.h
* Routines for interprocess latches
*
*
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
* $PostgreSQL$
*
*-
*/
#ifndef _LATCH_H_
#define _LATCH_H_
#include stdio.h
#include errno.h
#include signal.h
#include unistd.h
/* Fake postgres. */
int MyProcPid;
#define false 0
#define true 1
typedef unsigned char bool;
typedef int pgsocket;
#define PGINVALID_SOCKET -1
#define elog(_x,...) printf(__VA_ARGS__)
#define Assert(_x) do { if ((_x) == 0) { printf(Assertion failure @%d\n,
__LINE__); exit(1); }} while (0);
//#define DEBUG(...) printf(__VA_ARGS__)
#define DEBUG(...) do { ; } while (0);
union semun {
int val;/* Value for SETVAL */
struct semid_ds *buf;/* Buffer for IPC_STAT, IPC_SET */
unsigned short *array; /* Array for GETALL, SETALL */
struct seminfo *__buf; /* Buffer for IPC_INFO
(Linux specific) */
};
/*
* Latch structure should be treated as opaque and only accessed through
* the public functions. It is defined here to allow embedding Latches as
* part of bigger structs.
*/
typedef struct
{
#if defined(LINUX_SEM)
int semId;
Re: [HACKERS] Configuring synchronous replication
Hi all, Some time ago I was also interested in this feature, and that time I also thought about complete setup possibility via postgres connections, meaning the transfer of the files and all configuration/slave registration to be done through normal backend connections. In the meantime our DBs are not able to keep in sync via WAL replication, that would need some kind of parallel WAL restore on the slave I guess, or I'm not able to configure it properly - in any case now we use slony which is working. In fact the way slony is doing the configuration could be a good place to look... On Wed, 2010-09-22 at 13:16 -0400, Robert Haas wrote: I guarantee you there is a way around the cascade slave problem. And that would be...? * restrict the local file configuration to a replication ID; * make all configuration refer to the replica ID; * keep all configuration in a shared catalog: it can be kept exactly the same on all replicas, as each replication node will only care about the configuration concerning it's own replica ID; * added advantage: after take-over the slave will change the configured master to it's own replica ID, and if the old master would ever connect again, it could easily notice that and give up; Cheers, Csaba. -- 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] Configuring synchronous replication
On Thu, 2010-09-23 at 12:02 +0300, Heikki Linnakangas wrote: On 23/09/10 11:34, Csaba Nagy wrote: In the meantime our DBs are not able to keep in sync via WAL replication, that would need some kind of parallel WAL restore on the slave I guess, or I'm not able to configure it properly - in any case now we use slony which is working. It would be interesting to debug that case a bit more. Was bottlenecked by CPU or I/O, or network capacity perhaps? Unfortunately it was quite long time ago we last tried, and I don't remember exactly what was bottlenecked. Our application is quite write-intensive, the ratio of writes to reads which actually reaches the disk is about 50-200% (according to the disk stats - yes, sometimes we write more to the disk than we read, probably due to the relatively large RAM installed). If I remember correctly, the standby was about the same regarding IO/CPU power as the master, but it was not able to process the WAL files as fast as they were coming in, which excludes at least the network as a bottleneck. What I actually suppose happens is that the one single process applying the WAL on the slave is not able to match the full IO the master is able to do with all it's processors. If you're interested, I could try to set up another try, but it would be on 8.3.7 (that's what we still run). On 9.x would be also interesting, but that would be a test system and I can't possibly get there the load we have on production... Cheers, Csaba. -- 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] Needs Suggestion
On 09/22/2010 05:03 AM, sub...@cse.iitb.ac.in wrote: Actually, I used palloc() to set the stack base address. And I am trying to create only a single thread, then also it is causing problem. Did you not understand when people told you this wasn't going to work? Don't create any threads. 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] Configuring synchronous replication
On 23/09/10 15:26, Csaba Nagy wrote: Unfortunately it was quite long time ago we last tried, and I don't remember exactly what was bottlenecked. Our application is quite write-intensive, the ratio of writes to reads which actually reaches the disk is about 50-200% (according to the disk stats - yes, sometimes we write more to the disk than we read, probably due to the relatively large RAM installed). If I remember correctly, the standby was about the same regarding IO/CPU power as the master, but it was not able to process the WAL files as fast as they were coming in, which excludes at least the network as a bottleneck. What I actually suppose happens is that the one single process applying the WAL on the slave is not able to match the full IO the master is able to do with all it's processors. There's a program called pg_readahead somewhere on pgfoundry by NTT that will help if it's the single-threadedness of I/O. Before handing the WAL file to the server, it scans it through and calls posix_fadvise for all the blocks that it touches. When the server then replays it, the data blocks are already being fetched by the OS, using the whole RAID array. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] wip: functions median and percentile
Hello
I moved a median function to core.
+ doc part
+ regress test
Regards
Pavel Stehule
2010/9/20 Hitoshi Harada umi.tan...@gmail.com:
2010/8/19 Pavel Stehule pavel.steh...@gmail.com:
Hello
I am sending a prototype implementation of functions median and
percentile. This implementation is very simple and I moved it to
contrib for this moment - it is more easy maintainable. Later I'll
move it to core.
I've reviewed this patch.
* The patch can apply cleanly and make doesn't print any errors nor
warnings. But it doesn't touch contrib/Makefile so I had to make by
changing dir to contrib/median.
* Cosmetic coding style should be more appropriate, including trailing
white spaces and indentation positions.
* Since these two aggregates use tuplesort inside it, there're
possible risk to cause out of memory in normal use case. I don't think
this fact is critical, but at least some notation should be referred
in docs.
* It doesn't contain any document nor regression tests.
* They should be callable in window function context; for example
contrib_regression=# select median(a) over (order by a) from t1;
ERROR: invalid tuplesort state
or at least user-friend message should be printed.
* The returned type is controversy. median(int) returns float8 as the
patch intended, but avg(int) returns numeric. AFAIK only avg(float8)
returns float8.
* percentile() is more problematic; first, the second argument for the
aggregate takes N of N%ile as int, like 50 if you want 50%ile, but
it doesn't care about negative values or more than 100. In addition,
the second argument is taken at the first non-NULL value of the first
argument, but the second argument is semantically constant. For
example, for (1.. 10) value of a in table t1,
contrib_regression=# select percentile(a, a * 10 order by a) from t1;
percentile
1
(1 row)
contrib_regression=# select percentile(a, a * 10 order by a desc) from t1;
percentile
10
(1 row)
and if the argument comes from the subquery which doesn't contain
ORDER BY clause, you cannot predict the result.
That's all of my quick review up to now.
Regards,
--
Hitoshi Harada
*** ./doc/src/sgml/func.sgml.orig 2010-08-17 06:37:20.0 +0200
--- ./doc/src/sgml/func.sgml 2010-09-23 15:03:10.021576906 +0200
***
*** 10304,10309
--- 10304,10329
row
entry
+indexterm
+ primaryArithmetic median/primary
+ secondarymedian/secondary
+/indexterm
+functionmedian(replaceable class=parameterexpression/replaceable)/function
+ /entry
+ entry
+typesmallint/type, typeint/type,
+typebigint/type, typereal/type, typedouble
+precision/type, or typenumeric/type
+ /entry
+ entry
+typedouble precision/type for floating-point arguments,
+otherwise typenumeric/type
+ /entry
+ entryarithmetic median/entry
+ /row
+
+ row
+ entry
functionregr_avgx(replaceable class=parameterY/replaceable, replaceable class=parameterX/replaceable)/function
/entry
entry
*** ./src/backend/utils/adt/numeric.c.orig 2010-08-04 19:33:09.0 +0200
--- ./src/backend/utils/adt/numeric.c 2010-09-23 15:15:26.775451348 +0200
***
*** 30,39
--- 30,42
#include catalog/pg_type.h
#include libpq/pqformat.h
#include miscadmin.h
+ #include parser/parse_coerce.h
+ #include parser/parse_oper.h
#include utils/array.h
#include utils/builtins.h
#include utils/int8.h
#include utils/numeric.h
+ #include utils/tuplesort.h
/* --
* Uncomment the following to enable compilation of dump_numeric()
***
*** 144,149
--- 147,161
union NumericChoice choice; /* choice of format */
};
+ /*
+ * used as type of state variable median's function
+ */
+ typedef struct
+ {
+ int nelems; /* number of valid entries */
+ Tuplesortstate *sortstate;
+ FmgrInfo cast_func_finfo;
+ } MedianAggState;
/*
* Interpretation of high bits.
***
*** 6173,6175
--- 6185,6490
var-digits = digits;
var-ndigits = ndigits;
}
+
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targetoid)
+ {
+ MemoryContext oldctx;
+ MemoryContext aggcontext;
+ MedianAggState *aggstate;
+ Oid sortop,
+ castfunc;
+ Oid targettype = InvalidOid;
+ CoercionPathType pathtype;
+
+ /*
+ * We cannot to allow a median function under WindowAgg State.
+ * This content needs a repetetive a calling of final function,
+ * what isn't possible now with using a tuplestore.
+ * tuplesort_performsort can be called only once, and some has
+ * to call a tuplesort_end.
+ */
+ if (fcinfo-context IsA(fcinfo-context, WindowAggState))
+ {
+ /* cannot be called inside like windowAggregates */
+ elog(ERROR, median_transfn called as windows aggregates);
+ }
+
+
Re: [HACKERS] Latch implementation
On Wed, Sep 22, 2010 at 4:31 PM, Ganesh Venkitachalam-1 gan...@vmware.com wrote: I've been playing around with measuring the latch implementation in 9.1, and here are the results of a ping-pong test with 2 processes signalling and waiting on the latch. I did three variations (linux 2.6.18, nehalem processor). One is the current one. The second is built on native semaphors on linux. This one cannot implement WaitLatchOrSocket, there's no select involved. The third is an implementation based on pipe() and poll. Note: in its current incarnation it's essentially a hack to measure performance, it's not usable in postgres, this assumes all latches are created before any process is forked. We'd need to use mkfifo to sort that out if we really want to go this route, or similar. - Current implementation: 1 pingpong is avg 15 usecs - Pipe+poll: 9 usecs - Semaphore: 6 usecs Interesting numbers. I guess one question is how much improving the performance of the latch implementation would affect overall system performance. Synchronous replication is obviously going to be highly sensitive to latency, but even in that context I'm not really sure whether this is enough to matter. Do you have any sense of that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] wip: functions median and percentile
sorry
little bit fixed patch
Pavel
2010/9/23 Pavel Stehule pavel.steh...@gmail.com:
Hello
I moved a median function to core.
+ doc part
+ regress test
Regards
Pavel Stehule
2010/9/20 Hitoshi Harada umi.tan...@gmail.com:
2010/8/19 Pavel Stehule pavel.steh...@gmail.com:
Hello
I am sending a prototype implementation of functions median and
percentile. This implementation is very simple and I moved it to
contrib for this moment - it is more easy maintainable. Later I'll
move it to core.
I've reviewed this patch.
* The patch can apply cleanly and make doesn't print any errors nor
warnings. But it doesn't touch contrib/Makefile so I had to make by
changing dir to contrib/median.
* Cosmetic coding style should be more appropriate, including trailing
white spaces and indentation positions.
* Since these two aggregates use tuplesort inside it, there're
possible risk to cause out of memory in normal use case. I don't think
this fact is critical, but at least some notation should be referred
in docs.
* It doesn't contain any document nor regression tests.
* They should be callable in window function context; for example
contrib_regression=# select median(a) over (order by a) from t1;
ERROR: invalid tuplesort state
or at least user-friend message should be printed.
* The returned type is controversy. median(int) returns float8 as the
patch intended, but avg(int) returns numeric. AFAIK only avg(float8)
returns float8.
* percentile() is more problematic; first, the second argument for the
aggregate takes N of N%ile as int, like 50 if you want 50%ile, but
it doesn't care about negative values or more than 100. In addition,
the second argument is taken at the first non-NULL value of the first
argument, but the second argument is semantically constant. For
example, for (1.. 10) value of a in table t1,
contrib_regression=# select percentile(a, a * 10 order by a) from t1;
percentile
1
(1 row)
contrib_regression=# select percentile(a, a * 10 order by a desc) from t1;
percentile
10
(1 row)
and if the argument comes from the subquery which doesn't contain
ORDER BY clause, you cannot predict the result.
That's all of my quick review up to now.
Regards,
--
Hitoshi Harada
*** ./doc/src/sgml/func.sgml.orig 2010-08-17 06:37:20.0 +0200
--- ./doc/src/sgml/func.sgml 2010-09-23 15:03:10.021576906 +0200
***
*** 10304,10309
--- 10304,10329
row
entry
+indexterm
+ primaryArithmetic median/primary
+ secondarymedian/secondary
+/indexterm
+functionmedian(replaceable class=parameterexpression/replaceable)/function
+ /entry
+ entry
+typesmallint/type, typeint/type,
+typebigint/type, typereal/type, typedouble
+precision/type, or typenumeric/type
+ /entry
+ entry
+typedouble precision/type for floating-point arguments,
+otherwise typenumeric/type
+ /entry
+ entryarithmetic median/entry
+ /row
+
+ row
+ entry
functionregr_avgx(replaceable class=parameterY/replaceable, replaceable class=parameterX/replaceable)/function
/entry
entry
*** ./src/backend/utils/adt/numeric.c.orig 2010-08-04 19:33:09.0 +0200
--- ./src/backend/utils/adt/numeric.c 2010-09-23 15:24:04.025453940 +0200
***
*** 30,39
--- 30,42
#include catalog/pg_type.h
#include libpq/pqformat.h
#include miscadmin.h
+ #include parser/parse_coerce.h
+ #include parser/parse_oper.h
#include utils/array.h
#include utils/builtins.h
#include utils/int8.h
#include utils/numeric.h
+ #include utils/tuplesort.h
/* --
* Uncomment the following to enable compilation of dump_numeric()
***
*** 144,149
--- 147,161
union NumericChoice choice; /* choice of format */
};
+ /*
+ * used as type of state variable median's function
+ */
+ typedef struct
+ {
+ int nelems; /* number of valid entries */
+ Tuplesortstate *sortstate;
+ FmgrInfo cast_func_finfo;
+ } MedianAggState;
/*
* Interpretation of high bits.
***
*** 6173,6175
--- 6185,6467
var-digits = digits;
var-ndigits = ndigits;
}
+
+ static MedianAggState *
+ makeMedianAggState(FunctionCallInfo fcinfo, Oid valtype, Oid targettype)
+ {
+ MemoryContext oldctx;
+ MemoryContext aggcontext;
+ MedianAggState *aggstate;
+ Oid sortop,
+ castfunc;
+ CoercionPathType pathtype;
+
+ /*
+ * We cannot to allow a median function under WindowAgg State.
+ * This content needs a repetetive a calling of final function,
+ * what isn't possible now with using a tuplestore.
+ * tuplesort_performsort can be called only once, and some has
+ * to call a tuplesort_end.
+ */
+ if (fcinfo-context IsA(fcinfo-context, WindowAggState))
+ {
+ /* cannot be called inside like windowAggregates */
+
Re: [HACKERS] Path question
On Tue, Sep 21, 2010 at 12:29 AM, David Fetter da...@fetter.org wrote: On Mon, Sep 20, 2010 at 10:57:00PM -0400, Robert Haas wrote: 2010/9/3 Hans-Jürgen Schönig h...@cybertec.at: On Sep 2, 2010, at 1:20 AM, Robert Haas wrote: I agree. Explicit partitioning may open up some additional optimization possibilities in certain cases, but Merge Append is more general and extremely valuable in its own right. we have revised greg's wonderful work and ported the entire thing to head. it solves the problem of merge_append. i did some testing earlier on today and it seems most important cases are working nicely. First, thanks for merging this up to HEAD. I took a look through this patch tonight, and the previous reviews thereof that I was able to find, most notably Tom's detailed review on 2009-07-26. I'm not sure whether or not it's accidental that this didn't get added to the CF, It's because I missed putting it in, and oversight I've corrected. If we need to bounce it on to the next one, them's the breaks. [points elided] 7. I think there's some basic code cleanup needed here, also: comment formatting, variable naming, etc. Hans-Jürgen, Will you be able to get to this in the next couple of days? I don't see a response to this which I assume means no - I'm going to take a crack at fixing some of these issues. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Latch implementation
On 22/09/10 23:31, Ganesh Venkitachalam-1 wrote: I've been playing around with measuring the latch implementation in 9.1, and here are the results of a ping-pong test with 2 processes signalling and waiting on the latch. I did three variations (linux 2.6.18, nehalem processor). One is the current one. The second is built on native semaphors on linux. This one cannot implement WaitLatchOrSocket, there's no select involved. The third is an implementation based on pipe() and poll. Note: in its current incarnation it's essentially a hack to measure performance, it's not usable in postgres, this assumes all latches are created before any process is forked. We'd need to use mkfifo to sort that out if we really want to go this route, or similar. - Current implementation: 1 pingpong is avg 15 usecs - Pipe+poll: 9 usecs - Semaphore: 6 usecs The test program modified unix_latch.c is attached, you can compile it like gcc -DPIPE -O2 sema.c or gcc -DLINUX_SEM -O2 sema.c or gcc -O2 sema.c. Interesting, thanks for the testing! Could you also test how much faster the current implementation gets by just replacing select() with poll()? That should shave off some overhead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Git cvsserver serious issue
Magnus Hagander wrote: I assume most buildfarm clients are off static IPs (at least as seen from the servers - they may be behind a NAT device, but that one having static out)? If so, it seems simply easier to use pserver... Yes, I think we should have a VM. Is that so hard to do in these days of Xen etc? I'm surprised we can't run up a VM pretty much at the drop of a hat. In the new infrastructure, it is. The main resource that's limited really is IP addresses ;) I was suggesting that the accounts would be protected using ssh keys. Password and IP address protection seem pretty weak to me. Passwords can be sniffed or attacked using brute force. IP addresses can be spoofed. But you're the SA, not me. I prefer keys. But I don't want those users on the same VM as important services, because passphrase-less keys are a lot more likely to be compromised than the keys of say a regular committer... Stupid question, but can't we just create a CVSROOT fed from git, and use the normal CVS server to feed sites? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Git cvsserver serious issue
On 09/23/2010 09:55 AM, Bruce Momjian wrote: Stupid question, but can't we just create a CVSROOT fed from git, and use the normal CVS server to feed sites? Where is it going to get the ,v files that CVS uses from? git-cvsserver emulates a CVS server from git. It doesn't create a CVS repository that CVS in server mode could use. 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] Git cvsserver serious issue
Andrew Dunstan wrote: On 09/23/2010 09:55 AM, Bruce Momjian wrote: Stupid question, but can't we just create a CVSROOT fed from git, and use the normal CVS server to feed sites? Where is it going to get the ,v files that CVS uses from? git-cvsserver emulates a CVS server from git. It doesn't create a CVS repository that CVS in server mode could use. I suppose there is no way to pull diffs from git continuously and commit them to CVS. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] security label support, revised
Robert, First off, thanks alot for working on this. My apologies for not having time to help out. A few minor comments: * Robert Haas (robertmh...@gmail.com) wrote: Most of the contents of the new documentation section on external security providers seemed irrelevant to me, which I guess I can only blame myself for since I was the one who asked for that section to be created, and I didn't specify what it should contain all that well. I took a try at rewriting it to be more on-topic, but it didn't amount to much so I ended up just ripping that part out completely. Do we have a place where we actually document hooks today..? Seems like we should and that'd be a good place to put the few necessary comments regarding these. There are a few other problems. First, there's no psql support of any kind. Now, this is kind of a corner-case feature: so maybe we don't really need it. And as I mentioned on another thread, there aren't a lot of good letters left for backslash-d commands. One thought would be to add it to \dp or have a \dp+. So I'd be just as happy to add a system view along the lines I previously proposed for comments and call it good. I think that regardless of psql and \d, we should have a sensible system view for it. Second, there are no regression tests. It's a bit tricky to think about how to crack that nut because this feature is somewhat unusual in that it can't be used without loading an appropriate loadable module. I'm wondering if we can ship a dummy_seclabel contrib module that can be loaded during the regression test run and then run various tests using that, but I'm not quite sure what the best way to set that up is. SECURITY LABEL is a core feature, so it would be nice to test it in the core regression tests... but maybe that's too complicated to get working, and we should just test it from the contrib module. The first set of regression tests could simply run the SECURITY LABEL commands and then check the results in the catalog. If some kind of psql support is included, it could test that also. That doesn't check that the hooks are called at the right time and with the right data, so I agree with the suggestion to have dummy contrib modules (or something) to do that generically for all our hooks, but I don't think we've got anything like that today..? If we do, then we should model it off whatever's there now. Perhaps we can look at how to do it comprehensively for all hooks.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 16:11, Bruce Momjian br...@momjian.us wrote: Andrew Dunstan wrote: On 09/23/2010 09:55 AM, Bruce Momjian wrote: Stupid question, but can't we just create a CVSROOT fed from git, and use the normal CVS server to feed sites? Where is it going to get the ,v files that CVS uses from? git-cvsserver emulates a CVS server from git. It doesn't create a CVS repository that CVS in server mode could use. I suppose there is no way to pull diffs from git continuously and commit them to CVS. You probably could. I'm very doubtful it's worth it... -- 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 04:38:27PM +0200, Magnus Hagander wrote: On Thu, Sep 23, 2010 at 16:11, Bruce Momjian br...@momjian.us wrote: Andrew Dunstan wrote: On 09/23/2010 09:55 AM, Bruce Momjian wrote: Stupid question, but can't we just create a CVSROOT fed from git, and use the normal CVS server to feed sites? Where is it going to get the ,v files that CVS uses from? git-cvsserver emulates a CVS server from git. It doesn't create a CVS repository that CVS in server mode could use. I suppose there is no way to pull diffs from git continuously and commit them to CVS. You probably could. I'm very doubtful it's worth it... Back to a question you asked earlier, what exactly still depends on CVS right now, as in which buildfarm animals, what parts of the NLS processes? Also as you asked earlier, what else? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Serializable Snapshot Isolation
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 23/09/10 02:14, Kevin Grittner wrote: There is a rub on the other point, though. Without transaction information you have no way of telling whether TN committed before T0, so you would need to assume that it did. So on this count, there is bound to be some increase in false positives leading to transaction rollback. Without more study, and maybe some tests, I'm not sure how significant it is. (Actually, we might want to track commit sequence somehow, so we can determine this with greater accuracy.) I'm confused. AFAICS there is no way to tell if TN committed before T0 in the current patch either. Well, we can certainly infer it if the finishedBefore values differ. And, as I said, if we don't eliminate this structure for committed transactions, we could add a commitId or some such, with precedes and follows tests similar to TransactionId. The other way we can detect conflicts is a read by a serializable transaction noticing that a different and overlapping serializable transaction wrote the tuple we're trying to read. How do you propose to know that the other transaction was serializable without keeping the SERIALIZABLEXACT information? Hmm, I see. We could record which transactions were serializable in a new clog-like structure that wouldn't exhaust shared memory. And how do you propose to record the conflict without it? I thought you just abort the transaction that would cause the conflict right there. The other transaction is committed already, so you can't do anything about it anymore. No, it always requires a rw-conflict from T0 to T1 and a rw-conflict from T1 to TN, as well as TN committing first and (T0 not being READ ONLY or TN not overlapping T0). The number and complexity of the conditions which must be met to cause a serialization failure are what keep the failure rate reasonable. If we start rolling back transactions every time one transaction simply reads a row modified by a concurrent transaction I suspect that we'd have such a storm of serialization failures in most workloads that nobody would want to use it. Finally, this would preclude some optimizations which I *think* will pay off, which trade a few hundred kB more of shared memory, and some additional CPU to maintain more detailed conflict data, for a lower false positive rate -- meaning fewer transactions rolled back for hard-to-explain reasons. This more detailed information is also what seems to be desired by Dan S (on another thread) to be able to log the information needed to be able to reduce rollbacks. Ok, I think I'm ready to hear about those optimizations now :-). Dan Ports is eager to implement next key predicate locking for indexes, but wants more benchmarks to confirm the benefit. (Most of the remaining potential optimizations carry some risk of being counter-productive, so we want to go in with something conservative and justify each optimization separately.) That one only affects your proposal to the extent that the chance to consolidate locks on the same target by committed transactions would likely have fewer matches to collapse. One that I find interesting is the idea that we could set a SERIALIZABLE READ ONLY transaction with some additional property (perhaps DEFERRED or DEFERRABLE) which would cause it to take a snapshot and then wait until there were no overlapping serializable transactions which are not READ ONLY which overlap a running SERIALIZABLE transaction which is not READ ONLY. At this point it could make a valid snapshot which would allow it to run without taking predicate locks or checking for conflicts. It would have no chance of being rolled back with a serialization failure *or* of contributing to the failure of any other transaction, yet it would be guaranteed to see a view of the database consistent with the actions of all other serializable transactions. One place I'm particularly interested in using such a feature is in pg_dump. Without it we have the choice of using a SERIALIZABLE transaction, which might fail or cause failures (which doesn't seem good for a backup program) or using REPEATABLE READ (to get current snapshot isolation behavior), which might capture a view of the data which contains serialization anomalies. The notion of capturing a backup which doesn't comply with business rules enforced by serializable transactions gives me the willies, but it would be better than not getting a backup reliably, so in the absence of this feature, I think we need to change pg_dump to use REPEATABLE READ. I can't see how to do this without keeping information on committed transactions. This next paragraph is copied straight from the Wiki page: It appears that when a pivot is formed where T0 is a flagged as a READ ONLY transaction, and it is concurrent with TN, we can wait to see whether anything really needs to roll back. If T1 commits before developing a
Re: [HACKERS] Git cvsserver serious issue
On 09/23/2010 10:58 AM, David Fetter wrote: Back to a question you asked earlier, what exactly still depends on CVS right now, as in which buildfarm animals, what parts of the NLS processes? Also as you asked earlier, what else? At least one buildfarm member, spoonbill, is known to have issues with git. This machine, because of the flags it uses, has found numerous bugs for us in the past and is quite important for us to maintain (a direct counter argument to your suggestion of dropping platforms that don't support git). 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] Git cvsserver serious issue
Magnus Hagander mag...@hagander.net writes: So, I found (with some helpful hints from Robert who caught the final nail in the coffin) a good reason why we really can't run a git-cvsserver globally. Any user can point their cvs client at the repository. And check out an arbitrary branch, tag *or individual commit*. Doing so will create a 50Mb sqlite database on the server with cache information about that head. I'm still wondering why we don't simply lobotomize git-cvsserver to refuse requests to check out anything except the active branch tips. It's only a Perl script. I could probably hack it in an hour, there are those here who could do it in ten minutes. 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 17:16, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: So, I found (with some helpful hints from Robert who caught the final nail in the coffin) a good reason why we really can't run a git-cvsserver globally. Any user can point their cvs client at the repository. And check out an arbitrary branch, tag *or individual commit*. Doing so will create a 50Mb sqlite database on the server with cache information about that head. I'm still wondering why we don't simply lobotomize git-cvsserver to refuse requests to check out anything except the active branch tips. It's only a Perl script. I could probably hack it in an hour, there are those here who could do it in ten minutes. Yeah, that would not be a bad idea - if someone can do it who feels comfortable doing it :-) I could probably hack it up as well, but I wouldn't trust myself to have convered all the bases. -- 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 11:17:35AM -0400, Andrew Dunstan wrote: On 09/23/2010 10:58 AM, David Fetter wrote: Back to a question you asked earlier, what exactly still depends on CVS right now, as in which buildfarm animals, what parts of the NLS processes? Also as you asked earlier, what else? At least one buildfarm member, spoonbill, is known to have issues with git. Do those issues appear fixable? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Git cvsserver serious issue
On 09/23/2010 11:18 AM, Magnus Hagander wrote: On Thu, Sep 23, 2010 at 17:16, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagandermag...@hagander.net writes: So, I found (with some helpful hints from Robert who caught the final nail in the coffin) a good reason why we really can't run a git-cvsserver globally. Any user can point their cvs client at the repository. And check out an arbitrary branch, tag *or individual commit*. Doing so will create a 50Mb sqlite database on the server with cache information about that head. I'm still wondering why we don't simply lobotomize git-cvsserver to refuse requests to check out anything except the active branch tips. It's only a Perl script. I could probably hack it in an hour, there are those here who could do it in ten minutes. Yeah, that would not be a bad idea - if someone can do it who feels comfortable doing it :-) I could probably hack it up as well, but I wouldn't trust myself to have convered all the bases. Are we sure that's going to stop the DOS issue? 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] Latch implementation
On Wed, 2010-09-22 at 13:31 -0700, Ganesh Venkitachalam-1 wrote: Hi, I've been playing around with measuring the latch implementation in 9.1, and here are the results of a ping-pong test with 2 processes signalling and waiting on the latch. I did three variations (linux 2.6.18, nehalem processor). One is the current one. The second is built on native semaphors on linux. This one cannot implement WaitLatchOrSocket, there's no select involved. That looks interesting. If we had a need for a latch that would not need to wait on a socket as well, this would be better. In sync rep, we certainly do. Thanks for measuring this. Question is: in that case would we use latches or a PGsemaphore? If the answer is latch then we could just have an additional boolean option when we request InitLatch() to see what kind of latch we want. The third is an implementation based on pipe() and poll. Note: in its current incarnation it's essentially a hack to measure performance, it's not usable in postgres, this assumes all latches are created before any process is forked. We'd need to use mkfifo to sort that out if we really want to go this route, or similar. - Current implementation: 1 pingpong is avg 15 usecs - Pipe+poll: 9 usecs - Semaphore: 6 usecs Pipe+poll not worth it then. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] Configuring synchronous replication
On Wed, 2010-09-22 at 13:00 -0400, Robert Haas wrote: I think it should be a separate config file, and I think it should be a config file that can be edited using DDL commands as you propose. But it CAN'T be a system catalog, because, among other problems, that rules out cascading slaves, which are a feature a lot of people probably want to eventually have. ISTM that we can have a system catalog and still have cascading slaves. If we administer the catalog via the master, why can't we administer all slaves, however they cascade, via the master too? What other problems are there that mean we *must* have a file? I can't see any. Elsewhere, we've established that we can have unregistered standbys, so max_wal_senders cannot go away. If we do have a file, it will be a problem after failover since the file will be either absent or potentially out of date. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] Git cvsserver serious issue
David Fetter wrote: On Thu, Sep 23, 2010 at 11:17:35AM -0400, Andrew Dunstan wrote: On 09/23/2010 10:58 AM, David Fetter wrote: Back to a question you asked earlier, what exactly still depends on CVS right now, as in which buildfarm animals, what parts of the NLS processes? Also as you asked earlier, what else? At least one buildfarm member, spoonbill, is known to have issues with git. Do those issues appear fixable? maybe but I have absolutely no time to investigate in the next few days. Stefan -- 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 17:32, Andrew Dunstan and...@dunslane.net wrote: On 09/23/2010 11:18 AM, Magnus Hagander wrote: On Thu, Sep 23, 2010 at 17:16, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagandermag...@hagander.net writes: So, I found (with some helpful hints from Robert who caught the final nail in the coffin) a good reason why we really can't run a git-cvsserver globally. Any user can point their cvs client at the repository. And check out an arbitrary branch, tag *or individual commit*. Doing so will create a 50Mb sqlite database on the server with cache information about that head. I'm still wondering why we don't simply lobotomize git-cvsserver to refuse requests to check out anything except the active branch tips. It's only a Perl script. I could probably hack it in an hour, there are those here who could do it in ten minutes. Yeah, that would not be a bad idea - if someone can do it who feels comfortable doing it :-) I could probably hack it up as well, but I wouldn't trust myself to have convered all the bases. Are we sure that's going to stop the DOS issue? As long as it's done right, I don't see how it wouldn't. -- 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] Git cvsserver serious issue
Andrew Dunstan and...@dunslane.net writes: On Thu, Sep 23, 2010 at 17:16, Tom Lanet...@sss.pgh.pa.us wrote: I'm still wondering why we don't simply lobotomize git-cvsserver to refuse requests to check out anything except the active branch tips. Are we sure that's going to stop the DOS issue? The claimed denial of service is that each checkout target requires a separate SQLite database. Limit the number of checkout targets accepted and you're done. Or at least, if you're not done, it behooves those claiming there's a security problem to show what the problem is. It's not like this piece of software isn't used in production, so I doubt it needs to be babied quite as much as this thread is assuming. 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] Configuring synchronous replication
Simon Riggs si...@2ndquadrant.com writes: ISTM that we can have a system catalog and still have cascading slaves. If we administer the catalog via the master, why can't we administer all slaves, however they cascade, via the master too? What other problems are there that mean we *must* have a file? Well, for one thing, how do you add a new slave? If its configuration comes from a system catalog, it seems that it has to already be replicating before it knows what its configuration is. 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] Git cvsserver serious issue
Magnus Hagander mag...@hagander.net writes: On Thu, Sep 23, 2010 at 17:32, Andrew Dunstan and...@dunslane.net wrote: Are we sure that's going to stop the DOS issue? As long as it's done right, I don't see how it wouldn't. There might be a cleaner way to do it, but after a moment's inspection of the script, I'd be inclined to just hack GITCVS::updater-new() to throw error if $module is neither master nor REL\d_\d_STABLE. Keep in mind of course that I'm a lousy Perl coder. 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] Configuring synchronous replication
On Thu, 2010-09-23 at 11:43 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: ISTM that we can have a system catalog and still have cascading slaves. If we administer the catalog via the master, why can't we administer all slaves, however they cascade, via the master too? What other problems are there that mean we *must* have a file? Well, for one thing, how do you add a new slave? If its configuration comes from a system catalog, it seems that it has to already be replicating before it knows what its configuration is. At the moment, I'm not aware of any proposed parameters that need to be passed from master to standby, since that was one of the arguments for standby registration in the first place. If that did occur, when the standby connects it would get told what parameters to use by the master as part of the handshake. It would have to work exactly that way with standby.conf on the master also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] Configuring synchronous replication
On Thu, Sep 23, 2010 at 11:32 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-09-22 at 13:00 -0400, Robert Haas wrote: I think it should be a separate config file, and I think it should be a config file that can be edited using DDL commands as you propose. But it CAN'T be a system catalog, because, among other problems, that rules out cascading slaves, which are a feature a lot of people probably want to eventually have. ISTM that we can have a system catalog and still have cascading slaves. If we administer the catalog via the master, why can't we administer all slaves, however they cascade, via the master too? Well, I guess we could, but is that really convenient? My gut feeling is no, but of course it's subjective. What other problems are there that mean we *must* have a file? I can't see any. Elsewhere, we've established that we can have unregistered standbys, so max_wal_senders cannot go away. If we do have a file, it will be a problem after failover since the file will be either absent or potentially out of date. I'm not sure about that. I wonder if we can actually turn this into a feature, with careful design. Suppose that you have the common configuration of two machines, A and B. At any give time, one is the master and one is the slave. And let's say you've opted for sync rep, apply mode, don't wait for disconnected standbys. Well, you can have a config file on A that defines B as the slave, and a config file on B that defines A as the slave. When failover happens, you still have to worry about taking a new base backup, removing recovery.conf from the new master and adding it to the slave, and all that stuff, but the standby config just works. Now, admittedly, in more complex topologies, and especially if you're using configuration options that pertain to the behavior of disconnected standbys (e.g. wait for them, or retain WAL for them), you're going to need to adjust the configs. But I think that's likely to be true anyway, even with a catalog. If A is doing sync rep and waiting for B even when B is disconnected, and the machines switch roles, it's hard to see how any configuration isn't going to need some adjustment. One thing that's nice about the flat file system is that you can make the configuration changes on the new master before you promote it (perhaps you had A replicating synchronously to B and B replicating asynchronously to C, but now that A is dead and B is promoted, you want the latter replication to become synchronous). Being able to make those kinds of changes before you start processing live transactions is possibly useful to some people. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Configuring synchronous replication
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-09-23 at 11:43 -0400, Tom Lane wrote: Well, for one thing, how do you add a new slave? If its configuration comes from a system catalog, it seems that it has to already be replicating before it knows what its configuration is. At the moment, I'm not aware of any proposed parameters that need to be passed from master to standby, since that was one of the arguments for standby registration in the first place. If that did occur, when the standby connects it would get told what parameters to use by the master as part of the handshake. It would have to work exactly that way with standby.conf on the master also. Um ... so how does this standby know what master to connect to, what password to offer, etc? I don't think that pass down parameters after connecting is likely to cover anything but a small subset of the configuration problem. 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] Configuring synchronous replication
On Thu, Sep 23, 2010 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-09-23 at 11:43 -0400, Tom Lane wrote: Well, for one thing, how do you add a new slave? If its configuration comes from a system catalog, it seems that it has to already be replicating before it knows what its configuration is. At the moment, I'm not aware of any proposed parameters that need to be passed from master to standby, since that was one of the arguments for standby registration in the first place. If that did occur, when the standby connects it would get told what parameters to use by the master as part of the handshake. It would have to work exactly that way with standby.conf on the master also. Um ... so how does this standby know what master to connect to, what password to offer, etc? I don't think that pass down parameters after connecting is likely to cover anything but a small subset of the configuration problem. Huh? We have that stuff already. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Path question
On Sep 23, 2010, at 3:29 PM, Robert Haas wrote: On Tue, Sep 21, 2010 at 12:29 AM, David Fetter da...@fetter.org wrote: On Mon, Sep 20, 2010 at 10:57:00PM -0400, Robert Haas wrote: 2010/9/3 Hans-Jürgen Schönig h...@cybertec.at: On Sep 2, 2010, at 1:20 AM, Robert Haas wrote: I agree. Explicit partitioning may open up some additional optimization possibilities in certain cases, but Merge Append is more general and extremely valuable in its own right. we have revised greg's wonderful work and ported the entire thing to head. it solves the problem of merge_append. i did some testing earlier on today and it seems most important cases are working nicely. First, thanks for merging this up to HEAD. I took a look through this patch tonight, and the previous reviews thereof that I was able to find, most notably Tom's detailed review on 2009-07-26. I'm not sure whether or not it's accidental that this didn't get added to the CF, It's because I missed putting it in, and oversight I've corrected. If we need to bounce it on to the next one, them's the breaks. [points elided] 7. I think there's some basic code cleanup needed here, also: comment formatting, variable naming, etc. Hans-Jürgen, Will you be able to get to this in the next couple of days? I don't see a response to this which I assume means no - I'm going to take a crack at fixing some of these issues. hello ... sorry for not getting back to you sooner. i am currently on the road for some days. we got the top 3 things fixed already. however, some code seems to be relying on a sorted list somewhere(???). we are in the process of sorting out most of the stuff. i guess we will have something done next week. sorry for the delay. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- 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] Configuring synchronous replication
Robert Haas robertmh...@gmail.com writes: Now, admittedly, in more complex topologies, and especially if you're using configuration options that pertain to the behavior of disconnected standbys (e.g. wait for them, or retain WAL for them), you're going to need to adjust the configs. But I think that's likely to be true anyway, even with a catalog. If A is doing sync rep and waiting for B even when B is disconnected, and the machines switch roles, it's hard to see how any configuration isn't going to need some adjustment. One thing that's nice about the flat file system is that you can make the configuration changes on the new master before you promote it Actually, that's the killer argument in this whole thing. If the configuration information is in a system catalog, you can't change it without the master being up and running. Let us suppose for example that you've configured hard synchronous replication such that the master can't commit without slave acks. Now your slaves are down and you'd like to change that setting. Guess what. 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] Configuring synchronous replication
Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Um ... so how does this standby know what master to connect to, what password to offer, etc? I don't think that pass down parameters after connecting is likely to cover anything but a small subset of the configuration problem. Huh? We have that stuff already. Oh, I thought part of the objective here was to try to centralize that stuff. If we're assuming that slaves will still have local replication configuration files, then I think we should just add any necessary info to those files and drop this entire conversation. We're expending a tremendous amount of energy on something that won't make any real difference to the overall complexity of configuring a replication setup. AFAICS the only way you make a significant advance in usability is if you can centralize all the configuration information in some fashion. 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] Configuring synchronous replication
On Thu, Sep 23, 2010 at 1:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 12:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Um ... so how does this standby know what master to connect to, what password to offer, etc? I don't think that pass down parameters after connecting is likely to cover anything but a small subset of the configuration problem. Huh? We have that stuff already. Oh, I thought part of the objective here was to try to centralize that stuff. If we're assuming that slaves will still have local replication configuration files, then I think we should just add any necessary info to those files and drop this entire conversation. We're expending a tremendous amount of energy on something that won't make any real difference to the overall complexity of configuring a replication setup. AFAICS the only way you make a significant advance in usability is if you can centralize all the configuration information in some fashion. Well, it's quite fanciful to suppose that the slaves aren't going to need to have local configuration for how to connect to the master. The configuration settings we're talking about here are the things that affect either the behavior of the master-slave system as a unit (like what kind of ACK the master needs to get from the slave before ACKing the commit back to the user) or the master alone (like tracking how much WAL needs to be retained for a particular disconnected slave, rather than as presently always retaining a fixed amount). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Git cvsserver serious issue
On Thu, Sep 23, 2010 at 11:49 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Magnus Hagander mag...@hagander.net writes:
On Thu, Sep 23, 2010 at 17:32, Andrew Dunstan and...@dunslane.net wrote:
Are we sure that's going to stop the DOS issue?
As long as it's done right, I don't see how it wouldn't.
There might be a cleaner way to do it, but after a moment's inspection
of the script, I'd be inclined to just hack GITCVS::updater-new() to
throw error if $module is neither master nor REL\d_\d_STABLE.
Keep in mind of course that I'm a lousy Perl coder.
Here's a quick change that will allow you to specifig a modules in
the gitcvs section to export, like:
[gitcvs]
enabled=1
modules=master,REL9_0_STABLE,REL8_4_STABLE
--- git-cvsserver.orig 2010-09-23 12:03:06.0 -0400
+++ git-cvsserver 2010-09-23 13:16:53.0 -0400
@@ -2771,6 +2771,12 @@
die Git repo '$self-{git_path}' doesn't exist unless ( -d
$self-{git_path} );
+if (defined $cfg-{gitcvs}{modules})
+{
+$log-debug(Limitting modules: . $cfg-{gitcvs}{modules});
+die Invalid module $module unless map {/^ *$module$/}
split(',', $cfg-{gitcvs}{modules});
+}
+
$self-{dbdriver} = $cfg-{gitcvs}{$state-{method}}{dbdriver} ||
$cfg-{gitcvs}{dbdriver} || SQLite;
$self-{dbname} = $cfg-{gitcvs}{$state-{method}}{dbname} ||
--
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] Path question
2010/9/23 Hans-Jürgen Schönig h...@cybertec.at: sorry for not getting back to you sooner. i am currently on the road for some days. we got the top 3 things fixed already. however, some code seems to be relying on a sorted list somewhere(???). we are in the process of sorting out most of the stuff. i guess we will have something done next week. Oh, cool. Is it possible for you to post your WIP any sooner? I've been looking at #4 today. Further details to follow after I finish beating my head against a wall. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Why is time with timezone 12 bytes?
On 9/22/10 6:00 PM, Tom Lane wrote: I think you missed the point of my response, which is that there are easily 106 more-pressing things to work on than the size of timetz. Do you know of any actual use cases for it? It would be a good project to add to the list of easy TODOs to get started with. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] Why is time with timezone 12 bytes?
Josh Berkus wrote: On 9/22/10 6:00 PM, Tom Lane wrote: I think you missed the point of my response, which is that there are easily 106 more-pressing things to work on than the size of timetz. Do you know of any actual use cases for it? It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Configuring synchronous replication
On Thu, 2010-09-23 at 16:18 +0300, Heikki Linnakangas wrote: There's a program called pg_readahead somewhere on pgfoundry by NTT that will help if it's the single-threadedness of I/O. Before handing the WAL file to the server, it scans it through and calls posix_fadvise for all the blocks that it touches. When the server then replays it, the data blocks are already being fetched by the OS, using the whole RAID array. That sounds useful, thanks for the hint ! But couldn't this also be directly built in to WAL recovery process ? It would probably help a lot for recovering from a crash too. We did have recently a crash and it took hours to recover. I will try it out as soon as I get the time to set it up... [searching pgfoundry] Unfortunately I can't find it, and google is also not very helpful. Do you happen to have some links to it ? Cheers, Csaba. -- 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] Configuring synchronous replication
On Thu, 2010-09-23 at 11:43 -0400, Tom Lane wrote: What other problems are there that mean we *must* have a file? Well, for one thing, how do you add a new slave? If its configuration comes from a system catalog, it seems that it has to already be replicating before it knows what its configuration is. Or the slave gets a connection string to the master, and reads the configuration from there - it has to connect there anyway... The ideal bootstrap for a slave creation would be: get the params to connect to the master + the replica ID, and the rest should be done by connecting to the master and getting all the needed thing from there, including configuration. Maybe you see some merit for this idea: it wouldn't hurt to get the interfaces done so that the master could be impersonated by some WAL repository serving a PITR snapshot, and that the same WAL repository could connect as a slave to the master and instead of recovering the WAL stream, archive it. Such a WAL repository would possibly connect to multiple masters and could also get regularly snapshots too. This would provide a nice complement to WAL replication as PITR solution using the same protocols as the WAL standby. I have no idea if this would be easy to implement or useful for anybody. Cheers, Csaba. -- 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] Latch implementation
Attached is the current implementation redone with poll. It lands at
around 10.5 usecs, right above pipe, but better than the current
implementation.
As to the other questions: yes, this would matter for sync replication.
Cosider an enterprise use case with 10Gb network SSDs (not at all
uncommon): a 10Gb network can do a roundtrip with the commitlog in 10
usecs, and SSDs have write latency 50 usec. Now if the latch takes tens
of usescs (this stuff scales somewhat with the number of processes, my
data is all with 2 processes), that becomes a very significant part of the
net commit latency. So I'd think this is worth fixing.
Thanks,
--Ganesh
On Thu, 23 Sep 2010, Simon Riggs wrote:
Date: Thu, 23 Sep 2010 06:56:38 -0700
From: Simon Riggs si...@2ndquadrant.com
To: Ganesh Venkitachalam gan...@vmware.com
Cc: pgsql-hackers@postgresql.org pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Latch implementation
On Wed, 2010-09-22 at 13:31 -0700, Ganesh Venkitachalam-1 wrote:
Hi,
I've been playing around with measuring the latch implementation in 9.1,
and here are the results of a ping-pong test with 2 processes signalling
and waiting on the latch. I did three variations (linux 2.6.18, nehalem
processor).
One is the current one.
The second is built on native semaphors on linux. This one cannot
implement WaitLatchOrSocket, there's no select involved.
That looks interesting. If we had a need for a latch that would not need
to wait on a socket as well, this would be better. In sync rep, we
certainly do. Thanks for measuring this.
Question is: in that case would we use latches or a PGsemaphore?
If the answer is latch then we could just have an additional boolean
option when we request InitLatch() to see what kind of latch we want.
The third is an implementation based on pipe() and poll. Note: in its
current incarnation it's essentially a hack to measure performance, it's
not usable in postgres, this assumes all latches are created before any
process is forked. We'd need to use mkfifo to sort that out if we really
want to go this route, or similar.
- Current implementation: 1 pingpong is avg 15 usecs
- Pipe+poll: 9 usecs
- Semaphore: 6 usecs
Pipe+poll not worth it then.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
#include sys/file.h
#include sys/stat.h
#include sys/ipc.h
#include sys/shm.h
#include sys/time.h
#include sys/stat.h
#include sys/ipc.h
#include sys/sem.h
#include unistd.h
#include stdlib.h
#include signal.h
#include errno.h
int nIter = 100;
#define LATCH_TIMEOUT 5000L
#include latch.h
#include unix_latch.c
int selfLatch, otherLatch;
Latch *latchArray;
#ifdef LINUX_SEM
int semId = -1;
#endif
void DoWork(int selfLatch);
void
sigusr1handler(int n)
{
DEBUG(sigusr handler\n);
latch_sigusr1_handler();
}
main()
{
int child;
int i;
int shmid;
for (i = 0; i 1000; i++) {
shmid = shmget(6500 + i, sizeof(Latch) * 2, IPC_CREAT | IPC_EXCL|0x1ff);
if (shmid 0) {
DEBUG(shmget error %d %d\n, shmid, errno);
if (i == 999) {
printf(Can't get shm, aborting test\n);
exit (1);
}
} else {
break;
}
}
latchArray = shmat(shmid, NULL, 0);
if ((long)latchArray 0) {
printf(shmat error %ld %d\n, (long)latchArray, errno);
exit(1);
}
InitSharedLatch(latchArray[0]);
InitSharedLatch(latchArray[1]);
child = fork();
if (child 0) {
printf(fork error %d %d\n, child, errno);
exit(1);
}
MyProcPid = getpid();
signal(SIGUSR1, sigusr1handler);
DoWork(child != 0);
}
static int
WaitForOther(int latch)
{
int ret;
Latch *l = latchArray[latch];
DEBUG(Wait %p\n, l);
ret = WaitLatch(l, LATCH_TIMEOUT);
ResetLatch(l);
return ret;
}
static void
SignalOther(int latch)
{
Latch *l = latchArray[latch];
DEBUG(Signal %p\n, l);
SetLatch(l);
}
void
DoWork(int l)
{
int i;
struct timeval tv1, tv2;
struct timezone tz;
float diff;
selfLatch = l, otherLatch = selfLatch ^ 1;
Latch *self = latchArray[selfLatch];
Latch *other = latchArray[otherLatch ^ 1];
OwnLatch(self);
sleep(2); /* Cheat: pseudo-barrier. */
printf(Start Test:);
#if defined(LINUX_SEM)
printf(Using semaphores\n);
#elif defined(PIPE)
printf(Using pipe\n);
#else
printf(Using signal\n);
#endif
/* Set one of the latches in the beginning. */
if (selfLatch == 0) {
SignalOther(otherLatch);
}
gettimeofday(tv1, tz);
for (i = 0; i nIter; i++) {
if (WaitForOther(selfLatch) != 1) {
printf(BUG BUG BUG\n);
exit(1);
}
SignalOther(otherLatch);
}
gettimeofday(tv2, tz);
diff = (tv2.tv_sec - tv1.tv_sec) * 100 + (tv2.tv_usec - tv1.tv_usec);
printf(%ld iterations took %.2f sec (%.2f usec/iter)\n,
nIter, diff/100, diff/nIter);
}
Re: [HACKERS] Why is time with timezone 12 bytes?
On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: On 9/22/10 6:00 PM, Tom Lane wrote: I think you missed the point of my response, which is that there are easily 106 more-pressing things to work on than the size of timetz. Do you know of any actual use cases for it? It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. Which is a big except. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] security label support, revised
On Thu, Sep 23, 2010 at 10:21 AM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: Most of the contents of the new documentation section on external security providers seemed irrelevant to me, which I guess I can only blame myself for since I was the one who asked for that section to be created, and I didn't specify what it should contain all that well. I took a try at rewriting it to be more on-topic, but it didn't amount to much so I ended up just ripping that part out completely. Do we have a place where we actually document hooks today..? Seems like we should and that'd be a good place to put the few necessary comments regarding these. We do not. Whether or not we should, I'm not sure. There are a few other problems. First, there's no psql support of any kind. Now, this is kind of a corner-case feature: so maybe we don't really need it. And as I mentioned on another thread, there aren't a lot of good letters left for backslash-d commands. One thought would be to add it to \dp or have a \dp+. That only works for table-ish things, though. So I'd be just as happy to add a system view along the lines I previously proposed for comments and call it good. I think that regardless of psql and \d, we should have a sensible system view for it. That's fine with me. The one I wrote for comments can probably be adapted pretty easily. Second, there are no regression tests. It's a bit tricky to think about how to crack that nut because this feature is somewhat unusual in that it can't be used without loading an appropriate loadable module. I'm wondering if we can ship a dummy_seclabel contrib module that can be loaded during the regression test run and then run various tests using that, but I'm not quite sure what the best way to set that up is. SECURITY LABEL is a core feature, so it would be nice to test it in the core regression tests... but maybe that's too complicated to get working, and we should just test it from the contrib module. The first set of regression tests could simply run the SECURITY LABEL commands and then check the results in the catalog. If some kind of psql support is included, it could test that also. That doesn't check that the hooks are called at the right time and with the right data, so I agree with the suggestion to have dummy contrib modules (or something) to do that generically for all our hooks, but I don't think we've got anything like that today..? If we do, then we should model it off whatever's there now. Perhaps we can look at how to do it comprehensively for all hooks.. The point is that SECURITY LABEL, as coded, will fail utterly unless there is a label provider loaded. So you can't actually run it and check the results in the catalog without loading a contrib module. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Configuring synchronous replication
On 23/09/10 20:03, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 12:52 PM, Tom Lanet...@sss.pgh.pa.us wrote: Um ... so how does this standby know what master to connect to, what password to offer, etc? I don't think that pass down parameters after connecting is likely to cover anything but a small subset of the configuration problem. Huh? We have that stuff already. Oh, I thought part of the objective here was to try to centralize that stuff. If we're assuming that slaves will still have local replication configuration files, then I think we should just add any necessary info to those files and drop this entire conversation. We're expending a tremendous amount of energy on something that won't make any real difference to the overall complexity of configuring a replication setup. AFAICS the only way you make a significant advance in usability is if you can centralize all the configuration information in some fashion. If you want the behavior where the master doesn't acknowledge a commit to the client until the standby (or all standbys, or one of them etc.) acknowledges it, even if the standby is not currently connected, the master needs to know what standby servers exist. *That's* why synchronous replication needs a list of standby servers in the master. If you're willing to downgrade to a mode where commit waits for acknowledgment only from servers that are currently connected, then you don't need any new configuration files. But that's not what I call synchronous replication, it doesn't give you the guarantees that textbook synchronous replication does. (Gosh, I wish the terminology was more standardized in this area) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] wip: functions median and percentile
2010/9/23 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/22 Hitoshi Harada umi.tan...@gmail.com: 2010/9/22 Pavel Stehule pavel.steh...@gmail.com: Hello I found probably hard problem in cooperation with window functions :( maybe I was confused. I found a other possible problems. The problem with median function is probably inside a final function implementation. Actually we request possibility of repetitive call of final function. But final function call tuplesort_end function and tuplesort_performsort. These function changes a state of tuplesort. The most basic question is who has to call tuplesort_end function and when? Reading the comment in array_userfuncs.c, array_agg_finalfn() doesn't clean up its internal state at all and tells it's the executor's responsibility to clear memory. It is allowed since ArrayBuildState is only in-memory state. In the other hand, TupleSort should be cleared by calling tuplesort_end() if it has tapeset member (on file based sort) to close physical files. So 2 or 3 ways to go in my mind: 1. call tuplesort_begin_datum with INT_MAX workMem rather than the global work_mem, to avoid it spills out sort state to files. It may sounds dangerous, but actually memory exhausting can happen in array_agg() as well. 2. add TupleSort an argument that tells not to use file at all. This results in the same as #1 but more generic approach. 3. don't use tuplesort in median() but implement its original sort management. This looks quite redundant and like maintenance problem. #2 sounds like the best in generic and consistent way. The only point is whether the change is worth for implementing median() as it's very system-wide common fundamentals. Other options? Regards, -- 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] Why is time with timezone 12 bytes?
Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. Which is a big except. Yeah. That constraint is what leads me to think that the return on effort is just not worth it. Maybe we should file this in the category of things to look at next time we break on-disk compatibility. 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] Why is time with timezone 12 bytes?
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. Which is a big except. Yeah. That constraint is what leads me to think that the return on effort is just not worth it. Maybe we should file this in the category of things to look at next time we break on-disk compatibility. Yes, I would like to see such a category on the TODO list. Should I do it? FYI, I am please at the lack of serious problems with pg_upgrade. People are obviously using it because the are filing bug reports, but none of them are serious, and relate to pilot error or odd configurations. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] security label support, revised
* Robert Haas (robertmh...@gmail.com) wrote: The point is that SECURITY LABEL, as coded, will fail utterly unless there is a label provider loaded. So you can't actually run it and check the results in the catalog without loading a contrib module. Urgh, yes, point. Well, we could test that it errors out correctly. :) Another thought might be to allow the check if a module is loaded before doing things to be a postgresql.conf option that is disabled in the regression testing.. If you can modify postgresql.conf you can remove the module anyway.. Interesting question as to if we should auto-fail queries against objects which have labels when no security module is loaded. Have we discussed that yet? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] .gitignore files, take two
On Tue, Sep 21, 2010 at 22:11, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Magnus Hagander mag...@hagander.net writes: Do we know what the exact pattern would be for .sl and .dylib? Are they following the same basic pattern of .sl.major.minor? Yes, they'll be just the same --- Makefile.shlib treats all those extensions alike. I take that back. Darwin does things differently, bless their pointy little heads: DLSUFFIX = .dylib shlib = lib$(NAME).$(SO_MAJOR_VERSION).$(SO_MINOR_VERSION)$(DLSUFFIX) So it looks like *.dylib is sufficient and we don't need anything with numbers afterwards for that variant. Ok. Just to be clear, here's what I have now: -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ diff --git a/.gitignore b/.gitignore new file mode 100644 index 000..1be11e8 --- /dev/null +++ b/.gitignore @@ -0,0 +1,19 @@ +# Global excludes across all subdirectories +*.o +*.so +*.so.[0-9] +*.so.[0-9].[0-9] +*.sl +*.sl.[0-9] +*.sl.[0-9].[0-9] +*.dylib +*.dll +*.a +*.mo +objfiles.txt +.deps/ + +# Local excludes in root directory +/GNUmakefile +/config.log +/config.status diff --git a/contrib/adminpack/.gitignore b/contrib/adminpack/.gitignore new file mode 100644 index 000..07d3199 --- /dev/null +++ b/contrib/adminpack/.gitignore @@ -0,0 +1 @@ +adminpack.sql diff --git a/contrib/btree_gin/.gitignore b/contrib/btree_gin/.gitignore new file mode 100644 index 000..8e9f4c4 --- /dev/null +++ b/contrib/btree_gin/.gitignore @@ -0,0 +1 @@ +btree_gin.sql diff --git a/contrib/btree_gist/.gitignore b/contrib/btree_gist/.gitignore new file mode 100644 index 000..cc855cf --- /dev/null +++ b/contrib/btree_gist/.gitignore @@ -0,0 +1 @@ +btree_gist.sql diff --git a/contrib/chkpass/.gitignore b/contrib/chkpass/.gitignore new file mode 100644 index 000..2427d62 --- /dev/null +++ b/contrib/chkpass/.gitignore @@ -0,0 +1 @@ +chkpass.sql diff --git a/contrib/citext/.gitignore b/contrib/citext/.gitignore new file mode 100644 index 000..cb8c4d9 --- /dev/null +++ b/contrib/citext/.gitignore @@ -0,0 +1 @@ +citext.sql diff --git a/contrib/cube/.cvsignore b/contrib/cube/.cvsignore deleted file mode 100644 index 19ecc85..000 --- a/contrib/cube/.cvsignore +++ /dev/null @@ -1,2 +0,0 @@ -cubeparse.c -cubescan.c diff --git a/contrib/cube/.gitignore b/contrib/cube/.gitignore new file mode 100644 index 000..3d15800 --- /dev/null +++ b/contrib/cube/.gitignore @@ -0,0 +1,3 @@ +cubeparse.c +cubescan.c +cube.sql diff --git a/contrib/dblink/.gitignore b/contrib/dblink/.gitignore new file mode 100644 index 000..c5f6774 --- /dev/null +++ b/contrib/dblink/.gitignore @@ -0,0 +1 @@ +dblink.sql diff --git a/contrib/dict_int/.gitignore b/contrib/dict_int/.gitignore new file mode 100644 index 000..b1fe21b --- /dev/null +++ b/contrib/dict_int/.gitignore @@ -0,0 +1 @@ +dict_int.sql diff --git a/contrib/dict_xsyn/.gitignore b/contrib/dict_xsyn/.gitignore new file mode 100644 index 000..f639d69 --- /dev/null +++ b/contrib/dict_xsyn/.gitignore @@ -0,0 +1 @@ +dict_xsyn.sql diff --git a/contrib/earthdistance/.gitignore b/contrib/earthdistance/.gitignore new file mode 100644 index 000..35e7437 --- /dev/null +++ b/contrib/earthdistance/.gitignore @@ -0,0 +1 @@ +earthdistance.sql diff --git a/contrib/fuzzystrmatch/.gitignore b/contrib/fuzzystrmatch/.gitignore new file mode 100644 index 000..8006def --- /dev/null +++ b/contrib/fuzzystrmatch/.gitignore @@ -0,0 +1 @@ +fuzzystrmatch.sql diff --git a/contrib/hstore/.gitignore b/contrib/hstore/.gitignore new file mode 100644 index 000..acaeaa1 --- /dev/null +++ b/contrib/hstore/.gitignore @@ -0,0 +1 @@ +hstore.sql diff --git a/contrib/intarray/.gitignore b/contrib/intarray/.gitignore new file mode 100644 index 000..17a6d14 --- /dev/null +++ b/contrib/intarray/.gitignore @@ -0,0 +1 @@ +_int.sql diff --git a/contrib/isn/.gitignore b/contrib/isn/.gitignore new file mode 100644 index 000..3352289 --- /dev/null +++ b/contrib/isn/.gitignore @@ -0,0 +1 @@ +isn.sql diff --git a/contrib/lo/.gitignore b/contrib/lo/.gitignore new file mode 100644 index 000..4024934 --- /dev/null +++ b/contrib/lo/.gitignore @@ -0,0 +1 @@ +lo.sql diff --git a/contrib/ltree/.gitignore b/contrib/ltree/.gitignore new file mode 100644 index 000..2c043e6 --- /dev/null +++ b/contrib/ltree/.gitignore @@ -0,0 +1 @@ +ltree.sql diff --git a/contrib/oid2name/.gitignore b/contrib/oid2name/.gitignore new file mode 100644 index 000..81a1560 --- /dev/null +++ b/contrib/oid2name/.gitignore @@ -0,0 +1 @@ +oid2name diff --git a/contrib/pageinspect/.gitignore b/contrib/pageinspect/.gitignore new file mode 100644 index 000..6150c6b --- /dev/null +++ b/contrib/pageinspect/.gitignore @@ -0,0 +1 @@ +pageinspect.sql diff --git a/contrib/pg_archivecleanup/.gitignore b/contrib/pg_archivecleanup/.gitignore new file mode 100644 index 000..51d2443 --- /dev/null +++ b/contrib/pg_archivecleanup/.gitignore @@ -0,0
Re: [HACKERS] wip: functions median and percentile
2010/9/23 Hitoshi Harada umi.tan...@gmail.com: 2010/9/23 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/22 Hitoshi Harada umi.tan...@gmail.com: 2010/9/22 Pavel Stehule pavel.steh...@gmail.com: Hello I found probably hard problem in cooperation with window functions :( maybe I was confused. I found a other possible problems. The problem with median function is probably inside a final function implementation. Actually we request possibility of repetitive call of final function. But final function call tuplesort_end function and tuplesort_performsort. These function changes a state of tuplesort. The most basic question is who has to call tuplesort_end function and when? Reading the comment in array_userfuncs.c, array_agg_finalfn() doesn't clean up its internal state at all and tells it's the executor's responsibility to clear memory. It is allowed since ArrayBuildState is only in-memory state. In the other hand, TupleSort should be cleared by calling tuplesort_end() if it has tapeset member (on file based sort) to close physical files. So 2 or 3 ways to go in my mind: it is little bit worse - we cannot to call tuplesort_performsort repetitive. 1. call tuplesort_begin_datum with INT_MAX workMem rather than the global work_mem, to avoid it spills out sort state to files. It may sounds dangerous, but actually memory exhausting can happen in array_agg() as well. 2. add TupleSort an argument that tells not to use file at all. This results in the same as #1 but more generic approach. 3. don't use tuplesort in median() but implement its original sort management. This looks quite redundant and like maintenance problem. #2 sounds like the best in generic and consistent way. The only point is whether the change is worth for implementing median() as it's very system-wide common fundamentals. Other options? #4 block median under window clause #5 use a C array instead tuplesort under window clause. It is very unpractical to use a windows clauses with large datasets, so it should not be a problem. More, this can be very quick, because for C array we can use a qsort function. Now I prefer #5 - it can be fast for using inside windows clause and safe when window clause will not be used. Regards Pavel Regards, -- 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] Why is time with timezone 12 bytes?
On Thu, Sep 23, 2010 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. Which is a big except. Yeah. That constraint is what leads me to think that the return on effort is just not worth it. Maybe we should file this in the category of things to look at next time we break on-disk compatibility. I'm worried about how we're going to manage that. First, as pg_upgrade becomes more mature, the penalty for breaking on-disk compatibility gets a LOT bigger. I'd like to think that the next time we break on-disk compatibility means approximately never, or at least not for a very long time. Second, if we do decide to break it, how and when will we make that decision? Are we just going to decide to break it when we run into a feature that we really want that can't be had any other way? If we want to make breaking on-disk compatibility something that only happens every 5 years or so, we had better give people - I don't know, a year's notice - so that we can really knock out everything people have any interest in fixing in one release. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] security label support, revised
On Thu, Sep 23, 2010 at 2:06 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: The point is that SECURITY LABEL, as coded, will fail utterly unless there is a label provider loaded. So you can't actually run it and check the results in the catalog without loading a contrib module. Urgh, yes, point. Well, we could test that it errors out correctly. :) Indeed. Another thought might be to allow the check if a module is loaded before doing things to be a postgresql.conf option that is disabled in the regression testing.. If you can modify postgresql.conf you can remove the module anyway.. That might work, although I'm not sure whether it's any easier that getting a contrib module to run during the regression tests. I think we're testing LOAD in there already somewhere, so... Interesting question as to if we should auto-fail queries against objects which have labels when no security module is loaded. Have we discussed that yet? My feeling is that we should do what the existing code does, namely, bounce the request immediately if the relevant label provider can't be found. It isn't as if people can't modify the labels anyway in that case, by messing with pg_seclabel by hand, but I don't really see the need to spend extra code trying to make this work sensibly when I'm not sure there's any real sensible behavior. I think that people who write these modules will need to include a mechanism to disable checking, hedged about with some appropriate protections. Isn't that what SE-Linux permissive mode is for? (And you could possibly have a similar concept within the module, just local to PG, driven off a GUC; of course the assign_hook can ask SE-Linux whether it's OK to enable PG-only permissive mode.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] wip: functions median and percentile
On Thu, Sep 23, 2010 at 08:27:38PM +0200, Pavel Stehule wrote: 2010/9/23 Hitoshi Harada umi.tan...@gmail.com: 2010/9/23 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/22 Hitoshi Harada umi.tan...@gmail.com: 2010/9/22 Pavel Stehule pavel.steh...@gmail.com: Hello I found probably hard problem in cooperation with window functions :( maybe I was confused. I found a other possible problems. The problem with median function is probably inside a final function implementation. Actually we request possibility of repetitive call of final function. But final function call tuplesort_end function and tuplesort_performsort. These function changes a state of tuplesort. The most basic question is who has to call tuplesort_end function and when? Reading the comment in array_userfuncs.c, array_agg_finalfn() doesn't clean up its internal state at all and tells it's the executor's responsibility to clear memory. It is allowed since ArrayBuildState is only in-memory state. In the other hand, TupleSort should be cleared by calling tuplesort_end() if it has tapeset member (on file based sort) to close physical files. So 2 or 3 ways to go in my mind: it is little bit worse - we cannot to call tuplesort_performsort repetitive. 1. call tuplesort_begin_datum with INT_MAX workMem rather than the global work_mem, to avoid it spills out sort state to files. It may sounds dangerous, but actually memory exhausting can happen in array_agg() as well. 2. add TupleSort an argument that tells not to use file at all. This results in the same as #1 but more generic approach. 3. don't use tuplesort in median() but implement its original sort management. This looks quite redundant and like maintenance problem. #2 sounds like the best in generic and consistent way. The only point is whether the change is worth for implementing median() as it's very system-wide common fundamentals. Other options? #4 block median under window clause #5 use a C array instead tuplesort under window clause. It is very unpractical to use a windows clauses with large datasets, so it should not be a problem. More, this can be very quick, because for C array we can use a qsort function. Now I prefer #5 - it can be fast for using inside windows clause and safe when window clause will not be used. If there's some way to do this using the same code in the windowing and non-windowing case, that would be much, much better from an architectural point of view. Single Point of Truth and all that. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Easy way to verify gitignore files?
Tom Lane t...@sss.pgh.pa.us writes: However, it seems that git isn't so willing to tell you about gitignore patterns that cover too much, i.e. match files that are already in the repository. It seems to me that git-ls-files is what you want here : http://www.kernel.org/pub/software/scm/git/docs/git-ls-files.html git ls-files -i --exclude-standard Regards, -- dim -- 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: functions median and percentile
2010/9/23 David Fetter da...@fetter.org: On Thu, Sep 23, 2010 at 08:27:38PM +0200, Pavel Stehule wrote: 2010/9/23 Hitoshi Harada umi.tan...@gmail.com: 2010/9/23 Pavel Stehule pavel.steh...@gmail.com: Hello 2010/9/22 Hitoshi Harada umi.tan...@gmail.com: 2010/9/22 Pavel Stehule pavel.steh...@gmail.com: Hello I found probably hard problem in cooperation with window functions :( maybe I was confused. I found a other possible problems. The problem with median function is probably inside a final function implementation. Actually we request possibility of repetitive call of final function. But final function call tuplesort_end function and tuplesort_performsort. These function changes a state of tuplesort. The most basic question is who has to call tuplesort_end function and when? Reading the comment in array_userfuncs.c, array_agg_finalfn() doesn't clean up its internal state at all and tells it's the executor's responsibility to clear memory. It is allowed since ArrayBuildState is only in-memory state. In the other hand, TupleSort should be cleared by calling tuplesort_end() if it has tapeset member (on file based sort) to close physical files. So 2 or 3 ways to go in my mind: it is little bit worse - we cannot to call tuplesort_performsort repetitive. 1. call tuplesort_begin_datum with INT_MAX workMem rather than the global work_mem, to avoid it spills out sort state to files. It may sounds dangerous, but actually memory exhausting can happen in array_agg() as well. 2. add TupleSort an argument that tells not to use file at all. This results in the same as #1 but more generic approach. 3. don't use tuplesort in median() but implement its original sort management. This looks quite redundant and like maintenance problem. #2 sounds like the best in generic and consistent way. The only point is whether the change is worth for implementing median() as it's very system-wide common fundamentals. Other options? #4 block median under window clause #5 use a C array instead tuplesort under window clause. It is very unpractical to use a windows clauses with large datasets, so it should not be a problem. More, this can be very quick, because for C array we can use a qsort function. Now I prefer #5 - it can be fast for using inside windows clause and safe when window clause will not be used. If there's some way to do this using the same code in the windowing and non-windowing case, that would be much, much better from an architectural point of view. Single Point of Truth and all that. We can have a median with support a window clause, but limited to work_mem, or we can have a unlimited median, but without window clause. I think, I am able to minimalize a code duplicity - just to define some envelope over tuplesort. The unique code isn't possible there - minimally now we have a two variants - one for numeric result and second for double. But it is usual - try to look how much AVG functions are in core. Regards Pavel Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 is time with timezone 12 bytes?
Excerpts from Robert Haas's message of jue sep 23 14:33:06 -0400 2010: I'm worried about how we're going to manage that. First, as pg_upgrade becomes more mature, the penalty for breaking on-disk compatibility gets a LOT bigger. I'd like to think that the next time we break on-disk compatibility means approximately never, or at least not for a very long time. Second, if we do decide to break it, how and when will we make that decision? I liked your earlier suggestion: if somebody wants to pg_upgrade, he needs to go to the latest minor release of their branch, run some command to upgrade the on-disk format (say ALTER TABLE / SET TYPE), and *then* upgrade. Now if it was workable to handle floating-point datetimes to integer datetimes this way, it would be excellent. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
pg_upgrade pain; was Re: [HACKERS] Why is time with timezone 12 bytes?
Robert Haas wrote: On Thu, Sep 23, 2010 at 1:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian br...@momjian.us wrote: Josh Berkus wrote: It would be a good project to add to the list of easy TODOs to get started with. Except for the pg_upgrade issue. Which is a big except. Yeah. ?That constraint is what leads me to think that the return on effort is just not worth it. ?Maybe we should file this in the category of things to look at next time we break on-disk compatibility. I'm worried about how we're going to manage that. First, as pg_upgrade becomes more mature, the penalty for breaking on-disk compatibility gets a LOT bigger. I'd like to think that the next time we break on-disk compatibility means approximately never, or at least not for a very long time. Second, if we do decide to break it, how and when will we make that decision? Are we just going to decide to break it when we run into a feature that we really want that can't be had any other way? If we want to make breaking on-disk compatibility something that only happens every 5 years or so, we had better give people - I don't know, a year's notice - so that we can really knock out everything people have any interest in fixing in one release. Let me come clean and explain that I am worried pg_upgrade has limited our ability to make data format changes. pg_upgrade is much more accepted now than I think anyone expected a year ago. Our users are now going to complain if pg_upgrade upgrades are not supported in future releases, which eventually is going to cause us problems. I think having binary upgrades for 9.0 was a big features, and got mentioned in the press release, but let's not kid ourselves that we aren't going down a road that might be paved with pain. We have explored all sorts of ideas to mitigate the pain, like new data type oids and reading (writing?) old data format pages, but that is all untested territory. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Serializable Snapshot Isolation
On 23/09/10 18:08, Kevin Grittner wrote: Less important than any of the above, but still significant in my book, I fear that conflict recording and dangerous structure detection could become very convoluted and fragile if we eliminate this structure for committed transactions. Conflicts among specific sets of transactions are the linchpin of this whole approach, and I think that without an object to represent each one for the duration for which it is significant is dangerous. Inferring information from a variety of sources feels wrong to me. Ok, so if we assume that we must keep all the information we have now, let me try again with that requirement. My aim is still to put an upper bound on the amount of shared memory required, regardless of the number of committed but still interesting transactions. Cahill's thesis mentions that the per-transaction information can be kept in a table like this: txnID beginTime commitTime inConf outConf 1001000 1100 N Y 1011000 1500 N N 1021200 N/A Y N That maps nicely to a SLRU table, truncated from the top as entries become old enough, and appended to the end. In addition to that, we need to keep track of locks held by each transaction, in a finite amount of shared memory. For each predicate lock, we need to store the lock tag, and the list of transactions holding the lock. The list of transactions is where the problem is, there is no limit on its size. Conveniently, we already have a way of representing an arbitrary set of transactions with a single integer: multi-transactions, in multixact.c. Now, we have a little issue in that read-only transactions don't have xids, and can't therefore be part of a multixid, but it could be used as a model to implement something similar for virtual transaction ids. Just a thought, not sure what the performance would be like or how much work such a multixid-like structure would be to implement.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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: pg_upgrade pain; was Re: [HACKERS] Why is time with timezone 12 bytes?
On Thu, 2010-09-23 at 15:20 -0400, Bruce Momjian wrote: decide to break it when we run into a feature that we really want that can't be had any other way? If we want to make breaking on-disk compatibility something that only happens every 5 years or so, we had better give people - I don't know, a year's notice - so that we can really knock out everything people have any interest in fixing in one release. Let me come clean and explain that I am worried pg_upgrade has limited our ability to make data format changes. It is nice to see hackers finally realizing that this is true (and required). pg_upgrade is much more accepted now than I think anyone expected a year ago. Our users are now going to complain if pg_upgrade upgrades are not supported in future releases, which eventually is going to cause us problems. us being -hackers yes, but it will only help the community. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Configuring synchronous replication
On Thu, 2010-09-23 at 13:07 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Now, admittedly, in more complex topologies, and especially if you're using configuration options that pertain to the behavior of disconnected standbys (e.g. wait for them, or retain WAL for them), you're going to need to adjust the configs. But I think that's likely to be true anyway, even with a catalog. If A is doing sync rep and waiting for B even when B is disconnected, and the machines switch roles, it's hard to see how any configuration isn't going to need some adjustment. Well, its not at all hard to see how that could be configured, because I already proposed a simple way of implementing parameters that doesn't suffer from those problems. My proposal did not give roles to named standbys and is symmetrical, so switchovers won't cause a problem. Earlier you argued that centralizing parameters would make this nice and simple. Now you're pointing out that we aren't centralizing this at all, and it won't be simple. We'll have to have a standby.conf set up that is customised in advance for each standby that might become a master. Plus we may even need multiple standby.confs in case that we have multiple nodes down. This is exactly what I was seeking to avoid and exactly what I meant when I asked for an analysis of the failure modes. This proposal is a configuration nightmare, no question, and that is not the right way to go if you want high availability that works when you need it to. One thing that's nice about the flat file system is that you can make the configuration changes on the new master before you promote it Actually, that's the killer argument in this whole thing. If the configuration information is in a system catalog, you can't change it without the master being up and running. Let us suppose for example that you've configured hard synchronous replication such that the master can't commit without slave acks. Now your slaves are down and you'd like to change that setting. Guess what. If we have standby registration and I respect that some people want it, a table seems to be the best place for them. In a table the parameters are passed through from master to slave automatically without needing to synchronize multiple files manually. They can only be changed on a master, true. But since they only effect the behaviour of a master (commits = writes) then that doesn't matter at all. As soon as you promote a new master you'll be able to change them again, if required. Configuration options that differ on each node, depending upon the current state of others nodes are best avoided. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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: pg_upgrade pain; was Re: [HACKERS] Why is time with timezone 12 bytes?
Joshua D. Drake wrote: On Thu, 2010-09-23 at 15:20 -0400, Bruce Momjian wrote: decide to break it when we run into a feature that we really want that can't be had any other way? If we want to make breaking on-disk compatibility something that only happens every 5 years or so, we had better give people - I don't know, a year's notice - so that we can really knock out everything people have any interest in fixing in one release. Let me come clean and explain that I am worried pg_upgrade has limited our ability to make data format changes. It is nice to see hackers finally realizing that this is true (and required). It is like credit card companies offering customer perks to encourage vendors to accept credit cards. It is not something vendors set out to do, but it becomes a customer disappointment if they don't comply. pg_upgrade is much more accepted now than I think anyone expected a year ago. Our users are now going to complain if pg_upgrade upgrades are not supported in future releases, which eventually is going to cause us problems. us being -hackers yes, but it will only help the community. Right. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] git cherry-pick timestamping issue
So my first attempt at using git cherry-pick didn't go so well. The commit in master looks like commit ee63981c1fe26299162b9c7f1218d7e3ef802409 Author: Tom Lane t...@sss.pgh.pa.us Date: Thu Sep 23 15:34:56 2010 -0400 Avoid sharing subpath list structure when flattening nested AppendRels. which is the correct timestamp, but all the back-patched ones look like commit 4e60212ab5a956bcba89cfd465f945a9c8969f27 Author: Tom Lane t...@sss.pgh.pa.us Date: Thu Sep 23 19:34:56 2010 -0400 Avoid sharing subpath list structure when flattening nested AppendRels. Apparently somebody's confused between local and GMT time somewhere in there. This is with a vanilla build of 1.7.2.3. Anybody else see this type of symptom? 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] git cherry-pick timestamping issue
On Thu, Sep 23, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Apparently somebody's confused between local and GMT time somewhere in there. Ouch. That rather sucks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Configuring synchronous replication
On Thu, Sep 23, 2010 at 3:46 PM, Simon Riggs si...@2ndquadrant.com wrote: Well, its not at all hard to see how that could be configured, because I already proposed a simple way of implementing parameters that doesn't suffer from those problems. My proposal did not give roles to named standbys and is symmetrical, so switchovers won't cause a problem. I know you proposed a way, but my angst is all around whether it was actually simple. I found it somewhat difficult to understand, so possibly other people might have the same problem. Earlier you argued that centralizing parameters would make this nice and simple. Now you're pointing out that we aren't centralizing this at all, and it won't be simple. We'll have to have a standby.conf set up that is customised in advance for each standby that might become a master. Plus we may even need multiple standby.confs in case that we have multiple nodes down. This is exactly what I was seeking to avoid and exactly what I meant when I asked for an analysis of the failure modes. If you're operating on the notion that no reconfiguration will be necessary when nodes go down, then we have very different notions of what is realistic. I think that copy the new standby.conf file in place is going to be the least of the fine admin's problems. One thing that's nice about the flat file system is that you can make the configuration changes on the new master before you promote it Actually, that's the killer argument in this whole thing. If the configuration information is in a system catalog, you can't change it without the master being up and running. Let us suppose for example that you've configured hard synchronous replication such that the master can't commit without slave acks. Now your slaves are down and you'd like to change that setting. Guess what. If we have standby registration and I respect that some people want it, a table seems to be the best place for them. In a table the parameters are passed through from master to slave automatically without needing to synchronize multiple files manually. They can only be changed on a master, true. But since they only effect the behaviour of a master (commits = writes) then that doesn't matter at all. As soon as you promote a new master you'll be able to change them again, if required. Configuration options that differ on each node, depending upon the current state of others nodes are best avoided. I think maybe you missed Tom's point, or else you just didn't respond to it. If the master is wedged because it is waiting for a standby, then you cannot commit transactions on the master. Therefore you cannot update the system catalog which you must update to unwedge it. Failing over in that situation is potentially a huge nuisance and extremely undesirable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Serializable Snapshot Isolation
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 23/09/10 18:08, Kevin Grittner wrote: Less important than any of the above, but still significant in my book, I fear that conflict recording and dangerous structure detection could become very convoluted and fragile if we eliminate this structure for committed transactions. Conflicts among specific sets of transactions are the linchpin of this whole approach, and I think that without an object to represent each one for the duration for which it is significant is dangerous. Inferring information from a variety of sources feels wrong to me. Ok, so if we assume that we must keep all the information we have now, let me try again with that requirement. My aim is still to put an upper bound on the amount of shared memory required, regardless of the number of committed but still interesting transactions. Cahill's thesis mentions that the per-transaction information can be kept in a table like this: txnID beginTime commitTime inConf outConf 1001000 1100 N Y 1011000 1500 N N 1021200 N/A Y N That maps nicely to a SLRU table, truncated from the top as entries become old enough, and appended to the end. Well, the inConf and outConf were later converted to pointers in Cahill's work, and our MVCC implementation doesn't let us use times quite that way -- we're using xmins and such, but I assume the point holds regardless of such differences. (I mostly mention it to avoid confusion for more casual followers of the thread.) In addition to that, we need to keep track of locks held by each transaction, in a finite amount of shared memory. For each predicate lock, we need to store the lock tag, and the list of transactions holding the lock. The list of transactions is where the problem is, there is no limit on its size. Conveniently, we already have a way of representing an arbitrary set of transactions with a single integer: multi-transactions, in multixact.c. Now, we have a little issue in that read-only transactions don't have xids, and can't therefore be part of a multixid, but it could be used as a model to implement something similar for virtual transaction ids. Just a thought, not sure what the performance would be like or how much work such a multixid-like structure would be to implement.. You're pointing toward some code I haven't yet laid eyes on, so it will probably take me a few days to really digest your suggestion and formulate an opinion. This is just to let you know I'm working on it. I really appreciate your attention to this. Thanks! -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is there a reason we can't copy from a view?
... other than it just got forgotten? dolszewski=# create view meh as select * from pg_tables; CREATE VIEW dolszewski=# copy meh to stdout; ERROR: cannot copy from view meh HINT: Try the COPY (SELECT ...) TO variant. dolszewski=# copy (select * from meh) to stdout; pg_catalogpg_statisticpostgres\Ntff pg_catalogpg_typepostgres\Ntff -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] is there a reason we can't copy from a view?
Josh Berkus j...@agliodbs.com writes: ... other than it just got forgotten? We've been over that ground before. IIRC the conclusion was that letting COPY TO pretend that a view is a table is a bad idea because then people would complain about COPY FROM not working for it. You can use COPY (select * from myview) TO ... so the functionality is available. 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] git cherry-pick timestamping issue
On Sep 23, 2010, at 1:02 PM, Robert Haas wrote: On Thu, Sep 23, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Apparently somebody's confused between local and GMT time somewhere in there. Ouch. That rather sucks. Obviously, all committers must now relocate to the UK. Best, David -- 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] git cherry-pick timestamping issue
On 23 September 2010 22:20, David E. Wheeler da...@kineticode.com wrote: On Sep 23, 2010, at 1:02 PM, Robert Haas wrote: On Thu, Sep 23, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Apparently somebody's confused between local and GMT time somewhere in there. Ouch. That rather sucks. Obviously, all committers must now relocate to the UK. Yes, move here. I'll put the kettle on. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] git cherry-pick timestamping issue
On Thu, 2010-09-23 at 22:22 +0100, Thom Brown wrote: On 23 September 2010 22:20, David E. Wheeler da...@kineticode.com wrote: On Sep 23, 2010, at 1:02 PM, Robert Haas wrote: On Thu, Sep 23, 2010 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Apparently somebody's confused between local and GMT time somewhere in there. Ouch. That rather sucks. Obviously, all committers must now relocate to the UK. Yes, move here. I'll put the kettle on. Better add some whiskey buddy... postgresql people can put it down. /me looks hard at dpage JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
