Re: [HACKERS] WIP - syslogger infrastructure changes
Magnus Hagander mag...@hagander.net wrote: On 15 sep 2009, at 07.21, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp I'd like to have an opposite approach -- per-backend log files. I can see each backend writing it, certainly, but keeping it in separate files makes it useless without post processing, which in most vases means useless for day-to-day work. Sure. There should be a trade-off between performance and usability. And that's is the reason I submitted per-destination or per-category log filter. Log messages for day-to-day work should be written in a single file (either text or syslog), but sql queries used only for database auditing are acceptable even if written in separate files. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
Hi, On Tue, Sep 15, 2009 at 2:54 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The first thing that caught my eye is that I don't think replication should be a real database. Rather, it should by a keyword in pg_hba.conf, like the existing all, sameuser, samerole keywords that you can put into the database-column. I'll try that! It might be only necessary to prevent walsender from accessing pg_database and checking if the target database is present, in InitPostres(). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bulk Inserts
Yes, I did not consider that to be a problem because I did not think it would be used on indexed tables. I figured that the gain from doing bulk inserts into the table would be so diluted by the still-bottle-necked index maintenance that it was OK not to use this optimization for indexed tables. I've tested with indexes, and the index update time is much larger than the inserts time. Bulk inserts still provide a little bonus though, and having a solution that works in all cases is better IMHO. My original thought was based on the idea of still using heap_insert, but with a modified form of bistate which would hold the exclusive lock and not just a pin. If heap_insert is being driven by the unmodified COPY code, then it can't guarantee that COPY won't stall on a pipe read or something, and so probably shouldn't hold an exclusive lock while filling the block. Exactly, that's what I was thinking too, and reached the same conclusion. That is why I decided a local buffer would be better, as the exclusive lock is really a no-op and wouldn't block anyone. But if you are creating a new heap_bulk_insert and modifying the COPY to go with it, then you can guarantee it won't stall from the driving end, instead. I think it's better, but you have to buffer tuples : at least a full page's worth, or better, several pages' worth of tuples, in case inline compression kicks in and shrinks them, since the purpose is to be able to fill a complete page in one go. Whether any of these approaches will be maintainable enough to be integrated into the code base is another matter. It seems like there is already a lot of discussion going on around various permutations of copy options. It's not really a COPY mod, since it would also be good for big INSERT INTO SELECT FROM which is wal-bound too (even more so than COPY, since there is no parsing to do). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bulk Inserts
Does that heuristic change the timings much? If not, it seems like it would better to keep it simple and always do the same thing, like log the tuples (if it is done under one WALInsertLock, which I am assuming it is..) It is the logging of whole pages that makes it faster. If you fill a page with tuples in one operation (while holding exclusive lock) and then insert WAL records for each tuple, there is no speed gain. Inserting a full page WAL record (since you just filled the page completely) : - only takes WalInsertLock once instead of once per tuple - reduces wal traffic - is about 2x faster in my benchmark And inserting a clear new page record (if the page was previously new/empty and relation is fsync'd at the end) : - only takes WalInsertLock once instead of once per tuple - reduces wal traffic a lot - is about 4x faster in my benchmark Do you even need the new empty page record? I think a zero page will be handled correctly next time it is read into shared buffers, won't it? I have no idea ;) But I guess it is need to avoid problems with partial page writes that would leave in a state that is neither all zeros nor consistent. Plus, empty page records make for very small WAL traffic and I didn't see any performance difference with or without them. If the entire page is logged, would it have to marked as not removable by the log compression tool? Or can the tool recreate the needed delta? No, the tool cannot recreate the data, since the idea is precisely to replace a lot of tuple insert messages with one entire page message, which takes both less space and less time. The warm-standby replicators that get this WAL need to know the page contents to replicate it... (also, it will probably be faster for them to redo a page write than redo all the tuple inserts). Here is what I'm thinking about now : * have some kind of BulkInsertState which contains - info about the relation, indexes, triggers, etc - a tuple queue. The tuple queue may be a tuple store, or simply tuple copies in a local memory context. You'd have functions to : - Setup the BulkInsertState - Add a tuple to the BulkInsertState - Finish the operation and clear the BulkInsertState When adding a tuple, it is stored in the queue. When the queue is full, a bulk insert operation takes place, hopefully we can fill an entire page, and return. Post insert triggers and index updates are also handled at this point. When finished, the function that clears the state also inserts all remaining tuples in the queue. With this you could also do something *really* interesting : bulk index updates... Bulk index updates are probably mutually exclusive with after-row triggers though. Another angle of attack would be to make wal-writing more efficient... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issues for named/mixed function notation patch
Same problem. Build log attached. ...Robert My renonc, please, try new patch. I forgot mark regproc.c file. regards Pavel Stehule nm.diff.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] clang's static checker report.
http://llvm.org/bugs/show_bug.cgi?id=4979 will see, one issue is already fixed. I'll retry when the second one is too. -- 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] WIP: generalized index constraints
On Sun, 2009-09-13 at 19:08 +1000, Brendan Jurd wrote: Any update on this patch? Attached is the latest version. Changes: * Merged with HEAD * Changed from storing the information in pg_index to pg_constraint. This required rewriting a large portion of the patch, so it's not a clean diff from the previous patch. * Implemented the language using ALTER TABLE to add the constraint, as discussed (with a slight change to avoid the extra INDEX keyword). * Added docs * Added tests * All relations with relindconstraints == 0 do not pass through the index constraints enforcement code at all. I did this a little differently than what I laid out in the design, but the idea is the same and it should avoid any problems. That's the good news. The bad news: * No pg_dump support yet (shouldn't be too hard) * Creating a new constraint does not check the existing data for conflicts. * Doesn't work like the new deferrable unique constraints yet (also shouldn't be too hard). * I didn't make any changes to the behavior of LIKE (also not hard). * Can't be specified at CREATE INDEX time. I don't think this is a showstopper, and it will take some significant effort. The advantage of allowing this is that the constraints can be checked more quickly (in theory) while building the index, and it also might be handy shorthand. However, it suffers from a number of problems: 1. Extra syntax that is almost entirely redundant. 2. More work. 3. The performance gains will probably be pretty marginal. We have to do N index scans anyway; the savings would only be due to the better caching impact and the fact that the index in the process of being built is smaller than an already-built index. So, right now I'm not in a hurry to fix this last point. I realize that some of the things missing make the patch uncomittable in its current form. However, I would still appreciate a review of what I have ready. Regards, Jeff Davis generalized-index-constraints-20090915.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] clang's static checker report.
On Mon, Sep 14, 2009 at 06:39:11PM +0100, Grzegorz Jaskiewicz wrote: meanwhile, since quite a lot stuff went in over weekend, and since Yesterday, new report at: http://zlew.org/postgresql_static_check/scan-build-2009-09-14-1/ Looking at http://zlew.org/postgresql_static_check/scan-build-2009-09-14-1/report-3LPmKK.html#EndPath it tells me that the value stored to 'counter' is never used. However, the counter++ is called inside a loop and thus will be read the next time the loop is run. Looks to me like a bug, or did I miss something? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
On Mon, Sep 14, 2009 at 11:20:59PM -0400, Tom Lane wrote: There is some moderately interesting reading material in section 4.17.4 Domain constraints of SQL:2008. Not sure where to look for a copy of that, nor any particularly helpful links :( In particular, it appears to me that the standard goes out of its way to NOT claim that every value that is of a domain type satisfies the domain's constraints. It looks to me that the implementation they have in mind is that domain constraints are to be checked: (1) when a value is assigned to a *table* column having that domain type; (2) when a value is converted to that domain type by an *explicit* cast construct; (3) nowhere else. I struggle to get any useful meaning out of the SQL specs, but that sounds about right to me. If I'm reading this right, it sidesteps most of the concerns we have been worrying about here, at the cost of being perhaps more surprising and less useful than one would expect. It means that domains are a world away from ADTs (abstract data types) and just seem to function as quick templates for creating new columns. PG seems to be treating domains as ADTs at the moment, which is the abstraction that's proved to be more useful in larger programming projects. It would also mean that a lot of our existing domain behavior is wrong. I think there is ammunition here for an argument that, in effect, values in flight in expression or query evaluation should always be considered to be of base types, and domain constraints should only be checked when assigning to a persistent storage location such as a table field or plpgsql variable (plus the special case for CAST constructs). Are you considering changing PGs behavior here? and if so, what would happen to existing behavior? -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
On Tue, Sep 15, 2009 at 05:13:21AM +0100, Andrew Gierth wrote: But there's a kicker: in Subclause 6.12, cast specification, in the General Rules is: a) If the cast operand specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied. That no further General Rules clause implies (assuming it's not a blatant mistake in the spec) that this rule is therefore skipped in the case of nulls: I think the NOT NULL constraint is a PG specific constraint, I can't see how it's allowed in the spec. Then again, I have trouble parsing the spec so could well be wrong about this. The NOT NULL constraint feels wrong as well, what are the semantics of: CREATE DOMAIN d AS INTEGER NOT NULL; SELECT a.n AS aa, b.n AS bb FROM (VALUES (CAST(1 AS d)),(2)) a(n) LEFT JOIN (VALUES (CAST(1 AS d))) b(n) ON a.n = b.n; in the presence of it? I'm expecting aa and bb both to come out as domain d, but this shouldn't work with what you're saying the current semantics should be. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
Kevin Grittner wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Kevin Grittner wrote: IMO, it would be best if the status could be sent via NOTIFY. To where? To registered listeners? I guess I should have worded that as it would be best if a change is replication status could be signaled via NOTIFY -- does that satisfy, or am I missing your point entirely? Ok, makes more sense 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] FDW-based dblink (WIP)
On Wed, 2009-08-19 at 17:07 +0900, Itagaki Takahiro wrote: Here is a WIP patch for a foreign data wrapper based dblink. It integrates dblink module into core and adds a new functionality, automatic transaction management. The new interface of dblink is exported by include/foreign/dblink.h. We can easily write a connector module for another database because we can reuse transaction and resource management parts in core. This patch is listed in the commitfest, but I think the consensus was that it needed some rework. I think the idea is that we will have support for syntax like Syntax to create FDW with connector is below: CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator CONNECTOR postgresql_fdw_connector OPTIONS (...); in core, but the actual implementation of postgresql_fdw_connector would be a loadable module. Personally, I'm undecided whether the single-function connector implementation is the best. The other approach would be to use a multiple-function interface based directly on the functions currently provided by dblink. More generally, what does this really buy us? It doesn't advance the SQL/MED implementation, because you are not adding, say, some kind of CREATE FOREIGN TABLE support. You are just changing the dblink implementation to go through the FDW. I would argue that it should be the other way around: The FDW should go through dblink. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication patch for CommitFest 2009-09
After playing with this a little bit, I think we need logic in the slave to reconnect to the master if the connection is broken for some reason, or can't be established in the first place. At the moment, that is considered as the end of recovery, and the slave starts up. You have the trigger file mechanism to stop that, but it only gives you a chance to manually kill and restart the slave before it chooses a new timeline and starts up, it doesn't reconnect automatically. -- 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] clang's static checker report.
2009/9/15 Michael Meskes mes...@postgresql.org: Looking at http://zlew.org/postgresql_static_check/scan-build-2009-09-14-1/report-3LPmKK.html#EndPath it tells me that the value stored to 'counter' is never used. However, the counter++ is called inside a loop and thus will be read the next time the loop is run. Looks to me like a bug, or did I miss something? I guess that the problem is that the variable counter is declared inside that loop itself. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PGCluster-II Progress
I was searching info about PgCluster-II yesterday and there is not much information about it. Do can give to me any report of this? Because I need to know the progress of the project. On PgFoundry, only it talks about PgCluster-1.9, but not of the 2.x versions. Who is the PgCluster-II´s developer? Regards The hurry is enemy of the success: for that reason...Be patient Ing. Marcos L. Ortiz Valmaseda Línea Soporte y Despliegue Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Linux User # 418229 PostgreSQL User http://www.postgresql.org http://www.planetpostgresql.org/ http://www.postgresql-es.org/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGCluster-II Progress
I had the same question a while a go. After a lot of googling I found http://www.cybertec.at/english/start_e.html This seems to be an active replacement for the dead? pg-cluster HTH Regards, Serge Fonville On Tue, Sep 15, 2009 at 1:29 PM, Marcos Luis Ortiz Valmaseda mlor...@uci.cu wrote: I was searching info about PgCluster-II yesterday and there is not much information about it. Do can give to me any report of this? Because I need to know the progress of the project. On PgFoundry, only it talks about PgCluster-1.9, but not of the 2.x versions. Who is the PgCluster-II´s developer? Regards The hurry is enemy of the success: for that reason...Be patient Ing. Marcos L. Ortiz Valmaseda Línea Soporte y Despliegue Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Linux User # 418229 PostgreSQL User http://www.postgresql.org http://www.planetpostgresql.org/ http://www.postgresql-es.org/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGCluster-II Progress
Yeah, the problem here is that CyberCluster is based yet on PostgreSQL 8.1 and is a very old version to use it. I found the developer of PgCluster-II: Atsushi MITANI - mit...@sraw.co.jp The hurry is enemy of the success: for that reason...Be patient Ing. Marcos L. Ortiz Valmaseda Línea Soporte y Despliegue Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Linux User # 418229 PostgreSQL User http://www.postgresql.org http://www.planetpostgresql.org/ http://www.postgresql-es.org/ - Mensaje original - De: Serge Fonville serge.fonvi...@gmail.com Para: Marcos Luis Ortiz Valmaseda mlor...@uci.cu CC: pgsql-hackers pgsql-hackers@postgresql.org Enviados: Martes, 15 de Septiembre 2009 1:32:37 GMT -10:00 Hawai Asunto: Re: [HACKERS] PGCluster-II Progress I had the same question a while a go. After a lot of googling I found http://www.cybertec.at/english/start_e.html This seems to be an active replacement for the dead? pg-cluster HTH Regards, Serge Fonville On Tue, Sep 15, 2009 at 1:29 PM, Marcos Luis Ortiz Valmaseda mlor...@uci.cu wrote: I was searching info about PgCluster-II yesterday and there is not much information about it. Do can give to me any report of this? Because I need to know the progress of the project. On PgFoundry, only it talks about PgCluster-1.9, but not of the 2.x versions. Who is the PgCluster-II´s developer? Regards The hurry is enemy of the success: for that reason...Be patient Ing. Marcos L. Ortiz Valmaseda Línea Soporte y Despliegue Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Linux User # 418229 PostgreSQL User http://www.postgresql.org http://www.planetpostgresql.org/ http://www.postgresql-es.org/ -- 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] Resjunk sort columns, Heikki's index-only quals patch, and bug #5000
On Tue, Sep 15, 2009 at 5:47 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Robert Haas wrote: Hi, I'm reviewing this patch for the 2009-09 CommitFest. Thank you! It doesn't seem to compile. Looks like the patch has bitrotted, sorry about that. Attached is an updated version. I've also pushed this to my git repository at git://git.postgresql.org/git/users/heikki/postgres.git, branch heapfetch. OK, I'll pull from that. Actually, before I even tried compiling this, I was looking through the joinpath.c changes, since that is an area of the code with which I have some familiarity. As I'm sure you're aware, the lack of commenting makes it quite difficult to understand what this is trying to do, and the functions are poorly named. It isn't self-explanatory what bubbling up means, even in the limited context of joinpath.c. Yeah, understood :-(. The bubbling up refers to moving HeapFetch nodes above a join, which I explained earlier in this thread: http://archives.postgresql.org/message-id/4a90448f.4060...@enterprisedb.com, Leaving that aside, I think that the approach here is likely wrong; the decision about when to perform a heap fetch doesn't seem to be based on cost, which I think it needs to be. Right, cost estimation and making choices based on it still missing. Consider A IJ B, with the scan over A implemented as an index scan. It seems to me that if the join selectivity is 1, then assuming there's a choice, we probably want to join A to B and then do the heap fetches against A afterwards. But if the join selectivity is 1 (consider, for example, a cross join), we probably want to do the heap fetches first. Hmm, good point. I didn't consider that join selectivity can be 1. A more common scenario is that there's an additional filter condition on the HeapFetch, with a selectivity 1. It can then cheaper to perform the heap fetches first and only join the remaining rows that satisfy the filter condition. Well, again, it seems to me that it entirely depends on whether the IJ increases or decreases the number of rows. You want to do the heap fetches at the point where there are the fewest of them to do, and you can't know that a priori. When you start talking about more common scenarios, what you really mean is more common in the queries I normally do, and that's not the same as what other people's queries do. (See, for example, previous discussions on -performance, where it turns out that my suggested fix for a particular kind of planner problem is the exact opposite of Kevin Grittner's fix for a problem with the same code; the existing code bounds a certain value from below at 1 - I suggested raising it to 2, he suggested lowering it to 0.) It could also be cheaper to perform HeapFetch first if there's a lot of dead tuples in the table, as the heap fetch weeds them out. I'm not sure if we can estimate that in a meaningful way. Depends whether the selectivity calculations take this into account - off the top of my head, I'm not sure. Am I right to think that the heap fetch node is not optional? i.e. even if only columns in the index are ever used, we need to make sure that a heap fetch node gets inserted to check visibility. Is that right? Correct. The plan is to eventually use the visibility map to skip the heap access altogether, but we'll need to make the visibility map 100% reliable first. We'll still need a HeapFetch node to perform the visibility check, but it could perform it against the visibility map instead of the heap. I also think that the use of the term index-only quals is misleading. It seemed good when you first posted to -hackers about it, but looking at the patch, it means we have both index quals and index-only quals, and it seems like that might not be the greatest naming. Maybe we could call them index-tuple quals or index-evaluated quals or something. Hmm, I'm not too fond of the naming either. Not sure I like those alternatives any better, though. Maybe someone else will come up with a better idea. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
2009/9/15 Jeff Davis pg...@j-davis.com: Attached is the latest version. Hi Jeff, I'm just getting started reviewing this version now. I noticed that your patch seems to have been generated by git. Are you hosting this work on a public repo somewhere that I can pull from? Also I think the committers generally prefer context diffs (pipe it through filterdiff --format=context --strip=1) in submissions. Regarding the documentation updates, I think you might want to add some commentary to Chapter 11: Indexes -- perhaps add a new section after 11.6 Unique Indexes to talk about general index constraints, and/or update the wording of 11.6 to reflect your changes. The paragraph explaining index_constraints in ALTER TABLE may be a little bit confusing. quote ADD index_constraint This form adds a new index constraint to the table which is enforced by the given index. The operator provided must be usable as a search strategy for the index, and it also must detect conflicts symmetrically. The semantics are similar to a unique index but it opens up new possibilities. A unique index can only detect conflicts when the two values are equal, and that behavior is equivalent to an index constraint where all operators are the equality operator. However, an index constraint allows you to use other operators as well, such as the overlaps operator provided for the circle data type. The index constraint will ensure that no two circles overlap. See example below. /quote My eyes started to cross in the second sentence. Detect conflicts symmetrically? I have actually *used* this feature successfully in testing the patch, and I still don't know quite what to make of that phrase. You might need to dumb it down. It might also be good to be a bit more explicit about the way the choice of operators works. It is the inverse of the logic used to express an ordinary value constraint. E.g., when you use the equality operator in an index constraint you are in effect saying that new rows MUST NOT satisfy this operator for any existing rows. I'll continue reviewing and post more comments on the code itself shortly. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
2009/9/15 Jeff Davis pg...@j-davis.com: Attached is the latest version. The new error message for a conflict is: ERROR: index constraint violation detected DETAIL: tuple conflicts with existing data How about also including the name of the constraint (or index) that was violated? I could imagine this error message being frustrating for someone who had a table with multiple index constraints, as they wouldn't know which one had raised the conflict. Also, the DETAIL part should be written as a full sentence with leading capital and full stop [1], see I deliberately tried to create an index constraint using a bogus operator, to see what would happen: postgres=# alter table circles add constraint circles_overlap (c -) using index circle_idx; ERROR: no strategy found for operator 1520 in operator family 2595 The error message is pretty unfriendly, but I'm ambivalent about whether it's worth doing anything about this particular case. One of the comments I made in my original review [2] was that \d in psql should show the constraint. I don't think you've addressed this in the current version. Cheers, BJ [1] http://www.postgresql.org/docs/current/static/error-style-guide.html [2] http://archives.postgresql.org/message-id/37ed240d090715w7ccfc13i8ce8d11a0c51...@mail.gmail.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] Linux LSB init script
On Wed, 2009-09-02 at 15:06 -0500, Kevin Grittner wrote: Wolfgang Wilhelm wolfgang20121...@yahoo.de wrote: if [ $# -lt 1 -o $1 = ] ] ; then Oops. Fixed patch attached. Thanks! The commitfest lists this as the last patch, but there was some discussion after this. Could you/we clarify what is actually proposed for inclusion now? I have seen proposals for: - Linux LSB init script - Linux non-LSB init script - SUSE specific init script I can see all of these as being useful, but the question might be what we want to commit to maintaining. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-09: Now In Progress
On Tue, Sep 15, 2009 at 1:36 AM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2009-09-14 at 21:14 -0400, Robert Haas wrote: [P.S. I learned my lesson - last CF the equivalent email said that the CF was closed, which of course was not what I meant at all.] Yeah, except is it just me or is this open terminology equally weird? Isn't the 2009-09 fest the one that is open right now? I can't win. I believe the terminology we've been using, at least for the past year since I've been involved, is as follows: Open = open to new patches In Progress = working on reviewing and committing patches, no longer open to new patches Closed = all patches have been dealt with So, no, 2009-09 was open yesterday. Now it's in progress, and 2009-11 is open. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
Sam == Sam Mason s...@samason.me.uk writes: But there's a kicker: in Subclause 6.12, cast specification, in the General Rules is: a) If the cast operand specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied. That no further General Rules clause implies (assuming it's not a blatant mistake in the spec) that this rule is therefore skipped in the case of nulls: Sam I think the NOT NULL constraint is a PG specific constraint, I Sam can't see how it's allowed in the spec. That's a good point; it doesn't seem to be. But the spec _does_ appear to allow CHECK(VALUE IS NOT NULL) as a domain constraint (in general the spec defines NOT NULL constraints this way), and the wording from 6.12 implies that that check is still skipped in the case of NULLs (so that constraint would stop you inserting a null into a table column (I think), but not from casting a null value to the domain type). Sam The NOT NULL constraint feels wrong as well, what are the Sam semantics of: Sam CREATE DOMAIN d AS INTEGER NOT NULL; Sam SELECT a.n AS aa, b.n AS bb Sam FROM (VALUES (CAST(1 AS d)),(2)) a(n) Sam LEFT JOIN (VALUES (CAST(1 AS d))) b(n) ON a.n = b.n; Sam in the presence of it? I'm expecting aa and bb both to come out Sam as domain d, but this shouldn't work with what you're saying Sam the current semantics should be. I think that's just another example of Tom's initial comment about how broken domain not null constraints are currently. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-09: Now In Progress
2009/9/15 Robert Haas robertmh...@gmail.com: I believe the terminology we've been using, at least for the past year since I've been involved, is as follows: Open = open to new patches In Progress = working on reviewing and committing patches, no longer open to new patches Closed = all patches have been dealt with So, no, 2009-09 was open yesterday. Now it's in progress, and 2009-11 is open. Yeah, that's what we've been using to date, but I do agree with Stefan that it's a bit confusing. I think part of the problem is that open and closed are normally opposites, but in our case they are talking about different things. Perhaps we should move to something like: Accepting contributions = Under review = Complete. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
Andrew Gierth and...@tao11.riddles.org.uk writes: Sam == Sam Mason s...@samason.me.uk writes: Sam The NOT NULL constraint feels wrong as well, what are the Sam semantics of: Sam CREATE DOMAIN d AS INTEGER NOT NULL; Sam SELECT a.n AS aa, b.n AS bb Sam FROM (VALUES (CAST(1 AS d)),(2)) a(n) Sam LEFT JOIN (VALUES (CAST(1 AS d))) b(n) ON a.n = b.n; Sam in the presence of it? I'm expecting aa and bb both to come out Sam as domain d, but this shouldn't work with what you're saying Sam the current semantics should be. I think that's just another example of Tom's initial comment about how broken domain not null constraints are currently. Well, the LEFT JOIN case is exactly why I feel that domain not-null constraints are inherently broken. The only clean way around it is to decree that the output of a left join is not of the domain type after all, but of its base type. Which seems to me to be one side effect of the wording in 4.17.4, though they are extending it to *all* evaluation contexts not only outer joins. I haven't yet read the additional material you guys found ... 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] CommitFest 2009-09: Now In Progress
Brendan Jurd wrote: Perhaps we should move to something like: Accepting contributions = Under review = Complete. I say paint the bikeshed yellow! (h/t Dimitri) 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 11:21:14PM +1000, Brendan Jurd wrote: 2009/9/15 Jeff Davis pg...@j-davis.com: Attached is the latest version. The new error message for a conflict is: ERROR: index constraint violation detected DETAIL: tuple conflicts with existing data How about also including the name of the constraint (or index) that was violated? I could imagine this error message being frustrating for someone who had a table with multiple index constraints, as they wouldn't know which one had raised the conflict. Perhaps the tuple that caused the violation as well, like UNIQUE index violations already do? Even if we know what constraint has been tripped, we might not know what value did it. j...@josh# create table a (a integer); j...@josh*# create unique index a_unique on a (a); j...@josh*# insert into a values (1), (2), (3); j...@josh*# insert into a values (8), (3), (4); ERROR: duplicate key value violates unique constraint a_unique DETAIL: Key (a)=(3) already exists. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] CommitFest 2009-09: Now In Progress
On Tue, Sep 15, 2009 at 10:08 AM, Andrew Dunstan and...@dunslane.net wrote: Brendan Jurd wrote: Perhaps we should move to something like: Accepting contributions = Under review = Complete. I say paint the bikeshed yellow! (h/t Dimitri) -1. Yellow bikesheds are sometimes mistaken for giant bees that have lost their black stripes. Blue is much preferable. I realized, too, that we have a handy paragraph of text that explains all this and provides useful links. You can find it at: https://commitfest.postgresql.org/ :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
On Tue, Sep 15, 2009 at 02:54:18PM +0100, Andrew Gierth wrote: the spec _does_ appear to allow CHECK(VALUE IS NOT NULL) as a domain constraint (in general the spec defines NOT NULL constraints this way), Huh, that's a trivial rewrite isn't it. Not sure why it didn't occur to me that it's just syntax sugar. and the wording from 6.12 implies that that check is still skipped in the case of NULLs (so that constraint would stop you inserting a null into a table column (I think), but not from casting a null value to the domain type). Explicitly ignoring NULL values in CAST expressions seems like a good feature as well. Although it gives me the feeling that domains are more and more like a mis-designed feature. Sam == Sam Mason s...@samason.me.uk writes: Sam The NOT NULL constraint feels wrong as well, I think that's just another example of Tom's initial comment about how broken domain not null constraints are currently. Hum, given that it's just sugar for more general constraints I'm not sure if it's the not null constraints that are broken or just the current interpretation of them. They would do the right thing if they were only checked in a limited number of places that the user was aware of, which the spec seems to imply is when the user explicitly asks for a CAST to be performed or when writing into the table. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
Scott Mohekey scott.mohe...@telogis.com wrote: I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as TIMESTAMP at GMT. We then convert it to a users local timezone within application code. That sounds like an accident waiting to happen. Sure, you can make it work, but you're doing things the hard way, and the defaults will probably be to do the wrong thing. TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, in that it doesn't store a time zone with the timestamp. What it does do is store the timestamp in GMT, so that it represents a moment in time, changing the representation of the moment to local time in any time zone as needed. This sounds a lot like what you're trying to do -- a natural fit. If you want to see it in GMT, that easy enough. If you want to see it as local time in any other time zone, that's easily done without risk of actually getting a timestamp representing the wrong moment. TIMESTAMP WITHOUT TIME ZONE is stored raw and is not considered to be associated to a time zone until you do so. It will default to assigning the time zone set on your server, which is normally your local time zone. Unless that's GMT, you will need to be very careful to always localize the timestamp to GMT before doing anything with it. -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] Timestamp to time_t
On Tue, Sep 15, 2009 at 09:23:09AM -0500, Kevin Grittner wrote: Scott Mohekey scott.mohe...@telogis.com wrote: I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as TIMESTAMP at GMT. We then convert it to a users local timezone within application code. That sounds like an accident waiting to happen. Sure, you can make it work, but you're doing things the hard way, and the defaults will probably be to do the wrong thing. TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, in that it doesn't store a time zone with the timestamp. I've looked through SQL:2008 (well, through 6WD2_02_Foundation_2007-12.pdf), and I didn't find anything that implies that the input time zone needs to be retrievable, nor anything that would specify the syntax for doing so. Can you point me to a section? Lots of people, including your humble emailer, would find it very handy to be able to access such information, but I thought TIMESTAMP WITH TIME ZONE only needed to be retrieved either as default time zone, or as whatever AT TIME ZONE specified. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
David Fetter da...@fetter.org writes: I've looked through SQL:2008 (well, through 6WD2_02_Foundation_2007-12.pdf), and I didn't find anything that implies that the input time zone needs to be retrievable, nor anything that would specify the syntax for doing so. EXTRACT()? 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] errcontext support in PL/Perl
On Tue, 2009-07-21 at 20:54 +0300, Alexey Klyukin wrote: Attached is the updated version of the patch (the original description is here: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01332.php) that doesn't use global variables. Patch looks OK. But for extra credit, couldn't we code it so that the context is set before the PL handler is called, so that we get this functionality into all PLs at once? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: Kevin TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PGCluster-II Progress
On Tue, Sep 15, 2009 at 7:48 AM, Marcos Luis Ortiz Valmaseda mlor...@uci.cu wrote: Yeah, the problem here is that CyberCluster is based yet on PostgreSQL 8.1 and is a very old version to use it. I found the developer of PgCluster-II: Atsushi MITANI - mit...@sraw.co.jp Yeah, AFAICS, PGCluster II is and has been dead for years. I did an architectural review of PGCluster II while at EnterpriseDB, and it's never going to work from a performance perspective. Unfortunately, the architecture (as it stands) requires coordination of pretty much all locks and semaphores, and will only run on a single system because it requires a nearly-identical shared memory segment for each instance. Unlike Oracle RAC, which was designed to be run on separate nodes and share/coordinate only relevant resources, PGCluster II coordinates almost everything (transaction ids, buffer locks, etc.). As an example, Oracle doesn't need to communicate with every node in the cluster to generate a new transaction id (SCN in Oracle parlance) because it employs a system based on Lamport timestamps, but PGCluster II has to coordinate a global transaction id across all nodes. Also, PGCluster II has no concept of shared data/local WAL. Whereas Oracle supports threads of REDO/UNDO generated by the local node, all WAL in PGCluster II has to be coordinated between nodes, which creates a bottleneck on *all* concurrent workloads. When I first saw the demonstration of PGCluster II, I was in awe. Unfortunately, when I reviewed the architecture, I saw that it was too good to be true. Perhaps it has been rearchitected in private to overcome some of these issues, but I'm not aware of it. All attempts to talk to Atsushi about it were met with no response. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Timestamp to time_t
On Tue, Sep 15, 2009 at 11:02:52AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: I've looked through SQL:2008 (well, through 6WD2_02_Foundation_2007-12.pdf), and I didn't find anything that implies that the input time zone needs to be retrievable, nor anything that would specify the syntax for doing so. EXTRACT()? I see that EXTRACT() can take a time zone as input, but I don't see anywhere that could distinguish among the following inputs, once stored, as they have identical representations in UTC: SELECT now() AS West Oakland, now() AT TIME ZONE 'UTC' AS Greenwich, now() AT TIME ZONE 'Asia/Shanghai' AS Pudong; West Oakland | Greenwich |Pudong ---++ 2009-09-15 08:27:00.306403-07 | 2009-09-15 15:27:00.306403 | 2009-09-15 23:27:00.306403 (1 row) The way we store TIMESTAMP WITH TIME ZONE, the database converts to UTC, discarding the input time zone in the process. SQL:2008 appears to allow this, and doesn't appear to have a way to retrieve that input time zone once a TIMESTAMP WITH TIME ZONE field has been stored. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] errcontext support in PL/Perl
Peter Eisentraut pete...@gmx.net writes: But for extra credit, couldn't we code it so that the context is set before the PL handler is called, so that we get this functionality into all PLs at once? FWIW, I don't particularly agree with that --- there is no reason to suppose that all PLs will want to do this exactly the same way. Even if they did, the amount of code shared would only be a few lines. It would likely end up being *more* code not less by the time you'd found a way to do it in common (since e.g. the function caches would not be the same for all PLs, if they even had one). 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] PGCluster-II Progress
On Tue, 2009-09-15 at 07:29 -0400, Marcos Luis Ortiz Valmaseda wrote: I was searching info about PgCluster-II yesterday and there is not much information about it. Do can give to me any report of this? Because I need to know the progress of the project. It is dead. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Timestamp to time_t
David Fetter da...@fetter.org writes: On Tue, Sep 15, 2009 at 11:02:52AM -0400, Tom Lane wrote: EXTRACT()? I see that EXTRACT() can take a time zone as input, but I don't see anywhere that could distinguish among the following inputs, once stored, as they have identical representations in UTC: See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular b) Otherwise, let TZ be the interval value of the implicit or explicit time zone associated with the datetime value expression. If extract field is TIMEZONE_HOUR, then the result is calculated as EXTRACT (HOUR FROM TZ) Otherwise, the result is calculated as EXTRACT (MINUTE FROM TZ) I haven't tracked down whether the phrase implicit or explicit time zone is hiding any interesting weasel words, but it sure *looks* like you are supposed to be able to pull out the TZ offset. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] hardware information
Hello all.. I'm using PostgreSQL 8.3.. How can I get information about the hardware utilization: - CPU usage. - Disk space. - Memory allocation. thank you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Can the PostgreSQL weekly news be retrospectively edited?
Hi, I saw the editor added this line: Zoltan Boszormenyi sent in a small patch to fix a typo in an earlier ECPG patch he sent. This typo fix is an upstream bugfix. Thanks. -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
Andrew Gierth and...@tao11.riddles.org.uk wrote: Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: Kevin TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. Codd went on at some length about why this is the right thing to do. He was highly critical of systems where adding a month to a date and then subtracting month from the result could result in a date which was off from the original date by as much as three days. As a mathematician he felt strongly that (x + y) - y should equal x -- even when x is a date and y is an interval. Of course, you need to support the whole, coherent set of operations for it to make sense; if you take this particular operation out of context and put it together with other operations which don't follow his coherent set of rules, it does look silly. Treating stored dates as an abstraction which is mapped to the actual calendar as needed is different, but hardly foolish. Such features would make it a bit easier for software, for example, to properly handle a court order that someone make an initial payment on a given date (say January 30th) and then the same day of each subsequent month until the amount is paid in full. From what review I've done of it, it holds together as a complete system; the question is how many little bits and pieces can be adopted into a fundamentally different system and still have them make sense. Personally, I think that including time zone in the TIMESTAMP WITH TIME ZONE data type would go a long way toward making some useful features work. -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] WIP: generalized index constraints
On Tue, 2009-09-15 at 22:52 +1000, Brendan Jurd wrote: I'm just getting started reviewing this version now. I noticed that your patch seems to have been generated by git. Are you hosting this work on a public repo somewhere that I can pull from? I just requested a public repo. I will publish there as soon as its approved. Also I think the committers generally prefer context diffs (pipe it through filterdiff --format=context --strip=1) in submissions. Thanks, I will do that for my future patch submissions. Regarding the documentation updates, I think you might want to add some commentary to Chapter 11: Indexes -- perhaps add a new section after 11.6 Unique Indexes to talk about general index constraints, and/or update the wording of 11.6 to reflect your changes. Will do. My eyes started to cross in the second sentence. Detect conflicts symmetrically? I have actually *used* this feature successfully in testing the patch, and I still don't know quite what to make of that phrase. You might need to dumb it down. Will do. It might also be good to be a bit more explicit about the way the choice of operators works. It is the inverse of the logic used to express an ordinary value constraint. E.g., when you use the equality operator in an index constraint you are in effect saying that new rows MUST NOT satisfy this operator for any existing rows. I'll include that, thanks. I appreciate the quick feedback; I'll make these changes tonight. 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] PGCluster-II Progress
On Tue, 2009-09-15 at 13:16 -0300, Marc G. Fournier wrote: Odd, I talked to him a couple of weeks ago and he was working on a new release in preparation for some upcoming talks he was doing ... was working on bringing it up to support 8.3.x ... But, I'm just prepareing new version of the PGCluster... Mitani was probably talking about PGCluster, not PGCluster -II. Mitani? Last time I heard about PGCluster-II was at Anniversary Summit(2006). Oh, and I met with him at France at 2007, and no improvements since then. Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] PGCluster-II Progress
Odd, I talked to him a couple of weeks ago and he was working on a new release in preparation for some upcoming talks he was doing ... was working on bringing it up to support 8.3.x ... But, I'm just prepareing new version of the PGCluster... Mitani ... any status on this? On Tue, 15 Sep 2009, Devrim G?ND?Z wrote: On Tue, 2009-09-15 at 07:29 -0400, Marcos Luis Ortiz Valmaseda wrote: I was searching info about PgCluster-II yesterday and there is not much information about it. Do can give to me any report of this? Because I need to know the progress of the project. It is dead. -- Devrim G?ND?Z, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, 2009-09-15 at 23:21 +1000, Brendan Jurd wrote: How about also including the name of the constraint (or index) that was violated? I could imagine this error message being frustrating for someone who had a table with multiple index constraints, as they wouldn't know which one had raised the conflict. Yes, that makes sense. As Joshua Tolley mentions, I'll also include the tuples that caused the conflict. Also, the DETAIL part should be written as a full sentence with leading capital and full stop [1], see Oh, I haven't seen that document before. Thanks. postgres=# alter table circles add constraint circles_overlap (c -) using index circle_idx; ERROR: no strategy found for operator 1520 in operator family 2595 The error message is pretty unfriendly, but I'm ambivalent about whether it's worth doing anything about this particular case. I think I could make that error a little better by providing a detail message explaining what the operator should be able to do. One of the comments I made in my original review [2] was that \d in psql should show the constraint. I don't think you've addressed this in the current version. I have psql on my list along with pg_dump, but unfortunately I haven't done either yet. I don't think it will take too much work, so I'll fix this as soon as I can. 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] WIP: generalized index constraints
On Tue, 2009-09-15 at 08:08 -0600, Joshua Tolley wrote: Perhaps the tuple that caused the violation as well, like UNIQUE index violations already do? Even if we know what constraint has been tripped, we might not know what value did it. Or, even better, include both tuples. With these new constraints the conflicting tuples aren't necessarily equal. 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] Timestamp to time_t
On Sep 15, 2009, at 8:50 AM, Tom Lane wrote: See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular b) Otherwise, let TZ be the interval value of the implicit or explicit time zone associated with the datetime value expression. If extract field is TIMEZONE_HOUR, then the result is calculated as EXTRACT (HOUR FROM TZ) Otherwise, the result is calculated as EXTRACT (MINUTE FROM TZ) I haven't tracked down whether the phrase implicit or explicit time zone is hiding any interesting weasel words, but it sure *looks* like you are supposed to be able to pull out the TZ offset try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ Los_Angeles'::timestamptz); date_part --- 0 (1 row) That doesn't look right. AFAICT, timestamptz converts the TZ to the default TZ and discards the specified TZ. Same with offsets: try=# select extract(timezone_hour from '2001-02-16 20:38:40 -08:00'::timestamptz);date_part --- 0 (1 row) Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 12:18 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-09-15 at 22:52 +1000, Brendan Jurd wrote: I'm just getting started reviewing this version now. I noticed that your patch seems to have been generated by git. Are you hosting this work on a public repo somewhere that I can pull from? I just requested a public repo. I will publish there as soon as its approved. Also I think the committers generally prefer context diffs (pipe it through filterdiff --format=context --strip=1) in submissions. Thanks, I will do that for my future patch submissions. Regarding the documentation updates, I think you might want to add some commentary to Chapter 11: Indexes -- perhaps add a new section after 11.6 Unique Indexes to talk about general index constraints, and/or update the wording of 11.6 to reflect your changes. Will do. My eyes started to cross in the second sentence. Detect conflicts symmetrically? I have actually *used* this feature successfully in testing the patch, and I still don't know quite what to make of that phrase. You might need to dumb it down. Will do. It might also be good to be a bit more explicit about the way the choice of operators works. It is the inverse of the logic used to express an ordinary value constraint. E.g., when you use the equality operator in an index constraint you are in effect saying that new rows MUST NOT satisfy this operator for any existing rows. I'll include that, thanks. I appreciate the quick feedback; I'll make these changes tonight. Instead of calling these generalized index constraints, I wonder if we oughtn't to be calling them something like don't-overlap constraints (that's a bad name, but something along those lines). They're not really general at all, except compared to uniqueness constraints (and they aren't called generalized unique-index constraints, just generalized index constraints). I didn't realize understand what this was all for until I read Brendan's review. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hardware information
On Tue, Sep 15, 2009 at 5:34 AM, std pik std...@gmail.com wrote: Hello all.. I'm using PostgreSQL 8.3.. How can I get information about the hardware utilization: - CPU usage. - Disk space. - Memory allocation. thank you. This question would be more appropriate for pgsql-general or pgsql-novice. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
2009/9/16 Robert Haas robertmh...@gmail.com: Instead of calling these generalized index constraints, I wonder if we oughtn't to be calling them something like don't-overlap constraints (that's a bad name, but something along those lines). They're not really general at all, except compared to uniqueness constraints (and they aren't called generalized unique-index constraints, just generalized index constraints). Well generalized index constraints is what we're calling the patch, but I don't think they are called by that name anywhere in the proposed documentation changes. In the extension to ALTER TABLE syntax, they are simply referred to as index_constraint. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] errcontext support in PL/Perl
On tis, 2009-09-15 at 11:32 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: But for extra credit, couldn't we code it so that the context is set before the PL handler is called, so that we get this functionality into all PLs at once? FWIW, I don't particularly agree with that --- there is no reason to suppose that all PLs will want to do this exactly the same way. I'd imagine that we simply set the context to $language function $name, probably in fmgr_info_other_lang(). If a language wants more than that, it can set another level of context. Of course this way we would not save much code in, say, PL/Perl, but all the other PLs that are currently not using this stuff at all would get it for free. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote: Instead of calling these generalized index constraints, I wonder if we oughtn't to be calling them something like don't-overlap constraints (that's a bad name, but something along those lines). They're not really general at all, except compared to uniqueness constraints (and they aren't called generalized unique-index constraints, just generalized index constraints). What would you like to be able to enforce using an index that can't be solved by this patch? It only works for constraints entirely within a single table, can you think of a way to express that better? In the code/docs, mostly I call them just index constraints or some variation thereof. But for the lists, I think that might be too vague. I don't want to call them don't overlap constraints, because it's not limited to a non-overlapping constraint. I also don't think generalized unique-index constraints is a good name: it's confusing and it makes it sound like it is some new way to use a unique index. 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] hardware information
On 9/15/09 2:34 AM, std pik wrote: Hello all.. I'm using PostgreSQL 8.3.. How can I get information about the hardware utilization: - CPU usage. - Disk space. - Memory allocation. thank you. This is not a question for the -hackers mailing list. Please post your question to pgsql-performance instead. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Linux LSB init script
Peter Eisentraut pete...@gmx.net wrote: The commitfest lists this as the last patch, but there was some discussion after this. Could you/we clarify what is actually proposed for inclusion now? I have seen proposals for: - Linux LSB init script - Linux non-LSB init script - SUSE specific init script I can see all of these as being useful, but the question might be what we want to commit to maintaining. The patch is for Linux LSB init script. I withdrew an earlier suggested patch for the generic Linux init script, since what's already there seems to be pretty good, even if it hasn't been modified in years. The only change I had suggested was to add an LSB block, which I later decided was better addressed by a separate script. A few people have commented that the non-LSB script could use some improvements, but I don't recall any specific suggestions; and I couldn't see any obvious ones. SuSE supplies a script with its distribution of PostgreSQL. The SuSE licenses prelude incorporating it or any derivative work in the PostgreSQL product. The LSB init script should also work for SuSE; I see no point in trying to produce some other SuSE-specific script. -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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote: Instead of calling these generalized index constraints, I wonder if we oughtn't to be calling them something like don't-overlap constraints (that's a bad name, but something along those lines). They're not really general at all, except compared to uniqueness constraints (and they aren't called generalized unique-index constraints, just generalized index constraints). What would you like to be able to enforce using an index that can't be solved by this patch? It only works for constraints entirely within a single table, can you think of a way to express that better? In the code/docs, mostly I call them just index constraints or some variation thereof. But for the lists, I think that might be too vague. I don't want to call them don't overlap constraints, because it's not limited to a non-overlapping constraint. Oh. What else can you do with it? I also don't think generalized unique-index constraints is a good name: it's confusing and it makes it sound like it is some new way to use a unique index. I agree. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
2009/9/16 Robert Haas robertmh...@gmail.com: On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis pg...@j-davis.com wrote: I don't want to call them don't overlap constraints, because it's not limited to a non-overlapping constraint. Oh. What else can you do with it? Anything that there is an operator for. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] errcontext support in PL/Perl
Peter Eisentraut pete...@gmx.net writes: On tis, 2009-09-15 at 11:32 -0400, Tom Lane wrote: FWIW, I don't particularly agree with that --- there is no reason to suppose that all PLs will want to do this exactly the same way. I'd imagine that we simply set the context to $language function $name, probably in fmgr_info_other_lang(). If a language wants more than that, it can set another level of context. So if we want to emit something like $language function $name line $lineno, that now has to be two separate lines of context? There'd be no clean way for the PL to suppress the one it doesn't really need. Of course this way we would not save much code in, say, PL/Perl, but all the other PLs that are currently not using this stuff at all would get it for free. It would be very far from being for free, because there's no inexpensive way for a general-purpose hook to get hold of either $language or $name without knowing anything about the internals of the PL. It would have to fetch the relevant pg_language and pg_proc rows, the former being unnecessary for the PL itself, and the latter being almost certainly redundant with what the PL is doing. You could eliminate the performance objection perhaps by fetching the rows only if the context hook is actually called, but then you have added failure modes that weren't there before (if the fetch fails for some reason). Also, there is noplace to establish the hook anyway (without adding another layer of call overhead). fmgr_info cannot do it, because it's not in the actual runtime call chain. Between the expense, the low return, and the high probability of not being exactly what's wanted, I don't think this seems like a good design choice. 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 1:14 PM, Brendan Jurd dire...@gmail.com wrote: 2009/9/16 Robert Haas robertmh...@gmail.com: On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis pg...@j-davis.com wrote: I don't want to call them don't overlap constraints, because it's not limited to a non-overlapping constraint. Oh. What else can you do with it? Anything that there is an operator for. Uhh so what happens if I create an index constraint using the +(integer, integer) operator? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
David E. Wheeler da...@kineticode.com writes: On Sep 15, 2009, at 8:50 AM, Tom Lane wrote: See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ Los_Angeles'::timestamptz); You appear to be confusing what PG currently does with what the spec says. 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] hardware information
std pik std...@gmail.com wrote: How can I get information about the hardware utilization: This list is for discussing development of the PostgreSQL product. Please re-post on the novice or admin list. You'll be more likely to get a useful reply if you give people more information, like what operating system you use. (The answer for Linux is going to be different from Windows, etc.) -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] WIP: generalized index constraints
2009/9/16 Robert Haas robertmh...@gmail.com: On Tue, Sep 15, 2009 at 1:14 PM, Brendan Jurd dire...@gmail.com wrote: 2009/9/16 Robert Haas robertmh...@gmail.com: On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis pg...@j-davis.com wrote: I don't want to call them don't overlap constraints, because it's not limited to a non-overlapping constraint. Oh. What else can you do with it? Anything that there is an operator for. Uhh so what happens if I create an index constraint using the +(integer, integer) operator? Okay, so my first answer was a simplification. You can use any operator that has an appropriate index strategy entry. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
On Sep 15, 2009, at 10:17 AM, Tom Lane wrote: try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ Los_Angeles'::timestamptz); You appear to be confusing what PG currently does with what the spec says. Sorry, I thought you were referring to what PostgreSQL does. Would I be wrong in thinking that the current behavior might be surprising to some? I mean, I'd really like a timestamptz that tracked the tz or offset that was used to create its value… I'm sure that's been on the ToDo list for time immemorial. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: Uhh so what happens if I create an index constraint using the +(integer, integer) operator? You can use any operator that has an index search strategy. Overlaps is probably the most useful, but you could imagine other operators, like a bi-directional containment operator (either LHS is contained in RHS, or vice-versa). You can also get creative and have a similarity operator that determines whether two tuples are too similar. As long as it is symmetric, the feature will work. Or just use wrap random() in an operator and see what happens ;) 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 1:28 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: Uhh so what happens if I create an index constraint using the +(integer, integer) operator? You can use any operator that has an index search strategy. Overlaps is probably the most useful, but you could imagine other operators, like a bi-directional containment operator (either LHS is contained in RHS, or vice-versa). You can also get creative and have a similarity operator that determines whether two tuples are too similar. As long as it is symmetric, the feature will work. So it allows us to create constraints of the following form? For all A in the index, there exists no B in the index such that the given operator (which must be a binary operator returning boolean) holds of A and B. If that's correct, I think we should definitely at least mention the word overlap somewhere in the documentation, because that's what people are going to want to use it for, and it's hard to conceptualize without examples, at least for me. You may already be doing this, I haven't read the patch. Also, there are certainly other things you could want to do that can't be handled by this approach. Perhaps you'd like to create a constraint that a given value can appear at most twice, or a two column index (A, B) such that for any A the smallest value of B is less than A. These are certainly less common requirements than what you're talking about here, and I don't think it's important to try to support them - at least not at this point - but the word generalized doesn't give me a clue that I won't be able to do those things but I will be able to make an index that prevents my users from handing out duplicate IP blocks. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
Kevin Grittner kevin.gritt...@wicourts.gov writes: Andrew Gierth and...@tao11.riddles.org.uk wrote: Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. Codd went on at some length about why this is the right thing to do. He was highly critical of systems where adding a month to a date and then subtracting month from the result could result in a date which was off from the original date by as much as three days. As a mathematician he felt strongly that (x + y) - y should equal x -- even when x is a date and y is an interval. [ shrug... ] We *have* that property, for sane cases such as adding and subtracting a fixed number of days. For less sane cases, I would point out to Codd that the current calendar system was not designed by mathematicians, and trying to superimpose strict mathematical rules on it just leads to nonsense (like the spec's requirements). 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] Timestamp to time_t
Kevin == Kevin Grittner kevin.gritt...@wicourts.gov writes: Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. Kevin Codd went on at some length about why this is the right thing Kevin to do. He was highly critical of systems where adding a month Kevin to a date and then subtracting month from the result could Kevin result in a date which was off from the original date by as Kevin much as three days. As a mathematician he felt strongly that Kevin (x + y) - y should equal x -- even when x is a date and y is Kevin an interval. Mathematical elegance is all very well, but until you convince the real world to abandon inelegant concepts like months with unequal lengths, the database has to behave in ways that are useful within the constraints of actual practice. (To me, the fact that the spec's idea of 2009-01-31 + 1 month corresponds to a value that current_date will never be equal to is a far greater show-stopper.) To look specifically at timezones, the problem with the spec here is that it doesn't store _timezones_, it stores _timezone offsets_. So per the spec, (timestamp with time zone '2009-01-01 12:00:00 +' + interval 6 months) would be equal to '2009-07-01 12:00:00 +' (REGARDLESS of what the server's timezone is configured as), which is remarkably non-useful; also, the spec's idea of + interval 24 hours is equal to + interval 1 day, whereas here in the real world those aren't the same thing at all. Worse still, the spec defines the behaviour of DST as follows: the server has a specific timezone _offset_, that offset _changes_ on DST start/end, and conversions between timestamp w/o tz and timestamptz are done USING THE SERVER'S CURRENT OFFSET, NOT THE OFFSET AS IT WOULD HAVE BEEN AT THE TIME VALUE BEING CONVERTED. This is so wrong there aren't even words to describe how wrong it is. Kevin Personally, I think that including time zone in the TIMESTAMP Kevin WITH TIME ZONE data type would go a long way toward making Kevin some useful features work. It would break far too many other things in the process. If you want to store both a timestamp and an associated timezone you can do it right now, using a composite type or two columns, with the advantage that you get semantics that you can rely on. -- 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] revised hstore patch
Accidentally left the doc patch out of the hstore patch posted previously, so here it is as a separate patch. -- Andrew (irc:RhodiumToad) hstore-doc-20090914.patch.gz Description: hstore doc patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] syslog_line_prefix
Magnus Hagander wrote: I'm not sure I like this as a GUC. We're going to end up with a lot of different GUCs, and everytime we add a new log destination (admittedly not often, of course), that increases even further. And GUCs really don't provide the level of flexibility you'd really like to have. I've been thinking (long-term) in the direction of a separate config file, since that could contain an arbitrary number of lines, with rules on them (somewhat like pg_hba.conf maybe). I tend to agree with this idea, but I'm not sure about rejecting the current patch because of it. FWIW one of the things that this rules of logging config system should support is configuring each type of server process differently, for example set min_log_level to debug2 for autovacuum only, etc. -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Timestamp to time_t
Tom Lane wrote: For less sane cases, I would point out to Codd that the current calendar system was not designed by mathematicians, and trying to superimpose strict mathematical rules on it just leads to nonsense (like the spec's requirements). He's not listening ... Strangely (or perhaps not), he stopped listening around the time I started working on Postgres ... 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] WIP: generalized index constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote: Uhh so what happens if I create an index constraint using the +(integer, integer) operator? You can use any operator that has an index search strategy. Overlaps is probably the most useful, but you could imagine other operators, like a bi-directional containment operator (either LHS is contained in RHS, or vice-versa). Does it behave sanely for operators that are non-commutative, such as ''? (I'm not even very sure that I know what sanely would be in such a 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] Timestamp to time_t
Tom Lane t...@sss.pgh.pa.us wrote: [ shrug... ] We *have* that property, for sane cases such as adding and subtracting a fixed number of days. Adding and subtracting months is very common in business software. I have seen application bugs related to this many times. I suspect that such bugs would occur less often with a more abstract date type and a date normalization strategy for mapping to the calendar than it does with typical techniques; but it's not something I would propose that PostgreSQL move toward. (Well, maybe some day as a pgfoundry project or something, given that such a system could plug right in, but not as the default date handling -- for compatibility, if nothing else.) I was just reacting to the assertion that date abstraction was such a stupid thing to do that nothing else proposed in a document which supports it is worth considering. The Turing Award isn't usually awarded to those proposing complete nonsense. -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] WIP: generalized index constraints
On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote: So it allows us to create constraints of the following form? For all A in the index, there exists no B in the index such that the given operator (which must be a binary operator returning boolean) holds of A and B. Yes. And it's slightly more complicated for multi-column constraints: For all tuples A in the index with attributes 1 to N, there exists no tuple B such that: A1 op1 B1 AND A2 op2 B2 AND ... AN op2 BN If all operators are =, and the index implements searching on equality, it's semantically equivalent to a unique index. If that's correct, I think we should definitely at least mention the word overlap somewhere in the documentation, because that's what people are going to want to use it for, and it's hard to conceptualize without examples, at least for me. You may already be doing this, I haven't read the patch. My current example uses overlaps, but I will expand the documentation to provide more clarity. Also, there are certainly other things you could want to do that can't be handled by this approach. Perhaps you'd like to create a constraint that a given value can appear at most twice, or a two column index (A, B) such that for any A the smallest value of B is less than A. The first is a good example, and actually I think that could be an add-on to my patch without much difficulty. The second can't be enforced with an index in nearly the same way because deleting a tuple could violate the constraint. Also, it seems like it would be hard to express that kind of constraint. But I agree that, in principle, it is an index-enforceable constraint. These are certainly less common requirements than what you're talking about here, and I don't think it's important to try to support them - at least not at this point - but the word generalized doesn't give me a clue that I won't be able to do those things but I will be able to make an index that prevents my users from handing out duplicate IP blocks. As far as the name goes, the best I've got so far are index constraints and generalized index constraints. I'm happy to change the name if you have a reasonable suggestion. I don't think the word generalized implies that it can do absolutely anything possible. For the list discussion, I think it's appropriate to use the term generalized, because my patch generalizes index constraints. However, I agree that we shouldn't use that too much in the code/docs because someone might think of something more general later. 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 11:31:48AM -0700, Jeff Davis wrote: On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote: So it allows us to create constraints of the following form? For all A in the index, there exists no B in the index such that the given operator (which must be a binary operator returning boolean) holds of A and B. Yes. And it's slightly more complicated for multi-column constraints: For all tuples A in the index with attributes 1 to N, there exists no tuple B such that: A1 op1 B1 AND A2 op2 B2 AND ... AN op2 BN If all operators are =, and the index implements searching on equality, it's semantically equivalent to a unique index. Interesting :) I take it op1..opN (it's opN, not op2, right?) need to commute? These are certainly less common requirements than what you're talking about here, and I don't think it's important to try to support them - at least not at this point - but the word generalized doesn't give me a clue that I won't be able to do those things but I will be able to make an index that prevents my users from handing out duplicate IP blocks. As far as the name goes, the best I've got so far are index constraints and generalized index constraints. I'm happy to change the name if you have a reasonable suggestion. Here's a couple: * generalized-uniqueness constraints the hyphen disambiguates * operator-based constraints A little math-ier, but talks about the API rather than details of the server implementation. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dropping partitions and concurrent reads
S1, S2 are concurrent sessions: S1: create table test_par (v int); S1: create table test_ch1 (check (v 0 and v = 2)) inherits (test_par); S1: create table test_ch2 (check (v 2 and v = 4)) inherits (test_par); S1: begin; S1: drop table test_ch1 cascade; S2: select * from test_par where v = 3; S1: commit; in S2 I get: ERROR: could not open relation with OID 66962 This can happen without partitioning by dropping the table you're reading. That won't ever be better than an ERROR, because obviously you can't answer the query without the table. However it seems to be worse in the case of inheritance/partitioning because you are dropping a table that you don't need to answer the select query. Also, it seems like a common use-case for partitioning. Acquiring an ACCESS EXCLUSIVE lock on the parent is a workaround. Should we do that when dropping a child table? Or can plan invalidation correct this? 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 3:03 PM, David Fetter da...@fetter.org wrote: * operator-based constraints A little math-ier, but talks about the API rather than details of the server implementation. Or operator-exclusion constraints? Operator-based exclusion constraints? I'm feeling exclusive. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
Andrew Gierth and...@tao11.riddles.org.uk wrote: (To me, the fact that the spec's idea of 2009-01-31 + 1 month corresponds to a value that current_date will never be equal to is a far greater show-stopper.) You get to pick which way you want to normalize that to the calendar -- 31 days past the start of the next month, or pulled back to the last day of the next month which is not greater than 31. The latter is more common, but I've seen both practices in real world business applications. -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] WIP: generalized index constraints
On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote: Does it behave sanely for operators that are non-commutative, such as ''? (I'm not even very sure that I know what sanely would be in such a case.) One of the requirements is commutativity (I called it symmetry in the docs, for some reason, I will change that). I haven't explored in too much detail, but using x (or maybe its ?) would basically mean that you can only insert increasing values of x. There most likely be some serious problems there, for instance, if you HOT update an old tuple's y attribute, everything is fine; if you cold update it you would get an error. Not exactly intuitive, but if you have lots of other requirements about how things are updated, then I suppose it might be useful to someone. If you try it, my current patch won't stop you. Maybe I should detect the fact that the commutator of an operator is not the operator itself, and throw an ERROR? Probably would be a good idea. 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] WIP: generalized index constraints
On Tue, 2009-09-15 at 12:03 -0700, David Fetter wrote: Interesting :) I take it op1..opN (it's opN, not op2, right?) need to commute? Yeah, it's opN. And they should commute, but my current patch won't stop you. I think I should stop that though, it's pretty difficult to think of a good use-case for that and there is all kinds of danger. * generalized-uniqueness constraints the hyphen disambiguates I don't like using the word unique in the description, I think it only adds to the confusion. * operator-based constraints A little math-ier, but talks about the API rather than details of the server implementation. I like this much better. Maybe index operator constraints or operator index constraints? 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] WIP: generalized index constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote: Does it behave sanely for operators that are non-commutative, such as ''? (I'm not even very sure that I know what sanely would be in such a case.) If you try it, my current patch won't stop you. Maybe I should detect the fact that the commutator of an operator is not the operator itself, and throw an ERROR? Probably would be a good idea. +1. Otherwise people *will* try it, and then send us bug reports when it doesn't behave sanely. 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] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 12:22:46PM -0700, Jeff Davis wrote: On Tue, 2009-09-15 at 12:03 -0700, David Fetter wrote: * operator-based constraints A little math-ier, but talks about the API rather than details of the server implementation. I like this much better. Maybe index operator constraints or operator index constraints? The word, index goes to implementation details, which may change. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Timestamp to time_t
On Sep 15, 2009, at 11:01 AM, Andrew Gierth wrote: If you want to store both a timestamp and an associated timezone you can do it right now, using a composite type or two columns, with the advantage that you get semantics that you can rely on. How would a composite work in practice? Can you index it on the timestamp? Or would you have to use two columns for that? I could see a real advantage to a type that stored the TZ with which it was created, with the ability to fetch it back out. Internally the data could be stored just like it is with timestamptz, and by default, perhaps, it would display in $PGTZ, but if $PGTZ was set to a value like original or something, it should display the originals. Now *that* would be really useful IMHO. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #5053: domain constraints still leak
Since our shop seems to use domains more than most, I figured I should comment on this thread. Sam Mason s...@samason.me.uk wrote: On Tue, Sep 15, 2009 at 02:54:18PM +0100, Andrew Gierth wrote: and the wording from 6.12 implies that that check is still skipped in the case of NULLs (so that constraint would stop you inserting a null into a table column (I think), but not from casting a null value to the domain type). Explicitly ignoring NULL values in CAST expressions seems like a good feature as well. OK by me. Although it gives me the feeling that domains are more and more like a mis-designed feature. They have their place, for when you don't really need a new type, but you want to show that multiple columns contain data from the same set. My rule of thumb is this -- if it would make sense for two columns to be compared for equality, there's a very good chance they belong in the same domain; if not, they probably don't. Using them helps to document complex databases and helps with portability, quite aside from the issue of constraints. Hum, given that it's just sugar for more general constraints I'm not sure if it's the not null constraints that are broken or just the current interpretation of them. They would do the right thing if they were only checked in a limited number of places that the user was aware of, which the spec seems to imply is when the user explicitly asks for a CAST to be performed or when writing into the table. If that's what the spec says, then +1 from me. The change won't cause problems 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] dropping partitions and concurrent reads
Jeff Davis pg...@j-davis.com writes: S1, S2 are concurrent sessions: S1: create table test_par (v int); S1: create table test_ch1 (check (v 0 and v = 2)) inherits (test_par); S1: create table test_ch2 (check (v 2 and v = 4)) inherits (test_par); S1: begin; S1: drop table test_ch1 cascade; S2: select * from test_par where v = 3; S1: commit; in S2 I get: ERROR: could not open relation with OID 66962 Oh? Are you using 8.4+? 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] dropping partitions and concurrent reads
On Tue, 2009-09-15 at 16:58 -0400, Tom Lane wrote: Oh? Are you using 8.4+? Oops, connecting to the wrong port. 8.5-dev works fine. 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
[HACKERS] I need a Postgres Admin $130K + 20K in NYC Any Ideas?
Best Regards, Ed Koch Principal Addison Search 212-378-1634 1350 Broadway NY,NY suite 810, 10018 This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. Should you wish to be excluded from any future mailings, please send an email to unsubscr...@addisonsearch.com with the word unsubscribe in the subject. If verification is required please request a hard-copy version. Addison Search, 222 S. Riverside PLZ, STE 1710, Chicago, IL, www.addisonsearch.com
Re: [HACKERS] I need a Postgres Admin $130K + 20K in NYC Any Ideas?
Perhaps you should post to the correct mailing list, which is pgsql-j...@postgresql.org. Posting a real description of the position - including where it's located, what the responsibilities are, and so on, would be a good idea too. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, 2009-09-15 at 12:49 -0700, David Fetter wrote: I like this much better. Maybe index operator constraints or operator index constraints? The word, index goes to implementation details, which may change. Ok, let's vote on a name then: operator constraints operator exclusion constraints operator conflict constraints conflict operator constraints operator index constraints index constraints generalized index constraints something else? Right now, I like conflict operator constraints for the long name (e.g. feature title, long description in docs), and operator constraints for short (e.g. in the code and some places in the docs). 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] WIP: generalized index constraints
On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote: operator constraints operator exclusion constraints operator conflict constraints conflict operator constraints operator index constraints index constraints generalized index constraints something else? Just to add a couple more permutations of Robert Haas's suggestions: exclusion operator constraints exclusive operator constraints I also like those. I think that using the word operator first makes it sound like the operator is the thing being excluded, and adding -based makes it more clear but it is too verbose. 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] WIP: generalized index constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote: operator constraints operator exclusion constraints operator conflict constraints conflict operator constraints operator index constraints index constraints generalized index constraints something else? Just to add a couple more permutations of Robert Haas's suggestions: exclusion operator constraints exclusive operator constraints To my ear, operator exclusion constraints or exclusive operator constraints seem reasonable; the other permutations of that phrase simply aren't good English. I'm not tremendously happy with any of them 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] Hot Standby 0.2.1
On Tue, Sep 15, 2009 at 10:41:59PM +0100, Simon Riggs wrote: OK, here is the latest version of the Hot Standby patchset. This is about version 30+ by now, but we should regard this as 0.2.1 Patch against CVS HEAD (now): clean apply, compile, no known bugs. Kudos Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
On Tue, Sep 15, 2009 at 05:52:35PM -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote: operator constraints operator exclusion constraints operator conflict constraints conflict operator constraints operator index constraints index constraints generalized index constraints something else? Just to add a couple more permutations of Robert Haas's suggestions: exclusion operator constraints exclusive operator constraints To my ear, operator exclusion constraints or exclusive operator constraints seem reasonable; the other permutations of that phrase simply aren't good English. I was having a hard time coming up with a name that was adequately short-and-sweet, and still conveyed the idea of both operator and index, which seems important so as to designate between these and the constraints we've had all along. Perhaps indexed operator constraints? -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Timestamp to time_t
On Wed, Sep 16, 2009 at 2:23 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Scott Mohekey scott.mohe...@telogis.com wrote: I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as TIMESTAMP at GMT. We then convert it to a users local timezone within application code. TIMESTAMP WITHOUT TIME ZONE is stored raw and is not considered to be associated to a time zone until you do so. It will default to assigning the time zone set on your server, which is normally your local time zone. Unless that's GMT, you will need to be very careful to always localize the timestamp to GMT before doing anything with it. -Kevin This is exactly what we do. All of our servers are set to GMT time, all times in the database are stored in GMT time, and are converted to a users local time within the application. Scott Mohekey Systems/Application Specialist – OnTrack – Telogis, Inc. www.telogis.com www.telogis.co.nz +1 949 625-4115 ext. 207 (USA) +64 3339 2825 x207 (NZ) Leading Global Platform for Location Based Services -- This e-mail, and any attachments, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. It is the property of Telogis. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying of this e-mail, any attachments thereto, and use of the information contained, is strictly prohibited. If you have received this e-mail in error, please notify the sender and permanently delete the original and any copy there of.
Re: [HACKERS] updated join removal patch
Robert Haas robertmh...@gmail.com writes: Here we go again. Following Tom's advice to not insert crocks in add_path(), I've instead introduced a noopjoin path type which ignores its inner side. This could possibly be simplified to just a noop path that doesn't even include an outer side, but the way I've done it here doesn't really cost anything and might allow debug pprints to print more useful information. If, in the future, we have some other use for a noop path, we might want to reconsider, though. I took a closer look at this version of the patch. Some comments: * I'm not really happy with the NoopJoinPath representation. In the first place, it isn't a join and should not be carrying a useless inner path. The other nasty thing about it is that it violates the rule that path.pathtype is supposed to be the nodetag for the executable plan node that corresponds to the path. My original thought about this was to just use the left input's paths as-is. I am not totally sure that that works --- up to now, all the members of a reloptinfo's path list have had that same reloptinfo as their parent --- but it seems less ugly than this. A possible compromise is to use T_SubqueryScan as the pathtype, with the idea that we're pretending a SubqueryScan is to be inserted and then immediately optimized away. But I don't want the inner path in there in any case. * Speaking of the left input, it's not good enough to copy up only the cheapest startup and cheapest total paths. If there are any other paths there at all, they're probably there because they have interesting sort orders. With the patch as-is, it'd be possible for join removal to be a net loss because it forces an extra sort at an upper level. Now it's possible that some of those sort orderings are only interesting to use for mergejoining in the same join that we're removing; in which case they could safely be discarded. But I'm not sure that's worth checking for, and I am sure that throwing everything away shouldn't be the base behavior. * I'm quite unimpressed with the refactoring you did to make a single function deal with merge clauses, hash clauses, *and* join removal clauses. I think that makes it close to unmaintainable and is buying little if anything speedwise. We can afford another iteration over the join clauses, especially if there's a GUC to let people turn it off. (BTW, do we really need that GUC, or is it only there for testing the patch? I'm leaning towards not having it.) * I'm not sure about this, because surely you would have tested it, but isn't it looking at the wrong side of the join clauses? I thought the idea is to prove the nullable (inner) side of the join unique. * Not entirely sure where to put the code that does the hard work (relation_is_distinct_for). I see you put it in pathnode.c beside query_is_distinct_for, but the *only* reason the latter is where it is is that it has only one caller, namely create_unique_path, which is (and belongs) in that module. Opening up pathnode's API to include a function unrelated to its purpose doesn't seem right. So I'm inclined to think they should both go someplace else, just not sure where. There doesn't seem to be any planner file whose charter is to export this type of knowledge; maybe we need to add one. * It shouldn't be that hard to support expression indexes. I think the only reason you can't do it right now is that you chose an intermediate data structure that presumes simple Vars ... but if you refactor to have bespoke code examining the indexes and joinclauses together, I think it would work all right. * I wonder whether all the relevant clauses are really to be found as join clauses. Consider tab1 left join tab2 on (tab1.a = tab2.x and tab2.y = 42) where tab2 has a unique index on (x,y). This would at least suggest examining the inner rel's baserestrictclauses along with the current join's clauses. * I wouldn't really bother with cost_noopjoin. It does not, and never will, encapsulate any interesting knowledge. In other places where we have similar issues (eg no-op UniquePaths in create_unique_path), we just copy up the child path's costs without any folderol. Do you want to have another go at this, or shall I? 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] query cancel issues in contrib/dblink
Joe, Itagaki, Can you provide an update on this patch? Joe, you were going to review and possibly commit it. Itagaki, did you have a new version? Are there any outstanding issues? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] pgbench: new feature allowing to launch shell commands
Michael, I just wanted to follow-up on your pgbench patch. The latest version that I see is from August 13th. Is that the correct patch to be reviewing? Do you have any other updates on it? Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Lock Wait Statistics (next commitfest)
Mark, Your last email on this patch, from August 9th, indicates that you've still got TODO: redo pg_stat_lock_waits Has you updated this patch since then? Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] PGCluster-II Progress
Hi, 2009/9/16 Marc G. Fournier scra...@hub.org: Odd, I talked to him a couple of weeks ago and he was working on a new release in preparation for some upcoming talks he was doing ... was working on bringing it up to support 8.3.x ... Yes. He will make a presentation about PGCluster at PGCon 2009 Japan. http://www.postgresql.jp/events/pgcon09j/e/program_2#10 Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] updated join removal patch
On Tue, Sep 15, 2009 at 9:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Here we go again. Following Tom's advice to not insert crocks in add_path(), I've instead introduced a noopjoin path type which ignores its inner side. This could possibly be simplified to just a noop path that doesn't even include an outer side, but the way I've done it here doesn't really cost anything and might allow debug pprints to print more useful information. If, in the future, we have some other use for a noop path, we might want to reconsider, though. I took a closer look at this version of the patch. Some comments: * I'm not really happy with the NoopJoinPath representation. In the first place, it isn't a join and should not be carrying a useless inner path. The other nasty thing about it is that it violates the rule that path.pathtype is supposed to be the nodetag for the executable plan node that corresponds to the path. My original thought about this was to just use the left input's paths as-is. I am not totally sure that that works --- up to now, all the members of a reloptinfo's path list have had that same reloptinfo as their parent --- but it seems less ugly than this. A possible compromise is to use T_SubqueryScan as the pathtype, with the idea that we're pretending a SubqueryScan is to be inserted and then immediately optimized away. But I don't want the inner path in there in any case. I don't have strong feelings about it. I thought about making just a Noop path type, but I couldn't see any clear reason to prefer it one way or the other. The only reason we need a path type at all is because you didn't like the crocks I inserted into add_path() to avoid pfree()-ing paths that might still be pointed to from elsewhere. But those crocks were VASTLY simpler than this, which feels completely Rube Goldberg-esque to me. If it were up to me, we'd be either reinserting those crocks, or looking for some less crock-y variant of them. * Speaking of the left input, it's not good enough to copy up only the cheapest startup and cheapest total paths. If there are any other paths there at all, they're probably there because they have interesting sort orders. With the patch as-is, it'd be possible for join removal to be a net loss because it forces an extra sort at an upper level. Now it's possible that some of those sort orderings are only interesting to use for mergejoining in the same join that we're removing; in which case they could safely be discarded. But I'm not sure that's worth checking for, and I am sure that throwing everything away shouldn't be the base behavior. Good point. * I'm quite unimpressed with the refactoring you did to make a single function deal with merge clauses, hash clauses, *and* join removal clauses. I think that makes it close to unmaintainable and is buying little if anything speedwise. We can afford another iteration over the join clauses, especially if there's a GUC to let people turn it off. (BTW, do we really need that GUC, or is it only there for testing the patch? I'm leaning towards not having it.) You're the committer; I'm not. But I completely disagree. There isn't any reason at all to duplicate this logic in two separate places, let alone three. I'd actually be in favor of merging the existing two cases even if we weren't adding join removal. The existing code does the identical tests in hash_inner_and_outer() in a slightly different order than select_mergejoin_clauses() for no reason at all. As for a GUC, I think it would be useful to have for debugging, or in case of bugs. It's really another join strategy, and we have enable_* flags for all the others. But if you don't want it for some reason, I'm not in a position to twist your arm. * I'm not sure about this, because surely you would have tested it, but isn't it looking at the wrong side of the join clauses? I thought the idea is to prove the nullable (inner) side of the join unique. Grr. I think it's more broken than that. Wow, this is really embarassing. * Not entirely sure where to put the code that does the hard work (relation_is_distinct_for). I see you put it in pathnode.c beside query_is_distinct_for, but the *only* reason the latter is where it is is that it has only one caller, namely create_unique_path, which is (and belongs) in that module. Opening up pathnode's API to include a function unrelated to its purpose doesn't seem right. So I'm inclined to think they should both go someplace else, just not sure where. There doesn't seem to be any planner file whose charter is to export this type of knowledge; maybe we need to add one. Yeah, it seemed a little weird to me. For a while I was reusing some of the support functions that query_is_distinct_for() calls, but the final version doesn't. I wonder if it should just be moved to joinpath.c; it seems to fit in better with what is going on there.
Re: [HACKERS] updated join removal patch
On Tue, Sep 15, 2009 at 10:10 PM, Robert Haas robertmh...@gmail.com wrote: * I'm not sure about this, because surely you would have tested it, but isn't it looking at the wrong side of the join clauses? I thought the idea is to prove the nullable (inner) side of the join unique. Grr. I think it's more broken than that. Wow, this is really embarassing. Well, you're definitely right that it's looking at the wrong side of the join clauses. Still trying to figure out if there is another bug, too. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
