Re: [HACKERS] creating CHECK constraints as NOT VALID
On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011: Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011: I think that you also need to update the constraint exclusion code (get_relation_constraints() or nearby), otherwise the planner might exclude a relation on the basis of a CHECK constraint that is not currently VALID. Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix this is quite simple. I don't have it handy right now but I'll post it soon. Here's the complete patch. psql \h says (among other things) for ALTER TABLE ADD table_constraint ADD table_constraint_using_index ADD table_constraint [ NOT VALID ] ADD table_constraint appears twice and isn't true that all table_constraint accept the NOT VALID syntax... maybe we can accpet the syntax and send an unimplemented feature message for the other table_constraints? attached, is a script with the examples i have tried: EXAMPLE 1: constraint_exclusion when using NOT VALID check constraints... and it works well, except when the constraint has been validated, it keeps ignoring it (which means i won't benefit from constraint_exclusion) until i execute ANALYZE on the table or close connection EXAMPLE 2: if i have a DOMAIN with a NOT VALID check constraint, and i use it as the new type of a column it checks the constraint -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación /* example 1 */ DROP TABLE IF EXISTS padre CASCADE; create table padre(i serial primary key, d date); create table hija_2010 () inherits (padre); create table hija_2011 () inherits (padre); insert into hija_2010(d) values ('2011-08-15'::date); insert into hija_2011(d) values ('2011-09-15'::date); alter table hija_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; alter table hija_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid; explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date; create table hija_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (padre); insert into hija_2009(d) values ('2009-06-13'); explain analyze select * from padre where d between '2011-08-01'::date and '2011-08-31'::date; explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date; alter table hija_2011 VALIDATE CONSTRAINT hija_2011_d_check; explain analyze select * from padre where d between '2009-08-01'::date and '2009-08-31'::date; /* example 2 */ create domain mes as int; create table t_mes (m mes); insert into t_mes values(13); alter domain mes add check (value between 1 and 12) not valid; create table t_mes2 (m int); insert into t_mes2 values(13); alter table t_mes2 ALTER m type mes; ERROR: value for domain mes violates check constraint mes_check -- 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] time-delayed standbys
On Wed, Jun 15, 2011 at 12:58 AM, Fujii Masao masao.fu...@gmail.com wrote: http://forge.mysql.com/worklog/task.php?id=344 According to the above page, one purpose of time-delayed replication is to protect against user mistakes on master. But, when an user notices his wrong operation on master, what should he do next? The WAL records of his wrong operation might have already arrived at the standby, so neither promote nor restart doesn't cancel that wrong operation. Instead, probably he should shutdown the standby, investigate the timestamp of XID of the operation he'd like to cancel, set recovery_target_time and restart the standby. Something like this procedures should be documented? Or, we should implement new promote mode which finishes a recovery as soon as promote is requested (i.e., not replay all the available WAL records)? i would prefer something like pg_ctl promote -m immediate that terminates the recovery -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FK NOT VALID can't be deferrable?
Hi, Testing the CHECK NOT VALID patch i found $subject... is this intended? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] use less space in xl_xact_commit patch
We don't need to be in a hurry here. As the reviewer I'm happy to give Leonardo some time, obviously no more than the end of the commit fest. If he doesn't respond at all, I'll do it, but I'd like to give him the chance and the experience if possible. Sorry I couldn't update the patch (in fact, it's more of a total-rewrite than an update). How much time do I have? Leonardo -- 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: Fast GiST index build
I've tried index tuples sorting on penalty function before buffer relocation on split. But it was without any success. Index quality becomes even worse than without sorting. The next thing I've tried is buffer relocation between all neighbor buffers. Results of first tests is much more promising. Number of page accesses during index scan is similar to those without fast index build. I'm going to hold on this approach. test=# create index test_idx on test using gist(v); NOTICE: Level step = 1, pagesPerBuffer = 406 CREATE INDEX Time: 10002590,469 ms test=# select pg_size_pretty(pg_relation_size('test_idx')); pg_size_pretty 6939 MB (1 row) test=# explain (analyze, buffers) select * from test where v @ '(0.903,0.203),(0.9,0.2)'::box; QUERY PLAN --- Bitmap Heap Scan on test (cost=4366.78..258752.22 rows=10 width=16) (actual time=1.412..2.295 rows=897 loops=1) Recheck Cond: (v @ '(0.903,0.203),(0.9,0.2)'::box) Buffers: shared hit=1038 - Bitmap Index Scan on test_idx (cost=0.00..4341.78 rows=10 width=0) (actual time=1.311..1.311 rows=897 loops=1) Index Cond: (v @ '(0.903,0.203),(0.9,0.2)'::box) Buffers: shared hit=141 Total runtime: 2.375 ms (7 rows) test=# explain (analyze, buffers) select * from test where v @ '(0.503,0.503),(0.5,0.5)'::box; QUERY PLAN --- Bitmap Heap Scan on test (cost=4366.78..258752.22 rows=10 width=16) (actual time=2.113..2.972 rows=855 loops=1) Recheck Cond: (v @ '(0.503,0.503),(0.5,0.5)'::box) Buffers: shared hit=1095 - Bitmap Index Scan on test_idx (cost=0.00..4341.78 rows=10 width=0) (actual time=2.016..2.016 rows=855 loops=1) Index Cond: (v @ '(0.503,0.503),(0.5,0.5)'::box) Buffers: shared hit=240 Total runtime: 3.043 ms (7 rows) -- With best regards, Alexander Korotkov. gist_fast_build-0.1.0.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] WIP: Fast GiST index build
On Wed, Jun 15, 2011 at 11:21 AM, Alexander Korotkov aekorot...@gmail.comwrote: I've tried index tuples sorting on penalty function before buffer relocation on split. But it was without any success. Index quality becomes even worse than without sorting. The next thing I've tried is buffer relocation between all neighbor buffers. Results of first tests is much more promising. Number of page accesses during index scan is similar to those without fast index build. I'm going to hold on this approach. test=# create index test_idx on test using gist(v); NOTICE: Level step = 1, pagesPerBuffer = 406 CREATE INDEX Time: 10002590,469 ms I forget to say that build time increases in about 40%, but it is still faster than ordinal build in about 10 times. -- With best regards, Alexander Korotkov.
Re: [HACKERS] use less space in xl_xact_commit patch
On Tue, Jun 14, 2011 at 2:31 PM, Simon Riggs si...@2ndquadrant.com wrote: Well, we certainly have the option to review and commit the patch any time up until feature freeze. However, I don't want the CommitFest application to be full of entries for patches that are not actually being worked on, because it makes it hard for reviewers to figure out which patches in a state where they can be usefully looked at. AIUI, this one is currently not, because it was reviewed three weeks ago and hasn't been updated. Yes it's true: I thought I could find the time to work on it, but I didn't. Let me know the deadline for it, and I'll see if I can make it (or I'll make it in the next commit fest). Leonardo -- 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] creating CHECK constraints as NOT VALID
On 15 June 2011 07:09, Jaime Casanova ja...@2ndquadrant.com wrote: On Tue, Jun 14, 2011 at 4:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Alvaro Herrera's message of lun jun 13 18:08:12 -0400 2011: Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011: I think that you also need to update the constraint exclusion code (get_relation_constraints() or nearby), otherwise the planner might exclude a relation on the basis of a CHECK constraint that is not currently VALID. Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix this is quite simple. I don't have it handy right now but I'll post it soon. Here's the complete patch. psql \h says (among other things) for ALTER TABLE ADD table_constraint ADD table_constraint_using_index ADD table_constraint [ NOT VALID ] ADD table_constraint appears twice and isn't true that all table_constraint accept the NOT VALID syntax... maybe we can accpet the syntax and send an unimplemented feature message for the other table_constraints? Yeah, I was just about to make the same observation about the 9.1beta docs. The 3rd line makes the 1st one redundant. Regards, Dean -- 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] procpid?
Here's the sort of thing every person who writes a monitoring tool involving pg_stat_activity goes through: 1) Hurray! I know how to see what the database is doing now! Let me try counting all the connections so I can finally figure out what to set [max_connections | work_mem | other] to. 2) Wait, some of these can be IDLE. That's not documented. I'll have to special case them because they don't really matter for my computation. 3) Seriously, there's another state for idle in a transaction? Just how many of these special values are there? [There's actually one more surprise after this] The whole thing is enormously frustrating, and it's an advocacy problem--it contributes to people just starting to become serious about using PostgreSQL lowering their opinion of its suitability for their business. If this is what's included for activity monitoring, and it's this terrible, it suggest people must not have very high requirements for that. And what you end up with to make it better is not just another few keystrokes. Here, as a common example I re-use a lot, is a decoder inspired by Munin's connection count monitoring graph: SELECT waiting, CASE WHEN current_query='IDLE' THEN true ELSE false END AS idle, CASE WHEN current_query='IDLE in transaction' THEN true ELSE false END AS idletransaction, CASE WHEN current_query='insufficient privilege' THEN false ELSE true END as visible, CASE WHEN NOT waiting AND current_query NOT IN ('IDLE', 'IDLE in transaction', 'insufficient privilege') THEN true ELSE false END AS active, procpid,current_query FROM pg_stat_activity WHERE procpid != pg_backend_pid(); What percentage of people do you think get this right? Now, what does that number go to if these states were all obviously exposed booleans? As I'm concerned, this design is fundamentally flawed as currently delivered, so the concept of breaking it doesn't really make sense. The fact that you can only figure all this decoding magic out through extensive trial and error, or reading the source code to [the database | another monitoring tool], is crazy. It's a much bigger problem than the fact that the pid column is misnamed, and way up on my list of things I'm just really tired of doing. Yes, we could just document all these mystery states to help, but they'd still be terrible. This is a database; let's expose the data in a way that it's easy to slice yourself using a database query. And if we're going to fix that--which unfortunately will be breaking it relative to those already using the current format--I figure why not bundle the procpid fix into that while we're at it. It's even possible to argue that breaking that small thing will draw useful attention to the improvements in other parts of the view. Having your monitoring query break after a version upgrade is no fun. But if investigating why reveals new stuff you didn't notice in the release notes, the changes become more discoverable, albeit in a somewhat perverse way. Putting on my stability hat instead of my make it right one, maybe this really makes sense to expose as a view with a whole new name. Make this new one pg_activity (there's no stats here anyway), keep the old one around as pg_stat_activity for a few releases until everyone has converted to the new one. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Polecat quit unexpectdly
On Jun 14, 2011, at 2:11 PM, Kevin Grittner wrote: Robert Creager robert.crea...@oracle.com wrote: Stack trace, nothing else. 3 postgres 0x00010005cafa multixact_twophase_postcommit + 74 (multixact.c:1367) 4 postgres 0x00010005deab ProcessRecords + 91 (twophase.c:1407) 5 postgres 0x00010005f78a FinishPreparedTransaction + 1610 (twophase.c:1368) If this was a checkout from more than about 7 hours ago and less than about 10 hours ago, please get a fresh copy of the source and try again. You believe it was related to the flurry of errors that popped up then. This machine updates git every 30 minutes, runs builds every 2 hours, forces HEAD every 6 hours. Later, Rob -- inline: oracle_sig_logo.gif Robert Creager, Principal Software Engineer Oracle Server Technologies 500 Eldorado Blvd, Bldg 5 Broomfield, CO, 80021 Phone: 303-272-6830 Email: robert.crea...@oracle.com inline: green-for-email-sig_0.gifOracle is committed to developing practices and products that help protect the environment -- 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: Fast GiST index build
On 15.06.2011 10:24, Alexander Korotkov wrote: On Wed, Jun 15, 2011 at 11:21 AM, Alexander Korotkov aekorot...@gmail.comwrote: I've tried index tuples sorting on penalty function before buffer relocation on split. But it was without any success. Index quality becomes even worse than without sorting. The next thing I've tried is buffer relocation between all neighbor buffers. Results of first tests is much more promising. Number of page accesses during index scan is similar to those without fast index build. I'm going to hold on this approach. test=# create index test_idx on test using gist(v); NOTICE: Level step = 1, pagesPerBuffer = 406 CREATE INDEX Time: 10002590,469 ms I forget to say that build time increases in about 40%, but it is still faster than ordinal build in about 10 times. Is this relocation mechanism something that can be tuned, for different tradeoffs between index quality and build time? In any case, it seems that we're going to need a lot of testing with different data sets to get a better picture of how this performs. But at least for now, it looks like this approach is going to be acceptable. -- 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] FK NOT VALID can't be deferrable?
On 15 June 2011 07:56, Jaime Casanova ja...@2ndquadrant.com wrote: Hi, Testing the CHECK NOT VALID patch i found $subject... is this intended? I just noticed that too, and was about to raise it as a bug. If it is intended, then it's not documented. I noticed it while browsing gram.y, and thought it looks a bit ugly having 2 almost identical code blocks, one for the normal case and one for NOT VALID. The second block doesn't have a ConstraintAttributeSpec, so won't allow any deferrable options. Aside from the ugliness of the code, we can't just add a ConstraintAttributeSpec to the second block, because that would enforce an order to these options. OTOH adding NOT VALID to ConstraintAttributeSpec is a bit invasive, since it's used in quite a few places, including CREATE TABLE, where NOT VALID is never allowed. Thoughts? Regards, Dean -- 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: Fast GiST index build
On Wed, Jun 15, 2011 at 12:03 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Is this relocation mechanism something that can be tuned, for different tradeoffs between index quality and build time? Yes, it can. I believe that it can be index parameter. In any case, it seems that we're going to need a lot of testing with different data sets to get a better picture of how this performs. Sure. My problem is that I haven't large enough reallife datasets. Picture of syntetic datasets can be unrepresentative on reallife cases. On smaller datasets that I have I actually can compare only index quality. Also, tests with large datasets takes long time especially without fast build. Probably solution is to limit cache size during testing. It should allow to measure I/O benefit even on relatively small datasets. But while I don't know now to do that on Linux. -- With best regards, Alexander Korotkov.
Re: [HACKERS] procpid?
Following this whole conversation rises the impression the topic is going to get lost in nirvana of personal preferences. Most suggestions on change for itself are likely to not cross the border of not justifying a compatibility break. I wonder, whether the actual point really is towards compatibility. On closer look this is more about a change in paradigm of system tables. Seems like those previously had been crafted with having in mind more a human reader than a programmatic user. What seems to be requested sounds more like splitting access to system information into a level that is more appropriate for programmatic use (with all those basic properties being explicit) and some level more apt for being read. E.g. I much prefer reading an IDLE in transaction on a quick glance over having to search a column and recognize a t from an f to find out whether there is a transaction pending or not. So may be we need a (new) set of tables/views that provide detailed information that is designed for programmatic use as a basic interface layer. And reconstruct the existing tables /views based on those. That would allow all required changes to coexist without braking compatibility. And it also provides an easier ground for later extensions to such information. Anybody sticking with the existing interface will not suffer incompatibility. While anybody in need of more details and better information may switch over to the new basic layer. (And I doubt adding that extra level will cause problems performance wise...) Rainer Am 15.06.2011 06:19, schrieb Robert Haas: On Tue, Jun 14, 2011 at 11:04 PM, Greg Sabino Mullane g...@turnstep.com wrote: For me, the litmus test is whether the change provides enough improvement that it outweighs the disruption when the user runs into it. For the procpid that started all of this, the clear answer is no. I'm surprised people seriously considered making this change. It's a historical accident: document and move on. I agree with you on this one... This is why I suggested a specific, useful, and commonly requested (to me at least) change to pg_stat_activity go along with this. +1. The procpid change is silly, but fixing the current_query field would be very useful. You don't know how many times my fingers have typed WHERE current_query 'IDLE' ...but I'm not even excited about this. *Maybe* it's worth adding another column, but the problem with the existing system is *entirely* cosmetic. The string chosen here is unconfusable with an actual query, so we are talking here, as with the procpid - pid proposal, ONLY about saving a few keystrokes when writing queries. That is a pretty thin justification for a compatibility break IMV. -- 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] Why polecat and colugos are failing to build back branches
On Wed, Jun 15, 2011 at 00:01, Andrew Dunstan and...@dunslane.net wrote: On 06/14/2011 05:45 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 06/13/2011 08:05 PM, Tom Lane wrote: I looked into $SUBJECT. There appear to be two distinct issues: ... I think we can be a bit more liberal about build patches than things that can affect the runtime behaviour. So +1 for fixing both of these. I've committed patches that fix these issues on my own OS X machine, though it remains to be seen whether polecat and colugos will like them. It turns out that whatever setup Robert has got with '/Volumes/High Usage/' is really *not* fully exercising the system as far as space-containing paths go, because I found bugs in all active branches when I tried to do builds and installs underneath '/Users/tgl/foo bar/'. Is it worth setting up a buildfarm critter to exercise the case on a long-term basis? If we don't, I think we can expect that it'll break regularly. (I wouldn't care to bet that the MSVC case works yet, either.) Well, OSX is just using our usual *nix paraphernalia, so if it's broken won't all such platforms probably be broken too? I'd actually bet a modest amount MSVC is less broken because it uses perl modules like File::Copy to do most of its work and so will be less prone to shell parsing breakage. Also, once installed, the vast majority of all Windows installs will be running out of c:\program files\postgresql, so we know it works at *runtime*. But yeah, it wouldn't hurt to have a bf animal do the actual testing out of such a path too. -- 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] SSI work for 9.1
On 14.06.2011 17:57, Kevin Grittner wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: I did some further changes, refactoring SkipSerialization so that it's hopefully more readable, and added a comment about the side-effects. See attached. Let me know if I'm missing something. I do think the changes improve readability. I don't see anything missing, but there's something we can drop. Now that you've split the read and write tests, this part can be dropped from the SerializationNeededForWrite function: + + /* Check if we have just become RO-safe. */ + if (SxactIsROSafe(MySerializableXact)) + { + ReleasePredicateLocks(false); + return false; + } If it's doing a write, it can't be a read-only transaction Ah, good point. Committed with that removed. -- 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] use less space in xl_xact_commit patch
On Wed, May 25, 2011 at 3:05 PM, Simon Riggs si...@2ndquadrant.com wrote: Leonardo, can you submit an updated version of this patch today that incorporates Simon's suggestion? Mmmh, maybe it was simpler than I thought; I must be missing something... patch attached How can I test it with weird stuff as subtransactions, shared cache invalidation messages...? Leonardo commitlog_lessbytes_v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Small SSI issues
On 10.06.2011 18:05, Kevin Grittner wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: * Is the SXACT_FLAG_ROLLED_BACK flag necessary? It's only set in ReleasePredicateLocks() for a fleeting moment while the function releases all conflicts and locks held by the transaction, and finally the sxact struct itself containing the flag. I think that one can go away. It had more of a point many months ago before we properly sorted out what belongs in PreCommit_CheckForSerializationFailure() and what belongs in ReleasePredicateLocks(). The point at which we reached clarity on that and moved things around, this flag probably became obsolete. Also, isn't a transaction that's already been marked for death the same as one that has already rolled back, for the purposes of detecting conflicts? Yes. We should probably ignore any marked-for-death transaction during conflict detection and serialization failure detection. As a start, anywhere there is now a check for rollback and not for this, we should change it to this. Ok, I removed the SXACT_FLAG_ROLLED_BACK flag. I also renamed the marked-for-death flag into SXACT_FLAG_DOOMED; that's a lot shorter. There may be some places this can be checked which haven't yet been identified and touched. Yeah - in 9.2. -- 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] [BUG] SSPI authentication fails on Windows when server parameter is localhost or domain name
On Wed, Jun 15, 2011 at 10:53 AM, Ahmed Shinwari ahmed.shinw...@gmail.com wrote: Hi All, I faced a bug on Windows while connecting via SSPI authentication. I was able to find the bug and have attached the patch. Details listed below; Postgres Installer: Version 9.0.4 OS: Windows Server 2008 R2/Windows 7 Bug Description: = If database Server is running on Windows ('Server 2008 R2' or 'Windows 7') with authentication mode SSPI and one try to connect from the same machine via 'psql' with server parameter as 'localhost' or 'fully qualified domain name', the database throws error; I've been able to reproduce this issue, and the patch does indeed fix it. One of our customers has also confirmed it fixed it for them. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] testing nested case-when scoping
Hello Heikki, probably I found a bug in patch: CREATE FUNCTION fx(i integer) RETURNS integer LANGUAGE plpgsql AS $$begin raise notice '%', i; return i; end;$$; CREATE FUNCTION fx1(integer) RETURNS text LANGUAGE sql AS $_$ select case $1 when 1 then 'A' else 'B' end$_$; CREATE FUNCTION fx2(text) RETURNS text LANGUAGE sql AS $_$ select case $1 when 'A' then 'a' else 'b' end$_$; CREATE TABLE foo ( a integer ); COPY foo (a) FROM stdin; 1 0 \. postgres=# select fx2(fx1(fx(a))) from foo; NOTICE: 1 ERROR: invalid expression parameter reference (1 levels up, while stack is only 1 elements deep) Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Jesper Krogh wrote: On 2011-06-15 05:01, Bruce Momjian wrote: You might remember we added a postmaster/postgres -b switch to indicate binary upgrade mode. The attached patch prevents any client without an application_name of 'binary-upgrade' from connecting to the cluster while it is binary upgrade mode. This helps prevent unauthorized users from connecting during the upgrade. This will not help for clusters that do not have the -b flag, e.g. pre-9.1. Does this seem useful? Something for 9.1 or 9.2? This idea came from Andrew Dunstan via IRC during a pg_upgrade run by Stephen Frost when some clients accidentally connected. (Stephen reran pg_upgrade successfully.) Couldn't the -b flag also imply a very strict hba.conf configuration, that essentially only lets pg_upgrade in..? Yes, it could. What rules would we use? We could prohibit non-local connections. -- 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] pg_upgrade using appname to lock out other users
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: You might remember we added a postmaster/postgres -b switch to indicate binary upgrade mode. The attached patch prevents any client without an application_name of 'binary-upgrade' from connecting to the cluster while it is binary upgrade mode. This helps prevent unauthorized users from connecting during the upgrade. This will not help for clusters that do not have the -b flag, e.g. pre-9.1. Does this seem useful? No ... that seems like a kluge. It's ugly and it's leaky. What we really ought to be doing here is fixing things so that pg_upgrade does not need to have a running postmaster in either installation, but works with some variant of standalone mode. That would actually be *safe* against concurrent connections, rather than only sorta kinda maybe safe. I keep replying to that suggestion by reminding people that pg_upgrade relies heavily on psql features, as does pg_dumpall, and recoding that in the backend will be error-prone. Also, a standalone backend does not have libpq either so how do you get values into application variables? Parse the text output? That seems like a much larger kludge. -- 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] pg_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 8:05 AM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: You might remember we added a postmaster/postgres -b switch to indicate binary upgrade mode. The attached patch prevents any client without an application_name of 'binary-upgrade' from connecting to the cluster while it is binary upgrade mode. This helps prevent unauthorized users from connecting during the upgrade. This will not help for clusters that do not have the -b flag, e.g. pre-9.1. Does this seem useful? No ... that seems like a kluge. It's ugly and it's leaky. What we really ought to be doing here is fixing things so that pg_upgrade does not need to have a running postmaster in either installation, but works with some variant of standalone mode. That would actually be *safe* against concurrent connections, rather than only sorta kinda maybe safe. I keep replying to that suggestion by reminding people that pg_upgrade relies heavily on psql features, as does pg_dumpall, and recoding that in the backend will be error-prone. Also, a standalone backend does not have libpq either so how do you get values into application variables? Parse the text output? That seems like a much larger kludge. Maybe we could do something like this. 1. pg_upgrade invokes the postmaster with --binary-upgrade=port:password 2. postmaster starts up into multi-user mode, but it does not start autovacuum and ignores pg_hba.conf, listen_addresses, and port. Instead it listens only on the localhost interface on the designated port (perhaps the port can be a filename on systems that support UNIX sockets, and it can listen only on a UNIX socket at that location instead). It refuses all connections except for those that attempt to log in with binary_upgrade as the user and the given password as the password. pg_upgrade will randomly generate a password (like C51622FA-7513-4300-A4B7-6423769276F8) and port number at the start of each run, and use that for all connections to the postmaster. As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. -- 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] procpid?
On Wed, Jun 15, 2011 at 3:34 AM, Greg Smith g...@2ndquadrant.com wrote: The whole thing is enormously frustrating, and it's an advocacy problem--it contributes to people just starting to become serious about using PostgreSQL lowering their opinion of its suitability for their business. If this is what's included for activity monitoring, and it's this terrible, it suggest people must not have very high requirements for that. Well, if we're going to start complaining about the lack of proper activity monitoring, the problems that you're talking about are just the tip of the iceberg. Don't even get me started. Putting on my stability hat instead of my make it right one, maybe this really makes sense to expose as a view with a whole new name. Make this new one pg_activity (there's no stats here anyway), keep the old one around as pg_stat_activity for a few releases until everyone has converted to the new one. Now, that's a suggestion I could very possibly get behind. Though the fact that it would leave us with pg_activity / pg_stat_replication seems less than ideal. Maybe pg_activity isn't the best name either... bikeshedding time! -- 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] procpid?
On Tue, Jun 14, 2011 at 9:50 PM, Bruce Momjian br...@momjian.us wrote: Greg Smith wrote: On 06/14/2011 06:00 PM, Tom Lane wrote: As far as Greg's proposal is concerned, I don't see how a proposed addition of two columns would justify renaming an existing column. Additions should not break any sanely-implemented application, but renamings certainly will. It's not so much justification as something that makes the inevitable complaints easier to stomach, in terms of not leaving a really bad taste in the user's mouth. My thinking is that if we're going to mess with pg_stat_activity in a way that breaks something, I'd like to see it completely refactored for better usability in the process. If code breaks and the resulting investigation by the admin highlights something new, that offsets some of the bad user experience resulting from the breakage. Also, I haven't fully worked whether it makes sense to really change what current_query means if the idle/transaction component of it gets moved to another column. Would it be better to set current_query to null if you are idle, rather than the way it's currently overloaded with text in that case? I don't like the way this view works at all, but I'm not sure the best way to change it. Just changing procpid wouldn't be the only thing on the list though. Agreed on moving 'IDLE' and 'IDLE in transaction' into separate fields. If I had thought of it I would have done it that way years ago. (At least I think it was me.) Using angle brackets to put magic values in that field was clearly wrong. FWIW, I wrote a monitoring query around it like this (the requirement was to not expose the current_query contents). SELECT datname, procpid, usename, backend_start, xact_start, query_start, waiting AS is_waiting, current_query = $$IDLE$$ AS is_idle, current_query = $$IDLE in transaction$$ AS is_idle_in_transaction, current_query ilike $$VACUUM%$$ as is_vacuum, client_port IS NULL AND (current_query like $$autovacuum:%$$ OR current_query like $$VACUUM%$$) as is_autovacuum, now() AS capture_time FROM pg_catalog.pg_stat_activity The tricky part was to determine how long a connection has been in the state that it currently is in. Since the various *_start columns are changed only as needed, I had to use the following expression to calculate that. (capture_time - COALESCE(query_start, xact_start, backend_start))::interval query_start is changed every time current_query value is changed; but it is NULL if the backend has just started. Similarly, xact_start changes whenever backend goes into/comes out of a transaction; but it is NULL when the backend has just started. backend_start is never NULL, so we can fall back on that when nothing else is available (i.e when the backend has just started). If we separated is_idle and is_idle_in_transaction into separate fields, then we also need to somehow expose when did the backend get into that state, unless we promise to hold the assumptions true that were made when writing the above query (which is not as straightforward as one would expect). -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] procpid?
On Wed, Jun 15, 2011 at 8:47 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 15, 2011 at 3:34 AM, Greg Smith g...@2ndquadrant.com wrote: The whole thing is enormously frustrating, and it's an advocacy problem--it contributes to people just starting to become serious about using PostgreSQL lowering their opinion of its suitability for their business. If this is what's included for activity monitoring, and it's this terrible, it suggest people must not have very high requirements for that. Well, if we're going to start complaining about the lack of proper activity monitoring, the problems that you're talking about are just the tip of the iceberg. Don't even get me started. Putting on my stability hat instead of my make it right one, maybe this really makes sense to expose as a view with a whole new name. Make this new one pg_activity (there's no stats here anyway), keep the old one around as pg_stat_activity for a few releases until everyone has converted to the new one. Now, that's a suggestion I could very possibly get behind. Though the fact that it would leave us with pg_activity / pg_stat_replication seems less than ideal. Maybe pg_activity isn't the best name either... bikeshedding time! Why not expose this new information as functions instead of a new view, like we do for pg_is_in_replication(). People can use whatever alias they want in the queries they write. SELECT get_current_query(pid), is_idle(pid), is_idle_in_transaction(pid), transaction_start_time(pid), FROM (select procpid as pid FROM pg_stat_activity); Then pg_activity (or whatever we name it later) would also be a view on top of these functions. -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] psql describe.c cleanup
On Tue, Jun 14, 2011 at 9:08 PM, Josh Kupershmidt schmi...@gmail.com wrote: On Tue, Jun 14, 2011 at 12:15 PM, Merlin Moncure mmonc...@gmail.com wrote: What I do wonder though is if the ; appending should really be happening in printQuery() instead of in each query -- the idea being that formatting for external consumption should be happening in one place. Maybe that's over-thinking it though. That's a fair point, and hacking up printQuery() would indeed solve my original gripe about copy-pasting queries out of psql -E. But more generally, I think that standardizing the style of the code is a Good Thing, particularly where style issues impact usability (like here). sure -- if anyone would like to comment on this one way or the other feel free -- otherwise I'll pass the patch up the chain as-is...it's not exactly the 'debate of the century' :-). 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] pg_upgrade using appname to lock out other users
On 06/14/2011 11:01 PM, Bruce Momjian wrote: You might remember we added a postmaster/postgres -b switch to indicate binary upgrade mode. The attached patch prevents any client without an application_name of 'binary-upgrade' from connecting to the cluster while it is binary upgrade mode. This helps prevent unauthorized users from connecting during the upgrade. This will not help for clusters that do not have the -b flag, e.g. pre-9.1. Does this seem useful? Something for 9.1 or 9.2? This idea came from Andrew Dunstan via IRC during a pg_upgrade run by Stephen Frost when some clients accidentally connected. (Stephen reran pg_upgrade successfully.) What I actually had in mind was rather different: an HBA mechanism based on appname. But on second thoughts maybe the protocol wouldn't support that. 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] pg_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 10:05 AM, Andrew Dunstan and...@dunslane.net wrote: What I actually had in mind was rather different: an HBA mechanism based on appname. But on second thoughts maybe the protocol wouldn't support that. Ah, a similar thought struck me. Independent of this particular feature, it would be rather useful to augment pg_hba.conf to filter based on appname. For my pet case, that would mean one might let slon and slonik (Slony appname values) in, whilst keeping other appnames out, during a system maintenance. It's debatable whether or not that's more useful than filtering on the basis of user names, which are likely to be pretty nearly isomorphic to appnames. Due to the near-isomorphism, I would not be comfortable trying to claim that this is anywhere near essential. During upgrade, I expect that we'd want everything but the upgrade process locked out, including some backend-ish processes such as autovacuum. That doesn't have quite the same feel as pg_hba.conf, which also piques my not totally comfortable with this being a pg_hba.conf thing. That doesn't mean the idea's useless in and of itself, nor that there's not some useful adaption to be made. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 10:05 AM, Andrew Dunstan and...@dunslane.net wrote: What I actually had in mind was rather different: an HBA mechanism based on appname. But on second thoughts maybe the protocol wouldn't support that. Ah, a similar thought struck me. Independent of this particular feature, it would be rather useful to augment pg_hba.conf to filter based on appname. For my pet case, that would mean one might let slon and slonik (Slony appname values) in, whilst keeping other appnames out, during a system maintenance. It's debatable whether or not that's more useful than filtering on the basis of user names, which are likely to be pretty nearly isomorphic to appnames. Due to the near-isomorphism, I would not be comfortable trying to claim that this is anywhere near essential. During upgrade, I expect that we'd want everything but the upgrade process locked out, including some backend-ish processes such as autovacuum. That doesn't have quite the same feel as pg_hba.conf, which also piques my not totally comfortable with this being a pg_hba.conf thing. That doesn't mean the idea's useless in and of itself, nor that there's not some useful adaption to be made. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] procpid?
On 06/14/2011 08:04 PM, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 For me, the litmus test is whether the change provides enough improvement that it outweighs the disruption when the user runs into it. For the procpid that started all of this, the clear answer is no. I'm surprised people seriously considered making this change. It's a historical accident: document and move on. It is a bug in consistency, the table pg_locks uses pid where pg_stat_activity uses procpid. That is a bug and all bugs are accidents. We take a lot of care in fixing bugs. This isn't just about a few characters in a query, it is about consistency and providing an overall more sane user experience. Frankly I don't care if we use procpid or pid but it should be one or the other not both. Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] procpid?
On Wed, Jun 15, 2011 at 9:44 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: Why not expose this new information as functions instead of a new view, like we do for pg_is_in_replication(). People can use whatever alias they want in the queries they write. SELECT get_current_query(pid), is_idle(pid), is_idle_in_transaction(pid), transaction_start_time(pid), FROM (select procpid as pid FROM pg_stat_activity); Then pg_activity (or whatever we name it later) would also be a view on top of these functions. Well, that would probably be a lot slower, and wouldn't necessarily deliver as consistent a snapshot of system activity. It's better to have one set-returning function that dumps out all the data in a single pass. -- 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] procpid?
On Wed, Jun 15, 2011 at 10:31 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 15, 2011 at 9:44 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: Why not expose this new information as functions instead of a new view, like we do for pg_is_in_replication(). People can use whatever alias they want in the queries they write. SELECT get_current_query(pid), is_idle(pid), is_idle_in_transaction(pid), transaction_start_time(pid), FROM (select procpid as pid FROM pg_stat_activity); Then pg_activity (or whatever we name it later) would also be a view on top of these functions. Well, that would probably be a lot slower, and wouldn't necessarily deliver as consistent a snapshot of system activity. It's better to have one set-returning function that dumps out all the data in a single pass. I wanted to address consistency issue in the previous mail, but then wanted that to be left for later. We can provide consistency the same way pg_locks provides; take a snapshot on first request within a transaction, and reuse that snapshot for subsequent calls. In this case we might want to go a bit finer grained by providing a snapshot for every query. -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] procpid?
Gurjeet Singh singh.gurj...@gmail.com writes: On Wed, Jun 15, 2011 at 10:31 AM, Robert Haas robertmh...@gmail.com wrote: Well, that would probably be a lot slower, and wouldn't necessarily deliver as consistent a snapshot of system activity. It's better to have one set-returning function that dumps out all the data in a single pass. I wanted to address consistency issue in the previous mail, but then wanted that to be left for later. We can provide consistency the same way pg_locks provides; take a snapshot on first request within a transaction, and reuse that snapshot for subsequent calls. In this case we might want to go a bit finer grained by providing a snapshot for every query. Quite honestly, the implementation mechanism used by the other statistics views is enormous overkill. I agree with Robert that I'm not eager to duplicate that for the activity view, when a simple SRF can get the job done. 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] FK NOT VALID can't be deferrable?
Dean Rasheed dean.a.rash...@gmail.com writes: On 15 June 2011 07:56, Jaime Casanova ja...@2ndquadrant.com wrote: Testing the CHECK NOT VALID patch i found $subject... is this intended? Aside from the ugliness of the code, we can't just add a ConstraintAttributeSpec to the second block, because that would enforce an order to these options. OTOH adding NOT VALID to ConstraintAttributeSpec is a bit invasive, since it's used in quite a few places, including CREATE TABLE, where NOT VALID is never allowed. Thoughts? I think we need to do the second one, ie, add it to ConstraintAttributeSpec and do what's necessary to filter later. The reason we have a problem here is exactly that somebody took shortcuts. It'd probably be sufficient to have one or two places in parse_utilcmds.c know which variants of Constraint actually support NOT VALID, and throw an error for the rest. 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] procpid?
Excerpts from Robert Haas's message of mié jun 15 08:47:58 -0400 2011: On Wed, Jun 15, 2011 at 3:34 AM, Greg Smith g...@2ndquadrant.com wrote: Putting on my stability hat instead of my make it right one, maybe this really makes sense to expose as a view with a whole new name. Make this new one pg_activity (there's no stats here anyway), keep the old one around as pg_stat_activity for a few releases until everyone has converted to the new one. Now, that's a suggestion I could very possibly get behind. Though the fact that it would leave us with pg_activity / pg_stat_replication seems less than ideal. Maybe pg_activity isn't the best name either... bikeshedding time! pg_sessions? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Small SSI issues
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: There may be some places this can be checked which haven't yet been identified and touched. Yeah - in 9.2. No argument here. I'm all for stabilizing and getting the thing out -- I think we've established that performance is good for many workloads as it stands, and that there are workloads where it will never be useful. Chipping away at the gray area, to make it perform well in a few workloads where it currently doesn't (and, of course, *even better* in workloads where it's currently better than the alternatives), seems like future release work to me. There is one issue you raised in this post: http://archives.postgresql.org/message-id/4def3194.6030...@enterprisedb.com Robert questioned whether it should be 9.1 material here: http://archives.postgresql.org/message-id/BANLkTint2i2fHDTdr=Xq3K=yrxegovg...@mail.gmail.com I posted a patch here: http://archives.postgresql.org/message-id/4defb16902250003e...@gw.wicourts.gov Should I put that patch into a 9.2 CF? There is an unnecessary include of predicate.h in nbtree.c we should delete. That seems safe enough. You questioned whether OldSerXidPagePrecedesLogically was buggy. I will look at that by this weekend at the latest. If it is buggy we obviously should fix that. Are there any other changes you think we should make to handle the odd corner cases in the SLRU for SSI? It did occur to me that we should be safe from actual overwriting of an old entry by the normal transaction wrap-around protections -- the worst that should happen with the current code (I think) is that in extreme cases we may get LOG level messages or accumulate a surprising number of SLRU segment files. That's because SLRU will start to nag about things at one billion transactions, but we need to get all the way to two billion transactions used up while a single serializable transaction remains active before we could overwrite something. It seems like it might be a good idea to apply pgindent formating to the latest SSI changes, to minimize conflict on back-patching any bug fixes. I've attached a patch to do this formatting -- entirely whitespace changes from a pgindent run against selected files. Unless I'm missing something, the only remaining changes needed are for documentation (as mentioned in previous posts). I will work on those after I look at OldSerXidPagePrecedesLogically. -Kevin *** a/src/backend/access/nbtree/nbtsearch.c --- b/src/backend/access/nbtree/nbtsearch.c *** *** 849,856 _bt_first(IndexScanDesc scan, ScanDirection dir) if (!BufferIsValid(buf)) { /* !* We only get here if the index is completely empty. !* Lock relation because nothing finer to lock exists. */ PredicateLockRelation(rel, scan-xs_snapshot); return false; --- 849,856 if (!BufferIsValid(buf)) { /* !* We only get here if the index is completely empty. Lock relation !* because nothing finer to lock exists. */ PredicateLockRelation(rel, scan-xs_snapshot); return false; *** a/src/backend/access/transam/twophase_rmgr.c --- b/src/backend/access/transam/twophase_rmgr.c *** *** 26,32 const TwoPhaseCallback twophase_recover_callbacks[TWOPHASE_RM_MAX_ID + 1] = NULL, /* END ID */ lock_twophase_recover, /* Lock */ NULL, /* pgstat */ ! multixact_twophase_recover, /* MultiXact */ predicatelock_twophase_recover /* PredicateLock */ }; --- 26,32 NULL, /* END ID */ lock_twophase_recover, /* Lock */ NULL, /* pgstat */ ! multixact_twophase_recover, /* MultiXact */ predicatelock_twophase_recover /* PredicateLock */ }; *** *** 44,50 const TwoPhaseCallback twophase_postabort_callbacks[TWOPHASE_RM_MAX_ID + 1] = NULL, /* END ID */ lock_twophase_postabort,/* Lock */ pgstat_twophase_postabort, /* pgstat */ ! multixact_twophase_postabort, /* MultiXact */ NULL/* PredicateLock */ }; --- 44,50 NULL, /* END ID */ lock_twophase_postabort,/* Lock */ pgstat_twophase_postabort, /* pgstat */ ! multixact_twophase_postabort, /* MultiXact */ NULL/* PredicateLock */ }; *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *** ***
[HACKERS] bad posix_fadvise support causes initdb to exit ungracefully
Due to unfortunate environmental conditions (don't ask) I've been trying to get postgres 9.0 up and running on a fairly ancient linux -- redhat EL 3 which as kernel 2.4.21. initdb borks on the create database step with the error message child process exited with error code 139. A bit of tracing revealed the exit was happening at the pg_flush_data which basically wraps posix_fadvise. Disabling fadvise support in pg_config_manual.h fixed the problem. Things brings up a couple of questions: *) Are linuxes this old out of support? *) Should configure be testing for working posix_fadvise? 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] pg_upgrade using appname to lock out other users
Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011: 1. pg_upgrade invokes the postmaster with --binary-upgrade=port:password 2. postmaster starts up into multi-user mode, but it does not start autovacuum and ignores pg_hba.conf, listen_addresses, and port. Instead it listens only on the localhost interface on the designated port (perhaps the port can be a filename on systems that support UNIX sockets, and it can listen only on a UNIX socket at that location instead). It refuses all connections except for those that attempt to log in with binary_upgrade as the user and the given password as the password. pg_upgrade will randomly generate a password (like C51622FA-7513-4300-A4B7-6423769276F8) and port number at the start of each run, and use that for all connections to the postmaster. Seems good, except that passing the password as a command line argument is obviously broken from a privacy perspective -- anyone could see the process list and get it. Maybe have postmaster ask for it on startup somehow, or have pg_upgrade write it in a file which is read by postmaster. As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 08:47:58 -0400 2011: Now, that's a suggestion I could very possibly get behind. Though the fact that it would leave us with pg_activity / pg_stat_replication seems less than ideal. Maybe pg_activity isn't the best name either... bikeshedding time! pg_sessions? Yeah. Or pg_stat_sessions if you want to keep it looking like it's part of the pg_stat_ family. (I'm not sure if we do, since it's really a completely independent facility. OTOH, if we don't name it that way, we're kind of bound to move the documentation into the System Views chapter, whereas it'd be better to keep it where it is.) 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] bad posix_fadvise support causes initdb to exit ungracefully
On Wed, Jun 15, 2011 at 11:12 AM, Merlin Moncure mmonc...@gmail.com wrote: Due to unfortunate environmental conditions (don't ask) I've been trying to get postgres 9.0 up and running on a fairly ancient linux -- redhat EL 3 which as kernel 2.4.21. initdb borks on the create database step with the error message child process exited with error code 139. A bit of tracing revealed the exit was happening at the pg_flush_data which basically wraps posix_fadvise. Disabling fadvise support in pg_config_manual.h fixed the problem. Things brings up a couple of questions: *) Are linuxes this old out of support? *) Should configure be testing for working posix_fadvise? some searching of the archives turned up this: http://archives.postgresql.org/pgsql-committers/2010-02/msg00175.php which pretty much explains the issue (see subsequent discussion). 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] creating CHECK constraints as NOT VALID
Excerpts from Jaime Casanova's message of mié jun 15 02:09:15 -0400 2011: psql \h says (among other things) for ALTER TABLE ADD table_constraint ADD table_constraint_using_index ADD table_constraint [ NOT VALID ] ADD table_constraint appears twice and isn't true that all table_constraint accept the NOT VALID syntax... maybe we can accpet the syntax and send an unimplemented feature message for the other table_constraints? Okay, I removed the redundant line from the synposis. As far as other types of constraints go, I don't feel we need to do anything here -- the description already says that it only works on FKs and CHECK. I'm not going to go to the trouble of fixing the redundant table_constraint line in the synopsis in HEAD -- if someone else wants to send a patch to fix that, I can apply it easily enough. EXAMPLE 1: constraint_exclusion when using NOT VALID check constraints... and it works well, except when the constraint has been validated, it keeps ignoring it (which means i won't benefit from constraint_exclusion) until i execute ANALYZE on the table or close connection Hmm, I think this means we need to send a sinval message to invalidate cached plans when a constraint is validated. I'll see about this. EXAMPLE 2: if i have a DOMAIN with a NOT VALID check constraint, and i use it as the new type of a column it checks the constraint I think this is OK. The NOT VALID declaration says that the existing columns declared using this constraint is not checked, but new columns (as well as new data in existing columns) are certainly going to require their values to pass the checks. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
On Wed, Jun 15, 2011 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 08:47:58 -0400 2011: Now, that's a suggestion I could very possibly get behind. Though the fact that it would leave us with pg_activity / pg_stat_replication seems less than ideal. Maybe pg_activity isn't the best name either... bikeshedding time! pg_sessions? Yeah. Or pg_stat_sessions if you want to keep it looking like it's part of the pg_stat_ family. (I'm not sure if we do, since it's really a completely independent facility. OTOH, if we don't name it that way, we're kind of bound to move the documentation into the System Views chapter, whereas it'd be better to keep it where it is.) I've always found the fact that the system views are documented in two different places to be somewhat confusing. It doesn't help that the documentation for the statistics views is quite a bit less detailed. At any rate, I like sessions. That's what it is, after all. But I will note that we had better be darn sure to make all the changes we want to make in one go, because I dowanna have to create pg_sessions2 (or pg_tessions?) in a year or three. -- 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] bad posix_fadvise support causes initdb to exit ungracefully
hello ... 2.4? we know that some versions of 2.4 cause problems due to broken posix_fadvise. if i remember correctly we built some configure magic into PostgreSQL to check for this bug. what does this check do? many thanks, hans On Jun 15, 2011, at 6:12 PM, Merlin Moncure wrote: Due to unfortunate environmental conditions (don't ask) I've been trying to get postgres 9.0 up and running on a fairly ancient linux -- redhat EL 3 which as kernel 2.4.21. initdb borks on the create database step with the error message child process exited with error code 139. A bit of tracing revealed the exit was happening at the pg_flush_data which basically wraps posix_fadvise. Disabling fadvise support in pg_config_manual.h fixed the problem. Things brings up a couple of questions: *) Are linuxes this old out of support? *) Should configure be testing for working posix_fadvise? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 At any rate, I like sessions. That's what it is, after all. But I will note that we had better be darn sure to make all the changes we want to make in one go, because I dowanna have to create pg_sessions2 (or pg_tessions?) in a year or three. Or perhaps pg_connections. Yes, +1 to making things fully backwards compatible by keeping pg_stat_activity around but making a better designed and better named table (view/SRF/whatever). Sounds like perhaps a wiki page to start documenting some of our monitoring shortcomings? Might as well fix as much as we can in one swoop. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201106151246 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAk344ioACgkQvJuQZxSWSshy9wCgnrj4lQkaomsgS55yq9KI0HBl P2UAoI62Tkt9/U62l0Bxv/KfQUUlL/NF =aaTL -END PGP SIGNATURE- -- 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_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011: Seems good, except that passing the password as a command line argument is obviously broken from a privacy perspective -- anyone could see the process list and get it. Maybe have postmaster ask for it on startup somehow, or have pg_upgrade write it in a file which is read by postmaster. Writing it to a file which is ready by postmaster seems promising. Then you wouldn't even need a command line option; you could just have the postmaster write out binary_upgrade.conf and have that work like recovery.conf to trigger the system to start up in a different mode. As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. I'm not sure exactly what is needed here - what programmable tasks are you thinking of, other than pg_upgrade? -- 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] pg_upgrade using appname to lock out other users
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011: As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. So we fix the interface presented by standalone mode to be less insane. That way, we can *reduce* the net amount of cruft in the system, rather than adding more as all these proposals do. 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] bad posix_fadvise support causes initdb to exit ungracefully
On 15 June 2011 17:12, Merlin Moncure mmonc...@gmail.com wrote: Due to unfortunate environmental conditions (don't ask) I've been trying to get postgres 9.0 up and running on a fairly ancient linux -- redhat EL 3 which as kernel 2.4.21. initdb borks on the create database step with the error message child process exited with error code 139. A bit of tracing revealed the exit was happening at the pg_flush_data which basically wraps posix_fadvise. Disabling fadvise support in pg_config_manual.h fixed the problem. Things brings up a couple of questions: *) Are linuxes this old out of support? *) Should configure be testing for working posix_fadvise? Doesn't it already test for that? Maybe it isn't doing a good enough job in this instance, because the function is present but doesn't behave as expected. After all, the wrapping code you refer to only builds with a call to posix_fadvise() when various macros are defined. It isn't exactly uncommon for it to be merely unavailable - on many of our supported platforms, setting effective_io_concurrency to anything other than 0 causes an error. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] procpid?
--On 15. Juni 2011 16:47:55 + Greg Sabino Mullane g...@turnstep.com wrote: Or perhaps pg_connections. Yes, +1 to making things fully backwards compatible by keeping pg_stat_activity around but making a better designed and better named table (view/SRF/whatever). I thought about that too when reading the thread the first time, but pg_stat_sessions sounds better. Our documentation also primarily refers to a database connection as a session, i think. -- Thanks Bernd -- 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] creating CHECK constraints as NOT VALID
On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hmm, I think this means we need to send a sinval message to invalidate cached plans when a constraint is validated. I'll see about this. I feel like that really ought to be happening automatically, as a result of committing the transaction that did the system catalog modification. It seems pretty strange if it isn't. -- 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] Strict Set Returning Functions
STRICT functions return NULL if any of their inputs are NULL according to the manual, so that they need not be executed at all. Unless it is a Set Returning Function, in which case a NULL input is not reduced nor does it to appear to be handled as a special case in the executor function scan code. So a function that is both STRICT and SET RETURNING will return rows. Presumably this is just a case of missing documentation? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating CHECK constraints as NOT VALID
Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011: On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hmm, I think this means we need to send a sinval message to invalidate cached plans when a constraint is validated. I'll see about this. I feel like that really ought to be happening automatically, as a result of committing the transaction that did the system catalog modification. It seems pretty strange if it isn't. The catalog change takes place in pg_constraint, so I'm not sure that it'd cause the sort of event we need. I'm testing whether adding a call to CacheInvalidateRelcache in the appropriate place works. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bad posix_fadvise support causes initdb to exit ungracefully
Merlin Moncure mmonc...@gmail.com writes: Due to unfortunate environmental conditions (don't ask) I've been trying to get postgres 9.0 up and running on a fairly ancient linux -- redhat EL 3 which as kernel 2.4.21. initdb borks on the create database step with the error message child process exited with error code 139. A bit of tracing revealed the exit was happening at the pg_flush_data which basically wraps posix_fadvise. Disabling fadvise support in pg_config_manual.h fixed the problem. Things brings up a couple of questions: *) Are linuxes this old out of support? RHEL3 is just about dead as far as Red Hat is concerned: only critical security bugs will be addressed, and even that is going to stop in a year or two. RH would certainly not recommend that you be trying to put any new applications on that platform. *) Should configure be testing for working posix_fadvise? There isn't any reliable way to do that at configure time, I think. We could add an AC_TRY_RUN call but it wouldn't be trustworthy; think cross-compiles, or running on some other kernel version than where you compiled. Unless the problem manifests on some not-quite-so-dead platform, I'm not in favor of it. 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] pg_upgrade using appname to lock out other users
Excerpts from Robert Haas's message of mié jun 15 12:51:29 -0400 2011: On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. I'm not sure exactly what is needed here - what programmable tasks are you thinking of, other than pg_upgrade? Well, something to use on shell scripts and the like first and foremost; see http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Excerpts from Tom Lane's message of mié jun 15 12:52:30 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 08:45:21 -0400 2011: As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. So we fix the interface presented by standalone mode to be less insane. That way, we can *reduce* the net amount of cruft in the system, rather than adding more as all these proposals do. +1 on that general idea, but who is going to do the work? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bad posix_fadvise support causes initdb to exit ungracefully
2011/6/15 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at: hello ... 2.4? we know that some versions of 2.4 cause problems due to broken posix_fadvise. if i remember correctly we built some configure magic into PostgreSQL to check for this bug. what does this check do? It doesn't check anything beyond looking for stanard defines -- posix_fadvise is there but fails immediately with ENOSYS despite what the lying manpage says. On Wed, Jun 15, 2011 at 12:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Things brings up a couple of questions: *) Are linuxes this old out of support? RHEL3 is just about dead as far as Red Hat is concerned: only critical security bugs will be addressed, and even that is going to stop in a year or two. RH would certainly not recommend that you be trying to put any new applications on that platform. *) Should configure be testing for working posix_fadvise? There isn't any reliable way to do that at configure time, I think. We could add an AC_TRY_RUN call but it wouldn't be trustworthy; think cross-compiles, or running on some other kernel version than where you compiled. Unless the problem manifests on some not-quite-so-dead platform, I'm not in favor of it. fair enough. anyways, at least it's documented if someone else bumps into this... 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] pg_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 1:13 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mié jun 15 12:51:29 -0400 2011: On Wed, Jun 15, 2011 at 12:12 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. I'm not sure exactly what is needed here - what programmable tasks are you thinking of, other than pg_upgrade? Well, something to use on shell scripts and the like first and foremost; see http://petereisentraut.blogspot.com/2010/03/running-sql-scripts-with-psql.html I don't think there's anything wrong with using psql for running scripts. It might need some work and maybe some better flags, but I don't think we need to throw it out wholesale. What we do need for pg_upgrade is to build more of the logic into either pg_upgrade itself or the server, so it's not spawning external programs right and left. It might help to library-ify some of the functionality that's being used so that it can be invoked via a function call rather than a shell exec. -- 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] pg_upgrade using appname to lock out other users
Alvaro Herrera wrote: Excerpts from Tom Lane's message of mi?? jun 15 12:52:30 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mi?? jun 15 08:45:21 -0400 2011: As a separate issue, I tend to agree with Tom that using psql as part of the pg_upgrade process is a lousy idea and we need a better solution. But let's fix one thing at a time. Agreed on both counts ... but ... does this mean that we need a different program for programmable tasks as opposed to interactive ones? Dealing with standalone backends *is* a pain, that's for sure. So we fix the interface presented by standalone mode to be less insane. That way, we can *reduce* the net amount of cruft in the system, rather than adding more as all these proposals do. +1 on that general idea, but who is going to do the work? And you are going to backpatch all this? I don't find this promising at all. -- 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] pg_upgrade using appname to lock out other users
Robert Haas wrote: Also, a standalone backend does not have libpq either so how do you get values into application variables? ?Parse the text output? ?That seems like a much larger kludge. Maybe we could do something like this. 1. pg_upgrade invokes the postmaster with --binary-upgrade=port:password 2. postmaster starts up into multi-user mode, but it does not start autovacuum and ignores pg_hba.conf, listen_addresses, and port. Instead it listens only on the localhost interface on the designated port (perhaps the port can be a filename on systems that support UNIX sockets, and it can listen only on a UNIX socket at that location instead). It refuses all connections except for those that attempt to log in with binary_upgrade as the user and the given password as the password. pg_upgrade will randomly generate a password (like C51622FA-7513-4300-A4B7-6423769276F8) and port number at the start of each run, and use that for all connections to the postmaster. I now believe we are overthinking all this. pg_upgrade has always supported specification of a port number. Why not just tell users to specify an unused port number 1023, and not to use the default value? Both old and new clusters will happily run on any specified port number during the upgrade. This allows the lockout to work for both old and new clusters, which is better than enhancing -b because that will only be for 9.1 servers. This requires no new backend code. We could even _require_ the port number to be specified in pg_upgrade. -- 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] pg_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian br...@momjian.us wrote: This requires no new backend code. We could even _require_ the port number to be specified in pg_upgrade. +1... That seems to have lots of nice properties. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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] Small SSI issues
Kevin Grittner kevin.gritt...@wicourts.gov wrote: Unless I'm missing something, the only remaining changes needed are for documentation (as mentioned in previous posts). I just found notes that we also need regression tests for the SSI/DDL combination and a comment in lazy_truncate_heap. At any rate, not anything which is part of executable code -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Christopher Browne cbbro...@gmail.com writes: On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian br...@momjian.us wrote: [ just recommend using a different port number during pg_upgrade ] +1... That seems to have lots of nice properties. Yeah, that seems like an appropriate expenditure of effort. It's surely not bulletproof, since someone could intentionally connect to the actual port number, but getting to bulletproof is a lot more work than anyone seems to want to do right now. (And, as Bruce pointed out, no complete solution would be back-patchable anyway.) 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] creating CHECK constraints as NOT VALID
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011: On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hmm, I think this means we need to send a sinval message to invalidate cached plans when a constraint is validated. I'll see about this. I feel like that really ought to be happening automatically, as a result of committing the transaction that did the system catalog modification. It seems pretty strange if it isn't. The catalog change takes place in pg_constraint, so I'm not sure that it'd cause the sort of event we need. I'm testing whether adding a call to CacheInvalidateRelcache in the appropriate place works. Currently, only updates in pg_class, pg_attribute, and pg_index cause automatic relcache invalidations --- see the logic in PrepareForTupleInvalidation. If you want to force replanning after an update elsewhere, you need to call CacheInvalidateRelcache. I've occasionally thought about extending the number of cases that get handled automatically by PrepareForTupleInvalidation, but not gotten off my duff to change it. I doubt that we want to make that routine know about *every* possible case, so it's a matter of tradeoffs ... 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] Strict Set Returning Functions
Simon Riggs si...@2ndquadrant.com writes: STRICT functions return NULL if any of their inputs are NULL according to the manual, so that they need not be executed at all. Unless it is a Set Returning Function, in which case a NULL input is not reduced nor does it to appear to be handled as a special case in the executor function scan code. So a function that is both STRICT and SET RETURNING will return rows. Really? The case behaves as expected for me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FK NOT VALID can't be deferrable?
On Wed, Jun 15, 2011 at 4:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: On 15 June 2011 07:56, Jaime Casanova ja...@2ndquadrant.com wrote: Testing the CHECK NOT VALID patch i found $subject... is this intended? Aside from the ugliness of the code, we can't just add a ConstraintAttributeSpec to the second block, because that would enforce an order to these options. OTOH adding NOT VALID to ConstraintAttributeSpec is a bit invasive, since it's used in quite a few places, including CREATE TABLE, where NOT VALID is never allowed. Thoughts? I think we need to do the second one, ie, add it to ConstraintAttributeSpec and do what's necessary to filter later. The reason we have a problem here is exactly that somebody took shortcuts. There were grammar issues in the NOT VALID patch which I sought to resolve. Those new suggestions may fall foul of the same issues. I raised that point and asked for input prior to commit. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq SSL with non-blocking sockets
On 06/12/2011 04:22 AM, Robert Haas wrote: One idea is that we could add outBuffer2/outBufSize2 to struct pg_conn, or something along those lines with less obviously stupid naming. Normally those would be unused, but in the special case where SSL indicates that we must retry the call with the same arguments, we set a flag that freezes the out buffer and forces any further data to be stuffed into outBuffer2. If or when the operation finally succeeds, we then move the data from outBuffer2 into outBuffer. Yes, that sounds like a good idea -- especially considering that COPY is not the only operation that can cause SSL_write retries. Attached is a first attempt at a patch to implement the described two buffer approach. This modifies pqSendSome so that whenever a SSL write retry is needed it saves the current outBuffer with its length and attempted write size to connection's sslRetry* variables. A new outBuffer is then allocated and used for any further data pushing. After the SSL write retry buffer is set up, any further calls to pqSendSome will first attempt to send the contents of the retry buffer, returning 1 to indicate that not all of the data could be sent. If the retry buffer is finally emptied it is freed and pqSendSome starts sending from the regular outBuffer. This is of course still work in progress, needs cleaning up and definitely more testing. But at this point before going any further, I'd really appreciate a quick review from resident libpq gurus. regards, Martin diff --git a/src/interfaces/libpq/fe-misc.c b/src/interfaces/libpq/fe-misc.c index 17dde4a..b5e62c9 100644 --- a/src/interfaces/libpq/fe-misc.c +++ b/src/interfaces/libpq/fe-misc.c @@ -781,6 +781,8 @@ pqSendSome(PGconn *conn, int len) char *ptr = conn-outBuffer; int remaining = conn-outCount; int result = 0; + int sent = 0; + bool usingSSLWriteBuffer = false; if (conn-sock 0) { @@ -789,10 +791,28 @@ pqSendSome(PGconn *conn, int len) return -1; } +#ifdef USE_SSL + if (conn-sslRetryPos) + { + /* + * We have some leftovers from previous SSL write attempts, send those + * instead of the regular output buffer. + */ + len = conn-sslRetryBytes; + ptr = conn-sslRetryPos; + remaining = conn-sslRetryBufSize; + + if (conn-Pfdebug) + fprintf(conn-Pfdebug, pqSendSome: using SSL write retry buffer: %p retry: %d total: %d\n, +ptr, len, remaining); + + usingSSLWriteBuffer = true; + } +#endif + /* while there's still data to send */ while (len 0) { - int sent; char sebuf[256]; #ifndef WIN32 @@ -807,6 +827,10 @@ pqSendSome(PGconn *conn, int len) sent = pqsecure_write(conn, ptr, Min(len, 65536)); #endif + if (conn-Pfdebug) + fprintf(conn-Pfdebug, pqSendSome: write buf: %p len: %d sent: %d\n, + ptr, len, sent); + if (sent 0) { /* @@ -857,6 +881,35 @@ pqSendSome(PGconn *conn, int len) return -1; } } +#ifdef USE_SSL + else if (sent == 0 conn-ssl) + { + /* + * With non-blocking SSL connections we need to ensure that the + * buffer passed to the pqsecure_write() retry is the the exact + * same buffer as in previous write -- see SSL_write(3SSL) for more + * on this. For this we need to keep the the original buffer and + * remember its length. Also a new outBuffer is needed, but the + * actual allocation is deferred to the end of the function. + * + * For non-SSL connections none of this is needed. + */ + if (!conn-sslRetryPos) + { +if (conn-Pfdebug) + fprintf(conn-Pfdebug, pqSendSome: SSL retry setup: buf: %p len: %d remain: %d\n, + conn-sslRetryBuf, len, remaining); + +conn-sslRetryBuf = conn-outBuffer; +conn-sslRetryPos = ptr; +conn-sslRetryBytes = len; +conn-sslRetryBufSize = remaining; +conn-outBuffer = NULL; + } + + usingSSLWriteBuffer = true; + } +#endif else { ptr += sent; @@ -903,10 +956,73 @@ pqSendSome(PGconn *conn, int len) } } - /* shift the remaining contents of the buffer */ - if (remaining 0) - memmove(conn-outBuffer, ptr, remaining); - conn-outCount = remaining; +#ifdef USE_SSL + if (conn-sslRetryPos sent 0) + { + /* + * A SSL write was successfully retried, advance the retry buffer + * position to the rest of the buffer. Free the buffer if all of its + * contents are delivered. + */ + conn-sslRetryPos = ptr; + conn-sslRetryBytes = remaining; + conn-sslRetryBufSize = remaining; + + if (conn-sslRetryBufSize = 0) + { + if (conn-Pfdebug) +fprintf(conn-Pfdebug, SSL retry clean-up: freed buf=%p\n, + conn-sslRetryBuf); + + free(conn-sslRetryBuf); + conn-sslRetryBytes = conn-sslRetryBufSize = 0; + conn-sslRetryBuf = conn-sslRetryPos = NULL; + } + + if (conn-sslRetryBytes || (conn-outCount - remaining) 0) + { + /* + * We still have some data left in the SSL retry buffers or the + * outBuffer. Return 1 to indicate that further retries are needed + * to flush the entire output buffer. + */ +
Re: [HACKERS] creating CHECK constraints as NOT VALID
Excerpts from Tom Lane's message of mié jun 15 14:49:04 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Robert Haas's message of mié jun 15 12:53:59 -0400 2011: On Wed, Jun 15, 2011 at 12:24 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Hmm, I think this means we need to send a sinval message to invalidate cached plans when a constraint is validated. I'll see about this. I feel like that really ought to be happening automatically, as a result of committing the transaction that did the system catalog modification. It seems pretty strange if it isn't. The catalog change takes place in pg_constraint, so I'm not sure that it'd cause the sort of event we need. I'm testing whether adding a call to CacheInvalidateRelcache in the appropriate place works. Currently, only updates in pg_class, pg_attribute, and pg_index cause automatic relcache invalidations --- see the logic in PrepareForTupleInvalidation. If you want to force replanning after an update elsewhere, you need to call CacheInvalidateRelcache. I've occasionally thought about extending the number of cases that get handled automatically by PrepareForTupleInvalidation, but not gotten off my duff to change it. I doubt that we want to make that routine know about *every* possible case, so it's a matter of tradeoffs ... I think pg_trigger is closer to needing a new case in PrepareForTupleInvalidation than pg_constraint, at this point -- triggers seem to be involved rather more with CacheInvalidateRelcache (and close relatives) calls than constraints. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Strict Set Returning Functions
On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: STRICT functions return NULL if any of their inputs are NULL according to the manual, so that they need not be executed at all. Unless it is a Set Returning Function, in which case a NULL input is not reduced nor does it to appear to be handled as a special case in the executor function scan code. So a function that is both STRICT and SET RETURNING will return rows. Really? The case behaves as expected for me. Seems that's the wrong question. Let me return to why I raised this: Why does evaluate_function() specifically avoid returning NULL for a set returning function? It could easily do the NULL test first, so it was applied to all function types. That seems strange. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
On ons, 2011-06-15 at 13:35 -0400, Bruce Momjian wrote: I now believe we are overthinking all this. pg_upgrade has always supported specification of a port number. Why not just tell users to specify an unused port number 1023, and not to use the default value? Both old and new clusters will happily run on any specified port number during the upgrade. This allows the lockout to work for both old and new clusters, which is better than enhancing -b because that will only be for 9.1 servers. On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] flexible array members
gcc 4.6 has now arrived as the default compiler on my desktop, and as previously reported, it throws a bunch of warnings, foiling my life-long plan of compiling PostgreSQL with -Werror. So looking more aggressively into fixing some of these, let's look at this case: gistutil.c: In function ‘gistMakeUnionKey’: gistutil.c:263:16: warning: array subscript is above array bounds [-Warray-bounds] gistutil.c:268:16: warning: array subscript is above array bounds [-Warray-bounds] gistutil.c:273:16: warning: array subscript is above array bounds [-Warray-bounds] The code in question is this: typedef struct { int32 n; /* number of elements */ GISTENTRY vector[1]; /* variable-length array */ } GistEntryVector; Not sure why the new gcc is confused about this when -Warray-bounds has existed for a while. But thinking a bit further, the proper fix for this would be to use flexible array members like this: typedef struct { int32 n; /* number of elements */ GISTENTRY vector[]; } GistEntryVector; This is C99, but with some gentle standard autoconf seasoning, it can be made transparent. See attached patch. Is this a route we want to go down? It looks as though other compilers could also benefit from this. clang throws even more warnings of this kind, and the clang static analyzer even more. One thing that is a bit concerning is that throwing more flexible array members around the code wherever variable-length arrays are used results in crash and burn. Probably some places are using sizeof or offsetof on these structures in incompatible ways. So each place would have to be examined separately. diff --git i/configure.in w/configure.in index ddc4cc9..e873c7b 100644 --- i/configure.in +++ w/configure.in @@ -1110,6 +1110,7 @@ AC_C_BIGENDIAN AC_C_CONST PGAC_C_INLINE AC_C_STRINGIZE +AC_C_FLEXIBLE_ARRAY_MEMBER PGAC_C_SIGNED AC_C_VOLATILE PGAC_C_FUNCNAME_SUPPORT diff --git i/src/include/access/gist.h w/src/include/access/gist.h index df9f39c..f3dfcaa 100644 --- i/src/include/access/gist.h +++ w/src/include/access/gist.h @@ -144,7 +144,7 @@ typedef struct GISTENTRY typedef struct { int32 n;/* number of elements */ - GISTENTRY vector[1]; /* variable-length array */ + GISTENTRY vector[FLEXIBLE_ARRAY_MEMBER]; } GistEntryVector; #define GEVHDRSZ (offsetof(GistEntryVector, vector)) diff --git i/src/include/pg_config.h.in w/src/include/pg_config.h.in index 5d38f25..19f38cc 100644 --- i/src/include/pg_config.h.in +++ w/src/include/pg_config.h.in @@ -61,6 +61,15 @@ (--enable-thread-safety) */ #undef ENABLE_THREAD_SAFETY +/* Define to nothing if C supports flexible array members, and to 1 if it does + not. That way, with a declaration like `struct s { int n; double + d[FLEXIBLE_ARRAY_MEMBER]; };', the struct hack can be used with pre-C99 + compilers. When computing the size of such an object, don't use 'sizeof + (struct s)' as it overestimates the size. Use 'offsetof (struct s, d)' + instead. Don't use 'offsetof (struct s, d[0])', as this doesn't work with + MSVC and with C++ compilers. */ +#undef FLEXIBLE_ARRAY_MEMBER + /* float4 values are passed by value if 'true', by reference if 'false' */ #undef FLOAT4PASSBYVAL -- 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] FK NOT VALID can't be deferrable?
Excerpts from Tom Lane's message of mié jun 15 11:54:25 -0400 2011: Dean Rasheed dean.a.rash...@gmail.com writes: On 15 June 2011 07:56, Jaime Casanova ja...@2ndquadrant.com wrote: Testing the CHECK NOT VALID patch i found $subject... is this intended? Aside from the ugliness of the code, we can't just add a ConstraintAttributeSpec to the second block, because that would enforce an order to these options. OTOH adding NOT VALID to ConstraintAttributeSpec is a bit invasive, since it's used in quite a few places, including CREATE TABLE, where NOT VALID is never allowed. I think we need to do the second one, ie, add it to ConstraintAttributeSpec and do what's necessary to filter later. The reason we have a problem here is exactly that somebody took shortcuts. It'd probably be sufficient to have one or two places in parse_utilcmds.c know which variants of Constraint actually support NOT VALID, and throw an error for the rest. So is somebody from 2nd Quadrant going to supply a patch to fix this? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FK NOT VALID can't be deferrable?
On Wed, Jun 15, 2011 at 3:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: So is somebody from 2nd Quadrant going to supply a patch to fix this? well, i was going to give it a try... but in a couple of hours... -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gcc 4.6 -Wunused-but-set-variable
Another set of new gcc 4.6 warnings: readfuncs.c: In function ‘_readCaseWhen’: readfuncs.c:875:567: warning: variable ‘token’ set but not used [-Wunused-but-set-variable] readfuncs.c: In function ‘_readFromExpr’: readfuncs.c:1159:568: warning: variable ‘token’ set but not used [-Wunused-but-set-variable] read.c: In function ‘nodeTokenType’: read.c:222:8: warning: variable ‘val’ set but not used [-Wunused-but-set-variable] I couldn't see a way good way of programming around this (perhaps in the second case, but it would get uselessly ugly), so perhaps just marking the variables as potentially unused would be appropriate? See patch. diff --git i/src/backend/nodes/read.c w/src/backend/nodes/read.c index 78775e8..5c872e8 100644 --- i/src/backend/nodes/read.c +++ w/src/backend/nodes/read.c @@ -219,7 +219,7 @@ nodeTokenType(char *token, int length) * We know the token will end at a character that strtol will stop at, * so we do not need to modify the string. */ - long val; + long val __attribute__((unused)); char *endptr; errno = 0; diff --git i/src/backend/nodes/readfuncs.c w/src/backend/nodes/readfuncs.c index 2288514..4c9e98f 100644 --- i/src/backend/nodes/readfuncs.c +++ w/src/backend/nodes/readfuncs.c @@ -47,7 +47,7 @@ /* And a few guys need only the pg_strtok support fields */ #define READ_TEMP_LOCALS() \ - char *token; \ + char *token __attribute__((unused)); \ int length /* ... but most need both */ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] patch: update README-SSI
The attached patch updates README-SSI. In addition to some minor edits, changes include: - add a section at the beginning that more clearly describes the SSI rule and defines dangerous structure with a diagram. It describes the optimizations we use about the relative commit times, and the case where one transaction is read-only. It includes a proof for the latter (novel) optimization, per Heikki's request. - note that heap page locks do not lock gaps like index pages - be clear about what's been implemented (parts of the README used the future tense, probably because they were written long ago), and remove a couple items from the RD Issues list that have since been addressed. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ diff --git a/src/backend/storage/lmgr/README-SSI b/src/backend/storage/lmgr/README-SSI index c685bee..09a8136 100644 --- a/src/backend/storage/lmgr/README-SSI +++ b/src/backend/storage/lmgr/README-SSI @@ -51,13 +51,13 @@ if a transaction can be shown to always do the right thing when it is run alone (before or after any other transaction), it will always do the right thing in any mix of concurrent serializable transactions. Where conflicts with other transactions would result in an -inconsistent state within the database, or an inconsistent view of +inconsistent state within the database or an inconsistent view of the data, a serializable transaction will block or roll back to prevent the anomaly. The SQL standard provides a specific SQLSTATE for errors generated when a transaction rolls back for this reason, so that transactions can be retried automatically. -Before version 9.1 PostgreSQL did not support a full serializable +Before version 9.1, PostgreSQL did not support a full serializable isolation level. A request for serializable transaction isolation actually provided snapshot isolation. This has well known anomalies which can allow data corruption or inconsistent views of the data @@ -77,7 +77,7 @@ Serializable Isolation Implementation Strategies Techniques for implementing full serializable isolation have been published and in use in many database products for decades. The -primary technique which has been used is Strict 2 Phase Locking +primary technique which has been used is Strict Two-Phase Locking (S2PL), which operates by blocking writes against data which has been read by concurrent transactions and blocking any access (read or write) against data which has been written by concurrent @@ -112,54 +112,90 @@ visualize the difference between the serializable implementations described above, is to consider that among transactions executing at the serializable transaction isolation level, the results are required to be consistent with some serial (one-at-a-time) execution -of the transactions[1]. How is that order determined in each? +of the transactions [1]. How is that order determined in each? -S2PL locks rows used by the transaction in a way which blocks -conflicting access, so that at the moment of a successful commit it -is certain that no conflicting access has occurred. Some transactions -may have blocked, essentially being partially serialized with the -committing transaction, to allow this. Some transactions may have -been rolled back, due to cycles in the blocking. But with S2PL, -transactions can always be viewed as having occurred serially, in the -order of successful commit. +In S2PL, each transaction locks any data it accesses. It holds the +locks until committing, preventing other transactions from making +conflicting accesses to the same data in the interim. Some +transactions may have to be rolled back to prevent deadlock. But +successful transactions can always be viewed as having occurred +sequentially, in the order they committed. With snapshot isolation, reads never block writes, nor vice versa, so -there is much less actual serialization. The order in which -transactions appear to have executed is determined by something more -subtle than in S2PL: read/write dependencies. If a transaction -attempts to read data which is not visible to it because the -transaction which wrote it (or will later write it) is concurrent -(one of them was running when the other acquired its snapshot), then -the reading transaction appears to have executed first, regardless of -the actual sequence of transaction starts or commits (since it sees a -database state prior to that in which the other transaction leaves -it). If one transaction has both rw-dependencies in (meaning that a -concurrent transaction attempts to read data it writes) and out -(meaning it attempts to read data a concurrent transaction writes), -and a couple other conditions are met, there can appear to be a cycle -in execution order of the transactions. This is when the anomalies -occur. - -SSI works by watching for the conditions mentioned above, and rolling -back a transaction when needed to prevent any anomaly. The
Re: [HACKERS] FK NOT VALID can't be deferrable?
On Wed, Jun 15, 2011 at 9:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: So is somebody from 2nd Quadrant going to supply a patch to fix this? My understanding was that your patch had a bug, rather than the existing code. If I misunderstood, please explain the bug. In terms of 2ndQuadrant supplying patches, you may not be aware that we all work independently on community contributions... -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Commitfest 2011-6 is underway! Reviewers needed.
Hackers, The first commitfest for PostgreSQL 9.2 has now started. As such, if you have a patch for 9.2 which was not yet submitted, you should add it to CommitFest 2011-9 (https://commitfest.postgresql.org/action/commitfest_view?id=11). Currently we have 53 open patches. 17 of them need reviewers. If you are not actively involved with finishing 9.1, you should be helping the project by reviewing patches! http://wiki.postgresql.org/wiki/Reviewing_a_Patch If you are a code contributor to PostgreSQL, then we especially need your help for second-level review and for the more difficult patches. Particularly since Tom, Robert, Heikki, Kevin, Dan and Peter are pretty much tied up with 9.1 beta. One special thing we need is folks who can review Windows-specific patches. Currently we don't have *anyone* doing this, and those patches are liable to be deferred because of it. To do this, you need to be able to build PostgreSQL on Windows, using MSVC++ or otherwise. Please let me know if you can help with reviewing a patch. You can pick one on your own, or you can join pgsql-rrreviewers and I'll assign you one. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why polecat and colugos are failing to build back branches
On tis, 2011-06-14 at 18:09 -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 06/14/2011 05:45 PM, Tom Lane wrote: I've committed patches that fix these issues on my own OS X machine, Well, OSX is just using our usual *nix paraphernalia, so if it's broken won't all such platforms probably be broken too? Yes, certainly. The reason I specified OS X in particular is that I only tested the darwin branch of Makefile.shlib. The -install_name switch that was the problem there is specific to OS X, but I wouldn't be surprised if some of the other branches have their own platform- specific issues. I'd actually bet a modest amount MSVC is less broken Very possibly, but unless it's being tested it's no sure bet. Here is some historical reference: http://archives.postgresql.org/message-id/200512231739.47400.pete...@gmx.net I fixed installation into directories containing spaces back then, in light of upcoming Windows and Mac support, but apparently some corner cases had remained or crept back in, for lack of testing. Building in a directory with spaces has always worked, and AFAICT, what Tom committed also concerns only the installation directory. Some problems like vpath that the above message mentioned remain, as others have pointed out. As a secondary point, we have so far used mostly single quotes for quoting the installation directories, in case someone wants to try other funny characters besides spaces. The most recent patch uses double quotes. I'm not sure what degree of support we want to achieve there. -- 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] FK NOT VALID can't be deferrable?
Excerpts from Simon Riggs's message of mié jun 15 16:31:45 -0400 2011: On Wed, Jun 15, 2011 at 9:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: So is somebody from 2nd Quadrant going to supply a patch to fix this? My understanding was that your patch had a bug, rather than the existing code. If I misunderstood, please explain the bug. This is about FOREIGN KEY NOT VALID, which is your patch that's already in 9.1. My patch doesn't touch foreign keys. CHECK constraints cannot be deferrable anyway. In terms of 2ndQuadrant supplying patches, you may not be aware that we all work independently on community contributions... I wasn't, but that doesn't change anything -- I mean, you were from 2nd Quadrant last time I checked. If Jaime is going to submit a patch to fix the bug, I assume you're fine with that too? (The other alternative is to leave the bug open until Robert or Tom fix it, I guess) -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Tom Lane wrote: Christopher Browne cbbro...@gmail.com writes: On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian br...@momjian.us wrote: [ just recommend using a different port number during pg_upgrade ] +1... That seems to have lots of nice properties. Yeah, that seems like an appropriate expenditure of effort. It's surely not bulletproof, since someone could intentionally connect to the actual port number, but getting to bulletproof is a lot more work than anyone seems to want to do right now. (And, as Bruce pointed out, no complete solution would be back-patchable anyway.) OK, let me work on that. -- 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] pg_upgrade using appname to lock out other users
Peter Eisentraut wrote: On ons, 2011-06-15 at 13:35 -0400, Bruce Momjian wrote: I now believe we are overthinking all this. pg_upgrade has always supported specification of a port number. Why not just tell users to specify an unused port number 1023, and not to use the default value? Both old and new clusters will happily run on any specified port number during the upgrade. This allows the lockout to work for both old and new clusters, which is better than enhancing -b because that will only be for 9.1 servers. On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. Yes, it would be nice to just create the socket in the current directory. The fact it doesn't work on Windows would cause our docs to have to differ for Windows, which seems unfortunate. -- 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] FK NOT VALID can't be deferrable?
Alvaro Herrera alvhe...@commandprompt.com writes: So is somebody from 2nd Quadrant going to supply a patch to fix this? I'm already on it. The whole patch appears to need some review, considering this is about the fourth major flaw we've found in it. 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] Why polecat and colugos are failing to build back branches
Peter Eisentraut pete...@gmx.net writes: As a secondary point, we have so far used mostly single quotes for quoting the installation directories, in case someone wants to try other funny characters besides spaces. The most recent patch uses double quotes. I'm not sure what degree of support we want to achieve there. Oh, hm, I had noted double quotes in the one place that wasn't broken in test/regress/Makefile, and followed that precedent. But you're probably right that single quotes would be better, since that would prevent funny business from $ characters for instance. We're not going to be able to make both types of quote characters safe, unless gmake has some quoting function I'm not aware of (which is surely possible). 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] FK NOT VALID can't be deferrable?
On Wed, Jun 15, 2011 at 9:59 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Simon Riggs's message of mié jun 15 16:31:45 -0400 2011: On Wed, Jun 15, 2011 at 9:14 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: So is somebody from 2nd Quadrant going to supply a patch to fix this? My understanding was that your patch had a bug, rather than the existing code. If I misunderstood, please explain the bug. This is about FOREIGN KEY NOT VALID, which is your patch that's already in 9.1. My patch doesn't touch foreign keys. CHECK constraints cannot be deferrable anyway. OK, thanks. My bug, my responsibility to provide a solution. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. Yes, it would be nice to just create the socket in the current directory. The fact it doesn't work on Windows would cause our docs to have to differ for Windows, which seems unfortunate. It still wouldn't be bulletproof against someone running as the postgres user, so probably not worth the trouble. 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] FK NOT VALID can't be deferrable?
On Wed, Jun 15, 2011 at 10:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: So is somebody from 2nd Quadrant going to supply a patch to fix this? I'm already on it. The whole patch appears to need some review, considering this is about the fourth major flaw we've found in it. I'll leave it with you then, but I remain happy to fix. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] flexible array members
Peter Eisentraut pete...@gmx.net writes: Is this a route we want to go down? - GISTENTRY vector[1]; /* variable-length array */ + GISTENTRY vector[FLEXIBLE_ARRAY_MEMBER]; Yes, I was thinking about the same trick after noting these warnings on Fedora 15, although personally I'd name the macro VARIABLE_LENGTH_ARRAY. One thing that is a bit concerning is that throwing more flexible array members around the code wherever variable-length arrays are used results in crash and burn. Probably some places are using sizeof or offsetof on these structures in incompatible ways. So each place would have to be examined separately. Hmm, that's nasty. But from a code-documentation standpoint I think this is a useful improvement, so it seems worth doing the work to clean things up. (I do recall a number of places that assume that sizeof() includes a single array element ...) 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] On-the-fly index tuple deletion vs. hot_standby
On Tue, Jun 14, 2011 at 5:28 AM, Noah Misch n...@leadboat.com wrote: On Mon, Jun 13, 2011 at 04:16:06PM +0100, Simon Riggs wrote: On Mon, Jun 13, 2011 at 3:11 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 12, 2011 at 3:01 PM, Noah Misch n...@leadboat.com wrote: Assuming that conclusion, I do think it's worth starting with something simple, even if it means additional bloat on the master in the wal_level=hot_standby + vacuum_defer_cleanup_age / hot_standby_feedback case. In choosing those settings, the administrator has taken constructive steps to accept master-side bloat in exchange for delaying recovery conflict. ?What's your opinion? I'm pretty disinclined to go tinkering with 9.1 at this point, too. Not least because a feature already exists in 9.1 to cope with this problem: hot standby feedback. A standby's receipt of an XLOG_BTREE_REUSE_PAGE record implies that the accompanying latestRemovedXid preceded or equaled the master's RecentXmin at the time of issue (see _bt_page_recyclable()). Neither hot_standby_feedback nor vacuum_defer_cleanup_age affect RecentXmin. Therefore, neither facility delays conflicts arising directly from B-tree page reuse. See attached test script, which yields a snapshot conflict despite active hot_standby_feedback. OK, agreed. Bug. Good catch, Noah. Fix is to use RecentGlobalXmin for the cutoff when in Hot Standby mode, so that it is under user control. Attached patch will be applied to head and backpatched to 9.1 and 9.0 to fix this. No effect on non-users of Hot Standby. Minimal invasive for HS users. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services hs_page_recyclable_respect.v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Strict Set Returning Functions
Simon Riggs si...@2ndquadrant.com writes: On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: So a function that is both STRICT and SET RETURNING will return rows. Really? The case behaves as expected for me. Seems that's the wrong question. Let me return to why I raised this: Why does evaluate_function() specifically avoid returning NULL for a set returning function? Because replacing the SRF call with a constant NULL would produce the wrong result, ie, a single row containing NULL, not zero rows. 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] gcc 4.6 -Wunused-but-set-variable
Peter Eisentraut pete...@gmx.net writes: I couldn't see a way good way of programming around this (perhaps in the second case, but it would get uselessly ugly), so perhaps just marking the variables as potentially unused would be appropriate? See patch. Of course this would break not only on non-gcc compilers, but old versions of gcc. I'd suggest a macro (cf PERL_UNUSED_DECL) and some version checks at the site of the macro declaration (perhaps the ones emitted by bison for its use of this construct will do). 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] creating CHECK constraints as NOT VALID
Alvaro Herrera alvhe...@commandprompt.com writes: Here's an updated patch fixing all of the above. I stole your first test case and added it to regression, after some editorialization. I've probably created some merge conflicts for you in process of fixing the FOREIGN KEY NOT VALID patch, but in any case you need to change this to use ConstraintAttributeSpec rather than a duplicate production. 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] Strict Set Returning Functions
On Thu, Jun 16, 2011 at 12:24 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, Jun 15, 2011 at 8:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: So a function that is both STRICT and SET RETURNING will return rows. Really? The case behaves as expected for me. Seems that's the wrong question. Let me return to why I raised this: Why does evaluate_function() specifically avoid returning NULL for a set returning function? Because replacing the SRF call with a constant NULL would produce the wrong result, ie, a single row containing NULL, not zero rows. OK, thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Commitfest 2011-6 is underway! Reviewers needed.
All, I've trolled this list, and I think I added in all patches which were submitted here but not on the commitfest app. Can someone double-check for me? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Tom Lane wrote: Christopher Browne cbbro...@gmail.com writes: On Wed, Jun 15, 2011 at 5:35 PM, Bruce Momjian br...@momjian.us wrote: [ just recommend using a different port number during pg_upgrade ] +1... That seems to have lots of nice properties. Yeah, that seems like an appropriate expenditure of effort. It's surely not bulletproof, since someone could intentionally connect to the actual port number, but getting to bulletproof is a lot more work than anyone seems to want to do right now. (And, as Bruce pointed out, no complete solution would be back-patchable anyway.) I have researched this and need feedback. Initially I wanted to use a single -p port flag to be used by the old and new clusters. However, pg_upgrade allows --check mode while the old server is running, so we need to allow you to use the current old postmaster port number and a different port number to test the new server. That kills the idea of using a single -p flag, so -p and -P are needed. So, do we allow -p and -P to default to DEF_PORT or PGPORT? For the live server check, that would be nice, but for the other cases we probably need a different port number. This does mean that for the most common use case they will be specifying the same port number for -p and -P, except for a live check. I am guessing we don't want any port number defaults. People are going to think it is odd to have to supply the same port number for -p and -P. We could allow -P to default to -p when not doing a check, but that seems confusing. Do we want -P to only be used in --check mode? That seems confusing too -- that would mean -p is the old server in --check mode, and the old and new server in non-check mode. -- 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] pg_upgrade using appname to lock out other users
Bruce, * Bruce Momjian (br...@momjian.us) wrote: I have researched this and need feedback. In general, I like the whole idea of using random/special ports for the duration of the upgrade. I agree that we need to keep the ability to check the existing clusters. My gut feeling is this: keep the existing port options just as they are, so --check works just fine, etc. Use *only* long-options for the ports to use during the actual upgrade and discourage their use- we want people to let a random couple of ports be used during the upgrade to minimize the risk of someone connecting to one of the systems. Obvioulsy, there may be special cases where that's not an option, but I don't think we need to make it easy nor do I think we need to have a short option for it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Commitfest 2011-6 is underway! Reviewers needed.
On Wed, Jun 15, 2011 at 8:54 PM, Josh Berkus j...@agliodbs.com wrote: I've trolled this list, and I think I added in all patches which were submitted here but not on the commitfest app. Can someone double-check for me? There's a read and heed that's appropriate here... As hard as we may try, if you imagine your patch should be going into PostgreSQL in the near future, you really ought to check for its presence at http://commitfest.postgresql.org, in one of the commitfests dated in 2011, because others that weren't necessarily aware of its existence mightn't notice its absence. Setting up authentication information and logging in to manage it would be an awesome thing for Sterling Community Characters to do, but it may suffice to make sure that *someone* has been poked into awareness! :-) I'll be taking a poke at some patch or another; the more of us that do so, the less we impose on those that are trying to get 9.1 out of beta! -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- 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_upgrade using appname to lock out other users
Stephen Frost wrote: -- Start of PGP signed section. Bruce, * Bruce Momjian (br...@momjian.us) wrote: I have researched this and need feedback. In general, I like the whole idea of using random/special ports for the duration of the upgrade. I agree that we need to keep the ability to check the existing clusters. My gut feeling is this: keep the existing port options just as they are, so --check works just fine, etc. Use *only* long-options for the ports to use during the actual upgrade and discourage their use- we want people to let a random couple of ports be used during the upgrade to minimize the risk of someone connecting to one of the systems. Obvioulsy, there may be special cases where that's not an option, but I don't think we need to make it easy nor do I think we need to have a short option for it. Having long options mean different than short options seems very confusing. -- 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] pg_upgrade using appname to lock out other users
* Bruce Momjian (br...@momjian.us) wrote: Having long options mean different than short options seems very confusing. Err, that wasn't what I was proposing.. Just having: --old-port-during-upgrade and similar would have to be used if they want to specify the ports to be used during the upgrade proces... We just wouldn't have a short-option for that option, since we discourage it.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade using appname to lock out other users
Stephen Frost wrote: -- Start of PGP signed section. * Bruce Momjian (br...@momjian.us) wrote: Having long options mean different than short options seems very confusing. Err, that wasn't what I was proposing.. Just having: --old-port-during-upgrade and similar would have to be used if they want to specify the ports to be used during the upgrade proces... We just wouldn't have a short-option for that option, since we discourage it.. I think that is going to be very hard to document --- seems easier to just use -p and -P always. -- 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] Why polecat and colugos are failing to build back branches
I wrote: Peter Eisentraut pete...@gmx.net writes: As a secondary point, we have so far used mostly single quotes for quoting the installation directories, in case someone wants to try other funny characters besides spaces. The most recent patch uses double quotes. I'm not sure what degree of support we want to achieve there. Oh, hm, I had noted double quotes in the one place that wasn't broken in test/regress/Makefile, and followed that precedent. But you're probably right that single quotes would be better, since that would prevent funny business from $ characters for instance. We're not going to be able to make both types of quote characters safe, unless gmake has some quoting function I'm not aware of (which is surely possible). I changed the places I'd modified yesterday to use single quotes not double, and fixed a couple other spots in passing to do the same. However, I don't see any simple way to deal with the places where we are sticking paths into C-string constants, to wit in building pg_config and pg_config_paths.h. pg_config in particular is nasty because there are pretty much guaranteed to be single quotes in the value we want for VAL_LDFLAGS (because of rpath...). So at the moment, we're not really safe against single *or* double quotes, nor dollar signs, in installation paths. About the only good thing to be said about it is that these characters are so troublesome that Unix users are unlikely to use them in directory names anyway. 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: [WIP] Support for ANY/ALL(array) op scalar (Was: Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY)
Florian Pflug f...@phlo.org writes: Comments are extremely welcome, especially ones regarding the overall approach taken in this patch. If people consider that to be acceptable, I'd try to add the missing features and add documentation. Quite honestly, I don't like this one bit and would rather you not pursue the idea. There is no such syntax in the standard, and presumably that's not because the SQL committee never thought of it. They may have some incompatible idea in mind for the future, who knows? But in any case, this won't provide any functionality whatever that we couldn't provide at much less effort and risk, just by providing commutator operators for the few missing cases. (FWIW, I've come around to liking the idea of using =~ and the obvious variants of that for regex operators, mainly because of the Perl precedent.) 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