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

2011-08-19 Thread 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


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

2011-08-19 Thread Pavel Stehule
Hello

2011/8/19 EazonGuo eazon...@gmail.com:
 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


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

2011-08-19 Thread Kohei KaiGai
2011/8/18 Robert Haas robertmh...@gmail.com:
 On Thu, Aug 18, 2011 at 1:17 PM, Kohei Kaigai kohei.kai...@emea.nec.com 
 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 kai...@kaigai.gr.jp
 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 selinux/selinux.h
+#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] [v9.1] sepgsql - userspace access vector cache

2011-08-19 Thread 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 kohei.kai...@emea.nec.com:
 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 kohei.kai...@emea.nec.com


 -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 robertmh...@gmail.com 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 kai...@kaigai.gr.jp
 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 at first.
+echo
+echo If yum is available on your system, it will suggest packages
+

[HACKERS] wrong plpgsql's line counting 9.1

2011-08-19 Thread 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] wrong plpgsql's line counting 9.1

2011-08-19 Thread Pavel Stehule
Hello

I am sorry, it's noise

regards

Pavel

2011/8/19 Pavel Stehule pavel.steh...@gmail.com:
 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] the big picture for index-only scans

2011-08-19 Thread 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] Backup's from standby

2011-08-19 Thread 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] make -j4 world falls over

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

2011-08-19 Thread Tom Lane
Kohei KaiGai kai...@kaigai.gr.jp writes:
 2011/8/18 Robert Haas robertmh...@gmail.com:
 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] [v9.1] sepgsql - userspace access vector cache

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 9:59 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Kohei KaiGai kai...@kaigai.gr.jp writes:
 2011/8/18 Robert Haas robertmh...@gmail.com:
 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] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 9:19 AM, Gokulakannan Somasundaram
gokul...@gmail.com 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

2011-08-19 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Fri, Aug 19, 2011 at 9:59 AM, Tom Lane t...@sss.pgh.pa.us 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] [v9.1] sepgsql - userspace access vector cache

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane t...@sss.pgh.pa.us 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 selinux/selinux.h 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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 10:31 AM, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane t...@sss.pgh.pa.us 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 selinux/selinux.h 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


[HACKERS] How to define global variable in postgresql

2011-08-19 Thread EazonGuo
Hello. How can we define a global variable in postgresql?

-- 
Best regards

Eazon


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

2011-08-19 Thread Christopher Browne
On Fri, Aug 19, 2011 at 10:58 AM, EazonGuo eazon...@gmail.com 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


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

2011-08-19 Thread 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  br...@momjian.ushttp://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] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 11:22 AM, Bruce Momjian br...@momjian.us 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] [v9.1] sepgsql - userspace access vector cache

2011-08-19 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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] [v9.1] sepgsql - userspace access vector cache

2011-08-19 Thread 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 robertmh...@gmail.com wrote:
  On Fri, Aug 19, 2011 at 10:20 AM, Tom Lane t...@sss.pgh.pa.us 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 selinux/selinux.h 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 kohei.kai...@emea.nec.com

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


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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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

2011-08-19 Thread Tom Lane
Kohei Kaigai kohei.kai...@emea.nec.com 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

2011-08-19 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane t...@sss.pgh.pa.us 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

2011-08-19 Thread 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 kohei.kai...@emea.nec.com 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 kohei.kai...@emea.nec.com

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


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

2011-08-19 Thread Tom Lane
Kohei Kaigai kohei.kai...@emea.nec.com 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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 11:46 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Kohei Kaigai kohei.kai...@emea.nec.com 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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Fri, Aug 19, 2011 at 11:26 AM, Tom Lane t...@sss.pgh.pa.us 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] How to define global variable in postgresql

2011-08-19 Thread 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 Weimerfwei...@bfk.de
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] Transient plans versus the SPI API

2011-08-19 Thread Tom Lane
[ getting back to the planner finally ]

Simon Riggs si...@2ndquadrant.com writes:
 On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Simon Riggs si...@2ndquadrant.com 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] New copyright program

2011-08-19 Thread 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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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


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

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

2011-08-19 Thread 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.


[HACKERS] Rethinking sinval callback hook API

2011-08-19 Thread 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


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

2011-08-19 Thread 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 provide ?

Thanks in advance,
Sergey


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

2011-08-19 Thread 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


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

2011-08-19 Thread 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.phphttp://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

2011-08-19 Thread 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.phphttp://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] FATAL: ReleaseSavepoint: unexpected state STARTED

2011-08-19 Thread Robert Haas
On Thu, Aug 18, 2011 at 3:57 AM, Marcin Mańk marcin.m...@gmail.com wrote:
 On Wed, Aug 17, 2011 at 11:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 =?UTF-8?B?TWFyY2luIE1hxYRr?= marcin.m...@gmail.com 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] FATAL: ReleaseSavepoint: unexpected state STARTED

2011-08-19 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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] the big picture for index-only scans

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 2:51 PM, 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,

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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 3:31 PM, Tom Lane t...@sss.pgh.pa.us 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

2011-08-19 Thread Robert Haas
On Fri, Aug 19, 2011 at 4:02 PM, Robert Haas robertmh...@gmail.com 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] New copyright program

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

2011-08-19 Thread 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] Transient plans versus the SPI API

2011-08-19 Thread Simon Riggs
On Fri, Aug 19, 2011 at 6:13 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 [ getting back to the planner finally ]

 Simon Riggs si...@2ndquadrant.com writes:
 On Wed, Aug 3, 2011 at 8:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Simon Riggs si...@2ndquadrant.com 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] New copyright program

2011-08-19 Thread 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] New copyright program

2011-08-19 Thread 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 da...@fetter.org 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

2011-08-19 Thread 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  br...@momjian.ushttp://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

2011-08-19 Thread 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

2011-08-19 Thread 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  br...@momjian.ushttp://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

2011-08-19 Thread 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

2011-08-19 Thread 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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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


Re: [HACKERS] New copyright program

2011-08-19 Thread 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

2011-08-19 Thread 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 da...@fetter.org 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

2011-08-19 Thread 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

2011-08-19 Thread 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 da...@fetter.org 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

2011-08-19 Thread 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  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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


Re: [HACKERS] New copyright program

2011-08-19 Thread 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. 
http://www.cpan.org/src/README.html 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

2011-08-19 Thread 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 da...@fetter.org 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] two index bitmap scan of a big table hash_seq_search

2011-08-19 Thread Tom Lane
Sergey E. Koposov m...@sai.msu.ru 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] the big picture for index-only scans

2011-08-19 Thread Heikki Linnakangas

On 19.08.2011 23:02, Robert Haas wrote:

On Fri, Aug 19, 2011 at 2:51 PM, Gokulakannan Somasundaram
gokul...@gmail.com  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] the big picture for index-only scans

2011-08-19 Thread Heikki Linnakangas

On 19.08.2011 23:17, Robert Haas wrote:

On Fri, Aug 19, 2011 at 4:02 PM, Robert Haasrobertmh...@gmail.com  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