[HACKERS] how to get the effected row after execute a query
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
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/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
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
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
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
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
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
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
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
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
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
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
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
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
Hello. How can we define a global variable in postgresql? -- Best regards Eazon
Re: [HACKERS] How to define global variable in postgresql
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
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
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
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
-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
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
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
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
-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
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
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
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
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
[ 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
