Re: [HACKERS] Per-column collation, work in progress
On tor, 2010-09-23 at 11:55 +0200, Pavel Stehule wrote: 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. I don't understand how you come to that conclusion. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_comments
Robert, I noticed a problem at the definition of the view. : +UNION ALL +SELECT + d.objoid, d.classoid, d.objsubid, + 'large object'::text AS objtype, + NULL::oid AS objnamespace, + d.objoid::text AS objname, + d.description +FROM + pg_description d + JOIN pg_largeobject_metadata lom ON d.objoid = lom.oid +WHERE + d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject') + AND d.objsubid = 0 +UNION ALL : If and when user create a table named 'pg_largeobject' on anywhere except for the 'pg_catalog' schema, the (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject') may not return 2613. It seems to me the query should be fixed up as follows: : WHERE d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')) : Thanks, (2010/09/20 13:53), Robert Haas wrote: The psql \dd command has a couple of infelicities. 1. It doesn't actually list comments on all of the object types to which they can be applied using the COMMENT command. 2. It also doesn't list comments on access methods, which have comments but are not supported by the COMMENT command. 3. It doesn't even list comments on all of the object types which the psql documentation claims it does. 4. It chooses to print out both the name and object columns in a format which is not 100% compatible with the COMMENT command, so that you can't necessarily use the output of \dd to construct valid input to COMMENT. 5. The SQL query used to generate the output it does produce is 75 lines long, meaning that it's really entertaining if you need, for some reason, to edit that query. In view of the foregoing problems, I'd like to propose adding a new system view, tentatively called pg_comments, which lists all of the comments for everything in the system in such a way that it's reasonably possible to do further filtering out the output in ways that you might care about; and which also gives objects the names and types in a format that matches what the COMMENT command will accept as input. Patch attached. I haven't yet written the documentation for the view or adjusted src/bin/psql/describe.c to do anything useful with it, just so that I won't waste any more time on this if it gets shot down. But for the record, it took me something like three hours to write and test this view, which I think is an excellent argument for why we need it. Supposing no major objections, there are a few things to think about if we wish to have psql use this: A. The obvious thing to do seems to be to retain the existing code for server versions 9.1 and to use pg_comments for= 9.1. I would be inclined not to bother fixing the code for pre-9.1 servers to display comments on everything (a 9.1 psql against a 9.0 or prior server will be no worse than a 9.0 psql against the same server; it just won't be any better). B. The existing code localizes the contents of the object column. This is arguably a misfeature if you are about (4), but if we want to keep the existing behavior I'm not quite sure what the best way to do that is. C. It's not so obvious which comments should be displayed with \dd vs. \ddS. In particular, comments on toast schemas have the same problem recently discussed with \dn, and there is a similar issue with tablespaces. Generally, it's not obvious what to do for objects that don't live in schemas - access methods, for example, are arguably always system objects. But... that's arguable. D. Fixing (4) with respect to object names implies listing argument types for functions and operators, which makes the display output quite wide when using \ddS. I am inclined to say that's just the cost of making the output accurate. There may be other issues I haven't noticed yet, too. Incidentally, if you're wondering what prompted this patch, I was reviewing KaiGai Kohei's patch to add security label support and noticed its complete lack of psql support. I'm actually not really sure that there's any compelling reason to provide psql support, considering that we've gotten to the point where any backslash command is almost bound to be something not terribly mnemonic, and because there are likely to be either no security labels at all or so many that a command that just dumps them ALL out in bulk is all but useless. But we at least need to provide a suitable system view, because the catalog structure used by these catalogs that can handle SQL objects of any type is pretty obnoxious for user querying (though, of course, it's pretty neat as an internal format). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list
Re: [HACKERS] Per-column collation, work in progress
2010/9/24 Peter Eisentraut pete...@gmx.net: On tor, 2010-09-23 at 11:55 +0200, Pavel Stehule wrote: 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. I don't understand how you come to that conclusion. sorry, I was wrong - it has sense for date output function. to_char is immutable everywhere Pavel -- 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 09/23/2010 10:09 PM, Robert Haas wrote: 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. Well, Simon is arguing that there's no need to wait for a disconnected standby. So that's not much of an issue. Regrads Markus Wanner -- 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, On 09/24/2010 12:11 AM, Simon Riggs wrote: As I keep pointing out, waiting for an acknowledgement from something that isn't there might just take a while. The only guarantee that provides is that you will wait a long time. Is my data more safe? No. By now I agree that waiting for disconnected standbies is useless in master-slave replication. However, it makes me wonder where you draw the line between just temporarily unresponsive and disconnected. To get zero data loss *and* continuous availability, you need two standbys offering sync rep and reply-to-first behaviour. You don't need standby registration to achieve that. Well, if your master reaches the false conclusion that both standbies are disconnected and happily continues without their ACKs (and the idiot admin being happy about having boosted database performance with whatever measure he recently took) you certainly don't have no zero data loss guarantee anymore. So for one, this needs a big fat warning that gets slapped on the admin's forehead in case of a disconnect. And second, the timeout for considering a standby to be disconnected should rather be large enough to not get false negatives. IIUC the master still waits for an ACK during that timeout. An infinite timeout doesn't have either of these issues, because there's no such distinction between temporarily unresponsive and disconnected. Regards Markus Wanner -- 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 24/09/10 01:11, Simon Riggs wrote: But that's not what I call synchronous replication, it doesn't give you the guarantees that textbook synchronous replication does. Which textbook? I was using that word metaphorically, but for example: Wikipedia http://en.wikipedia.org/wiki/Replication_%28computer_science%29 (includes a caveat that many commercial systems skimp on it) Oracle docs http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repoverview.htm Scroll to Synchronous Replication Googling for synchronous replication textbook also turns up this actual textbook: Database Management Systems by R. Ramakrishnan others which uses synchronous replication with this meaning, although in the context of multi-master replication. Interestingly, Transaction Processing: Concepts and techniques by Grey, Reuter, chapter 12.6.3, defines three levels: 1-safe - what we call asynchronous 2-safe - commit is acknowledged after the slave acknowledges it, but if the slave is down, fall back to asynchronous mode. 3-safe - commit is acknowledged only after slave acknowledges it. If it is down, refuse to commit In the context of multi-master replication, eager replication seems to be commonly used to mean synchronous replication. If we just want *something* that's useful, and want to avoid the hassle of registration and all that, I proposed a while back (http://archives.postgresql.org/message-id/4c7e29bc.3020...@enterprisedb.com) that we could aim for behavior that would be useful for distributing read-only load to slaves. The use case is specifically that you have one master and one or more hot standby servers. You also have something like pgpool that distributes all read-only queries across all the nodes, and routes updates to the master server. In this scenario, you want that the master node does not acknowledge a commit to the client until all currently connected standby servers have replayed the commit. Furthermore, you want a standby server to stop accepting queries if it loses connection to the master, to avoid giving out-of-date responses. With suitable timeouts in the master and the standby, it seems possible to guarantee that you can connect to any node in the system and get an up-to-date result. It does not give zero data loss like synchronous replication does, but it keeps hot standby servers trustworthy for queries. It bothers me that no-one seems to have a clear use case in mind. People want synchronous replication, but don't seem to care much what guarantees it should provide. I wish the terminology was better 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] ask for review of MERGE
Finding time for a review as large as this one is a bit tough, but I've managed to set aside a couple of days for it over the next week. I'm delivering a large project tonight and intend to start in on the review work tomorrow onced that's cleared up. If you're ever not sure who is working on your patch and what state they feel it's in, check https://commitfest.postgresql.org/action/commitfest_view?id=7 for an update; that's where we keep track of all that information. Did you ever end up keeping a current version of this patch in an alternate repository location, such as github? I thought I saw a suggestion from you about that, but after looking through the history here all I see are the diff patches you've been sending to the list. That's fine, just trying to confirm where everything is at. -- Greg Smith, 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Support www.2ndQuadrant.us Author, PostgreSQL 9.0 High PerformancePre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- 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 Fri, Sep 24, 2010 at 07:15, Peter Eisentraut pete...@gmx.net wrote: On tor, 2010-09-23 at 11:07 +0300, Peter Eisentraut wrote: 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. And NLS is also fixed. Great. Thanks - that takes one more thing off the cvs requirement ;) -- 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
[HACKERS] Enable logging requires restart
At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? -- 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] Configuring synchronous replication
On 24/09/10 01:11, Simon Riggs wrote: On Thu, 2010-09-23 at 20:42 +0300, Heikki Linnakangas wrote: 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. As I keep pointing out, waiting for an acknowledgement from something that isn't there might just take a while. The only guarantee that provides is that you will wait a long time. Is my data more safe? No. It provides zero data loss, at the expense of availability. That's what synchronous replication is all about. To get zero data loss *and* continuous availability, you need two standbys offering sync rep and reply-to-first behaviour. Yes, that is a good point. I'm starting to understand what your proposal was all about. It makes sense when you think of a three node system configured for high availability with zero data loss like that. The use case of keeping hot standby servers up todate in a cluster where read-only queries are distributed across all nodes seems equally important though. What's the simplest method of configuration that supports both use cases? You don't need standby registration to achieve that. Not necessarily I guess, but it creeps me out that a standby can just connect to the master and act as a synchronous slave, and there is no controls in the master on what standby servers there are. More complicated scenarios with quorums and different number of votes get increasingly complicated if there is no central place to configure it. But maybe we can ignore the more complicated setups for now. -- 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] patch: SQL/MED(FDW) DDL
On 24/09/10 06:26, Itagaki Takahiro wrote: On Fri, Sep 24, 2010 at 12:08 PM, Robert Haasrobertmh...@gmail.com wrote: I think we need to further discuss how this is eventually going to get integrated with the query planner and the executor before we commit anything. The syntax support by itself is quite trivial. There are no active discussions :-( I think the author tried his best, so if other developers think it's a bad design, alternate plan must be proposed. It's not that the design is bad, it's that it's non-existent. I haven't seen any design on how this integrates with the planner. -- 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] patch: SQL/MED(FDW) DDL
On Fri, Sep 24, 2010 at 6:12 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It's not that the design is bad, it's that it's non-existent. I haven't seen any design on how this integrates with the planner. In my understanding, the DDL part is independent from planner integration, and that's why the author extract DDL changes from the composite patch. (The proposal name is SQL/MED(FDW) DDL.) I think there are two type of FDWs. One is a simple flat file wrapper used by COPY FROM now, that doesn't require any planner hooks. Another is a connector to an external database, like as dblink, that should be integrated with the planner. The former is almost same as SRF functions, but it will skip unneeded tuplestore caching. Even the level is useful enough because we can support SELECT * FROM csvfile without any intermediate tables. Could we set the first goal to the level? Of course we need more discussion for the latter case. The current proposed patch (select part) supports executor integration -- WHERE clause push-down, so it can use indexes in external servers. On the other hand, unsupported ORDER BY, LIMIT, and JOIN push-down require planner integration. More works will be required for fdw_select20100917.patch.gz. -- 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
[HACKERS] Name column
I have just come across a weird thing. It works for any table and seems to be not documented. SELECT c.name FROM (VALUES(1, 'A', true)) c; SELECT c.name FROM pg_class c; And it does not work in these cases: SELECT name FROM (VALUES(1, 'A', true)); SELECT name FROM pg_class; PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit -- 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] Name column
On 24/09/10 13:02, Vlad Arkhipov wrote: I have just come across a weird thing. It works for any table and seems to be not documented. SELECT c.name FROM (VALUES(1, 'A', true)) c; SELECT c.name FROM pg_class c; And it does not work in these cases: SELECT name FROM (VALUES(1, 'A', true)); SELECT name FROM pg_class; For historical reasons PostgreSQL supports calling a function with a single argument like column.function, in addition to function(column). There is a function name(text) that casts the input to the 'name' datatype, so your example casts the row to text and from text to name. It is mentioned in the documentation at http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html Section 34.4.2. SQL Functions on Composite Types. -- 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] Configuring synchronous replication
On Thu, 2010-09-23 at 14:26 +0200, 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. 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... Substantial performance improvements came in 8.4 with bgwriter running in recovery. That meant that the startup process didn't need to spend time doing restartpoints and could apply changes continuously. -- 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, 2010-09-23 at 16:09 -0400, Robert Haas wrote: 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. Let's go back to Josh's 12 server example. This current proposal requires 12 separate and different configuration files each containing many parameters that require manual maintenance. I doubt that people looking at that objectively will decide that is the best approach. We need to arrange a clear way for people to decide for themselves. I'll work on that. 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. Earlier you argued that setting parameters on each standby was difficult and we should centralize things on the master. Now you tell us that actually we do need lots of settings on each standby and that to think otherwise is not realistic. That's a contradiction. The chain of argument used to support this as being a sensible design choice is broken or contradictory in more than one place. I think we should be looking for a design using the KISS principle, while retaining sensible tuning options. -- 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
Tom Lane t...@sss.pgh.pa.us writes: 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. +1, but for real usability you have to make it so that this central setup can be edited from any member of the replication. HINT: plproxy. 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] Configuring synchronous replication
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: 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. And this list can be maintained in a semi-automatic fashion: - adding to the list is done by the master as soon as a standby connects maybe we need to add a notion of fqdn in the standby setup? - service level and current weight and any other knob that comes from the standby are changed on the fly by the master if that changes on the standby (default async, 1, but SIGHUP please) - current standby position (LSN for recv, fsync and replayed) of the standby, as received in the feedback loop are changed on the fly by the master - removing a standby has to be done manually, using an admin function that's the only way to sort out permanent vs transient unavailability - checking the current values in this list is done on the master by using some system view based on a SRF, as already said 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] Configuring synchronous replication
On Fri, 2010-09-24 at 11:08 +0300, Heikki Linnakangas wrote: On 24/09/10 01:11, Simon Riggs wrote: But that's not what I call synchronous replication, it doesn't give you the guarantees that textbook synchronous replication does. Which textbook? I was using that word metaphorically, but for example: Wikipedia http://en.wikipedia.org/wiki/Replication_%28computer_science%29 (includes a caveat that many commercial systems skimp on it) Yes, I read that. The example it uses shows only one standby, which does suffer from the problem/caveat it describes. Two standbys resolves that problem, yet there is no mention of multiple standbys in Wikipedia. Oracle docs http://download.oracle.com/docs/cd/B10500_01/server.920/a96567/repoverview.htm Scroll to Synchronous Replication That document refers to sync rep *only* in the context of multimaster replication. We aren't discussing that here and so that link is not relevant at all. Oracle Data Guard in Maximum availability mode is roughly where I think we should be aiming http://download.oracle.com/docs/cd/B10500_01/server.920/a96653/concepts.htm#1033871 But I disagree with consulting other companies' copyrighted material, and I definitely don't like their overcomplicated configuration. And they have not yet thought of per-transaction controls. So I believe we should learn many lessons from them, but actually ignore and surpass them. Easily. Googling for synchronous replication textbook also turns up this actual textbook: Database Management Systems by R. Ramakrishnan others which uses synchronous replication with this meaning, although in the context of multi-master replication. Interestingly, Transaction Processing: Concepts and techniques by Grey, Reuter, chapter 12.6.3, defines three levels: 1-safe - what we call asynchronous 2-safe - commit is acknowledged after the slave acknowledges it, but if the slave is down, fall back to asynchronous mode. 3-safe - commit is acknowledged only after slave acknowledges it. If it is down, refuse to commit Which again is a one-standby viewpoint on the problem. Wikipedia is right that there is a problem when using just one server. 3-safe mode is not more safe than 2-safe mode when you have 2 standbys. If you want high availability you need N+1 redundancy. If you want a standby server that is N=1. If you want a highly available standby configuration then N+1 = 2. Show me the textbook that describes what happens with 2 standbys. If one exists, I'm certain it would agree with my analysis. (I'll read and comment on your other points later today.) -- 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 Fri, 2010-09-24 at 11:43 +0300, Heikki Linnakangas wrote: To get zero data loss *and* continuous availability, you need two standbys offering sync rep and reply-to-first behaviour. Yes, that is a good point. I'm starting to understand what your proposal was all about. It makes sense when you think of a three node system configured for high availability with zero data loss like that. The use case of keeping hot standby servers up todate in a cluster where read-only queries are distributed across all nodes seems equally important though. What's the simplest method of configuration that supports both use cases? That is definitely the right question. (More later) -- 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
Robert Haas robertmh...@gmail.com writes: 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. All Wrong. You might remember that Simon's proposal begins with per-transaction synchronous replication behavior? 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] Configuring synchronous replication
On 24/09/10 13:57, Simon Riggs wrote: If you want high availability you need N+1 redundancy. If you want a standby server that is N=1. If you want a highly available standby configuration then N+1 = 2. Yep. Synchronous replication with one standby gives you zero data loss. When you add a 2nd standby as you described, then you have a reasonable level of high availability as well, as you can continue processing transactions in the master even if one slave dies. Show me the textbook that describes what happens with 2 standbys. If one exists, I'm certain it would agree with my analysis. I don't disagree with your analysis about multiple standbys and high availability. What I'm saying is that in a two standby situation, if you're willing to continue operation as usual in the master even if the standby is down, you're not doing synchronous replication. Extending that to a two standby situation, my claim is that if you're willing to continue operation as usual in the master when both standbys are down, you're not doing synchronous replication. -- 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] Configuring synchronous replication
On Fri, 2010-09-24 at 14:12 +0300, Heikki Linnakangas wrote: What I'm saying is that in a two standby situation, if you're willing to continue operation as usual in the master even if the standby is down, you're not doing synchronous replication. Oracle and I disagree with you on that point, but I am more interested in behaviour than semantics. If you have two standbys and one is down, please explain how data loss has occurred. Extending that to a two standby situation, my claim is that if you're willing to continue operation as usual in the master when both standbys are down, you're not doing synchronous replication. Agreed. But you still need to decide how you will act. I choose pragmatism in that case. Others have voiced that they would like the database to shutdown or have all sessions hang. I personally doubt their employers would feel the same way. Arguing technical correctness would seem unlikely to allow a DBA to keep his job if they stood and watched the app become unavailable. -- 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] security label support, revised
2010/9/23 KaiGai Kohei kai...@ak.jp.nec.com: Please see http://archives.postgresql.org/pgsql-hackers/2010-09/msg01080.php OK, I'll emulate this approach at first. Don't worry about this part - I will do this myself. If you can just fix the pg_dump stuff, I think we will be in pretty good shape. -- 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] pg_comments
2010/9/24 KaiGai Kohei kai...@ak.jp.nec.com: If and when user create a table named 'pg_largeobject' on anywhere except for the 'pg_catalog' schema, the (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject') may not return 2613. Oh, dear, how embarassing. Perhaps it should be written as: d.classoid = 'pg_catalog.pg_largeobject'::regclass. -- 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] Name column
On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: For historical reasons PostgreSQL supports calling a function with a single argument like column.function, in addition to function(column). There is a function name(text) that casts the input to the 'name' datatype, so your example casts the row to text and from text to name. I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. -- 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] patch: SQL/MED(FDW) DDL
On Thu, Sep 23, 2010 at 11:26 PM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: There are no active discussions :-( I think the author tried his best, so if other developers think it's a bad design, alternate plan must be proposed. Also, if the syntax change is trivial, that's why we merge it at earlier commitfests. I saw many patch went into No patch reviewers because of too large size syndrome before. DDL changes are 5K lines of diff -c patch, and select part is additional 6K lines. IMHO, merging a syntax that doesn't actually work leaves the tree in a broken state. It optimistically presumes that we will later commit a patch to make the syntax in question do something useful. I don't wish to presume that, even if we were well along on the design of the core functionality and especially because we are not. It's fine to break the patches into separate chunks for review, but our main tree is not a dumping ground for half-finished features. -- 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] patch: SQL/MED(FDW) DDL
On Fri, Sep 24, 2010 at 5:56 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: I think there are two type of FDWs. One is a simple flat file wrapper used by COPY FROM now, that doesn't require any planner hooks. Another is a connector to an external database, like as dblink, that should be integrated with the planner. This is a good point. On the one hand, I do agree that the API for simple things like processing CSV files shouldn't be overly complex. So perhaps we could start with a simple API and extend it later. On the other hand, understanding how some of the more complex cases ought to work provides insight into handling the simpler cases. So I think we should aim to have at least a sketch of a design for the whole feature, and then if in phase 1 we want to implement only the easier parts, that's OK. -- 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] Enable logging requires restart
On Fri, Sep 24, 2010 at 4:33 AM, Thom Brown t...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. -- 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] Enable logging requires restart
On 24 September 2010 13:17, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 4:33 AM, Thom Brown t...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. This is probably blasphemy, but off would log to a symbolic link pointing to /dev/null, which is repointed to a log_file if reloaded with 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] Enable logging requires restart
On 24 September 2010 13:22, Thom Brown t...@linux.com wrote: On 24 September 2010 13:17, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 4:33 AM, Thom Brown t...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. This is probably blasphemy, but off would log to a symbolic link pointing to /dev/null, which is repointed to a log_file if reloaded with on? No, this is horrible. I take it back. -- 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] Configuring synchronous replication
On Fri, Sep 24, 2010 at 6:37 AM, Simon Riggs si...@2ndquadrant.com wrote: 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. Earlier you argued that setting parameters on each standby was difficult and we should centralize things on the master. Now you tell us that actually we do need lots of settings on each standby and that to think otherwise is not realistic. That's a contradiction. You've repeatedly accused me and others of contradicting ourselves. I don't think that's helpful in advancing the debate, and I don't think it's what I'm doing. The point I'm trying to make is that when failover happens, lots of reconfiguration is going to be needed. There is just no getting around that. Let's ignore synchronous replication entirely for a moment. You're running 9.0 and you have 10 slaves. The master dies. You promote a slave. Guess what? You need to look at each slave you didn't promote and adjust primary_conninfo. You also need to check whether the slave has received an xlog record with a higher LSN than the one you promoted. If it has, you need to take a new base backup. Otherwise, you may have data corruption - very possibly silent data corruption. Do you dispute this? If so, on which point? The reason I think that we should centralize parameters on the master is because they affect *the behavior of the master*. Controlling whether the master will wait for the slave on the slave strikes me (and others) as spooky action at a distance. Configuring whether the master will retain WAL for a disconnected slave on the slave is outright byzantine. Of course, configuring these parameters on the master means that when the master changes, you're going to need a configuration (possibly the same, possibly different) for said parameters on the new master. But since you may be doing a lot of other adjustment at that point anyway (e.g. new base backups, changes in the set of synchronous slaves) that doesn't seem like a big deal. The chain of argument used to support this as being a sensible design choice is broken or contradictory in more than one place. I think we should be looking for a design using the KISS principle, while retaining sensible tuning options. The KISS principle is exactly what I am attempting to apply. Configuring parameters that affect the master on some machine other than the master isn't KISS, to me. You may find that broken or contradictory, but I disagree. I am attempting to disagree respectfully, but statements like the above make me feel like you're flaming, and that's getting under my skin. -- 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] Enable logging requires restart
On Fri, Sep 24, 2010 at 8:31 AM, Thom Brown t...@linux.com wrote: On 24 September 2010 13:22, Thom Brown t...@linux.com wrote: On 24 September 2010 13:17, Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 4:33 AM, Thom Brown t...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. This is probably blasphemy, but off would log to a symbolic link pointing to /dev/null, which is repointed to a log_file if reloaded with on? No, this is horrible. I take it back. :-) It would be very nice to figure out a clever way to fix this. Maybe you could do something like create the logging pipe and pass the fd down to all the children even if the logging collector is turned off. Then they can use dup2() to switch around their fds between their original stdout and the logging pipe if the collector is turned on or off. Except that wouldn't actually work for switching the collector off, because the collector can't exit if anyone still has the fd open, and if the children closed the fd then you'd be hosed the next time the collector got turned back on. Maybe there's a way to use a named pipe or a socket or something. waves hands, wanders off muttering -- 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
(2010/09/24 20:56), Robert Haas wrote: 2010/9/23 KaiGai Koheikai...@ak.jp.nec.com: Please see http://archives.postgresql.org/pgsql-hackers/2010-09/msg01080.php OK, I'll emulate this approach at first. Don't worry about this part - I will do this myself. If you can just fix the pg_dump stuff, I think we will be in pretty good shape. Ahh, I already did this part at the today's afternoon: http://bit.ly/9kOsnx And, the pg_dump stuff has been just implemented(, but not tested yet): http://bit.ly/a0eVfL If you prefer to keep the patch small, I'll revert the system_views.sql in the next patch. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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 Fri, Sep 24, 2010 at 8:54 AM, KaiGai Kohei kai...@kaigai.gr.jp wrote: (2010/09/24 20:56), Robert Haas wrote: 2010/9/23 KaiGai Koheikai...@ak.jp.nec.com: Please see http://archives.postgresql.org/pgsql-hackers/2010-09/msg01080.php OK, I'll emulate this approach at first. Don't worry about this part - I will do this myself. If you can just fix the pg_dump stuff, I think we will be in pretty good shape. Ahh, I already did this part at the today's afternoon: http://bit.ly/9kOsnx And, the pg_dump stuff has been just implemented(, but not tested yet): http://bit.ly/a0eVfL If you prefer to keep the patch small, I'll revert the system_views.sql in the next patch. It probably doesn't matter much - it'll likely take me about the same amount of time to check your work as it would to do it myself, so it's pretty much six of one, half a dozen of the other. -- 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 Fri, Sep 24, 2010 at 7:47 AM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-09-24 at 14:12 +0300, Heikki Linnakangas wrote: What I'm saying is that in a two standby situation, if you're willing to continue operation as usual in the master even if the standby is down, you're not doing synchronous replication. Oracle and I disagree with you on that point, but I am more interested in behaviour than semantics. I *think* he meant s/two standby/two server/. That's taken from the 2 references: *the* master *the* slave. In that case, if the master is committing w/ no slave connected, it *isn't* repliation, synchronous or not. Usefull, likely, but replication, not at that PIT. If you have two standbys and one is down, please explain how data loss has occurred. Right, of course. But thinking he meant 2 servers (1 standby) not 3 servers (2 standby). But even with only 2 server, if it's down and the master is up, there isn't data loss. There's *potential* for dataloss. But you still need to decide how you will act. I choose pragmatism in that case. Others have voiced that they would like the database to shutdown or have all sessions hang. I personally doubt their employers would feel the same way. Arguing technical correctness would seem unlikely to allow a DBA to keep his job if they stood and watched the app become unavailable. Again, it all depends on the business. Synchronous replication can give you two things: 1) High Availability (Just answer my queries, dammit!) 2) High Durability (Don't give me an answer unless your damn well sure it's the right one) and its goal is to do that in the face of catastrophic failure (for some level of catastrophic). It's the trade of between: 1) The cost of delaying/refusing transactions being greater than the potential cost of a lost transaction 2) The cost of lost transaction being greater than the cost of delaying/refusing transactions So there are people who want to use PostgreSQL in a situation where they'ld much rather not say they have done something unless they are sure it's safely written in 2 different systems, in 2 different locations (and yes, the distance between those two locations will be a trade off wrt performance, and the business will need to decide on their risk levels). I understand it's optimal, desireable, or even praactical for the vast majority of cases. I don't want it to be impossible, or, if it's decide that it will be impossible, hopefully not just because you decided nobody ever needs it, but that its not feasible because of code/implimentation complexitites ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Large objects.
Hey all, Here is simple test case of LOB usage, please, note the comments: #include libpq-fe.h #include libpq/libpq-fs.h int main(int argc, char* argv[]) { PGconn* c = PQconnectdb(password=test); PGresult* r = PQexec(c, BEGIN); PQclear(r); const unsigned int id = lo_create(c, 0); int fd1 = lo_open(c, id, INV_READ | INV_WRITE); int nBytes = lo_write(c, fd1, D, 1); int fd1Pos = lo_lseek(c, fd1, 2147483647, SEEK_SET); fd1Pos = lo_lseek(c, fd1, 1, SEEK_CUR); nBytes = lo_write(c, fd1, Dima, 4); // nBytes == 4 ! Should be 0, IMO. // If not, where is my name // will be written? r = PQexec(c, COMMIT); PQclear(r); r = PQexec(c, BEGIN); PQclear(r); fd1 = lo_open(c, id, INV_READ | INV_WRITE); fd1Pos = lo_lseek(c, fd1, 0, SEEK_END); // fd1Pos == -2147483647 ! char buf[16]; nBytes = lo_read(c, fd1, buf, 4); // nBytes == 0 ! Correct, IMO. r = PQexec(c, COMMIT); PQclear(r); return 0; } Tell me please, why lo_write() returns me the number of bytes actually written when current write location is out of 2GB ? IMO, in this case it should returns at least zero. lo_read() returns zero in this case, and it is correct, IMO. -- Regards, Dmitriy
[HACKERS] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: Hi Mark, On of my college (Sujeet) has found a way to reproduce the same behaviour. 1. Installed PG 9.0 on Win XP SP3 2. Stop the Postgresql-9.0 service from service manager console 3. Create pgpass.conf in postgres (service account) user's profile with an incorrect password deliberately. (Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html) 4. Now start the postgresql-9.0 service, it will return an error and the status shows stopped 5. However i could connect to the psql shell and get the prompt which means the server is running. I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Does anybody recall the specific reason for this? Do we need a way for pg_ctl to figure this out, or do we need to change it in PQconnecitonNeedsPassword()? -- 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] pg_comments
KaiGai Kohei kai...@ak.jp.nec.com writes: It seems to me the query should be fixed up as follows: : WHERE d.classoid = (SELECT oid FROM pg_class WHERE relname = 'pg_largeobject' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')) : Actually, the preferred way to spell that sort of thing is WHERE d.classoid = 'pg_catalog.pg_largeobject'::regclass which is not only shorter but orders of magnitude more efficient. 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 24/09/10 14:47, Simon Riggs wrote: On Fri, 2010-09-24 at 14:12 +0300, Heikki Linnakangas wrote: What I'm saying is that in a two standby situation, if you're willing to continue operation as usual in the master even if the standby is down, you're not doing synchronous replication. Oracle and I disagree with you on that point, but I am more interested in behaviour than semantics. If you have two standbys and one is down, please explain how data loss has occurred. Sorry, that was a typo. As Aidan guessed, I meant even in a two server situation, ie. one master and one slave. -- 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] Configuring synchronous replication
Hi, Defending my ideas as not to be put in the bag you're wanting to put away. We have more than 2 proposals lying around here. I'm one of the guys with a proposal and no code, but still trying to be clear. Robert Haas robertmh...@gmail.com writes: The reason I think that we should centralize parameters on the master is because they affect *the behavior of the master*. Controlling whether the master will wait for the slave on the slave strikes me (and others) as spooky action at a distance. I hope it's clear that I didn't propose anything like this in the related threads. What you setup on the slave is related only to what the slave has to offer to the master. What happens on the master wrt with waiting etc is setup on the master, and is controlled per-transaction. As my ideas come in good parts from understanding Simon work and proposal, my feeling is that stating them here will help the thread. Configuring whether the master will retain WAL for a disconnected slave on the slave is outright byzantine. Again, I can't remember having proposed such a thing. Of course, configuring these parameters on the master means that when the master changes, you're going to need a configuration (possibly the same, possibly different) for said parameters on the new master. But since you may be doing a lot of other adjustment at that point anyway (e.g. new base backups, changes in the set of synchronous slaves) that doesn't seem like a big deal. Should we take some time and define the behaviors we expect in the cluster, and the ones we want to provide in case of each error case we can think about, we'd be able to define the set of parameters that we need to operate the system. Then, some of us are betting than it will be possible to accommodate with either a unique central setup that you edit in only one place at failover time, *or* that the best way to manage the setup is having it distributed. Granted, given how it currently works, it looks like you will have to edit the primary_conninfo on a bunch of standbys at failover time, e.g. I'd like that we now follow Josh Berkus (and some other) advice now, and start a new thread to decide what we mean by synchronous replication, what kind of normal behaviour we want and what responses to errors we expect to be able to deal with in what (optional) ways. Because the more we're staying on this thread, and the clearer it is that there isn't two of us talking about the same synchronous replication feature set. 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Magnus Hagander mag...@hagander.net writes: I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Why should it? That code is complicated enough, I don't think it needs to have a behavior of pretending that a wrong entry isn't there. 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] Standby registration
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: 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 don't see why we need to support unregistered standbys if we have automatic registration. I'm thinking about that on and off and took time to answer, but I fail to see the reason why you're saying that. What I think we need is an easy way to manually unregister the standby on the master, that would be part of the maintenance routine to disconnect a standby. It seems like an admin function would do, and it so happens that it's how it works with PGQ / londiste. 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. For this reason I think we need to have an easy to use facility to check the system health that includes showing how many WALs are currently kept and which standby is registered to still need them. If you happen you have forgotten to unregister your standby, time to call that admin function from above. 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] Configuring synchronous replication
On Fri, 2010-09-24 at 16:01 +0200, Dimitri Fontaine wrote: I'd like that we now follow Josh Berkus (and some other) advice now, and start a new thread to decide what we mean by synchronous replication, what kind of normal behaviour we want and what responses to errors we expect to be able to deal with in what (optional) ways. What I intend to do from here is make a list of all desired use cases, then ask for people to propose ways of configuring those. Hopefully we don't need to discuss the meaning of the phrase sync rep, we just need to look at the use cases. That way we will be able to directly compare the flexibility/complexity/benefits of configuration between different proposals. I think this will allows us to rapidly converge on something useful. If multiple solutions exist, we may then be able to decide/vote on a prioritisation of use cases to help resolve any difficulty. -- 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On Fri, Sep 24, 2010 at 16:04, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Why should it? That code is complicated enough, I don't think it needs to have a behavior of pretending that a wrong entry isn't there. In that case, we should probably teach pg_ctl about this case, no? Since it clearly gives an incorrect message to the user now... -- 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] Configuring synchronous replication
On 24/09/10 17:13, Simon Riggs wrote: On Fri, 2010-09-24 at 16:01 +0200, Dimitri Fontaine wrote: I'd like that we now follow Josh Berkus (and some other) advice now, and start a new thread to decide what we mean by synchronous replication, what kind of normal behaviour we want and what responses to errors we expect to be able to deal with in what (optional) ways. What I intend to do from here is make a list of all desired use cases, then ask for people to propose ways of configuring those. Hopefully we don't need to discuss the meaning of the phrase sync rep, we just need to look at the use cases. Yes, that seems like a good way forward. -- 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On 09/24/2010 10:15 AM, Magnus Hagander wrote: On Fri, Sep 24, 2010 at 16:04, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagandermag...@hagander.net writes: I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Why should it? That code is complicated enough, I don't think it needs to have a behavior of pretending that a wrong entry isn't there. In that case, we should probably teach pg_ctl about this case, no? Since it clearly gives an incorrect message to the user now... pg_ctl decides that the server is running iff it can connect to it. Do you intend to provide for a different test? Setting an incorrect password for the service account sounds like pilot error to 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Andrew Dunstan and...@dunslane.net writes: On 09/24/2010 10:15 AM, Magnus Hagander wrote: In that case, we should probably teach pg_ctl about this case, no? Since it clearly gives an incorrect message to the user now... pg_ctl decides that the server is running iff it can connect to it. Do you intend to provide for a different test? Seems like getting a password challenge from the server is sufficient evidence that the server is running, whether we are able to meet the challenge or not. Perhaps we could just twiddle pg_ctl's is it up test a bit to notice whether the connect failure was of this sort. (Of course, a pg_ping utility would be a better answer, but nobody's gotten around to that in more than ten years, so I'm not holding my breath.) 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On Fri, Sep 24, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: (Of course, a pg_ping utility would be a better answer, but nobody's gotten around to that in more than ten years, so I'm not holding my breath.) Hmm, that sounded like it could be my 9.1 mini project - then Google showed me that SeanC wrote something already. http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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 cherry-pick timestamping issue
I wrote: Apparently somebody's confused between local and GMT time somewhere in there. For the archives' sake: this turns out to be a portability issue not handled by the git code. If you are running on a platform old enough to have gmtime_r returning int rather than struct tm *, you need this patch: *** date.c~ Sat Sep 18 19:43:54 2010 --- date.c Fri Sep 24 10:57:28 2010 *** *** 406,412 case '.': now = time(NULL); refuse_future = NULL; ! if (gmtime_r(now, now_tm)) refuse_future = now_tm; if (num 70) { --- 406,412 case '.': now = time(NULL); refuse_future = NULL; ! if (gmtime_r(now, now_tm) == 0) refuse_future = now_tm; if (num 70) { *** *** 469,475 */ if (num = 1 nodate(tm)) { time_t time = num; ! if (gmtime_r(time, tm)) { *tm_gmt = 1; return end - date; } --- 469,475 */ if (num = 1 nodate(tm)) { time_t time = num; ! if (gmtime_r(time, tm) == 0) { *tm_gmt = 1; return end - date; } [ /me resolves to actually run a program's regression tests before assuming it works ] 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] Name column
Date: Fri, 24 Sep 2010 08:01:35 -0400 Subject: Re: [HACKERS] Name column From: robertmh...@gmail.com To: heikki.linnakan...@enterprisedb.com CC: arhi...@dc.baikal.ru; pgsql-hackers@postgresql.org On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: For historical reasons PostgreSQL supports calling a function with a single argument like column.function, in addition to function(column). There is a function name(text) that casts the input to the 'name' datatype, so your example casts the row to text and from text to name. I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. I agree, it is very unintuitive. +1 for deprecating this behavior.
Re: [HACKERS] Configuring synchronous replication
On Fri, Sep 24, 2010 at 10:01 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Configuring whether the master will retain WAL for a disconnected slave on the slave is outright byzantine. Again, I can't remember having proposed such a thing. No one has, but I keep hearing we don't need the master to have a list of standbys and a list of properties for each standby... -- 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Dave Page dp...@pgadmin.org writes: On Fri, Sep 24, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: (Of course, a pg_ping utility would be a better answer, but nobody's gotten around to that in more than ten years, so I'm not holding my breath.) Hmm, that sounded like it could be my 9.1 mini project - then Google showed me that SeanC wrote something already. http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php Huh, I wonder why we never adopted that? Although I'd be inclined to do most of the heavy lifting inside libpq, myself, and this is way more verbose than what pg_ctl wants. 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] Name column
=?iso-8859-1?B?QW5kcukgRmVybmFuZGVz?= andre.de.camargo.fernan...@hotmail.com writes: On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. I agree, it is very unintuitive. +1 for deprecating this behavior. -1. There's nothing wrong with the function-as-a-computed-column feature, and it seems likely that taking it away will break applications. What we are getting bit by is that I/O coercions to string types can be specified this way. Maybe what we ought to do is remove just that one capability. It'd be a bit non-orthogonal, but seems fairly unlikely to break anything, especially since we only began to allow such things recently (in 8.4 looks like). 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] Name column
On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote: =?iso-8859-1?B?QW5kcukgRmVybmFuZGVz?= andre.de.camargo.fernan...@hotmail.com writes: On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. I agree, it is very unintuitive. +1 for deprecating this behavior. -1. There's nothing wrong with the function-as-a-computed-column feature, and it seems likely that taking it away will break applications. What we are getting bit by is that I/O coercions to string types can be specified this way. Maybe what we ought to do is remove just that one capability. It'd be a bit non-orthogonal, but seems fairly unlikely to break anything, especially since we only began to allow such things recently (in 8.4 looks like). I think that might be an improvement, but I'm not convinced it goes far enough. What evidence do we have that anyone is relying on this behavior in applications? Every report I've heard of it involved someone being surprised that it worked that way. -- 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] Enable logging requires restart
On 09/24/2010 08:22 AM, Thom Brown wrote: On 24 September 2010 13:17, Robert Haasrobertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 4:33 AM, Thom Brownt...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. This is probably blasphemy, but off would log to a symbolic link pointing to /dev/null, which is repointed to a log_file if reloaded with on? No, off simply means we aren't collecting the log, not that we aren't producing any. All the buildfarm tests run without using the logging collector, but they DO produce logs :-) What's the use case for making it settable on the fly? I can't see that this is worth the probably quite large amount of coding that would be required. 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] Name column
Robert Haas robertmh...@gmail.com writes: On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote: -1. There's nothing wrong with the function-as-a-computed-column feature, and it seems likely that taking it away will break applications. ... What evidence do we have that anyone is relying on this behavior in applications? Every report I've heard of it involved someone being surprised that it worked that way. So? There are lots of surprising things in SQL. And *of course* the only complaints come from people who didn't know about it, not from satisfied users. The reason people don't know about this feature is that it's so poorly documented --- there's just one mention buried deep in chapter 35 of the manual, in a place where most people wouldn't think to look for it. I'm not quite sure where's a better place though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enable logging requires restart
On 24 September 2010 16:52, Andrew Dunstan and...@dunslane.net wrote: On 09/24/2010 08:22 AM, Thom Brown wrote: On 24 September 2010 13:17, Robert Haasrobertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 4:33 AM, Thom Brownt...@linux.com wrote: At the moment, to enable logging, a service restart is required. Is there any way to remove this requirement or is there a fundamental reason why it must always be like that? Are you speaking of the logging_collector GUC? I think the difficulty is - if you wanted to turn this on without a restart, how would you get the collector's stdin to be each backend's stdout/stderr? I don't see any way to do it, actually. This is probably blasphemy, but off would log to a symbolic link pointing to /dev/null, which is repointed to a log_file if reloaded with on? No, off simply means we aren't collecting the log, not that we aren't producing any. All the buildfarm tests run without using the logging collector, but they DO produce logs :-) What's the use case for making it settable on the fly? I can't see that this is worth the probably quite large amount of coding that would be required. A very busy server you can't afford to have down. There's a server at work which serves a lot of clients and it's always busy. An attempt to restart would be waiting for running queries to finish while denying new connections, and during this time, web applications dependent on it are non-functional. I noticed we weren't logging on that server and needed to enable it. In this case I've changed the config to the required setting, but won't take effect until one of those rare times when we restart the daemon or server. But the problem now is, in the meantime, if something goes wrong, I won't be able to see exactly what. -- 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] Name column
2010/9/24 André Fernandes andre.de.camargo.fernan...@hotmail.com: Date: Fri, 24 Sep 2010 08:01:35 -0400 Subject: Re: [HACKERS] Name column From: robertmh...@gmail.com To: heikki.linnakan...@enterprisedb.com CC: arhi...@dc.baikal.ru; pgsql-hackers@postgresql.org On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: For historical reasons PostgreSQL supports calling a function with a single argument like column.function, in addition to function(column). There is a function name(text) that casts the input to the 'name' datatype, so your example casts the row to text and from text to name. I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. I agree, it is very unintuitive. +1 for deprecating this behavior. +1 I dislike this feature too. It is breaking other ANSI SQL feature - constructors, because it has same syntax tablename(field1, field2, ). Sure, usually we can do ROW(a,b,c)::type - but little bit nicer and with standard is type(a,b,c). 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] Name column
On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote: -1. There's nothing wrong with the function-as-a-computed-column feature, and it seems likely that taking it away will break applications. ... What evidence do we have that anyone is relying on this behavior in applications? Every report I've heard of it involved someone being surprised that it worked that way. So? There are lots of surprising things in SQL. And *of course* the only complaints come from people who didn't know about it, not from satisfied users. I guess that's true, but is this behavior specified in or required by any SQL standard? Are there other database products that also support this syntax? Or is this just our own invention? The reason people don't know about this feature is that it's so poorly documented --- there's just one mention buried deep in chapter 35 of the manual, in a place where most people wouldn't think to look for it. I'm not quite sure where's a better place though. I think it's because it's counterintuitive. -- 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] Name column
Robert Haas robertmh...@gmail.com writes: On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane t...@sss.pgh.pa.us wrote: So? There are lots of surprising things in SQL. And *of course* the only complaints come from people who didn't know about it, not from satisfied users. I guess that's true, but is this behavior specified in or required by any SQL standard? Are there other database products that also support this syntax? Or is this just our own invention? It's a holdover from PostQUEL, I think, but it's still useful. I observe that SQL:2008 has added a significantly-uglier-than-this feature for computed columns, so there's certainly use cases out there. I think it's because it's counterintuitive. From an object-oriented-programming standpoint it seems entirely intuitive. Many OOP languages minimize the notational difference between members and methods of a class. 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] Name column
Pavel Stehule pavel.steh...@gmail.com writes: I dislike this feature too. It is breaking other ANSI SQL feature - constructors, because it has same syntax tablename(field1, field2, ). Uh, that's nonsense. What we're talking about is tablename.functionname. 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] Name column
2010/9/24 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote: -1. There's nothing wrong with the function-as-a-computed-column feature, and it seems likely that taking it away will break applications. ... What evidence do we have that anyone is relying on this behavior in applications? Every report I've heard of it involved someone being surprised that it worked that way. So? There are lots of surprising things in SQL. And *of course* the only complaints come from people who didn't know about it, not from satisfied users. The reason people don't know about this feature is that it's so poorly documented --- there's just one mention buried deep in chapter 35 of the manual, in a place where most people wouldn't think to look for it. I'm not quite sure where's a better place though. I hope so nobody use it. It is absolutely out of standard. It is like own syntax of mysql for some SQL statements like own INSERT. Some people talked so these specialities are useful too. Regards Pavel Stehule 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 -- 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: 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. That maps nicely to a SLRU table Well, that didn't take as long to get my head around as I feared. I think SLRU would totally tank performance if used for this, and would really not put much of a cap on the memory taken out of circulation for purposes of caching. Transactions are not referenced more heavily at the front of the list nor are they necessarily discarded more or less in order of acquisition. In transaction mixes where all transaction last about the same length of time, the upper limit of interesting transactions is about twice the number of active transactions, so memory demands are pretty light. The problems come in where you have at least one long-lived transaction and a lot of concurrent short-lived transactions. Since all transactions are scanned for cleanup every time a transaction completes, either they would all be taking up cache space or performance would drop to completely abysmal levels as it pounded disk. So SLRU in this case would be a sneaky way to effectively dynamically allocate shared memory, but about two orders of magnitude slower, at best. Here are the things which I think might be done, in some combination, to address your concern without killing performance: (1) Mitigate memory demand through more aggressive cleanup. As an example, a transaction which is READ ONLY (or which hasn't written to a relevant table as tracked by a flag in the transaction structure) is not of interest after commit, and can be immediately cleaned up, unless there is an overlapping non-read-only transaction which overlaps a committed transaction which wrote data. This is clearly not a solution to your concern in itself, but it combines with the other suggestions to make them more effective. (2) Similar to SLRU, allocate pages from shared buffers for lists, but pin them in memory without ever writing them to disk. A buffer could be freed when the last list item in it was freed and the buffer count for the list was above some minimum. This could deal with the episodic need for larger than typical amounts of RAM without permanently taking large quantities our of circulation. Obviously, we would still need some absolute cap, so this by itself doesn't answer your concern, either -- it just the impact to scale to the need dynamically and within bounds. It has the same effective impact on memory usage as SLRU for this application without the same performance penalty. (3) Here's the meat of it. When the lists hit their maximum, have some way to gracefully degrade the accuracy of the conflict tracking. This is similar to your initial suggestion that once a transaction committed we would not track it in detail, but implemented at need when memory resources for tracking the detail become exhausted. I haven't worked out all the details, but I have a rough outline in my head. I wanted to run this set of ideas past you before I put the work in to fully develop it. This would be an alternative to just canceling the oldest running serializable transaction, which is the solution we could use right now to live within some set limit, possibly with (1) or (2) to help push back the point at which that's necessary. Rather than deterministically canceling the oldest active transaction, it would increase the probability of transactions being canceled because of false positives, with the chance we'd get through the peak without any such cancellations. Thoughts? -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] Magnus? Is that you?
Some voter in Sweden has an interesting sense of humour http://alicebobandmallory.com/articles/2010/09/23/did-little-bobby-tables-migrate-to-sweden -- greg -- 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?
Dimitri Fontaine dfonta...@hi-media.com writes: 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: git ls-files -i --exclude-standard Ah-hah, that does what I want, and indeed it shows that we've got some issues. Working on cleaning them up. Thanks! 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] Magnus? Is that you?
On Fri, Sep 24, 2010 at 18:17, Greg Stark st...@mit.edu wrote: Some voter in Sweden has an interesting sense of humour http://alicebobandmallory.com/articles/2010/09/23/did-little-bobby-tables-migrate-to-sweden Ahem. No comment. -- 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] Magnus? Is that you?
On 24 September 2010 17:46, Magnus Hagander mag...@hagander.net wrote: On Fri, Sep 24, 2010 at 18:17, Greg Stark st...@mit.edu wrote: Some voter in Sweden has an interesting sense of humour http://alicebobandmallory.com/articles/2010/09/23/did-little-bobby-tables-migrate-to-sweden Ahem. No comment. interrogation=# SELECT truth FROM hagander.magnus WHERE question = 'Did you do it?'; truth - Okay, I did it. (1 row) -- 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] Serializable Snapshot Isolation
On Fri, Sep 24, 2010 at 12:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thoughts? Premature optimization is the root of all evil. I'm not convinced that we should tinker with any of this before committing it and getting some real-world experience. It's not going to be perfect in the first version, just like any other major feature. -- 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
Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 12:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thoughts? Premature optimization is the root of all evil. I'm not convinced that we should tinker with any of this before committing it and getting some real-world experience. It's not going to be perfect in the first version, just like any other major feature. In terms of pure optimization, I totally agree -- that's why I'm submitting early without a number of potential optimizations. I think we're better off getting a solid base and then attempting to prove the merits of each optimization separately. The point Heikki is on about, however, gets into user-facing behavior issues. The current implementation will give users an out of shared memory error if they attempt to start a SERIALIZABLE transaction when our preallocated shared memory for tracking such transactions reaches its limit. A fairly easy alternative would be to kill running SERIALIZABLE transactions, starting with the oldest, until a new request can proceed. The question is whether either of these is acceptable behavior for an initial implementation, or whether something fancier is needed up front. Personally, I'd be fine with out of shared memory for an excess of SERIALIZABLE transactions for now, and leave refinement for later -- I just want to be clear that there is user-visible behavior involved here. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Snapshot Isolation
On Fri, Sep 24, 2010 at 1:35 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: On Fri, Sep 24, 2010 at 12:17 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thoughts? Premature optimization is the root of all evil. I'm not convinced that we should tinker with any of this before committing it and getting some real-world experience. It's not going to be perfect in the first version, just like any other major feature. In terms of pure optimization, I totally agree -- that's why I'm submitting early without a number of potential optimizations. I think we're better off getting a solid base and then attempting to prove the merits of each optimization separately. The point Heikki is on about, however, gets into user-facing behavior issues. The current implementation will give users an out of shared memory error if they attempt to start a SERIALIZABLE transaction when our preallocated shared memory for tracking such transactions reaches its limit. A fairly easy alternative would be to kill running SERIALIZABLE transactions, starting with the oldest, until a new request can proceed. The question is whether either of these is acceptable behavior for an initial implementation, or whether something fancier is needed up front. Personally, I'd be fine with out of shared memory for an excess of SERIALIZABLE transactions for now, and leave refinement for later -- I just want to be clear that there is user-visible behavior involved here. Yeah, I understand, but I think the only changes we should make now are things that we're sure are improvements. I haven't read the code, but based on reading the thread so far, we're off into the realm of speculating about trade-offs, and I'm not sure that's a good place for us to be. -- 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
[HACKERS] History for 8.3.6 tag is a little strange
Doing git log tags/REL8_3_6 I see two commits after the one labeled tag for 8.3.6. The other tags I checked all seem to match what I would expect. I'm not suggesting that anything be done, I just wanted to point this out in case something strange happened. Regards, Jeff Davis -- 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] psql's \dn versus temp schemas
Peter Eisentraut pete...@gmx.net writes: On sön, 2010-09-19 at 13:51 -0400, Tom Lane wrote: Hmm. If we had a \dnS option, what I would sorta expect it to do is show the system schemas pg_catalog and information_schema. The toast and temp schemas seem like a different category somehow. On the other hand, if we did it like this, then the S and + modifiers would be orthogonal which is a nice property. Well, normally the + option shows more columns and the S option shows more rows. Showing more internal objects with + might be a bit confusing. Okay, it seems to be the consensus that \dn should have orthogonal S and + options (S = show system stuff, + = show more columns). How do we want to define system exactly? My original proposal was for bare \dn to hide the temp and toast schemas. If we consider that what it's hiding is system schemas then there's some merit to the idea that it should hide pg_catalog and information_schema too. In that case, in a fresh database you would *only* see public. I'm not sure that I like this though. Comments? 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] History for 8.3.6 tag is a little strange
Jeff Davis pg...@j-davis.com writes: Doing git log tags/REL8_3_6 I see two commits after the one labeled tag for 8.3.6. The other tags I checked all seem to match what I would expect. I'm not suggesting that anything be done, I just wanted to point this out in case something strange happened. Hmmm ... the files those commits touch are indeed tagged REL8_3_6 at the later commit in the CVS repository, and I pulled out the 8.3.6 tarball and confirmed that that has the newer versions too. [ pokes around in mail archives ... ] What seems to have happened is that the analyze_requires_snapshot() bug was reported on pgsql-packagers and we concluded that it was serious enough to justify a re-wrap; but the tarballs hadn't gone anywhere public so we just did the deed without bumping the version number. 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] Serializable Snapshot Isolation
Robert Haas robertmh...@gmail.com wrote: I think the only changes we should make now are things that we're sure are improvements. In that vein, anyone who is considering reviewing the patch should check the latest from the git repo or request an incremental patch. I've committed a few things since the last patch post, but it doesn't seem to make sense to repost the whole thing for them. I fixed a bug in the new shared memory list code, fixed a misleading hint, and fixed some whitespace and comment issues. The changes I've committed to the repo so far based on Heikki's comments are, I feel, clear improvements. It was actually fairly embarrassing that I didn't notice some of that myself. based on reading the thread so far, we're off into the realm of speculating about trade-offs This latest issue seems that way to me. We're talking about somewhere around 100 kB of shared memory in a 64 bit build with the default number of connections, with a behavior on exhaustion which matches what we do on normal locks. This limit is easier to hit, and we should probably revisit it, but I am eager to get the feature as a whole in front of people, to see how well it works for them in other respects. I'll be quite surprised if we've found all the corner cases, but it is working, and working well, in a variety of tests. It has been for months, really; I've been holding back, as requested, to avoid distracting people from the 9.0 release. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What happened to the is_type family of functions proposal?
Robert Haas robertmh...@gmail.com writes: On Tue, Sep 21, 2010 at 7:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: There are many rules that you could possibly make for type input functions. But you cannot throw an error is not one of them --- or at least, not one that you can usefully expect to be followed for anything more than trivial straightline code. OK. This is one of the things I don't understand. Why does throwing an error imply that we need to abort the current transaction? Why can't we just catch the longjmp() and trundle onwards? Obviously, that's unsafe if a pretty wide variety of cases, but if you're just scrutinizing the input string (even with a little bit of read-only database access) it's not obvious to me what can go wrong. The problem is to know that all you did was scrutinize the input string. If it's simple straightline code (even with some C library calls) then you can know that, but then you can write such code without including any elog(ERROR) in it in the first place. If you are trapping longjmps then what you'd need to assert is that no error thrown from anywhere in any of the code reachable from that place represents a problem that requires transaction abort to clean up after. This gets unmaintainable remarkably quickly, especially if you invoke anything as complicated as database access. And then there are asynchronous error reasons (query cancel) which you shouldn't trap in any case. 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] snapshot generation broken
Magnus Hagander mag...@hagander.net writes: On Fri, Sep 24, 2010 at 07:15, Peter Eisentraut pete...@gmx.net wrote: And NLS is also fixed. Great. Thanks - that takes one more thing off the cvs requirement ;) Yeah. Maybe we don't need a cvsserver after all. Would it make more sense to help Stefan get git running on his BSD boxes? If Bruce and I could get it to work on our pet dinosaurs, I think it likely can be gotten to work on spoonbill too. 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] snapshot generation broken
On 09/24/2010 03:47 PM, Tom Lane wrote: Yeah. Maybe we don't need a cvsserver after all. Would it make more sense to help Stefan get git running on his BSD boxes? If Bruce and I could get it to work on our pet dinosaurs, I think it likely can be gotten to work on spoonbill too. Yeah, I find it hard to believe that a machine can build postgres, with openssl, but can't build git. The problem is that Stefan doesn't have time to work on it, and I gather he's unable to give anyone else access. 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] levenshtein_less_equal (was: multibyte charater set in levenshtein function)
On Sat, Aug 28, 2010 at 8:34 AM, Alexander Korotkov aekorot...@gmail.com wrote: Here is the patch which adds levenshtein_less_equal function. I'm going to add it to current commitfest. There are some minor stylistic issues with this patch - e.g. lines ending in whitespace, cuddled elses - but those don't look too terribly difficult to fix. I'm more concerned about the fact that I don't really understand the algorithm you're using. Actually, I didn't really understand the original algorithm either until I went and read up on it, and I just adjusted the comments to make it a bit more clear what it's doing. That caused some minor merge conflicts with your patch, so I'm attaching a rebased version that applies cleanly over my changes. Can you explain a bit more what algorithm this is using? It seems like in the max_d = 0 case the cells of the notional array have a meaning which is completely different from what they mean in otherwise, and it's not clear to me from reading the comments what that meaning is. I took a look on that font of all human knowledge, Wikipedia: http://en.wikipedia.org/wiki/Levenshtein_distance Their suggestion for handling this case is: If we are only interested in the distance if it is smaller than a threshold k, then it suffices to compute a diagonal stripe of width 2k+1 in the matrix. In this way, the algorithm can be run in O(kl) time, where l is the length of the shortest string. It seems like that may be similar to what you're doing here but I don't think that's exactly it. I don't think that exact thing would work in our case anyhow because we've got configurable costs. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company levenshtein_less_equal-0.2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On 09/24/2010 11:11 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 09/24/2010 10:15 AM, Magnus Hagander wrote: In that case, we should probably teach pg_ctl about this case, no? Since it clearly gives an incorrect message to the user now... pg_ctl decides that the server is running iff it can connect to it. Do you intend to provide for a different test? Seems like getting a password challenge from the server is sufficient evidence that the server is running, whether we are able to meet the challenge or not. Perhaps we could just twiddle pg_ctl's is it up test a bit to notice whether the connect failure was of this sort. pg_ctl does in fact use that sort of logic: if ((conn = PQconnectdb(connstr)) != NULL (PQstatus(conn) == CONNECTION_OK || PQconnectionNeedsPassword(conn))) But of course, libpq won't set that last condition if there is a bad password in the pgpass file, which seems a rather perverse thing to do. cheers andrew
Re: [HACKERS] Path question
Robert Haas robertmh...@gmail.com writes: FIXME #1 and FIXME #2 were much harder to trigger. In fact, barring significant further lobotimization of the code, I couldn't. It's not that hard if you just tweak equivclass.c to make the order of equivalence-class lists different, viz diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c index a20ed5f..9528d0b 100644 --- a/src/backend/optimizer/path/equivclass.c +++ b/src/backend/optimizer/path/equivclass.c @@ -353,7 +353,7 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids, { ec-ec_relids = bms_add_members(ec-ec_relids, relids); } - ec-ec_members = lappend(ec-ec_members, em); + ec-ec_members = lcons(em, ec-ec_members); return em; } Then for instance: regression=# create table t1 (f1 int); CREATE TABLE regression=# create table t2 () inherits (t1); CREATE TABLE regression=# explain select * from t1 a join t1 b using (f1); WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 WARNING: FIXME #1 Since the order of equivalence-class member lists isn't supposed to be semantically significant, I claim that the code in createplan has to be able to deal with this. Note that what this is triggering is the em_is_child condition. I think it may indeed be impossible to get a hit on the em_is_const case as the system currently stands; the reason being that an EC containing a const won't normally show up as a pathkey. It can only do so if it's below_outer_join, as the comment notes. Now the calls to make_sort_from_pathkeys in createplan.c are only used for constructing subsidiary sorts for a mergejoin, and we won't consider building a mergejoin with an EC that contains a const (see eclass_useful_for_merging). There are some calls in planner.c that are associated with doing a final sort or distinct, but I suspect they'd never be applied with a below_outer_join EC. So given the current usage of make_sort_from_pathkeys it might be pretty hard to get it applied to an EC containing a constant. That's not a reason for it not to handle the case, though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle
Kevin Grittner gri...@gmail.com wrote: This now compiles and passes regression tests. I still need to re-run all the other tests which Florian and I previously used to test the patch. I don't have any reason to expect that they will now fail, but one need to be thorough. Once that is confirmed, I think this will be ready for committer unless someone can think of something else to throw at it first. I reran the tests at http://github.com/fgp/fk_concurrency and, unsurprisingly, it still works. This patch addresses concerns I heard expressed by a couple guys from an Oracle shop who wanted to convert to PostgreSQL but were much put out by the behavior of SELECT FOR UPDATE under snapshot isolation in PostgreSQL. This patch should do much to ease the migration of some Oracle shops to PostgreSQL. A complete review was done in the last CF, but I held off marking it as Ready for Committer then because there were some more tests coming, which all looked good. http://archives.postgresql.org/message-id/4c419320022500033...@gw.wicourts.gov I am marking this patch Ready for Committer now. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql's \dn versus temp schemas
Tom Lane t...@sss.pgh.pa.us writes: In that case, in a fresh database you would *only* see public. I'm not sure that I like this though. Comments? I sure like it! I can't count how many time I would have wanted a cleaned out \dn output. 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] snapshot generation broken
On 09/24/2010 09:54 PM, Andrew Dunstan wrote: On 09/24/2010 03:47 PM, Tom Lane wrote: Yeah. Maybe we don't need a cvsserver after all. Would it make more sense to help Stefan get git running on his BSD boxes? If Bruce and I could get it to work on our pet dinosaurs, I think it likely can be gotten to work on spoonbill too. Yeah, I find it hard to believe that a machine can build postgres, with openssl, but can't build git. The problem is that Stefan doesn't have time to work on it, and I gather he's unable to give anyone else access. yeah don't worry too much, I will find a way to fix it will just take a few more days until I get a bit of spare time... 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] trailing whitespace in psql table output
On Tue, Sep 21, 2010 at 09:28:07PM +0300, Peter Eisentraut wrote: Everyone using git diff in color mode will already or soon be aware that psql, for what I can only think is an implementation oversight, produces trailing whitespace in the table headers, like this: two | f1 $ -+$ | asdfghjkl;$ | d34aaasdf$ (2 rows)$ Does this break the output with \pset border 2? IIRC when I was doing the \pset linestyle work, I did look at doing this, but found that the padding was required in some cases. I couldn't tell from looking over the patch whether or not you were already taking this into account though? Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. signature.asc Description: Digital signature
[HACKERS] Re: [COMMITTERS] pgsql: git_topo_order script, to match up commits across branches.
On Fri, Sep 24, 2010 at 6:09 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: Excerpts from Tom Lane's message of vie sep 24 17:36:59 -0400 2010: Robert Haas robertmh...@gmail.com writes: On Sep 24, 2010, at 3:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW ... I don't especially care for the name you picked for this script. The fact that it does a topological sort is an implementation detail that its users couldn't care less about, especially since that doesn't matter except for corner cases. How about collate_git_log or something along it. I don't think that's any better - collate could mean anything - but I don't think it's any worse, either. Change it if you want. Well, I definitely think that the name should suggest a connection to git log. I agree that collate might not be the best possible verb here --- anyone have a better suggestion? How about something content-free like pg_git_log? If it resembles cvs2cl then why not name it git2cl? Or git_changelog. Since it doesn't do anything specific to Postgres' git, lets not have any pg in there. Regards, -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.EnterpriseDB.com singh.gurj...@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
Re: [HACKERS] Re: [COMMITTERS] pgsql: git_topo_order script, to match up commits across branches.
On 09/24/2010 06:53 PM, Gurjeet Singh wrote: If it resembles cvs2cl then why not name it git2cl? Or git_changelog. +1 for git_changelog 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
[HACKERS] Re: [COMMITTERS] pgsql: git_topo_order script, to match up commits across branches.
On Fri, Sep 24, 2010 at 6:53 PM, Gurjeet Singh singh.gurj...@gmail.com wrote: Since it doesn't do anything specific to Postgres' git, lets not have any pg in there. Assuming you discount the hard-coded list of our active branch heads, of course. -- 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 Fri, Sep 24, 2010 at 5:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: It's not that hard if you just tweak equivclass.c to make the order of equivalence-class lists different, viz [...] Since the order of equivalence-class member lists isn't supposed to be semantically significant, I claim that the code in createplan has to be able to deal with this. [...] That's not a reason for it not to handle the case, though. I'm not disputing that those tests are correct. All I'm saying is that I can't figure out a way to write regression tests that fail if they are removed. -- 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] [COMMITTERS] pgsql: git_topo_order script, to match up commits across branches.
Excerpts from Robert Haas's message of vie sep 24 22:20:54 -0400 2010: On Fri, Sep 24, 2010 at 6:53 PM, Gurjeet Singh singh.gurj...@gmail.com wrote: Since it doesn't do anything specific to Postgres' git, lets not have any pg in there. Assuming you discount the hard-coded list of our active branch heads, of course. Also, I thought it resembled pgcvslog more than cvs2cl. +1 to git_changelog anyway. -- Á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
Re: [HACKERS] What happened to the is_type family of functions proposal?
On Fri, Sep 24, 2010 at 3:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Sep 21, 2010 at 7:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: There are many rules that you could possibly make for type input functions. But you cannot throw an error is not one of them --- or at least, not one that you can usefully expect to be followed for anything more than trivial straightline code. OK. This is one of the things I don't understand. Why does throwing an error imply that we need to abort the current transaction? Why can't we just catch the longjmp() and trundle onwards? Obviously, that's unsafe if a pretty wide variety of cases, but if you're just scrutinizing the input string (even with a little bit of read-only database access) it's not obvious to me what can go wrong. The problem is to know that all you did was scrutinize the input string. If it's simple straightline code (even with some C library calls) then you can know that, but then you can write such code without including any elog(ERROR) in it in the first place. If you are trapping longjmps then what you'd need to assert is that no error thrown from anywhere in any of the code reachable from that place represents a problem that requires transaction abort to clean up after. This gets unmaintainable remarkably quickly, especially if you invoke anything as complicated as database access. And then there are asynchronous error reasons (query cancel) which you shouldn't trap in any case. Hmm. So the problem is that we don't want to accidentally catch an error that isn't actually safe to catch. We could probably mitigate this problem to a considerable degree by throwing data validation errors using some special flag that say this is a recoverable error. And if that flag isn't set then we abort the whole transaction, but if it is then we continue on. It's still possible for the person writing the typinput function to set that flag when they should not, but at least it's less likely to happen by accident. Another alternative would be to create some kind of explicit way for the function to RETURN an error instead of throwing it. But neither of these things is totally bullet-proof, because you could still do something that requires clean-up and then lie about it. To protect against that, you'd presumably need to set some kind of a flag whenever, say, a heap tuple gets modified, and then you could assert said flag false. What, other than writing to the database, requires subtransaction cleanup? -- 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] BUG #5661: The character encoding in logfile is confusing.
On 09/22/2010 09:55 PM, Tom Lane wrote: Peter Eisentrautpete...@gmx.net writes: On ons, 2010-09-22 at 19:25 +0800, Craig Ringer wrote: I still wonder if, rather than making this configurable, the right choice is to force logging to UTF-8 (with BOM) across the board, I don't think this would make things better or easier. At some point you're going to have to insert a recode call, and it doesn't matter much whether the destination argument is a constant or a variable. It'd avoid the problem of having possibly-unconvertable messages ... at the cost of pissing off users who have a uniform server encoding selection already and don't see why they should be forced to deal with UTF8 in the log. It's pretty much just one step from here to deciding that the server should work exclusively in UTF8 and never mind all those other legacy encodings. We've resisted that attitude for quite some years now, and are probably not really ready to adopt it for the log either. Fair enough. The current approach is broken, though. Mis-encoded messages the user can't read are little more good to them than messages that're never logged. I see four options here (two of which are practical IMO): (1) Log in UTF-8, convert everything to UTF-8. Better for admin tools apps, sucks for OS utilities/grep/etc on non-utf-8 locales. Preserves all messages no matter what the database and system encodings are. (2) Log in default encoding for locale, convert all messages to that encoding. Where characters cannot be represented in the target encoding replace them with a placeholder (? or something). Better - but far from good - for OS utilities/grep/etc, sucks for admin tools and apps. Doesn't preserve all messages properly if user has databases in encodings other than the system encoding. (3) Have a log for the postmaster in the default locale for the system. Have a log file for each database that's in the encoding for that database. IMO this is the worst of both worlds, but it does preserve original encodings without transcoding or forcing a particular encoding and does preserve messages. Horribly complicated for admin tools, inconsistent and horrid for grep etc. (4) Keep things much as they are, but log an encoding identifier prefix for each line. Lets GUI/admin tools post-process the logs into something sane, permits automated log processing because line encodings are known. Sucks for shell tools, which can't tell which lines are which; we'd need to provide a pggrep and pgless for reliable log search! Preserves all messages, but not in a reliably searchable manner. (0) Change nothing. Log all messages in the original encoding they were generated in. Perform no conversion. Logs contain mixed encodings. Horrible for admin/gui tools (broken text). Horrible for shell utilities/OS tools (can't trust grep results etc). Automatic log processing impossible as the encoding for each line isn't known and can't be reliably discovered. As far as I'm concerned, (3) is out. It's horrible. I don't think the status quo (0) is OK either, it's producing broken log files. (4) is pretty awful too, but it's the smallest change that kind-of fixes the issue to the point where it's at least possible for PgAdmin etc to convert the logs into a consistent encoding. IMO it's down to (1) and (2). There's no clear consensus between those two, so I'd be inclined to offer the admin the choice between them as a config option, depending on the trade-off they prefer to make. For sensible systems in a utf-8 locale (1) and (2) are equivalent, and (2) is fine for systems where the database encoding is always the same as the system encoding. It's only for systems with a non-utf-8 locale that use databases in encodings other than the system locale's encoding that problems arise. In this case they're going to get suboptimal results one way or the other, it's just a matter of letting them pick how. Thoughts? I should ask on the various language-specific mailing lists and see what people there have to say about it. Maybe it doesn't affect people enough in practice for them to care. -- Craig Ringer -- 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] What happened to the is_type family of functions proposal?
Colin 't Hart wrote: The fact that this wraps would seem to me to make the implementation of is_date() difficult. Having separate is_foo() syntax per type is a bad design idea, same as having a different equality test like eq_int() or assignment syntax like assign_str() per type. There should just be a single syntax that works for all types, in the general case, for testing whether a value is a member of that type, or alternately whether a value can be cast to a particular type. For example, one could say is_type( value, type-name ) or it could be spelled isa() or if you wanted to be more ambitious it could be an infix op, like value isa type-name to test when a value is of a type already. Pg already gets it right in this regard by having a single general syntax for type casting, the value::type-name and value membership of a type should be likewise. Maybe to test if a value can be cast as a type, you can continue the :: mnemonic, say adding a ? for yes and a ! for no. For example, value?::type-name tests if the value can be cast as the type and value!::type-name or not value?::type-name tests the opposite. An expression like this results in a boolean. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers