[HACKERS] Re: [COMMITTERS] pgsql: Add macros wrapping all usage of gcc's __attribute__.
On 2015-03-25 19:11:06 -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: Add macros wrapping all usage of gcc's __attribute__. I noticed that this commit attached pg_attribute_noreturn not only to the extern declarations, but to some actual function definitions. Unless either Oskari or I screwed up, it should just have been a 1:1 translation from previous __attribute__((noreturn)) to pg_attribute_noreturn. I looked through the commit just now and didn't see any new locations. I think this is a bad idea, because it's going to look like heck after pgindent gets through with it. Do we actually need decoration on the function definitions? Hm, I guess it should not look any worse than before? None of the locations look like they've been introduced after the last pgindent run. I only see plpgsql_yyerror, yyerror. That said, I see little reason to add the noreturn thingy to the definition and not the declaration for those. It actually looks to me like there's a declaration for replication_yyerror, but a plain yyerror is used instead in repl_scanner.l? Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] controlling psql's use of the pager a bit more
On 12/21/2014 02:22 PM, Andrew Dunstan wrote: On 11/15/2014 05:56 PM, Andrew Dunstan wrote: On 11/13/2014 11:41 AM, Andrew Dunstan wrote: On 11/13/2014 11:09 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I often get annoyed because psql is a bit too aggressive when it decides whether to put output through the pager, and the only way to avoid this is to turn the pager off (in which case your next query might dump many thousands of lines to the screen). I'd like a way to be able to specify a minumum number of lines of output before psql would invoke the pager, rather than just always using the terminal window size. Are you saying you'd want to set the threshold to *more* than the window height? Why? Because I might be quite happy with 100 or 200 lines I can just scroll in my terminal's scroll buffer, but want to use the pager for more than that. This is useful especially if I want to scroll back and see the results from a query or two ago. This patch shows more or less what I had in mind. However, there is more work to do. As Tom noted upthread, psql's calculation of the number of lines is pretty bad. For example, if I do: \pset pager_min_lines 100 select * from generate_series(1,50); the pager still gets invoked, which is unfortunate to say the least. So I'm going to take a peek at that. The over-eager invocation of the pager due to double counting of lines got fixed recently, so here's a slightly updated patch for a pager_min_lines setting, including docco. The assigned reviewer hasn't done a review and hasn't responded to email. If there are no other comments I propose to commit this shortly. 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] Error with index on unlogged table
Hello, At Thu, 26 Mar 2015 18:50:24 +0100, Andres Freund and...@2ndquadrant.com wrote in 20150326175024.gj...@alap3.anarazel.de I think the problem here is that the *primary* makes no such assumptions. Init forks are logged via stuff like smgrwrite(index-rd_smgr, INIT_FORKNUM, BTREE_METAPAGE, .. i.e. the data is written out directly to disk, circumventing shared_buffers. It's pretty bad that we don't do the same on the standby. For master I think we should just add a bit to the XLOG_FPI record saying the data should be forced out to disk. I'm less sure what's to be done in the back branches. Flushing every HEAP_NEWPAGE record isn't really an option. The problem exists only for INIT_FORKNUM. So I suppose it is enough to check forknum to decide whether to sync immediately. Specifically for this instance, syncing buffers of INIT_FORKNUM at the end of XLOG_FPI block in xlog_redo fixed the problem. The another (ugly!) solution sould be syncing only buffers for INIT_FORKNUM and is BM_DIRTY in ResetUnlogggedRelations(op = UNLOGGED_RELATION_INIT). This is catching-all-at-once solution though it is a kind of reversion of fast promotion. But buffers to be synced here should be pretty few. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] trying to study how sorting works
On Thu, Mar 26, 2015 at 10:44 PM, hitesh ramani hiteshram...@hotmail.com wrote: Hello devs, I'm trying to understand how sorting exactly works in Postgres, I've understood that there are two sorting mechanisms depending on the size of the data, one being qsort which is initiated if workmem is 1024 kb and the other being external sort which is initiated in the other case. I tried to find out more material to understand how it exactly works but I'm unable to find any help material. Jim has answered this already. Moreover, I'm trying to study the code using gdb by attaching it to the pg_backend_pid and having a breakpoint at raw_parser, from where I start analyzing. Any help material or resources would be really appreciated. In case of sort specifically, fire a query with an ORDER BY and break at ExecSort. Step through this function and its minions. Try range of sizes of results i.e. size of row * number of rows to understand the dynamics. Thanks. --Hitesh -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
[HACKERS] trying to study how sorting works
Hello devs, I'm trying to understand how sorting exactly works in Postgres, I've understood that there are two sorting mechanisms depending on the size of the data, one being qsort which is initiated if workmem is 1024 kb and the other being external sort which is initiated in the other case. I tried to find out more material to understand how it exactly works but I'm unable to find any help material. Moreover, I'm trying to study the code using gdb by attaching it to the pg_backend_pid and having a breakpoint at raw_parser, from where I start analyzing. Any help material or resources would be really appreciated. Thanks. --Hitesh
Re: [HACKERS] Error with index on unlogged table
On 2015-03-26 15:13:41 +0100, Andres Freund wrote: On 2015-03-26 13:55:22 +, Thom Brown wrote: I still, however, have a problem with the separate and original issue of: # insert into utest (thing) values ('moomoo'); ERROR: index utest_pkey contains unexpected zero page at block 0 HINT: Please REINDEX it. I don't see why the user should need to go re-indexing all unlogged tables each time a standby is promoted. The index should just be empty and ready to use. There's definitely something rather broken here. Investigating. As far as I can see this has been broken at least since the introduction of fast promotion. WAL replay will update the init fork in shared memory, but it'll not be guaranteed to be flushed to disk when the reset happens. d3586fc8a et al. then also made it possible to hit the issue without fast promotion. To hit the issue there may not be a restartpoint (requiring a checkpoint on the primary) since the creation of the unlogged table. I think the problem here is that the *primary* makes no such assumptions. Init forks are logged via stuff like smgrwrite(index-rd_smgr, INIT_FORKNUM, BTREE_METAPAGE, (char *) metapage, true); if (XLogIsNeeded()) log_newpage(index-rd_smgr-smgr_rnode.node, INIT_FORKNUM, BTREE_METAPAGE, metapage, false); /* * An immediate sync is required even if we xlog'd the page, because the * write did not go through shared_buffers and therefore a concurrent * checkpoint may have moved the redo pointer past our xlog record. */ smgrimmedsync(index-rd_smgr, INIT_FORKNUM); i.e. the data is written out directly to disk, circumventing shared_buffers. It's pretty bad that we don't do the same on the standby. For master I think we should just add a bit to the XLOG_FPI record saying the data should be forced out to disk. I'm less sure what's to be done in the back branches. Flushing every HEAP_NEWPAGE record isn't really an option. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index-only scans for GiST.
On 03/02/2015 12:43 AM, Heikki Linnakangas wrote: On 02/27/2015 04:19 PM, Anastasia Lubennikova wrote: I add MemoryContext listCxt to avoid memory leak. listCxt is created once in gistrescan (only for index-only scan plan ) and reseted when scan of the leaf page is finished. I do not sure if the problem was completely solved, so I wait for feedback. Yeah, I think that solves it. On further testing, there was actually still a leak with kNN-searches. Fixed. I spent a little time cleaning this up. I reverted that pageData change so that it's an array again, put back the gist_indexonly.sql and expected output files that were missing from your latest version, removed a couple of unused local variables that gcc complained about. I refactored gistFetchTuple a bit, because it was doing IMHO quite bogus things with NULLs. It was passing NULLs to the opclass' fetch function, but it didn't pass the isNull flag correctly. I changed it so that the fetch function is not called at all for NULLs. I think this is pretty close to being committable. I'll make a round of editorializing over the docs, and the code comments as well. The opr_sanity regression test is failing, there's apparently something wrong with the pg_proc entries of the *canreturn functions. I haven't looked into that yet; could you fix that? I have pushed this, after fixing the opr_sanity failure, some bug fixes, and documentation and comment cleanup. Thanks for the patch! - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewind in contrib
On Mar 26, 2015 4:20 AM, Vladimir Borodin r...@simply.name wrote: 26 марта 2015 г., в 7:32, Michael Paquier michael.paqu...@gmail.com написал(а): On Thu, Mar 26, 2015 at 12:23 PM, Venkata Balaji N nag1...@gmail.com wrote: Test 1 : [...] If the master is crashed or killed abruptly, it may not be possible to do a rewind. Is my understanding correct ? Yep. This is mentioned in the documentation: http://www.postgresql.org/docs/devel/static/app-pgrewind.html The target server must shut down cleanly before running pg_rewind. You can start old master, wait for crash recovery to complete, stop it cleanly and then use pg_rewind. It works. Shouldn't we have a flag so it does that automatically if necessary? Test 2 : - On a successfully running streaming replication with one master and one slave, i did a clean shutdown of master - promoted slave - performed some operations (data changes) on newly promoted slave and did a clean shutdown - Executed pg_rewind on the old master to sync with the latest changes on new master. I got the below message The servers diverged at WAL position 0/A298 on timeline 1. No rewind required. I am not getting this too. In this case the master WAL visibly did not diverge from the slave WAL line. A rewind is done if the master touches new relation pages after the standby has been promoted, and before the master is shutdown. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Да пребудет с вами сила... https://simply.name/ru
Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
On 27/03/15 09:14, Peter Geoghegan wrote: On Thu, Mar 26, 2015 at 2:51 PM, Heikki Linnakangas hlinn...@iki.fi wrote: [...] Oops - You're right. I find it interesting that this didn't result in breaking my test cases. [...] Reminds of the situation where I got an A++ for a COBOL programming assignment that successfully handled the test data provided - then I found a major bug when 'idly' reviewing my code! The lecturer (also a highly experienced developer) was amused when I pointed it out to her, and she said I still deserved the A++! Cheers, Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Exposing PG_VERSION_NUM in pg_config
On 3/25/15 2:00 PM, Andres Freund wrote: On 2015-03-25 14:50:44 -0400, Tom Lane wrote: Jim Nasbyjim.na...@bluetreble.com writes: On 3/24/15 6:26 PM, Tom Lane wrote: Hm. We're all agreed that there's a use case for exposing PG_VERSION_NUM to the makefiles, but I did not hear one for adding it to pg_config; and doing the former takes about two lines whereas adding a pg_config option entails quite a lot of overhead (documentation, translatable help text, yadda yadda). So I'm not in favor of doing the latter without a much more solid case than has been made. Why else would you want the version number other than to do some kind of comparison? The question is why, if we supply the version number in a make variable, you would not just use that variable instead of having to do $(shell $(PG_CONFIG) --something). The shell version adds new failure modes, removes none, and has no redeeming social value that I can see. I think using the makefile is preferrable if you have the version dependency in the makefile. But if you don't actually use make (e.g. stuff not written in C) or you need the detection in configure or something, it's different. Exactly; not every problem can be solved by make. I know I've had to futz with the output of SELECT version() in the past, and I think I've had to do the same with pg_config --version. -- Jim Nasby, Data Architect, Blue Treble Consulting 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] trying to study how sorting works
On 3/26/15 12:14 PM, hitesh ramani wrote: Hello devs, I'm trying to understand how sorting exactly works in Postgres, I've understood that there are two sorting mechanisms depending on the size of the data, one being qsort which is initiated if workmem is 1024 kb That's incorrect. What happens is we absorb tuples in memory until we would exceed work_mem. At that point the sort switches to a tapesort. and the other being external sort which is initiated in the other case. I tried to find out more material to understand how it exactly works but I'm unable to find any help material. Take a look at utils/sort/tuplesort.c and executor/nodeSort.c (both under src/backend). Moreover, I'm trying to study the code using gdb by attaching it to the pg_backend_pid and having a breakpoint at raw_parser, from where I start analyzing. Any help material or resources would be really appreciated. FWIW, I typically set a breakpoint at exec_simple_query when I want to do that, and skip over functions until either pg_analyze_and_rewrite-parse_analyze-transformTopLevelStmt or PortalRun. The first case is useful if you're dealing with something that's going to get handled entirely during query analysis, such as casting a constant or utility commands. PortalRun is what you want when there will be some kind of output, including DML. -- Jim Nasby, Data Architect, Blue Treble Consulting 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] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
On 03/26/2015 08:00 PM, Peter Geoghegan wrote: On Wed, Mar 25, 2015 at 12:42 PM, Peter Geoghegan p...@heroku.com wrote: My next revision will have a more polished version of this scheme. I'm not going to immediately act on Robert's feedback elsewhere (although I'd like to), owing to time constraints - no reason to deny you the opportunity to review the entirely unrelated low-level speculative locking mechanism due to that. Attached revision, V3.1, implements this slightly different way of figuring out if an insertion is speculative, as discussed. We reuse t_ctid for speculatively inserted tuples. That's where the counter goes. I think that this is a significant improvement, since there is no longer any need to touch the proc array for any reason, without there being any significant disadvantage that I'm aware of. I also fixed some bitrot, and a bug with index costing (the details aren't terribly interesting - tuple width wasn't being calculated correctly). Cool. Quickly looking at the patch though - does it actually work as it is? RelationPutHeapTuple will overwrite the ctid field when the tuple is put on the page, so I don't think the correct token will make it to disk as the patch stands. Also, there are a few places where we currently check if t_ctid equals the tuple's location, and try to follow t_ctid if it doesn't. I think those need to be taught that t_ctid can also be a token. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] controlling psql's use of the pager a bit more
On 2014-12-21 14:22:10 -0500, Andrew Dunstan wrote: @@ -301,11 +301,11 @@ slashUsage(unsigned short int pager) * show list of available variables (options) from command line */ void -helpVariables(unsigned short int pager) +helpVariables(unsigned short int pager, int pager_min_lines) { FILE *output; Odd space before pager_min_lines. Without having actually tried it, it looks clean enough to me. If there's more pager options we might at some point introduce a pager options struct instead adding more options to PageOutput. But for now it seems ok enough. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSSAPI, SSPI - include_realm default
Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Tue, Dec 9, 2014 at 05:38:25PM -0500, Stephen Frost wrote: My comment that include_realm is supported back to 8.4 was because there is an expectation that a pg_hba.conf file can be used unchanged across several major releases. So when 9.5 comes out and people update their pg_hba.conf files for 9.5, those files will still work in old releases. But the time to do those updates is then, not now. The back-branches are being patched to discourage using the default because it's not a secure approach. New users start using PG all the time and so changing the existing documentation is worthwhile to ensure those new users understand. A note in the release notes for whichever minor release the change to the documentation shows up in would be a good way to make existing users aware of the change and hopefully encourage them to review their configuration. If we don't agree that the change should be made then we can discuss that, but everyone commenting so far has agreed on the change. Where are we on this? Thanks for the reminder. I've not forgotten about it and will work on crafting language in the next week or so. Thanks again! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Error with index on unlogged table
On 26 March 2015 at 00:55, Michael Paquier michael.paqu...@gmail.com wrote: On Thu, Mar 26, 2015 at 1:02 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Wed, Mar 25, 2015 at 12:46 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Wed, Mar 25, 2015 at 10:53 AM, Andres Freund and...@2ndquadrant.com wrote: Did you check whether a similar bug was made in other places of 85b506bb? Could you additionally add a regression test to this end? Seems like something worth testing. I'm checking it and adding some regression tests. I didn't found any other similar bug introduced by 85b506bb. Attached the original patch provided by Michael with some regression tests. Thanks for adding a test, this looks fine to me (did some sanity checks and tutti-quanti for people wondering). On temporary tables this was failing with an error in md.c... Thanks to both of you for fixing this. I still, however, have a problem with the separate and original issue of: # insert into utest (thing) values ('moomoo'); ERROR: index utest_pkey contains unexpected zero page at block 0 HINT: Please REINDEX it. I don't see why the user should need to go re-indexing all unlogged tables each time a standby is promoted. The index should just be empty and ready to use. -- Thom
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add macros wrapping all usage of gcc's __attribute__.
On 2015-03-26 11:27:32 -0400, Tom Lane wrote: Being the one complaining, I'll go do the legwork to clean this up. Looks good, Thanks! Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Remove fsync ON/OFF as a visible option?
On 3/25/15 8:35 PM, Jeff Janes wrote: On Wed, Mar 25, 2015 at 12:45 PM, Jim Nasby jim.na...@bluetreble.com mailto:jim.na...@bluetreble.com wrote: I see 3 settings that allow people to accidentally shoot themselves in the foot; fsync, wal_sync_method and full_page_writes. How about just grouping those 3 together with a bulk disclaimer along the lines of The following 3 settings are dangerous. Use at your own risk, and read the docs first.? That would also allow us to just remove the comments about what the settings do; if you don't already know you certainly shouldn't be touching them! :) But one of these things is not like the other. Any supported (i.e. non fatal erroring) setting of wal_sync_method *should* always be safe (although may be inefficient) if the underlying kernel, RAID controller, hard drives, and fs fulfill their pledges. It is hard to document every known liar in this regard. About the best you can do, short of pull-the-plug test on a massive scale, is to run pg_fsync_test and assuming that any result inconsistent with the RPM of the spinning rust is obviously unsafe. Unfortunately that doesn't rule out the possibility that something is both unsafe and gratuitously slow. I agree, but the reason I include this setting as dangerous is you really don't know what you're getting into once you move past fsync unless you actually study it and/or do testing. To me, that makes that setting dangerous. -- Jim Nasby, Data Architect, Blue Treble Consulting 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] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
On Thu, Mar 26, 2015 at 2:51 PM, Heikki Linnakangas hlinn...@iki.fi wrote: Attached revision, V3.1, implements this slightly different way of figuring out if an insertion is speculative, as discussed. We reuse t_ctid for speculatively inserted tuples. That's where the counter goes. I think that this is a significant improvement, since there is no longer any need to touch the proc array for any reason, without there being any significant disadvantage that I'm aware of. I also fixed some bitrot, and a bug with index costing (the details aren't terribly interesting - tuple width wasn't being calculated correctly). Cool. Quickly looking at the patch though - does it actually work as it is? The test cases pass, including jjanes_upsert, and stress tests that test for unprincipled deadlocks. But yes, I am entirely willing to believe that something that was written in such haste could be broken. My manual testing was pretty minimal. Sorry for posting a shoddy patch, but I thought it was more important to show you that this is perfectly workable ASAP. RelationPutHeapTuple will overwrite the ctid field when the tuple is put on the page, so I don't think the correct token will make it to disk as the patch stands. Oops - You're right. I find it interesting that this didn't result in breaking my test cases. I guess that not having proc array locking might have made the difference with unprincipled deadlocks, which I could not recreate (and row locking saves us from breaking UPSERT, I think - although if so the token lock would still certainly be needed for the IGNORE variant). It is interesting that this wasn't obviously broken for UPSERT, though. I think it at least suggests that when testing, we need to be more careful with taking a working UPSERT as a proxy for a working ON CONFLICT IGNORE. Also, there are a few places where we currently check if t_ctid equals the tuple's location, and try to follow t_ctid if it doesn't. I think those need to be taught that t_ctid can also be a token. I did fix at least some of those. I thought that the choke point for doing that was fairly small, entirely confined to one or two routines with heapam.c. But it would surely be better to follow your suggestion of using an invalid/magic tuple offset value to be sure that it cannot possibly occur elsewhere. And I'm still using that infomask2 bit, which is probably not really necessary. So that needs to change too. -- 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
[HACKERS] Changing ownership of simple composite incomplete?
Hackers; I spotted this after doing some owner reassignments and then dropping the old roles. It looks like using either reassign or alter type owner to, the pg_class entry stays with old rolID. Then if we drop that old role going forward pg_dump complains about invalid owner. See below test case. I did look at releast notes above 9.4 and didn't notice a fix. I observed the problem originally on a 9.1 system here. And the warning is a bit confusing since it says table rather than type. FYI Thanks $ cat q \set ON_ERROR_STOP begin; select version(); create role foo; create schema foo; set search_path to foo; prepare foo as select c.relowner, t.typowner from pg_class c join pg_type t on typrelid = c.oid and typname = 'foo'; create type foo as (a int); execute foo; alter type foo owner to foo; execute foo; reassign owned by foo to postgres; drop role foo; execute foo; alter type foo owner to postgres; execute foo; commit; \! pg_dump --schema-only --schema foo /dev/null \echo '\n\n\n' drop schema foo cascade; $ psql -fq SET BEGIN version -- PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit (1 row) CREATE ROLE CREATE SCHEMA SET PREPARE CREATE TYPE relowner | typowner --+-- 16387 |16387 (1 row) ALTER TYPE relowner | typowner --+-- 266324 | 266324 (1 row) REASSIGN OWNED DROP ROLE relowner | typowner --+-- 266324 | 10 (1 row) ALTER TYPE relowner | typowner --+-- 266324 | 10 (1 row) COMMIT pg_dump: WARNING: owner of table foo appears to be invalid psql:q:39: NOTICE: drop cascades to type foo DROP SCHEMA $ $ -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Index-only scans with btree_gist
I've pushed Anastasia's patch to support index-only scans with GiST, and it's time to add opclasses support for all the opclasses that are not lossy. I think at least all the btree_gist opclasses need to be supported, it would be pretty surprising if they didn't support index-only scans, while some more complex opclasses did. Attached is a WIP patch for that. It covers all the varlen types that btree_gist supports, and int2, int4 and oid. The rest of the fixed-width types should be just a matter of copy-pasting. I'll continue adding those, but wanted to let people know I'm working on this. - Heikki From d0a1cd0aff05ac3fdfc3d5cea4d3bc6c738ffc23 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas heikki.linnakangas@iki.fi Date: Thu, 26 Mar 2015 23:10:14 +0200 Subject: [PATCH 1/1] Add index-only scan support to btree_gist. WIP: not all datatypes are covered yet. --- contrib/btree_gist/Makefile |3 +- contrib/btree_gist/btree_gist--1.0--1.1.sql | 58 + contrib/btree_gist/btree_gist--1.0.sql | 1491 -- contrib/btree_gist/btree_gist--1.1.sql | 1522 +++ contrib/btree_gist/btree_gist.control |2 +- contrib/btree_gist/btree_int2.c |8 + contrib/btree_gist/btree_int4.c |8 + contrib/btree_gist/btree_oid.c |8 + contrib/btree_gist/btree_utils_num.c| 55 + contrib/btree_gist/btree_utils_num.h|1 + contrib/btree_gist/btree_utils_var.c| 18 + 11 files changed, 1681 insertions(+), 1493 deletions(-) create mode 100644 contrib/btree_gist/btree_gist--1.0--1.1.sql delete mode 100644 contrib/btree_gist/btree_gist--1.0.sql create mode 100644 contrib/btree_gist/btree_gist--1.1.sql diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 19a5929..9b7d61d 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -9,7 +9,8 @@ OBJS = btree_gist.o btree_utils_num.o btree_utils_var.o btree_int2.o \ btree_numeric.o $(WIN32RES) EXTENSION = btree_gist -DATA = btree_gist--1.0.sql btree_gist--unpackaged--1.0.sql +DATA = btree_gist--1.1.sql btree_gist--unpackaged--1.0.sql \ + btree_gist--1.0--1.1.sql PGFILEDESC = btree_gist - B-tree equivalent GIST operator classes REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ diff --git a/contrib/btree_gist/btree_gist--1.0--1.1.sql b/contrib/btree_gist/btree_gist--1.0--1.1.sql new file mode 100644 index 000..6b6f496 --- /dev/null +++ b/contrib/btree_gist/btree_gist--1.0--1.1.sql @@ -0,0 +1,58 @@ +/* contrib/btree_gist/btree_gist--1.0--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use CREATE EXTENSION btree_gist FROM unpackaged to load this file. \quit + +-- Index-only scan support new in 9.5. +CREATE FUNCTION gbt_var_fetch(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_oid_fetch(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int2_fetch(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +CREATE FUNCTION gbt_int4_fetch(internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C IMMUTABLE STRICT; + +ALTER OPERATOR FAMILY gist_oid_ops USING gist ADD + FUNCTION 9 (oid, oid) gbt_oid_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_int2_ops USING gist ADD + FUNCTION 9 (int2, int2) gbt_int2_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_int4_ops USING gist ADD + FUNCTION 9 (int4, int4) gbt_int4_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_text_ops USING gist ADD + FUNCTION 9 (text, text) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_bpchar_ops USING gist ADD + FUNCTION 9 (bpchar, bpchar) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_bytea_ops USING gist ADD + FUNCTION 9 (bytea, bytea) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_numeric_ops USING gist ADD + FUNCTION 9 (numeric, numeric) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_bit_ops USING gist ADD + FUNCTION 9 (bit, bit) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_vbit_ops USING gist ADD + FUNCTION 9 (varbit, varbit) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_inet_ops USING gist ADD + FUNCTION 9 (inet, inet) gbt_var_fetch (internal) ; + +ALTER OPERATOR FAMILY gist_cidr_ops USING gist ADD + FUNCTION 9 (cidr, cidr) gbt_var_fetch (internal) ; diff --git a/contrib/btree_gist/btree_gist--1.0.sql b/contrib/btree_gist/btree_gist--1.0.sql deleted file mode 100644 index c5c9587..000 --- a/contrib/btree_gist/btree_gist--1.0.sql +++ /dev/null @@ -1,1491 +0,0 @@ -/* contrib/btree_gist/btree_gist--1.0.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use CREATE EXTENSION btree_gist to load this file. \quit - -CREATE FUNCTION gbtreekey4_in(cstring) -RETURNS gbtreekey4 -AS
[HACKERS] GSoC 2015 proposal: Support for microvacuum for GiST
Hi, hackers! I want to show my proposal idea for GSoC 2015. I'm newbie to coding for PostgreSQL. http://www.google-melange.com/gsoc/proposal/public/google/gsoc2015/ivanitskiy_ilya/5629499534213120 *** *Abstract:* Currently support for microvacuum is implemented only for BTree index. But GiST index is so useful and widely used for user defined datatypes instead of btree. During index search it reads page by page. Every tuple on the page in buffer marked as dead if it doesn't visible for all transactions. Whenever before receiving next page we check dead items and mark current page as has garbage[1]. When the page gets full, all the killed items are removed by calling microvacuum[2]. *Benefits **to **the **PostgreSQL **Community* The improvement can reduce handover time during execution VACUUM. It will be useful for high-loaded system, where PostgreSQL is used. *Quantifiable results* Reducing VACUUM run time and INSERT run time for GiST. *Project **details * I'm going to implement support for microvacuum for GiST as well as it was implemented for BTree access method, just taking into account specificity of GiST. During IndexScan we get pages from GiST index and download elected page one by one into buffer. Every item from buffering page is checked for dead. If item really is dead, we write item's adress in structure BTScanOpaque http://doxygen.postgresql.org/structBTScanOpaqueData.html in function btgettuple(). Before receiving next pafe into buffer it is started _bt_killitems() http://doxygen.postgresql.org/nbtutils_8c.html#a60f25ce314f5395e6f6ae44ccbae8859, which checked dead tuples with function ItemPointerEquals http://doxygen.postgresql.org/itemptr_8c.html#ad919b8efe8c466023017a83955157d6b (). If page contains at least one dead tuple, it's marked: opaque-btpo_flags |= BTP_HAS_GARBAGE http://doxygen.postgresql.org/nbtree_8h.html#a3b7c77849276ff8617edc1f84441c230 ; MarkBufferDirtyHint http://doxygen.postgresql.org/bufmgr_8c.html#ac40bc4868e97a49a25dd8be7c98b6773 (so-currPos http://doxygen.postgresql.org/structBTScanOpaqueData.html#a70a715bd5c5db16b699f5449495b0f70 .buf http://doxygen.postgresql.org/structBTScanPosData.html#a26f8687a5a566266e4d4190a4c16a0ef, true); _bt_killitems() http://doxygen.postgresql.org/nbtutils_8c.html#a60f25ce314f5395e6f6ae44ccbae8859 is called when we want to download next page to buffer or end of IndexScan or ReScan. Further, when call chain is called btinsert() - _bt_doinsert() - _bt_findinsertloc(), if the page, which should be carried out insert, is marked by HAS_GARBAGE flag, then function _bt_vacuum_one_page() is started. It vacuum just one index page. I'm going to realize such features for GiST index. *Project Schedule * until May 31 Solve architecture questions with help of community. 1 June – 30 June First, approximate implementation supporting microvacuum for GiST. I’ve got bachelor's degree in this month so I haven’t much time to work on project. 1 July – 31 July Implementation of supporting microvacuum for GiST and testing. 1 August -15 August Final refactoring, testing and committing. *About myself* I'm last year student at Moscow Engineering and Physical Institute at department Cybernetics. *Links * 1. http://doxygen.postgresql.org/nbtutils_8c.html#a60f25ce314f5395e6f6ae44ccbae8859 2. http://doxygen.postgresql.org/nbtinsert_8c.html#a89450d93d20d3d5e2d1e68849b69ee32 3. https://wiki.postgresql.org/wiki/GSoC_2015#Core ___ Best wishes, Ivanitskiy Ilya. https://slovari.yandex.ru/newbie/en-ru
Re: [HACKERS] controlling psql's use of the pager a bit more
On 03/26/2015 11:10 AM, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Without having actually tried it, it looks clean enough to me. If there's more pager options we might at some point introduce a pager options struct instead adding more options to PageOutput. But for now it seems ok enough. My reaction is that now would be the time to do that, really. This is messing up the argument lists of what seems like a whole lot of functions, and I have little faith that this is the last argument we'll need to add. OK, this version only changes the signature of one function: PageOutput(), which instead of taking just the pager flag takes a pointer to the whole printTableOpt structure that contains both the pager and pager_min_lines settings (NULL means don't use the pager). That makes the patch smaller and less invasive, and a bit more future-proof. cheers andrew diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index a637001..a33e460 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -2236,6 +2236,18 @@ lo_import 152801 /varlistentry varlistentry + termliteralpager_min_lines/literal/term + listitem + para + If literalpager_min_lines/ is set to a number greater than the + page height, the pager program will not be called unless there are + at least this many lines of output to show. The default setting + is 0. + /para + /listitem + /varlistentry + + varlistentry termliteralrecordsep/literal/term listitem para diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 7c9f28d..e64c033 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -1071,7 +1071,8 @@ exec_command(const char *cmd, static const char *const my_list[] = { border, columns, expanded, fieldsep, fieldsep_zero, footer, format, linestyle, null, -numericlocale, pager, recordsep, recordsep_zero, +numericlocale, pager, pager_min_lines, +recordsep, recordsep_zero, tableattr, title, tuples_only, unicode_border_linestyle, unicode_column_linestyle, @@ -1265,7 +1266,7 @@ exec_command(const char *cmd, lines++; } -output = PageOutput(lineno, pset.popt.topt.pager); +output = PageOutput(lineno, (pset.popt.topt)); is_pager = true; } else @@ -2519,6 +2520,13 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet) popt-topt.pager = 1; } + /* set minimum lines for pager use */ + else if (strcmp(param, pager_min_lines) == 0) + { + if (value) + popt-topt.pager_min_lines = atoi(value); + } + /* disable (x rows) footer */ else if (strcmp(param, footer) == 0) { @@ -2640,6 +2648,13 @@ printPsetInfo(const char *param, struct printQueryOpt *popt) printf(_(Pager usage is off.\n)); } + /* show minimum lines for pager use */ + else if (strcmp(param, pager_min_lines) == 0) + { + printf(_(Pager won't be used for less than %d lines\n), + popt-topt.pager_min_lines); + } + /* show record separator for unaligned text */ else if (strcmp(param, recordsep) == 0) { @@ -2792,6 +2807,8 @@ pset_value_string(const char *param, struct printQueryOpt *popt) return pstrdup(pset_bool_string(popt-topt.numericLocale)); else if (strcmp(param, pager) == 0) return psprintf(%d, popt-topt.pager); + else if (strcmp(param, pager_min_lines) == 0) + return psprintf(%d, popt-topt.pager_min_lines); else if (strcmp(param, recordsep) == 0) return pset_quoted_string(popt-topt.recordSep.separator ? popt-topt.recordSep.separator diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 15488ff..2e7d9a4 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -1337,7 +1337,7 @@ ExecQueryUsingCursor(const char *query, double *elapsed_msec) * If query requires multiple result sets, hack to ensure that * only one pager instance is used for the whole mess */ - pset.queryFout = PageOutput(10, my_popt.topt.pager); + pset.queryFout = PageOutput(10, (my_popt.topt)); did_pager = true; } diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index ac0dc27..2da444b 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -65,7 +65,7 @@ usage(unsigned short int pager) } } - output = PageOutput(59, pager); + output = PageOutput(59, pager ? (pset.popt.topt) : NULL); fprintf(output, _(psql is the PostgreSQL interactive terminal.\n\n)); fprintf(output, _(Usage:\n)); @@ -158,7 +158,7 @@ slashUsage(unsigned short int pager) currdb = PQdb(pset.db); - output = PageOutput(103, pager); + output = PageOutput(103, pager ? (pset.popt.topt) : NULL); /* if you add/remove a line here, change the row count above */ @@ -305,7 +305,7 @@ helpVariables(unsigned short int pager) { FILE *output; - output =
[HACKERS] WIP: Split of hash index bucket
I've read proposal [1] and also major part of thread [2]. [1] encouraged me to try an alternative approach, which does not require flags at tuple level. After thinking about it for some time, I decided to code something - see attachment of this mail. (I was not sure whether I should write some kind of pseudocode, but was too eager to try whether my idea works :-) ) The idea is that new bucket is initialized as an empty primary page, whose 'hasho_nextblkno' points at the first page of the old bucket (the one being split). Then, tuples belonging to the new bucket are copied there and the link at the end of the new bucket is redirected to the 2nd page of the old bucket. And so on. When the last page of the old bucket is processed, the link from the new to the old bucket is broken. Any bucket participating in a split (whether the original one or the one being created) has a flag on its primary page, so that its split-in-progress status does not require access to the index metapage. This logic should ensure that the split can be performed in small steps, w/o blocking scans and inserts at bucket level (of course, contention still exists at page level). I'm still testing it. especially the concurrent access. There are probably bugs in the code, but it can help understand what I mean. If this split algorithm proves to be viable, an important question about the role of bucket-level locks (implemented currently as heavyweight lock of the bucket's primary page) remains. (Note that squeeze bucket functionality is not implemented in this version.) References: [1] http://www.postgresql.org/message-id/ca+tgmozymojsrfxhxq06g8jhjxqcskvdihb_8z_7nc7hj7i...@mail.gmail.com [2] http://www.postgresql.org/message-id/ca+tgmoy4x7vkyc4dawujutuboyxe2qsjf9aybhwzjxxwoc6...@mail.gmail.co -- Antonin Houska Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de, http://www.cybertec.at diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c new file mode 100644 index 24b06a5..149bbcf *** a/src/backend/access/hash/hash.c --- b/src/backend/access/hash/hash.c *** loop_top: *** 572,577 --- 572,599 opaque = (HashPageOpaque) PageGetSpecialPointer(page); Assert(opaque-hasho_bucket == cur_bucket); + /* + * If the bucket participates in a split, give up. + * + * (Unlike the metapage copy, the flags at bucket level should + * always be up-to-date.) + * + * TODO + * + * 1. Analyze if both buckets participating in the split impose + * too severe restrictions, and if it makes sense to introduce + * separate flags for old and new bucket. Also, would such a + * restricted VACUUM still make sense? + * + * 2. Consider how statistics should reflect the fact that some + * buckets are skipped because of split. + */ + if (opaque-hasho_flag LH_BUCKET_SPLIT) + { + _hash_relbuf(rel, buf); + break; + } + /* Scan each tuple in page */ maxoffno = PageGetMaxOffsetNumber(page); for (offno = FirstOffsetNumber; diff --git a/src/backend/access/hash/hashinsert.c b/src/backend/access/hash/hashinsert.c new file mode 100644 index 63aaec9..4b372ae *** a/src/backend/access/hash/hashinsert.c --- b/src/backend/access/hash/hashinsert.c *** _hash_doinsert(Relation rel, IndexTuple *** 37,42 --- 37,43 Page page; HashPageOpaque pageopaque; Size itemsz; + uint16 buckets_total; bool do_expand; uint32 hashkey; Bucket bucket; *** _hash_doinsert(Relation rel, IndexTuple *** 173,180 metap-hashm_ntuples += 1; /* Make sure this stays in sync with _hash_expandtable() */ ! do_expand = metap-hashm_ntuples ! (double) metap-hashm_ffactor * (metap-hashm_maxbucket + 1); /* Write out the metapage and drop lock, but keep pin */ _hash_chgbufaccess(rel, metabuf, HASH_WRITE, HASH_NOLOCK); --- 174,182 metap-hashm_ntuples += 1; /* Make sure this stays in sync with _hash_expandtable() */ ! buckets_total = metap-hashm_maxbucket + 1 + metap-hashm_split_count; ! do_expand = metap-hashm_split_count HASH_MAX_SPLITS ! metap-hashm_ntuples (double) metap-hashm_ffactor * buckets_total; /* Write out the metapage and drop lock, but keep pin */ _hash_chgbufaccess(rel, metabuf, HASH_WRITE, HASH_NOLOCK); diff --git a/src/backend/access/hash/hashovfl.c b/src/backend/access/hash/hashovfl.c new file mode 100644 index b775164..4345f29 *** a/src/backend/access/hash/hashovfl.c --- b/src/backend/access/hash/hashovfl.c *** *** 21,27 #include utils/rel.h - static Buffer _hash_getovflpage(Relation rel, Buffer metabuf); static uint32 _hash_firstfreebit(uint32 map); --- 21,26 *** _hash_addovflpage(Relation rel, Buffer m *** 127,133 pageopaque = (HashPageOpaque) PageGetSpecialPointer(page); nextblkno = pageopaque-hasho_nextblkno;
[HACKERS] Bug fix for missing years in make_date()
Folks, For reasons unclear, dates before the Common Era are disallowed in make_date(), even though about 2/3 of the underlying data type's range up until the present time fits that description. Please find attached a patch fixing same. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index d66f640..807 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -248,10 +248,15 @@ make_date(PG_FUNCTION_ARGS) tm.tm_mday = PG_GETARG_INT32(2); /* -* Note: we'll reject zero or negative year values. Perhaps negatives -* should be allowed to represent BC years? +* Note: Non-positive years are take to be BCE. */ - dterr = ValidateDate(DTK_DATE_M, false, false, false, tm); + if (tm.tm_year = 0) + dterr = ValidateDate(DTK_DATE_M, false, false, false, tm); + else + { + tm.tm_year = -1 * tm.tm_year; + dterr = ValidateDate(DTK_DATE_M, false, false, true, tm); + } if (dterr != 0) ereport(ERROR, diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 8923f60..73b3062 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -1191,6 +1191,12 @@ select make_date(2013, 7, 15); 07-15-2013 (1 row) +select make_date(-44, 3, 15); -- Non-positive years are BCE + make_date +--- + 03-15-0044 BC +(1 row) + select make_time(8, 20, 0.0); make_time --- @@ -1204,8 +1210,6 @@ select make_date(2013, 13, 1); ERROR: date field value out of range: 2013-13-01 select make_date(2013, 11, -1); ERROR: date field value out of range: 2013-11--1 -select make_date(-44, 3, 15); -- perhaps we should allow this sometime? -ERROR: date field value out of range: -44-03-15 select make_time(10, 55, 100.1); ERROR: time field value out of range: 10:55:100.1 select make_time(24, 0, 2.1); diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index a62e92a..e6bff17 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -279,11 +279,11 @@ select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today' -- test constructors select make_date(2013, 7, 15); +select make_date(-44, 3, 15); -- Non-positive years are BCE select make_time(8, 20, 0.0); -- should fail select make_date(2013, 2, 30); select make_date(2013, 13, 1); select make_date(2013, 11, -1); -select make_date(-44, 3, 15); -- perhaps we should allow this sometime? select make_time(10, 55, 100.1); select make_time(24, 0, 2.1); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Changing ownership of simple composite incomplete?
Jerry Sievers gsiever...@comcast.net writes: Hackers; I spotted this after doing some owner reassignments and then dropping the old roles. It looks like using either reassign or alter type owner to, the pg_class entry stays with old rolID. Then if we drop that old role going forward pg_dump complains about invalid owner. See below test case. I did look at releast notes above 9.4 and didn't notice a fix. I observed the problem originally on a 9.1 system here. Oop! I looked at release notes above 9.3.4... FYI And the warning is a bit confusing since it says table rather than type. FYI Thanks $ cat q \set ON_ERROR_STOP begin; select version(); create role foo; create schema foo; set search_path to foo; prepare foo as select c.relowner, t.typowner from pg_class c join pg_type t on typrelid = c.oid and typname = 'foo'; create type foo as (a int); execute foo; alter type foo owner to foo; execute foo; reassign owned by foo to postgres; drop role foo; execute foo; alter type foo owner to postgres; execute foo; commit; \! pg_dump --schema-only --schema foo /dev/null \echo '\n\n\n' drop schema foo cascade; $ psql -fq SET BEGIN version -- PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit (1 row) CREATE ROLE CREATE SCHEMA SET PREPARE CREATE TYPE relowner | typowner --+-- 16387 |16387 (1 row) ALTER TYPE relowner | typowner --+-- 266324 | 266324 (1 row) REASSIGN OWNED DROP ROLE relowner | typowner --+-- 266324 | 10 (1 row) ALTER TYPE relowner | typowner --+-- 266324 | 10 (1 row) COMMIT pg_dump: WARNING: owner of table foo appears to be invalid psql:q:39: NOTICE: drop cascades to type foo DROP SCHEMA $ $ -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP Patch for GROUPING SETS phase 1
Svenne == Svenne Krap sve...@krap.dk writes: Svenne I still need to check against the standard and I will run it Svenne against a non-trivival production load... hopefully I will Svenne finish up my review shortly after the weekend... Thanks for the review so far; any progress? I'm quite interested in collecting samples of realistic grouping sets queries and their performance, for use in possible further optimization work. (I don't need full data or anything like that, just this query ran in x seconds on N million rows, which is fast enough/not fast enough/too slow to be any use) Let me know if there's anything you need... -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_rewind in contrib
26 марта 2015 г., в 7:32, Michael Paquier michael.paqu...@gmail.com написал(а): On Thu, Mar 26, 2015 at 12:23 PM, Venkata Balaji N nag1...@gmail.com wrote: Test 1 : [...] If the master is crashed or killed abruptly, it may not be possible to do a rewind. Is my understanding correct ? Yep. This is mentioned in the documentation: http://www.postgresql.org/docs/devel/static/app-pgrewind.html The target server must shut down cleanly before running pg_rewind». You can start old master, wait for crash recovery to complete, stop it cleanly and then use pg_rewind. It works. Test 2 : - On a successfully running streaming replication with one master and one slave, i did a clean shutdown of master - promoted slave - performed some operations (data changes) on newly promoted slave and did a clean shutdown - Executed pg_rewind on the old master to sync with the latest changes on new master. I got the below message The servers diverged at WAL position 0/A298 on timeline 1. No rewind required. I am not getting this too. In this case the master WAL visibly did not diverge from the slave WAL line. A rewind is done if the master touches new relation pages after the standby has been promoted, and before the master is shutdown. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Да пребудет с вами сила… https://simply.name/ru
Re: [HACKERS] controlling psql's use of the pager a bit more
On 03/26/2015 11:10 AM, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Without having actually tried it, it looks clean enough to me. If there's more pager options we might at some point introduce a pager options struct instead adding more options to PageOutput. But for now it seems ok enough. My reaction is that now would be the time to do that, really. This is messing up the argument lists of what seems like a whole lot of functions, and I have little faith that this is the last argument we'll need to add. *grumble* OK , I'll take a look. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Add macros wrapping all usage of gcc's __attribute__.
Andres Freund and...@anarazel.de writes: On 2015-03-25 19:11:06 -0400, Tom Lane wrote: I think this is a bad idea, because it's going to look like heck after pgindent gets through with it. Do we actually need decoration on the function definitions? Hm, I guess it should not look any worse than before? It does, because pgindent treats pg_attribute_noreturn differently than it treated __attribute__((noreturn)). Before you'd end up with something like void __attribute__((noreturn)) plpgsql_yyerror(const char *message) { pgindent forced the __attribute__(()) bit onto its own line, whether you wrote it that way or not, but it doesn't look *too* awful. But now that becomes: void pg_attribute_noreturn plpgsql_yyerror(const char *message) { The best you can get is to manually put the noreturn back onto the void line, but you still end up with: voidpg_attribute_noreturn plpgsql_yyerror(const char *message) { So this is just ugly. Maybe we could teach pgindent not to do that, but I'm doubtful. ... That said, I see little reason to add the noreturn thingy to the definition and not the declaration for those. It actually looks to me like there's a declaration for replication_yyerror, but a plain yyerror is used instead in repl_scanner.l? Right. Also, even in the context of extern declarations, it seems to be a lot easier to get pgindent not to mess with your layout if pg_attribute_noreturn is replaced with pg_attribute_noreturn(). I see no particular reason not to add parens to the macro, do you? Being the one complaining, I'll go do the legwork to clean this up. 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] controlling psql's use of the pager a bit more
Andres Freund and...@2ndquadrant.com writes: Without having actually tried it, it looks clean enough to me. If there's more pager options we might at some point introduce a pager options struct instead adding more options to PageOutput. But for now it seems ok enough. My reaction is that now would be the time to do that, really. This is messing up the argument lists of what seems like a whole lot of functions, and I have little faith that this is the last argument we'll need to add. 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] compiler warnings in lwlock
Jeff Janes jeff.ja...@gmail.com writes: When building with LOCK_DEBUG but without casserts, I was getting unused variable warnings. I believe this is the correct way to silence them. Committed, thanks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error with index on unlogged table
On 2015-03-26 13:55:22 +, Thom Brown wrote: I still, however, have a problem with the separate and original issue of: # insert into utest (thing) values ('moomoo'); ERROR: index utest_pkey contains unexpected zero page at block 0 HINT: Please REINDEX it. I don't see why the user should need to go re-indexing all unlogged tables each time a standby is promoted. The index should just be empty and ready to use. There's definitely something rather broken here. Investigating. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers