[HACKERS] Comfortably check BackendPID with psql
Hi, hackers! This is a so tiny patch but I think it is very useful for hackers and DBAs. When we debug with psql, we frequently use SELECT pg_backend_pid();. This can change the input of the 24 characters to the only 4 characters! Image. -- naoya=# \bid Backend Process ID pid -- 1716 (1 row) --- How do you like it? Regards, Naoya --- Naoya Anzai Engineering Department NEC Solution Inovetors, Ltd. E-Mail: nao-an...@xc.jp.nec.com --- psql_show_backend_pid.patch Description: psql_show_backend_pid.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comfortably check BackendPID with psql
On 6/11/15 11:41 AM, Naoya Anzai wrote: This can change the input of the 24 characters to the only 4 characters! Image. -- naoya=# \bid Backend Process ID pid -- 1716 (1 row) --- How do you like it? Seems easier to set this in .psqlrc: \set bid 'select pg_backend_pid();' and then: =# :bid pg_backend_pid 84430 (1 row) No patches or concerns for psql version necessary. .m -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comfortably check BackendPID with psql
Hi, Andres, Marko Seems easier to set this in .psqlrc: oops! I've never noticed.. Thank you for your comment. Regards, Naoya --- Naoya Anzai Engineering Department NEC Solution Inovetors, Ltd. E-Mail: nao-an...@xc.jp.nec.com --- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewind failure by file deletion in source server
On Thu, Jun 11, 2015 at 2:14 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Thu, Jun 11, 2015 at 1:51 AM, Fujii Masao masao.fu...@gmail.com wrote: Shouldn't pg_rewind ignore that failure of operation? If the file is not found in source server, the file doesn't need to be copied to destination server obviously. So ISTM that pg_rewind safely can skip copying that file. Thought? I think that you should fail. Let's imagine that the master to be rewound has removed a relation file before being stopped cleanly after its standby has been promoted that was here at the last checkpoint before forking, and that the standby still has the relation file after promotion. You should be able to copy it to be able to replay WAL on it. If the standby has removed a file in the file map after taking the file map, I guess that the best thing to do is fail because the file that should be here for the rewound node cannot be fetched. In this case, why do you think that the file should exist in the old master? Even if it doesn't exist, ISTM that the old master can safely replay the WAL records related to the file when it restarts. So what's the problem if the file doesn't exist in the old master? Documentation should be made clearer about that with a better error message... I'm wondering how we can recover (or rewind again) the old master from that error. This also would need to be documented if we decide not to fix any code regarding this problem... 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
[HACKERS] Entities created in one query not available in another in extended protocol
In Npgsql, the .NET driver for PostgreSQL, we've switched from simple to extended protocol and have received a user complaint. It appears that when we send two messages in an extended protocol (so two Parse/Bind/Execute followed by a single Sync), where the first one creates some entity (function, table), and the second one can't query that entity (not found). This isn't terribly important but does seem a bit odd, I wanted to make sure you're aware of this. Thanks, Shay
Re: [HACKERS] Comfortably check BackendPID with psql
Hi, On 2015-06-11 09:41:17 +, Naoya Anzai wrote: This is a so tiny patch but I think it is very useful for hackers and DBAs. When we debug with psql, we frequently use SELECT pg_backend_pid();. This can change the input of the 24 characters to the only 4 characters! Not a big fan of that abbreviation itself. What I'd wondered about instead - and actually had patched into my psql at some point - is adding an appropriate escape to psql's PROMPT. I think that'd serve your purpose as well? Regards, 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] Entities created in one query not available in another in extended protocol
On 11 June 2015 at 11:20, Shay Rojansky r...@roji.org wrote: In Npgsql, the .NET driver for PostgreSQL, we've switched from simple to extended protocol and have received a user complaint. It appears that when we send two messages in an extended protocol (so two Parse/Bind/Execute followed by a single Sync), where the first one creates some entity (function, table), and the second one can't query that entity (not found). This isn't terribly important but does seem a bit odd, I wanted to make sure you're aware of this. Sounds somewhat unlikely, but thank you for the report. Can we see a test case? Most commonly in such cases the first request failed and error messages weren't checked before running second message. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
On Jun 11, 2015 7:38 AM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: On 2015-06-11 PM 02:20, Abhijit Menon-Sen wrote: At 2015-06-10 13:22:27 -0400, robertmh...@gmail.com wrote: (1) include pg_log in pg_basebackup as we do currently (2) exclude it (3) add a switch controlling whether or not it gets excluded I can live with (3), but I bet most people want (2). Thanks for spelling out the options. I strongly prefer (2), but I could live with (3) if it were done as a GUC setting. (And if that's what we decide to do, I'm willing to write up the patch.) Whether or not it's a good idea to let one's logfiles grow to 8GB, the fact that doing so breaks base backups means that being able to exclude pg_log *somehow* is more of a necessity than personal preference. On the other hand, I don't like the idea of doing (3) by adding command line arguments to pg_basebackup and adding a new option to the command. I don't think that level of flexibility is justified; it would also make it easier to end up with a broken base backup (by inadvertently excluding more than you meant to). Maybe a combination of (2) and part of (3). In absence of any command line argument, the behavior is (2), to exclude. Provide an option to *include* it (-S/--serverlog) I think it's useful enough to have a switch, but no problem to exclude it by default. So I can definitely go for Amits suggestions. I also don't feel strongly enough about it to put up any kind of fight if the majority wants different :-) /Magnus
Re: [HACKERS] The Future of Aggregation
Robert Haas robertmh...@gmail.com writes: On Tue, Jun 9, 2015 at 11:00 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Uh, this also requires serialization and deserialization of non- finalized transition state, no? A bunch of this stuff does, but I recently had a Brilliant Insight: we don't need to add a new method for serializing and deserializing transition functions. We can already do that: to serialize an aggregate transition state, you run it through the typoutput (or typsend) function and to deserialize it, you run it through the typinput (or typreceive) function. The only problem is that we have some aggregate functions that use an internal type. Those could, however, be changed: we could invent new types for each aggregate that uses a distinctive internal representation, rather than lumping it all under internal, and then give those types real input and output functions. That way, we don't really need to invent anything new here. Yeah. Now, there are reasons why some of those aggregates are using internal and not, say, bytea: they want the core aggregate logic to be just passing a pointer around and not trying to copy the aggregate's actual state value. However, I have been wondering whether the expanded objects stuff I did recently could provide a more principled way to do that kind of 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] Entities created in one query not available in another in extended protocol
Simon Riggs si...@2ndquadrant.com writes: On 11 June 2015 at 11:20, Shay Rojansky r...@roji.org wrote: It appears that when we send two messages in an extended protocol (so two Parse/Bind/Execute followed by a single Sync), where the first one creates some entity (function, table), and the second one can't query that entity (not found). This isn't terribly important but does seem a bit odd, I wanted to make sure you're aware of this. Sounds somewhat unlikely, but thank you for the report. Can we see a test case? Most commonly in such cases the first request failed and error messages weren't checked before running second message. I'm wondering if it was really more like Parse/Parse/Bind/Bind/Execute/Execute/Sync, in which case the described behavior wouldn't be too surprising at all. I do note that if a transaction is implicitly started to execute these messages, it's not closed until Sync. But that should only affect the visibility of the results to other sessions, not to the current one. There's definitely a CommandCounterIncrement in exec_execute_message ... 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] DBT-3 with SF=20 got failed
On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much. You should set work_mem to a fraction of physical-RAM / concurrent-connections depending on the complexity of your queries. 48GB does not sound reasonable. Regards, Jan -- Jan Wieck Senior Software Engineer http://slony.info -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Entities created in one query not available in another in extended protocol
On 2015-06-11 10:50:31 -0400, Tom Lane wrote: I do note that if a transaction is implicitly started to execute these messages, it's not closed until Sync. But that should only affect the visibility of the results to other sessions, not to the current one. There's definitely a CommandCounterIncrement in exec_execute_message ... exec_execute_message() only does so if the command has run to completion. Shay, Is it possible that a row limit was used and the commands didn't run fully? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Entities created in one query not available in another in extended protocol
I just understood the same thing Tom wrote, yes, Npgsql (currently) sends Parse for the second command before sending Execute for the first one. I will look into that implementation decision. It might be worth looking into Simon's comment though, I'll report if I still see the same problematic behavior after reordering the messages (assuming we do reorder). Thanks for your inputs... On Thu, Jun 11, 2015 at 5:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 11 June 2015 at 11:20, Shay Rojansky r...@roji.org wrote: It appears that when we send two messages in an extended protocol (so two Parse/Bind/Execute followed by a single Sync), where the first one creates some entity (function, table), and the second one can't query that entity (not found). This isn't terribly important but does seem a bit odd, I wanted to make sure you're aware of this. Sounds somewhat unlikely, but thank you for the report. Can we see a test case? Most commonly in such cases the first request failed and error messages weren't checked before running second message. I'm wondering if it was really more like Parse/Parse/Bind/Bind/Execute/Execute/Sync, in which case the described behavior wouldn't be too surprising at all. I do note that if a transaction is implicitly started to execute these messages, it's not closed until Sync. But that should only affect the visibility of the results to other sessions, not to the current one. There's definitely a CommandCounterIncrement in exec_execute_message ... regards, tom lane
Re: [HACKERS] Entities created in one query not available in another in extended protocol
Andres Freund and...@anarazel.de writes: On 2015-06-11 10:50:31 -0400, Tom Lane wrote: I do note that if a transaction is implicitly started to execute these messages, it's not closed until Sync. But that should only affect the visibility of the results to other sessions, not to the current one. There's definitely a CommandCounterIncrement in exec_execute_message ... exec_execute_message() only does so if the command has run to completion. Shay, Is it possible that a row limit was used and the commands didn't run fully? That wouldn't affect utility statements like CREATE FUNCTION or CREATE TABLE, 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] DBT-3 with SF=20 got failed
On Wed, Jun 10, 2015 at 10:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with nrows=119994544, so, it is not strange even if hash-slot itself is larger than 1GB. You forgot to attach the patch, I think. It looks to me like the size of a HashJoinTuple is going to be 16 bytes, so 1GB/16 = ~64 million. That's a lot of buckets, but maybe not unreasonably many if you've got enough memory. -- 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] DBT-3 with SF=20 got failed
2015-06-11 23:20 GMT+09:00 Jan Wieck j...@wi3ck.info: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much. You should set work_mem to a fraction of physical-RAM / concurrent-connections depending on the complexity of your queries. 48GB does not sound reasonable. Smaller number of max_connections and large work_mem configuration are usual for typical OLAP workloads. Even if configuration is not reasonable, it is not a right error message. People cannot understand how to fix it. psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Entities created in one query not available in another in extended protocol
On Thu, Jun 11, 2015 at 5:38 AM, Simon Riggs si...@2ndquadrant.com wrote: On 11 June 2015 at 11:20, Shay Rojansky r...@roji.org wrote: In Npgsql, the .NET driver for PostgreSQL, we've switched from simple to extended protocol and have received a user complaint. It appears that when we send two messages in an extended protocol (so two Parse/Bind/Execute followed by a single Sync), where the first one creates some entity (function, table), and the second one can't query that entity (not found). This isn't terribly important but does seem a bit odd, I wanted to make sure you're aware of this. Sounds somewhat unlikely, but thank you for the report. Can we see a test case? Actually, I think I've seen this before. The code that handles the Sync message does this: case 'S': /* sync */ pq_getmsgend(input_message); finish_xact_command(); send_ready_for_query = true; break; finish_xact_command() calls CommitTransactionCommand(), which does CommandCounterIncrement() or CommitTransaction() as appropriate. So without the Sync, I think it's expected that you don't see the results of the previous command. -- 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] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
Michael Paquier wrote: After spending the night thinking about that, honestly, I think that we should go with (2) and keep the base backup as light-weight as possible and not bother about a GUC. (3) would need some extra intelligence to decide if some files can be skipped or not. Imagine for example --skip-files=global/pg_control or --skip-files=pg_clog (because it *is* a log file with much data), that would just corrupt silently your backup, but I guess that it is what you had in mind. In any case (3) is not worth the maintenance burden because we would need to update the things to filter each time a new important folder is added in PGDATA by a patch. If somebody sets log_directory=pg_clog/ they are screwed pretty badly, aren't they. (I guess this is just a case of don't do that). -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] DBT-3 with SF=20 got failed
Tomas Vondra tomas.von...@2ndquadrant.com writes: Interestingly, the hash code checks for INT_MAX overflows on a number of places, but does not check for this ... Yeah, and at least at one time there were checks to prevent the hash table request from exceeding MaxAllocSize. Did those get removed by somebody? 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] DBT-3 with SF=20 got failed
Hi, On 06/11/15 16:20, Jan Wieck wrote: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much. You should set work_mem to a fraction of physical-RAM / concurrent-connections depending on the complexity of your queries. 48GB does not sound reasonable. That's true, but there are cases where values like this may be useful (e.g. for a particular query). We do allow such work_mem values, so I consider this failure to be a bug. It probably existed in the past, but was amplified by the hash join improvements I did for 9.5, because that uses NTUP_PER_BUCKET=1 instead of NTUP_PER_BUCKET=10. So the arrays of buckets are much larger, and we also much more memory than we had in the past. Interestingly, the hash code checks for INT_MAX overflows on a number of places, but does not check for this ... regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, 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] DBT-3 with SF=20 got failed
2015-06-11 23:33 GMT+09:00 Tomas Vondra tomas.von...@2ndquadrant.com: Hi, On 06/11/15 16:20, Jan Wieck wrote: On 06/11/2015 09:53 AM, Kouhei Kaigai wrote: curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. work_mem can be allocated several times per backend. Nodes like sort and hash_aggregate may each allocate that much. You should set work_mem to a fraction of physical-RAM / concurrent-connections depending on the complexity of your queries. 48GB does not sound reasonable. That's true, but there are cases where values like this may be useful (e.g. for a particular query). We do allow such work_mem values, so I consider this failure to be a bug. It probably existed in the past, but was amplified by the hash join improvements I did for 9.5, because that uses NTUP_PER_BUCKET=1 instead of NTUP_PER_BUCKET=10. So the arrays of buckets are much larger, and we also much more memory than we had in the past. Interestingly, the hash code checks for INT_MAX overflows on a number of places, but does not check for this ... Which number should be changed in this case? Indeed, nbuckets is declared as int, so INT_MAX is hard limit of hash-slot. However, some extreme usage can easily create a situation that we shall touch this restriction. Do we have nbuckets using long int? -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.5 release notes
On Thu, Jun 11, 2015 at 10:20:13AM +0530, Amit Kapila wrote: On Thu, Jun 11, 2015 at 9:45 AM, Bruce Momjian br...@momjian.us wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html Thanks for writing the Release notes. Some comments: Have pg_basebackup use a tablespace mapping file, to allow for file paths of 100+ characters in length I think this is not completely correct. This is mainly done to allow usage of tar format in Windows when tablespaces are present in database, although we have eventually done it for both Windows and Linux in the same way. So how about: Have pg_basebackup use a tablespace mapping file, to allow usage of tar format consistently across all platforms Actually, the file fixes 100+ char on all platforms _and_ Windows symbolic links: Map basebackup tablespaces using a tablespace_map file Windows can't reliably restore symbolic links from a tar format, so instead during backup start we create a tablespace_map file, which is used by the restoring postgres to create the correct links in pg_tblspc. The backup protocol also now has an option to request this file to be included in the backup stream, and this is used by pg_basebackup when operating in tar mode. This is done on all platforms, not just Windows. This means that pg_basebackup will not not work in tar mode against 9.4 and older servers, as this protocol option isn't implemented there. Amit Kapila, reviewed by Dilip Kumar, with a little editing from me. (Andrew Dunstan) [72d422a52] 2015-05-12 09:29:10 -0400 pg_basebackup -F t now succeeds with a long symlink target Error when creating names too long for tar format The tar format (at least the version we are using), does not support file names or symlink targets longer than 99 bytes. Until now, the tar creation code would silently truncate any names that are too long. (Its original application was pg_dump, where this never happens.) This creates problems when running base backups over the replication protocol. The most important problem is when a tablespace path is longer than 99 bytes, which will result in a truncated tablespace path being backed up. Less importantly, the basebackup protocol also promises to back up any other files it happens to find in the data directory, which would also lead to file name truncation if someone put a file with a long name in there. Now both of these cases result in an error during the backup. Add tests that fail when a too-long file name or symlink is attempted to be backed up. Reviewed-by: Robert Hass robertmh...@gmail.com (Peter Eisentraut) [23a78352c] 2015-02-24 13:41:07 -0500 Also shall we mention about below in Migrations to 9.5 section pg_basebackup will not not work in tar mode against 9.4 and older servers, as we have introduced a new protocol option in that mode. Yes, added. The attached, applied patch has both of these mentions, and mentions 'tar' mode. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/release-9.5.sgml b/doc/src/sgml/release-9.5.sgml new file mode 100644 index 2073f74..f022050 *** a/doc/src/sgml/release-9.5.sgml --- b/doc/src/sgml/release-9.5.sgml *** *** 1322,1329 listitem para !Have applicationpg_basebackup/ use a tablespace mapping file, !to allow for file paths of 100+ characters in length (Amit Kapila) /para /listitem --- 1322,1336 listitem para !Have applicationpg_basebackup/ use a tablespace mapping !file with the applicationtar/ format, to handle file paths !of 100+ characters in length and sybolic links on systemitem !class=osnameMS Windows/ (Amit Kapila) ! /para ! ! para !This change prevents applicationpg_basebackup/ from backing !up pre-9.5 servers. /para /listitem -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] The purpose of the core team
On Tue, Jun 9, 2015 at 6:35 PM, Bruce Momjian br...@momjian.us wrote: There has been some confusion by old and new community members about the purpose of the core team, and this lack of understanding has caused some avoidable problems. Therefore, the core team has written a core charter and published it on our website: http://www.postgresql.org/developer/core/ Hopefully this will be helpful to people. I believe the core team is suffering from a lack of members who are involved in writing, reviewing, and committing patches. Those things are not core functions of the core team, as that charter illustrates. However, the core team needs to know when it should initiate a release, and to do that it needs to understand the impact of bugs that have been fixed and bugs that have not been fixed. The recent discussion of multixacts seems to indicate that the number of core team members who had a clear understanding of the issues was zero, which I view as unfortunate. The core team also needs to make good decisions about who should be made a committer, and the people who are doing reviews and commits of other people's patches are in the best position to have an informed opinion on that topic. As a non-core team member, I find it quite frustrating that getting a release triggered requires emailing a closed mailing list. I am not a party to all of the discussion on my request, and the other people who might know whether my request is technically sound or not are not party to that discussion either. I disagreed with the decision to stamp 9.4.3 without waiting for b6a3444fa63519a0192447b8f9a332dddc66018f, but of course I couldn't comment on it, because it was decided in a forum in which I don't get to participate, on a thread on which I was not copied. I realize that, because decisions about whether to release and when to release often touch on security issues, not all of this discussion can be carried on in public. But when the cone of secrecy is drawn in so tightly that excludes everyone who actually understands the technical issues related to the proposed release, we have lost our way, and do our users a disservice. I am not sure whether the solution to this problem is to add more people to the core team, or whether the solution is to move release timing decisions and committer selection out of the core team to some newly-created group. But I believe that change is needed. -- 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] DBT-3 with SF=20 got failed
2015-06-11 23:28 GMT+09:00 Robert Haas robertmh...@gmail.com: On Wed, Jun 10, 2015 at 10:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with nrows=119994544, so, it is not strange even if hash-slot itself is larger than 1GB. You forgot to attach the patch, I think. Oops, I forgot to attach indeed. It looks to me like the size of a HashJoinTuple is going to be 16 bytes, so 1GB/16 = ~64 million. That's a lot of buckets, but maybe not unreasonably many if you've got enough memory. EXPLAIN says, this Hash node takes underlying SeqScan with 119994544 (~119 million) rows, but it is much smaller than my work_mem setting. -- KaiGai Kohei kai...@kaigai.gr.jp hashslot-allocation-by-huge-alloc.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] The Future of Aggregation
On Tue, Jun 9, 2015 at 11:00 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Uh, this also requires serialization and deserialization of non- finalized transition state, no? A bunch of this stuff does, but I recently had a Brilliant Insight: we don't need to add a new method for serializing and deserializing transition functions. We can already do that: to serialize an aggregate transition state, you run it through the typoutput (or typsend) function and to deserialize it, you run it through the typinput (or typreceive) function. The only problem is that we have some aggregate functions that use an internal type. Those could, however, be changed: we could invent new types for each aggregate that uses a distinctive internal representation, rather than lumping it all under internal, and then give those types real input and output functions. That way, we don't really need to invent anything new here. -- 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] 9.5 release notes
Hi, On 06/11/15 06:15, Bruce Momjian wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html and it will eventually appear here: http://www.postgresql.org/docs/devel/static/release.html I am ready to make suggested adjustments, though I am traveling for conferences for the next ten days so there might a delay in my replies. I wonder whether this point: * Improve hash creation performance (Tomas Vondra, Teodor Sigaev, Tom Lane) is really about and 45f6240a, 8cce08f1 and 30d7ae3c. I can't remember or find other hash-related patches I've been working on. If that's the case, it really is not about hash creation performance (except maybe partially the first commit), but about the lookup performance on the hash table. So it should rather be 'improve hash-join performance'. Also, I think the list of authors is wrong. Robert Haas did a lot of work on those patches, so he should be mentioned there, and I don't remember Teodor working on this. But maybe I was working on another patch and managed to forget about it. -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, 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] minor issues in pg_rewind
On Thu, Jun 11, 2015 at 10:21 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Thu, Jun 11, 2015 at 2:38 AM, Fujii Masao masao.fu...@gmail.com wrote: * Remove invalid option character N from the third argument (valid option string) of getopt_long(). * Use pg_free() or pfree() to free the memory allocated by pg_malloc() or palloc() instead of always using free(). * Assume problem is no disk space if write() fails but doesn't set errno. * Fix several typos. This looks good to me, especially the ENOSPC enforcement for pg_control. Thanks for reviewing the patch! I just pushed it. We may want to rename datapagemap_iterate to datapagemap_init as well. I can't really see what iterate means in this context. returning an iterator object, maybe. The pair of iterator() and next() is common way to walk through the entries, so I can live with the current naming of the function. 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] DBT-3 with SF=20 got failed
On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Hello, I got the following error during DBT-3 benchmark with SF=20. psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 It looks to me Hash node tries to 1GB area using palloc0(), but it exceeds the limitation of none-huge interface. (gdb) bt #0 0x7f669d29a989 in raise () from /lib64/libc.so.6 #1 0x7f669d29c098 in abort () from /lib64/libc.so.6 #2 0x0090ccfd in ExceptionalCondition (conditionName=0xb18130 !(((Size) (size) = ((Size) 0x3fff))), errorType=0xb17efd FailedAssertion, fileName=0xb17e40 mcxt.c, lineNumber=856) at assert.c:54 #3 0x0093ad53 in palloc0 (size=1073741824) at mcxt.c:856 #4 0x00673045 in ExecHashTableCreate (node=0x7f669de951f0, hashOperators=0x24dbf90, keepNulls=0 '\000') at nodeHash.c:391 #5 0x006752e1 in ExecHashJoin (node=0x24d74e0) at nodeHashjoin.c:169 #6 0x0065abf4 in ExecProcNode (node=0x24d74e0) at execProcnode.c:477 #7 0x00681026 in ExecNestLoop (node=0x24d6668) at nodeNestloop.c:123 #8 0x0065abca in ExecProcNode (node=0x24d6668) at execProcnode.c:469 #9 0x00681026 in ExecNestLoop (node=0x24d61f8) at nodeNestloop.c:123 #10 0x0065abca in ExecProcNode (node=0x24d61f8) at execProcnode.c:469 #11 0x00681026 in ExecNestLoop (node=0x24d5478) at nodeNestloop.c:123 #12 0x0065abca in ExecProcNode (node=0x24d5478) at execProcnode.c:469 #13 0x00681026 in ExecNestLoop (node=0x24d51d0) at nodeNestloop.c:123 #14 0x0065abca in ExecProcNode (node=0x24d51d0) at execProcnode.c:469 The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with nrows=119994544, so, it is not strange even if hash-slot itself is larger than 1GB. Another allocation request potentially reset of expand hash-slot may also needs to be Huge version of memory allocation, I think. Thanks, Below is the query itself and EXPLAIN result. dbt3c=# EXPLAIN VERBOSE dbt3c-# select dbt3c-# s_name, dbt3c-# count(*) as numwait dbt3c-# from dbt3c-# supplier, dbt3c-# lineitem l1, dbt3c-# orders, dbt3c-# nation dbt3c-# where dbt3c-# s_suppkey = l1.l_suppkey dbt3c-# and o_orderkey = l1.l_orderkey dbt3c-# and o_orderstatus = 'F' dbt3c-# and l1.l_receiptdate l1.l_commitdate dbt3c-# and exists ( dbt3c(# select dbt3c(# * dbt3c(# from dbt3c(# lineitem l2 dbt3c(# where dbt3c(# l2.l_orderkey = l1.l_orderkey dbt3c(# and l2.l_suppkey l1.l_suppkey dbt3c(# ) dbt3c-# and not exists ( dbt3c(# select dbt3c(# * dbt3c(# from dbt3c(# lineitem l3 dbt3c(# where dbt3c(# l3.l_orderkey = l1.l_orderkey dbt3c(# and l3.l_suppkey l1.l_suppkey dbt3c(# and l3.l_receiptdate l3.l_commitdate dbt3c(# ) dbt3c-# and s_nationkey = n_nationkey dbt3c-# and n_name = 'UNITED KINGDOM' dbt3c-# group by dbt3c-# s_name dbt3c-# order by dbt3c-# numwait desc, dbt3c-# s_name dbt3c-# LIMIT 100; QUERY PLAN -- -- -- Limit (cost=6792765.24..6792765.24 rows=1 width=26) Output: supplier.s_name, (count(*)) - Sort (cost=6792765.24..6792765.24 rows=1 width=26) Output: supplier.s_name, (count(*)) Sort Key: (count(*)) DESC, supplier.s_name - HashAggregate (cost=6792765.22..6792765.23 rows=1 width=26) Output: supplier.s_name, count(*) Group Key: supplier.s_name - Nested Loop Anti Join (cost=4831094.94..6792765.21 rows=1 width=26) Output: supplier.s_name - Nested Loop (cost=4831094.37..6792737.52 rows=1 width=34) Output: supplier.s_name, l1.l_suppkey, l1.l_orderkey Join Filter: (supplier.s_nationkey = nation.n_nationkey) - Nested Loop (cost=4831094.37..6792736.19 rows=1 width=38) Output: supplier.s_name,
Re: [HACKERS] DBT-3 with SF=20 got failed
curious: what was work_mem set to? work_mem=48GB My machine mounts 256GB physical RAM. -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Thursday, June 11, 2015 10:52 PM To: Kaigai Kouhei(海外 浩平) Cc: pgsql-hackers@postgreSQL.org Subject: Re: [HACKERS] DBT-3 with SF=20 got failed On Wed, Jun 10, 2015 at 9:57 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: Hello, I got the following error during DBT-3 benchmark with SF=20. psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 psql:query21.sql:50: ERROR: invalid memory alloc request size 1073741824 It looks to me Hash node tries to 1GB area using palloc0(), but it exceeds the limitation of none-huge interface. (gdb) bt #0 0x7f669d29a989 in raise () from /lib64/libc.so.6 #1 0x7f669d29c098 in abort () from /lib64/libc.so.6 #2 0x0090ccfd in ExceptionalCondition (conditionName=0xb18130 !(((Size) (size) = ((Size) 0x3fff))), errorType=0xb17efd FailedAssertion, fileName=0xb17e40 mcxt.c, lineNumber=856) at assert.c:54 #3 0x0093ad53 in palloc0 (size=1073741824) at mcxt.c:856 #4 0x00673045 in ExecHashTableCreate (node=0x7f669de951f0, hashOperators=0x24dbf90, keepNulls=0 '\000') at nodeHash.c:391 #5 0x006752e1 in ExecHashJoin (node=0x24d74e0) at nodeHashjoin.c:169 #6 0x0065abf4 in ExecProcNode (node=0x24d74e0) at execProcnode.c:477 #7 0x00681026 in ExecNestLoop (node=0x24d6668) at nodeNestloop.c:123 #8 0x0065abca in ExecProcNode (node=0x24d6668) at execProcnode.c:469 #9 0x00681026 in ExecNestLoop (node=0x24d61f8) at nodeNestloop.c:123 #10 0x0065abca in ExecProcNode (node=0x24d61f8) at execProcnode.c:469 #11 0x00681026 in ExecNestLoop (node=0x24d5478) at nodeNestloop.c:123 #12 0x0065abca in ExecProcNode (node=0x24d5478) at execProcnode.c:469 #13 0x00681026 in ExecNestLoop (node=0x24d51d0) at nodeNestloop.c:123 #14 0x0065abca in ExecProcNode (node=0x24d51d0) at execProcnode.c:469 The attached patch replaces this palloc0() by MemoryContextAllocHuge() + memset(). Indeed, this hash table is constructed towards the relation with nrows=119994544, so, it is not strange even if hash-slot itself is larger than 1GB. Another allocation request potentially reset of expand hash-slot may also needs to be Huge version of memory allocation, I think. Thanks, Below is the query itself and EXPLAIN result. dbt3c=# EXPLAIN VERBOSE dbt3c-# select dbt3c-# s_name, dbt3c-# count(*) as numwait dbt3c-# from dbt3c-# supplier, dbt3c-# lineitem l1, dbt3c-# orders, dbt3c-# nation dbt3c-# where dbt3c-# s_suppkey = l1.l_suppkey dbt3c-# and o_orderkey = l1.l_orderkey dbt3c-# and o_orderstatus = 'F' dbt3c-# and l1.l_receiptdate l1.l_commitdate dbt3c-# and exists ( dbt3c(# select dbt3c(# * dbt3c(# from dbt3c(# lineitem l2 dbt3c(# where dbt3c(# l2.l_orderkey = l1.l_orderkey dbt3c(# and l2.l_suppkey l1.l_suppkey dbt3c(# ) dbt3c-# and not exists ( dbt3c(# select dbt3c(# * dbt3c(# from dbt3c(# lineitem l3 dbt3c(# where dbt3c(# l3.l_orderkey = l1.l_orderkey dbt3c(# and l3.l_suppkey l1.l_suppkey dbt3c(# and l3.l_receiptdate l3.l_commitdate dbt3c(# ) dbt3c-# and s_nationkey = n_nationkey dbt3c-# and n_name = 'UNITED KINGDOM' dbt3c-# group by dbt3c-# s_name dbt3c-# order by dbt3c-# numwait desc, dbt3c-# s_name dbt3c-# LIMIT 100; QUERY PLAN -- -- -- Limit (cost=6792765.24..6792765.24 rows=1 width=26) Output: supplier.s_name, (count(*)) - Sort (cost=6792765.24..6792765.24 rows=1 width=26) Output: supplier.s_name, (count(*)) Sort Key: (count(*)) DESC, supplier.s_name - HashAggregate (cost=6792765.22..6792765.23 rows=1 width=26) Output: supplier.s_name, count(*) Group Key: supplier.s_name -
Re: [HACKERS] Comfortably check BackendPID with psql
On 6/11/15 4:55 AM, Andres Freund wrote: Hi, On 2015-06-11 09:41:17 +, Naoya Anzai wrote: This is a so tiny patch but I think it is very useful for hackers and DBAs. When we debug with psql, we frequently use SELECT pg_backend_pid();. This can change the input of the 24 characters to the only 4 characters! Not a big fan of that abbreviation itself. What I'd wondered about instead - and actually had patched into my psql at some point - is adding an appropriate escape to psql's PROMPT. I think that'd serve your purpose as well? +3.14159; that would be hugely helpful when using gdb. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.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] 9.5 release notes
On Thu, Jun 11, 2015 at 4:23 PM, Peter Geoghegan p...@heroku.com wrote: Secondly, Robert didn't credit himself as an author in his commit message for the abbreviated keys infrastructure + text opclass support *at all*. However, I think that Robert should be listed as a secondary author of the abbreviated keys infrastructure, and that he would agree that I am clearly the primary author. Andrew Gierth did work on the datum case for sortsupport + abbreviation, so I agree he should be listed as a secondary author of the infrastructure too, after Robert. I'd probably say Peter, Andrew, me. I think there should be a total of 4 items related to sorting. The wording I come up with may not be appropriate, but will give you an idea: * Allow sorting to be performed by inlined, non-SQL-callable comparison functions for CREATE INDEX, REINDEX and CLUSTER operations based on a B-Tree operator class. (5ea86e6e6 -- Geoghegan) * Add abbreviated key sorting infrastructure. This allows B-Tree operator classes to provide compact abbreviated representations of pass-by-reference types which are sorted with inexpensive comparisons. This makes sort operations with support for the infrastructure very significantly faster in the common case where most comparisons can be resolved with the abbreviated representation alone. (4ea51cdfe85 -- Geoghegan, Haas, Gierth, with Gierth's contribution coming from 78efd5c1 alone) * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to text/varlena operator class. This significantly accelerates sorting on text columns. (4ea51cdfe85 too, but also b34e37bf. Worth noting separately IMV. Geoghegan, Haas). * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to numeric operator class. This significantly accelerates sorting on numeric columns. (abd94bcac, Gierth) I think this is overkill. Bruce's single entry seems fine to me, although we could adjust the wording a bit. We need this to be simple enough that someone not immersed in the code can understand it, and as soon as you talk about sortsupport as if it were a thing, you lose that. -- 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] 9.5 release notes
On Thu, Jun 11, 2015 at 2:17 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jun 11, 2015 at 4:23 PM, Peter Geoghegan p...@heroku.com wrote: Secondly, Robert didn't credit himself as an author in his commit message for the abbreviated keys infrastructure + text opclass support *at all*. However, I think that Robert should be listed as a secondary author of the abbreviated keys infrastructure, and that he would agree that I am clearly the primary author. Andrew Gierth did work on the datum case for sortsupport + abbreviation, so I agree he should be listed as a secondary author of the infrastructure too, after Robert. I'd probably say Peter, Andrew, me. If you bunch everything together, then yes, I'd agree. I think there should be a total of 4 items related to sorting. The wording I come up with may not be appropriate, but will give you an idea: * Allow sorting to be performed by inlined, non-SQL-callable comparison functions for CREATE INDEX, REINDEX and CLUSTER operations based on a B-Tree operator class. (5ea86e6e6 -- Geoghegan) * Add abbreviated key sorting infrastructure. This allows B-Tree operator classes to provide compact abbreviated representations of pass-by-reference types which are sorted with inexpensive comparisons. This makes sort operations with support for the infrastructure very significantly faster in the common case where most comparisons can be resolved with the abbreviated representation alone. (4ea51cdfe85 -- Geoghegan, Haas, Gierth, with Gierth's contribution coming from 78efd5c1 alone) * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to text/varlena operator class. This significantly accelerates sorting on text columns. (4ea51cdfe85 too, but also b34e37bf. Worth noting separately IMV. Geoghegan, Haas). * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to numeric operator class. This significantly accelerates sorting on numeric columns. (abd94bcac, Gierth) I think this is overkill. Bruce's single entry seems fine to me, although we could adjust the wording a bit. We need this to be simple enough that someone not immersed in the code can understand it, and as soon as you talk about sortsupport as if it were a thing, you lose that. I think that that is a valid point of view, but that we should get across that sortsupport works for all types with CREATE INDEX + CLUSTER now, including integers, for example. The reviewer of that particular piece of work, Andreas Karlsson, put it at a 10% - 15% improvement for CREATE INDEX on an integer column IIRC. That's not a bad little boost. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reconsidering the behavior of ALTER COLUMN TYPE
On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: A recent thread in pgsql-general shows yet another user who's befuddled by the need to add a USING clause to an ALTER TABLE ... ALTER COLUMN TYPE command: http://www.postgresql.org/message-id/flat/CAD25zGDiaqCG=eqXp=byvzcdgvcqubh7kbpjpjqsespowfv...@mail.gmail.com Specifically, it's not clear why you can change the type of a uuid[] column with alter table t alter u type text[]; but then you can't change it back with alter table t alter u type uuid[]; The reason of course is that uuid-to-text is considered an assignment-grade coercion while text-to-uuid is not. I've lost count of the number of times we've had to tell someone to use a USING clause for this. Maybe it's time to be a little bit less rigid about this situation, and do what the user obviously wants rather than make him spell out a rather pointless USING. Specifically, after a bit of thought, I suggest that (1) If there's no USING, attempt to coerce the column value as though an *explicit* coercion were used. (2) If there is a USING, maintain the current behavior that the result has to be assignment-coercible to the new column type. We could use explicit-coercion semantics here too, but I think that might be throwing away a bit too much error checking, in a case where the odds of a typo are measurably higher than for the default situation. This could be documented as if there is no USING, the default behavior is as if you'd written USING column::newtype. Thoughts? In any case, we oughta use two different error messages for the two cases, as per my comment in the above thread. That seems like a back-patchable bug fix, though of course any semantics change should only be in HEAD. I have a slight preference to keep it to tightening up the wording on both the hint and the error (for example, Perhaps you meant USING foo::type?) but leaving the behavior alone. In other components of the system, forcing explicit casts has added safety and there is no more dangerous footgun than 'ALTER TABLE'. IMSNHO, the issue is the somewhat arcane syntax, not the casting rules. 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] Is it possible to have a fast-write Index?
On 6/5/15 6:54 PM, deavid wrote: So the problem is: i see a low iowait, and CPU time for one core is at 80-90% most of the time. I can buy more ram, better disks, or cpu's with more cores. But one cpu core would have more-or-less the same speed no matter how much money you invest. When someone wants a delayed-write index is similar to setting synchronous_commit = off. We want to give an OK to the backend as soon as is possible and do this work in background. But we also want some reliability against crashes. Also, if the task is done in background it may be done from other backend, so probably several indexes could be packed at once using different backend processes. We could use the entire cpu if our index writes aren't tied to the session who wrote the row. Something that might help here would be doing the index maintenance in parallel via background workers. There's now enough parallelism infrastructure that it shouldn't be too hard to hack up a quick test of that. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.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] Construction of Plan-node by CSP (RE: Custom/Foreign-Join-APIs)
Robert Haas robertmh...@gmail.com writes: Tom, do you want to review this patch and figure out how to solve the underlying problem? If not, I will take care of it. But I will be unhappy if I put time and effort into this and then you insist on changing everything afterwards, again. [ sorry for slow response, been busy ] I will take a look. regards, tom lane Tom, please don't forget the problem. It is still problematic for custom-scan provider that tries to implement own join logic, thus we still have to apply additional patch (or copypaste createplan.c to module's source). Thanks, -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com custom-join-children.v2.patch Description: custom-join-children.v2.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cancel race condition
Thanks for the extra consideration Robert. Since I'm implementing a generic driver, users can send either single-statement transactions or actual multiple-statement transaction. However, whether we're in a transaction or not doesn't seem to affect Npgsql logic (unless I'm missing something) - if the cancellation does hit a query the transaction will be cancelled and it's up to the user to roll it back as is required in PostgreSQL... On Thu, Jun 11, 2015 at 9:50 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jun 9, 2015 at 4:42 AM, Shay Rojansky r...@roji.org wrote: Ah, OK - I wasn't aware that cancellation was actually delivered as a regular POSIX signal... You're right about the lack of guarantees then. In that case, I'm guessing not much could be do to guarantee sane cancellation behavior... I do understand the best effort idea around cancellations. However, it seems different to say we'll try our best and the cancellation may not be delivered (no bad consequences except maybe performance), and to say we'll try our best but the cancellation may be delivered randomly to any query you send from the moment you send the cancellation. The second makes it very difficult to design a 100% sane, deterministic application... Any plans to address this in protocol 4? Would you have any further recommendations or guidelines to make the situation as sane as possible? I guess I could block any new SQL queries while a cancellation on that connection is still outstanding (meaning that the cancellation connection hasn't yet been closed). As you mentioned this wouldn't be a 100% solution since it would only cover signal sending, but better than nothing? Blocking new queries seems like a good idea. Note that the entire transaction (whether single-statement or multi-statement) will be aborted, or at least the currently-active subtransaction, not just the current query. If you're using single-statement transactions I guess there is not much practical difference, but if you are using multi-statement transactions the application kind of needs to be aware of this, since it needs to know that any work it did got rolled back, and everything's going to fail up until the current (sub)transaction is rolled back. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Entities created in one query not available in another in extended protocol
Thanks everyone for your time (or rather sorry for having wasted it). Just in case it's interesting to you... The reason we implemented things this way is in order to avoid a deadlock situation - if we send two queries as P1/D1/B1/E1/P2/D2/B2/E2, and the first query has a large resultset, PostgreSQL may block writing the resultset, since Npgsql isn't reading it at that point. Npgsql on its part may get stuck writing the second query (if it's big enough) since PostgreSQL isn't reading on its end (thanks to Emil Lenngren for pointing this out originally). Of course this isn't an excuse for anything, we're looking into ways of solving this problem differently in our driver implementation. Shay On Thu, Jun 11, 2015 at 6:17 PM, Simon Riggs si...@2ndquadrant.com wrote: On 11 June 2015 at 16:56, Shay Rojansky r...@roji.org wrote: Npgsql (currently) sends Parse for the second command before sending Execute for the first one. Look no further than that. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
[HACKERS] Reconsidering the behavior of ALTER COLUMN TYPE
A recent thread in pgsql-general shows yet another user who's befuddled by the need to add a USING clause to an ALTER TABLE ... ALTER COLUMN TYPE command: http://www.postgresql.org/message-id/flat/CAD25zGDiaqCG=eqXp=byvzcdgvcqubh7kbpjpjqsespowfv...@mail.gmail.com Specifically, it's not clear why you can change the type of a uuid[] column with alter table t alter u type text[]; but then you can't change it back with alter table t alter u type uuid[]; The reason of course is that uuid-to-text is considered an assignment-grade coercion while text-to-uuid is not. I've lost count of the number of times we've had to tell someone to use a USING clause for this. Maybe it's time to be a little bit less rigid about this situation, and do what the user obviously wants rather than make him spell out a rather pointless USING. Specifically, after a bit of thought, I suggest that (1) If there's no USING, attempt to coerce the column value as though an *explicit* coercion were used. (2) If there is a USING, maintain the current behavior that the result has to be assignment-coercible to the new column type. We could use explicit-coercion semantics here too, but I think that might be throwing away a bit too much error checking, in a case where the odds of a typo are measurably higher than for the default situation. This could be documented as if there is no USING, the default behavior is as if you'd written USING column::newtype. Thoughts? In any case, we oughta use two different error messages for the two cases, as per my comment in the above thread. That seems like a back-patchable bug fix, though of course any semantics change should only be in HEAD. 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] 9.5 release notes
On Wed, Jun 10, 2015 at 9:15 PM, Bruce Momjian br...@momjian.us wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: + listitem + para +Improve the speed of sorting character and numeric fields (Robert +Haas, Peter Geoghegan, Andrew Gierth) + /para + /listitem A few comments on this. First of all, I think it should be separately noted that the sortsupport infrastructure is now used in virtually all places where it's useful (see commit 5ea86e6e6). So for example, CREATE INDEX on integer columns ought to be notably faster (and CLUSTER, too). The 9.2 era sortsupport stuff was simply never adopted to do that until now. That has nothing to do with abbreviated keys, except that the idea of abbreviated keys gave me a strong reason to care about sortsupport a lot more. But commit 5ea86e6e6 predates abbreviated keys, and is certainly independently useful (this really should have made it into 9.2). Secondly, Robert didn't credit himself as an author in his commit message for the abbreviated keys infrastructure + text opclass support *at all*. However, I think that Robert should be listed as a secondary author of the abbreviated keys infrastructure, and that he would agree that I am clearly the primary author. Andrew Gierth did work on the datum case for sortsupport + abbreviation, so I agree he should be listed as a secondary author of the infrastructure too, after Robert. I think there should be a total of 4 items related to sorting. The wording I come up with may not be appropriate, but will give you an idea: * Allow sorting to be performed by inlined, non-SQL-callable comparison functions for CREATE INDEX, REINDEX and CLUSTER operations based on a B-Tree operator class. (5ea86e6e6 -- Geoghegan) * Add abbreviated key sorting infrastructure. This allows B-Tree operator classes to provide compact abbreviated representations of pass-by-reference types which are sorted with inexpensive comparisons. This makes sort operations with support for the infrastructure very significantly faster in the common case where most comparisons can be resolved with the abbreviated representation alone. (4ea51cdfe85 -- Geoghegan, Haas, Gierth, with Gierth's contribution coming from 78efd5c1 alone) * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to text/varlena operator class. This significantly accelerates sorting on text columns. (4ea51cdfe85 too, but also b34e37bf. Worth noting separately IMV. Geoghegan, Haas). * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to numeric operator class. This significantly accelerates sorting on numeric columns. (abd94bcac, Gierth) I'm not sure if it's worth mentioning the cheap equality for text commit (e246b3d6eac09). I guess that it probably is, because it will help with things like index scans, too. Arguably that isn't a sorting thing (it's certainly not *just* a sorting thing). I've blogged on the abbreviated key stuff quite a bit, which may be useful should you require additional background information: http://pgeoghegan.blogspot.com/2015/01/abbreviated-keys-exploiting-locality-to.html http://pgeoghegan.blogspot.com/2015/04/abbreviated-keys-for-numeric-to.html Thanks -- Peter Geoghegan -- Sent 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.5 release notes
On Thu, Jun 11, 2015 at 1:23 PM, Peter Geoghegan p...@heroku.com wrote: * Add sortsupport (support for non-SQL callable interface for comparators) with abbreviation capability to text/varlena operator class. This significantly accelerates sorting on text columns. (4ea51cdfe85 too, but also b34e37bf. Worth noting separately IMV. Geoghegan, Haas). Also, note that there is no character(n) support for abbreviation as yet (nor is there any contrib/citext support), so it's not quite true that the sorting stuff accelerates sorts on character fields. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres GSSAPI Encryption
Robbie, * Robbie Harwood (rharw...@redhat.com) wrote: I've coded up the GSSAPI encryption and is on my github[0]. It's missing a number of things before merge, including proper error handling, correct ACL behavior (by and large, it currently doesn't respect hba.conf), and exposing configuration handles in hba.conf and the client for the settings we've talked about above, as well as documentation of all that. Neat! We're currently focusing on stabilizing for PostgreSQL 9.5, but this work is great and we'd like to make sure to review it for inclusion post-9.5. Our process is that patches are posted to the mailing list and then an entry is created on http://commitfest.postgresql.org which references the mailing list post. This way, we won't forget about the patch in a month or so, after we've branched off 9.5 and resumed reviewing development work. So, when you're ready for it to be reviewed for feedback, please post a full patch against whatever current master is at the time, and register the patch in the commitfest application. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 3:22 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I think #1 is the part that we seem to have the most trouble with. It seems easily fixable: establish a new mailing list for that task (say pgsql-release) and get all the current -core in there, plus the set of active committers. That group would handle tasks #1 and #2 above. +1. -- 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
[HACKERS] On columnar storage
We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. I've been trying to figure out a plan to enable native column stores (CS or colstore) for Postgres. Motivations: * avoid the 32 TB limit for tables * avoid the 1600 column limit for tables * increased performance There already are some third-party CS implementations for Postgres; some of these work on top of the FDW interface, others are simply proprietary forks. Since I don't have access to any of their code, it's not much I can learn from them. If people with insider knowledge on them can chime in, perhaps we can work together -- collaboration is very welcome. We're not interested in perpetuating the idea that a CS needs to go through the FDW mechanism. Even if there's a lot of simplicity of implementation, it's almost certain to introduce too many limitations. Simply switching all our code to use columnar storage rather than row-based storage is unlikely to go well. We're aiming at letting some columns of tables be part of a CS, while other parts would continue to be in the heap. At the same time, we're aiming at opening the way for different CS implementations instead of trying to provide a single one-size-fits-all one. There are several parts to this: 1. the CSM API 2. Cataloguing column stores 3. Query processing: rewriter, optimizer, executor The Column Store Manager API Since we want to have pluggable implementations, we need to have a registry of store implementations. I propose we add a catalog pg_cstore_impl with OID, name, and a bunch of function references to open a store, getvalue from it, getrows (to which we pass a qual and get a bunch of tuple IDs back), putvalue. This is in line with our procedural language support. One critical detail is what will be used to identify a heap row when talking to a CS implementation. There are two main possibilities: 1. use CTIDs 2. use some logical tuple identifier Using CTIDs is simpler. One disadvantage is that every UPDATE of a row needs to let the CS know about the new location of the tuple, so that the value is known associated with the new tuple location as well as the old. This needs to happen even if the value of the column itself is not changed. Using logical tuple identifiers solves this problem: an update does not change the LTID, so the tuple colstore needn't be involved unless the attribute(s) in the colstore is being changed. The downside is that the logical tuple identifier must come from somewhere. We could either use some user attribute, if there's something appropriate. But it's probably not good to simply use any primary key that the user has specified. (Also, having an UPDATE change the primary key would be troublesome). We could also offer the choice of having an autogenerated value that's not user-visible; we currently don't have non-user-visible columns, so this would be additional implementation effort. Furthermore, we should think about interactions between this and the IDENTITY stuff we currently have for replication -- my impression is that IDENTITY does not necessarily represent an useful identifier for column store purposes. All in all, it seems prudent to limit the initial implementation to use CTIDs only, and leave LTIDs for a later stage. Cataloguing Column Stores - Each table with columns in a separate store will have relhasstore=t. This hints construction of its relcache entry to obtain rows from pg_cstore for that table. The new catalog pg_cstore looks like this: cstname | cststoreid | cstrelid | cstnatts | cstatts cstname is the store name; unique within each relation. cststoreid is the OID of the pg_cstore_impl row. cstorerelid is the OID of the table that this cstore is for. cstnatts is the number of columns in the store cstatts is an array of attnums contained in this store. This design permits having multiple stores for a table, and one or more columns in a store. We will focus on the case that a table has a single column store, and a column store has a single column, because we believe this simplifies several things. Query Processing Rewriter Parsing occurs as currently. During query rewrite, specifically at the bottom of the per-relation loop in fireRIRrules(), we will modify the query tree: each relation RTE containing a colstore will be replaced with a JoinExpr containing the relation as left child and the colstore as right child (1). The colstore RTE will be of a new RTEKind. For each such change, all Var nodes that point to attnums stored in the colstore will modified so that they reference the RTE of the colstore instead (2). (1) This seems very similar to what convert_ANY_sublink_to_join() does. (2) This is very similar to ChangeVarNodes does, except that we modify only some of the var nodes pointing to the relation,
Re: [HACKERS] The purpose of the core team
On 12 June 2015 at 06:48, Noah Misch n...@leadboat.com wrote: On Thu, Jun 11, 2015 at 03:47:06PM -0300, Alvaro Herrera wrote: Bruce Momjian wrote: http://www.postgresql.org/developer/core/ After going over this a few times, there is one thing that strikes me that nobody has mentioned: the list of tasks mentioned there has one that's completely unlike the others. These are related to human relations: Acting as a conduit for confidential communication. Making policy announcements. Managing permissions for commits, infrastructure, etc. Handling disciplinary issues. Making difficult decisions when consensus is lacking. while this one is highly technical: Coordinating release activities. Quite so. Deciding it's time for a release requires the same knowledge and skills as deciding it's time to commit patch P, yet we have a special-case decision procedure. A release does require people acting in concert for a span of a few days, but that precise scheduling is work for an administrative assistant, not work befitting -core. Deciding WHAT goes in the next release? is what Committers do, by definition. It seems strange to have a different mailing list for WHEN is the next release needed?, so those two things should be combined. It seems that only this last one is where most people seem to have a problem. I wonder if it makes sense to create a separate group that handles release activites -- the release team. I think the decision to initiate or revoke release scheduling belongs in the same forum as patch development, usually -hackers or -security. We'd need to pick a way to clearly signal the discussion's conclusion, analogous to how a pushed commit unambiguously disposes a patch proposal. The balance of coordinating release activities is mechanical, and -packagers seems adequate for it. Packagers should be about HOW do we make the next release, which is separate from the above. Ultimately, How effects When, but When is it needed? is an earlier thought. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] Is it possible to have a fast-write Index?
On 5 June 2015 at 18:07, deavid deavidsed...@gmail.com wrote: There are several use cases where I see useful an index, but adding it will slow too much inserts and updates. For example, when we have 10 million rows on a table, and it's a table which has frequent updates, we need several index to speed up selects, but then we'll slow down updates a lot, specially when we have 10 or more indexes. Other cases involve indexes for text search, which are used only for user search and aren't that important, so we want to have them, but we don't want the overload they put whenever we write on the table. I know different approaches that already solve some of those problems in some ways (table partitioning, partial indexes, etc), but i don't feel they are the solution to every problem of this kind. Some people already asked for delayed write indexes, but the idea gets discarded because the index could get out of sync, so it can omit results and this is unacceptable. But i think maybe that could be fixed in several ways and we can have a fast and reliable index (but maybe not so fast on selects). This is exactly the use case and mechanism for BRIN indexes. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] On columnar storage
On Fri, Jun 12, 2015 at 4:33 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. I've been trying to figure out a plan to enable native column stores (CS or colstore) for Postgres. Motivations: * avoid the 32 TB limit for tables * avoid the 1600 column limit for tables * increased performance There already are some third-party CS implementations for Postgres; some of these work on top of the FDW interface, others are simply proprietary forks. Since I don't have access to any of their code, it's not much I can learn from them. If people with insider knowledge on them can chime in, perhaps we can work together -- collaboration is very welcome. We're not interested in perpetuating the idea that a CS needs to go through the FDW mechanism. Even if there's a lot of simplicity of implementation, it's almost certain to introduce too many limitations. Simply switching all our code to use columnar storage rather than row-based storage is unlikely to go well. We're aiming at letting some columns of tables be part of a CS, while other parts would continue to be in the heap. At the same time, we're aiming at opening the way for different CS implementations instead of trying to provide a single one-size-fits-all one. There are several parts to this: 1. the CSM API 2. Cataloguing column stores 3. Query processing: rewriter, optimizer, executor I think another important point is about the format of column stores, in Page format used by index/heap and how are they organised? The Column Store Manager API Since we want to have pluggable implementations, we need to have a registry of store implementations. I propose we add a catalog pg_cstore_impl with OID, name, and a bunch of function references to open a store, getvalue from it, getrows (to which we pass a qual and get a bunch of tuple IDs back), putvalue. This is in line with our procedural language support. One critical detail is what will be used to identify a heap row when talking to a CS implementation. There are two main possibilities: 1. use CTIDs 2. use some logical tuple identifier Using CTIDs is simpler. One disadvantage is that every UPDATE of a row needs to let the CS know about the new location of the tuple, so that the value is known associated with the new tuple location as well as the old. This needs to happen even if the value of the column itself is not changed. Isn't this somewhat similar to index segment? Will the column store obey snapshot model similar to current heap tuples, if so will it derive the transaction information from heap tuple? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 03:47:06PM -0300, Alvaro Herrera wrote: Bruce Momjian wrote: http://www.postgresql.org/developer/core/ After going over this a few times, there is one thing that strikes me that nobody has mentioned: the list of tasks mentioned there has one that's completely unlike the others. These are related to human relations: Acting as a conduit for confidential communication. Making policy announcements. Managing permissions for commits, infrastructure, etc. Handling disciplinary issues. Making difficult decisions when consensus is lacking. while this one is highly technical: Coordinating release activities. Quite so. Deciding it's time for a release requires the same knowledge and skills as deciding it's time to commit patch P, yet we have a special-case decision procedure. A release does require people acting in concert for a span of a few days, but that precise scheduling is work for an administrative assistant, not work befitting -core. It seems that only this last one is where most people seem to have a problem. I wonder if it makes sense to create a separate group that handles release activites -- the release team. I think the decision to initiate or revoke release scheduling belongs in the same forum as patch development, usually -hackers or -security. We'd need to pick a way to clearly signal the discussion's conclusion, analogous to how a pushed commit unambiguously disposes a patch proposal. The balance of coordinating release activities is mechanical, and -packagers seems adequate for 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] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
On Thu, Jun 11, 2015 at 2:20 PM, Abhijit Menon-Sen a...@2ndquadrant.com wrote: At 2015-06-10 13:22:27 -0400, robertmh...@gmail.com wrote: I'm not clear on which of these options you are voting for: (1) include pg_log in pg_basebackup as we do currently (2) exclude it (3) add a switch controlling whether or not it gets excluded I can live with (3), but I bet most people want (2). Thanks for spelling out the options. I strongly prefer (2), but I could live with (3) if it were done as a GUC setting. (And if that's what we decide to do, I'm willing to write up the patch.) Whether or not it's a good idea to let one's logfiles grow to 8GB, the fact that doing so breaks base backups means that being able to exclude pg_log *somehow* is more of a necessity than personal preference. On the other hand, I don't like the idea of doing (3) by adding command line arguments to pg_basebackup and adding a new option to the command. I don't think that level of flexibility is justified; it would also make it easier to end up with a broken base backup (by inadvertently excluding more than you meant to). After spending the night thinking about that, honestly, I think that we should go with (2) and keep the base backup as light-weight as possible and not bother about a GUC. (3) would need some extra intelligence to decide if some files can be skipped or not. Imagine for example --skip-files=global/pg_control or --skip-files=pg_clog (because it *is* a log file with much data), that would just corrupt silently your backup, but I guess that it is what you had in mind. In any case (3) is not worth the maintenance burden because we would need to update the things to filter each time a new important folder is added in PGDATA by a patch. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes for 9.4.4 et al
On Tue, Jun 09, 2015 at 04:31:43PM -0700, Josh Berkus wrote: First draft of the release announcement. 2015-06-12 Update Release = The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 9.4.4, 9.3.9, 9.2.13, 9.1.18 and 9.0.22. This release primarily fixes issues not successfully fixed in prior releases. It should be applied as soon as possible by any user who installed the May or June update releases. Other users should apply at the next available downtime. The urgency is the same whether or not you installed the last couple of releases: ASAP if you're on 9.3 or 9.4, next-downtime otherwise. (A site on 9.4.1 doesn't have new problems, but the old problems were urgent enough.) Crash Recovery Fixes - Earlier update releases attempted to fix an issue in PostgreSQL 9.3 and 9.4 with multixact wraparound, but failed to account for issues doing multixact cleanup during crash recovery. This could cause servers to be unable to restart after a crash. As such, all users of 9.3 and 9.4 should apply this update as soon as possible, expecially if they have already applied updates 9.3.7, 9.3.8, 9.4.2 or 9.4.3. Database administrators who used pg_upgrade to upgrade to PostgreSQL version 9.3 may find that applying the update causes an immediate autovacuum of their entire database. Please see the [release notes](http://www.postgresql.org/docs/9.4/static/release-9-3-9.html) for details and ways to change the timing of the vacuum. This also affects sites that subsequently upgraded to 9.4. (Your text doesn't rule that out, but it bears mentioning explicitly.) Also, I suggest saying a bit more about the reasons for changing the timing of the vacuum. Consider this expansion of the second paragraph (but feel free to account for those considerations other ways instead): Clusters previously upgraded to PostgreSQL 9.3 using pg_upgrade, even those clusters now running PostgreSQL 9.4 due to another upgrade, may experience an immediate autovacuum of all tables after applying this update. For large clusters, consider a controlled manual VACUUM, before updating, to better regulate the performance consequences of this critical maintenance. See the release notes for details. Cumulative Releases --- All PostgreSQL update releases are cumulative. As this update release fixes a number of problems inadvertently introduced by fixes in earlier update releases, we strongly urge users to apply this update, rather than installing less recent updates that have known issues. As this update release closes all known bugs with multixact handling, the PostgreSQL Project does not anticipate additional update releases soon. It does not close all known bugs with multixact handling; see point (2) here, for example: http://www.postgresql.org/message-id/20150608131504.gh24...@alap3.anarazel.de We nonetheless don't anticipate additional releases soon, but I would delete the last sentence. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
On 2015-06-11 PM 02:20, Abhijit Menon-Sen wrote: At 2015-06-10 13:22:27 -0400, robertmh...@gmail.com wrote: (1) include pg_log in pg_basebackup as we do currently (2) exclude it (3) add a switch controlling whether or not it gets excluded I can live with (3), but I bet most people want (2). Thanks for spelling out the options. I strongly prefer (2), but I could live with (3) if it were done as a GUC setting. (And if that's what we decide to do, I'm willing to write up the patch.) Whether or not it's a good idea to let one's logfiles grow to 8GB, the fact that doing so breaks base backups means that being able to exclude pg_log *somehow* is more of a necessity than personal preference. On the other hand, I don't like the idea of doing (3) by adding command line arguments to pg_basebackup and adding a new option to the command. I don't think that level of flexibility is justified; it would also make it easier to end up with a broken base backup (by inadvertently excluding more than you meant to). Maybe a combination of (2) and part of (3). In absence of any command line argument, the behavior is (2), to exclude. Provide an option to *include* it (-S/--serverlog). Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
At 2015-06-11 14:28:36 +0900, michael.paqu...@gmail.com wrote: After spending the night thinking about that, honestly, I think that we should go with (2) and keep the base backup as light-weight as possible and not bother about a GUC. OK. Then the patch I posted earlier should be sufficient. -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] skipping pg_log in basebackup (was Re: pg_basebackup and pg_stat_tmp directory)
On Thu, Jun 11, 2015 at 2:39 PM, Abhijit Menon-Sen a...@2ndquadrant.com wrote: At 2015-06-11 14:28:36 +0900, michael.paqu...@gmail.com wrote: After spending the night thinking about that, honestly, I think that we should go with (2) and keep the base backup as light-weight as possible and not bother about a GUC. OK. Then the patch I posted earlier should be sufficient. Btw, one thing that 010_pg_basebackup.pl does not check is actually if the files filtered by basebackup.c are included or not in the base backup. We may want to add some extra checks regarding that... Especially with your patch that filters things depending on if log_directory is an absolute path or not. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is it possible to have a fast-write Index?
On Fri, Jun 5, 2015 at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: So I really doubt that anyone would have any enthusiasm for saddling btree with a similar mechanism. It's complicated (and has been the cause of multiple bugs); it's hard to figure out when is the optimal time to flush the pending insertions; and it slows down searches in favor of making inserts cheap, which is generally not the way to bet --- if that's the tradeoff you want, why not drop the index altogether? I have seen a case that a major fact table with up to 7 indices, every 15~60 mins with large amount of data loading, and there are concurrrent seeks against indices at the same time. We can play with partitioning, or sarcrifice some application semantics, to alleviate the pressure but it is good to see if we can improve: sorting and batching insert into btree is helpful for better IO and locking behavior. So can we guard the case that hard to handle, e.g., the indices enforcing some constraints (like uniqueness), and improve the loading senario? Hint bits update is also painful in above case, but it is out of the topic here. Thanks, Qingqing -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Useless mention of RMGRDESCSOURCES in src/bin/pg_rewind/Makefile
On Tue, Jun 9, 2015 at 1:39 PM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, pg_rewind's Makefile uses RMGRDESCSOURCES: EXTRA_CLEAN = $(RMGRDESCSOURCES) xlogreader.c However this variable is defined only in the Makefile of pg_xlogdump so it has no utility in this case. Attached is a cleanup patch. Good catch. Patch applied. Thanks! 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.5 release notes
On Thu, Jun 11, 2015 at 8:24 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jun 11, 2015 at 10:20:13AM +0530, Amit Kapila wrote: On Thu, Jun 11, 2015 at 9:45 AM, Bruce Momjian br...@momjian.us wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html Thanks for writing the Release notes. Some comments: Have pg_basebackup use a tablespace mapping file, to allow for file paths of 100+ characters in length Also shall we mention about below in Migrations to 9.5 section pg_basebackup will not not work in tar mode against 9.4 and older servers, as we have introduced a new protocol option in that mode. Yes, added. The attached, applied patch has both of these mentions, and mentions 'tar' mode. Attached patch makes sense. Thanks. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] 9.5 release notes
On Thu, Jun 11, 2015 at 1:15 PM, Bruce Momjian br...@momjian.us wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html and it will eventually appear here: http://www.postgresql.org/docs/devel/static/release.html I found some minor issues. e.g. literalIDENTIFY_COMMAND/, are not logged, even when varnamelog_statements/ is set to literalall/. Typos. s/IDENTIFY_COMMAND/IDENTIFY_SYSTEM s/log_statements/log_statement para RETURN WHERE /para Looks like garbage. Add literalVERBOSE/ option to commandREINDEX/ (Fujii Masao) Could you change the author name to Sawada Masahiko? He is the author of the feature. 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] The purpose of the core team
On 06/11/2015 05:08 PM, Peter Geoghegan wrote: On Thu, Jun 11, 2015 at 9:49 AM, Andrew Dunstan and...@dunslane.net wrote: JoshB: Advocacy. There is a strong argument that does not need to be a core position. I strongly disagree with this. On the contrary, I think there is a very good argument that FOR such a position in core. +1. FYI, what do I mostly for Core is: a) Press Relations b) Corporate Relations Both of those need to be handled by a core team member, because they often fall under the confidential contact portion of Core's duties, or require nonpublic knowledge of things like security releases. I agree that general advocacy can certainly be handled outside core, and should be -- and, for that matter, is. -- 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] On columnar storage
Josh, * Josh Berkus (j...@agliodbs.com) wrote: On 06/11/2015 04:03 PM, Alvaro Herrera wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Added to: https://wiki.postgresql.org/wiki/PgCon_2015_Developer_Unconference#Topics I believe it was already there? Look for 'Native Columnar Storage'. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 9:49 AM, Andrew Dunstan and...@dunslane.net wrote: JoshB: Advocacy. There is a strong argument that does not need to be a core position. I strongly disagree with this. On the contrary, I think there is a very good argument that FOR such a position in core. +1. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On columnar storage
On 06/11/2015 04:03 PM, Alvaro Herrera wrote: We hope to have a chance to discuss this during the upcoming developer unconference in Ottawa. Here are some preliminary ideas to shed some light on what we're trying to do. Added to: https://wiki.postgresql.org/wiki/PgCon_2015_Developer_Unconference#Topics -- 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] 9.5 release notes
On Thu, Jun 11, 2015 at 02:00:08PM +0900, Amit Langote wrote: On 2015-06-11 PM 01:15, Bruce Momjian wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html and it will eventually appear here: http://www.postgresql.org/docs/devel/static/release.html I am ready to make suggested adjustments, though I am traveling for conferences for the next ten days so there might a delay in my replies. In the last section E.1.3.11.1. pgbench, there is: + listitem + para +Add information about buffer pins to applicationpg_buffercache/ +display (Andres Freund) + /para + /listitem Should be moved its own section? Oops, wrong section. Moved. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Entities created in one query not available in another in extended protocol
On 11 June 2015 at 16:56, Shay Rojansky r...@roji.org wrote: Npgsql (currently) sends Parse for the second command before sending Execute for the first one. Look no further than that. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 4:26 PM, Robert Haas robertmh...@gmail.com wrote: Timing *decisions* are not made by -core, as I've told you in the past. They are made by the packagers who do the actual work, based on suggestions from -core. You have told me that in the past, and I do not accept that it is true. The suggestions from -core are always accepted, or as near as makes no difference. So in effect, -core decides. No, that means we have some very committed people handling the release process, who are mostly able to put in the effort on the dates suggested, and on the odd occasion when they can't for some reason, we (core and packagers) figure out the best date for everyone involved. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: 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] The purpose of the core team
On 06/11/2015 08:26 AM, Robert Haas wrote: Timing *decisions* are not made by -core, as I've told you in the past. They are made by the packagers who do the actual work, based on suggestions from -core. You have told me that in the past, and I do not accept that it is true. The suggestions from -core are always accepted, or as near as makes no difference. So in effect, -core decides. Robert, This is crap. I am on the packagers list. Core always asks what people think and no it is not always accepted. There have been many times that the release has been pushed off because of resources available or new information being provided. There have also been plenty of well worn FOSS arguments on that list to make sure that everything is done in a mature, reliable and professional way. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent 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.5 release notes
On Thu, Jun 11, 2015 at 12:13:26PM -0400, Robert Haas wrote: On Thu, Jun 11, 2015 at 11:32 AM, Bruce Momjian br...@momjian.us wrote: Improve hash creation and lookup performance (Tomas Vondra, Teodor Sigaev, Tom Lane, Robert Haas) I suggest haveing two separate items. One of those is about the Hash executor node and the other is about our dynahash stuff. So they're completely different code bases. OK, can you give me some text? Do users really care which part it is in? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent 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.5 release notes
On Thu, Jun 11, 2015 at 11:32 AM, Bruce Momjian br...@momjian.us wrote: Improve hash creation and lookup performance (Tomas Vondra, Teodor Sigaev, Tom Lane, Robert Haas) I suggest haveing two separate items. One of those is about the Hash executor node and the other is about our dynahash stuff. So they're completely different code bases. -- 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] The purpose of the core team
On 06/11/2015 07:12 AM, Robert Haas wrote: Hopefully this will be helpful to people. I believe the core team is suffering from a lack of members who are involved in writing, reviewing, and committing patches. Those things are not core functions of the core team, as that charter illustrates. Bruce: Committer, maintains pg_upgrade and reviews patches here and there. Magnus: Committer, primary Windows dude and reviews patches here and there. Peter: Committer, reviews patches not only on -hackers but also -docs Tom: Enough said Dave: Committer, agreed that he doesn't do much -hackers work but I guarantee you he provides a unique perspective to the rest of his group due to his management of PgAdmin and an entire team at EDB. JoshB: Advocacy. There is a strong argument that does not need to be a core position. In short, I don't agree with you. However, the core team needs to know when it should initiate a release, and to do that it needs to understand the impact of bugs that have been fixed and bugs that have not been fixed. The recent discussion of multixacts seems to indicate that the number of core team members who had a clear understanding of the issues was zero, True but that isn't the fault of core outside of communication. The hackers, reviewers and committers of those patches should be required to communicate with core in a way that expresses the true severity of a situation so core can make a: Management decision. which I view as unfortunate. The core team also needs to make good decisions about who should be made a committer, and the people who are doing reviews and commits of other people's patches are in the best position to have an informed opinion on that topic. Which happens. As a non-core team member, I find it quite frustrating that getting a release triggered requires emailing a closed mailing list. I am not a party to all of the discussion on my request, and the other people who might know whether my request is technically sound or not are not party to that discussion either. Nor should you be. This idea that all communications must be open is a joke and shows a lack of maturity in a community. There are things that must be discussed in private for many reasons. Now, if you are saying that core isn't reaching out directly to people involved in suspect work to make a quality decision that may be one thing but I also know that happens. I disagreed with the decision to stamp 9.4.3 without waiting for b6a3444fa63519a0192447b8f9a332dddc66018f, but of course I couldn't comment on it, because it was decided in a forum in which I don't get to participate, on a thread on which I was not copied. We do not all have to agree and further there is nothing stopping you from commenting on -hackers. If enough people agree with you, core is going to listen. I realize that, because decisions about whether to release and when to release often touch on security issues, not all of this discussion can be carried on in public. But when the cone of secrecy is drawn in so tightly that excludes everyone who actually understands the technical issues related to the proposed release, we have lost our way, and do our users a disservice. We can't lose our way when this is the way it has always been. I am not sure whether the solution to this problem is to add more people to the core team, or whether the solution is to move release timing decisions and committer selection out of the core team to some newly-created group. But I believe that change is needed. If you have a people problem and you add people, you only have a bigger people problem. In terms of timing and committer selection, core does a very good job. Core has been around longer than you have and has shown great respect, maturity and management skills with most (not all of course) aspects of this community. There is one change to core that I (and I know others) would like to see: They should be serve a finite term and be elected. Sincerely, JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] The purpose of the core team
On 06/11/2015 12:29 PM, Joshua D. Drake wrote: JoshB: Advocacy. There is a strong argument that does not need to be a core position. I strongly disagree with this. On the contrary, I think there is a very good argument that FOR such a position in core. 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] The purpose of the core team
On Thu, Jun 11, 2015 at 12:13 PM, Joshua D. Drake j...@commandprompt.com wrote: This is crap. I am on the packagers list. Core always asks what people think and no it is not always accepted. There have been many times that the release has been pushed off because of resources available or new information being provided. There have also been plenty of well worn FOSS arguments on that list to make sure that everything is done in a mature, reliable and professional way. I think I've gotten sucked into arguing about something I don't really want to argue about. I agree that -core is always very polite when they asked on -packagers, and that -core would probably move the release date if subscribers to -packagers asked them so to do. I also do not dispute Dave's statement that PostgreSQL's packagers are very committed. That is certainly true. From what I can see they bend over backwards to get the job done, which is great. Now, I cannot personally recall, nor find in my email archives, an occasion on which -packagers asked for the release date to be moved. But you or others may be able to, and that is fine, but it's not what I'm unhappy about. -- 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] The purpose of the core team
On 06/11/2015 09:49 AM, Andrew Dunstan wrote: On 06/11/2015 12:29 PM, Joshua D. Drake wrote: JoshB: Advocacy. There is a strong argument that does not need to be a core position. I strongly disagree with this. On the contrary, I think there is a very good argument that FOR such a position in core. In the past, absolutely. However we have a very strong and powerful advocacy network now including two non-profits that are heavily involved. That said, I am not advocating that the the position be removed as much as stating that in my opinion it isn't necessary. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 6:29 PM, Joshua D. Drake j...@commandprompt.com wrote: On 06/11/2015 07:12 AM, Robert Haas wrote: Hopefully this will be helpful to people. I believe the core team is suffering from a lack of members who are involved in writing, reviewing, and committing patches. Those things are not core functions of the core team, as that charter illustrates. Bruce: Committer, maintains pg_upgrade and reviews patches here and there. Magnus: Committer, primary Windows dude and reviews patches here and there. Not sure that's a fair title at this point. Both Andrew and Michael seem to be doing more of that than me these days, for example. (I do review patches here and there, but not as much as I'd like) Peter: Committer, reviews patches not only on -hackers but also -docs Tom: Enough said Dave: Committer, agreed that he doesn't do much -hackers work but I guarantee you he provides a unique perspective to the rest of his group due to his management of PgAdmin and an entire team at EDB. Dave is not and never was a committer on the actual postgresql code - only on other subprojects. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] The purpose of the core team
On 06/11/2015 10:10 AM, Magnus Hagander wrote: Magnus: Committer, primary Windows dude and reviews patches here and there. Not sure that's a fair title at this point. Both Andrew and Michael seem to be doing more of that than me these days, for example. (I do review patches here and there, but not as much as I'd like) Peter: Committer, reviews patches not only on -hackers but also -docs Tom: Enough said Dave: Committer, agreed that he doesn't do much -hackers work but I guarantee you he provides a unique perspective to the rest of his group due to his management of PgAdmin and an entire team at EDB. Dave is not and never was a committer on the actual postgresql code - only on other subprojects. Thank you for the clarification. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] The purpose of the core team
On Thu, Jun 11, 2015 at 12:29 PM, Joshua D. Drake j...@commandprompt.com wrote: However, the core team needs to know when it should initiate a release, and to do that it needs to understand the impact of bugs that have been fixed and bugs that have not been fixed. The recent discussion of multixacts seems to indicate that the number of core team members who had a clear understanding of the issues was zero, True but that isn't the fault of core outside of communication. The hackers, reviewers and committers of those patches should be required to communicate with core in a way that expresses the true severity of a situation so core can make a: Management decision. I feel I've been making an honest and sincere effort do that with limited success. If you're confident that that is my fault rather than a sign of any possible problem with core, then I certainly respect your right to hold that opinion. As a non-core team member, I find it quite frustrating that getting a release triggered requires emailing a closed mailing list. I am not a party to all of the discussion on my request, and the other people who might know whether my request is technically sound or not are not party to that discussion either. Nor should you be. This idea that all communications must be open is a joke and shows a lack of maturity in a community. There are things that must be discussed in private for many reasons. You are arguing against a straw man, since I explicitly said they should not be. What I know, though, is that over the last four weeks, four committers and one other contributor worked round the clock for days to fix MultiXact bugs and test the fixes, and after that, it emerged that (at least as far as I can tell) nobody from core was even reading the -hackers thread closely enough to understand what problems we were actually fixing, or even the long commit message I wrote explaining it. I think it's silly to argue that there is no need for any overlap between the set of people who know why we need to do a release and the set of people deciding when to do it, but if I am in the minority, then so be it. I disagreed with the decision to stamp 9.4.3 without waiting for b6a3444fa63519a0192447b8f9a332dddc66018f, but of course I couldn't comment on it, because it was decided in a forum in which I don't get to participate, on a thread on which I was not copied. We do not all have to agree and further there is nothing stopping you from commenting on -hackers. If enough people agree with you, core is going to listen. It doesn't do much good when you only find out about the decision after it has been made. There is one change to core that I (and I know others) would like to see: They should be serve a finite term and be elected. In the rest of the email, you seemed to be arguing that there were no problems with core and that everything is working great. Here you are saying perhaps some change would be helpful and constructive for the project. I agree with the latter position, but not the former. On the question of whether that means elections or something else, I don't know what is best. I am honestly not trying to completely overturn the apple cart here. Obviously, many things that core has done - and is doing - for the project have worked out very well. The fact that things are not perfect is, as you say, to be expected. And certainly I appreciate the time that everyone puts into this project, which for the core team members adds up to a whole lot of time over very many years. Nevertheless, our release scheduling has been sluggish; Andres mentioned to me one occasion on which it took, IIRC, two months before we did a release of a fix for a data-corrupting multixact bug, which I think is too long; and there was a gap of more than 6 months between 9.3.5 and 9.3.6, which IMHO is too long even if no individual top-priority issue was fixed during that time. The fact that the core team (and the packagers!) are very dedicated is not a reason not to talk about these problems and how to fix them. I don't believe that we need to completely change the current system in order to make things better, but I don't believe that we need to change nothing, either. -- 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] 9.5 release notes
Bruce Momjian br...@momjian.us writes: On Thu, Jun 11, 2015 at 05:16:07PM +1200, David Rowley wrote: Would you also be able to mention something about f15821e and d222585 ? I am going to defer to Tom on that. I have added optimizer changes in the past but he didn't feel it made sense unless there was some user-visible change. I'd be inclined to document both of those. We mentioned outer join removal when it was first added, in 9.0, so making a significant improvement in it seems worthy of mention also. Both of these things are user visible to the extent that they affect EXPLAIN output. I'm not sure whether we need to document the semantic hazard that the second commit message worries about. 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] 9.5 release notes
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 11, 2015 at 11:32 AM, Bruce Momjian br...@momjian.us wrote: Improve hash creation and lookup performance (Tomas Vondra, Teodor Sigaev, Tom Lane, Robert Haas) I suggest haveing two separate items. One of those is about the Hash executor node and the other is about our dynahash stuff. So they're completely different code bases. As far as 4a14f13a0 goes, I would think that ought to be mentioned under Source Code since it's a change in a rather widely used API. I doubt that the performance aspect of it is really all that exciting to end users, but third-party modules calling the dynahash code would care. The hash join changes are a completely different 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] 9.5 release notes
On Thu, Jun 11, 2015 at 12:14 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jun 11, 2015 at 12:13:26PM -0400, Robert Haas wrote: On Thu, Jun 11, 2015 at 11:32 AM, Bruce Momjian br...@momjian.us wrote: Improve hash creation and lookup performance (Tomas Vondra, Teodor Sigaev, Tom Lane, Robert Haas) I suggest haveing two separate items. One of those is about the Hash executor node and the other is about our dynahash stuff. So they're completely different code bases. OK, can you give me some text? Do users really care which part it is in? One item should say something like: Improve hash join performance. The Teodor/Tom thing should say something like: Improve the performance of various in-memory hash tables. In particular, this can improve the performance of bitmap index scans. I assume users would view those as separate things. -- 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] [GENERAL] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
On Wed, Jun 10, 2015 at 7:16 PM, Noah Misch n...@leadboat.com wrote: On Mon, Jun 08, 2015 at 03:15:04PM +0200, Andres Freund wrote: One more thing: Our testing infrastructure sucks. Without writing C code it's basically impossible to test wraparounds and such. Even if not particularly useful for non-devs, I really think we should have functions for creating burning xids/multixacts in core. Or at least in some extension. +1. This keeps coming up, so it's worth maintaining a verified and speedy implementation. +1 from me as well. Also, I've pretty much given up on testing this area myself, because of the issue pointed out here: http://www.postgresql.org/message-id/CAMkU=1wbi5afhytawdkawease_mc00i4y_7ojhp1y-8sgci...@mail.gmail.com I think this is the same issue as part of Andres' point 1. It is pretty frustrating and futile to test wrap around when the database doesn't live long enough to wrap around under the high-stress conditions. I had thought that all changes to ShmemVariableCache except nextXid should be WAL logged at the time they occur, not just at the next checkpoint. But that wouldn't fix the problem, as the change to ShmemVariableCache has to be transactional with the change to pg_database. So it would have to be WAL logged inside the commit record or any transaction which changes pg_database. Cheers, Jeff
Re: [HACKERS] The purpose of the core team
On 06/11/2015 10:20 AM, Robert Haas wrote: True but that isn't the fault of core outside of communication. The hackers, reviewers and committers of those patches should be required to communicate with core in a way that expresses the true severity of a situation so core can make a: Management decision. I feel I've been making an honest and sincere effort do that with limited success. I have no disagreement with this statement. Bruce, you and I have all been advocating slowing down a bit (when it comes to the recent releases), we are obviously in the minority. Instead of a huge thread of complaining from a bunch of people how about we just say, These are the productive steps I would like to see Here are a few of mine: 1. I would like to see core elected to terms. I think the terms should be multi-year but no more than 2 or 3 years. 2. I would like to see more transparent discussion from core. This is a fine line. We shouldn't be talking about potential security issues publicly. On the other hand there is question about whether or not core had any business putting out the statement on the Russian conference. Note: I am not saying whether I agree or disagree with the statement. I am only talking about whether or not it was appropriate for core to handle it. 3. I would like to see core institute a different release policy. I think something similar to Ubuntu would be a big boon for us. 4. I would like to see core be a strictly technical committee. I think that advocacy and such with guidance from the community including core should be reflective of the community as a whole. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes for 9.4.4 et al
On 06/10/2015 11:35 PM, Noah Misch wrote: On Tue, Jun 09, 2015 at 04:31:43PM -0700, Josh Berkus wrote: First draft of the release announcement. Noah, thank you for corrections! -- 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] The purpose of the core team
Bruce Momjian wrote: There has been some confusion by old and new community members about the purpose of the core team, and this lack of understanding has caused some avoidable problems. Therefore, the core team has written a core charter and published it on our website: http://www.postgresql.org/developer/core/ Hopefully this will be helpful to people. After going over this a few times, there is one thing that strikes me that nobody has mentioned: the list of tasks mentioned there has one that's completely unlike the others. These are related to human relations: Acting as a conduit for confidential communication. Making policy announcements. Managing permissions for commits, infrastructure, etc. Handling disciplinary issues. Making difficult decisions when consensus is lacking. while this one is highly technical: Coordinating release activities. It seems that only this last one is where most people seem to have a problem. I wonder if it makes sense to create a separate group that handles release activites -- the release team. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Cancel race condition
On Tue, Jun 9, 2015 at 4:42 AM, Shay Rojansky r...@roji.org wrote: Ah, OK - I wasn't aware that cancellation was actually delivered as a regular POSIX signal... You're right about the lack of guarantees then. In that case, I'm guessing not much could be do to guarantee sane cancellation behavior... I do understand the best effort idea around cancellations. However, it seems different to say we'll try our best and the cancellation may not be delivered (no bad consequences except maybe performance), and to say we'll try our best but the cancellation may be delivered randomly to any query you send from the moment you send the cancellation. The second makes it very difficult to design a 100% sane, deterministic application... Any plans to address this in protocol 4? Would you have any further recommendations or guidelines to make the situation as sane as possible? I guess I could block any new SQL queries while a cancellation on that connection is still outstanding (meaning that the cancellation connection hasn't yet been closed). As you mentioned this wouldn't be a 100% solution since it would only cover signal sending, but better than nothing? Blocking new queries seems like a good idea. Note that the entire transaction (whether single-statement or multi-statement) will be aborted, or at least the currently-active subtransaction, not just the current query. If you're using single-statement transactions I guess there is not much practical difference, but if you are using multi-statement transactions the application kind of needs to be aware of this, since it needs to know that any work it did got rolled back, and everything's going to fail up until the current (sub)transaction is rolled back. -- 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] The purpose of the core team
On 06/11/2015 11:47 AM, Alvaro Herrera wrote: After going over this a few times, there is one thing that strikes me that nobody has mentioned: the list of tasks mentioned there has one that's completely unlike the others. These are related to human relations: Acting as a conduit for confidential communication. Making policy announcements. Managing permissions for commits, infrastructure, etc. Handling disciplinary issues. Making difficult decisions when consensus is lacking. while this one is highly technical: Coordinating release activities. It seems that only this last one is where most people seem to have a problem. I wonder if it makes sense to create a separate group that handles release activites -- the release team. De-facto, this is Packagers. Which is maybe not the best system, but it's what we're doing now. -- 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] The purpose of the core team
On Thu, Jun 11, 2015 at 2:50 PM, Josh Berkus j...@agliodbs.com wrote: On 06/11/2015 11:47 AM, Alvaro Herrera wrote: After going over this a few times, there is one thing that strikes me that nobody has mentioned: the list of tasks mentioned there has one that's completely unlike the others. These are related to human relations: Acting as a conduit for confidential communication. Making policy announcements. Managing permissions for commits, infrastructure, etc. Handling disciplinary issues. Making difficult decisions when consensus is lacking. while this one is highly technical: Coordinating release activities. It seems that only this last one is where most people seem to have a problem. I wonder if it makes sense to create a separate group that handles release activites -- the release team. De-facto, this is Packagers. Which is maybe not the best system, but it's what we're doing now. The release process has multiple parts: 1. Deciding that we need to do a release, either because $BUG is really bad or because we have security fixes to release or because enough time has gone by. 2. Updating translations and time zones and release notes and stamping version numbers and building tarballs. 3. Packaging and releasing tarballs. 4. Writing and publicizing the release announcement. #3 happens on pgsql-packagers and AFAICT it works fine. The problems are primarily with #1, and sometimes with #2 to the extent that Tom and Peter pretty much do them every time, so if they're not available, nobody else can step in. I have no complaints about #4. -- 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] 9.5 release notes
On 06/10/2015 09:50 PM, Amit Kapila wrote: Also shall we mention about below in Migrations to 9.5 section pg_basebackup will not not work in tar mode against 9.4 and older servers, as we have introduced a new protocol option in that mode. AFAIK, pg_basebackup has never worked across versions. So there's no reason for this note. -- 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] 9.5 release notes
On Thu, Jun 11, 2015 at 8:56 PM, Josh Berkus j...@agliodbs.com wrote: On 06/10/2015 09:50 PM, Amit Kapila wrote: Also shall we mention about below in Migrations to 9.5 section pg_basebackup will not not work in tar mode against 9.4 and older servers, as we have introduced a new protocol option in that mode. AFAIK, pg_basebackup has never worked across versions. So there's no reason for this note. It has. The resulting backup has not been usable cross version, but pg_basebackup itself has. Though not always, and I'm not sure we've ever claimed it was supported, but it has worked. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] The purpose of the core team
Robert Haas wrote: The release process has multiple parts: 1. Deciding that we need to do a release, either because $BUG is really bad or because we have security fixes to release or because enough time has gone by. 2. Updating translations and time zones and release notes and stamping version numbers and building tarballs. 3. Packaging and releasing tarballs. 4. Writing and publicizing the release announcement. #3 happens on pgsql-packagers and AFAICT it works fine. The problems are primarily with #1, and sometimes with #2 to the extent that Tom and Peter pretty much do them every time, so if they're not available, nobody else can step in. I have no complaints about #4. I am familiar with the part of #2 that Peter does, so I could do that in case of need. Not sure about the tzdata updates, but I expect that it should be reasonably straightforward. Stamping version numbers and building tarballs are tasks now scripted, so I think any well-documented release officer could do them also. Of that bunch, writing the release notes seems the most difficult. I think #1 is the part that we seem to have the most trouble with. It seems easily fixable: establish a new mailing list for that task (say pgsql-release) and get all the current -core in there, plus the set of active committers. That group would handle tasks #1 and #2 above. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Why does replication need the old history file?
Hackers, Sequence of events: 1. PITR backup of server on timeline 2. 2. Restored the backup to a new server, new-master. 3. Restored the backup to another new server, new-replica. 4. Started and promoted new-master (now on Timeline 3). 5. Started new-replica, connecting over streaming to new-master. 6. Get error message: 2015-06-11 12:24:14.503 PDT,,,7465,,5579e05e.1d29,1,,2015-06-11 12:24:14 PDT,,0,LOG,0,fetching timeline history file for timeline 2 from primary server, 2015-06-11 12:24:14.503 PDT,,,7465,,5579e05e.1d29,2,,2015-06-11 12:24:14 PDT,,0,FATAL,XX000,could not receive timeline history file from the primary server: ERROR: could not open file pg_xlog/0002.history: No such file or directory Questions: A. Why does the replica need 0002.history? Shouldn't it only need 0003.history? B. Did something change in this regard in 9.3.6, 9.3.7 or 9.3.8? It was working in our previous setup, on 9.3.5, although that could have just been that the history file hadn't been removed from the backups yet. -- 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] DBT-3 with SF=20 got failed
On 06/11/15 16:54, Tom Lane wrote:
Tomas Vondra tomas.von...@2ndquadrant.com writes:
Interestingly, the hash code checks for INT_MAX overflows on a number of
places, but does not check for this ...
Yeah, and at least at one time there were checks to prevent the hash
table request from exceeding MaxAllocSize. Did those get removed by
somebody?
I think the problem is in this piece of code:
if ((hashtable-nbatch == 1)
(hashtable-nbuckets_optimal = INT_MAX / 2)
/* overflow protection */
(ntuples = (hashtable-nbuckets_optimal * NTUP_PER_BUCKET)))
{
hashtable-nbuckets_optimal *= 2;
hashtable-log2_nbuckets_optimal += 1;
}
ISTM it does not check against the max_pointers (that's only done in
ExecChooseHashTableSize).
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, 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] The purpose of the core team
On Thu, Jun 11, 2015 at 11:13 AM, Dave Page dp...@pgadmin.org wrote: Yes, and we have recently been discussing how best to solicit those opinions this year. Great! As a non-core team member, I find it quite frustrating that getting a release triggered requires emailing a closed mailing list. It does not, unless you're talking about a security release. You might have to prod people if they overlook an email on -hackers, but you can certainly suggest releasing updates there. I certainly can suggest it in a variety of ways on a variety of mailing lists. Getting it to happen is a different thing. Timing *decisions* are not made by -core, as I've told you in the past. They are made by the packagers who do the actual work, based on suggestions from -core. You have told me that in the past, and I do not accept that it is true. The suggestions from -core are always accepted, or as near as makes no difference. So in effect, -core decides. -- 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] 9.5 release notes
On Thu, Jun 11, 2015 at 05:16:07PM +1200, David Rowley wrote: On 11 June 2015 at 16:15, Bruce Momjian br...@momjian.us wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html Thanks Bruce. Would you also be able to mention something about f15821e and d222585 ? I am going to defer to Tom on that. I have added optimizer changes in the past but he didn't feel it made sense unless there was some user-visible change. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent 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.5 release notes
On Thu, Jun 11, 2015 at 02:16:59PM +0200, Tomas Vondra wrote: Hi, On 06/11/15 06:15, Bruce Momjian wrote: I have committed the first draft of the 9.5 release notes. You can view the output here: http://momjian.us/pgsql_docs/release-9-5.html and it will eventually appear here: http://www.postgresql.org/docs/devel/static/release.html I am ready to make suggested adjustments, though I am traveling for conferences for the next ten days so there might a delay in my replies. I wonder whether this point: * Improve hash creation performance (Tomas Vondra, Teodor Sigaev, Tom Lane) is really about and 45f6240a, 8cce08f1 and 30d7ae3c. I can't remember or find other hash-related patches I've been working on. If that's the case, it really is not about hash creation performance (except maybe partially the first commit), but about the lookup performance on the hash table. So it should rather be 'improve hash-join performance'. That is a merged item; here is the other one; Improve hash_create's API for selecting simple-binary-key hash functions. Previously, if you wanted anything besides C-string hash keys, you had to specify a custom hashing function to hash_create(). Nearly all such callers were specifying tag_hash or oid_hash; which is tedious, and rather error-prone, since a caller could easily miss the opportunity to optimize by using hash_uint32 when appropriate. Replace this with a design whereby callers using simple binary-data keys just specify HASH_BLOBS and don't need to mess with specific support functions. hash_create() itself will take care of optimizing when the key size is four bytes. This nets out saving a few hundred bytes of code space, and offers a measurable performance improvement in tidbitmap.c (which was not exploiting the opportunity to use hash_uint32 for its 4-byte keys). There might be some wins elsewhere too, I didn't analyze closely. In future we could look into offering a similar optimized hashing function for 8-byte keys. Under this design that could be done in a centralized and machine-independent fashion, whereas getting it right for keys of platform-dependent sizes would've been notationally painful before. For the moment, the old way still works fine, so as not to break source code compatibility for loadable modules. Eventually we might want to remove tag_hash and friends from the exported API altogether, since there's no real need for them to be explicitly referenced from outside dynahash.c. Teodor Sigaev and Tom Lane (Tom Lane) [4a14f13a0] 2014-12-18 13:36:36 -0500 Also, I think the list of authors is wrong. Robert Haas did a lot of work on those patches, so he should be mentioned there, and I don't remember Teodor working on this. But maybe I was working on another patch and managed to forget about it. New text: Improve hash creation and lookup performance (Tomas Vondra, Teodor Sigaev, Tom Lane, Robert Haas) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
