Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-15 Thread Jaime Casanova
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

2011-06-15 Thread Jaime Casanova
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?

2011-06-15 Thread Jaime Casanova
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

2011-06-15 Thread Leonardo Francalanci
 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

2011-06-15 Thread Alexander Korotkov
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

2011-06-15 Thread Alexander Korotkov
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

2011-06-15 Thread Leonardo Francalanci
 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

2011-06-15 Thread Dean Rasheed
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?

2011-06-15 Thread Greg Smith
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

2011-06-15 Thread Robert Creager

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

2011-06-15 Thread Heikki Linnakangas

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?

2011-06-15 Thread Dean Rasheed
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

2011-06-15 Thread Alexander Korotkov
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?

2011-06-15 Thread Rainer Pruy
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

2011-06-15 Thread Magnus Hagander
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

2011-06-15 Thread Heikki Linnakangas

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

2011-06-15 Thread Leonardo Francalanci
   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

2011-06-15 Thread Heikki Linnakangas

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

2011-06-15 Thread Dave Page
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

2011-06-15 Thread Pavel Stehule
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Robert Haas
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?

2011-06-15 Thread Robert Haas
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?

2011-06-15 Thread Gurjeet Singh
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?

2011-06-15 Thread Gurjeet Singh
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

2011-06-15 Thread Merlin Moncure
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

2011-06-15 Thread Andrew Dunstan



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

2011-06-15 Thread Christopher Browne
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

2011-06-15 Thread Christopher Browne
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?

2011-06-15 Thread Joshua D. Drake

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?

2011-06-15 Thread Robert Haas
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?

2011-06-15 Thread Gurjeet Singh
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?

2011-06-15 Thread Tom Lane
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?

2011-06-15 Thread Tom Lane
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?

2011-06-15 Thread Alvaro Herrera
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

2011-06-15 Thread Kevin Grittner
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

2011-06-15 Thread Merlin Moncure
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

2011-06-15 Thread Alvaro Herrera
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?

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Merlin Moncure
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

2011-06-15 Thread Alvaro Herrera
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?

2011-06-15 Thread Robert Haas
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

2011-06-15 Thread PostgreSQL - Hans-Jürgen Schönig
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?

2011-06-15 Thread Greg Sabino Mullane

-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

2011-06-15 Thread Robert Haas
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Peter Geoghegan
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?

2011-06-15 Thread Bernd Helmle



--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

2011-06-15 Thread Robert Haas
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

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Alvaro Herrera
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Alvaro Herrera
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

2011-06-15 Thread Alvaro Herrera
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-06-15 Thread Merlin Moncure
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

2011-06-15 Thread Robert Haas
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Christopher Browne
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

2011-06-15 Thread Kevin Grittner
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Tom Lane
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?

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Martin Pihlak
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

2011-06-15 Thread Alvaro Herrera
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

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Peter Eisentraut
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

2011-06-15 Thread Peter Eisentraut
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?

2011-06-15 Thread Alvaro Herrera
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?

2011-06-15 Thread Jaime Casanova
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

2011-06-15 Thread Peter Eisentraut
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

2011-06-15 Thread Dan Ports
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?

2011-06-15 Thread Simon Riggs
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.

2011-06-15 Thread Josh Berkus
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

2011-06-15 Thread Peter Eisentraut
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?

2011-06-15 Thread Alvaro Herrera
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Bruce Momjian
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?

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Tom Lane
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?

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Tom Lane
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?

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Simon Riggs
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Tom Lane
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

2011-06-15 Thread Simon Riggs
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.

2011-06-15 Thread Josh Berkus
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Stephen Frost
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.

2011-06-15 Thread Christopher Browne
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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Stephen Frost
* 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

2011-06-15 Thread Bruce Momjian
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

2011-06-15 Thread Tom Lane
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)

2011-06-15 Thread Tom Lane
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


  1   2   >