Re: [HACKERS] the big picture for index-only scans

[Heikki Linnakangas]

On 19.08.2011 23:17, Robert Haas wrote:

On Fri, Aug 19, 2011 at 4:02 PM, Robert Haas  wrote:

Hmm, you have a point.  If 100 backends simultaneously write to 100
different pages, and all of those pages are all-visible, then it's
possible that they could end up fighting over the buffer content lock
on the visibility map page.  But why would you expect that to matter?
In a heavily updated table, the proportion of visibility map bits that
are set figures to be quite low, since they're only set during VACUUM.
  To have 100 backends simultaneously pick different pages to write
each of which is all-visible seems really unlucky.   Even if it does
happen from time to time, I suspect the effects would be largely
masked by WALInsertLock contention.  The visibility map content lock
is only taken very briefly, whereas the operations protected by
WALInsertLock are much more complex.


Oh, snap.  I see another possible problem here.

At the time visibilitymap_clear() is called, we're already (and
necessarily) holding a content lock on the buffer.  And then we go get
a content lock on the visibility map page, whose buffer number might
be higher or lower than that of the heap page, possibly leading us to
violate the rule the buffer content locks must be taken increasing
buffer number order.


Huh? The rule is that you have to acquire locks on heap pages in 
increasing page number order. That doesn't apply to the order between 
the heap and the visibility map. The rule we've established for that is 
that you have to acquire the lock on the heap page first, before locking 
the corresponding vm page. It would be good to add a comment about that 
to the header comment of RelationGetBufferForTuple(), there doesn't seem 
to be anything about the visibility map buffer arguments there.


--
  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] the big picture for index-only scans

[Heikki Linnakangas]

On 19.08.2011 23:02, Robert Haas wrote:

On Fri, Aug 19, 2011 at 2:51 PM, Gokulakannan Somasundaram
  wrote:

won't it make the 99
sessions wait for that visibility map while holding the exclusive lock on
the 99 heap pages?


Hmm, you have a point.  If 100 backends simultaneously write to 100
different pages, and all of those pages are all-visible, then it's
possible that they could end up fighting over the buffer content lock
on the visibility map page.  But why would you expect that to matter?
In a heavily updated table, the proportion of visibility map bits that
are set figures to be quite low, since they're only set during VACUUM.
  To have 100 backends simultaneously pick different pages to write
each of which is all-visible seems really unlucky.   Even if it does
happen from time to time, I suspect the effects would be largely
masked by WALInsertLock contention.  The visibility map content lock
is only taken very briefly, whereas the operations protected by
WALInsertLock are much more complex.


The above could already happen in 8.4, where the visibility map was 
introduced. The contention on the VM buffer would be just as bad whether 
you hold the heap page lock at the same time or not. I have not heard 
any complaints of contention on VM buffers.


--
  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] two index bitmap scan of a big table & hash_seq_search

[Tom Lane]

"Sergey E. Koposov"  writes:
> But the funny thing I noticed is that the query after running a certain 
> amount of time doing I/O, starts to use 100%CPU and spend 99% the time in 
> hash_seq_search. Here is the oprofile of PG during that period:
> 
> CPU: Intel Core/i7, speed 2.268e+06 MHz (estimated)
> Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit 
> mask of 0x00 (No unit mask) count 10
> samples  %symbol name
> 303404   99.3562  hash_seq_search
> 1163  0.3808  tbm_lossify
> 639   0.2093  hash_search_with_hash_value

It seems like you've uncovered a scaling limitation in the tidbitmap
logic when it has to deal with very very large numbers of pages.

I might be reading too much into the mention of tbm_lossify, but
I wonder if the problem is repeated invocations of tbm_lossify()
as the bitmap gets larger.  Maybe that function needs to be more
aggressive about how much information it deletes per call.

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] New copyright program

[David Fetter]

On Fri, Aug 19, 2011 at 09:49:35PM -0400, Bruce Momjian wrote:
> David Fetter wrote:
> > On Fri, Aug 19, 2011 at 09:22:03PM -0400, Andrew Dunstan wrote:
> > > On 08/19/2011 09:02 PM, David Fetter wrote:
> > > >On Fri, Aug 19, 2011 at 07:37:29PM -0400, Andrew Dunstan wrote:
> > > >>
> > > >>On 08/19/2011 07:31 PM, Bruce Momjian wrote:
> > > >>>Kris Jurka wrote:
> > > Did you also try the "use Tie::File" addition in my fix because your
> > > current coding doesn't work at all.  The tie operation is key to 
> > > actually
> > > writing out the modified copyright notice.  Your version just updates 
> > > the
> > > copyright year in memory, but never gets it back to the file.
> > > >>>Ah, that did fix it;  thanks.  I am attached the updated committed
> > > >>>copyright.pl.  I also skipped symlinks.
> > > >>>
> > > >>It probably doesn't matter that much in this context, but I should
> > > >>point out that Tie::File is not universally available. Some years
> > > >>ago I had to revert its use in the buildfarm code for that reason.
> > > >>In general we should try to avoid adding extra dependencies
> > > >>wherever possible.
> > > >Tie::File ships as part of core Perl for all non-EOL versions, so I
> > > >really can't consider this as a problem.
> > > 
> > > Which are those?
> > 
> > 5.12 and 5.14 are still supported.  5.10 and earlier are EOL.
> > 
> > http://news.perlfoundation.org/2011/05/perl-514.html
> 
> Odd the Tie works in my Perl 5.10;  I wonder if I installed it somehow.

Sorry for being unclear.  I didn't mean to imply that earlier versions
of Perl didn't ship with File::Tie.  Just that all supported versions
do.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Andrew Dunstan]

On 08/19/2011 09:39 PM, David Fetter wrote:


Tie::File ships as part of core Perl for all non-EOL versions, so I
really can't consider this as a problem.

Which are those?

5.12 and 5.14 are still supported.  5.10 and earlier are EOL.

http://news.perlfoundation.org/2011/05/perl-514.html




Well, they need to get their story straight. 
 says:


   Please note that branches earlier than 5.8 are no longer supported,
   though fixes for urgent issues, for example severe security
   problems, may still be issued.

What is more, ignoring 5.10 and older would simply foolish. Those 
versions are live on many of the platforms we build Postgres on, and in 
quite modern distributions too, for example in RHEL 6.


Anyway, they point seems moot in this context.

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] New copyright program

[Bruce Momjian]

David Fetter wrote:
> On Fri, Aug 19, 2011 at 09:22:03PM -0400, Andrew Dunstan wrote:
> > On 08/19/2011 09:02 PM, David Fetter wrote:
> > >On Fri, Aug 19, 2011 at 07:37:29PM -0400, Andrew Dunstan wrote:
> > >>
> > >>On 08/19/2011 07:31 PM, Bruce Momjian wrote:
> > >>>Kris Jurka wrote:
> > Did you also try the "use Tie::File" addition in my fix because your
> > current coding doesn't work at all.  The tie operation is key to 
> > actually
> > writing out the modified copyright notice.  Your version just updates 
> > the
> > copyright year in memory, but never gets it back to the file.
> > >>>Ah, that did fix it;  thanks.  I am attached the updated committed
> > >>>copyright.pl.  I also skipped symlinks.
> > >>>
> > >>It probably doesn't matter that much in this context, but I should
> > >>point out that Tie::File is not universally available. Some years
> > >>ago I had to revert its use in the buildfarm code for that reason.
> > >>In general we should try to avoid adding extra dependencies
> > >>wherever possible.
> > >Tie::File ships as part of core Perl for all non-EOL versions, so I
> > >really can't consider this as a problem.
> > 
> > Which are those?
> 
> 5.12 and 5.14 are still supported.  5.10 and earlier are EOL.
> 
> http://news.perlfoundation.org/2011/05/perl-514.html

Odd the Tie works in my Perl 5.10;  I wonder if I installed it somehow.

-- 
  Bruce Momjian  http://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] New copyright program

[David Fetter]

On Fri, Aug 19, 2011 at 09:22:03PM -0400, Andrew Dunstan wrote:
> On 08/19/2011 09:02 PM, David Fetter wrote:
> >On Fri, Aug 19, 2011 at 07:37:29PM -0400, Andrew Dunstan wrote:
> >>
> >>On 08/19/2011 07:31 PM, Bruce Momjian wrote:
> >>>Kris Jurka wrote:
> Did you also try the "use Tie::File" addition in my fix because your
> current coding doesn't work at all.  The tie operation is key to actually
> writing out the modified copyright notice.  Your version just updates the
> copyright year in memory, but never gets it back to the file.
> >>>Ah, that did fix it;  thanks.  I am attached the updated committed
> >>>copyright.pl.  I also skipped symlinks.
> >>>
> >>It probably doesn't matter that much in this context, but I should
> >>point out that Tie::File is not universally available. Some years
> >>ago I had to revert its use in the buildfarm code for that reason.
> >>In general we should try to avoid adding extra dependencies
> >>wherever possible.
> >Tie::File ships as part of core Perl for all non-EOL versions, so I
> >really can't consider this as a problem.
> 
> Which are those?

5.12 and 5.14 are still supported.  5.10 and earlier are EOL.

http://news.perlfoundation.org/2011/05/perl-514.html

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Andrew Dunstan]

On 08/19/2011 09:02 PM, David Fetter wrote:

On Fri, Aug 19, 2011 at 07:37:29PM -0400, Andrew Dunstan wrote:


On 08/19/2011 07:31 PM, Bruce Momjian wrote:

Kris Jurka wrote:

Did you also try the "use Tie::File" addition in my fix because your
current coding doesn't work at all.  The tie operation is key to actually
writing out the modified copyright notice.  Your version just updates the
copyright year in memory, but never gets it back to the file.

Ah, that did fix it;  thanks.  I am attached the updated committed
copyright.pl.  I also skipped symlinks.


It probably doesn't matter that much in this context, but I should
point out that Tie::File is not universally available. Some years
ago I had to revert its use in the buildfarm code for that reason.
In general we should try to avoid adding extra dependencies wherever
possible.

Tie::File ships as part of core Perl for all non-EOL versions, so I
really can't consider this as a problem.




Which are those?

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] New copyright program

[David Fetter]

On Fri, Aug 19, 2011 at 07:37:29PM -0400, Andrew Dunstan wrote:
> 
> 
> On 08/19/2011 07:31 PM, Bruce Momjian wrote:
> >Kris Jurka wrote:
> >>Did you also try the "use Tie::File" addition in my fix because your
> >>current coding doesn't work at all.  The tie operation is key to actually
> >>writing out the modified copyright notice.  Your version just updates the
> >>copyright year in memory, but never gets it back to the file.
> >Ah, that did fix it;  thanks.  I am attached the updated committed
> >copyright.pl.  I also skipped symlinks.
> >
> 
> It probably doesn't matter that much in this context, but I should
> point out that Tie::File is not universally available. Some years
> ago I had to revert its use in the buildfarm code for that reason.
> In general we should try to avoid adding extra dependencies wherever
> possible.

Tie::File ships as part of core Perl for all non-EOL versions, so I
really can't consider this as a problem.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Kris Jurka]

On Fri, 19 Aug 2011, Bruce Momjian wrote:

> Andrew Dunstan wrote:
> > 
> > It probably doesn't matter that much in this context, but I should point 
> > out that Tie::File is not universally available. Some years ago I had to 
> > revert its use in the buildfarm code for that reason. In general we 
> > should try to avoid adding extra dependencies wherever possible.
> 
> 
> I can easily change this to rewrite files that contain copyright changes
> --- should I?
> 

No.  We don't need a super portable copyright year changing script.

Kris Jurka

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> On 08/19/2011 07:31 PM, Bruce Momjian wrote:
> > Kris Jurka wrote:
> >> Did you also try the "use Tie::File" addition in my fix because your
> >> current coding doesn't work at all.  The tie operation is key to actually
> >> writing out the modified copyright notice.  Your version just updates the
> >> copyright year in memory, but never gets it back to the file.
> > Ah, that did fix it;  thanks.  I am attached the updated committed
> > copyright.pl.  I also skipped symlinks.
> >
> 
> It probably doesn't matter that much in this context, but I should point 
> out that Tie::File is not universally available. Some years ago I had to 
> revert its use in the buildfarm code for that reason. In general we 
> should try to avoid adding extra dependencies wherever possible.

Oh, great.  :-(

I can easily change this to rewrite files that contain copyright changes
--- should I?

-- 
  Bruce Momjian  http://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] New copyright program

[Andrew Dunstan]

On 08/19/2011 07:31 PM, Bruce Momjian wrote:

Kris Jurka wrote:

Did you also try the "use Tie::File" addition in my fix because your
current coding doesn't work at all.  The tie operation is key to actually
writing out the modified copyright notice.  Your version just updates the
copyright year in memory, but never gets it back to the file.

Ah, that did fix it;  thanks.  I am attached the updated committed
copyright.pl.  I also skipped symlinks.



It probably doesn't matter that much in this context, but I should point 
out that Tie::File is not universally available. Some years ago I had to 
revert its use in the buildfarm code for that reason. In general we 
should try to avoid adding extra dependencies wherever possible.


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] New copyright program

[Bruce Momjian]

Kris Jurka wrote:
> 
> 
> On Fri, 19 Aug 2011, Bruce Momjian wrote:
> 
> > Was able to reproduce the error you reported with Perl 5.10.  I then
> > tried the single-quote idea I got from Googling, but then got an error
> > about TIEARRAY being missing, so I recoded it as a simple file
> > open/close.  I also incorported your regex fix.  Path attached and
> > applied.  Thanks.
> > 
> 
> Did you also try the "use Tie::File" addition in my fix because your 
> current coding doesn't work at all.  The tie operation is key to actually 
> writing out the modified copyright notice.  Your version just updates the 
> copyright year in memory, but never gets it back to the file.

Ah, that did fix it;  thanks.  I am attached the updated committed
copyright.pl.  I also skipped symlinks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
#!/usr/bin/perl 
#
# copyright.pl -- update copyright notices throughout the source tree, 
idempotently.
#
# Copyright (c) 2011, PostgreSQL Global Development Group
#
# src/tools/copyright.pl
#

use strict;
use warnings;

use File::Find;
use Tie::File;

my $pgdg = 'PostgreSQL Global Development Group';
my $cc = 'Copyright \(c\) ';
# year-1900 is what localtime(time) puts in element 5
my $year = 1900 + ${[localtime(time)]}[5];

print "Using current year:  $year\n";

find({wanted => \&wanted, no_chdir => 1}, '.');

sub wanted {
return if ! -f $File::Find::name || -l $File::Find::name;

my @lines;
tie @lines, "Tie::File", $File::Find::name;

foreach my $line (@lines) {
# We only care about lines with a copyright notice.
next unless $line =~ m/$cc.*$pgdg/;
# We stop when we've done one substitution.  This is both for
# efficiency and, at least in the case of this program, for
# correctness.
last if $line =~ m/$cc.*$year.*$pgdg/;
last if $line =~ s/($cc\d{4})(, $pgdg)/$1-$year$2/;
last if $line =~ s/($cc\d{4})-\d{4}(, $pgdg)/$1-$year$2/;
}
untie @lines;
}

print "Manually update doc/src/sgml/legal.sgml and 
src/interfaces/libpq/libpq.rc.in too\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] New copyright program

[Kris Jurka]

On Fri, 19 Aug 2011, Bruce Momjian wrote:

> Was able to reproduce the error you reported with Perl 5.10.  I then
> tried the single-quote idea I got from Googling, but then got an error
> about TIEARRAY being missing, so I recoded it as a simple file
> open/close.  I also incorported your regex fix.  Path attached and
> applied.  Thanks.
> 

Did you also try the "use Tie::File" addition in my fix because your 
current coding doesn't work at all.  The tie operation is key to actually 
writing out the modified copyright notice.  Your version just updates the 
copyright year in memory, but never gets it back to the file.

Kris Jurka

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Bruce Momjian]

Kris Jurka wrote:
> 
> 
> On Fri, 19 Aug 2011, Kris Jurka wrote:
> 
> > For me this fails with:
> > 
> > Bareword "Tie::File" not allowed while "strict subs" in use  at
> > /home/jurka/pg/server/postgresql/src/tools/copyright.pl line 28.
> > 
> 
> This fixes things for me.  The copyright matching wasn't working for me 
> either without escaping the parentheses.

Was able to reproduce the error you reported with Perl 5.10.  I then
tried the single-quote idea I got from Googling, but then got an error
about TIEARRAY being missing, so I recoded it as a simple file
open/close.  I also incorported your regex fix.  Path attached and
applied.  Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/src/tools/copyright.pl b/src/tools/copyright.pl
new file mode 100755
index 96b1f22..91f73e3
*** a/src/tools/copyright.pl
--- b/src/tools/copyright.pl
*** use warnings;
*** 13,19 
  use File::Find;
  
  my $pgdg = 'PostgreSQL Global Development Group';
! my $cc = 'Copyright (c) ';
  # year-1900 is what localtime(time) puts in element 5
  my $year = 1900 + ${[localtime(time)]}[5];
  
--- 13,19 
  use File::Find;
  
  my $pgdg = 'PostgreSQL Global Development Group';
! my $cc = 'Copyright \(c\) ';
  # year-1900 is what localtime(time) puts in element 5
  my $year = 1900 + ${[localtime(time)]}[5];
  
*** print "Using current year:  $year\n";
*** 22,33 
  find({wanted => \&wanted, no_chdir => 1}, '.');
  
  sub wanted {
! return unless -f $File::Find::name;
  
! my @lines;
! tie @lines, Tie::File, $File::Find::name;
  
! foreach my $line (@lines) {
  # We only care about lines with a copyright notice.
  next unless $line =~ m/$cc.*$pgdg/;
  # We stop when we've done one substitution.  This is both for
--- 22,35 
  find({wanted => \&wanted, no_chdir => 1}, '.');
  
  sub wanted {
! my $filename = $File::Find::name;
  
! # only regular files
! return if ! -f $filename;
  
! open(my $FILE, '<', $filename) or die "Cannot open $filename";
! 
! foreach my $line (<$FILE>) {
  # We only care about lines with a copyright notice.
  next unless $line =~ m/$cc.*$pgdg/;
  # We stop when we've done one substitution.  This is both for
*** sub wanted {
*** 37,43 
  last if $line =~ s/($cc\d{4})(, $pgdg)/$1-$year$2/;
  last if $line =~ s/($cc\d{4})-\d{4}(, $pgdg)/$1-$year$2/;
  }
! untie @lines;
  }
  
  print "Manually update doc/src/sgml/legal.sgml and src/interfaces/libpq/libpq.rc.in too\n";
--- 39,45 
  last if $line =~ s/($cc\d{4})(, $pgdg)/$1-$year$2/;
  last if $line =~ s/($cc\d{4})-\d{4}(, $pgdg)/$1-$year$2/;
  }
! close($FILE) or die "Cannot close $filename";
  }
  
  print "Manually update doc/src/sgml/legal.sgml and src/interfaces/libpq/libpq.rc.in too\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] New copyright program

[David Fetter]

On Fri, Aug 19, 2011 at 05:34:01PM -0400, Kris Jurka wrote:
> On Fri, 19 Aug 2011, Kris Jurka wrote:
> 
> > For me this fails with:
> > 
> > Bareword "Tie::File" not allowed while "strict subs" in use  at
> > /home/jurka/pg/server/postgresql/src/tools/copyright.pl line 28.
> 
> This fixes things for me.  The copyright matching wasn't working for me 
> either without escaping the parentheses.

Thanks for fixing this :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New copyright program

[Kris Jurka]

On Fri, 19 Aug 2011, Kris Jurka wrote:

> For me this fails with:
> 
> Bareword "Tie::File" not allowed while "strict subs" in use  at
> /home/jurka/pg/server/postgresql/src/tools/copyright.pl line 28.
> 

This fixes things for me.  The copyright matching wasn't working for me 
either without escaping the parentheses.

Kris Jurkadiff --git a/src/tools/copyright.pl b/src/tools/copyright.pl
index 96b1f22..9531430 100644
--- a/src/tools/copyright.pl
+++ b/src/tools/copyright.pl
@@ -10,10 +10,11 @@
 use strict;
 use warnings;
 
+use Tie::File;
 use File::Find;
 
 my $pgdg = 'PostgreSQL Global Development Group';
-my $cc = 'Copyright (c) ';
+my $cc = 'Copyright \(c\) ';
 # year-1900 is what localtime(time) puts in element 5
 my $year = 1900 + ${[localtime(time)]}[5];
 
@@ -25,7 +26,7 @@ sub wanted {
 return unless -f $File::Find::name;
 
 my @lines;
-tie @lines, Tie::File, $File::Find::name;
+tie @lines, 'Tie::File', $File::Find::name;
 
 foreach my $line (@lines) {
 # We only care about lines with a copyright notice.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transient plans versus the SPI API

[Simon Riggs]

On Fri, Aug 19, 2011 at 6:13 PM, Tom Lane  wrote:
> [ getting back to the planner finally ]
>
> Simon Riggs  writes:
>> On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane  wrote:
>>> Simon Riggs  writes:
 I think its possible to tell automatically whether we need to replan
 always or not based upon the path we take through selectivity
 functions.
>
>>> I don't really believe that, or at least I think it would only detect a
>>> few cases.
>
>> The problem there is which executions we build custom plans for. That
>> turns the problem into a sampling issue and you'll only fix the
>> problems that occur with a frequency to match your sampling pattern
>> and rate. Examples of situations where it won't help.
>
> Sure, this is not going to solve every problem we have with the
> planner.  What it is intended to solve is cases where someone is trying
> to use the prepared-plan mechanisms but he would be a lot better off
> with parameter-value-specific plans.  In particular:

I just realised this is exactly the same strategy as the
no-longer-used JDBC parameter prepareThreshold.

If we treat this in a similar way. prepare_threshold currently = 0 and
you are suggesting we move the value to 5. OK.

Will this be an actual parameter? If so, it removes my objection
because I can turn it off. What would be even better would be some
other controls, like a plugin that allows us to control the mechanism
or at least experiment with it.

Maybe we can assemble enough evidence to remove it before release.

I've been arguing it won't solve all problems. It won't. But if it
solves some, so its worth having.


On another point, I'd still like a "one-shot plan" flag, so that we
can act on that knowledge and have various pieces of code take
decisions that override the plan cache. i.e. if the plan screws up
during execution we can mark the plan as a one shot so it isn't
reused.


>> * plans that vary by table size will be about the same in the first 5
>> executions. After large number of executions, things go bad.
>
> This is a red herring.  The plancache code already arranges to replan
> every time the relevant table stats are updated by autovacuum, which
> should certainly happen from time to time if the table's contents are
> changing materially.  If you're thinking in terms of plans being "stale"
> then you're worrying about a long-since-solved problem.

Fair enough.

-- 
 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] How to define global variable in postgresql

[Valentine Gogichashvili]

> Hello. How can we define a global variable in postgresql?

you can also use global structure in plpython for example:

http://www.postgresql.org/docs/9.0/static/plpython-sharing.html

Re: [HACKERS] New copyright program

[Kris Jurka]

On 8/19/2011 10:51 AM, Bruce Momjian wrote:

David Fetter wrote:

[Here's a new copyright program.]


Thanks.  Applied to HEAD.  I never liked putting scripts in git that
only I could run, but I thought if something happened to me, it would be
good to record what I did.  The Perl solution is perfect.



For me this fails with:

Bareword "Tie::File" not allowed while "strict subs" in use  at 
/home/jurka/pg/server/postgresql/src/tools/copyright.pl line 28.


Using perl -v:
This is perl 5, version 12, subversion 3 (v5.12.3) built for 
x86_64-linux-gnu-thread-multi


Additionally it would be nice if this file was marked executable in git.

Kris Jurka

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] the big picture for index-only scans

[Robert Haas]

On Fri, Aug 19, 2011 at 4:02 PM, Robert Haas  wrote:
> Hmm, you have a point.  If 100 backends simultaneously write to 100
> different pages, and all of those pages are all-visible, then it's
> possible that they could end up fighting over the buffer content lock
> on the visibility map page.  But why would you expect that to matter?
> In a heavily updated table, the proportion of visibility map bits that
> are set figures to be quite low, since they're only set during VACUUM.
>  To have 100 backends simultaneously pick different pages to write
> each of which is all-visible seems really unlucky.   Even if it does
> happen from time to time, I suspect the effects would be largely
> masked by WALInsertLock contention.  The visibility map content lock
> is only taken very briefly, whereas the operations protected by
> WALInsertLock are much more complex.

Oh, snap.  I see another possible problem here.

At the time visibilitymap_clear() is called, we're already (and
necessarily) holding a content lock on the buffer.  And then we go get
a content lock on the visibility map page, whose buffer number might
be higher or lower than that of the heap page, possibly leading us to
violate the rule the buffer content locks must be taken increasing
buffer number order.

Maybe that's OK, because I can't see that we'd ever acquire any other
buffer content lock while already holding a lock on the visibility map
buffer.  But given this logic, if we did do such a thing, it could
result in an undetected deadlock.

Hmm

-- 
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] FATAL: ReleaseSavepoint: unexpected state STARTED

[Robert Haas]

On Fri, Aug 19, 2011 at 3:31 PM, Tom Lane  wrote:
>> As for ROLLBACK, I think it should chuck an error instead of doing
>> this funny 
>> emit-a-warning-and-silently-arrange-for-the-transaction-to-be-aborted-later
>> thing.
>
> I'm pretty unexcited about changing the behavior of established
> mainstream cases just so that we can throw slightly-more-meaningful
> errors in the psql -c case.  ROLLBACK when not in a transaction is not
> an error, only a NOTICE, and it should stay that way.  If you change
> that there are going to be a lot of application and driver authors on
> your doorstep with the usual equipment.

I guess.  It's totally inconsistent, though.  COMMIT emits a WARNING,
ROLLBACK emits a NOTICE, and SAVEPOINT and ROLLBACK TO SAVEPOINT emit
FATAL.   Maybe we should add some commands that throw PANIC and DEBUG1
just for good measure.

The thing that really bothers me is that the way ROLLBACK rolls the
transaction back, but only half-way.  It would be reasonable for it to
JUST emit a notice.  And it would be reasonable for it to error out
and abort the execution of the command string.  But allowing the
execution of the command string to continue while arranging for it to
abort at the end is really pretty strange.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] the big picture for index-only scans

[Robert Haas]

On Fri, Aug 19, 2011 at 2:51 PM, Gokulakannan Somasundaram
 wrote:
> On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas
>  wrote:
>>
>> On 19.08.2011 21:06, Gokulakannan Somasundaram wrote:
>>>
>>> If you are following the same design that Heikki put forward, then there
>>> is
>>> a problem with it in maintaining the bits in page and the bits in
>>> visibility
>>> map in sync, which we have already discussed.
>>
>> Are you referring to this:
>> http://archives.postgresql.org/pgsql-hackers/2010-02/msg02097.php ? I
>> believe Robert's changes to make the visibility map crash-safe covers that.
>> Clearing the bit in the visibility map now happens within the same critical
>> section as clearing the flag on the heap page and writing th WAL record.
>>
> In that case, say a 100 sessions are trying to update records which fall
> under the 8000*4 heap pages( i assume 2 bits per visibility map - 8 * 1024 *
> 4 exact) covered by one page of visibility map,

There are about 8000 visibility map bytes per page, so about 64000
bits, each covering one page.  So a visibility map page covers about
512MB of heap.

> won't it make the 99
> sessions wait for that visibility map while holding the exclusive lock on
> the 99 heap pages?

Hmm, you have a point.  If 100 backends simultaneously write to 100
different pages, and all of those pages are all-visible, then it's
possible that they could end up fighting over the buffer content lock
on the visibility map page.  But why would you expect that to matter?
In a heavily updated table, the proportion of visibility map bits that
are set figures to be quite low, since they're only set during VACUUM.
 To have 100 backends simultaneously pick different pages to write
each of which is all-visible seems really unlucky.   Even if it does
happen from time to time, I suspect the effects would be largely
masked by WALInsertLock contention.  The visibility map content lock
is only taken very briefly, whereas the operations protected by
WALInsertLock are much more complex.

This does, however, remind me of two other points:

1. Heikki's idea of trying to set visibility map bits more
aggressively is probably a good one, but it would be possible to
overdo it, because setting visibility map bits is not free. It has an
immediate cost - in that we have to write xlog - and a deferred cost -
in that it will impose overhead when those pages are re-dirtied.  At
the moment, I think we're probably too far in the opposite direction -
i.e. we leave the visibility map bits unset for too long, leading to a
massive amount of deferred work that gets done all at once when VACUUM
finally runs.  But we shouldn't overcorrect.

2. While we're tinkering with the visibility map, we should think
about whether it makes sense to carve out some more bits for such
purposes as we may in the future require.  Even if we allowed each
heap page a byte in the visibility map instead of a single bit, the
visibility map would still be roughly 1000 times smaller than the
heap; and if there are any situations where the page-level locks
become choke points, this would mitigate that effect.  There might
also be some advantage in that bytes can be atomically set, while bits
can't, although I can't immediately think how we'd leverage that.
Alternatively, we could widen the field to something less than a full
byte, like 2 or 4 bits, if that seems better.

-- 
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] FATAL: ReleaseSavepoint: unexpected state STARTED

[Tom Lane]

Robert Haas  writes:
> I spent some time looking at this afternoon and it appears that the
> root of this problem is that we're a bit schizophrenic about whether a
> multi-query command string constitutes a transaction or not.

Yeah.  The current behavior sort of automatically adds a BEGIN and a
COMMIT around the string, but it's evidently not tied into the real
BEGIN and COMMIT commands well enough.

> As for ROLLBACK, I think it should chuck an error instead of doing
> this funny 
> emit-a-warning-and-silently-arrange-for-the-transaction-to-be-aborted-later
> thing.

I'm pretty unexcited about changing the behavior of established
mainstream cases just so that we can throw slightly-more-meaningful
errors in the psql -c case.  ROLLBACK when not in a transaction is not
an error, only a NOTICE, and it should stay that way.  If you change
that there are going to be a lot of application and driver authors on
your doorstep with the usual equipment.

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] FATAL: ReleaseSavepoint: unexpected state STARTED

[Robert Haas]

On Thu, Aug 18, 2011 at 3:57 AM, Marcin Mańk  wrote:
> On Wed, Aug 17, 2011 at 11:30 PM, Tom Lane  wrote:
>> =?UTF-8?B?TWFyY2luIE1hxYRr?=  writes:
>>>  psql -c 'release q; prepare q(int) as select 1'
>>> FATAL:  ReleaseSavepoint: unexpected state STARTED
>>
>> Can't get terribly excited about that, seeing that the statement is
>> surely going to draw an error and abort processing the rest of the
>> command string in any case ...
>
> Oh, I thought FATAL was disconnectiong other sessions. Yeah, that was
> not bad enough.
>
> Here is a better one:
>
> psql postgres -c 'savepoint q; select 1'
> FATAL:  DefineSavepoint: unexpected state STARTED
> server closed the connection unexpectedly
>        This probably means the server terminated abnormally
>        before or while processing the request.

I spent some time looking at this afternoon and it appears that the
root of this problem is that we're a bit schizophrenic about whether a
multi-query command string constitutes a transaction or not.For
example, DECLARE CURSOR works fine in that context, and you can fetch
from the cursor.  Since that command normally only works from within a
transaction, you might reasonably conclude "hey, we're in a
transaction".   Furthermore, commands that can't be run from with a
transaction context are fenced out here, too - e.g. VACUUM.  The error
message that you get there shows that someone thought about this
specific case:

[rhaas pgsql]$ psql -c 'SELECT 1;VACUUM'
ERROR:  VACUUM cannot be executed from a function or multi-command string

The transaction control commands are not on the same page.  BEGIN
thinks we're not in a transaction, so you can use BEGIN to start one.
If you do that then things are pretty normal.  Phew.  But let's say
you don't use BEGIN.  ROLLBACK claims that you aren't in a transaction
and therefore you can't roll back:

[rhaas pgsql]$ psql -c 'create table xyz(); rollback'
NOTICE:  there is no transaction in progress
ROLLBACK

But if there really were no transaction in progress at the point where
ROLLBACK was issued, then the rollback wouldn't do anything.  In fact,
it does do something: it prevents xyz from getting created.  Crazily
enough, it does this without aborting the execution of the remaining
commands:

[rhaas pgsql]$ psql -c 'create table xyz(); rollback; select 1'
NOTICE:  there is no transaction in progress
 ?column?
--
1
(1 row)

So, from ROLLBACK's perspective, you are sort of in a transaction, and
sort of not in a transaction.

SAVEPOINT and ROLLBACK TO SAVEPOINT are normally called after
RequireTransactionChain(), so that they can only be called from within
a transaction block.  But exec_simple_query is passing down isTopLevel
= true, so RequireTransactionChain() eventually gets that value in the
mail and says "oh, good.  we're in a transaction.  these commands can
be allowed to work!"  But then when the functions actually
implementing those commands are invoked, they view TBLOCK_STARTED as
unacceptable, and a FATAL error results.

If backward compatibility were no object, I'd be tempted to deal with
this by turning a multi-query command string into a full-fledged
transaction.  But that would break things like psql -c 'BEGIN; ...do
stuff...; COMMIT; BEGIN; ...do other stuff...; COMMIT', which is
probably common enough that we shouldn't indiscriminately break it.
So I'm inclined to think that the problem - at least as far as
SAVEPOINT and ROLLBACK TO SAVEPOINT are concerned - is that
RequireTransactionChain() really can't just be the mirror image of
PreventTransactionChain().  When you're in a transaction,
PreventTransactionChain() should be unhappy; when you're outside one,
RequireTransactionChain() should be unhappy; and when you're in this
intermediate state inside of a multi-command string, BOTH of them
should be unhappy.  I'm not sure whether we need to go through and
replace isTopLevel with a three-valued flag, or whether we can just
delete the isTopLevel test from RequireTransactionChain() altogether.
The latter seems like it might do the trick, but I haven't puzzled
through all the logic yet.

As for ROLLBACK, I think it should chuck an error instead of doing
this funny 
emit-a-warning-and-silently-arrange-for-the-transaction-to-be-aborted-later
thing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] the big picture for index-only scans

[Gokulakannan Somasundaram]

On Sat, Aug 20, 2011 at 2:51 AM, Gokulakannan Somasundaram <
gokul...@gmail.com> wrote:

> On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas <
> heikki.linnakan...@enterprisedb.com> wrote:
>
>> On 19.08.2011 21:06, Gokulakannan Somasundaram wrote:
>>
>>> If you are following the same design that Heikki put forward, then there
>>> is
>>> a problem with it in maintaining the bits in page and the bits in
>>> visibility
>>> map in sync, which we have already discussed.
>>>
>>
>> Are you referring to this: http://archives.postgresql.**
>> org/pgsql-hackers/2010-02/**msg02097.php?
>>  I believe Robert's changes to make the visibility map crash-safe covers
>> that. Clearing the bit in the visibility map now happens within the same
>> critical section as clearing the flag on the heap page and writing th WAL
>> record.
>>
>> In that case, say a 100 sessions are trying to update records which fall
> under the 8000*4 heap pages( i assume 2 bits per visibility map - 8 * 1024 *
> 4 exact) covered by one page of visibility map, won't it make the 99
> sessions wait for that visibility map while holding the exclusive lock on
> the 99 heap pages?
> Are we going to say, that these kind of situations occur very rarely? Or
> that the loss of scalability in these situations, is worth the performance
> during the read-heavy workloads?
>
> In any case, making a database going through all these extra overheads,
> while they don't even have any index-only scans!!!  That definitely should
> be given a thought.
>
> Gokul.
>

Please consider the update, i mentioned above  occurs and changes the
visibility bit of the page.

Re: [HACKERS] the big picture for index-only scans

[Gokulakannan Somasundaram]

On Sat, Aug 20, 2011 at 2:25 AM, Heikki Linnakangas <
heikki.linnakan...@enterprisedb.com> wrote:

> On 19.08.2011 21:06, Gokulakannan Somasundaram wrote:
>
>> If you are following the same design that Heikki put forward, then there
>> is
>> a problem with it in maintaining the bits in page and the bits in
>> visibility
>> map in sync, which we have already discussed.
>>
>
> Are you referring to this: http://archives.postgresql.**
> org/pgsql-hackers/2010-02/**msg02097.php?
>  I believe Robert's changes to make the visibility map crash-safe covers
> that. Clearing the bit in the visibility map now happens within the same
> critical section as clearing the flag on the heap page and writing th WAL
> record.
>
> In that case, say a 100 sessions are trying to update records which fall
under the 8000*4 heap pages( i assume 2 bits per visibility map - 8 * 1024 *
4 exact) covered by one page of visibility map, won't it make the 99
sessions wait for that visibility map while holding the exclusive lock on
the 99 heap pages?
Are we going to say, that these kind of situations occur very rarely? Or
that the loss of scalability in these situations, is worth the performance
during the read-heavy workloads?

In any case, making a database going through all these extra overheads,
while they don't even have any index-only scans!!!  That definitely should
be given a thought.

Gokul.

Re: [HACKERS] the big picture for index-only scans

[Heikki Linnakangas]

On 19.08.2011 21:06, Gokulakannan Somasundaram wrote:

If you are following the same design that Heikki put forward, then there is
a problem with it in maintaining the bits in page and the bits in visibility
map in sync, which we have already discussed.


Are you referring to this: 
http://archives.postgresql.org/pgsql-hackers/2010-02/msg02097.php ? I 
believe Robert's changes to make the visibility map crash-safe covers 
that. Clearing the bit in the visibility map now happens within the same 
critical section as clearing the flag on the heap page and writing th 
WAL record.


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

[HACKERS] two index bitmap scan of a big table & hash_seq_search

[Sergey E. Koposov]

Hello Hackers,

I've recently noticed a particularly strange behaviour of one of my simple 
queries:

SELECT ra,dec FROM sdssdr7.photoobjall WHERE ra BETWEEN 175 and 190 AND
   dec BETWEEN 17 And 23 ;
The table is quite large (1.1Tb, 10^9 rows, and has Btree indexes on 
"ra","dec" columns).

The plan is a combination of two bitmap scans:
wsdb=# explain SELECT ra,dec FROM sdssdr7.photoobjall WHERE ra BETWEEN 175 and 
190 AND dec BETWEEN 17 And 23 ;
QUERY 
PLAN
---
 Bitmap Heap Scan on photoobjall  (cost=1854918.62..13401835.50 rows=3266290 
width=16)
   Recheck Cond: ((ra >= 175::double precision) AND (ra <= 190::double precision) AND ("dec" 
>= 17::double precision) AND ("dec" <= 23::double precision))
   ->  BitmapAnd  (cost=1854918.62..1854918.62 rows=3266290 width=0)
 ->  Bitmap Index Scan on sdssdr7_photoobjall_ra_idx  
(cost=0.00..607217.21 rows=30530306 width=0)
   Index Cond: ((ra >= 175::double precision) AND (ra <= 
190::double precision))
 ->  Bitmap Index Scan on sdssdr7_photoobjall_dec_idx  
(cost=0.00..1246068.01 rows=62654186 width=0)
   Index Cond: (("dec" >= 17::double precision) AND ("dec" <= 
23::double precision))

But the funny thing I noticed is that the query after running a certain 
amount of time doing I/O, starts to use 100%CPU and spend 99% the time in 
hash_seq_search. Here is the oprofile of PG during that period:


CPU: Intel Core/i7, speed 2.268e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (No unit mask) count 10
samples  %symbol name
303404   99.3562  hash_seq_search
1163  0.3808  tbm_lossify
639   0.2093  hash_search_with_hash_value



During that it very rarely tries to actually do any I/O. Every second or 
so it does issue an read of the main relation(not the index). Here is 
how strace looks like during that time:

read(455, "\0\0\0\0\0\0\0\0\1\0\0\0t\6\200\6\360\37\4 \0\0\0\0\200\206 \0\340\237 
\0\320"..., 8192) = 8192
... repeated 100 or more  times(issued every second or so) ..
lseek(455, 603635712, SEEK_SET) = 603635712
read(455, "\0\0\0\0\0\0\0\0\1\0\0\0t\6\200\6\360\37\4 \0\0\0\0\200\206 \0\340\237 
\0\320"..., 8192) = 8192
... repeated a lot of times...
...etc...
So it seems that it actually reads the same thing all over again and 
again.


Another funny thing is that I have the exactly the same table, but with 
significantly smaller number of columns (so it occupies 180 gb 
instead) but has the same indexes. Also all the rows in the table are 
exactly in the same order (both tables were clustered using the same 
thing). For that table the query succeeds without problems in 20 seconds 
or so:

wsdb=> explain analyze SELECT ra,dec FROM sdssdr7.phototag WHERE ra BETWEEN 175 
and 190 AND dec BETWEEN 17 And 23 ;
 
QUERY PLAN

 Bitmap Heap Scan on phototag  (cost=1824731.39..10750473.21 rows=3204629 
width=16) (actual time=21146.520..23136.512 rows=2973205 loops=1)
   Recheck Cond: ((ra >= 175::double precision) AND (ra <= 190::double precision) AND ("dec" 
>= 17::double precision) AND ("dec" <= 23::double precision))
   ->  BitmapAnd  (cost=1824731.39..1824731.39 rows=3204629 width=0) (actual 
time=21093.906..21093.906 rows=0 loops=1)
 ->  Bitmap Index Scan on sdssdr7_phototag2_ra_idx  
(cost=0.00..613910.25 rows=30866810 width=0) (actual time=6502.589..6502.589 
rows=30418322 loops=1)
   Index Cond: ((ra >= 175::double precision) AND (ra <= 
190::double precision))
 ->  Bitmap Index Scan on sdssdr7_phototag2_dec_idx  
(cost=0.00..1209218.57 rows=60801242 width=0) (actual time=14201.455..14201.455 
rows=63031352 loops=1)
   Index Cond: (("dec" >= 17::double precision) AND ("dec" <= 
23::double precision))
 Total runtime: 23295.384 ms
(8 rows)

While for "wider" table it is still running the query for more than an 
hour  with 100%CPU an almost no I/O


Additional info:
PG version 8.4.8,
OS: debian 5.0
hardware: 2xXeon E5520, 12GB RAM, RAID50 with BBU
The tables are completely static(e.g. no write activity on them), and have 
been vacuum analyzed.

The system is not busy at all (e.g. not much if any concurrent queries).
The tables have large number of columns of real/int/bigint/double 
precision time.


Some config parameters:
 effective_cache_size| 6GB
 work_mem| 1GB
 effective_io_concurrency| 0
 shared_buffers  | 4GB

Any ideas what can be wrong? Any info I can pro

[HACKERS] Rethinking sinval callback hook API

[Tom Lane]

Currently, we have two types of callbacks that can be registered to get
control when an invalidation message is received: syscache callbacks and
relcache callbacks.  It strikes me that we might be better advised to
unify these into a single type of callback that gets a
SharedInvalidationMessage struct pointer (we could pass NULL to signify
a cache reset event).  That would avoid having to add another
registration list every time we decide that there's a reason for
callbacks to see another type of inval message.  There are a couple of
reasonably near-term reasons why we might want to do this:

1. Robert was speculating the other day about wanting to be able to
snoop the inval traffic.  Right now, callbacks can only snoop a fairly
small subset of it.

2. In conjunction with what I'm doing with plancache, it struck me that
it might be nice to subdivide relcache inval messages into two types,
one indicating a schema (DDL) change and one that just indicates that
statistics changed; this would allow us to avoid redoing parse analysis
and rewrite for a cached query as a consequence of autovacuum stats
updates.  With the current scheme, plancache.c would need to register
two different kinds of callbacks to handle that, or we'd need some other
API change for relcache callbacks so they could distinguish.

A small disadvantage of this is that callbacks would have to know about
struct SharedInvalidationMessage, which right now isn't tremendously
widely known, but that doesn't seem like a fatal objection to me.
In practice that struct definition has been pretty stable.

Also, right now (9.2 cycle) would be a good time to do this since we
already changed the API for syscache callbacks as a result of my cache
bug investigations last week.  Any third-party code that's hooking into
this area is going to need changes for 9.2 anyway.

Thoughts?

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] the big picture for index-only scans

[Gokulakannan Somasundaram]

>
>> Well, that would certainly be alarming if true, but I don't think it
>> is.  As far as I can see, the overhead of making the visibility map
>> crash-safe is just (1) a very small percentage increase in the work
>> being done by VACUUM and (2) a slight possibility of extra work done
>> by a foreground process if the visibility map bit changes at almost
>> exactly the same time the process was about to insert, update, or
>> delete a tuple.
>>
>> Let's forget the overhead posed by vacuum. Can you please point me to the
> design which talks in detail of the second overhead?
>
> Thanks.
>

If you are following the same design that Heikki put forward, then there is
a problem with it in maintaining the bits in page and the bits in visibility
map in sync, which we have already discussed.

Re: [HACKERS] the big picture for index-only scans

[Gokulakannan Somasundaram]

>
>
> Well, that would certainly be alarming if true, but I don't think it
> is.  As far as I can see, the overhead of making the visibility map
> crash-safe is just (1) a very small percentage increase in the work
> being done by VACUUM and (2) a slight possibility of extra work done
> by a foreground process if the visibility map bit changes at almost
> exactly the same time the process was about to insert, update, or
> delete a tuple.
>
> Let's forget the overhead posed by vacuum. Can you please point me to the
design which talks in detail of the second overhead?

Thanks.

Re: [HACKERS] New copyright program

[Bruce Momjian]

David Fetter wrote:
> Folks,
> 
> I noticed that src/tools/copyright looks like it can only be run on
> Bruce's machine, so this translation to Perl is intended:
> 
> 1.  To make the script idempotent, which allows its safe use in
> automated tools that might run it many times.
> 
> 2.  To get the script to run on any machine a PostgreSQL developer
> would be using, as Perl is already required.
> 
> 3.  To make the script more efficient.  As the copyright notice we
> need to munge only appears once per file, it stops once it has made a
> substitution.
> 
> Please find attached a patch implementing same.

Thanks.  Applied to HEAD.  I never liked putting scripts in git that
only I could run, but I thought if something happened to me, it would be
good to record what I did.  The Perl solution is perfect.

-- 
  Bruce Momjian  http://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] Transient plans versus the SPI API

[Tom Lane]

[ getting back to the planner finally ]

Simon Riggs  writes:
> On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane  wrote:
>> Simon Riggs  writes:
>>> I think its possible to tell automatically whether we need to replan
>>> always or not based upon the path we take through selectivity
>>> functions.

>> I don't really believe that, or at least I think it would only detect a
>> few cases.

> The problem there is which executions we build custom plans for. That
> turns the problem into a sampling issue and you'll only fix the
> problems that occur with a frequency to match your sampling pattern
> and rate. Examples of situations where it won't help.

Sure, this is not going to solve every problem we have with the
planner.  What it is intended to solve is cases where someone is trying
to use the prepared-plan mechanisms but he would be a lot better off
with parameter-value-specific plans.  In particular:

> * plans that vary by table size will be about the same in the first 5
> executions. After large number of executions, things go bad.

This is a red herring.  The plancache code already arranges to replan
every time the relevant table stats are updated by autovacuum, which
should certainly happen from time to time if the table's contents are
changing materially.  If you're thinking in terms of plans being "stale"
then you're worrying about a long-since-solved problem.

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] How to define global variable in postgresql

[Florian Weimer]

> Hello. How can we define a global variable in postgresql?

Do you mean session-private, but persistent across transactions?
Configuration parameters can be abused for this purpose.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 11:40 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane  wrote:
>>> No objection to fixing or backpatching this, but I'm not seeing the
>>> argument for treating this module differently from contrib/xml2.
>
>> Because I screwed it up accidentally for sepgsql, and I can't screw it
>> up for xml2 on purpose even after working fairly hard.  Even after
>> shoving in the necessary -I switch (through a slightly different
>> mechanism than the one you just proposed), it still won't link,
>> whether -lxml2 is on the command-line or not.
>
> Huh.  Links for me on Fedora 14 ...
>
> [tgl@rh3 ~]$ cd ~/pgsql/contrib/xml2
> [tgl@rh3 xml2]$ make clean
> rm -f pgxml.so   libpgxml.a
> rm -f xpath.o xslt_proc.o
> rm -rf results/ regression.diffs regression.out tmp_check/ log/
> [tgl@rh3 xml2]$ make PROFILE=-I/usr/include/libxml2
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wformat-security 
> -fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -I. -I. 
> -I../../src/include -D_GNU_SOURCE   -c -o xpath.o xpath.c
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wformat-security 
> -fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -I. -I. 
> -I../../src/include -D_GNU_SOURCE   -c -o xslt_proc.o xslt_proc.c
> gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
> -Wdeclaration-after-statement -Wendif-labels -Wformat-security 
> -fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -shared -o 
> pgxml.so xpath.o xslt_proc.o -L../../src/port -Wl,--as-needed 
> -Wl,-rpath,'/home/tgl/testversion/lib',--enable-new-dtags 
> -I/usr/include/libxml2
> [tgl@rh3 xml2]$
>
> (and yes, this is in a build tree configured without --with-libxml).
> As far as I can tell, this *must* work this way on Linux.  Maybe you
> were testing the xml2 case on OS X?  That OS is pickier.

Hrm, I *thought* I had tested on Linux, but maybe I was on OS X that
time around.  Anyway, I can reproduce this now.

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 11:46 AM, Tom Lane  wrote:
> Kohei Kaigai  writes:
>>> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
>>> Well, they should get at least a warning from referencing undefined
>>> functions, no?
>
>> Yes. User should notice warning messages due to undefined symbols.
>> I'm not certain whether it makes sense to add -Werror here, or not.
>
> Hmm.  That would help catch the problem, but I'm a bit uncomfortable
> with adding -Werror in relatively new code.  On the other hand, it's
> not like we expect sepgsql to work on a wide variety of systems, so
> maybe it'd be OK.
>
> On the whole I don't think it's worth messing with the cflags for this.

Yeah, I agree.

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Kohei Kaigai  writes:
>> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
>> Well, they should get at least a warning from referencing undefined
>> functions, no?

> Yes. User should notice warning messages due to undefined symbols.
> I'm not certain whether it makes sense to add -Werror here, or not.

Hmm.  That would help catch the problem, but I'm a bit uncomfortable
with adding -Werror in relatively new code.  On the other hand, it's
not like we expect sepgsql to work on a wide variety of systems, so
maybe it'd be OK.

On the whole I don't think it's worth messing with the cflags for this.

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] [v9.1] sepgsql - userspace access vector cache

[Kohei Kaigai]

> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: 19. August 2011 16:34
> To: Kohei Kaigai
> Cc: Robert Haas; Kohei KaiGai; Yeb Havinga; PgHacker
> Subject: Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
> 
> Kohei Kaigai  writes:
> > One point I'm worrying about is a case when contrib/sepgsql is compiled
> > with older libselinux than minimum requirement. In this case, we may not
> > notice the broken module unless user tries to load it actually.
> > Is there a good idea to ensure compile failure when we try to build sepgsql
> > module when libselinux-2.0.98 or older was installed?
> 
> Well, they should get at least a warning from referencing undefined
> functions, no?
> 
Yes. User should notice warning messages due to undefined symbols.
I'm not certain whether it makes sense to add -Werror here, or not.

> There's a limit to how friendly we can be here, since Linux's shlib
> stuff is designed to not require all symbols to be resolvable at shlib
> construction time.  This is one place where Darwin works better ...
> 
Hmm...
--
NEC Europe Ltd, SAP Global Competence Center
KaiGai Kohei 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Robert Haas  writes:
> On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane  wrote:
>> No objection to fixing or backpatching this, but I'm not seeing the
>> argument for treating this module differently from contrib/xml2.

> Because I screwed it up accidentally for sepgsql, and I can't screw it
> up for xml2 on purpose even after working fairly hard.  Even after
> shoving in the necessary -I switch (through a slightly different
> mechanism than the one you just proposed), it still won't link,
> whether -lxml2 is on the command-line or not.

Huh.  Links for me on Fedora 14 ...

[tgl@rh3 ~]$ cd ~/pgsql/contrib/xml2
[tgl@rh3 xml2]$ make clean
rm -f pgxml.so   libpgxml.a 
rm -f xpath.o xslt_proc.o
rm -rf results/ regression.diffs regression.out tmp_check/ log/
[tgl@rh3 xml2]$ make PROFILE=-I/usr/include/libxml2
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wformat-security 
-fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -I. -I. 
-I../../src/include -D_GNU_SOURCE   -c -o xpath.o xpath.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wformat-security 
-fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -I. -I. 
-I../../src/include -D_GNU_SOURCE   -c -o xslt_proc.o xslt_proc.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wformat-security 
-fno-strict-aliasing -fwrapv -g -I/usr/include/libxml2 -fpic -shared -o 
pgxml.so xpath.o xslt_proc.o -L../../src/port -Wl,--as-needed 
-Wl,-rpath,'/home/tgl/testversion/lib',--enable-new-dtags 
-I/usr/include/libxml2
[tgl@rh3 xml2]$ 

(and yes, this is in a build tree configured without --with-libxml).
As far as I can tell, this *must* work this way on Linux.  Maybe you
were testing the xml2 case on OS X?  That OS is pickier.

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] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Kohei Kaigai  writes:
> One point I'm worrying about is a case when contrib/sepgsql is compiled
> with older libselinux than minimum requirement. In this case, we may not
> notice the broken module unless user tries to load it actually.
> Is there a good idea to ensure compile failure when we try to build sepgsql
> module when libselinux-2.0.98 or older was installed?

Well, they should get at least a warning from referencing undefined
functions, no?

There's a limit to how friendly we can be here, since Linux's shlib
stuff is designed to not require all symbols to be resolvable at shlib
construction time.  This is one place where Darwin works better ...

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] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On further review, if the initial configure was done without
>> --with-libxml, xml2 is doomed anyway.
>
> True, but it's still possible to build a shlib that will then not work.
> I just did, after manually supplying the right -I switch:
>
> make PROFILE=-I/usr/include/libxml2
>
> Now admittedly a clueless person would be unlikely to know to do that,
> but if his libxml installation were arranged so that no special -I
> switch was needed (unlike the Fedora packaging), it'd be more likely
> that this could happen.
>
>> This probably explains why no one's complained about this before, and
>> I think the appropriate fix is to change just sepgsql.  I would like
>> to back-patch that (one-line) change into 9.1 as well, to eliminate
>> this as a foot-gun for anyone who may be packaging that module for the
>> first time.
>
> No objection to fixing or backpatching this, but I'm not seeing the
> argument for treating this module differently from contrib/xml2.  If you
> believe that someone will try to manually build in contrib/sepgsql after
> having failed to configure correctly, why do you not believe that for
> xml2?

Because I screwed it up accidentally for sepgsql, and I can't screw it
up for xml2 on purpose even after working fairly hard.  Even after
shoving in the necessary -I switch (through a slightly different
mechanism than the one you just proposed), it still won't link,
whether -lxml2 is on the command-line or not.

That having been said, I don't mind changing them both symmetrically;
I'm just convinced that there's any benefit.  However, if you think
that way is more future-proof or that I might be overlooking some
scenario, fine!  It won't hurt anything.

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Kohei Kaigai]

> -Original Message-
> From: Robert Haas [mailto:robertmh...@gmail.com]
> Sent: 19. August 2011 15:55
> To: Tom Lane
> Cc: Kohei KaiGai; Kohei Kaigai; Yeb Havinga; PgHacker
> Subject: Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
> 
> On Fri, Aug 19, 2011 at 10:31 AM, Robert Haas  wrote:
> > On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane  wrote:
> >>> Why not just:
> >>
> >>> SHLIB_LINK = -lselinux
> >>
> >> I wouldn't have any particular objection to that (although I think it's
> >> supposed to be += here).
> >
> > Oh, right.
> >
> >> I don't see that any of the other changes
> >> Kaigai proposed are helpful, though.
> >
> > I was coming to the same conclusion.  I sort of liked his idea of
> > sticking a conditional #error directive in the header files to make it
> > more clear why it was failing.  But on closer examination there's
> > really no benefit: it gets lost in a sea of other failures, and if you
> > have to look through the failure messages anyway you may as well
> > notice that the #include of  failed as anything
> > else.  So I think changing that line to link with libselinux
> > unconditionally is about as well as we can do.
> >
> >>> Similarly, in the case of xml2 we have:
> >>
> >>> SHLIB_LINK += $(filter -lxslt, $(LIBS)) $(filter -lxml2, $(LIBS))
> >>
> >>> For xslt, it probably makes sense to filter it out if it wasn't found,
> >>> because the code has ifdefs for USE_XSLT that do something sensible if
> >>> the library is not there.  But I fail to see what the point is of
> >>> filtering out xml2, because surely we're doomed if that's not there...
> >>> or am I confused?
> >>
> >> Hmm.  I think it's just that way to make the code look parallel for both
> >> libraries.  But I can see potential value in making -lxml2 unconditional
> >> --- as you say, that would result in a link failure instead of a
> >> silently broken library.
> >
> > Right.
> 
> On further review, if the initial configure was done without
> --with-libxml, xml2 is doomed anyway.  There's no value to changing
> anything there one way or the other, because it relies not only on
> symbols from the library, but also on symbols that are only defined
> when PostgreSQL itself is configured with xml support.  In other
> words, there's no chance of undetected failure here.
> 
> On the other hand, we've worked pretty hard to keep sepgsql at arm's
> length from the core server, so as it stands, it's quite easy to build
> a busted sepgsql module.
> 
> This probably explains why no one's complained about this before, and
> I think the appropriate fix is to change just sepgsql.  I would like
> to back-patch that (one-line) change into 9.1 as well, to eliminate
> this as a foot-gun for anyone who may be packaging that module for the
> first time.
> 
I almost agree with this change.

One point I'm worrying about is a case when contrib/sepgsql is compiled
with older libselinux than minimum requirement. In this case, we may not
notice the broken module unless user tries to load it actually.
Is there a good idea to ensure compile failure when we try to build sepgsql
module when libselinux-2.0.98 or older was installed?

Of course, using --with-selinux on ./configure time is the best way...

Thanks,
--
NEC Europe Ltd, SAP Global Competence Center
KaiGai Kohei 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Robert Haas  writes:
> On further review, if the initial configure was done without
> --with-libxml, xml2 is doomed anyway.

True, but it's still possible to build a shlib that will then not work.
I just did, after manually supplying the right -I switch:

make PROFILE=-I/usr/include/libxml2

Now admittedly a clueless person would be unlikely to know to do that,
but if his libxml installation were arranged so that no special -I
switch was needed (unlike the Fedora packaging), it'd be more likely
that this could happen.

> This probably explains why no one's complained about this before, and
> I think the appropriate fix is to change just sepgsql.  I would like
> to back-patch that (one-line) change into 9.1 as well, to eliminate
> this as a foot-gun for anyone who may be packaging that module for the
> first time.

No objection to fixing or backpatching this, but I'm not seeing the
argument for treating this module differently from contrib/xml2.  If you
believe that someone will try to manually build in contrib/sepgsql after
having failed to configure correctly, why do you not believe that for
xml2?

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] the big picture for index-only scans

[Robert Haas]

On Fri, Aug 19, 2011 at 11:22 AM, Bruce Momjian  wrote:
> Robert Haas wrote:
>> > I am happy to have pg_upgrade skip upgrading visibility map files --- it
>> > already has code to conditionally process them because they only exist
>> > in >= 8.4:
>> >
>> > ? ? ? ?/* fsm/vm files added in PG 8.4 */
>> > ? ? ? ?if (GET_MAJOR_VERSION(old_cluster.major_version) >= 804)
>> > ? ? ? ?{
>> > ? ? ? ? ? ?/*
>> > ? ? ? ? ? ? * Copy/link any fsm and vm files, if they exist
>> > ? ? ? ? ? ? */
>> >
>> > Just give the word and it will be done.
>>
>> I hereby give the word.  :-)
>>
>> Specifically, we need to skip copying vm files (only) if coming from a
>> version prior to this commit:
>>
>> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e16954f3d27fa8e16c379ff6623ae18d6250a39c
>
> Done with the attached, applied patch.  There was no cat-version bump
> from that commit (because the format didn't change, just the
> crash-safeness) so I picked the first cat-version change after this
> commit.  This is only a pg_upgrade 9.2+ issue.

Thanks!

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] the big picture for index-only scans

[Bruce Momjian]

Robert Haas wrote:
> > I am happy to have pg_upgrade skip upgrading visibility map files --- it
> > already has code to conditionally process them because they only exist
> > in >= 8.4:
> >
> > ? ? ? ?/* fsm/vm files added in PG 8.4 */
> > ? ? ? ?if (GET_MAJOR_VERSION(old_cluster.major_version) >= 804)
> > ? ? ? ?{
> > ? ? ? ? ? ?/*
> > ? ? ? ? ? ? * Copy/link any fsm and vm files, if they exist
> > ? ? ? ? ? ? */
> >
> > Just give the word and it will be done.
> 
> I hereby give the word.  :-)
> 
> Specifically, we need to skip copying vm files (only) if coming from a
> version prior to this commit:
> 
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e16954f3d27fa8e16c379ff6623ae18d6250a39c

Done with the attached, applied patch.  There was no cat-version bump
from that commit (because the format didn't change, just the
crash-safeness) so I picked the first cat-version change after this
commit.  This is only a pg_upgrade 9.2+ issue.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 6def748..a19b3df
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
***
*** 64,69 
--- 64,75 
  #define TABLE_SPACE_SUBDIRS_CAT_VER 20100
  /* postmaster/postgres -b (binary_upgrade) flag added during PG 9.1 development */
  #define BINARY_UPGRADE_SERVER_FLAG_CAT_VER 201104251
+ /*
+  * 	Visibility map changed with this 9.2 commit,
+  *	8f9fe6edce358f7904e0db119416b4d1080a83aa; pick later catalog version.
+  */
+ #define VISIBILITY_MAP_CRASHSAFE_CAT_VER 201107031
+ 
  
  /*
   * Each relation is represented by a relinfo structure.
diff --git a/contrib/pg_upgrade/relfilenode.c b/contrib/pg_upgrade/relfilenode.c
new file mode 100644
index d4a420f..df752c5
*** a/contrib/pg_upgrade/relfilenode.c
--- b/contrib/pg_upgrade/relfilenode.c
*** transfer_single_new_db(pageCnvCtx *pageC
*** 120,128 
  	int			numFiles = 0;
  	int			mapnum;
  	int			fileno;
! 
  	old_dir[0] = '\0';
  
  	for (mapnum = 0; mapnum < size; mapnum++)
  	{
  		char		old_file[MAXPGPATH];
--- 120,134 
  	int			numFiles = 0;
  	int			mapnum;
  	int			fileno;
! 	bool		vm_crashsafe_change = false;
! 	
  	old_dir[0] = '\0';
  
+ 	/* Do not copy non-crashsafe vm files for binaries that assume crashsafety */
+ 	if (old_cluster.controldata.cat_ver < VISIBILITY_MAP_CRASHSAFE_CAT_VER &&
+ 		new_cluster.controldata.cat_ver >= VISIBILITY_MAP_CRASHSAFE_CAT_VER)
+ 		vm_crashsafe_change = true;
+ 	
  	for (mapnum = 0; mapnum < size; mapnum++)
  	{
  		char		old_file[MAXPGPATH];
*** transfer_single_new_db(pageCnvCtx *pageC
*** 168,175 
  
  			for (fileno = 0; fileno < numFiles; fileno++)
  			{
  if (strncmp(namelist[fileno]->d_name, scandir_file_pattern,
! 			strlen(scandir_file_pattern)) == 0)
  {
  	snprintf(old_file, sizeof(old_file), "%s/%s", maps[mapnum].old_dir,
  			 namelist[fileno]->d_name);
--- 174,189 
  
  			for (fileno = 0; fileno < numFiles; fileno++)
  			{
+ char *vm_offset = strstr(namelist[fileno]->d_name, "_vm");
+ bool is_vm_file = false;
+ 
+ /* Is a visibility map file? (name ends with _vm) */
+ if (vm_offset && strlen(vm_offset) == strlen("_vm"))
+ 	is_vm_file = true;
+ 
  if (strncmp(namelist[fileno]->d_name, scandir_file_pattern,
! 			strlen(scandir_file_pattern)) == 0 &&
! 	(!is_vm_file || !vm_crashsafe_change))
  {
  	snprintf(old_file, sizeof(old_file), "%s/%s", maps[mapnum].old_dir,
  			 namelist[fileno]->d_name);

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] How to define global variable in postgresql

[Christopher Browne]

On Fri, Aug 19, 2011 at 10:58 AM, EazonGuo  wrote:
> Hello. How can we define a global variable in postgresql?

I believe CREATE TABLE is the usual mechanism to do this sort of thing.
-- 
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

[HACKERS] How to define global variable in postgresql

[EazonGuo]

Hello. How can we define a global variable in postgresql?

-- 
Best regards

Eazon

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 10:31 AM, Robert Haas  wrote:
> On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane  wrote:
>>> Why not just:
>>
>>> SHLIB_LINK = -lselinux
>>
>> I wouldn't have any particular objection to that (although I think it's
>> supposed to be += here).
>
> Oh, right.
>
>> I don't see that any of the other changes
>> Kaigai proposed are helpful, though.
>
> I was coming to the same conclusion.  I sort of liked his idea of
> sticking a conditional #error directive in the header files to make it
> more clear why it was failing.  But on closer examination there's
> really no benefit: it gets lost in a sea of other failures, and if you
> have to look through the failure messages anyway you may as well
> notice that the #include of  failed as anything
> else.  So I think changing that line to link with libselinux
> unconditionally is about as well as we can do.
>
>>> Similarly, in the case of xml2 we have:
>>
>>> SHLIB_LINK += $(filter -lxslt, $(LIBS)) $(filter -lxml2, $(LIBS))
>>
>>> For xslt, it probably makes sense to filter it out if it wasn't found,
>>> because the code has ifdefs for USE_XSLT that do something sensible if
>>> the library is not there.  But I fail to see what the point is of
>>> filtering out xml2, because surely we're doomed if that's not there...
>>> or am I confused?
>>
>> Hmm.  I think it's just that way to make the code look parallel for both
>> libraries.  But I can see potential value in making -lxml2 unconditional
>> --- as you say, that would result in a link failure instead of a
>> silently broken library.
>
> Right.

On further review, if the initial configure was done without
--with-libxml, xml2 is doomed anyway.  There's no value to changing
anything there one way or the other, because it relies not only on
symbols from the library, but also on symbols that are only defined
when PostgreSQL itself is configured with xml support.  In other
words, there's no chance of undetected failure here.

On the other hand, we've worked pretty hard to keep sepgsql at arm's
length from the core server, so as it stands, it's quite easy to build
a busted sepgsql module.

This probably explains why no one's complained about this before, and
I think the appropriate fix is to change just sepgsql.  I would like
to back-patch that (one-line) change into 9.1 as well, to eliminate
this as a foot-gun for anyone who may be packaging that module for the
first 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] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane  wrote:
>> Why not just:
>
>> SHLIB_LINK = -lselinux
>
> I wouldn't have any particular objection to that (although I think it's
> supposed to be += here).

Oh, right.

> I don't see that any of the other changes
> Kaigai proposed are helpful, though.

I was coming to the same conclusion.  I sort of liked his idea of
sticking a conditional #error directive in the header files to make it
more clear why it was failing.  But on closer examination there's
really no benefit: it gets lost in a sea of other failures, and if you
have to look through the failure messages anyway you may as well
notice that the #include of  failed as anything
else.  So I think changing that line to link with libselinux
unconditionally is about as well as we can do.

>> Similarly, in the case of xml2 we have:
>
>> SHLIB_LINK += $(filter -lxslt, $(LIBS)) $(filter -lxml2, $(LIBS))
>
>> For xslt, it probably makes sense to filter it out if it wasn't found,
>> because the code has ifdefs for USE_XSLT that do something sensible if
>> the library is not there.  But I fail to see what the point is of
>> filtering out xml2, because surely we're doomed if that's not there...
>> or am I confused?
>
> Hmm.  I think it's just that way to make the code look parallel for both
> libraries.  But I can see potential value in making -lxml2 unconditional
> --- as you say, that would result in a link failure instead of a
> silently broken library.

Right.

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Robert Haas  writes:
> On Fri, Aug 19, 2011 at 9:59 AM, Tom Lane  wrote:
>> This patch seems unnecessary to me.

> Hmm.  I see now that it's parallel, but I find it pretty confusing
> that building sepgsql without specifying --with-selinux results in a
> shared library that seems to compile OK but won't load.

Well, that's a fair point, but the same happens in contrib/xml2 (if you
have a setup that doesn't need a special -I switch, or you provide that
some other way), and nobody has ever complained about it.

> Why not just:

> SHLIB_LINK = -lselinux

I wouldn't have any particular objection to that (although I think it's
supposed to be += here).  I don't see that any of the other changes
Kaigai proposed are helpful, though.

> Similarly, in the case of xml2 we have:

> SHLIB_LINK += $(filter -lxslt, $(LIBS)) $(filter -lxml2, $(LIBS))

> For xslt, it probably makes sense to filter it out if it wasn't found,
> because the code has ifdefs for USE_XSLT that do something sensible if
> the library is not there.  But I fail to see what the point is of
> filtering out xml2, because surely we're doomed if that's not there...
> or am I confused?

Hmm.  I think it's just that way to make the code look parallel for both
libraries.  But I can see potential value in making -lxml2 unconditional
--- as you say, that would result in a link failure instead of a
silently broken library.

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] the big picture for index-only scans

[Robert Haas]

On Fri, Aug 19, 2011 at 9:19 AM, Gokulakannan Somasundaram
 wrote:
> The fact that the
> proposal is for crash safe visibility map, to become a default package of
> any Postgresql table will definitely have wide ranging implications on OLTP
> performance.

Well, that would certainly be alarming if true, but I don't think it
is.  As far as I can see, the overhead of making the visibility map
crash-safe is just (1) a very small percentage increase in the work
being done by VACUUM and (2) a slight possibility of extra work done
by a foreground process if the visibility map bit changes at almost
exactly the same time the process was about to insert, update, or
delete a tuple.

If someone comes up with a test where this overhead is enough to
measure, then we might need to rethink our whole approach.  Maybe we
would make it an optional feature, or maybe we would just rip it out
and start over with some sort of redesign, or maybe we would look for
other optimizations to counterbalance the additional overhead.  I
don't know.  But as far as I can see you're hypothesizing without
evidence.

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Robert Haas]

On Fri, Aug 19, 2011 at 9:59 AM, Tom Lane  wrote:
> Kohei KaiGai  writes:
>> 2011/8/18 Robert Haas :
>>> Actually, as I look at this more, I think this build system is
>>> completely mis-designed.  Given that you want to build sepgsql,
>>> selinux is not an optional feature.  So the stuff in
>>> contrib/sepgsql/Makefile that is intended to link against libselinux
>>> only if --with-selinux was specified at configure time is nonsense.
>
> What stuff is that?

SHLIB_LINK += $(filter -lselinux, $(LIBS))

> This patch seems unnecessary to me.  The way it works now appears to be
> quite parallel to the way that contrib/xml2 works, and has worked for
> years.  I don't think that sepgsql should behave differently from that.

Hmm.  I see now that it's parallel, but I find it pretty confusing
that building sepgsql without specifying --with-selinux results in a
shared library that seems to compile OK but won't load.   Why not
just:

SHLIB_LINK = -lselinux

Similarly, in the case of xml2 we have:

SHLIB_LINK += $(filter -lxslt, $(LIBS)) $(filter -lxml2, $(LIBS))

For xslt, it probably makes sense to filter it out if it wasn't found,
because the code has ifdefs for USE_XSLT that do something sensible if
the library is not there.  But I fail to see what the point is of
filtering out xml2, because surely we're doomed if that's not there...
or am I confused?

-- 
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] [v9.1] sepgsql - userspace access vector cache

[Tom Lane]

Kohei KaiGai  writes:
> 2011/8/18 Robert Haas :
>> Actually, as I look at this more, I think this build system is
>> completely mis-designed.  Given that you want to build sepgsql,
>> selinux is not an optional feature.  So the stuff in
>> contrib/sepgsql/Makefile that is intended to link against libselinux
>> only if --with-selinux was specified at configure time is nonsense.

What stuff is that?

> So, it seems to me we also need to revise configure script, not only
> Makefile of sepgsql.

This patch seems unnecessary to me.  The way it works now appears to be
quite parallel to the way that contrib/xml2 works, and has worked for
years.  I don't think that sepgsql should behave differently from that.

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] "make -j4 world" falls over

[Tom Lane]

I wrote:
> I ran into $SUBJECT whilst doing trial RPM packaging of 9.1.

BTW, the failure seems rather hard to provoke at -j4 ... so it was just
bad luck that my first rpmbuild run fell over.  However, in manual
testing, it fails pretty much every time at -j16, on my 4-processor
Fedora box.

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] Backup's from standby

[Valentine Gogichashvili]

> > What issue we may face if you take a backups(includes data dir + wal
files)
> > at standby without LVM snapshot?
>
> The backup might be corrupted in arbitrary ways.
>

And what will happen, if one issues a pg_start_backup() on the master, then
takes a file-backup on slave, and issues pg_stop_backup() on master again?
As far as I remember this approach was working for me, considering, that all
needed WAL files are transferred to the newly created DB copy as well.

-- Valentine Gogichashvili

Re: [HACKERS] the big picture for index-only scans

[Gokulakannan Somasundaram]

>
>
>> Note that we already have the visibility map, and the accesses needed to
> update it are already there. Granted, we'll have to change the logic
> slightly to make it crash safe, but I don't expect that to add any
> meaningful overhead - the changes are going to be where the bits are set,
> ie. vacuum, not when the bits are cleared. Granted, we might also want to
> set the bits more aggressively once they're used by index-only-scans. *But
> done correctly, just taking advantage of the VM that's already there
> shouldn't add overhead to other operations.*
>
> I agree that we need to do tests to demonstrate that there's a gain from
> the patch, once we have a patch to test. I would be very surprised if there
> isn't, but that just means the testing is going to be easy.
>
> --
>  Heikki Linnakangas
>
>  EnterpriseDB   http://www.enterprisedb.com
>
>
>  I could see some arguments supporting this feature, citing covering
indexes as example. But i just want to highlight they are not same.
Visibility map approach is totally not related to the covering indexes
approach, except the intention of avoiding the heap scan. Because of the
small size, we will be having more contentions(People who have worked with
Oracle can take the example of a bitmap index on a OLTP database). I was
making the suggestion previously to make these crash safe visibility maps
optional for a table, so that the overhead, which comes with it, can be
avoided for those tables, which have queries that don't support index only
scans. The fact that the proposal is for crash safe visibility map, to
become a default package of any Postgresql table will definitely have wide
ranging implications on OLTP performance.

Gokul.

Re: [HACKERS] wrong plpgsql's line counting 9.1

[Pavel Stehule]

Hello

I am sorry, it's noise

regards

Pavel

2011/8/19 Pavel Stehule :
> Hello
>
> I found a bug in plpgsql parser.
>
>
> create table t(a int, b int);
>
> postgres=# \sf fx
> CREATE OR REPLACE FUNCTION public.fx()
>  RETURNS void
>  LANGUAGE plpgsql
> AS $function$
> declare r record;
> begin
>  r := (10,20)::t;
>  if true then
>    raise notice '% %', r.c, r.b;
>  end if;
> end;
> $function$
>
> postgres=# select fx();
> ERROR:  record "r" has no field "c"
> CONTEXT:  SQL statement "SELECT r.c"
> PL/pgSQL function "fx" line 4 during function entry
>
> it reports a bug on line 4, but it should to be 6
>
> postgres=# \sf+ fx
>        CREATE OR REPLACE FUNCTION public.fx()
>         RETURNS void
>         LANGUAGE plpgsql
> 1       AS $function$
> 2       declare r record;
> 3       begin
> 4         r := (10,20)::t;
> 5         if true then
> 6           raise notice '% %', r.c, r.b;
> 7         end if;
> 8       end;
> 9       $function$
>
> Regards
>
> Pavel Stehule
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] wrong plpgsql's line counting 9.1

[Pavel Stehule]

Hello

I found a bug in plpgsql parser.


create table t(a int, b int);

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare r record;
begin
  r := (10,20)::t;
  if true then
raise notice '% %', r.c, r.b;
  end if;
end;
$function$

postgres=# select fx();
ERROR:  record "r" has no field "c"
CONTEXT:  SQL statement "SELECT r.c"
PL/pgSQL function "fx" line 4 during function entry

it reports a bug on line 4, but it should to be 6

postgres=# \sf+ fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS void
 LANGUAGE plpgsql
1   AS $function$
2   declare r record;
3   begin
4 r := (10,20)::t;
5 if true then
6   raise notice '% %', r.c, r.b;
7 end if;
8   end;
9   $function$

Regards

Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Kohei KaiGai]

I fixed up the security policy for regression test, and chkselinuxenv script.

The revised security policy allows test domains to execute programs
being installed under home directories.
In addition, the revised chkselinuxenv newly checks necessary commands
to run this script itself, and changed the way to validate executability of
psql command. (The point of this test is whether the psql is executable
by sepgsql_regtest_user_t, or not. So, bin_t is not a criteria to fail the
script.)

Thanks,

2011/8/18 Kohei Kaigai :
>> OK, I'm giving up for now.  I hit two more snags:
>>
>> 1. chkselinuxenv uses "which", and a Fedora 15 minimal install doesn't
>> include that.  I fixed that by installing "which", but maybe we ought
>> to be looking for a way to eliminate that dependency, like testing for
>> the commands you need by running them with --help, or something like
>> that.
>>
> Oops, I thought "which" is a part of coreutils.
>
> I'll try to update chkselinuxenv to print a help message when necessary 
> commands are not installed.
>
>> 2. restorecon doesn't correctly set the permissions for me on
>> ~/project/bin/psql.  I get:
>>
>> [rhaas@f15selinux sepgsql]$ ls -Z ~/project/bin/psql
>> -rwxr-xr-x. rhaas rhaas unconfined_u:object_r:user_home_t:s0
>> /home/rhaas/project/bin/psql
>>
>> Now I can fix that by applying bin_t manually, as suggested in the
>> documentation.  However, that just moves the failure to library load
>> time.  regression.diffs has multiple copies of this error message:
>>
>> /home/rhaas/project/bin/psql: error while loading shared libraries:
>> libpq.so.5: failed to map segment from shared object: Permission
>> denied
>>
> I guess it tries to mmap(2) libpq.so.5 (labeled as user_home_t) with 
> executable mode.
> The regression test switches domain of psql command on its execution from 
> "unconfined_t" to "sepgsql_regtest_user_t", however, I didn't allow this 
> domain to mmap(2) files in user's home directory with executable mode.
> It may need to revise the security policy of regression test to support 
> installation onto home directory.
>
> As a quick avoidance, how about --prefix=/usr/local/sepgsql instead?
>
> Thanks,
> --
> NEC Europe Ltd, SAP Global Competence Center
> KaiGai Kohei 
>
>
>> -Original Message-
>> From: Robert Haas [mailto:robertmh...@gmail.com]
>> Sent: 18. August 2011 18:22
>> To: Kohei Kaigai
>> Cc: Yeb Havinga; PgHacker; Kohei KaiGai
>> Subject: Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
>>
>> On Thu, Aug 18, 2011 at 1:00 PM, Robert Haas  wrote:
>> > [more problems]
>>
>> OK, I'm giving up for now.  I hit two more snags:
>>
>> 1. chkselinuxenv uses "which", and a Fedora 15 minimal install doesn't
>> include that.  I fixed that by installing "which", but maybe we ought
>> to be looking for a way to eliminate that dependency, like testing for
>> the commands you need by running them with --help, or something like
>> that.
>>
>> 2. restorecon doesn't correctly set the permissions for me on
>> ~/project/bin/psql.  I get:
>>
>> [rhaas@f15selinux sepgsql]$ ls -Z ~/project/bin/psql
>> -rwxr-xr-x. rhaas rhaas unconfined_u:object_r:user_home_t:s0
>> /home/rhaas/project/bin/psql
>>
>> Now I can fix that by applying bin_t manually, as suggested in the
>> documentation.  However, that just moves the failure to library load
>> time.  regression.diffs has multiple copies of this error message:
>>
>> /home/rhaas/project/bin/psql: error while loading shared libraries:
>> libpq.so.5: failed to map segment from shared object: Permission
>> denied
>>
>> Help!
>>
>> Thanks,
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>>  Click
>> https://www.mailcontrol.com/sr/g7UEZIfD10rTndxI!oX7Unz1!gA0DCbilsfI53CIRke!PbNpuk4RnjmGfZ8cEe1DM1
>> BV3YJKcc9jEfBJ2k7YZA==  to report this email as spam.
>



-- 
KaiGai Kohei 
 contrib/sepgsql/chkselinuxenv  |   68 ++--
 contrib/sepgsql/sepgsql-regtest.te |4 ++-
 2 files changed, 60 insertions(+), 12 deletions(-)

diff --git a/contrib/sepgsql/chkselinuxenv b/contrib/sepgsql/chkselinuxenv
index 0be17ab..76e41d1 100755
--- a/contrib/sepgsql/chkselinuxenv
+++ b/contrib/sepgsql/chkselinuxenv
@@ -4,11 +4,43 @@
 # satisfies prerequisites to run regression test.
 # If incorrect settings are found, this script suggest user a hint.
 #
+# NOTE:
+#   This script assumes the following commands are already installed:
+# /bin/sh, sed, awk, coreutils (id, test, echo, ...)
+#   If not installed, please set up them first.
+#
 PG_BINDIR="$1"
 PG_DATADIR="$2"
 
 echo
 echo "== checking selinux environment   =="
+#
+# Test.0 - necessary commands for environment checks
+#
+echo -n "test installed commans... "
+if ! which --help >&/dev/null; then
+echo "failed"
+echo
+echo "'which' command was not found, executable or installed."
+echo "Please make sure your PATH, or install this command

Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache

[Kohei KaiGai]

2011/8/18 Robert Haas :
> On Thu, Aug 18, 2011 at 1:17 PM, Kohei Kaigai  
> wrote:
>>> That's lame.  I think we need to patch contrib/sepgsql so that it
>>> fails to build in that case, rather than building and then not
>>> working.
>>>
>> It might be the following fix, but I have no idea to generate an error when 
>> $(with_selinux) != "yes" on makefile.
>
> Actually, as I look at this more, I think this build system is
> completely mis-designed.  Given that you want to build sepgsql,
> selinux is not an optional feature.  So the stuff in
> contrib/sepgsql/Makefile that is intended to link against libselinux
> only if --with-selinux was specified at configure time is nonsense.
> We should just ALWAYS try to link against libselinux, and if it's not
> there, then at least it'll fail right away at compile time instead of
> appearing to compile OK but producing an so that then fails to load at
> runtime.
>
> The only actual legitimate purpose of --with-selinux is to allow
> contrib/Makefile to decide whether, when someone tries to build "all
> the contrib modules", we should try to build sepgsql too.
>
I agree.

So, it seems to me we also need to revise configure script, not only
Makefile of sepgsql.

On configure script, we may need to check availability of libselinux
on the build system, independent from --with-selinux.
But it should not raise an error even if appropriate libselinux was not
available; except for the case when --with-selinux was explicitly given.
It just set flags of HAVE_SELINUX, instead.
I injected #error condition in sepgsql.h that shall be fired if user tries
to build contrib/sepgsql module without libselinux.

And, Makefile was revised to link libselinux always.

How about this design?

Thanks,
-- 
KaiGai Kohei 
 configure.in  |9 +
 contrib/sepgsql/Makefile  |2 +-
 contrib/sepgsql/sepgsql.h |3 +++
 3 files changed, 9 insertions(+), 5 deletions(-)

diff --git a/configure.in b/configure.in
index a844afc..cf95a7c 100644
--- a/configure.in
+++ b/configure.in
@@ -963,10 +963,11 @@ if test "$with_libxslt" = yes ; then
 fi
 
 # for contrib/sepgsql
-if test "$with_selinux" = yes; then
-  AC_CHECK_LIB(selinux, selinux_sepgsql_context_path, [],
-   [AC_MSG_ERROR([library 'libselinux', version 2.0.93 or newer, is required for SELinux support])])
-fi
+AC_CHECK_LIB(selinux, selinux_sepgsql_context_path,
+ [AC_DEFINE(HAVE_LIBSELINUX)],
+ [if test "$with_selinux" = yes; then
+AC_MSG_ERROR([library 'libselinux', version 2.0.93 or newer, is required for SELinux support])
+  fi])
 
 # for contrib/uuid-ossp
 if test "$with_ossp_uuid" = yes ; then
diff --git a/contrib/sepgsql/Makefile b/contrib/sepgsql/Makefile
index 7f997ee..1978ccf 100644
--- a/contrib/sepgsql/Makefile
+++ b/contrib/sepgsql/Makefile
@@ -19,7 +19,7 @@ include $(top_builddir)/src/Makefile.global
 include $(top_srcdir)/contrib/contrib-global.mk
 endif
 
-SHLIB_LINK += $(filter -lselinux, $(LIBS))
+SHLIB_LINK += -lselinux
 REGRESS_OPTS += --launcher $(top_builddir)/contrib/sepgsql/launcher
 
 check_selinux_environment:
diff --git a/contrib/sepgsql/sepgsql.h b/contrib/sepgsql/sepgsql.h
index 71688ab..455b638 100644
--- a/contrib/sepgsql/sepgsql.h
+++ b/contrib/sepgsql/sepgsql.h
@@ -15,6 +15,9 @@
 #include "fmgr.h"
 
 #include 
+#ifndef HAVE_LIBSELINUX
+#error libselinux is required for SELinux support
+#endif
 
 /*
  * SE-PostgreSQL Label Tag

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] how to get the effected row after execute a query

[Pavel Stehule]

Hello

2011/8/19 EazonGuo :
> Hello, I have a question. In SQL Server, we can use @@rowcount to get the
> efftected row which record last query 's  effected row .So I have a
> question:
> In Postgresql, how we can get the effected row? Is there some similar
> functions to complete this?
>

It depends on API hat you use. You can use GET DIAGNOSTICS statement
in PL/pgSQL. On client side, you should to use a PQcmdtuples function
or their equivalent (depends on interface).

http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

Regards

Pavel Stehule

>
> --
> Best regards
> Eazon
>

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] how to get the effected row after execute a query

[EazonGuo]

Hello, I have a question. In SQL Server, we can use @@rowcount to get the
efftected row which record last query 's  effected row .So I have a
question:
In Postgresql, how we can get the effected row? Is there some similar
functions to complete this?


-- 
Best regards

Eazon