Re: [HACKERS] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Wed, 2011-01-05 at 22:05 -0500, Bruce Momjian wrote: Robert Haas wrote: On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan and...@dunslane.net wrote: In fact it's possible now to disable FK enforcement, by disabling the triggers. It's definitely a footgun though. Just the other day I was asked how data violating the constraint could have got into the table, and caused some surprise by demonstrating how easy this was to produce. Ugh. I have read the entire pg manual and I did not recall that footgun. ?At least in MySQL disabling fk's is explicit. There is something to be said for being able to tell the database: Hey, hold my beer and watch this, it might be stupid but it is what we are going to do. I couldn't agree more, and that's a great way to put it. The user is in charge. Our job is to prevent the user from *accidentally* shooting themselves in the foot. But if a crocodile is biting their foot off and they want to fire their gun in that direction and take their chances, it's not our job to say oh, no, you might injure your foot. DBAs hate getting eaten by crocodiles. Is this a TODO? The patch I'll be submitting, or getting eaten by crocodiles? -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
On 06.01.2011 00:27, Dimitri Fontaine wrote: Magnus Hagandermag...@hagander.net writes: What about pg_streamrecv | gzip …, which has the big advantage of That's part of what I meant with easier and more useful. Well… One thing to keep in mind is that if you do compression in libpq for the transfer, and gzip the tar file in the client, that's quite inefficient. You compress the data once in the server, decompress in the client, then compress it again in the client. If you're going to write the backup to a compressed file, and you want to transfer it compressed to save bandwidth, you want to gzip it in the server to begin with. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Jan6, 2011, at 04:13 , Bruce Momjian wrote: Robert Haas wrote: On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian br...@momjian.us wrote: I think pg_dumpall would have failed with this setup too, so I don't see this as a pg_upgrade bug, nor something that I am willing to risk adding to pg_upgrade. If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should consider doing that. If we add every fix that could conceivably break a pg_dumpall restore, pg_upgrade will be less stable than it is now. I don't see why adding this should be any different. The issue is more complicted. In my situation, it's not the pg_dumpall restore that's failing, but rather pg_upgrade's attempt to install the support functions necessary for the upgrade. But in principle, you're right I think. pg_dumpall *would* fail if my database contained any objects that required superuser privileges to create, like C-language functions. If you want to argue that pg_dumpall should be doing it, that is a separate issue and not related to pg_upgrade. I think both need the fix. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Jan6, 2011, at 05:08 , Tom Lane wrote: I think an appropriate response would be to prevent ALTER DATABASE SET ROLE. I really cannot believe that there are any situations where that's a good idea. I explained up-thread why, in my situation, doing this *is* a perfectly good idea. You have neither offered an alternative solution nor argued why *exactly* this is supposed to be such a bad idea, other than the obvious it breaks pg_upgrade. Which isn't a very convincing argument that this isn't simply a pg_upgrade bug... To reiterate: I did ALTER DATABASE SET ROLE to allow different developers to work on the same database without the permission system getting into their way. Without that, objects created by one developer couldn't be modified by another, which obviously didn't work very well... Or we could take the approach somebody was just espousing about Our job is to prevent the user from *accidentally* shooting themselves in the foot. If they want to deliberately shoot themselves in the foot by hosing the login system like that, it's not our job to prevent it. But it's not our job to try to work around it, either. Nothing was hosed here. I simply solved a very real problem with the tools made available by postgres. Telling me after *years* of this solution working perfectly, and after I discovered that a *new* tool doesn't handle the situation well, that I deliberately hosed things is downright unfriendly from where I stand. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
On Wed, Jan 5, 2011 at 23:58, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * Stefan mentiond it might be useful to put some posix_fadvise(POSIX_FADV_DONTNEED) in the process that streams all the files out. Seems useful, as long as that doesn't kick them out of the cache *completely*, for other backends as well. Do we know if that is the case? Maybe have a look at pgfincore to only tag DONTNEED for blocks that are not already in SHM? It's not much of an improvement. For pages that we already have in shared memory, OS cache is mostly useless. OS cache matters for pages that *aren't* in shared memory. Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sepgsql contrib module
2011/1/6 KaiGai Kohei kai...@ak.jp.nec.com: 1. Why is sepgsql the right name for this module, as opposed to, say, selinux? We don't call the cube module cubepgsql, or the hstore module hstorepgsql. Maybe there's a reason why this case is different, but I'm not sure. In some previous cases when SELinux model was ported to other systems, its project was named as SE-(other system), such as SE-BSD, SE-X, etc... I named it according to this convention, however, it is indeed uncertain whether 'sepgsql' follows on the convention in pgsql side. OK. If there's an existing convention of calling things SE-productname then let's do the same thing here. As long as it's documented clearly it shouldn't be a problem. 2. The docs contains some references to /usr/local/pgsql/share.. Does this really mean whatever sharedir you established when you ran configure, i.e. the output of pg_config --sharedir? I hope so. Yes, it means the sharedir being configured. I found the following description at the installation.sgml. I should put this kind of mention on the documentation. | para | These instructions assume that your existing installation is under the | filename/usr/local/pgsql/ directory, and that the data area is in | filename/usr/local/pgsql/data/. Substitute your paths | appropriately. | /para Why does the user need to know about this at all? Doesn't make install put everything in the right place? 5. I'm not too sure about this one, but I think it might be good to elaborate on what we mean by respecting the system SE-Linux policy. What kinds of objects do we support checks on? What sorts of checks? What kind of access can we allow/deny? I guess these detailed description makes amount of this chapter disproportionally increase in the future version. My preference is wikipage to provide this kind of detailed information. http://wiki.postgresql.org/wiki/SEPostgreSQL The contents of above wikipage is now obsoleted, because it assumed SELinux support as a built-in feature. But it is a good time to fix up the description. I'd prefer to have it in the actual documentation. I think SE-PostgreSQL is going to need a lot of documentation. A wiki page risks getting out of date or having the wrong information for the version the user has installed. 9.1 may be quite different from 9.2, for example. I wouldn't worry about the scale of the patch too much as far as documentation goes. In reviewing previous patches you've written, I've often cut large amounts of documentation that I didn't think were important enough to be worth including (and most of the contents of the upcoming features section falls into that category). But that doesn't really take that much time, and it's certainly a lot easier to remove some extra documentation than it is to write something that's missing. Most of what you have here right now describes why you might want to use this feature, rather than what the feature actually does. If you want to start by updating the wiki page, that's fine, and may be an easier way for us to collaborate than doing it by exchanging patches. But ultimately I think it needs to go in the docs. -- 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] Intermittent buildfarm failures in sequence test
Have a look at
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ravendt=2011-01-05%2001%3A30%3A12
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=currawongdt=2011-01-06%2002%3A30%3A01
I recall seeing a couple of similar failures in the past few weeks but
can't dredge them up at the moment.
The test case is pretty simple:
CREATE SEQUENCE foo_seq;
ALTER TABLE foo_seq RENAME TO foo_seq_new;
SELECT * FROM foo_seq_new;
SELECT nextval('foo_seq_new');
SELECT nextval('foo_seq_new');
SELECT * FROM foo_seq_new;
DROP SEQUENCE foo_seq_new;
In the failure reports, all the SELECTs give the expected values except
that log_cnt in the last one is 31 instead of expected 32.
Anybody have any idea what's causing that? I can't avoid the suspicion
that this is a consequence of some replication-related hack or other,
but I haven't been keeping close enough tabs to guess just what.
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] Intermittent buildfarm failures in sequence test
I wrote: Have a look at http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ravendt=2011-01-05%2001%3A30%3A12 http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=currawongdt=2011-01-06%2002%3A30%3A01 Anybody have any idea what's causing that? Oh, never mind. The failure mechanism is explained here: http://archives.postgresql.org/pgsql-hackers/2008-08/msg01359.php and the reason it's started to fail again in the past few days is that Peter didn't bother to update sequence_1.out here: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=39b88432968a2f4c01c20948f12bf9c8e388474d Tut tut. 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] english parser in text search: support for multiple words in the same position
Do not know if this mail got lost in between or no one noticed it! On Thu, 2010-12-23 at 11:05 +0530, Sushant Sinha wrote: Just a reminder that this patch is discussing how to break url, emails etc into its components. On Mon, Oct 4, 2010 at 3:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: [ sorry for not responding on this sooner, it's been hectic the last couple weeks ] Sushant Sinha sushant...@gmail.com writes: I looked at this patch a bit. I'm fairly unhappy that it seems to be inventing a brand new mechanism to do something the ts parser can already do. Why didn't you code the url-part mechanism using the existing support for compound words? I am not familiar with compound word implementation and so I am not sure how to split a url with compound word support. I looked into the documentation for compound words and that does not say much about how to identify components of a token. IIRC, the way that that works is associated with pushing a sub-state of the state machine in order to scan each compound-word part. I don't have the details in my head anymore, though I recall having traced through it in the past. Look at the state machine actions that are associated with producing the compound word tokens and sub-tokens. I did look around for compound word support in postgres. In particular, I read the documentation and code in tsearch/spell.c that seems to implement the compound word support. So in my understanding the way it works is: 1. Specify a dictionary of words in which each word will have applicable prefix/suffix flags 2. Specify a flag file that provides prefix/suffix operations on those flags 3. flag z indicates that a word in the dictionary can participate in compound word splitting 4. When a token matches words specified in the dictionary (after applying affix/suffix operations), the matching words are emitted as sub-words of the token (i.e., compound word) If my above understanding is correct, then I think it will not be possible to implement url/email splitting using the compound word support. The main reason is that the compound word support requires the PRE-DETERMINED dictionary of words. So to split a url/email we will need to provide a list of *all possible* host names and user names. I do not think that is a possibility. Please correct me if I have mis-understood something. -Sushant. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
On Wed, Jan 5, 2011 at 23:27, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Magnus Hagander mag...@hagander.net writes: Well, I would guess that if you're streaming the WAL files in parallel while the base backup is taken, then you're able to have it all without archiving setup, and the server could still recycling them. Yes, this was mostly for the use-case of getting a single tarfile that you can actually use to restore from without needing the log archive at all. It also allows for a simpler kick-start procedure for preparing a standby, and allows to stop worrying too much about wal_keep_segments and archive servers. When do the standby launch its walreceiver? It would be extra-nice for the base backup tool to optionally continue streaming WALs until the standby starts doing it itself, so that wal_keep_segments is really deprecated. No idea how feasible that is, though. I think that's we're inventing a whole lot of complexity that may not be necessary at all. Let's do it the simple way and see how far we can get by with that one - we can always improve this for 9.2 -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET patch
On Thu, January 6, 2011 12:54, Itagaki Takahiro wrote: Here is an updated patch for MULTISET functions support. There seem to be some faulty line-endings in arrays.out that break the arrays test (on x86_64 Centos 5.4). make, make check were OK after I removed these (3 lines, from line 1370). *** /var/data1/pg_stuff/pg_sandbox/pgsql.multiset/src/test/regress/expected/arrays.out 2011-01-06 17:05:33.0 +0100 --- /var/data1/pg_stuff/pg_sandbox/pgsql.multiset/src/test/regress/results/arrays.out 2011-01-06 17:08:47.0 +0100 *** *** 1367,1375 SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[1, NULL], ARRAY[1, 2] SUBMULTISET OF ARRAY[3, NULL]; ! submultiset_of | submultiset_of ^M ! +^M ! | f^M (1 row) SELECT ARRAY[1, NULL] SUBMULTISET OF ARRAY[1, NULL]; --- 1367,1375 SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[1, NULL], ARRAY[1, 2] SUBMULTISET OF ARRAY[3, NULL]; ! submultiset_of | submultiset_of ! + ! | f (1 row) SELECT ARRAY[1, NULL] SUBMULTISET OF ARRAY[1, NULL]; == Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Something fishy about the current Makefiles
Whilst fooling around with GIN, I have repeatedly observed that doing make in src/backend/access/gin, followed by make install-bin in src/backend, fails to rebuild the postgres executable --- it just installs the existing one. A second execution of make install-bin does notice that postgres is out of date and rebuilds it. This procedure for rebuilding after changing one or two .c files has always worked for me before. I can't avoid the suspicion that the recent changes to make things more parallel-friendly broke something. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
On Thu, 2011-01-06 at 09:30 +0900, Hitoshi Harada wrote: Robert Haas originally began to propose the idea of type interface to get together three of KNN-GIST, range type and window frame issue. For KNN-GIST, it was committed by extending pg_amop without considering others and range type will be as well. Not getting them together might be the answer. We may end up combining all of these concepts into type interfaces later. Now that we have multiple potential users of type interfaces, it will be easier to design type interfaces to work well for all of them. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Something fishy about the current Makefiles
On Thu, Jan 6, 2011 at 11:57 AM, Tom Lane t...@sss.pgh.pa.us wrote: Whilst fooling around with GIN, I have repeatedly observed that doing make in src/backend/access/gin, followed by make install-bin in src/backend, fails to rebuild the postgres executable --- it just installs the existing one. A second execution of make install-bin does notice that postgres is out of date and rebuilds it. This procedure for rebuilding after changing one or two .c files has always worked for me before. I can't avoid the suspicion that the recent changes to make things more parallel-friendly broke something. I've noticed something like this as well, but haven't been able to figure out exactly what is going wrong. -- 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] WIP: Range Types
On Wed, 2011-01-05 at 12:07 -0800, Jeff Davis wrote: The current design for range types doesn't ask for add or subtract. Although it might be interesting to try to use such an interface for range types, it introduces a lot of complexity and makes it easier to cause subtle problems (consider that addition of timestamps and intervals is not commutative). A consequence of this design is that some generic range functions, like length or distance would need to rely on the polymorphism of + and - to work. I'm also not sure if a constructor like range(start, offset) returns anyrange could be made to work generically at all, because the start and offset may be two different types (and a function that takes ANYELEMENT requires that all ANYELEMENT arguments are the same type). Does anyone see a problem with that? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
On Thu, Jan 6, 2011 at 12:32 PM, Jeff Davis pg...@j-davis.com wrote: On Wed, 2011-01-05 at 12:07 -0800, Jeff Davis wrote: The current design for range types doesn't ask for add or subtract. Although it might be interesting to try to use such an interface for range types, it introduces a lot of complexity and makes it easier to cause subtle problems (consider that addition of timestamps and intervals is not commutative). A consequence of this design is that some generic range functions, like length or distance would need to rely on the polymorphism of + and - to work. I'm also not sure if a constructor like range(start, offset) returns anyrange could be made to work generically at all, because the start and offset may be two different types (and a function that takes ANYELEMENT requires that all ANYELEMENT arguments are the same type). Does anyone see a problem with that? Seems like you could make people who want that write range(start, start+offset) instead without too much pain. -- 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] Something fishy about the current Makefiles
Robert Haas robertmh...@gmail.com writes: On Thu, Jan 6, 2011 at 11:57 AM, Tom Lane t...@sss.pgh.pa.us wrote: Whilst fooling around with GIN, I have repeatedly observed that doing make in src/backend/access/gin, followed by make install-bin in src/backend, fails to rebuild the postgres executable --- it just installs the existing one. A second execution of make install-bin does notice that postgres is out of date and rebuilds it. This procedure for rebuilding after changing one or two .c files has always worked for me before. I can't avoid the suspicion that the recent changes to make things more parallel-friendly broke something. I've noticed something like this as well, but haven't been able to figure out exactly what is going wrong. I'm not entirely sure either, but it looks like the first upper make updates the objfiles.txt file in src/backend/access and then the second one realizes it has to rebuild postgres. Something about multi-level dependencies isn't quite right. 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Florian Pflug wrote: On Jan6, 2011, at 04:13 , Bruce Momjian wrote: Robert Haas wrote: On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian br...@momjian.us wrote: I think pg_dumpall would have failed with this setup too, so I don't see this as a pg_upgrade bug, nor something that I am willing to risk adding to pg_upgrade. If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should consider doing that. If we add every fix that could conceivably break a pg_dumpall restore, pg_upgrade will be less stable than it is now. I don't see why adding this should be any different. The issue is more complicted. In my situation, it's not the pg_dumpall restore that's failing, but rather pg_upgrade's attempt to install the support functions necessary for the upgrade. But in principle, you're right I think. pg_dumpall *would* fail if my database contained any objects that required superuser privileges to create, like C-language functions. Right, it was only the pg_upgrade support functions that failed first. If you want to argue that pg_dumpall should be doing it, that is a separate issue and not related to pg_upgrade. I think both need the fix. Actually, pg_dump would need to be doing it, so would need a line in every pg_dump file with a RESET SESSION AUTHORIZATION, but because the fact that the command actually reset the username suprised many of us, you would also need an SQL command stating why it is there. And at that point, it seems like complete overkill. Also, remember, pg_upgrade does as liittle as possible (like me :-) ) and relies as much as possible on the existing Postgres facilities to improve its reliability and reduce the churn needed for each new major release. As far as telling you what database you failed in, pg_upgrade can't because it blindly runs the pg_dumpall file through psql and just exits on _any_ error, hence the failure you saw, but we don't know what database you were in when the failure happened. We would need to modify psql to report the database in the error message. Looking at your use case of ALTER DATABASE SET, shouldn't you be using the new default schema object permission feature? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Robert Haas wrote: On Wed, Jan 5, 2011 at 11:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Or we could take the approach somebody was just espousing about Our job is to prevent the user from *accidentally* shooting themselves in the foot. I don't see how you can compare those two cases with a straight face. In the FOREIGN KEY NOT ENFORCED case, there is an explicit piece of syntax by means of which the user is asking for the dangerous behavior. In this case, the user made a settings change which was allowed by the system and solved his problem, and then pg_upgrade broke. If he had typed ALTER DATABASE .. SET ROLE .. BREAK PG_UPGRADE, the two cases would be comparable. Or if we failed to enforce foreign keys by default, that'd be comparable, too. How exactly is the user supposed to know that ALTER DATABASE .. SET ROLE is a bad idea? You've repeatedly made remarks about deliberately hosing the login system, but you've offered no evidence that the user deliberately hosed anything. Changed the behavior? Well, yeah. And fixed his problem, too! I even sympathize with his use case. Hosed? Well, maybe. It worked for him, until he tried to run pg_upgrade. Deliberately hosed, like he did it just to break things? Doesn't seem that way. Your argument rests on the presumption that the user should have known better than to execute a command which didn't produce an error and did solve his problem. Perhaps that's a reasonable argument in some cases - a user might be reasonably expected to foresee that setting work_mem to 100GB could cause problems even if it happens to fix the immediate issue, based on the description of the parameter - but neither you nor anyone else on this thread have offered more than hand-waving to explain how the user was supposed to know that it was unwise, or even to substantiate your position that it WAS unwise. Well, if everyone who logs in gets the same username, you can easily conclude that trying to dump/restore the database will cause problems if you have objects in there that are not owned by that user. I now realize the pg_upgrade problem was that it requires super-user objects. You could argue that requiring the ability for a super-user to do things in every database is either reasonable or overly-restrictive. I am not sure which it is, but I do know pg_upgrade requires it. Does anything else require super-user in every database. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql expanded auto
Peter Eisentraut wrote: I have often found myself wanting that psql automatically switch between normal and \x mode depending on the width of the output. Would others find this useful? Attached is a crude demo patch. Enable with \pset expanded auto. It is a TODO: Add auto-expanded mode so expanded output is used if the row length is wider than the screen width. Consider using auto-expanded mode for backslash commands like \df+. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DISCARD ALL ; stored procedures
Greetings, Looking at discard all, I was a bit suprised that 'DISCARD PLANS;' doesn't clear out cached stored procedures. To be honest, that's one of the main reasons I'd see to use it. I thought there had been some discussion in the archives related to invalidating stored procedure plans due to catalog or other changes, I would have thought it'd be possible to hook into that to do the same on a DISCARD PLANS;. Thoughts? Is there an issue doing that? It certainly seems like it'd be a lot better than what he current documentation requires: When necessary, the cache can be flushed by starting a fresh database session. Maybe we could use 'DISCARD PLPLANS;' or something, if people feel there needs to be a seperate way to clear those. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote: Well, if everyone who logs in gets the same username, you can easily conclude that trying to dump/restore the database will cause problems if you have objects in there that are not owned by that user. I can't, and neither could Florian. I'm not sure why this is so obvious to you and Tom. Unless I've made some catastrophic *manual* change to the system catalogs, like nuking pg_proc, I expect dump and restore to just work. pg_dump's job is to emit a series of commands that will work. Every time I run across a case where it doesn't, I'm violently annoyed, because it's happened to me as a user and it feels like a bug every time. Florian is probably made of a bit sterner stuff than the typical user, but a typical user doesn't go Oh, gee, dump and restore didn't work, I guess that setting I installed in there six years ago must actually be something that the developers never intended for me to do. First they cuss, and then they blame us for not being able to dump the database that we let them create, and then if they're really ticked they go use some other product. When someone actually takes the time to troubleshoot what broke and let us know, the only correct response from our end is to say thanks, we'll work on making that less confusing, not well that was a stupid thing to do. I now realize the pg_upgrade problem was that it requires super-user objects. You could argue that requiring the ability for a super-user to do things in every database is either reasonable or overly-restrictive. I am not sure which it is, but I do know pg_upgrade requires it. Does anything else require super-user in every database. Monitoring and/or management applications of any sort, I would assume. -- 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Robert Haas wrote: On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote: Well, if everyone who logs in gets the same username, you can easily conclude that trying to dump/restore the database will cause problems if you have objects in there that are not owned by that user. I can't, and neither could Florian. I'm not sure why this is so obvious to you and Tom. Unless I've made some catastrophic *manual* change to the system catalogs, like nuking pg_proc, I expect dump and restore to just work. pg_dump's job is to emit a series of commands that will work. Every time I run across a case where it doesn't, I'm violently annoyed, because it's happened to me as a user and it feels like a bug every time. Florian is probably made of a bit sterner stuff than the typical user, but a typical user doesn't go Oh, gee, dump and restore didn't work, I guess that setting I installed in there six years ago must actually be something that the developers never intended for me to do. First they cuss, and then they blame us for not being able to dump the database that we let them create, and then if they're really ticked they go use some other product. When someone actually takes the time to troubleshoot what broke and let us know, the only correct response from our end is to say thanks, we'll work on making that less confusing, not well that was a stupid thing to do. Well, we usually tell people to restore as super-user, particularly pg_dumpall, but in this case, it is impossible. Certainly pg_upgrade requires it, which is the root of the problem. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Thu, Jan 6, 2011 at 3:54 PM, Bruce Momjian br...@momjian.us wrote: Well, we usually tell people to restore as super-user, particularly pg_dumpall, but in this case, it is impossible. Certainly pg_upgrade requires it, which is the root of the problem. True. Although it's not really impossible, it just requires one additional step that we don't currently perform. -- 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] DISCARD ALL ; stored procedures
On Thu, Jan 6, 2011 at 3:20 PM, Stephen Frost sfr...@snowman.net wrote: Greetings, Looking at discard all, I was a bit suprised that 'DISCARD PLANS;' doesn't clear out cached stored procedures. To be honest, that's one of the main reasons I'd see to use it. I thought there had been some discussion in the archives related to invalidating stored procedure plans due to catalog or other changes, I would have thought it'd be possible to hook into that to do the same on a DISCARD PLANS;. Thoughts? Is there an issue doing that? It certainly seems like it'd be a lot better than what he current documentation requires: When necessary, the cache can be flushed by starting a fresh database session. Maybe we could use 'DISCARD PLPLANS;' or something, if people feel there needs to be a seperate way to clear those. this is a problem. under what circumstances would you want to discard them and why? the main problem I see with cached plpgsql plans is interactions with search_path -- but DISCARD might not be the best way to attack that problem. There might be other reasons though. 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] Patch to add a primary key using an existing index
On Thu, Dec 9, 2010 at 2:48 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Gurjeet Singh singh.gurj...@gmail.com writes:
But I still hold a bias towards renaming the index to match constraint
name
(with a NOTICE), rather than require that the constraint name match the
index name, because the constraint name is optional and when it is not
provided system has to generate a name and we have to rename the index
anyway to maintain consistency.
No. If the constraint name is not specified, we should certainly use
the existing index name, not randomly rename it.
Attached is the updated patch with doc changes and test cases. An overview
of the patch is in order:
The new command syntax is
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
PRIMARY KEY USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];
ALTER TABLE table_name
ADD [CONSTRAINT constraint_name]
UNIQUE USING INDEX index_name
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE
];
The index should be a unique index, and it should not be an expressional or
partial index. The included test cases exercise a few other cases.
If the constraint name is provided, then index is renamed to that with a
NOTICE, else the index name is used as the constraint name.
I have consciously disallowed the ability to specify storage_parameters
using the WITH clause, if somebody thinks it is wise to allow that and is
needed, I can do that.
Git branch: https://github.com/gurjeet/postgres/tree/constraint_with_index
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
constraint_using_index.patch.gz
Description: GNU Zip compressed data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DISCARD ALL ; stored procedures
* Merlin Moncure (mmonc...@gmail.com) wrote: this is a problem. under what circumstances would you want to discard them and why? the main problem I see with cached plpgsql plans is interactions with search_path -- but DISCARD might not be the best way to attack that problem. There might be other reasons though. interaction w/ search_path (or, rather, lack of respect for it..) is exactly the issue here for me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] DISCARD ALL ; stored procedures
On Thu, Jan 6, 2011 at 4:30 PM, Stephen Frost sfr...@snowman.net wrote: * Merlin Moncure (mmonc...@gmail.com) wrote: this is a problem. under what circumstances would you want to discard them and why? the main problem I see with cached plpgsql plans is interactions with search_path -- but DISCARD might not be the best way to attack that problem. There might be other reasons though. interaction w/ search_path (or, rather, lack of respect for it..) is exactly the issue here for me. this has been discussed a couple of times -- a plausible alternative might be to adjust the plan caching mechanism to organize the plan cache around search_path. that way you get a separate plan per search_path instance. discard has zero backwards compatibility issues but has one big problem -- if you are using combination of connection pooling, lots of plpgsql and search_path manipulation, you take a big performance hit. in other words, even if you can discard everything., do you really want to? 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] DISCARD ALL ; stored procedures
* Merlin Moncure (mmonc...@gmail.com) wrote: this has been discussed a couple of times -- a plausible alternative might be to adjust the plan caching mechanism to organize the plan cache around search_path. that way you get a separate plan per search_path instance. That would certainly be fine for me. To be honest, I feel like I've even suggested that in the past, somewhere. discard has zero backwards compatibility issues but has one big problem -- if you are using combination of connection pooling, lots of plpgsql and search_path manipulation, you take a big performance hit. in other words, even if you can discard everything., do you really want to? I don't see how this can be an unnecessary performance hit. You might argue that I should redesign things to not work this way, but that's a whole different discussion. At the moment the options are: - switch to using execute - force a full database reconnect To get the 'correct' behavior. If it's performance vs. correctness, you can guess what I'm going to vote for, however, in this case, I can't see how either of the other options would perform better than a discard-like approach. If people are already using 'discard all;' then they're already throwing away their plans for prepared queries, it strikes me as unlikely that they'd have an issue with also getting rid of stored procedure plans. If they do, they could certainly use the individual 'discard' statements instead (presuming we implement this with a new discard argument). Thanks Stephen signature.asc Description: Digital signature
Re: [HACKERS] DISCARD ALL ; stored procedures
On Thu, Jan 6, 2011 at 5:22 PM, Stephen Frost sfr...@snowman.net wrote: If it's performance vs. correctness, you can guess what I'm going to vote for, however, in this case, I can't see how either of the other options would perform better than a discard-like approach. If people are already using 'discard all;' then they're already throwing away their plans for prepared queries, it strikes me as unlikely that they'd have an issue with also getting rid of stored procedure plans. If they do, they could certainly use the individual 'discard' statements instead (presuming we implement this with a new discard argument). If DISCARD ALL doesn't flush this stuff, I'd consider that an outright bug. Does it? -- 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] Streaming base backups
On 05.01.2011 15:54, Magnus Hagander wrote: Attached is an updated streaming base backup patch, based off the work that Heikki started. ... I've implemented a frontend for this in pg_streamrecv, based on the assumption that we wanted to include this in bin/ for 9.1 - and that it seems like a reasonable place to put it. This can obviously be moved elsewhere if we want to. Hmm, is there any point in keeping the two functionalities in the same binary, taking the base backup and streaming WAL to an archive directory? Looks like the only common option between the two modes is passing the connection string, and the verbose flag. A separate pg_basebackup binary would probably make more sense. That code needs a lot more cleanup, but I wanted to make sure I got the backend patch out for review quickly. You can find the current WIP branch for pg_streamrecv on my github page at https://github.com/mhagander/pg_streamrecv, in the branch baserecv. I'll be posting that as a separate patch once it's been a bit more cleaned up (it does work now if you want to test it, though). Looks like pg_streamrecv creates the pg_xlog and pg_tblspc directories, because they're not included in the streamed tar. Wouldn't it be better to include them in the tar as empty directories at the server-side? Otherwise if you write the tar file to disk and untar it later, you have to manually create them. It would be nice to have an option in pg_streamrecv to specify the backup label to use. An option to stream the tar to stdout instead of a file would be very handy too, so that you could pipe it directly to gzip for example. I realize you get multiple tar files if tablespaces are used, but even if you just throw an error in that case, it would be handy. * Suggestion from Heikki: perhaps at some point we're going to need a full bison grammar for walsender commands. Maybe we should at least start using the lexer; we're not quite there to need a full-blown grammar yet, but even a lexer might help. BTW, looking at the WAL-streaming side of pg_streamrecv, if you start it from scratch with an empty target directory, it needs to connect to postgres database, to run pg_current_xlog_location(), and then reconnect in replication mode. That's a bit awkward, there might not be a postgres database, and even if there is, you might not have the permission to connect to it. It would be much better to have a variant of the START_REPLICATION command at the server-side that begins streaming from the current location. Maybe just by leaving out the start-location parameter. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
On Thu, Jan 6, 2011 at 23:57, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 05.01.2011 15:54, Magnus Hagander wrote: Attached is an updated streaming base backup patch, based off the work that Heikki started. ... I've implemented a frontend for this in pg_streamrecv, based on the assumption that we wanted to include this in bin/ for 9.1 - and that it seems like a reasonable place to put it. This can obviously be moved elsewhere if we want to. Hmm, is there any point in keeping the two functionalities in the same binary, taking the base backup and streaming WAL to an archive directory? Looks like the only common option between the two modes is passing the connection string, and the verbose flag. A separate pg_basebackup binary would probably make more sense. Yeah, once I broke things apart for better readability, I started leaning in that direction as well. However, if you consider the things that Dimiti mentioned about streaming at the same time as downloading, having them in the same one would make more sense. I don't think that's something for now, though.. That code needs a lot more cleanup, but I wanted to make sure I got the backend patch out for review quickly. You can find the current WIP branch for pg_streamrecv on my github page at https://github.com/mhagander/pg_streamrecv, in the branch baserecv. I'll be posting that as a separate patch once it's been a bit more cleaned up (it does work now if you want to test it, though). Looks like pg_streamrecv creates the pg_xlog and pg_tblspc directories, because they're not included in the streamed tar. Wouldn't it be better to include them in the tar as empty directories at the server-side? Otherwise if you write the tar file to disk and untar it later, you have to manually create them. Yeah, good point. Originally, the tar code (your tar code, btw :P) didn't create *any* directories, so I stuck it in there. I agree it should be moved to the backend patch now. It would be nice to have an option in pg_streamrecv to specify the backup label to use. Agreed. An option to stream the tar to stdout instead of a file would be very handy too, so that you could pipe it directly to gzip for example. I realize you get multiple tar files if tablespaces are used, but even if you just throw an error in that case, it would be handy. Makes sense. * Suggestion from Heikki: perhaps at some point we're going to need a full bison grammar for walsender commands. Maybe we should at least start using the lexer; we're not quite there to need a full-blown grammar yet, but even a lexer might help. Might. I don't speak flex very well, so I'm not really sure what that would mean. BTW, looking at the WAL-streaming side of pg_streamrecv, if you start it from scratch with an empty target directory, it needs to connect to postgres database, to run pg_current_xlog_location(), and then reconnect in replication mode. That's a bit awkward, there might not be a postgres database, and even if there is, you might not have the permission to connect to it. It would be much better to have a variant of the START_REPLICATION command at the server-side that begins streaming from the current location. Maybe just by leaving out the start-location parameter. Agreed. That part is unchanged from the one that runs against 9.0 though, where that wasn't a possibility. But adding something like that to the walsender in 9.1 would be good. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
2011/1/5 Magnus Hagander mag...@hagander.net: On Wed, Jan 5, 2011 at 22:58, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Magnus Hagander mag...@hagander.net writes: * Stefan mentiond it might be useful to put some posix_fadvise(POSIX_FADV_DONTNEED) in the process that streams all the files out. Seems useful, as long as that doesn't kick them out of the cache *completely*, for other backends as well. Do we know if that is the case? Maybe have a look at pgfincore to only tag DONTNEED for blocks that are not already in SHM? I think that's way more complex than we want to go here. DONTNEED will remove the block from OS buffer everytime. It should not be that hard to implement a snapshot(it needs mincore()) and to restore previous state. I don't know how basebackup is performed exactly...so perhaps I am wrong. posix_fadvise support is already in postgresql core...we can start by just doing a snapshot of the files before starting, or at some point in the basebackup, it will need only 256kB per GB of data... -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Something fishy about the current Makefiles
On Thu, 2011-01-06 at 13:53 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jan 6, 2011 at 11:57 AM, Tom Lane t...@sss.pgh.pa.us wrote: Whilst fooling around with GIN, I have repeatedly observed that doing make in src/backend/access/gin, followed by make install-bin in src/backend, fails to rebuild the postgres executable --- it just installs the existing one. A second execution of make install-bin does notice that postgres is out of date and rebuilds it. This procedure for rebuilding after changing one or two .c files has always worked for me before. I can't avoid the suspicion that the recent changes to make things more parallel-friendly broke something. I've noticed something like this as well, but haven't been able to figure out exactly what is going wrong. I'm not entirely sure either, but it looks like the first upper make updates the objfiles.txt file in src/backend/access and then the second one realizes it has to rebuild postgres. Something about multi-level dependencies isn't quite right. I had a weirdness with the dependency files a few days ago. The error was a missing delimiter : at the end of one of the files. Stopped everything cold until I added that character, then re-configured and not a hint of the earlier problem. Sounds like dependency is a common issue. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming base backups
On Wed, 2011-01-05 at 14:54 +0100, Magnus Hagander wrote: The basic implementation is: Add a new command to the replication mode called BASE_BACKUP, that will initiate a base backup, stream the contents (in tar compatible format) of the data directory and all tablespaces, and then end the base backup in a single operation. I'm a little dubious of the performance of that approach for some users, though it does seem a popular idea. One very useful feature will be some way of confirming the number and size of files to transfer, so that the base backup client can find out the progress. It would also be good to avoid writing a backup_label file at all on the master, so there was no reason why multiple concurrent backups could not be taken. The current coding allows for the idea that the start and stop might be in different sessions, whereas here we know we are in one session. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pov and tsort
Greetings hackers!
I've renamed the project fsnapshot to pov, PostgreSQL Object Version
control system. :)
I've also created a quite nifty SQL command line based utility to
perform graph/tree sorting algorithms on data in the database, without
the need to export the data to some external application.
The utility is named tsort and behaves exactly like the GNU coreutils
tool tsort (or the Perl Power Tools tool tcsort).
Since tree sorting is a quite common task in computer science, perhaps
some of you will find it useful.
Source code:
https://github.com/gluefinance/pov/blob/master/sql/schema/pov/functions/tsort.pl
Also, if I have understood everything correctly, the toposort
algorithm in pg_dump_sort.c does not does its best ensuring the
objects are created in the same order, if the oids would change or if
objects would be dropped/added.
The algorithm is probably a simple DFS (depth-first sorting) or BFS
(breadth-first sorting) without any effort made to do sorting when
selecting the successor objects.
It's probably a quite challenging task to implement the extra
necessary sorting in C, on top of the standard DFS or BFS algorithm.
I put together a small quite nifty plperl utility to do the task, as a
simple proof-of-concept and also because I thought it makes sense to
provide such a method accessible from the SQL prompt,
since tree/graph sorting is probably one of the most commonly
performed tasks in many applications dealing.
It is quite powerful with all the basic tree sorting features I could think of.
I hope you find it useful and perhaps even a bit fun :)
Here is a small example on how to use it to analyze pg_depend:
create table a ( id int not null, primary key (id));
create table aa ( id int not null, primary key (id), foreign key (id)
references a(id));
create table ab ( id int not null, primary key (id), foreign key (id)
references a(id));
create table aaa ( id int not null, primary key (id), foreign key (id)
references aa(id));
create table aab ( id int not null, primary key (id), foreign key (id)
references aa(id));
create table aba ( id int not null, primary key (id), foreign key (id)
references ab(id));
create table abb ( id int not null, primary key (id), foreign key (id)
references ab(id));
Instead of using oid, one should use unique names for each object
(composed differently based on the object type).
If doing so, the example below would render exactly the same result
even in two databases with completely different oids for the same
schema.
glue=# select oid,relname from pg_class where relname ~ '^a.?.?$';
oid | relname
---+-
52354 | a
52359 | aa
52399 | aba
52369 | ab
52389 | aab
52379 | aaa
52409 | abb
(7 rows)
Find root objects (source objects, no predecessors), sort numerically:
glue=# SELECT tsort(array_to_string(array_agg(distinct objid || ',' ||
refobjid), ','), ',', 0, 'sub {$a = $b}', 'SOURCE') FROM pg_depend;
tsort
--
--
--
--
--
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
bruce wrote: Robert Haas wrote: On Thu, Jan 6, 2011 at 1:59 PM, Bruce Momjian br...@momjian.us wrote: Well, if everyone who logs in gets the same username, you can easily conclude that trying to dump/restore the database will cause problems if you have objects in there that are not owned by that user. I can't, and neither could Florian. I'm not sure why this is so obvious to you and Tom. Unless I've made some catastrophic *manual* change to the system catalogs, like nuking pg_proc, I expect dump and restore to just work. pg_dump's job is to emit a series of commands that will work. Every time I run across a case where it doesn't, I'm violently annoyed, because it's happened to me as a user and it feels like a bug every time. Florian is probably made of a bit sterner stuff than the typical user, but a typical user doesn't go Oh, gee, dump and restore didn't work, I guess that setting I installed in there six years ago must actually be something that the developers never intended for me to do. First they cuss, and then they blame us for not being able to dump the database that we let them create, and then if they're really ticked they go use some other product. When someone actually takes the time to troubleshoot what broke and let us know, the only correct response from our end is to say thanks, we'll work on making that less confusing, not well that was a stupid thing to do. Well, we usually tell people to restore as super-user, particularly pg_dumpall, but in this case, it is impossible. Certainly pg_upgrade requires it, which is the root of the problem. We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade mode. I am unclear if that might cause some other problems though. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sepgsql contrib module
2. The docs contains some references to /usr/local/pgsql/share.. Does this really mean whatever sharedir you established when you ran configure, i.e. the output of pg_config --sharedir? I hope so. Yes, it means the sharedir being configured. I found the following description at the installation.sgml. I should put this kind of mention on the documentation. |para | These instructions assume that your existing installation is under the |filename/usr/local/pgsql/ directory, and that the data area is in |filename/usr/local/pgsql/data/. Substitute your paths | appropriately. |/para Why does the user need to know about this at all? Doesn't make install put everything in the right place? It seemed to me a convenient writing-style to inform users an installation path of file. What I like to write is showing users what path stores what files needed in installation process. If we use result of the `pg_config --sharedir` here, how about this writing style? Or, do we have any other ideas? screen $ su # SHAREDIR=`pg_config --sharedir` # semodule -u $SHAREDIR/contrib/sepgsql-regtest.pp # semodule -l : sepgsql-regtest 1.03 : /screen 5. I'm not too sure about this one, but I think it might be good to elaborate on what we mean by respecting the system SE-Linux policy. What kinds of objects do we support checks on? What sorts of checks? What kind of access can we allow/deny? I guess these detailed description makes amount of this chapter disproportionally increase in the future version. My preference is wikipage to provide this kind of detailed information. http://wiki.postgresql.org/wiki/SEPostgreSQL The contents of above wikipage is now obsoleted, because it assumed SELinux support as a built-in feature. But it is a good time to fix up the description. I'd prefer to have it in the actual documentation. I think SE-PostgreSQL is going to need a lot of documentation. A wiki page risks getting out of date or having the wrong information for the version the user has installed. 9.1 may be quite different from 9.2, for example. Indeed, wikipage might not be suitable to document for several different version. OK, I'll try to add description what you suggested above. Most of what you have here right now describes why you might want to use this feature, rather than what the feature actually does. If you want to start by updating the wiki page, that's fine, and may be an easier way for us to collaborate than doing it by exchanging patches. But ultimately I think it needs to go in the docs. The background of this wikipage is that I was persuading people this feature being worthful, so the contents tend to philosophical things rather than actual specifications. I also think wiki page allows us to brush up the documentation rather than exchanging patches effectively. I'll set up a wiki page that contains same contents with *.sgml file to revise documentation stuff to be included into the *.sgml file finally. How about this idea? Thanks, -- KaiGai Kohei kai...@ak.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] sepgsql contrib module
2011/1/6 KaiGai Kohei kai...@ak.jp.nec.com: If we use result of the `pg_config --sharedir` here, how about this writing style? Or, do we have any other ideas? I'm not sure - I'll look at your next draft more closely. The background of this wikipage is that I was persuading people this feature being worthful, so the contents tend to philosophical things rather than actual specifications. Yeah. I also think wiki page allows us to brush up the documentation rather than exchanging patches effectively. I'll set up a wiki page that contains same contents with *.sgml file to revise documentation stuff to be included into the *.sgml file finally. How about this idea? Sounds good. -- 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Bruce Momjian br...@momjian.us writes: We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade mode. I am unclear if that might cause some other problems though. I finally figured out what was really bugging me about that proposal: it's a one-shot hack for fixing one problem that could arise from non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there are not other such issues, either now or in the future? It occurs to me that a more principled way to deal with this class of problems would be to delay restoring ALTER DATABASE/ALTER ROLE settings until after everything else is done. Not sure what the implementation consequences of that would be. Ideally we'd make pg_dumpall output work that way in general, not just for pg_upgrade. 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade mode. I am unclear if that might cause some other problems though. I finally figured out what was really bugging me about that proposal: it's a one-shot hack for fixing one problem that could arise from non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there are not other such issues, either now or in the future? It occurs to me that a more principled way to deal with this class of problems would be to delay restoring ALTER DATABASE/ALTER ROLE settings until after everything else is done. Not sure what the implementation consequences of that would be. Ideally we'd make pg_dumpall output work that way in general, not just for pg_upgrade. Yep, it feels like a one-off that no one else will ever hit, and there are certainly other ALTER DATABASE SET commands that could also obstruct a restore. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] system views for walsender activity
On Wed, Jan 5, 2011 at 02:48, Simon Riggs si...@2ndquadrant.com wrote: The way I coded it was a new SRF that joins to the existing pg_stat_activity. So no initdb required, and this can also easily be included as an external module for 9.0. Please notice also that my coding of the new SRF does not have the O^2 issue you mention, which I was keen to avoid. Yeah, using SQL JOIN to avoid O(n^2) is a good idea. My only concern is that pg_stat_get_activity(NULL) might return rows that are not actually used. Is it an ignorable overhead? We should also include application name, since the user may set that in the standby for all the same reasons it is set elsewhere. Ah, we can use application_name to name each wal senders. Small point: please lets not call this pg_stat_walsender? pg_stat_replication_sent and pg_stat_replication_received would be easier for normal humans to understand. A list of proposed view names for replication master server: - pg_stat_replication - pg_stat_replication_sent - pg_stat_standby - pg_stat_walsender - pg_stat_walsender_activity We have some functions for standby server activity (pg_last_xlog_[receive|replay]_[location|timestamp]) but could have a view for them: - pg_stat_replication_received - pg_stat_walreceiver pg_stat_replication and pg_stat_standby might not be good names when we have a view for standby server because the names are not clear for master server. But if we will have a view only on master, pg_stat_replication seems to be the most understandable name. I would very much appreciate it if one of you could complete something here and commit in the next few days. That would then allow me to extend the view with sync rep specific info for monitoring and patch testing. What will we name xlog locations that have been received? We call xlog locations sent to standby as sentPtr. If we have sync rep, we will have two locations for each wal sender. For example, we can call them sent_location and sync_location. -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fixing GIN for empty/null/full-scan cases
Em 06-01-2011 21:31, Tom Lane escreveu: I think I like option #2 better. Comments? +1. -- Euler Taveira de Oliveira http://www.timbira.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] We need to log aborted autovacuums
Robert Treat wrote: This is a great use case for user level tracing support. Add a probe around these bits, and you can capture the information when you need it. Sure. I would also like a pony. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need to log aborted autovacuums
Josh Berkus wrote: Or should it perhaps be a per-table counter in pg_stat_user_tables, given your statement above? Or even a timestamp: last_autovacuum_attempt, which would record the last time autovacuum was tried. If that's fairly recent and you have a large number of dead rows, you know what kind of problem you have and can turn on debug. These are both reasonable ideas. But there was just some kickback on Tomas's keeping timestamp of the lasts stats reset patch recently, from the perspective of trying to limit per-table stats bloat. I think it's relatively easy to make a case that this situation is difficult enough to diagnose that a little bit of extra low-level logging is worthwhile. That Josh and I have both been bit by it enough to be thinking about patches to make it easier to diagnost suggests it's obviously too hard to nail down. But is this so common and difficult to recognize that it's worth making all the table stats bigger? That's a harder call. It's already possible to detect the main symptom--dead row percentage is much higher than the autovacuum threshold, but there's been no recent autovacuum. That makes me less enthusiastic that there's such a genuine need to justify the overhead of storing more table stats just to detect the same thing a little more easily. I've been playing with the Munin PG plug-in more recently, and I was just thinking of adding a dead row trend graph/threshold to it to address this general area instead. We could argue both sides of the trade-off of tracking this directly in stats for some time, and I'd never expect there to be a clear victory for either perspective. I've run into this vacuum problem a few times, but certainly less than I've run into why is the stats table so huge? -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
