Re: [HACKERS] Latch for the WAL writer - further reducing idle wake-ups.
On 03.05.2012 03:41, Robert Haas wrote: On Wed, May 2, 2012 at 7:21 PM, Tom Lanet...@sss.pgh.pa.us wrote: Adding any contention at all to XLogInsert doesn't seem like a smart idea, even if you failed to measure any problem in the specific tests you made. I wonder whether we could not improve matters by adding an additional bool wal_writer_needs_wakening in the state that's considered to be protected by WALInsertLock. I am skeptical about this, although it could be right. It could also be better the way Peter did it; a fetch of an uncontended cache line is pretty cheap. I'm very wary of adding any extra shared memory accesses to XLogInsert. I spent a lot of time trying to eliminate them in my XLogInsert scaling patch. It might be ok if the flag is usually not modified, and we don't add any extra barrier instructions in there, but it would be better to avoid it. One simple idea would be to only try to set the latch every 100 XLogInsert calls in the backend. That would cut whatever contention it might cause by a factor of 100, making it negligible. Another approach - which I think might be better still - is to not bother kicking the WAL writer and let it wake up when it wakes up. Maybe have it hibernate for 3 seconds instead of 10, or something like that. It seems unlikely to cause any real problem if WAL writer takes a couple seconds to get with the program after a long period of inactivity; note that an async commit will kick it anyway, and a sync commit will probably half to flush WAL whether the WAL writer wakes up or not. Yeah, that'd be even simpler. -- 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] Advisory locks seem rather broken
On Thu, May 3, 2012 at 1:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: According to http://archives.postgresql.org/pgsql-general/2012-04/msg00374.php advisory locks now cause problems for prepared transactions, which ought to ignore them. It appears to me that this got broken by commit 62c7bd31c8878dd45c9b9b2429ab7a12103f3590, which marked the userlock lock method as transactional, which seems just about 100% misguided to me. At the very least this would require reconsidering every single place that tests lock transactionality, and that evidently did not happen. If this patch weren't already in a released branch I would be arguing for reverting it. As is, I think we're going to have to clean it up. I don't have time to look at it in detail right now, though. There was an attempt to add a transactional advisory lock call type, but my understanding of the plan for that was not to change the existing advisory lock mechanism. It seems that was bungled, so some change is required, but maybe not total revoke. If the change was actually intended that way then I object to it and I also want it changed back. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/Python result set slicing broken in Python 3
On 02/05/12 20:18, Peter Eisentraut wrote: This doesn't work anymore with Python 3: rv = plpy.execute(...) do_something(rv[0:1]) Apparently, they changed the C API for doing slicing, or rather made one of the two APIs for it silently do nothing. Details are difficult to find, but this email message seems to contain something: http://mail.python.org/pipermail/python-3000/2007-August/009851.html. I'll try to sort this out sometime, but if someone wants to take a shot at it, go ahead. Sounds ugly. I'll take a look. Cheers, Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Thu, May 3, 2012 at 1:57 AM, Josh Berkus j...@agliodbs.com wrote: Michael, What is the use case for temporary tables on a hot standby server? Perhaps this is a noobie question, but it seems to me that a hot standby server's use by* applications* or *users* should be limited to transactions that don't alter the database in any form. A very common use for asynchronous replicas is to offload long-running reporting jobs onto the replica so that they don't bog down the master. However, long-running reporting jobs often require temporary tables, especially if they use some 3rd-party vendor's reporting tool. For example, the average Microstrategy report involves between 1 and 12 temporary tables. Many tools and applications choose to use temporary tables. Often this isn't necessary at all, for example in MicroStrategy it is possible to ask it to use derived tables instead and thus avoid using temp tables, so that can still work against Hot Standby. Derived tables means rewriting the query from CREATE TEMP TABLE s1 AS SELECT1; SELECT ... FROM s1 WHERE ... into SELECT ... FROM (SELECT1) AS s1 WHERE Many apps are easily rewritten in this way and so the lack of temp tables isn't a total blocker in the way some people think. If we had Global Temp Tables, users would still need to rewrite their code, just in a different way, like this... (on master) CREATE GLOBAL TEMP TABLE s1 (); (on standby) INSERT INTO s1 SELECT1; SELECT ... FROM s1 WHERE ... which seems to me to be actually harder than just rewriting as derived table and isn't an option on Microstrategy etc, hence my observation that GTTs don't help HS much. What I would like to see, one day, is for temp tables to work without any changes. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Modeling consumed shmem sizes, and some thorns
On Wed, May 2, 2012 at 9:38 PM, Daniel Farina dan...@heroku.com wrote: Besides accuracy, there is a thornier problem here that has to do with hot standby (although the use case is replication more generally) when one has heterogeneously sized database resources. As-is, it is required that locking-related structures -- max_connections, max_prepared_xacts, and max_locks_per_xact (but not predicate locks, is that an oversight?) must be a larger number on a standby than on a primary. = not so you can use the same values on both sides Predicate locks aren't set in recovery so the value isn't checked as a required parameter value. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Modeling consumed shmem sizes, and some thorns
On Thu, May 3, 2012 at 2:23 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, May 2, 2012 at 9:38 PM, Daniel Farina dan...@heroku.com wrote: Besides accuracy, there is a thornier problem here that has to do with hot standby (although the use case is replication more generally) when one has heterogeneously sized database resources. As-is, it is required that locking-related structures -- max_connections, max_prepared_xacts, and max_locks_per_xact (but not predicate locks, is that an oversight?) must be a larger number on a standby than on a primary. = not so you can use the same values on both sides Predicate locks aren't set in recovery so the value isn't checked as a required parameter value. I had a feeling that might be the case, since my understanding is that they are not actually locks -- rather, markers. In any case, it would be strange to change the *number* of locks per transaction in such heterogeneous environments because then some fairly modestly sized transactions will simply not work depending on one size of system one selects. The more problematic issue is that small systems will be coerced into having a very high number for max_connections and the memory usage required by that, if one also provides a large system supporting a high connection limit and moves things around via WAL shipping. I'm not sure what there is to be done about this other than make the absolutely required locking structures smaller -- I wonder if not unlike the out-of-line storage for PGPROC patch this might also make some things faster. All in all, without having gone in to figure out *why* the size consumption is as it is I'm a little flabbergasted as to why the locking structures are just so large. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Latch for the WAL writer - further reducing idle wake-ups.
On Thu, May 3, 2012 at 2:41 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, May 2, 2012 at 7:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: It is getting a bit late to be considering such changes for 9.2, but I'm willing to review and commit this if there's not anybody who feels strongly that it's too late. Personally I think it's in the nature of cleanup and so fair game as long as we haven't formally started beta. However I will confess to some bias about wanting to get the server's idle wake-up rate down, because Fedora people have been bugging me about that for a long time now. So I'm probably not the best person to objectively evaluate whether we should hold this for 9.3. Comments? Well, I feel that one of the weaknesses of our CommitFest process is that changes like this (which are really pretty small) end up having the same deadline as patches that are large (command triggers, checksums, etc.); in fact, they sometimes end up having an earlier deadline, because the people doing the big stuff end up continuing to hack on it for another couple months while the door is shut to smaller improvements. So I'm not going to object if you feel like slipping this one in. I looked it over myself and I think it's broadly reasonable, although I'm not too sure about the particular criteria chosen for sending the WAL writer to sleep and waking it up again. And like you I'd like to see some more improvement in this area. I agree that it's ok to slip it in given that it's finishing off a patch from earlier. I think it's reasonable to hold it to a little bit higher review stadards since it's that late in the cycle though, such as two people reviewing it before it goes in (or 1 reviewer + 1 committer - and of course, unless it's a truly trivial patch). Which it seems you both are doing now, so that makes it ok ;) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
On Wed, 2012-05-02 at 14:32 -0500, Merlin Moncure wrote: On Wed, May 2, 2012 at 12:06 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2012-05-02 at 13:40 +0200, Hannu Krosing wrote: How hard would it be to add support for LIKE syntax, similar to table def in field list declaration for generic record functions What I'dd like to be able to do is to have a generic json_to_record function CREATE OR REPLACE RECORD json_to_record(json) RETURNS RECORD AS $$ ... $$ LANGUAGE ... ; and then be able to call it like this insert into test2 select * from json_to_record(jrec json) as (like test2); That would be very useful, and shouldn't be too hard to implement. (I had to look about three times to understand what this was supposed to achieve, but I think the syntax is the right one after all.) Although I like the functionality, is this better than the trick used by hstore/populate_record? That approach doesn't require syntax changes and allows you to execute the function without 'FROM'. You mean the one using a null::returntype for as first argument for defining a return type of a function: Convert an hstore to a predefined record type: CREATE TABLE test (col1 integer, col2 text, col3 text); SELECT * FROM populate_record(null::test, 'col1=456, col2=zzz'); col1 | col2 | col3 --+--+-- 456 | zzz | (1 row) This works the same indeed, just seems to be a hack, though a cool one :) hannu=# insert into test hannu-# SELECT * FROM populate_record(null::test, 'id=456, data=zzz'); INSERT 0 1 putting the same functionality in LIKE at syntax level kind of feels more orthogonal to table definition:) select * from to_record(null::mytable, datasource); vs select * from to_record(datasource) as (like mytable); OTOH, we do not support LIKE in type definition either. If we were to overhaul template-based structure definition, the perhaps the following syntax woul be better: create type mytape as mytable; -- exact copy create type mytape as (like mytable, comment text); -- copy + extra field and same for giving type to generic record in function calls. If it does not mess up the syntax, it would also be good to add simple casts in record--table case select * from to_record(datasource)::mytable; -- --- Hannu Krosing PostgreSQL Unlimited Scalability and Performance Consultant 2ndQuadrant Nordic PG Admin Book: http://www.2ndQuadrant.com/books/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) hannu=# insert into test hannu-# SELECT * FROM populate_record(null::test, 'id=456, data=zzz'); INSERT 0 1 few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] unexpected EOF messages
I had a request from a customer asking if we could make a switch to specifically disable the unexpected EOF message that fills lots of peoples logs. Along the same way that we have a flag to turn off the nonstandard use of string escapes message that is another culprit (that's actually a much *worse* problem than just the unexpected EOF). The unexpected EOF message *does* indicate the client is doing something stupid, but it's not like it's an *actual problem* in pretty much every deployment out there... Would we consider adding such a switch (it should be easy enough to do), or do we want to push this off to the mythical let's improve the logging subsystem project that might eventually materialize if we're lucky? Meaning - would people object to such a switch? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 1:26 PM, Magnus Hagander mag...@hagander.net wrote: I had a request from a customer asking if we could make a switch to specifically disable the unexpected EOF message that fills lots of peoples logs. Along the same way that we have a flag to turn off the nonstandard use of string escapes message that is another culprit (that's actually a much *worse* problem than just the unexpected EOF). The unexpected EOF message *does* indicate the client is doing something stupid, but it's not like it's an *actual problem* in pretty much every deployment out there... Would we consider adding such a switch (it should be easy enough to do), or do we want to push this off to the mythical let's improve the logging subsystem project that might eventually materialize if we're lucky? Meaning - would people object to such a switch? Yes, if the new parameter allows a generic filter on multiple user-specified message types. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER DATABASE and datallowconn
Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, May 3, 2012 at 1:26 PM, Magnus Hagander mag...@hagander.net wrote: I had a request from a customer asking if we could make a switch to specifically disable the unexpected EOF message that fills lots of peoples logs. Along the same way that we have a flag to turn off the nonstandard use of string escapes message that is another culprit (that's actually a much *worse* problem than just the unexpected EOF). The unexpected EOF message *does* indicate the client is doing something stupid, but it's not like it's an *actual problem* in pretty much every deployment out there... Would we consider adding such a switch (it should be easy enough to do), or do we want to push this off to the mythical let's improve the logging subsystem project that might eventually materialize if we're lucky? Meaning - would people object to such a switch? Yes, if the new parameter allows a generic filter on multiple user-specified message types. Uh, just to be clear, you object *if* it has the generic filter? Also, AFAIK we don't *have* a message type at this point (one of the things said mythical project wanted to look at), so the only thing we could really filter on would be the whole text of the message, no? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote: Would we consider adding such a switch (it should be easy enough to do), or do we want to push this off to the mythical let's improve the logging subsystem project that might eventually materialize if we're lucky? Meaning - would people object to such a switch? Yes, if the new parameter allows a generic filter on multiple user-specified message types. Are you answering the Would we consider or the would people object?
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 2:34 PM, Vik Reykja vikrey...@gmail.com wrote: On Thu, May 3, 2012 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote: Would we consider adding such a switch (it should be easy enough to do), or do we want to push this off to the mythical let's improve the logging subsystem project that might eventually materialize if we're lucky? Meaning - would people object to such a switch? Yes, if the new parameter allows a generic filter on multiple user-specified message types. Are you answering the Would we consider or the would people object? Oh, nice catch - I guess my phrasing of those two questions was really stupid :) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Magnus Hagander wrote: Also, AFAIK we don't *have* a message type at this point (one of the things said mythical project wanted to look at), so the only thing we could really filter on would be the whole text of the message, no? We have SQLSTATE, but this seems to be one of those situations where we've been sloppy about using the right value. We seem to be using '08P01' (protocol_violation), which is also used for finding the wrong bytes on a working connection. It seems to me a broken connection is exactly the case where you would expect to see '08006' (connection_failure). FWIW, there are also specific exceptions for rejecting a connection attempt, and for attempting to send something when no connection exists. We don't need to invent new mechanisms for categorizing messages; we just need to start consistently using the one we have correctly. -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] ALTER DATABASE and datallowconn
On Thu, May 3, 2012 at 1:31 PM, Magnus Hagander mag...@hagander.net wrote: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? I think it can be removed, or rather deprecated. datconnlimit can be set to 0 If we need to special case template0 then we should just do that. No need to add a column to do it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Torn page hazard in ginRedoUpdateMetapage()
On Thu, May 3, 2012 at 12:16 AM, Tom Lane t...@sss.pgh.pa.us wrote: Having said all that, I wasn't really arguing that this was a guaranteed safe thing for us to rely on; just pointing out that it's quite likely that the issue hasn't been seen in the field because of this type of consideration. Well, we do rely, in numerous places, on writes 512 bytes not getting torn. pd_prune_xid, index tuple kills, heap tuple hint bits, relmapper files, etc. We generally assume, for example, that a 4-byte write which is 4-byte aligned does not need to be WAL-logged, which would be necessary if we thought that the write might be torn. Are you planning to commit Noah's patch? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER DATABASE and datallowconn
On Thu, May 3, 2012 at 2:48 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, May 3, 2012 at 1:31 PM, Magnus Hagander mag...@hagander.net wrote: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? I think it can be removed, or rather deprecated. datconnlimit can be set to 0 superusers bypass datconnlimit, but not datallowconn, don't they? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] outdated comment in heapam.c
Hi, It seems that when Heikki added the multi_insert code the following comment in htup.h wasn't updated: /* * We ran out of opcodes, so heapam.c now has a second RmgrId. These opcodes * are associated with RM_HEAP2_ID, but are not logically different from * the ones above associated with RM_HEAP_ID. We apply XLOG_HEAP_OPMASK, * although currently XLOG_HEAP_INIT_PAGE is not used for any of these. */ #define XLOG_HEAP2_FREEZE 0x00 #define XLOG_HEAP2_CLEAN0x10 /* 0x20 is free, was XLOG_HEAP2_CLEAN_MOVE */ #define XLOG_HEAP2_CLEANUP_INFO 0x30 #define XLOG_HEAP2_VISIBLE 0x40 #define XLOG_HEAP2_MULTI_INSERT 0x50 I suggest simply to remove the although currently XLOG_HEAP_INIT_PAGE is not used for any of these part. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
On Thu, May 3, 2012 at 4:11 AM, Simon Riggs si...@2ndquadrant.com wrote: which seems to me to be actually harder than just rewriting as derived table and isn't an option on Microstrategy etc, hence my observation that GTTs don't help HS much. What I would like to see, one day, is for temp tables to work without any changes. yes. except (global) temp tables can: *) be updated with data not derived from permanent tables (think: session management, etc) *) outlive a single transaction *) be indexed independently of the sourcing tables *) be referred to from multiple queries (you can simulate this with CTE, but that approach obviously has limits) of course, temp tables that would 'just work' would be wonderful. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) Yeah -- the syntax isn't great, but IMO it's more generally usable than what you're proposing because it's a scalar returning function not a table expression. Another point is that the proposed 'like' syntax (which I still think is great, just maybe not for conversions from json) seems wedded to record types. The hstore trick should be able to take a foo[], set it all up and return it. How would that work with like? few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) that would be great IMO. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
2012/5/3 Merlin Moncure mmonc...@gmail.com: On Thu, May 3, 2012 at 7:13 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) Yeah -- the syntax isn't great, but IMO it's more generally usable than what you're proposing because it's a scalar returning function not a table expression. Another point is that the proposed 'like' syntax (which I still think is great, just maybe not for conversions from json) seems wedded to record types. The hstore trick should be able to take a foo[], set it all up and return it. How would that work with like? few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) that would be great IMO. I'll try propose it again - implementation should not be hard Regards Pavel merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
On 05/03/2012 09:43 AM, Pavel Stehule wrote: 2012/5/3 Merlin Moncuremmonc...@gmail.com: On Thu, May 3, 2012 at 7:13 AM, Pavel Stehulepavel.steh...@gmail.com wrote: Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) Yeah -- the syntax isn't great, but IMO it's more generally usable than what you're proposing because it's a scalar returning function not a table expression. Another point is that the proposed 'like' syntax (which I still think is great, just maybe not for conversions from json) seems wedded to record types. The hstore trick should be able to take a foo[], set it all up and return it. How would that work with like? few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) that would be great IMO. I'll try propose it again - implementation should not be hard You guys seem to be taking the original proposal off into the weeds. I have often wanted to be able to use LIKE in type expressions, and I'd like to see exactly that implemented. 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] ALTER DATABASE and datallowconn
Magnus Hagander mag...@hagander.net writes: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? It was never intended to be a user-accessible switch, just something to protect template0. I don't agree with Simon's proposal to hard-wire protection for template0 instead; that's ugly, and sometimes you do need to be able to turn it off. But that's something that should be done only with adult supervision, so having a nice friendly ALTER DATABASE command for it seems exactly the wrong thing. 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] unexpected EOF messages
Simon Riggs si...@2ndquadrant.com writes: On Thu, May 3, 2012 at 1:26 PM, Magnus Hagander mag...@hagander.net wrote: I had a request from a customer asking if we could make a switch to specifically disable the unexpected EOF message that fills lots of peoples logs. Yes, if the new parameter allows a generic filter on multiple user-specified message types. I agree with Simon --- a disable for that specific message seems like a kluge, and an ugly one at that. (The right solution for this customer is to fix their broken application.) But a generic filter capability might be useful enough to justify its keep. 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
On Thu, May 3, 2012 at 9:01 AM, Andrew Dunstan and...@dunslane.net wrote: On 05/03/2012 09:43 AM, Pavel Stehule wrote: 2012/5/3 Merlin Moncuremmonc...@gmail.com: On Thu, May 3, 2012 at 7:13 AM, Pavel Stehulepavel.steh...@gmail.com wrote: Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) Yeah -- the syntax isn't great, but IMO it's more generally usable than what you're proposing because it's a scalar returning function not a table expression. Another point is that the proposed 'like' syntax (which I still think is great, just maybe not for conversions from json) seems wedded to record types. The hstore trick should be able to take a foo[], set it all up and return it. How would that work with like? few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) that would be great IMO. I'll try propose it again - implementation should not be hard You guys seem to be taking the original proposal off into the weeds. I have often wanted to be able to use LIKE in type expressions, and I'd like to see exactly that implemented. would it work for array types? can it called without using FROM? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER DATABASE and datallowconn
On Thu, May 3, 2012 at 4:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? It was never intended to be a user-accessible switch, just something to protect template0. It can be rather useful for others as well, though - since it works as a defense against superusers doing the wrong thing.. I don't agree with Simon's proposal to hard-wire protection for template0 instead; that's ugly, and sometimes you do need to be able to turn it off. But that's something that should be done only with adult supervision, so having a nice friendly ALTER DATABASE command for it seems exactly the wrong thing. Yeah, I agree that from the perspective of template0, it definitely looks that way. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER DATABASE and datallowconn
Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 4:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? It was never intended to be a user-accessible switch, just something to protect template0. It can be rather useful for others as well, though - since it works as a defense against superusers doing the wrong thing.. I'm having a hard time seeing the use-case for a user-created database that nobody at all can connect to. Even if there is some marginal use for that, you could achieve the result with a special entry in pg_hba.conf to reject all connection attempts for that DB. 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] ALTER DATABASE and datallowconn
On Thu, May 3, 2012 at 4:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 4:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Is there a particular reason we don't have an ALTER DATABASE switch that controls the datallowconn, or is it just something missed out? It was never intended to be a user-accessible switch, just something to protect template0. It can be rather useful for others as well, though - since it works as a defense against superusers doing the wrong thing.. I'm having a hard time seeing the use-case for a user-created database that nobody at all can connect to. Even if there is some marginal use template databases. for that, you could achieve the result with a special entry in pg_hba.conf to reject all connection attempts for that DB. Yeah, that would work. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
Andrew Dunstan and...@dunslane.net writes: You guys seem to be taking the original proposal off into the weeds. I have often wanted to be able to use LIKE in type expressions, and I'd like to see exactly that implemented. This notion of anytypename is utterly unworkable anyway; there's no way for the parser to know soon enough that a given argument position needs to be read as a type name rather than a normal expression. You could conceivably make it work with the argument being a regtype literal (ie, quoted); but that seems at least as klugy as what hstore is doing. 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
2012/5/3 Tom Lane t...@sss.pgh.pa.us: Andrew Dunstan and...@dunslane.net writes: You guys seem to be taking the original proposal off into the weeds. I have often wanted to be able to use LIKE in type expressions, and I'd like to see exactly that implemented. This notion of anytypename is utterly unworkable anyway; there's no way for the parser to know soon enough that a given argument position needs to be read as a type name rather than a normal expression. You could conceivably make it work with the argument being a regtype literal (ie, quoted); but that seems at least as klugy as what hstore is doing. type identifier is same identifier like other - but I have no prototype now, so I don't know if there is some trap I remember so I though about using CAST keyword too some like SELECT CAST( populate_record(hstore_value) AS type) Regards Pavel 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
On 05/03/2012 10:18 AM, Merlin Moncure wrote: On Thu, May 3, 2012 at 9:01 AM, Andrew Dunstanand...@dunslane.net wrote: On 05/03/2012 09:43 AM, Pavel Stehule wrote: 2012/5/3 Merlin Moncuremmonc...@gmail.com: On Thu, May 3, 2012 at 7:13 AM, Pavel Stehulepavel.steh...@gmail.com wrote: Hello (1 row) This works the same indeed, just seems to be a hack, though a cool one :) Yeah -- the syntax isn't great, but IMO it's more generally usable than what you're proposing because it's a scalar returning function not a table expression. Another point is that the proposed 'like' syntax (which I still think is great, just maybe not for conversions from json) seems wedded to record types. The hstore trick should be able to take a foo[], set it all up and return it. How would that work with like? few years back I proposed anytypename type with this feature, you can has some clean and more readable call SELECT * FROM populate_record(test, ...) that would be great IMO. I'll try propose it again - implementation should not be hard You guys seem to be taking the original proposal off into the weeds. I have often wanted to be able to use LIKE in type expressions, and I'd like to see exactly that implemented. would it work for array types? can it called without using FROM? Why would you always need FROM? I want to be able to do things like: create type new_type as (like old_type, extra text); i.e., anywhere we are specifying a type (e.g. as above or for a function returnign a setof record), we should be able to import an existing one rather than having to replicate it. 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] unexpected EOF messages
On Thu, May 3, 2012 at 2:46 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Magnus Hagander wrote: Also, AFAIK we don't *have* a message type at this point (one of the things said mythical project wanted to look at), so the only thing we could really filter on would be the whole text of the message, no? We have SQLSTATE, but this seems to be one of those situations where we've been sloppy about using the right value. We seem to be using '08P01' (protocol_violation), which is also used for finding the wrong bytes on a working connection. It seems to me a broken connection is exactly the case where you would expect to see '08006' (connection_failure). FWIW, there are also specific exceptions for rejecting a connection attempt, and for attempting to send something when no connection exists. We don't need to invent new mechanisms for categorizing messages; we just need to start consistently using the one we have correctly. While it might work a bit for this one, do we really expect to be able to map a single SQLSTATE to each single message at any point? Unless we can do that, it's never going to go all the way - though it might still be useful of course. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 4:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, May 3, 2012 at 1:26 PM, Magnus Hagander mag...@hagander.net wrote: I had a request from a customer asking if we could make a switch to specifically disable the unexpected EOF message that fills lots of peoples logs. Yes, if the new parameter allows a generic filter on multiple user-specified message types. I agree with Simon --- a disable for that specific message seems like a kluge, and an ugly one at that. (The right solution for this customer is to fix their broken application.) But a generic filter capability might be useful enough to justify its keep. Are you thinking basically regexp against the main text, or something else, when you say generic filter capacity? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
Pavel Stehule pavel.steh...@gmail.com writes: 2012/5/3 Tom Lane t...@sss.pgh.pa.us: This notion of anytypename is utterly unworkable anyway; there's no way for the parser to know soon enough that a given argument position needs to be read as a type name rather than a normal expression. type identifier is same identifier like other - but I have no prototype now, so I don't know if there is some trap No, it isn't, at least not if you have any ambition to support array types for instance; to say nothing of types whose standard names are keywords, multiple words, etc. Even if you were willing to restrict the feature to only work for simple-identifier type names, the parser would have thrown an error for failing to find a column by that name, or else would have misinterpreted the type name as a column name, long before there is any opportunity to recognize that the argument position is an anytypename argument. 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] unexpected EOF messages
Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 4:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: I agree with Simon --- a disable for that specific message seems like a kluge, and an ugly one at that. (The right solution for this customer is to fix their broken application.) But a generic filter capability might be useful enough to justify its keep. Are you thinking basically regexp against the main text, or something else, when you say generic filter capacity? In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
On Thu, May 3, 2012 at 9:44 AM, Andrew Dunstan and...@dunslane.net wrote: Why would you always need FROM? that was coming from Hannu's original example: insert into test2 select * from json_to_record(jrec json) as (like test2); how do you work it so you can call: select json_to_record(jrec json) as (like test2); select json_to_array(jrec_json) as ?? create type new_type as (like old_type, extra text); sure, that would be great on it's own merits. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 4:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 4:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: I agree with Simon --- a disable for that specific message seems like a kluge, and an ugly one at that. (The right solution for this customer is to fix their broken application.) But a generic filter capability might be useful enough to justify its keep. Are you thinking basically regexp against the main text, or something else, when you say generic filter capacity? In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. I'm worried it's not really granular enough. regexp-on-text would also have the advantage of being able to filter stuff coming from stored procedures or such as well - without having to invent a whole bunch of SQLSTATEs to put in the stored procedures (consider the usecase when somebody else wrote the stored procedures and the DBA wants to limit the logging). We could have two parameters of course - log_filter_sqlstate and log_filter_re or something like that... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
On Tue, May 01, 2012 at 04:39:32PM -0400, Bruce Momjian wrote: On Tue, Apr 24, 2012 at 09:29:39PM +0300, Peter Eisentraut wrote: I propose that we remove support for the following OS ports from our source tree. They are totally dead, definitely don't work, and/or probably no one remembers what they even were. The code just bit rots and is in the way of future improvements. * Dead/remove: dgux nextstep sunos4 svr4 ultrix4 univel * Dubious, but keep for now: bsdi I am no longer on bsdi and I bet there are no more existing users either. It can be removed, I think. Having received no replies on general from bsdi users considering upgrading to 9.2, I have removed the port. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How hard would it be to support LIKE in return declaration of generic record function calls ?
2012/5/3 Tom Lane t...@sss.pgh.pa.us:
Pavel Stehule pavel.steh...@gmail.com writes:
2012/5/3 Tom Lane t...@sss.pgh.pa.us:
This notion of anytypename is utterly unworkable anyway; there's no
way for the parser to know soon enough that a given argument position
needs to be read as a type name rather than a normal expression.
type identifier is same identifier like other - but I have no
prototype now, so I don't know if there is some trap
No, it isn't, at least not if you have any ambition to support array
types for instance; to say nothing of types whose standard names are
keywords, multiple words, etc. Even if you were willing to restrict the
feature to only work for simple-identifier type names, the parser would
have thrown an error for failing to find a column by that name, or else
would have misinterpreted the type name as a column name, long before
there is any opportunity to recognize that the argument position is
an anytypename argument.
we can identify a position anytypename before raising error - it can
be similar to current identification of PL/pgSQL variables inside
expression. Probably it is too complex for this issue :(
Maybe some keyword can help to us. What do you think about new
operator TYPE that can returns regtype value and can be used together
with polymorphic functions.
CREATE FUNCTION foo(anyregtype, )
RETURNS anyelement AS ..
SELECT foo('mytype', )
or
SELECT foo(TYPE mytype, )
It is little bit cleaner than NULL::type.
Regards
Pavel
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] unexpected EOF messages
Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 4:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Are you thinking basically regexp against the main text, or something else, when you say generic filter capacity? In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. I'm worried it's not really granular enough. I dislike the idea of regex-on-text because of i18n issues. There's no guarantee for instance that all sessions are running with the same LC_MESSAGES locale. In any case, anybody who's dead set on doing it that way can do it today with grep. 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
Pavel Stehule pavel.steh...@gmail.com writes: 2012/5/3 Tom Lane t...@sss.pgh.pa.us: No, it isn't, at least not if you have any ambition to support array types for instance; to say nothing of types whose standard names are keywords, multiple words, etc. we can identify a position anytypename before raising error - it can be similar to current identification of PL/pgSQL variables inside expression. Probably it is too complex for this issue :( [ shrug ... ] Feel free to spend time that way if you want to, but I'm entirely confident that you won't come out with anything except an ugly, unmaintainable, incomplete kluge. Maybe some keyword can help to us. What do you think about new operator TYPE that can returns regtype value and can be used together with polymorphic functions. Doesn't have any more attraction for me than the proposed LIKE extension; that will have the same results and it's at least traceable to SQL-standard notations. 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] unexpected EOF messages
Excerpts from Magnus Hagander's message of jue may 03 10:58:12 -0400 2012: On Thu, May 3, 2012 at 4:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. I'm worried it's not really granular enough. Yeah. regexp-on-text would also have the advantage of being able to filter stuff coming from stored procedures or such as well - without having to invent a whole bunch of SQLSTATEs to put in the stored procedures (consider the usecase when somebody else wrote the stored procedures and the DBA wants to limit the logging). We could have two parameters of course - log_filter_sqlstate and log_filter_re or something like that... The problem with regexes is that they are so expensive. You just need to forget the start anchor and it's suddenly a serious problem. And if you want to filter out a second message, the config option starts to become rather unwieldy. I wonder if there's a better way to selectively filter out messages -- say some sort of config file that contains a list of filenames/numbers of messages to disable. That particular idea would be a pain to maintain, of course, not to mention that it'd change from one release to the next. Hey, maybe we could add a UUID to each ereport() call site ;-) (Maybe the sites that have a load problem caused by log traffic are not the same sites that would like to filter out messages, and thus using regexes is not really a problem. It doesn't seem to be the kind of bet that we want to do.) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 11:20 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hey, maybe we could add a UUID to each ereport() call site ;-) I can't help but feel we're designing a $10.00 solution to a $0.25 problem. I think I'd actually support adding something like a UUID to every ereport and a filtering mechanism that works on that basis. But let's face it: this particular message is exponentially more annoying than average. We're basically forcing application developers to jump through hoops to avoid filling the log with unnecessary chatter. I've spent a bunch of time trying to get rid of them in various past jobs, and I've never gotten any benefit out of having them. Maybe the solution is to just demote that particular message to DEBUG1 and declare that closing the connection is a perfectly sensible way for an application to indicate that the conversation is over. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Torn page hazard in ginRedoUpdateMetapage()
Robert Haas robertmh...@gmail.com writes: Are you planning to commit Noah's patch? I wasn't intending to do so personally in the near future; I've got other things on my to-do list. I won't object if somebody else commits it though. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Robert Haas robertmh...@gmail.com writes: On Thu, May 3, 2012 at 11:20 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hey, maybe we could add a UUID to each ereport() call site ;-) I can't help but feel we're designing a $10.00 solution to a $0.25 problem. I think I'd actually support adding something like a UUID to every ereport and a filtering mechanism that works on that basis. But let's face it: this particular message is exponentially more annoying than average. We're basically forcing application developers to jump through hoops to avoid filling the log with unnecessary chatter. I've spent a bunch of time trying to get rid of them in various past jobs, and I've never gotten any benefit out of having them. Maybe the solution is to just demote that particular message to DEBUG1 and declare that closing the connection is a perfectly sensible way for an application to indicate that the conversation is over. I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. 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] unexpected EOF messages
On Thu, May 3, 2012 at 11:39 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, May 3, 2012 at 11:20 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hey, maybe we could add a UUID to each ereport() call site ;-) I can't help but feel we're designing a $10.00 solution to a $0.25 problem. I think I'd actually support adding something like a UUID to every ereport and a filtering mechanism that works on that basis. But let's face it: this particular message is exponentially more annoying than average. We're basically forcing application developers to jump through hoops to avoid filling the log with unnecessary chatter. I've spent a bunch of time trying to get rid of them in various past jobs, and I've never gotten any benefit out of having them. Maybe the solution is to just demote that particular message to DEBUG1 and declare that closing the connection is a perfectly sensible way for an application to indicate that the conversation is over. I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. I agree. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Magnus Hagander's message: Tom Lane t...@sss.pgh.pa.us wrote: In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. I'm worried it's not really granular enough. Yeah. Just to be sure we're not inventing a problem here, can someone produce an example of a situation where it would not be granular enough (assuming we correct bad SQLSTATE choices where they exist)? I count 232 distinct SQLSTATE values (139 standard values and 93 PostgreSQL-specific values), and we can create more if we want them; although I would recommend against doing that to get finer resolution on a standard SQLSTATE value. A standard value which is too coarse would be the strongest argument for adding some other mechanism, IMO. If we do, I would be inclined toward something to identify distinct conditions within a SQLSTATE, rather than some overarching independent mechanism. -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] Advisory locks seem rather broken
Simon Riggs si...@2ndquadrant.com writes: On Thu, May 3, 2012 at 1:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: If this patch weren't already in a released branch I would be arguing for reverting it. As is, I think we're going to have to clean it up. I don't have time to look at it in detail right now, though. There was an attempt to add a transactional advisory lock call type, but my understanding of the plan for that was not to change the existing advisory lock mechanism. It seems that was bungled, so some change is required, but maybe not total revoke. If the change was actually intended that way then I object to it and I also want it changed back. After studying the patch a bit more I have the definite feeling that it needs to be rewritten from scratch. It has turned the LockMethodData.transactional flag into something completely useless for telling whether a lock is session-level or transaction-local. And, instead of removing that flag and forcing all the code that checks it to be rewritten, it's dropped ad-hoc code into just some of those places. And, as far as I can tell, the ad-hoc test that it's replaced the transactionality tests with is is this lock held by a ResourceOwner, which is a flagrant abuse of the ResourceOwner mechanism. ResourceOwners should only be used to make sure resources are released at appropriate times, they should not cause fundamental changes in the semantics of those resources. I'm inclined to think that a saner implementation would involve splitting the userlock lockmethod into two, one transactional and one not. That gets rid of the when-to-release kluges, but instead we have to think of a way for two different lockmethods to share the same lock keyspace. If we don't split it then we definitely need to figure out someplace else to keep the transactionality flag. Anyway, I'm going to go work on this now ... 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] How hard would it be to support LIKE in return declaration of generic record function calls ?
On Thu, May 3, 2012 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Maybe some keyword can help to us. What do you think about new operator TYPE that can returns regtype value and can be used together with polymorphic functions. Doesn't have any more attraction for me than the proposed LIKE extension; that will have the same results and it's at least traceable to SQL-standard notations. no it won't (unless I'm being completely obtuse in addition to being repetitive): LIKE only works when you treat your function call as a table expression: FROM func() AS ... that's fine, but converting-from-json functions will want to be able to be called anywhere a single value returning function would be normally called. hstore/populate_record trick allows this, so it's not apples to apples. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
On tor, 2012-05-03 at 10:59 -0400, Bruce Momjian wrote: Having received no replies on general from bsdi users considering upgrading to 9.2, I have removed the port. I think that was quite premature. There is no requirement that bsdi users need to read pgsql-general, especially if you give them only a 24 hour notice. The bsdi port still appears to work, and it doesn't cost us anything to maintain it, so I think we should keep it, or at least have a longer grace period. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
On Thu, May 3, 2012 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to think that a saner implementation would involve splitting the userlock lockmethod into two, one transactional and one not. That gets rid of the when-to-release kluges, but instead we have to think of a way for two different lockmethods to share the same lock keyspace. If we don't split it then we definitely need to figure out someplace else to keep the transactionality flag. hm, would that be exposed through the pg_locks view? some users might be running queries like select * from pg_locks where locktype='advisory' and ... it's a minor point, but ideally if they share the same lockspace the same locktype would be reported in the view. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
On Thu, May 3, 2012 at 5:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to think that a saner implementation would involve splitting the userlock lockmethod into two, one transactional and one not. Agreed That gets rid of the when-to-release kluges, but instead we have to think of a way for two different lockmethods to share the same lock keyspace. If we don't split it then we definitely need to figure out someplace else to keep the transactionality flag. Is that even an issue? Do we really want an overlapping lock space? AFAICS you'd either use transactional or session level, but to use both seems bizarre. And if you really did need both, you can put a wrapper around the function to check whether a session level exists before you grant the transaction level lock, or vice versa. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
On Thu, May 03, 2012 at 07:11:47PM +0300, Peter Eisentraut wrote: On tor, 2012-05-03 at 10:59 -0400, Bruce Momjian wrote: Having received no replies on general from bsdi users considering upgrading to 9.2, I have removed the port. I think that was quite premature. There is no requirement that bsdi users need to read pgsql-general, especially if you give them only a 24 hour notice. The bsdi port still appears to work, and it doesn't cost us anything to maintain it, so I think we should keep it, or at least have a longer grace period. I think I was the only user left; I have never heard from a BSD/OS user in the past 5-7 years. The last official release was in 2003/2004: http://en.wikipedia.org/wiki/BSD/OS I rather think I kept it a viable port on my own, and can't anymore. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
Simon Riggs si...@2ndquadrant.com writes: On Thu, May 3, 2012 at 5:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: That gets rid of the when-to-release kluges, but instead we have to think of a way for two different lockmethods to share the same lock keyspace. If we don't split it then we definitely need to figure out someplace else to keep the transactionality flag. Is that even an issue? Do we really want an overlapping lock space? AFAICS you'd either use transactional or session level, but to use both seems bizarre. I dunno. That's the existing user-visible semantics, and I wasn't proposing that we revisit the behavior. It's a bit late for such a proposal given this already shipped in 9.1. 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] Advisory locks seem rather broken
Merlin Moncure mmonc...@gmail.com writes: On Thu, May 3, 2012 at 11:04 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to think that a saner implementation would involve splitting the userlock lockmethod into two, one transactional and one not. hm, would that be exposed through the pg_locks view? some users might be running queries like select * from pg_locks where locktype='advisory' and ... I don't think we can or should change what pg_locks reports. So they'd have to look like just one lockmethod at that level. I'm not actually sure that a split is a practical idea anyway, given that assorted places use a LockMethod as an identifier for a class of locks; unless all of those happen to want to distinguish transactional and session-level userlocks, it'd be problematic. I plan to look also at the idea of removing the transactional field and seeing what that breaks... 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] CLOG extension
Currently, the following can happen: 1. A backend needs a new transaction, so it calls GetNewTransactionId(). It acquires XidGenLock and then calls ExtendCLOG(). 2. ExtendCLOG() decides that a new CLOG page is needed, so it acquires CLogControlLock and then calls ZeroCLOGPage(). 3. ZeroCLOGPage() calls WriteZeroPageXlogRec(), which calls XLogInsert(). 4. XLogInsert() acquires WALInsertLock and then calls AdvanceXLInsertBuffer(). 5. AdvanceXLInsertBuffer() sees that WAL buffers may be full and acquires WALWriteLock to check, and possibly to write WAL if the buffers are in fact full. At this point, we have a single backend simultaneously holding XidGenLock, CLogControlLock, WALInsertLock, and WALWriteLock, which from a concurrency standpoint is, at the risk of considerable understatement, not so great. The situation is no better if (as seems to be more typical) we block waiting for WALWriteLock rather than actually holding it ourselves: either way, nobody can get perform any WAL-logged operation, get an XID, or consult CLOG - so all write activity is blocked, and read activity will block as well as soon as it hits an unhinted tuple. This leads to a couple of questions. First, do we really need to WAL-log CLOG extension at all? Perhaps recovery should simply extend CLOG when it hits a commit or abort record that references a page that doesn't exist yet. Second, is there any harm in pre-extending CLOG? Currently, we don't extend CLOG until we get to the point where the XID we're allocating is on a page that doesn't exist yet, so no further XIDs can be assigned until the extension is complete. We could avoid that by extending a page in advance. Right now, whenever a backend rolls onto a new CLOG page, it must first create it. What we could do instead is try to stay one page ahead of whatever we're currently using: whenever a backend rolls onto a new CLOG page, it creates *the next page*. That way, it can release XidGenLock first and *then* call ExtendCLOG(). That allows all the other backends to continue allocating XIDs in parallel with the CLOG extension. In theory we could still get a pile-up if the entire page worth of XIDs gets used up before we can finish the extension, but that should be pretty rare. (Alternatively, we could introduce a separate background process to extend CLOG, and just have foreground processes kick it periodically. This currently seems like overkill to me.) Third, assuming we do need to write WAL, can we somehow rejigger the logging so that we need not hold CLogControlLock while we're writing it, so that other backends can still do CLOG lookups during that time? Maybe when we take CLogControlLock and observe that extension is needed, we can release CLogControlLock, WAL-log the extension, and then retake CLogControlLock to do SimpleLruZeroPage(). We might need a separate CLogExtensionLock to make sure that two different backends aren't trying to do this dance at the same time, but that should be largely uncontended. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove dead ports?
On 3 May 2012 17:21, Bruce Momjian br...@momjian.us wrote: I think I was the only user left; I have never heard from a BSD/OS user in the past 5-7 years. I'm inclined to agree with Bruce. While it's not reasonable to assume that the lack of a BSD/OS user complaining on -general indicates that there are none, it's also not reasonable for them to expect us to support their operating system for 8 years after the original proprietary vendor. Better to not support BSD/OS than to supply a port that no one really has any confidence in. It's not as if we've ceased support in release branches. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 11:46 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Magnus Hagander's message: Tom Lane t...@sss.pgh.pa.us wrote: In the context of yesterday's discussions, I wonder whether a filter by SQLSTATE would be appropriate. I'm worried it's not really granular enough. Yeah. Just to be sure we're not inventing a problem here, can someone produce an example of a situation where it would not be granular enough (assuming we correct bad SQLSTATE choices where they exist)? I count 232 distinct SQLSTATE values (139 standard values and 93 PostgreSQL-specific values), and we can create more if we want them; although I would recommend against doing that to get finer resolution on a standard SQLSTATE value. A standard value which is too coarse would be the strongest argument for adding some other mechanism, IMO. If we do, I would be inclined toward something to identify distinct conditions within a SQLSTATE, rather than some overarching independent mechanism. Well, nearby Tom and I discussed demoting the message to DEBUG1 when no transaction is in progress. Presumably the two messages would share the same SQL state, unless we're going to create separate SQL states for connection-closed-not-in-a-txn and connection-closed-in-a-txn; and yet I think there's a very decent argument that you're much more likely to care about the latter than the former. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Robert Haas robertmh...@gmail.com writes: Well, nearby Tom and I discussed demoting the message to DEBUG1 when no transaction is in progress. Presumably the two messages would share the same SQL state, unless we're going to create separate SQL states for connection-closed-not-in-a-txn and connection-closed-in-a-txn; and yet I think there's a very decent argument that you're much more likely to care about the latter than the former. If we're going to treat the two cases differently then assigning distinct SQLSTATEs seems entirely reasonable to me. 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] outdated comment in heapam.c
On 03.05.2012 16:08, Andres Freund wrote: Hi, It seems that when Heikki added the multi_insert code the following comment in htup.h wasn't updated: /* * We ran out of opcodes, so heapam.c now has a second RmgrId. These opcodes * are associated with RM_HEAP2_ID, but are not logically different from * the ones above associated with RM_HEAP_ID. We apply XLOG_HEAP_OPMASK, * although currently XLOG_HEAP_INIT_PAGE is not used for any of these. */ #define XLOG_HEAP2_FREEZE 0x00 #define XLOG_HEAP2_CLEAN0x10 /* 0x20 is free, was XLOG_HEAP2_CLEAN_MOVE */ #define XLOG_HEAP2_CLEANUP_INFO 0x30 #define XLOG_HEAP2_VISIBLE 0x40 #define XLOG_HEAP2_MULTI_INSERT 0x50 I suggest simply to remove the although currently XLOG_HEAP_INIT_PAGE is not used for any of these part. Thanks, fixed! -- 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] Future In-Core Replication
On 5/2/12 10:58 PM, Jim Nasby wrote: On 4/29/12 6:03 AM, Simon Riggs wrote: The DML-WITH-LIMIT-1 is required to do single logical updates on tables with non-unique rows. And as for any logical updates we will have huge performance problem when doing UPDATE or DELETE on large table with no indexes, but fortunately this problem is on slave, not master;) While that is possible, I would favour the do-nothing approach. By making the default replication mode = none, we then require a PK to be assigned before allowing replication mode = on for a table. Trying to replicate tables without PKs is a problem that can wait basically. Something that a in-core method might be able to do that an external one can't would be to support a method of uniquely identifying rows in tables with no PK's. A gross example (that undoubtedly wouldn't work in the real world) would be using TID's. A real-world implementation might be based on a hidden serial column. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
Robert Haas robertmh...@gmail.com writes: [ CLOG extension is horrid for concurrency ] Yeah. When that code was designed, a page's worth of transactions seemed like a lot so we didn't worry too much about performance glitches when we crossed a page boundary. It's time to do something about it though. The idea of extending CLOG in advance, so that the work doesn't have to be done with quite so many locks held, sounds like a plan to me. The one thing I'd worry about is that extension has to interact with freezing of very old XIDs and subsequent removal of old clog pages; make sure that pages will get removed before they could possibly get created again. First, do we really need to WAL-log CLOG extension at all? Perhaps recovery should simply extend CLOG when it hits a commit or abort record that references a page that doesn't exist yet. Maybe, but see above. I'd be particularly worried about this in a hot standby situation, as you would then end up with HS queries seeing XIDs (in tuples) for which there was no clog page yet. I'm inclined to think it's better to continue to WAL-log it, but try to arrange to do that without holding the other locks that are now involved. 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] Future In-Core Replication
Something that a in-core method might be able to do that an external one can't would be to support a method of uniquely identifying rows in tables with no PK's. A gross example (that undoubtedly wouldn't work in the real world) would be using TID's. A real-world implementation might be based on a hidden serial column. Realistically you need more than a serial for MM replication. For each row-version, you need: serverID of last update serialID of row timestamp of last update ... and note that this would have to include deleted rows as well. Currently Bucardo does this by using several fields, but you could put together one 128-bit field which contains all of this information. Or you could do an Intagram and compress it into 64 bits, but that would require limiting the problem space in a way you probably can't do it for a general tool. I do agree that depending on user-defined PKs raises a whole host of issues which we'd rather just sidestep, though. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, May 3, 2012 at 11:20 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hey, maybe we could add a UUID to each ereport() call site ;-) I can't help but feel we're designing a $10.00 solution to a $0.25 problem. I think I'd actually support adding something like a UUID to every ereport and a filtering mechanism that works on that basis. But let's face it: this particular message is exponentially more annoying than average. We're basically forcing application developers to jump through hoops to avoid filling the log with unnecessary chatter. I've spent a bunch of time trying to get rid of them in various past jobs, and I've never gotten any benefit out of having them. Maybe the solution is to just demote that particular message to DEBUG1 and declare that closing the connection is a perfectly sensible way for an application to indicate that the conversation is over. I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. I agree - that would certainly be a good fix for this one. One question is do we want something like this: - ereport(COMMERROR, + ereport(IsTransactionState() ? COMMERROR : DEBUG1, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(unexpected EOF on client connection))); (in a couple of places, yes) or do we want to make the text of the error message different as well, saying something like unexpected EOF on client connection with an open transaction? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. I agree - that would certainly be a good fix for this one. One question is do we want something like this: - ereport(COMMERROR, + ereport(IsTransactionState() ? COMMERROR : DEBUG1, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(unexpected EOF on client connection))); or do we want to make the text of the error message different as well, saying something like unexpected EOF on client connection with an open transaction? I'd vote for different texts and different SQLSTATEs too, per other discussion. (I think we'd decided that ERRCODE_PROTOCOL_VIOLATION was a bad choice anyway.) Also, I'm afraid that the above patch probably doesn't work as-is; won't elog.c try to send the DEBUG1 message to the client? I think you'll need some additional code to shut down error message output first. Resetting whereToSendOutput is probably sufficient. 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] extending relations more efficiently
Robert,
* Stephen Frost (sfr...@snowman.net) wrote:
In all seriousness, this is not a great test case unless you can
provide some scripts to make it easy to run it in a reproducible
fashion. Can you?
Yeah, sure, I'll do that. The PostGIS folks have scripts, but they're
kind of ugly, tbh.. I'll give you what I used.
Alright, I made it dirt simple. Get a DB set up, get PostGIS installed
in to it (or not, if you don't really want to..) and then download this:
http://tamriel.snowman.net/~sfrost/testcase.tar.gz
(Note: it's ~3.7G)
It'll untar in to a 'testcase' directory. If you have PostGIS
instealled already, just run testcase/create_linearwater.sql first, then
run all the other .sql files in there in parallel by passing them to
psql, eg:
for file in tl*.sql; do
psql -d mydb -f $file
done
It shouldn't take long to see stuff start blocking on that extension
lock, since the table starts out empty.
If you don't want to install PostGIS, just change the
create_linearwater.sql script to have a column at the end named
'the_geom' and nuke the 'AddGeometryColumn' call. If you run into
trouble getting PG to exhibit the expected behavior, you might try
combining sets of files, like so:
for state in `ls tl_2011_* | cut -f3 -d_ | cut -c1,2 | sort -u`; do
cat tl_2011_${state}*.sql | psql -d mydb
done
Which will reduce the number of processes to just the number of states
and territories (eg: 56), while increasing the amount of work each is
doing.
Thanks,
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] Advisory locks seem rather broken
AFAICS you'd either use transactional or session level, but to use both seems bizarre. And if you really did need both, you can put a wrapper around the function to check whether a session level exists before you grant the transaction level lock, or vice versa. You wouldn't want to *intentionally*. On a large complex codebase, though, who knows? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, nearby Tom and I discussed demoting the message to DEBUG1 when no transaction is in progress. Presumably the two messages would share the same SQL state, unless we're going to create separate SQL states for connection-closed-not-in-a-txn and connection-closed-in-a-txn; and yet I think there's a very decent argument that you're much more likely to care about the latter than the former. If we're going to treat the two cases differently then assigning distinct SQLSTATEs seems entirely reasonable to me. Would it make sense to use 08003 (connection_does_not_exist) when a broken connection for an idle process is discovered, and 08006 (connection_failure) for the in transaction failure? What about a failure just after COMMIT and before successfully sending that result to the client? I notice there's a SQLSTATE 08007 (transaction_resolution_unknown), but I don't know whether that makes sense on the server side, or just on the client side. -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] unexpected EOF messages
Kevin Grittner kevin.gritt...@wicourts.gov writes: Would it make sense to use 08003 (connection_does_not_exist) when a broken connection for an idle process is discovered, and 08006 (connection_failure) for the in transaction failure? What about a failure just after COMMIT and before successfully sending that result to the client? I notice there's a SQLSTATE 08007 (transaction_resolution_unknown), but I don't know whether that makes sense on the server side, or just on the client side. AFAICS, all the 08 class is meant to be issued by client-side code, not the server. I think we probably have to use nonstandard SQLSTATEs for these messages. 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] unexpected EOF messages
On Thu, May 3, 2012 at 7:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. I agree - that would certainly be a good fix for this one. One question is do we want something like this: - ereport(COMMERROR, + ereport(IsTransactionState() ? COMMERROR : DEBUG1, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(unexpected EOF on client connection))); or do we want to make the text of the error message different as well, saying something like unexpected EOF on client connection with an open transaction? I'd vote for different texts and different SQLSTATEs too, per other discussion. (I think we'd decided that ERRCODE_PROTOCOL_VIOLATION was a bad choice anyway.) Also, I'm afraid that the above patch probably doesn't work as-is; won't elog.c try to send the DEBUG1 message to the client? I think you'll need some additional code to shut down error message output first. Resetting whereToSendOutput is probably sufficient. Yeah, I didn't go as far as testing it - there's also more than one spot where we log it... I'll cook up a patch. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Future In-Core Replication
On Thu, May 3, 2012 at 6:03 PM, Josh Berkus j...@agliodbs.com wrote: I do agree that depending on user-defined PKs raises a whole host of issues which we'd rather just sidestep, though. What do you have in mind instead? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
On Thu, May 3, 2012 at 12:12 PM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS you'd either use transactional or session level, but to use both seems bizarre. I'm a bit confused by all this, because we use both transaction and session level locks internally - on the same lock tags - so I don't know why we think it wouldn't be useful for user code to do the same. In fact I'm a bit confused by the original complaint for the same reason - if LockRelationOid and LockRelationIdForSession can coexist, why doesn't the same thing work for advisory locks? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
On Thu, May 3, 2012 at 5:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: [ CLOG extension is horrid for concurrency ] Yeah. When that code was designed, a page's worth of transactions seemed like a lot so we didn't worry too much about performance glitches when we crossed a page boundary. It's time to do something about it though. The idea of extending CLOG in advance, so that the work doesn't have to be done with quite so many locks held, sounds like a plan to me. The one thing I'd worry about is that extension has to interact with freezing of very old XIDs and subsequent removal of old clog pages; make sure that pages will get removed before they could possibly get created again. First, do we really need to WAL-log CLOG extension at all? Perhaps recovery should simply extend CLOG when it hits a commit or abort record that references a page that doesn't exist yet. Maybe, but see above. I'd be particularly worried about this in a hot standby situation, as you would then end up with HS queries seeing XIDs (in tuples) for which there was no clog page yet. I'm inclined to think it's better to continue to WAL-log it, but try to arrange to do that without holding the other locks that are now involved. Why not switch to 1 WAL record per file, rather than 1 per page. (32 pages, IIRC). We can then have the whole new file written as zeroes by a background process, which needn't do that while holding the XidGenLock. My earlier patch to do background flushing from bgwriter can be extended to do that. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
Robert Haas robertmh...@gmail.com writes: On Thu, May 3, 2012 at 12:12 PM, Simon Riggs si...@2ndquadrant.com wrote: AFAICS you'd either use transactional or session level, but to use both seems bizarre. I'm a bit confused by all this, because we use both transaction and session level locks internally - on the same lock tags - so I don't know why we think it wouldn't be useful for user code to do the same. Yeah. I'm too lazy to go look up the original discussion for the feature, but it seems to me that having session-lifetime and transaction-lifetime advisory locks conflict is exactly what was wanted. If you want some that don't conflict, just choose distinct key values. In fact I'm a bit confused by the original complaint for the same reason - if LockRelationOid and LockRelationIdForSession can coexist, why doesn't the same thing work for advisory locks? The problem (or problems) is bad implementation, not the specification. In particular, at least one place that should have been patched was not. 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] unexpected EOF messages
On Thu, May 3, 2012 at 7:21 PM, Magnus Hagander mag...@hagander.net wrote: On Thu, May 3, 2012 at 7:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Thu, May 3, 2012 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: I could support that with one tweak: it's only DEBUG1 if you don't have an open transaction. Dropping the connection while in a transaction *is* an application bug; I don't care how lazy the app programmer is feeling. I agree - that would certainly be a good fix for this one. One question is do we want something like this: - ereport(COMMERROR, + ereport(IsTransactionState() ? COMMERROR : DEBUG1, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(unexpected EOF on client connection))); or do we want to make the text of the error message different as well, saying something like unexpected EOF on client connection with an open transaction? I'd vote for different texts and different SQLSTATEs too, per other discussion. (I think we'd decided that ERRCODE_PROTOCOL_VIOLATION was a bad choice anyway.) Also, I'm afraid that the above patch probably doesn't work as-is; won't elog.c try to send the DEBUG1 message to the client? I think you'll need some additional code to shut down error message output first. Resetting whereToSendOutput is probably sufficient. Yeah, I didn't go as far as testing it - there's also more than one spot where we log it... I'll cook up a patch. Heh - we already used ERRCODE_CONNECTION_FAILURE on the errors in copy.c. Since COPY can only happen when there is a transaction (right?), I just changed those error messages for consistency. This patch works through my testing - can anyone spot a hole in it still? The next question is - of course - whether we can sneak this in before beta... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ ereport_eof.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Tom Lane t...@sss.pgh.pa.us wrote: AFAICS, all the 08 class is meant to be issued by client-side code, not the server. I think we probably have to use nonstandard SQLSTATEs for these messages. OK, if we're going that route, how about using Class 2D * Invalid Transaction Termination? I still think it might be useful to differentiate in our server log between the case where the transaction failed and the case where the transaction committed but we don't know that the client got the news of that. How about something like: 2DP01 connection_lost_during_transaction 2DP02 connection_lost_during_commit_notification I'm less sure what makes sense if the connection fails while idle (not in transaction). If you don't like Class 08 * Connection Exception for that, I'm not quite sure where it belongs. -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] unexpected EOF messages
Magnus Hagander mag...@hagander.net writes: Heh - we already used ERRCODE_CONNECTION_FAILURE on the errors in copy.c. Since COPY can only happen when there is a transaction (right?), I just changed those error messages for consistency. Agreed on changing the message texts to match, but I wonder whether we ought not switch all those SQLSTATEs to something different. Per my comment to Kevin, I think the whole 08 class is meant to be issued on the client side. Maybe it's okay to conflate a server-detected connection loss with client-detected loss, but I'm not convinced. 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] Advisory locks seem rather broken
On Thursday, May 03, 2012 06:12:04 PM Simon Riggs wrote: AFAICS you'd either use transactional or session level, but to use both seems bizarre. And if you really did need both, you can put a wrapper around the function to check whether a session level exists before you grant the transaction level lock, or vice versa. I don't think at all that this is crazy. For queues it very well might make sense for a dequeuing side to hold a lock in a session mode while the putting side uses normal transaction scope (because its done inside a trigger or such). Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
Kevin Grittner kevin.gritt...@wicourts.gov writes: I still think it might be useful to differentiate in our server log between the case where the transaction failed and the case where the transaction committed but we don't know that the client got the news of that. How about something like: 2DP01 connection_lost_during_transaction 2DP02 connection_lost_during_commit_notification That would be a useful distinction, but I'm not sure how easily our code can make it. I'm less sure what makes sense if the connection fails while idle (not in transaction). If you don't like Class 08 * Connection Exception for that, I'm not quite sure where it belongs. I'm not convinced that these cases belong in any of the standard's classes. IMO the standard is only standardizing application-visible error cases, which these are not. In particular I think class 2D is not appropriate, since AFAICS the standard means that to pertain to incorrect issuance of a COMMIT or ROLLBACK command. 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] CLOG extension
On Thu, May 3, 2012 at 1:27 PM, Simon Riggs si...@2ndquadrant.com wrote: Why not switch to 1 WAL record per file, rather than 1 per page. (32 pages, IIRC). We can then have the whole new file written as zeroes by a background process, which needn't do that while holding the XidGenLock. I thought about doing a single record covering a larger number of pages, but that would be an even bigger hit if it were ever to occur in the foreground path, so you'd want to be very sure that the background process was going to absorb all the work. And if the background process is going to absorb all the work, then I'm not sure it matters very much whether we emit one xlog record or 32. After all it's pretty low volume compared to all the other xlog traffic. Maybe there's some room for optimization here, but it doesn't seem like the first thing to pursue. Doing it a background process, though, may make sense. What I'm a little worried about is that - on a busy system - we've only got about 2 seconds to complete each CLOG extension, and we must do an fsync in order to get there. And the fsync can easily take a good chunk of (or even more than) that two seconds. So it's possible that saddling the bgwriter with this responsibility would be putting too many eggs in one basket. We might find that under the high-load scenarios where this is supposed to help, bgwriter is already too busy doing other things, and it doesn't get around to extending CLOG quickly enough. Or, conversely, we might find that it does get around to extending CLOG quickly enough, but consequently fails to carry out its regular duties. We could of course add a NEW background process just for this purpose, but it'd be nicer if we didn't have to go that far. My earlier patch to do background flushing from bgwriter can be extended to do that. I've just been looking at that patch again, since as we discussed before commit 3ae5133b1cf478d51f2003bc68ba0edb84c7 fixed a problem in this area, and it may be that we can now show a benefit of this approach where we couldn't before. I think it's separate from what we're discussing here, so let me write more about that on another thread after I poke at it a little more. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch submission: truncate trailing nulls from heap rows to reduce the size of the null bitmap
Tom, So that I can test this properly, what is the specific use-case we'd expect to be slow with this patch? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: xReader, double-effort (was: Temporary tables under hot standby)
One thing I wanted to mention is that non-binary replication has an added advantage over binary from a DR standpoint: if corruption occurs on a master it is more likely to make it into your replicas thanks to full page writes. You might want to consider that depending on how sensitive your data is. Yeah, we've seen this a few times. We just recently had to rescue a client from HS-wide corruption using Slony. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Temporary tables under hot standby
(on standby) INSERT INTO s1 SELECT1; SELECT ... FROM s1 WHERE ... which seems to me to be actually harder than just rewriting as derived table and isn't an option on Microstrategy etc, hence my observation that GTTs don't help HS much. What I would like to see, one day, is for temp tables to work without any changes. 100% agreement. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
On Thu, May 3, 2012 at 2:50 PM, Robert Haas robertmh...@gmail.com wrote: Doing it a background process, though, may make sense. What I'm a little worried about is that - on a busy system - we've only got about 2 seconds to complete each CLOG extension, and we must do an fsync in order to get there. Scratch that - we don't routinely need to do an fsync, though we can end up backed up behind one if wal_buffers are full. I'm still more interested in the do-it-a-page-in-advance idea discussed upthread, but this might be viable as well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
On Thu, May 3, 2012 at 7:50 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 3, 2012 at 1:27 PM, Simon Riggs si...@2ndquadrant.com wrote: Why not switch to 1 WAL record per file, rather than 1 per page. (32 pages, IIRC). We can then have the whole new file written as zeroes by a background process, which needn't do that while holding the XidGenLock. I thought about doing a single record covering a larger number of pages, but that would be an even bigger hit if it were ever to occur in the foreground path, so you'd want to be very sure that the background process was going to absorb all the work. And if the background process is going to absorb all the work, then I'm not sure it matters very much whether we emit one xlog record or 32. After all it's pretty low volume compared to all the other xlog traffic. Maybe there's some room for optimization here, but it doesn't seem like the first thing to pursue. Doing it a background process, though, may make sense. What I'm a little worried about is that - on a busy system - we've only got about 2 seconds to complete each CLOG extension, and we must do an fsync in order to get there. And the fsync can easily take a good chunk of (or even more than) that two seconds. So it's possible that saddling the bgwriter with this responsibility would be putting too many eggs in one basket. We might find that under the high-load scenarios where this is supposed to help, bgwriter is already too busy doing other things, and it doesn't get around to extending CLOG quickly enough. Or, conversely, we might find that it does get around to extending CLOG quickly enough, but consequently fails to carry out its regular duties. We could of course add a NEW background process just for this purpose, but it'd be nicer if we didn't have to go that far. Your two paragraphs have roughly opposite arguments... Doing it every 32 pages would give you 30 seconds to complete the fsync, if you kicked it off when half way through the previous file - at current maximum rates. So there is utility in doing it in larger chunks. If it is too slow, we would just wait for sync like we do now. I think we need another background process since we have both cleaning and pre-allocating tasks to perform. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unexpected EOF messages
On Thu, May 3, 2012 at 7:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Heh - we already used ERRCODE_CONNECTION_FAILURE on the errors in copy.c. Since COPY can only happen when there is a transaction (right?), I just changed those error messages for consistency. Agreed on changing the message texts to match, but I wonder whether we ought not switch all those SQLSTATEs to something different. Per my comment to Kevin, I think the whole 08 class is meant to be issued on the client side. Maybe it's okay to conflate a server-detected connection loss with client-detected loss, but I'm not convinced. Sure,that's a simple search and replace of course... If we can come to a decision about what codes to actually use. I'm not sure I have much input other than that I agree they need to be different :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: xReader, double-effort (was: Temporary tables under hot standby)
Josh Berkus j...@agliodbs.com wrote: One thing I wanted to mention is that non-binary replication has an added advantage over binary from a DR standpoint: if corruption occurs on a master it is more likely to make it into your replicas thanks to full page writes. You might want to consider that depending on how sensitive your data is. Yeah, we've seen this a few times. We just recently had to rescue a client from HS-wide corruption using Slony. That's an interesting point. Out of curiosity, how did the corruption originate? It suggests a couple questions: (1) Was Slony running before the corruption occurred? If not, how was Slony helpful? I know that in our environment, where we have both going through separate streams, with a repository of the logical transactions, we would use PITR recovery to get to the latest known good state which we could easily identify, and then replay the logical transactions to top it off to get current. If necessary we could skip logical transactions which were problematic results of the corruption. (2) If logical transactions had been implemented as additions to the WAL stream, and Slony was using that, do you think they would still have been usable for this recovery? Perhaps sending both physical and logical transaction streams over the WAN isn't such a bad thing, if it gives us more independent recovery mechanisms. That's fewer copies than we're sending with current trigger-based techniques. It would be particularly attractive is we could omit (filter out) certain tables before going across the WAN. I would be willing to risk sending the big raster-scanned documents through just the physical channel so long as I had a nightly compare of md5sum values on both sides so we can resend any corrupted data (or tell people to rescan). -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] Uppercase tab completion keywords in psql?
Peter, where are we on this?
---
On Fri, Mar 30, 2012 at 08:16:59PM +0300, Peter Eisentraut wrote:
On fre, 2012-03-23 at 07:52 -0700, David Fetter wrote:
On Thu, Mar 22, 2012 at 06:05:30PM -0400, Andrew Dunstan wrote:
On 03/22/2012 05:49 PM, Bruce Momjian wrote:
Robert Haas and I are disappointed by this change. I liked the
fact that I could post nice-looking SQL queries without having to
use my capslock key (which I use as a second control key). Any
chance of reverting this change?
Should it be governed by a setting?
Something like (upper|lower|preserve) ?
How about this patch then? (There are actually four possible settings,
see patch.)
diff --git i/doc/src/sgml/ref/psql-ref.sgml w/doc/src/sgml/ref/psql-ref.sgml
index b849101..be9d37d 100644
--- i/doc/src/sgml/ref/psql-ref.sgml
+++ w/doc/src/sgml/ref/psql-ref.sgml
@@ -2652,6 +2652,22 @@ bar
/varlistentry
varlistentry
+termvarnameCOMP_KEYWORD_CASE/varname/term
+listitem
+para
+Determines which letter case to use when completing an SQL key word.
+If set to literallower/literal or literalupper/literal, the
+completed word will be in lower or upper case, respectively. If set
+to literalpreserve-lower/literal
+or literalpreserve-upper/literal (the default), the completed
word
+will be in the case of the word already entered, but words being
+completed without anything entered will be in lower or upper case,
+respectively.
+/para
+/listitem
+ /varlistentry
+
+ varlistentry
termvarnameDBNAME/varname/term
listitem
para
diff --git i/src/bin/psql/tab-complete.c w/src/bin/psql/tab-complete.c
index 6f481bb..00d87d5 100644
--- i/src/bin/psql/tab-complete.c
+++ w/src/bin/psql/tab-complete.c
@@ -682,7 +682,7 @@ static char **complete_from_variables(char *text,
const char *prefix, const char
*suffix);
static char *complete_from_files(const char *text, int state);
-static char *pg_strdup_same_case(const char *s, const char *ref);
+static char *pg_strdup_keyword_case(const char *s, const char *ref);
static PGresult *exec_query(const char *query);
static void get_previous_words(int point, char **previous_words, int nwords);
@@ -3048,7 +3048,7 @@ create_or_drop_command_generator(const char *text, int
state, bits32 excluded)
{
if ((pg_strncasecmp(name, text, string_length) == 0)
!(words_after_create[list_index - 1].flags excluded))
- return pg_strdup_same_case(name, text);
+ return pg_strdup_keyword_case(name, text);
}
/* if nothing matches, return NULL */
return NULL;
@@ -3335,9 +3335,9 @@ complete_from_list(const char *text, int state)
if (completion_case_sensitive)
return pg_strdup(item);
else
- /* If case insensitive matching was requested
initially, return
- * it in the case of what was already entered.
*/
- return pg_strdup_same_case(item, text);
+ /* If case insensitive matching was requested
initially, adjust
+ * the case according to setting. */
+ return pg_strdup_keyword_case(item, text);
}
}
@@ -3374,9 +3374,9 @@ complete_from_const(const char *text, int state)
if (completion_case_sensitive)
return pg_strdup(completion_charp);
else
- /* If case insensitive matching was requested
initially, return it
- * in the case of what was already entered. */
- return pg_strdup_same_case(completion_charp, text);
+ /* If case insensitive matching was requested
initially, adjust the
+ * case according to setting. */
+ return pg_strdup_keyword_case(completion_charp, text);
}
else
return NULL;
@@ -3484,27 +3484,48 @@ complete_from_files(const char *text, int state)
/*
- * Make a pg_strdup copy of s and convert it to the same case as ref.
+ * Make a pg_strdup copy of s and convert the case according to
+ * COMP_KEYWORD_CASE variable, using ref as the text that was already
entered.
*/
static char *
-pg_strdup_same_case(const char *s, const char *ref)
+pg_strdup_keyword_case(const char *s, const char *ref)
{
char *ret, *p;
unsigned char first = ref[0];
+ int tocase;
+ const char *varval;
+
+ varval = GetVariable(pset.vars, COMP_KEYWORD_CASE);
+
Re: [HACKERS] Have we out-grown Flex?
I haven't tried quex, but I have tried lemon (which can be broken out of SQLite) and re2c and ragel. I like ragel and lemon, but the combination supports a push-parser style from memory, and many tools are inconvenient unless you are prepared to suck in a whole message before parsing, or let the parser drive a pull loop, or use a coroutine structure. Could go all trendy and use a PEG tool like, er,, peg (http://piumarta.com/software/peg/). (I haven't tried them tho') James -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
I wrote: Robert Haas robertmh...@gmail.com writes: In fact I'm a bit confused by the original complaint for the same reason - if LockRelationOid and LockRelationIdForSession can coexist, why doesn't the same thing work for advisory locks? The problem (or problems) is bad implementation, not the specification. In particular, at least one place that should have been patched was not. After calming down a bit and reading the patch more, I think the only place that was really seriously overlooked was PREPARE TRANSACTION, specifically AtPrepare_Locks/PostPrepare_Locks. To some extent this is just a matter of missing code, but there is one assumption in there that seems hard to get around: the code expects that any given lock object will be held at session level or at transaction level, never both. If it is held at session level then ownership stays with the current session, otherwise ownership of the lock is transferred to the prepared transaction (the gxact object). Since advisory-lock objects can be held at session and transaction levels concurrently, this assumption fails. It might seem obvious to move the transaction lock to the prepared xact while keeping the session ownership, but that doesn't look workable because it would require an additional ProcLock object in shared memory, which we cannot guarantee in advance is available (and failing at the PostPrepare stage is not acceptable). I'm inclined to say that you can PREPARE if your session holds a given advisory lock at either session or transaction level, but not both. This is a bit annoying but doesn't seem likely to be a real problem in practice, so thinking of a hack to support the case seems like more work than is justified. 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] Have we out-grown Flex?
Doesn't that imply that a plan cache might be worthwhile? But no matter: didn't the OP really have issue with packaging and Windows support - and there are a lot of Windows users, and in general there are many Windows devs: making it easier for them to contribute has to be good doesn't it? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: xReader, double-effort (was: Temporary tables under hot standby)
That's an interesting point. Out of curiosity, how did the corruption originate? We're still not sure. It appears to be in the system catalogs, though. Note that the original master developed memory issues. It suggests a couple questions: (1) Was Slony running before the corruption occurred? No. If not, how was Slony helpful? Install, replicate DB logically, new DB works fine. (2) If logical transactions had been implemented as additions to the WAL stream, and Slony was using that, do you think they would still have been usable for this recovery? Quite possibly not. Perhaps sending both physical and logical transaction streams over the WAN isn't such a bad thing, if it gives us more independent recovery mechanisms. That's fewer copies than we're sending with current trigger-based techniques. Frankly, there's nothing wrong with the Slony model for replication except for the overhead of: 1. triggers 2. queues 3. Running DDL However, the three above are really big issues. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Have we out-grown Flex?
On Thu, May 3, 2012 at 12:51 PM, james ja...@mansionfamily.plus.com wrote: I haven't tried quex, but I have tried lemon (which can be broken out of SQLite) and re2c and ragel. I like ragel and lemon, but the combination supports a push-parser style from memory, and many tools are inconvenient unless you are prepared to suck in a whole message before parsing, or let the parser drive a pull loop, or use a coroutine structure. Could go all trendy and use a PEG tool like, er,, peg (http://piumarta.com/software/peg/). (I haven't tried them tho') I think the goal is not trendy nor easy to use (but easy to maintain, at least...), but faster, and even then there is some doubt if any amount of lexer optimization could possibly matter given everything else that needs to happen to execute a query. Better error messages (with position information) might be a functional enhancement that I'd like, but I don't think flex is limiting in that regard; rather, a lot more information already exposed by flex would have to be passed through the semantic analyzer. Provided it could matter, are these tools faster than flex? My limited understanding is probably not. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Have we out-grown Flex?
I believe there are tools that are significantly faster than flex. I believe re2c generates code that is faster. But the key thing is to test, probably, or perhaps ask around. I'm out of touch, but from memory flex wasn't the be-all and end-all. Lemon is definitely easy to maintain/port and the result is pretty nice, too (I know Bison/Yacc wasn't the focus here). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
On Thu, May 3, 2012 at 3:20 PM, Simon Riggs si...@2ndquadrant.com wrote: Your two paragraphs have roughly opposite arguments... Doing it every 32 pages would give you 30 seconds to complete the fsync, if you kicked it off when half way through the previous file - at current maximum rates. So there is utility in doing it in larger chunks. Maybe, but I'd like to try changing one thing at a time. If we change too much at once, it's likely to be hard to figure out where the improvement is coming from. Moving the task to a background process is one improvement; doing it in larger chunks is another. Those deserve independent testing. If it is too slow, we would just wait for sync like we do now. I think we need another background process since we have both cleaning and pre-allocating tasks to perform. Possibly. I have some fear of ending up with too many background processes, but we may need them. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
On Thu, May 3, 2012 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to say that you can PREPARE if your session holds a given advisory lock at either session or transaction level, but not both. This is a bit annoying but doesn't seem likely to be a real problem in practice, so thinking of a hack to support the case seems like more work than is justified. I'd be more inclined to say that if you have a session-level lock, you can't prepare, period. Doesn't a rollback release session-level locks? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CLOG extension
On Thu, May 3, 2012 at 1:56 PM, Robert Haas robertmh...@gmail.com wrote: Possibly. I have some fear of ending up with too many background processes, but we may need them. I sort of care about this, but only on systems that are not very busy and could otherwise get by with fewer resources -- for example, it'd be nice to turn off autovacuum and the stat collector if it really doesn't have to be around. Perhaps a Nap Commander[0] process or procedure (if baked into postmaster, to optimize to one process from two) would do the trick? This may be related to some of the nascent work mentioned recently on allowing for backend daemons, primarily for event scheduling. Said Nap Commander could also possibly help with wakeups. [0]: Credit to Will Leinweber for the memorable name. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Advisory locks seem rather broken
Robert Haas robertmh...@gmail.com writes: On Thu, May 3, 2012 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm inclined to say that you can PREPARE if your session holds a given advisory lock at either session or transaction level, but not both. This is a bit annoying but doesn't seem likely to be a real problem in practice, so thinking of a hack to support the case seems like more work than is justified. I'd be more inclined to say that if you have a session-level lock, you can't prepare, period. The bug report that started this investigation was precisely that preparing in the presence of a session-level lock failed, where it has worked in every release before 9.1; the prepare is supposed to simply ignore session locks. Doesn't a rollback release session-level locks? No, it doesn't. Read http://www.postgresql.org/docs/devel/static/explicit-locking.html#ADVISORY-LOCKS (which could use some wordsmithing, but the specification is clear enough) 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
