Re: [HACKERS] Call for Google Summer of Code mentors, admins
Re: Andres Freund 2013-02-18 20130218213711.ga1...@awork2.anarazel.de On 2013-02-14 10:02:13 -0800, Josh Berkus wrote: - Please suggest project ideas for GSOC pg_upgrade support for debian's pg_upgradecluster We'd need Peter to be the student for that one :) http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=682938 I can certainly mentor some packaging/QA/web frontend project(s) in the apt.postgresql.org area. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sql_drop Event Trigger
Robert Haas robertmh...@gmail.com writes: Well, there's this, upon which we surely have not achieved consensus: http://www.postgresql.org/message-id/ca+tgmobq6ngsxguihwqcygf0q+7y9zhnerepo3s1vswkknw...@mail.gmail.com Sub-Transaction Handling. I fail to come up with a regression test showing any problem here, and Álvaro is now working on the patch so he might be finding both a failure test and a fix. And then Tom also wrote this, which is kind of a good point, too: Well, a list of object OIDs is of exactly zero use once the command has been carried out. So I don't think that that represents a useful or even very testable feature on its own, if there's no provision to fire user code while the OIDs are still in the catalogs. That's the reason why I've been proposing that we first add some information to the event triggers, then see about the DROP support. You might want to realize that the current event triggers implementation is not even publishing the object ID now, only the command tag and the name of the event. We still don't have any way to make any use of the whole thing, apart from maybe block all commands (but all is in fact a subset of known DDL). I don't think we still are able to solve *any* use case with what's currently commited. At all. Given that, it's really hard for me to get excited on that very point. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] pg_basebackup with -R option and start standby have problems with escaped password
On Monday, February 18, 2013 8:06 PM Boszormenyi Zoltan wrote:
On 2013-01-29 11:15 keltezéssel, Magnus Hagander írta:
On Thu, Jan 24, 2013 at 7:04 AM, Hari Babu wrote:
On Wed, Jan 23, 2013 11:48 PM, Magnus Hagander wrote:
On Wed, Jan 23, 2013 at 10:18 AM, Hari Babu wrote:
Test scenario to reproduce:
1. Start the server
2. create the user as follows
./psql postgres -c create user user1 superuser
login password 'use''1'
3. Take the backup with -R option as follows.
./pg_basebackup -D ../../data1 -R -U user1 -W
The following errors are occurring when the new standby on the
backup database starts.
FATAL: could not connect to the primary server: missing = after
1'
in
connection info string
What does the resulting recovery.conf file look like?
The recovery.conf which is generated is as follows
standby_mode = 'on'
primary_conninfo = 'user=''user1'' password=''use''1'' port=''5432'' '
I observed the problem is while reading primary_conninfo from the
recovery.conf file the function GUC_scanstr removes the quotes of
the string and also makes the continuos double quote('') as single
quote(').
By using the same connection string while connecting to primary
server the function conninfo_parse the escape quotes are not able
to parse properly and it is leading to problem.
please correct me if any thing wrong in my observation.
Well, it's clearly broken at least :O
So, there is a bug in generating recovery.conf by not double-escaping the
values and another bug in parsing the connection string in libpq when the
parameter value starts with a single-quote character.
Attached are two patches to fix these two bugs, the libpq part can be
back-patched.
With the attached patches I tested the defect and it is fixed.
Regards,
Hari babu.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
Dne 19.02.2013 05:46, Alvaro Herrera napsal: Alvaro Herrera wrote: I have pushed it now. Further testing, of course, is always welcome. Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. Hmmm, interesting. A single Windows machine, while the other Windows machines seem to work fine (although some of them were not built for a few weeks). I'll look into that, but I have no clue why this might happen. Except maybe for some unexpected timing issue or something ... Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. Hmmm, interesting. A single Windows machine, while the other Windows machines seem to work fine (although some of them were not built for a few weeks). Could be random chance --- we've seen the same failure before, eg http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2012-11-25%2006%3A00%3A00 regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add PQconninfoParseParams and PQconninfodefaultsMerge to libpq
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Amit Kapila Sent: Monday, February 18, 2013 6:38 PM To: 'Heikki Linnakangas' Cc: 'Phil Sorber'; 'Alvaro Herrera'; 'Magnus Hagander'; 'PostgreSQL- development' Subject: Re: [HACKERS] [PATCH] Add PQconninfoParseParams and PQconninfodefaultsMerge to libpq On Monday, February 18, 2013 1:41 PM Heikki Linnakangas wrote: On 18.02.2013 06:07, Amit Kapila wrote: On Sunday, February 17, 2013 8:44 PM Phil Sorber wrote: On Sun, Feb 17, 2013 at 1:35 AM, Amit kapilaamit.kap...@huawei.com wrote: Now the patch of Phil Sober provides 2 new API's PQconninfoParseParams(), and PQconninfodefaultsMerge(), using these API's I can think of below way for patch pass a connection string to pg_basebackup, ... 1. Call existing function PQconinfoParse() with connection string input by user and get PQconninfoOption. 2. Now use the existing keywords (individual options specified by user) and extract the keywords from PQconninfoOption structure and call new API PQconninfoParseParams() which will return PQconninfoOption. The PQconninfoOption structure returned in this step will contain all keywords 3. Call PQconninfodefaultsMerge() to merge any default values if exist. Not sure if this step is required? 4. Extract individual keywords from PQconninfoOption structure and call PQconnectdbParams. Is this inline with what you have in mind or you have thought of some other simpler way of using new API's? Yep, that's roughly what I had in mind. I don't think it's necessary to merge defaults in step 3, but it needs to add the replication=true and dbname=replication options. I could see the advantage of calling PQconninfoParseParams() in step-2 is that it will remove the duplicate values by overriding the values for conflicting keywords. This is done in function conninfo_array_parse() which is called from PQconninfoParseParams(). Am I right or there is any other advantage of calling PQconninfoParseParams()? If there is no other advantage then this is done in PQconnectdbParams() also, so can't we avoid calling PQconninfoParseParams()? I note that pg_dumpall also has a similar issue as pg_basebackup and pg_receivexlog; there's no way to pass a connection string to it either. I think not only pg_dumpall, but we need to add it to pg_dump. As -C is already used option in pg_dump, I need to use something different. I am planning to use -K as new option(available ones were d,g,j,k,l,m,p,q,y). I am planning to keep option same for pg_dumpall, as pg_dumpall internally calls pg_dump with the options supplied by user. In fact, I think we can hack the string passed to pg_dump to change the option from -C to -K, but I am not able see if it will be way better than using -K for both. Suggestions? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] indirect toast tuple support
On Sat, Feb 16, 2013 at 11:42 AM, Andres Freund and...@2ndquadrant.com wrote: Given that there have been wishes to support something like b) for quite some time, independent from logical decoding, it seems like a good idea to add support for it. Its e.g. useful for avoiding repeated detoasting or decompression of tuples. The problem with b) is that there is no space in varlena's flag bits to directly denote that a varlena points into memory instead of either directly containing the data or a varattrib_1b_e containing a varatt_external pointing to an on-disk toasted tuple. So the other way that we could do this is to use something that's the same size as a TOAST pointer but has different content - the seemingly-obvious choice being va_toastrelid == 0. I'd be a little reluctant to do it the way you propose because we might, at some point, want to try to reduce the size of toast pointers. If you have a tuple with many attributes, the size of the TOAST pointers themselves starts to add up. It would be nice to be able to have 8 byte or even 4 byte toast pointers to handle those situations. If we steal one or both of those lengths to mean the data is cached in memory somewhere then we can't use those lengths in a smaller on-disk representation, which would seem a shame. But having said that, +1 on the general idea of getting something like this done. We really need a better infrastructure to avoid copying large values around repeatedly in memory - a gigabyte is a lot of data to be slinging around. Of course, you will not be surprised to hear that I think this is 9.4 material. -- 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] Materialized views WIP patch
On Mon, Feb 18, 2013 at 4:48 PM, Kevin Grittner kgri...@ymail.com wrote: This should allow me to simplify the code a little bit and move the RMV step to the very end. That may have some advantages when users want to start using the database while MVs are being populated. In the department of crazy ideas, what about having pg_dump NEVER refresh ANY materialized views? It's true that the job of pg_dump and pg_restore is to put the new database in the same state that the old database was in, but I think you could make a reasonable case that materialized views ought to be an exception. After all, even with all of this infrastructure, chances are pretty good that the new MV contents won't end up being the same as the old MV contents on the old server - because the old MVs could easily have been stale. So why not just get the restore over with as fast as possible, and then let the user refresh the MVs that they think need refreshing (perhaps after getting the portions of their system that don't rely on MVs up and running)? At the very least, I think we ought to have an option for this behavior. But the more I think about it, the more I think maybe it ought to be the default. -- 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] sql_drop Event Trigger
On Sun, Feb 17, 2013 at 4:12 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Well, a list of object OIDs is of exactly zero use once the command has been carried out. So I don't think that that represents a useful or even very testable feature on its own, if there's no provision to fire user code while the OIDs are still in the catalogs. That's the reason why I've been proposing that we first add some information to the event triggers, then see about the DROP support. I think the question of the interface to the data and the data to expose are pretty tightly related. You can't exactly get one right and the other one wrong and say, OK, we'll fix it later. You might want to realize that the current event triggers implementation is not even publishing the object ID now, only the command tag and the name of the event. I know that. I also know that after I committed this patch in July, many months went by before we had any further discussion of next steps. I'll admit that some of this stuff was on the table for the November CommitFest, but I also won't accept complete blame for the fact that we're not further along than we are. -- 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] [RFC] indirect toast tuple support
On 2013-02-19 08:48:05 -0500, Robert Haas wrote: On Sat, Feb 16, 2013 at 11:42 AM, Andres Freund and...@2ndquadrant.com wrote: Given that there have been wishes to support something like b) for quite some time, independent from logical decoding, it seems like a good idea to add support for it. Its e.g. useful for avoiding repeated detoasting or decompression of tuples. The problem with b) is that there is no space in varlena's flag bits to directly denote that a varlena points into memory instead of either directly containing the data or a varattrib_1b_e containing a varatt_external pointing to an on-disk toasted tuple. So the other way that we could do this is to use something that's the same size as a TOAST pointer but has different content - the seemingly-obvious choice being va_toastrelid == 0. Unfortunately that would mean you need to copy the varatt_external (or whatever it would be called) to aligned storage to check what it is. Thats why I went the other way. Its a bit sad that varatt_1b_e only contains a length and not a type byte. I would like to change the storage of existing toast types but thats not going to work for pg_upgrade reasons... I'd be a little reluctant to do it the way you propose because we might, at some point, want to try to reduce the size of toast pointers. If you have a tuple with many attributes, the size of the TOAST pointers themselves starts to add up. It would be nice to be able to have 8 byte or even 4 byte toast pointers to handle those situations. If we steal one or both of those lengths to mean the data is cached in memory somewhere then we can't use those lengths in a smaller on-disk representation, which would seem a shame. I agree. As I said above, having the type overlayed into the lenght was and is a bad idea, I just haven't found a better one thats compatible yet. Except inventing typlen=-3 aka toast2 or something. But even that wouldn't help getting rid of existing pg_upgraded tables. Besides being a maintenance nightmare. The only reasonable thing I can see us doing is renaming varattrib_1b_e.va_len_1be into va_type and redefine VARSIZE_1B_E into a switch that maps types into lengths. But I think I would put this off, except placing a comment somewhere, until its gets necessary. But having said that, +1 on the general idea of getting something like this done. We really need a better infrastructure to avoid copying large values around repeatedly in memory - a gigabyte is a lot of data to be slinging around. Of course, you will not be surprised to hear that I think this is 9.4 material. Yes, obviously. But I need time to actually propose a working patch (I already found 2 bugs in what I had submitted), thats why I brought it up now. No point in wasting time if there's an oviously better idea around. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON Function Bike Shedding
On Mon, Feb 18, 2013 at 10:42 AM, Merlin Moncure mmonc...@gmail.com wrote: if you wanted to. And yes, I absolutely think this is superior to cluttering the public namespace with xml specific verbage, and could be extended to other formats. Look at the other way: we currently have encode(format text, stuff bytea). Would we be better off with hex_encode(bytea), escape_encode(bytea)... .etc? Probably not, but that's not what I proposed either. The argument for removing json_ prefix is that when function behaviors are unambiguously controlled by the arguments, decorating the function name to match the input argument is unnecessary verbosity. I've come to value greppability of source code pretty highly. I think that some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. -- 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] JSON Function Bike Shedding
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Robert Haas Sent: 19 February 2013 15:05 To: Merlin Moncure Cc: David E. Wheeler; PostgreSQL-development Hackers The argument for removing json_ prefix is that when function behaviors are unambiguously controlled by the arguments, decorating the function name to match the input argument is unnecessary verbosity. I've come to value greppability of source code pretty highly. I think that some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. Let me join the minority here, +1 Regards Petr Jelinek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] indirect toast tuple support
On Tue, Feb 19, 2013 at 9:00 AM, Andres Freund and...@2ndquadrant.com wrote: So the other way that we could do this is to use something that's the same size as a TOAST pointer but has different content - the seemingly-obvious choice being va_toastrelid == 0. Unfortunately that would mean you need to copy the varatt_external (or whatever it would be called) to aligned storage to check what it is. Thats why I went the other way. How big a problem is that, though? I'd be a little reluctant to do it the way you propose because we might, at some point, want to try to reduce the size of toast pointers. If you have a tuple with many attributes, the size of the TOAST pointers themselves starts to add up. It would be nice to be able to have 8 byte or even 4 byte toast pointers to handle those situations. If we steal one or both of those lengths to mean the data is cached in memory somewhere then we can't use those lengths in a smaller on-disk representation, which would seem a shame. I agree. As I said above, having the type overlayed into the lenght was and is a bad idea, I just haven't found a better one thats compatible yet. Except inventing typlen=-3 aka toast2 or something. But even that wouldn't help getting rid of existing pg_upgraded tables. Besides being a maintenance nightmare. The only reasonable thing I can see us doing is renaming varattrib_1b_e.va_len_1be into va_type and redefine VARSIZE_1B_E into a switch that maps types into lengths. But I think I would put this off, except placing a comment somewhere, until its gets necessary. I guess I wonder how hard we think it would be to insert such a thing when it becomes necessary. How much stuff is there out there that cares about the fact that that length is a byte? But having said that, +1 on the general idea of getting something like this done. We really need a better infrastructure to avoid copying large values around repeatedly in memory - a gigabyte is a lot of data to be slinging around. Of course, you will not be surprised to hear that I think this is 9.4 material. Yes, obviously. But I need time to actually propose a working patch (I already found 2 bugs in what I had submitted), thats why I brought it up now. No point in wasting time if there's an oviously better idea around. Cool. -- 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] JSON Function Bike Shedding
2013/2/19 Petr Jelinek pjmo...@pjmodos.net: -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Robert Haas Sent: 19 February 2013 15:05 To: Merlin Moncure Cc: David E. Wheeler; PostgreSQL-development Hackers The argument for removing json_ prefix is that when function behaviors are unambiguously controlled by the arguments, decorating the function name to match the input argument is unnecessary verbosity. I've come to value greppability of source code pretty highly. I think that some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. Let me join the minority here, +1 me too +1 Pavel Regards Petr Jelinek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
2013/2/19 Robert Haas robertmh...@gmail.com: In the department of crazy ideas, what about having pg_dump NEVER refresh ANY materialized views? It's true that the job of pg_dump and pg_restore is to put the new database in the same state that the old database was in, but I think you could make a reasonable case that materialized views ought to be an exception. After all, even with all of this infrastructure, chances are pretty good that the new MV contents won't end up being the same as the old MV contents on the old server - because the old MVs could easily have been stale. So why not just get the restore over with as fast as possible, and then let the user refresh the MVs that they think need refreshing (perhaps after getting the portions of their system that don't rely on MVs up and running)? At the very least, I think we ought to have an option for this behavior. But the more I think about it, the more I think maybe it ought to be the default. +1 from me from a minimalist point of view. I think of a matview of the manually refreshed kind as “can contain stale contents (or be invalid) unless someone manually makes sure it is up to date (or valid)”. Making any matviews invalid by default upon restoring (itself being a manual action) would be consistent with that definition. Additionally, ISTM to be the least arbitrary (and hence most elegant) choice, and even more so in the context of matviews-depending-on-matviews. Spamming some more craziness: Another (more elaborate) suggestion could be: Store for each matview whether it is to be rebuilt upon restore or not. Using this setting would intuitively mean something like “I consider this matview being valid a precondition for considering the database state valid.” Setting this to true for a matview would only be allowed when any other matviews on which it depends also have this setting set to true. Just my €0.02 of course. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] indirect toast tuple support
On 2013-02-19 09:12:02 -0500, Robert Haas wrote: On Tue, Feb 19, 2013 at 9:00 AM, Andres Freund and...@2ndquadrant.com wrote: So the other way that we could do this is to use something that's the same size as a TOAST pointer but has different content - the seemingly-obvious choice being va_toastrelid == 0. Unfortunately that would mean you need to copy the varatt_external (or whatever it would be called) to aligned storage to check what it is. Thats why I went the other way. How big a problem is that, though? There are quite some places where we test the actual type of a Datum inside tuptoaster.c. Copying it to local storage everytime might actually be noticeable performancewise. Besides the ugliness of needing a local variable, copying the data and only testing afterwards... I'd be a little reluctant to do it the way you propose because we might, at some point, want to try to reduce the size of toast pointers. If you have a tuple with many attributes, the size of the TOAST pointers themselves starts to add up. It would be nice to be able to have 8 byte or even 4 byte toast pointers to handle those situations. If we steal one or both of those lengths to mean the data is cached in memory somewhere then we can't use those lengths in a smaller on-disk representation, which would seem a shame. I agree. As I said above, having the type overlayed into the lenght was and is a bad idea, I just haven't found a better one thats compatible yet. Except inventing typlen=-3 aka toast2 or something. But even that wouldn't help getting rid of existing pg_upgraded tables. Besides being a maintenance nightmare. The only reasonable thing I can see us doing is renaming varattrib_1b_e.va_len_1be into va_type and redefine VARSIZE_1B_E into a switch that maps types into lengths. But I think I would put this off, except placing a comment somewhere, until its gets necessary. I guess I wonder how hard we think it would be to insert such a thing when it becomes necessary. How much stuff is there out there that cares about the fact that that length is a byte? You mean whether we could store the length in 6 bytes and use two for the type? That should probably work as well. But I don't see much advantage in that given that all those sizes ought to be static. Redefining VARSIZE_1B_E as indicated above should be fairly easy, there aren't many callsites that touch stuff at such low level. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON Function Bike Shedding
On Tue, Feb 19, 2013 at 8:04 AM, Robert Haas robertmh...@gmail.com wrote: The argument for removing json_ prefix is that when function behaviors are unambiguously controlled by the arguments, decorating the function name to match the input argument is unnecessary verbosity. I've come to value greppability of source code pretty highly. Hm, good point. Counter argument: use better editing tools. Counter-counter argument: postgresql's fast moving boutique syntax is not well understood by any editor I'm aware of. The editor I use is Source insight, a $$$ windows tool, and I use it because it's basically a source code indexer with a full java and C parser. It can do SQL also, but it's limited to what you can do with regex for non fully parsmed languages so if I have the code: select get(j, '...') from foo; It doesn't know that j is json and as such I can't look for all instances of get() as pertains to json generally or the json field j Interesting aside: another language that is essentially immune to good tooling, javascript, is exploding in use -- even on the server side. Anyways, as to overloading in general, well, SQL is heavily overloaded. We don't have int_max, float_max, etc. and it would be usability reduction if we did. But that's not even the point; the driving philosophy of SQL is that your data structures (and types) are to be strongly decoupled from the manipulation you do -- this keeps the language very general. That philosophy, while not perfect, should be adhered to when possible. 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] Support for REINDEX CONCURRENTLY
On Thu, Feb 14, 2013 at 4:08 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Please find attached a new set of 3 patches for REINDEX CONCURRENTLY (v11). - 20130214_1_remove_reltoastidxid.patch - 20130214_2_reindex_concurrently_v11.patch - 20130214_3_reindex_concurrently_docs_v11.patch Patch 1 needs to be applied before patches 2 and 3. 20130214_1_remove_reltoastidxid.patch is the patch removing reltoastidxid (approach mentioned by Tom) to allow server to manipulate multiple indexes of toast relations. Catalog views, system functions and pg_upgrade have been updated in consequence by replacing reltoastidxid use by a join on pg_index/pg_class. All the functions of tuptoaster.c now use RelationGetIndexList to fetch the list of indexes on which depend a given toast relation. There are no warnings, regressions are passing (here only an update of rules.out and oidjoins has been necessary). 20130214_2_reindex_concurrently_v11.patch depends on patch 1. It includes the feature with all the fixes requested by Andres in his previous reviews. Regressions are passing and I haven't seen any warnings. in this patch concurrent rebuild of toast indexes is fully supported thanks to patch 1. The kludge used in previous version to change reltoastidxid when swapping indexes is not needed anymore, making swap code far cleaner. 20130214_3_reindex_concurrently_docs_v11.patch includes the documentation of REINDEX CONCURRENTLY. This might need some reshuffling with what is written for CREATE INDEX CONCURRENTLY. I am now pretty happy with the way implementation is done, so I think that the basic implementation architecture does not need to be changed. Andres, I think that only a single round of review would be necessary now before setting this patch as ready for committer. Thoughts? Comments, as well as reviews are welcome. When I compiled the HEAD with the patches, I got the following warnings. index.c:1273: warning: unused variable 'parentRel' execUtils.c:1199: warning: 'return' with no value, in function returning non-void When I ran REINDEX CONCURRENTLY for the same index from two different sessions, I got the deadlock. The error log is: ERROR: deadlock detected DETAIL: Process 37121 waits for ShareLock on virtual transaction 2/196; blocked by process 36413. Process 36413 waits for ShareUpdateExclusiveLock on relation 16457 of database 12293; blocked by process 37121. Process 37121: REINDEX TABLE CONCURRENTLY pgbench_accounts; Process 36413: REINDEX TABLE CONCURRENTLY pgbench_accounts; HINT: See server log for query details. STATEMENT: REINDEX TABLE CONCURRENTLY pgbench_accounts; And, after the REINDEX CONCURRENTLY that survived the deadlock finished, I found that new index with another name was created. It was NOT marked as INVALID. Are these behaviors intentional? =# \di pgbench_accounts* List of relations Schema | Name| Type | Owner | Table +---+---+--+-- public | pgbench_accounts_pkey | index | postgres | pgbench_accounts public | pgbench_accounts_pkey_cct | index | postgres | pgbench_accounts (2 rows) Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2.3 crashes during archive recovery
On Mon, Feb 18, 2013 at 8:27 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: backupStartPoint is set, which signals recovery to wait for an end-of-backup record, until the system is considered consistent. If the backup is taken from a hot standby, backupEndPoint is set, instead of inserting an end-of-backup record. Thank you for explaining this, I can see how it works now. I'll see if I can document this better so others won't have to go through as much effort. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
On 2/19/13 8:54 AM, Robert Haas wrote: In the department of crazy ideas, what about having pg_dump NEVER refresh ANY materialized views? It might be useful to have an option for this, but I don't think it should be the default. The default should be that the new database is ready to go. Then again, when would you ever actually use that option? This might be different if there were a command to refresh all materialized views, because you don't want to have to go around and type separate commands 47 times after a restore. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
Dne 19.02.2013 11:27, Tom Lane napsal: Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. Hmmm, interesting. A single Windows machine, while the other Windows machines seem to work fine (although some of them were not built for a few weeks). Could be random chance --- we've seen the same failure before, eg http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2012-11-25%2006%3A00%3A00 Maybe. But why does random chance happens to me only with regression tests and not lottery, like to normal people? Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JSON Function Bike Shedding
On Feb 19, 2013, at 6:11 AM, Petr Jelinek pjmo...@pjmodos.net wrote: some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. Let me join the minority here, +1 Well, that's why I called them get_json() and get_text(). Basically, I don't mind that the function name says something about the return type. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Call for Google Summer of Code mentors, admins
On Thu, Feb 14, 2013 at 4:02 PM, Josh Berkus j...@agliodbs.com wrote: [...] - Please suggest project ideas for GSOC - Students seeing this -- please speak up if you have projects you plan to submit. I would like to propose implement a way to track creation times to database objects. This was discussed before in this thread [1]. This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. Regards, [1] http://www.postgresql.org/message-id/CAFcNs+qMGbLmeUOnjmbna_K7=up817bpw9qxhbctgnscpkv...@mail.gmail.com -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] JSON Function Bike Shedding
I've come to value greppability of source code pretty highly. I think that some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. What about extract()? That's consistent with the function we already use for timestamps and intervals, and is more clear than get(). On the other hand, to_string() seems like a GREAT name for an overloaded function. You know that it takes some other type as an argument, possibly several other types, and will always output a string. -- 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] JSON Function Bike Shedding
2013/2/19 Josh Berkus j...@agliodbs.com: I've come to value greppability of source code pretty highly. I think that some of the points you raise are valid, but in my (minority) opinion overloading creates more problems than it solves. You're not going to convince me that get() is *ever* a good name for a function - you might as well call it thing() or foo() for all the useful information that name conveys. What about extract()? That's consistent with the function we already use for timestamps and intervals, and is more clear than get(). extract is not usual function, it is supported by parser, and in this time nobody knows datatypes, so result can be some ugly error messages Regards Pavel On the other hand, to_string() seems like a GREAT name for an overloaded function. You know that it takes some other type as an argument, possibly several other types, and will always output a string. -- 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] posix_fadvise missing in the walsender
On Mon, Feb 18, 2013 at 2:16 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 17.02.2013 14:55, Joachim Wieland wrote: In access/transam/xlog.c we give the OS buffer caching a hint that we won't need a WAL file any time soon with posix_fadvise(openLogFile, 0, 0, POSIX_FADV_DONTNEED); before closing the WAL file, but only if we don't have walsenders. That's reasonable because the walsender will reopen that same file shortly after. However the walsender doesn't call posix_fadvise once it's done with the file and I'm proposing to add this to walsender.c for consistency as well. Since there could be multiple walsenders, only the slowest one should call this function. Finding out the slowest walsender can be done by inspecting the shared memory and looking at the sentPtr of each walsender. I doubt it's worth it, the OS cache generally does a reasonable job at deciding what to keep. In the non-walsender case, it's pretty clear that we don't need the WAL file anymore, but if we need to work any harder than a one-line posix_fadvise call, meh. If that's the case, why have the advisory call at all? The OS is being lied too (in some cases)... 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] Call for Google Summer of Code mentors, admins
This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. As a mentor or as a student? -- 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] Call for Google Summer of Code mentors, admins
On Tue, Feb 19, 2013 at 5:38 PM, Josh Berkus j...@agliodbs.com wrote: This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. As a mentor or as a student? As a student. Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Call for Google Summer of Code mentors, admins
On 19.02.2013 20:07, Fabrízio de Royes Mello wrote: I would like to propose implement a way to track creation times to database objects. This was discussed before in this thread [1]. This was discussed before in this thread [1] but we don't reach a consensus of what we'll do, so I propose we discuss any more about it and I can implement it in GSOC2013, if my proposal will be accepted. I don't think that's a good GSoC project. There's no consensus on what to do, if anything, so 95% of the work is going to arguing over what we want, and 5% coding. I'd suggest finding something more well-defined. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
On 19.2.2013 11:27, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: Dne 19.02.2013 05:46, Alvaro Herrera napsal: Mastodon failed: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2013-02-19%2000%3A00%3A01 probably worth investigating a bit; we might have broken something. Hmmm, interesting. A single Windows machine, while the other Windows machines seem to work fine (although some of them were not built for a few weeks). Could be random chance --- we've seen the same failure before, eg http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mastodondt=2012-11-25%2006%3A00%3A00 regards, tom lane I'm looking at that test, and I'm not really sure about a few details. First, this function seems pretty useless to me: === create function wait_for_stats() returns void as $$ declare start_time timestamptz := clock_timestamp(); updated bool; begin -- we don't want to wait forever; loop will exit after 30 seconds for i in 1 .. 300 loop -- check to see if indexscan has been sensed SELECT (st.idx_scan = pr.idx_scan + 1) INTO updated FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; exit when updated; -- wait a little perform pg_sleep(0.1); -- reset stats snapshot so we can test again perform pg_stat_clear_snapshot(); end loop; -- report time waited in postmaster log (where it won't change test output) raise log 'wait_for_stats delayed % seconds', extract(epoch from clock_timestamp() - start_time); end $$ language plpgsql; === AFAIK the stats remain the same within a transaction, and as a function runs within a transaction, it will either get new data on the first iteration, or it will run all 300 of them. I've checked several buildfarm members and I'm yet to see a single duration between 12ms and 30sec. So IMHO we can replace the function call with pg_sleep(30) and we'll get about the same effect. But this obviously does not answer the question why it failed, although on both occasions there's this log message: [50b1b7fa.0568:14] LOG: wait_for_stats delayed 34.75 seconds which essentialy means the stats were not updated before the call to wait_for_stats(). Anyway, there are these two failing queries: === -- check effects SELECT st.seq_scan = pr.seq_scan + 1, st.seq_tup_read = pr.seq_tup_read + cl.reltuples, st.idx_scan = pr.idx_scan + 1, st.idx_tup_fetch = pr.idx_tup_fetch + 1 FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? | ?column? | ?column? --+--+--+-- t| t| t| t (1 row) SELECT st.heap_blks_read + st.heap_blks_hit = pr.heap_blks + cl.relpages, st.idx_blks_read + st.idx_blks_hit = pr.idx_blks + 1 FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr WHERE st.relname='tenk2' AND cl.relname='tenk2'; ?column? | ?column? --+-- t| t (1 row) === The first one returns just falses, the second one retuns either (t,f) or (f,f) - for the two failures posted by Alvaro and TL earlier today. I'm really wondering how that could happen. The only thing that I can think of is some strange timing issue, causing lost requests to write the stats or maybe some of the stats updates. Hmmm, IIRC the stats are sent over UDP - couldn't that be related? Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch to make pgindent work cleanly
Please find attached the patch for some cleanup and fix bit rot in pgindent
script.
There were a few problems with the script.
.) It failed to use the $ENV{PGENTAB} even if it was set.
.) The file it tries to download from Postgres' ftp site is no longer
present.
ftp://ftp.postgresql.org/pub/dev/indent.netbsd.patched.tgz
.) The tar command extracted the above-mentioned file to a child directory,
but did not descend into it before running make on it.
I think it expected a tarbomb, but clearly the current .tgz file on ftp
site is not a tarbomb.
I don't like the fact that it dies with a message fetching xyz rather
than saying Could not fetch xyz, but this patch does not address that
since it doesn't really affect the output when script does work.
With this patch in place I could very easily run it on any arbitrary file,
which seemed like a black-magic before the patch.
src/tools/pgindent/pgindent --build your file path here
--
Gurjeet Singh
http://gurjeet.singh.im/
EnterprsieDB Inc.
pgindent.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] Materialized views WIP patch
On Sat, February 16, 2013 02:01, Kevin Grittner wrote:
matview-v4.patch.gz
Hi,
I was wondering if material views should not go into information_schema. I was
thinking either
.views or .tables. Have you considered this?
I ask because as far as I can see querying for mv's has to go like this:
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('m','')
and n.nspname = 'myschema'
which seems rather ugly.
Also, some documentation typos: please see attached.
Thanks,
Erik Rijkers
matviewtypos.diff
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] Materialized views WIP patch
On 02/19/2013 02:09 PM, Erik Rijkers wrote:
On Sat, February 16, 2013 02:01, Kevin Grittner wrote:
matview-v4.patch.gz
Hi,
I was wondering if material views should not go into information_schema. I
was thinking either
.views or .tables. Have you considered this?
I ask because as far as I can see querying for mv's has to go like this:
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('m','')
and n.nspname = 'myschema'
Well, I'm not sure about information_schema, but we'll definitely want a
pg_matviews system view. Also a \dM. That could wait until 9.4, 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] Materialized views WIP patch
On Tue, Feb 19, 2013 at 11:09:13PM +0100, Erik Rijkers wrote: On Sat, February 16, 2013 02:01, Kevin Grittner wrote: matview-v4.patch.gz Hi, I was wondering if material views should not go into information_schema. I was thinking either .views or .tables. Have you considered this? I'm guessing it'd be .views if anything. Haven't been able to decipher from section 11 of the standard (Schemata) whether the standard has anything to say on the matter. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
Erik Rijkers e...@xs4all.nl wrote: I was wondering if material views should not go into information_schema. I was thinking either .views or .tables. Have you considered this? I had not considered this to be a good idea because information_schema is defined by the standard, and materialized views are an extension to the standard. Someone using these views to identify either tables or views might make a bad choice based on this. I'm open to arguments for inclusion, if you think it would not violate the standard. Which would be safe? Also, some documentation typos: please see attached. Will apply. Thanks. -- Kevin Grittner 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: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
Tomas Vondra wrote: AFAIK the stats remain the same within a transaction, and as a function runs within a transaction, it will either get new data on the first iteration, or it will run all 300 of them. I've checked several buildfarm members and I'm yet to see a single duration between 12ms and 30sec. No, there's a call to pg_stat_clear_snapshot() that takes care of that. I'm really wondering how that could happen. The only thing that I can think of is some strange timing issue, causing lost requests to write the stats or maybe some of the stats updates. Hmmm, IIRC the stats are sent over UDP - couldn't that be related? yes, UDP packet drops can certainly happen. This is considered a feature (do not cause backends to block when the network socket to stat collector is swamped; it's better to lose some stat messages instead). -- Álvaro Herrerahttp://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] Materialized views WIP patch
Josh Berkus j...@agliodbs.com wrote: Well, I'm not sure about information_schema, but we'll definitely want a pg_matviews system view. That could wait until 9.4, though. That I could probably do. Do you think they should have a separate pg_stat_user_matviews table, etc., or do you think it would be better to include them in with tables there? Also a \dM. I already added it as \dm in the current patch. Does that conflict with something else that's pending? -- Kevin Grittner 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: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
On 19.2.2013 23:31, Alvaro Herrera wrote: Tomas Vondra wrote: AFAIK the stats remain the same within a transaction, and as a function runs within a transaction, it will either get new data on the first iteration, or it will run all 300 of them. I've checked several buildfarm members and I'm yet to see a single duration between 12ms and 30sec. No, there's a call to pg_stat_clear_snapshot() that takes care of that. Aha! Missed that for some reason. Thanks. I'm really wondering how that could happen. The only thing that I can think of is some strange timing issue, causing lost requests to write the stats or maybe some of the stats updates. Hmmm, IIRC the stats are sent over UDP - couldn't that be related? yes, UDP packet drops can certainly happen. This is considered a feature (do not cause backends to block when the network socket to stat collector is swamped; it's better to lose some stat messages instead). Is there anything we could add to the test to identify this? Something that either shows stats were sent and stats arrived (maybe in the log only), or that some UPD packets were dropped? Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
That I could probably do. Do you think they should have a separate pg_stat_user_matviews table, etc., or do you think it would be better to include them in with tables there? Well, ideally pg_matviews would have matview definitions, and pg_stat_matviews would have stats on matview usage and rows. But see what you can get done; I imagine we'll overhaul it for 9.4 anyway once we've had a chance to use the feature. Also a \dM. I already added it as \dm in the current patch. Does that conflict with something else that's pending? Oh, no, I thought \dm was *already* in use, but apparently not. -- 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] Materialized views WIP patch
Kevin Grittner kgri...@ymail.com There was one minor syntax issue not addressed by Noah, nor much discussed in general that I didn't want to just unilaterally choose; but given that nobody seems to care that much I will put forward a proposal and do it that way tomorrow if nobody objects. Before this patch tables were the only things subject to truncation, but now materialized views can also be truncated. So far we have been treating TABLE as a noise word in the truncate command. I assume we still want to allow tables to be truncated with or without the word. The question is what to do about materialized views, and wheter both can be specified on a single TRUNCATE statement. I propose that we allow TABLE or MATERIALIZED VIEW to be specified, or that part of the statement to be left out. I propose that either type of object be allowed unless one or the other is specified and the object to be truncated is not of that kind. So you could mix both kinds on one statement, so long as you didn't specify either kind. When I went to do this, I hit a shift/reduce conflict, because with TABLE being optional it couldn't tell whether: TRUNCATE MATERIALIZED VIEW x, y, z; ... was looking for five relations or three. That goes away with MATERIALIZED escalated to TYPE_FUNC_NAME_KEYWORD. Is that OK? -- Kevin Grittner 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] posix_fadvise missing in the walsender
On 19 February 2013 20:19, Merlin Moncure mmonc...@gmail.com wrote: In access/transam/xlog.c we give the OS buffer caching a hint that we won't need a WAL file any time soon with posix_fadvise(openLogFile, 0, 0, POSIX_FADV_DONTNEED); If that's the case, why have the advisory call at all? The OS is being lied too (in some cases)... I agree with Merlin and Joachim - if we have the call in one place, we should have it in both. This means that if a standby fails it will likely have to re-read these files from disk. Cool, we can live with that. Patch please, -- 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] Materialized views WIP patch
Josh Berkus j...@agliodbs.com wrote:
That I could probably do. Do you think they should have a separate
pg_stat_user_matviews table, etc., or do you think it would be
better to include them in with tables there?
Well, ideally pg_matviews would have matview definitions, and
pg_stat_matviews would have stats on matview usage and rows. But see
what you can get done; I imagine we'll overhaul it for 9.4 anyway once
we've had a chance to use the feature.
I agree on pg_matviews, but after looking over the existing views
and thinking about what I would use them for as a DBA, I'm inclined
to fold the backing tables for MVs into the _stat_ and _statio_
views -- especially since we already include the backing tables and
indexes for TOAST. There is a precident for including
implementation details at that level. The only difference from
TOAST, is that I include the heap and indexes for MVs in the _user_
views. I'm attaching the patch for just the system_views.sql file
for discussion before I go write docs for this part.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Companydiff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c479c23..711a2ba 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -94,6 +94,18 @@ CREATE VIEW pg_tables AS
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE C.relkind = 'r';
+CREATE VIEW pg_matviews AS
+SELECT
+N.nspname AS schemaname,
+C.relname AS matviewname,
+pg_get_userbyid(C.relowner) AS matviewowner,
+T.spcname AS tablespace,
+C.relhasindex AS hasindexes,
+pg_get_viewdef(C.oid) AS definition
+FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+ LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
+WHERE C.relkind = 'm';
+
CREATE VIEW pg_indexes AS
SELECT
N.nspname AS schemaname,
@@ -402,7 +414,7 @@ CREATE VIEW pg_stat_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_xact_all_tables AS
@@ -422,7 +434,7 @@ CREATE VIEW pg_stat_xact_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_sys_tables AS
@@ -467,7 +479,7 @@ CREATE VIEW pg_statio_all_tables AS
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
pg_class X ON T.reltoastidxid = X.oid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
CREATE VIEW pg_statio_sys_tables AS
@@ -494,7 +506,7 @@ CREATE VIEW pg_stat_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't');
+WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_stat_sys_indexes AS
SELECT * FROM pg_stat_all_indexes
@@ -520,7 +532,7 @@ CREATE VIEW pg_statio_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't');
+WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_statio_sys_indexes AS
SELECT * FROM pg_statio_all_indexes
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialized views WIP patch
Kevin Grittner kgri...@ymail.com wrote:
I'm attaching the patch for just the system_views.sql file
for discussion before I go write docs for this part.
Meh. If I'm gonna have pg_matviews I might as well include an
isscannable column. v2 attached.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Companydiff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index c479c23..ccf0bd8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -94,6 +94,19 @@ CREATE VIEW pg_tables AS
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE C.relkind = 'r';
+CREATE VIEW pg_matviews AS
+SELECT
+N.nspname AS schemaname,
+C.relname AS matviewname,
+pg_get_userbyid(C.relowner) AS matviewowner,
+T.spcname AS tablespace,
+C.relhasindex AS hasindexes,
+pg_relation_is_scannable(C.oid) AS isscannable,
+pg_get_viewdef(C.oid) AS definition
+FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
+ LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
+WHERE C.relkind = 'm';
+
CREATE VIEW pg_indexes AS
SELECT
N.nspname AS schemaname,
@@ -402,7 +415,7 @@ CREATE VIEW pg_stat_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_xact_all_tables AS
@@ -422,7 +435,7 @@ CREATE VIEW pg_stat_xact_all_tables AS
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname;
CREATE VIEW pg_stat_sys_tables AS
@@ -467,7 +480,7 @@ CREATE VIEW pg_statio_all_tables AS
pg_class T ON C.reltoastrelid = T.oid LEFT JOIN
pg_class X ON T.reltoastidxid = X.oid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't')
+WHERE C.relkind IN ('r', 't', 'm')
GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;
CREATE VIEW pg_statio_sys_tables AS
@@ -494,7 +507,7 @@ CREATE VIEW pg_stat_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't');
+WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_stat_sys_indexes AS
SELECT * FROM pg_stat_all_indexes
@@ -520,7 +533,7 @@ CREATE VIEW pg_statio_all_indexes AS
pg_index X ON C.oid = X.indrelid JOIN
pg_class I ON I.oid = X.indexrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
-WHERE C.relkind IN ('r', 't');
+WHERE C.relkind IN ('r', 't', 'm');
CREATE VIEW pg_statio_sys_indexes AS
SELECT * FROM pg_statio_all_indexes
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] One-line comment to improve understanding of VARSIZE_ANY_EXHDR macro
Hopefully I am not wrong. +/* Size of a varlena data, excluding header */ #define VARSIZE_ANY_EXHDR(PTR) \ -- Gurjeet Singh http://gurjeet.singh.im/ EnterprsieDB Inc. exhdr_comment.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] Materialized views WIP patch
On 02/19/2013 03:41 PM, Kevin Grittner wrote: Kevin Grittner kgri...@ymail.com wrote: I'm attaching the patch for just the system_views.sql file for discussion before I go write docs for this part. Meh. If I'm gonna have pg_matviews I might as well include an isscannable column. v2 attached. pg_get_viewdef() will work on Matviews? Great. -- 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] streaming header too small
On Mon, Jan 9, 2012 at 9:11 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Jan 9, 2012 at 12:00, Magnus Hagander mag...@hagander.net wrote: On Mon, Jan 9, 2012 at 11:09, Magnus Hagander mag...@hagander.net wrote: On Mon, Jan 9, 2012 at 07:34, Jaime Casanova ja...@2ndquadrant.com wrote: Hi, I was trying pg_basebackup on head, i used this command: postgres@jaime:/usr/local/pgsql/9.2$ bin/pg_basebackup -D $PWD/data2 -x stream -P -p 54392 i got this error 19093/19093 kB (100%), 1/1 tablespace pg_basebackup: streaming header too small: 17 pg_basebackup: child process exited with error 1 now, this streaming header size is defined in src/bin/pg_basebackup/receivelog.c as #define STREAMING_HEADER_SIZE (1+8+8+8), so WTF is this? what are these numbers? shouldn't be at least a comment explaining those? more important it's seems obvious something broke that, unless Those numbers are the size of WalDataMessageHeader - a struct which is not available in the frontend, or at least wasn't at the time. i misunderstood something which is completely possible, and that the way is do it it will broke again in the future if the header change Without looking at the details, I'm pretty sure it's the keepalive message patch (64233902d22ba42846397cb7551894217522fad4).That one does introduce a new message that's exactly that size. pg_basebackup assumes the only kind of messages that can arrive are the data messages, and this is no longer true. But if you check the code for pg_basebackup, you'll see it checks the size of the message *before* it checks the type of the message, which is why you get a misleading error. I'll dig into the details later - but you could try backing out that patch to confirm if that's the problem. Confirmed that is it, and attached are two patches to fix it. The first one I intend to backport to 9.1, since it just fixes the error message. The other one is for 9.2. I'll also look at a better way to get that structure size. comments? Patch applied. Realized there is no need to backpatch, because this code didn't even exist in 9.1. The streaming mode of pg_basebackup (which is the only affected one) didn't exist then... So, I just ran into a similar issue backing up a 9.2.1 server using pg_basebackup version 9.2.3: pg_basebackup: starting background WAL receiver pg_basebackup: streaming header too small: 25 I've had it happen two times in a row. I'm going to try again... But -- what would be helpful here? I can recompile pg_basebackup with more debugging... -selena -- http://chesnok.com
Re: [HACKERS] [PATCH 0/3] Work around icc miscompilation
On Thu, Jan 24, 2013 at 11:40:41AM -0500, Xi Wang wrote: On 1/24/13 10:48 AM, Tom Lane wrote: The fundamental problem here is that the compiler, unless told otherwise by a compilation switch, believes it is entitled to assume that no integer overflow will happen anywhere in the program. Therefore, any error check that is looking for overflow *should* get optimized away. The only reason the compiler would fail to do that is if its optimizer isn't quite smart enough to prove that the code is testing for an overflow condition. So what you are proposing here is merely the next move in an arms race with the compiler writers, and it will surely break again in a future generation of compilers. Or even if these particular trouble spots don't break, something else will. The only reliable solution is to not let the compiler make that type of assumption. What I am proposing here is the opposite: _not_ to enter an arm race with the compiler writers. Instead, make the code conform to the C standard, something both sides can agree on. Particularly, avoid using (signed) overflowed results to detect overflows, which the C standard clearly specifies as undefined behavior and many compilers are actively exploiting. We could use either unsigned overflows (which is well defined) or precondition testing (like `x INT_MAX - y' in the patches). So I think we should just reject all of these, and instead fix configure to make sure it turns on icc's equivalent of -fwrapv. While I agree it's better to turn on icc's -fno-strict-overflow as a workaround, the fundamental problem here is that we are _not_ programming in the C language. Rather, we are programming in some C-with-signed-wrapraround dialect. I could not have said it better. All other things being similar, I'd rather have PostgreSQL be written in C, not C-with-signed-wrapraround. The latter has been a second class citizen for over 20 years, and that situation won't be improving. However, compiler options selecting it are common and decently-maintained. Changes along these lines would become much more interesting if PostgreSQL has a live bug on a modern compiler despite the use of available options comparable to -fwrapv. When, if ever, to stop using -fwrapv (and use -ftrapv under --enable-cassert) is another question. GCC 4.7 reports 999 warnings at -fstrict-overflow -Wstrict-overflow=5. That doesn't mean 999 bugs to fix before we could remove -fwrapv, but it does mean 999 places where the compiler will start generating different code. That has a high chance of breaking something not covered by the regression tests, so I'd hope to see a notable upside, perhaps a benchmark improvement. It would also be instructive to know how much code actually needs to change. Fixing 20 code sites in exchange for standard-conformance and an X% performance improvement is a different proposition from fixing 200 code sites for the same benefit. If we do start off in this direction at any scale, I suggest defining macros like INT_MULT_OVERFLOWS(a, b) to wrap the checks. Then these changes would at least make the code clearer, not less so. Thanks, nm -- Noah Misch 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] Comment typo
Sorry, I found one more typo. Attached is a patch. Thanks, Best regards, Etsuro Fujita -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] Sent: Friday, February 08, 2013 7:47 PM To: Etsuro Fujita Cc: PostgreSQL-development Subject: Re: [HACKERS] Comment typo On Fri, Feb 8, 2013 at 6:49 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: I found a comment typo. Please find attached a patch. Applied, thanks. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ comment_typo_20130220.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] bugfix: --echo-hidden is not supported by \sf statements
On Wed, Jan 23, 2013 at 12:06 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/1/14 Tom Lane t...@sss.pgh.pa.us: Well, fine, but then it should fix both of them and remove minimal_error_message altogether. I would however suggest eyeballing what happens when you try \ef nosuchfunction (with or without -E). I'm pretty sure that the reason for the special error handling in these functions is that the default error reporting was unpleasant for this use-case. so I rewrote patch still is simple On the end I didn't use PSQLexec - just write hidden queries directly from related functions - it is less invasive Sorry for the delay, but I finally had a chance to review this patch. I think the patch does a good job of bringing the behavior of \sf and \ef in-line with the other meta-commands as far as --echo-hidden is concerned. About the code changes: The bulk of the code change comes from factoring TraceQuery() out of PSQLexec(), so that \ef and \sf may make use of this query-printing without going through PSQLexec(). And not using PSQLexec() lets us avoid a somewhat uglier error message like: ERROR: function nonexistent does not exist LINE 1: SELECT 'nonexistent'::pg_catalog.regproc::pg_catalog.oid Tom suggested removing minimal_error_message() entirely, which would be nice if possible. It is a shame that \sf nonexistent produces a scary-looking ERROR: message (and would cause any in-progress transaction to need a rollback) while the other informational metacommands do not. Actually, the other metacommands are not entirely consistent with each other; compare \dt nonexistent and \df nonexistent. It would be nice if the case of a matching function not found by \sf or \ef could be handled in the same fashion as: # \d nonexistent Did not find any relation named nonexistent. though I realize that's not trivial due to the implementation of lookup_function_oid(). At any rate, this nitpicking about the error handling in the case that the function is not found is quibbling about behavior unchanged by the patch. I don't have any complaints about the patch itself, so I think it can be applied as-is, and we can attack the error handling issue separately. Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] posix_fadvise missing in the walsender
On Tue, Feb 19, 2013 at 5:48 PM, Simon Riggs si...@2ndquadrant.com wrote: In access/transam/xlog.c we give the OS buffer caching a hint that we won't need a WAL file any time soon with posix_fadvise(openLogFile, 0, 0, POSIX_FADV_DONTNEED); I agree with Merlin and Joachim - if we have the call in one place, we should have it in both. You could argue that if it's considered beneficial in the case with no walsender, then you should definitely have it if there are walsenders around: The walsenders reopen and read those files which gives the OS reason to believe that other processes might do the same in the near future and hence that it should not evict those pages too early. Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [PATCH 2/5] Make relpathbackend return a statically result instead of palloc()'ing it
On Tue, Jan 08, 2013 at 08:02:16PM -0500, Robert Haas wrote: On Tue, Jan 8, 2013 at 7:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I was thinking more about a sprintf()-type function that only understands a handful of escapes, but adds the additional and novel escapes %I (quote as identifier) and %L (quote as literal). I think that would allow a great deal of code simplification, and it'd be more efficient, too. Seems like a great idea. Are you offering to code it? Not imminently. Note that this wouldn't entirely fix the fmtId problem, as not all the uses of fmtId are directly in sprintf calls. Still, it might get rid of most of the places where it'd be painful to avoid a memory leak with a strdup'ing version of fmtId. Yeah, I didn't think about that. Might be worth a look to see how comprehensively it would solve the problem. But I'll have to leave that for another day. As a cheap solution addressing 95+% of the trouble, how about giving fmtId() a ring of, say, eight static buffers to cycle through? That satisfies code merely wishing to pass multiple fmtId()'d values to one appendPQExpBuffer(). -- Noah Misch 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: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system
On Mon, Feb 18, 2013 at 7:50 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: So here's v11. I intend to commit this shortly. (I wanted to get it out before lunch, but I introduced a silly bug that took me a bit to fix.) On Windows with Mingw I get this: pgstat.c:4389:8: warning: variable 'found' set but not used [-Wunused-but-set-variable] I don't get that on Linux, but I bet that is just the gcc version (4.6.2 vs 4.4.6) rather than the OS. It looks like it is just a useless variable, rather than any possible cause of the Windows make check failure (which I can't reproduce). Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.2.3 crashes during archive recovery
Hello, I looked this from another point of view.
I consider the current discussion to be based on how to predict
the last consistency point. But there is another aspect of this
issue.
I tried to postpone smgrtruncate after the next checkpoint. This
is similar to what hotstandby feedback does to vacuum. It seems
to be working fine but I warry that it might also bloats the
table. I haven't found the way to postpone only objective
smgrtruncate.
The patch below is a immediate verification patch for this
solution.
- CreateCheckPoint records the oldest xmin at that point. Let's
call it 'checkpoint xmin'.
- vacuum skips the modification by the transactions at the same
time or after the checkpoint xmin.
What do you think of this?
--
Kyotaro Horiguchi
NTT Open Source Software Center
=
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index 479c14d..a274393 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -493,6 +493,7 @@ typedef struct XLogCtlData
XLogRecPtr lastFpwDisableRecPtr;
slock_t info_lck; /* locks shared variables shown
above */
+ TransactionId chkptxactid;
} XLogCtlData;
static XLogCtlData *XLogCtl = NULL;
@@ -676,6 +677,11 @@ static bool read_backup_label(XLogRecPtr *checkPointLoc,
static void rm_redo_error_callback(void *arg);
static int get_sync_bit(int method);
+TransactionId
+XLogGetChkptTrId()
+{
+ return XLogCtl-chkptxactid;
+}
/*
* Insert an XLOG record having the specified RMID and info bytes,
@@ -3875,7 +3881,7 @@ XLOGShmemInit(void)
SpinLockInit(XLogCtl-info_lck);
SpinLockInit(XLogCtl-ulsn_lck);
InitSharedLatch(XLogCtl-recoveryWakeupLatch);
-
+ XLogCtl-chkptxactid = InvalidTransactionId;
/*
* If we are not in bootstrap mode, pg_control should already exist.
Read
* and validate it immediately (see comments in ReadControlFile() for
the
@@ -6700,6 +6706,8 @@ CreateCheckPoint(int flags)
else
checkPoint.oldestActiveXid = InvalidTransactionId;
+ XLogCtl-chkptxactid = GetOldestXmin(true, true);
+
/*
* We must hold WALInsertLock while examining insert state to determine
* the checkpoint REDO pointer.
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 4800b43..79205a0 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -374,6 +374,7 @@ get_rel_oids(Oid relid, const RangeVar *vacrel)
/*
* vacuum_set_xid_limits() -- compute oldest-Xmin and freeze cutoff points
*/
+extern TransactionId XLogGetChkptTrId(void);
void
vacuum_set_xid_limits(int freeze_min_age,
int freeze_table_age,
@@ -397,6 +398,11 @@ vacuum_set_xid_limits(int freeze_min_age,
* always an independent transaction.
*/
*oldestXmin = GetOldestXmin(sharedRel, true);
+ {
+ TransactionId chkpttrid = XLogGetChkptTrId();
+ if (chkpttrid *oldestXmin)
+ *oldestXmin = chkpttrid;
+ }
Assert(TransactionIdIsNormal(*oldestXmin));
==
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Identity projection
I have updated the patch as per comments from Tom and Heikki. If you can verify it, then IMO it can be marked as 'Ready For Committer' Would you please do that? Done. Thank you. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
